Python2.x Python Operating MySQL Database

The standard database interface of Python is Python DB-API, which provides a database application programming interface for developers.

Python database interface supports many databases, and you can choose the database suitable for your project:

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase

You can visit Python Database Interfaces & API to view the detailed list of supported databases.

For different databases, you need to download different DB API modules. For example, if you need to access Oracle and MySQL databases, you need to download the Oracle and MySQL database modules.

DB-API is a specification. It defines a series of objects and database access methods to provide consistent access interfaces for various underlying database systems and multiple database interface programs.

Python's DB-API implements an interface for most databases. After connecting to the database using it, you can operate the databases in the same way.

The usage flow of Python DB-API:

1. Import the API module.
2. Get the connection to the database.
3. Execute SQL statements and stored procedures.
4. Close the database connection.

---

What is MySQLdb?

MySQLdb is an interface for Python to link to MySQL databases. It implements Python Database API Specification V2.0 and is based on MySQL C API.

---

How to Install MySQLdb?

To write MySQL scripts using DB-API, you must ensure that MySQL is already installed. Copy the following code and execute:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

If the output after execution is as follows, it means you have not installed the MySQLdb module:

Traceback (most recent call last):
  File "test.py", line 3, in <module>
    import MySQLdb
ImportError: No module named MySQLdb

To install MySQLdb, please visit http://sourceforge.net/projects/mysql-python, (for Linux platform, visit: https://pypi.python.org/pypi/MySQL-python). Here you can choose the installation package suitable for your platform, divided into precompiled binary files and source code installation packages.

If you choose the binary file distribution version, the installation process can be completed according to the installation prompts. If you install from the source code, you need to switch to the top-level directory of the MySQLdb distribution version and enter the following commands:

$ gunzip MySQL-python-1.2.2.tar.gz
$ tar -xvf MySQL-python-1.2.2.tar
$ cd MySQL-python-1.2.2
$ python setup.py build
$ python setup.py install

Note: Please ensure you have root privileges to install the above modules.

---

Database Connection

Before connecting to the database, please confirm the following:

• You have created the database TESTDB.
• In the TESTDB database, you have created the table EMPLOYEE.
• The fields of the EMPLOYEE table are FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
• The username used to connect to the TESTDB database is "testuser" and the password is "test123". You can set it yourself or directly use the root username and its password. Use the GRANT command for MySQL database user authorization.
• The Python MySQLdb module is installed on your machine.
• If you are unfamiliar with SQL statements, you can visit our SQL Basic Tutorial.

Example:

The following example connects to the TESTDB database of Mysql:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# Get a cursor object using the cursor() method
cursor = db.cursor()

# Execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print("Database version : %s ") % data

# Disconnect from server
db.close()

The output result of executing the above script is as follows:

Database version : 5.0.45

---

Create Database Table

If the database connection exists, we can use the execute() method to create a table for the database, as shown below in creating the EMPLOYEE table:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# Get a cursor object using the cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT
)"""
cursor.execute(sql)

# Disconnect from server
db.close()

---

Database Insert Operation

The following example uses the execute() method to insert records into the EMPLOYEE table by executing an SQL INSERT statement:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# Get a cursor object using the cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# Disconnect from server
db.close()

The above example can also be written as follows:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# Get a cursor object using the cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, 
       LAST_NAME, AGE, SEX, INCOME) 
       VALUES (%s, %s, %s, %s, %s )" % 
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# Disconnect from server
db.close()

Example:

The following code uses variables to pass parameters to the SQL statement:

..................................
user_id = "test123"
password = "password"
con.execute('insert into Login values(%s, %s)' % 
             (user_id, password))
..............................

---

Database Query Operation

Python queries MySQL using the fetchone() method to get a single piece of data, and uses fetchall() method to get multiple pieces of data.

• fetchone(): This method gets the next row of the query result set. The result set is an object.
• fetchall(): Receives all the returned result rows.
• rowcount: This is a read-only attribute and returns the number of rows affected by the execute() method.

Example:

The following code queries all data in the EMPLOYEE table where the salary field is greater than 1000:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# Get a cursor object using the cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE 
       WHERE INCOME > %s" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row
      lname = row
      age = row
      sex = row
      income = row
      # Now print fetched result
      print "fname=%s,lname=%s,age=%s,sex=%s,income=%s" % 
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fetch data"

# Disconnect from server
db.close()

The execution result of the above script is as follows:

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

---

Database Update Operation

The update operation is used to update the data in the data table. The following example increments the AGE field by 1 for all records in the EMPLOYEE table where the SEX field is 'M':

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# Get a cursor object using the cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# Disconnect from server
db.close()

---

Delete Operation

The delete operation is used to delete data in the data table. The following example demonstrates deleting all records in the EMPLOYEE table where AGE is greater than 20:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# Get a cursor object using the cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# Disconnect from server
db.close()

Executing Transactions

The transaction mechanism can ensure data consistency.

A transaction should have four attributes: atomicity, consistency, isolation, and durability. These four attributes are usually referred to as ACID properties.

• Atomicity (atomici