Latest web development tutorials

python operations MySQL database

Python standard database interface for the Python DB-API, Python DB-API provides a database application programming interface for developers.

Python database interface supports very large databases, you can choose your database project:

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

You can access the Python database interface and API See detailed list of supported databases.

Different database you will need to download a different DB API module, for example, you need to access Oracle databases and Mysql data, you need to download the Oracle and MySQL database module.

DB-API is a specification that defines a set of objects and the database must access mode, in order to provide a consistent interface to access a wide variety of underlying database system and a variety of database interface program.

Python's DB-API, for most of the database implements the interface, use it after connecting the database, you can use the same manner each database.

Python DB-API using the process:

  • The introduction of API module.
  • Get connected to the database.
  • Execute SQL statements and stored procedures.
  • Close the database connection.

What is MySQLdb?

MySQLdb is an interface for Python Mysql database link, which implements the Python Database API specification V2.0, based on the establishment of the MySQL C API.


How to install MySQLdb?

To write MySQL scripts with DB-API, you must ensure that you have installed MySQL. Copy the following code, and execute it:

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

import MySQLdb

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

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

Install MySQLdb, visit http://sourceforge.net/projects/mysql-python , (the Linux platform can be accessed: https://pypi.python.org/pypi/MySQL-python ) From here choose for your platform installation package, pre-compiled into binaries and source code package.

If you select binary release version, the installation process prompts to complete the basic installation. If installing from source code, you need to switch to MySQLdb release top-level directory, and type the following command:

$ 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: Make sure you have root privileges to install the modules described above.


Database Connectivity

Before connecting to the database, make sure the following items:

  • You have created a database TESTDB.
  • In TESTDB database you have created the table EMPLOYEE
  • EMPLOYEE table field FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
  • Users connect to the database using TESTDB named "testuser", password "test123", you can set your own directly or root user name and password, Mysql database user license, please use the Grant command.
  • On your machine already installed Python MySQLdb module.
  • If you are not familiar with the sql statement, you can visit our SQL tutorial basis

Example:

The following examples of TESTDB Mysql database link:

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

import MySQLdb

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

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

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

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

print "Database version : %s " % data

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

Implementation of the above script outputs the results are as follows:

Database version : 5.0.45

Create a database table

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

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

import MySQLdb

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

# 使用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

The following example uses execute a SQL INSERT statement to insert a record to the table EMPLOYEE:

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

import MySQLdb

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

# 使用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 as follows:

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

import MySQLdb

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

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

# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('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 a SQL statement:

..................................
user_id = "test123"
password = "password"

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

Database queries

Python Mysql query using fetchone () method to obtain a single data using fetchall () method to obtain a plurality of data.

  • fetchone (): This method gets the next query result set.The result set is a target
  • fetchall (): returns the result to receive all rows.
  • rowcount: This is a read-only attribute, and returns the execution execute () method after the number of rows affected.

Example:

Discover EMPLOYEE table salary (wage) data field is greater than all of 1000:

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

import MySQLdb

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

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

# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (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=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"

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

The above script execution results are as follows:

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

Database updates

Data updating operation for updating the data table, the following examples will TESTDB table SEX field all changes to 'M', AGE field is incremented by 1:

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

import MySQLdb

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

# 使用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()

Deletion

Delete data delete data for the table, the following example demonstrates delete data in the table EMPLOYEE AGE is greater than 20, all of the data:

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

import MySQLdb

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

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

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

# 关闭连接
db.close()

Executive Affairs

Transaction mechanism to ensure data consistency.

Services should have four properties: atomicity, consistency, isolation, durability. These four properties are often referred to as the ACID properties.

  • Atomic (atomicity). A transaction is an indivisible unit of work, such operations are included in the transaction either do or do not do.
  • Consistency (consistency). Services must be changed to make the database from one consistent state to another consistent state. Consistency and atomicity are closely related.
  • Isolation (isolation). Execute a transaction can not be other transactions interference. That is a transaction internal operation and use of other data transaction concurrency is isolated and can not interfere with each other between individual transactions execute concurrently.
  • Persistence (durability). Persistent also called permanent (permanence), it means that once a transaction commits, change its data in the database should be permanent. The following other operations or its failure should not have any impact.

Python DB API 2.0 provides two methods of transaction commit or rollback.

Example:

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

For transactional database, database programming in Python, when the cursor is established, automatically begins a database transaction invisible.

commit () method for all the updates cursor, rollback () method to roll back all the operations of the current cursor. Each method has started a new business.


Error Handling

DB API defines some errors and abnormal operation of the database, the following table lists the errors and exceptions:

abnormal description
Warning When there is a serious warning to trigger, such as inserting data is truncated, and so on. It must be a subclass of StandardError.
Error All other error classes other than warnings. It must be a subclass of StandardError.
InterfaceError Fires when the database interface module has its own error (instead of an error database) occurs. It must be a subclass of Error.
DatabaseError Trigger and database-related errors. It must be a subclass of Error.
DataError When there is a data processing error occurred when triggered, for example: divide by zero error, the data out of range, and so on. It must be a subclass of DatabaseError.
OperationalError It refers to a non-user-controlled, but the error occurred while the database. For example: accidentally disconnected, the database name is not found, the transaction fails, memory allocation error, etc. operations database error occurred. It must be a subclass of DatabaseError.
IntegrityError Integrity-related errors, such as a foreign key check fails and the like. It must be DatabaseError subclass.
InternalError Internal database error, such as a cursor (cursor) fails, the failure transaction synchronization and so on. It must be DatabaseError subclass.
ProgrammingError Procedural errors, such as data table (table) not found or already exists, SQL statement syntax error, an error number of parameters, and so on. It must be a subclass of DatabaseError.
NotSupportedError Not supported error, refers to the use of functions such as API or database does not support. For example, using .rollback on the connection object () function, but the database does not support transactions or transaction has been closed. It must be a subclass of DatabaseError.