Python Operating MySQL Database

The Python standard database interface is Python DB-API. Python DB-API provides developers with a database application programming interface.

The Python database interface supports a great many databases. You can choose the database that suits your project:

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

You can visitPython Database Interfaces and APIto 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 required objects and database access methods in order to provide a consistent access interface for a variety of underlying database systems and a wide range of database interface programs.

Python's DB-API implements interfaces for most databases. After using it to connect to each database, you can operate on all databases in the same way.

Python DB-API usage process:

• Import the API module.
• Obtain the connection to the database.
• Execute SQL statements and stored procedures.
• Close the database connection.

---

What is MySQLdb?

MySQLdb is an interface for Python to connect to the MySQL database. It implements the Python Database API specification V2.0 and is built on the MySQL C API.

---

How to install MySQLdb?

In order to write MySQL scripts using DB-API, you must ensure that MySQL is installed. Copy the following code and execute it:

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

import MySQLdb

If the output after execution is as shown below, 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 visithttp://sourceforge.net/projects/mysql-python, (Linux platforms can visit:https://pypi.python.org/pypi/MySQL-python) From here you can choose an installation package suitable for your platform, divided into precompiled binary files and source code installation packages.

If you choose the binary release version, the installation process can basically be completed following the installation prompts. If installing from source code, you need to switch to the top-level directory of the MySQLdb release version and type 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 make sure you have root privileges to install the above module.

---

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 EMPLOYEE table fields 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 your own, or directly use the root username and its password. For MySQL database user authorization, please use the GRANT command.
• You have installed the Python MySQLdb module on your machine.
• If you are not familiar with SQL statements, you can visit ourSQL Basics Tutorial

Example:

The following example connects to MySQL's TESTDB database:

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

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()

print "Database version : %s " % data

# 关闭数据库连接
db.close()

The output after executing the above script is as follows:

Database version : 5.0.45

---

Creating Database Tables

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

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

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 创建数据表SQL语句
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

# 关闭数据库连接
db.close()

---

Database Insert Operation

The following example inserts records into the EMPLOYEE table by executing an SQL INSERT statement:

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

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# 关闭数据库连接
db.close()

The above example can also be written in the following form:

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

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s, %s, %s, %s, %s )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

Example:

The following code uses variables to pass parameters to SQL statements:

..................................
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 fetch a single piece of data and the fetchall() method to fetch multiple pieces of data.

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

Example:

Query all data in the EMPLOYEE table where the salary field is greater than 1000:

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

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # 打印结果
      print "fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fetch data"

# 关闭数据库连接
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 data in data tables. The following example increments by 1 the AGE field of records in the EMPLOYEE table whose SEX field is 'M':

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

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

---

Delete Operation

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

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

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭连接
db.close()

Performing Transactions

The transaction mechanism can ensure data consistency.

A transaction should have four attributes: atomicity, consistency, isolation, and durability. These four attributes are usually called the ACID characteristics.

• Atomicity. A transaction is an indivisible unit of work; the operations included in a transaction are either all performed or none are performed.
• Consistency. A transaction must change the database from one consistent state to another consistent state. Consistency is closely related to atomicity.
• Isolation. The execution of a transaction cannot be interfered with by other transactions. That is, the operations inside a transaction and the data it uses are isolated from other concurrent transactions, and concurrently executing transactions cannot interfere with each other.
• Durability. Durability is also called permanence, meaning that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent operations or failures should not have any effect on it.

Python DB API 2.0 provides two methods for transactions: commit or rollback.

Example:

# SQL删除记录语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 向数据库提交
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

For databases that support transactions, in Python database programming, when a cursor is established, an implicit database transaction automatically begins.

The commit() method commits all update operations of the cursor, and the rollback() method rolls back all operations of the current cursor. Each method starts a new transaction.

---

Error Handling

The DB API defines some errors and exceptions for database operations. The following table lists these errors and exceptions:

Exception	Description
Warning	Triggered when there is a serious warning, for example when inserted data is truncated, etc. Must be a subclass of StandardError.
Error	All error classes other than warnings. Must be a subclass of StandardError.
InterfaceError	Triggered when an error of the database interface module itself (rather than an error of the database) occurs. Must be a subclass of Error.
DatabaseError	Triggered when a database-related error occurs. Must be a subclass of Error.
DataError	Triggered when a data processing error occurs, for example: division by zero error, data out of range, etc. Must be a subclass of DatabaseError.
OperationalError	Refers to errors that are not under user control but occur when operating the database. For example: unexpected disconnection, database name not found, transaction processing failure, memory allocation error, and other errors that occur when operating the database. Must be a subclass of DatabaseError.
IntegrityError	Integrity-related errors, such as foreign key check failure, etc. Must be a subclass of DatabaseError.
InternalError	Internal database errors, such as an invalid cursor, transaction synchronization failure, etc. Must be a subclass of DatabaseError.
ProgrammingError	Programming errors, such as a table not found or already existing, SQL statement syntax errors, wrong number of parameters, etc. Must be a subclass of DatabaseError.
NotSupportedError	Unsupported errors, referring to the use of functions or APIs that the database does not support. For example, using the .rollback() function on a connection object, but the database does not support transactions or transactions have been disabled. Must be a subclass of DatabaseError.

Other Extensions