PYTHON - MySQL database
MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one of the parts of the very popular LAMP platform. Linux, Apache, MySQL, PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs under BSD Unix, Linux, Windows or Mac. Wikipedia and YouTube use MySQL. These sites manage millions of queries each day. MySQL comes in two versions. MySQL server system and MySQL embedded system.
To install several packages to execute the examples .
You Should have administrator Rights
username :su password :cselab
Installing MySQL 5
yum install mysql mysql-server
Then we create the system startup links for MySQL (so that MySQL starts automatically whenever thesystem boots) and start the MySQL server
chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start
Here we install the Python interface to the MySQL database. Both _mysql and MySQL modules.
Next, we are going to create a new database user and a new database. We use the mysql client.
[root@localhost Desktop]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.60 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+-------------------------+
| Database |
+-------------------------+
| information_schema |
| mysql | |
| test |
+--------------------+
3 rows in set (0.00 sec)
We connect to the database using the root account. We show all available databases with the SHOW DATABASES statement.
mysql> CREATE DATABASE testdb ;
Query OK, 1 row affected (0.02 sec)
We create a new testdb database. We will use this database throughout the tutorial.
mysql> CREATE USER '2910001'@'localhost' IDENTIFIED BY 'yourpassword';
Query OK, 0 rows affected (0.00 sec)
mysql> USE testdb;
Database changed
mysql> GRANT ALL ON testdb.* TO '2910001'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> quit;
Bye
We create a new database user. We grant all privileges to this user for all tables of the testdb database.
_mysql module
The _mysql module implements the MySQL C API directly. It is not compatible with the Python DB API interface. Generally, the programmers prefer the object oriented MySQLdb module. We will concern ourself with the latter module. Here we present only one small example with the _mysql module.
Creating and populating a table
We create a table and populate it with some data.
Create a python executable file say create.py
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
import sys
con = mdb.connect('localhost', '2910001', 'root', 'testdb');
with con:
cur = con.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS \
Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR (25))")
cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
We create a Writers table and add five authors to it.
cur.execute("CREATE TABLE IF NOT EXISTS \
Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
This SQL statement creates a new database table called Writers. It has two columns. Id and Name.
cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
...
We use the INSERT statement to insert authors to the table. Here we add two rows.
mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name |
+----+-------------------+
| 1 | Jack London |
| 2 | Honore de Balzac |
| 3 | Lion Feuchtwanger |
| 4 | Emile Zola |
| 5 | Truman Capote |
+----+-------------------+
5 rows in set (0.00 sec)
After executing the script, we use the mysql client tool to select all data from the Writers table.
Retrieving data
Returning all data at a time may not be feasible. We can fetch rows one by one.
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
import sys
con = mdb.connect('localhost', '2910001',
'root', 'testdb');
with con:
cur = con.cursor()
cur.execute("SELECT * FROM Writers")
numrows = int(cur.rowcount)
for i in range(numrows):
row = cur.fetchone()
print row[0], row[1]
We again print the data from the Writers table to the console. This time, we fetch the rows one by one.
numrows = int(cur.rowcount)
Here we determine the number of rows returned by the SQL statement.
for i in range(numrows):
row = cur.fetchone()
print row[0], row[1]
We fetch the rows one by one using the fetchone() method.
$ ./retrieve2.py
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote
Output of the example.
Prepared statements
Now we will concern ourselves with prepared statements. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance. The Python DB API specification suggests 5 different ways how to build prepared statements. MySQLdb module supports one of them, the ANSI printf format codes.
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
import sys
aid=raw_input('enter the Author id u want to change')
aname=raw_iput('enter the author newname')
con = mdb.connect('localhost', '2910003',
'root', 'testdb')
with con:
cur = con.cursor()
cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
(aname,aid))
print "Number of rows updated: %d" % cur.rowcount
aid=4
aname=sai
This is the way to do it. We use the two %s placeholders. Before the SQL statement is executed, the values are bound to their placeholders.
mysql> SELECT Name FROM Writers WHERE Id=4;
+------------------+
| Name |
+------------------+
| sai
+------------------+
1 row in set (0.00 sec)
The author on the fourth row was successfully changed.
No comments:
Post a Comment