Python operates the mysql database

python operates the mysql database

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

The Python database interface supports a very large number of databases, and you can choose the database that is right for your project:

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

You can access the Python database interface and API to see a detailed list of support databases.

Different databases You need to download different DB API modules, for example you need access to Oracle databases and Mysql data, you need to download Oracle and MySQL database modules.

DB-API is a specification. It defines a set of necessary object and data inventory methods to provide a consistent provider for a wide variety of underlying database systems and a wide variety of database interface programs.

Python's DB-API, which implements interfaces for most databases, allows you to manipulate each database in the same way.

Python DB-API usage process:

• Introduce the API module.
• Get a connection to the database.
• Perform SQL statements and stored procedures.
• Close the database connection.

---

What is MySQLdb?

MySQLdb is an interface for Python linking to mysql databases, and it implements the Python database API specification V2.0, based on the MySQL C API.

---

How do I install MySQLdb?

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

# encoding: utf-8
#!/usr/bin/python

import MySQLdb

If the output after execution looks like this, it means that you are not installing 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, visit http://sourceforge.net/projects/mysql-python , where https://pypi.python.org/pypi/MySQL-python you can choose the installation package that is right for your platform, divided into precompiled binary files and source code installation packages.

If you select a binary release, the installation process is complete with basic installation tips. If you install from source code, you'll need to switch to the top-level directory of the MySQLdb release 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: Make sure that you have root permission to install the modules above.

---

The database connection

Before you connect to the database, please confirm the following:

• You have created a database TESTDB.
• You have created the table EMPLOYEE in the TESTDB database
• The EMPLOYEE table fields are FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
• The user name used by the connection database TESTDB is "testuser" and the password is "test123", you can set or directly use the root username and its password, mysql database user authorization to use the Grant command.
• The Python MySQLdb module is already installed on your machine.
• If you are not familiar with sql statements, you can visit our SQL Basics tutorial

Instance:

The following example links mysql's TESTDB database:

# encoding: utf-8
#!/usr/bin/python

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()

The output of the above script is as follows:

Database version : 5.0.45

---

Create a database table

If a database connection exists, we can use the execute() method to create a table for the database, as follows:

# encoding: utf-8
#!/usr/bin/python

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()

---

The database insert operation

The following instance inserts a record into the table EMPLOYEE using the SQL INSERT statement:

# encoding: utf-8
#!/usr/bin/python

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 examples can also be written as follows:

# encoding: utf-8
#!/usr/bin/python

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()

Instance:

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))
..................................

---

The database query operation

Python query Mysql uses the fetchone() method to get a single data, and the fetchall() method to get multiple data.

• Fetchone(): The method gets the next set of query results. The result set is an object
• Fetchall(): Receive all return result lines.
• Rowcount: This is a read-only property and returns the number of rows affected after executing the execute() method.

Instance:

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

# encoding: utf-8
#!/usr/bin/python

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 executes as follows:

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

---

The database update operation

The update operation is used to update the data of the data table, and the following examples modify all the SEX fields in the TESTDB table to 'M', with the AGE field increasing by 1:

# encoding: utf-8
#!/usr/bin/python

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()

---

Delete the action

The delete operation is used to delete the data in the data table, and the following example demonstrates the removal of all data in the data table EMPLOYEE with AGE 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 > '%d'" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭连接
db.close()

The transaction is executed

The transaction mechanism ensures data consistency.

Transactions should have four properties: atomicity, consistency, isolation, persistence. These four properties are often referred to as ACID attributes.

• Atomicity. A transaction is an indivisible unit of work, and all operations included in a transaction are either done or not done.
• Consistency. T he transaction must change the database from one consistency state to another. Consistency is closely related to atomicity.
• Isolation. T he execution of one transaction cannot be interfered with by other transactions. That is, operations within a transaction and the data used are isolated from other transactions that are performed in a transaction and cannot interfere with each other.
• Durability. C ontinuity, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Other actions or failures that follow should not have any effect on them.

Transactions for Python DB API 2.0 provide two methods, commit or rollback.

Instance:

# SQL删除记录语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (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 invisible database transaction is automatically started.

Commit() method cursor for all update operations, rollback() method to roll back all operations for the current cursor. Each method starts a new transaction.

---

Error handling

Errors and exceptions to database operations are defined in the DB API, and are listed in the following table:

Abnormal	Describe
Warning	Triggered when there is a critical warning, such as inserting data that is truncated, and so on. Must be a sub-class of StandardError.
Error	All other error classes except the warning. Must be a sub-class of StandardError.
InterfaceError	Triggered when an error occurs with the database interface module itself, not the database. Must be a sub-class of Error.
DatabaseError	A database-related error is triggered when it occurs. Must be a sub-class of Error.
DataError	Triggers when errors occur when data is processed, such as zero-except errors, data out-of-range, and so on. Must be a sub-class of DatabaseError.
OperationalError	Refers to an error that is not controlled by the user, but occurs when the database is operated. F or example, an unexpected disconnection of a connection, a database name not found, a transaction failure, a memory allocation error, and so on are errors that occur in the operational database. Must be a sub-class of DatabaseError.
IntegrityError	Integrity-related errors, such as failed foreign key checks, etc. Must be a DatabaseError sub-class.
InternalError	Internal errors in the database, such as cursor failure, transaction synchronization failure, and so on. Must be a DatabaseError sub-class.
ProgrammingError	Program errors, such as no or existing data table, SQL statement syntax errors, parameter number errors, and so on. Must be a sub-class of DatabaseError.
NotSupportedError	Errors are not supported, refers to the use of functions or APIs that are not supported by the database, etc. F or example, use the .rollback() function on a connection object, but the database does not support transactions or transactions are closed. Must be a sub-class of DatabaseError.