Python operates MySQL database

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

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

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

You can visit the Python database interface and API to view a detailed list of supported databases.

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

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

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

Python DB-API usage process:

Introduce the API module.
Get 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 based on MySQL C Built on API.

How to install MySQLdb?

In order to write MySQL scripts with 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 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 , (Linux platform can access: https://pypi.python.org/pypi/MySQL-python) From here you can choose the installation package suitable for your platform, which is divided into precompiled binary files and Source code installation package.

If you choose the binary file release version, the installation process will be completed with basic installation prompts. If you are installing from source code, you need to switch to the top-level directory of the MySQLdb distribution 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: Please make sure you have root permissions to install the above modules.

Database connection

Before connecting to the database, please confirm the following:

You have created the database TESTDB.
You have created the table EMPLOYEE in the TESTDB database
The fields of the EMPLOYEE table are FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
The user name used to connect to the database TESTDB is "testuser" and the password is "test123". You can set it yourself or directly use the root username and password. Mysql database user authorization Please use the Grant command.
The Python MySQLdb module has been installed on your machine.
If you are not familiar with sql statements, you can visit our basic SQL tutorial

Examples:

The following examples link to Mysql's TESTDB Database:

#!/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()

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 database table Table, create table EMPLOYEE as follows:

#!/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 insertion operation

The following example uses the SQL INSERT statement to insert records into 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 It can also be written in the following form:

#!/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 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 to obtain a single piece of data using the fetchone() method, and to obtain multiple pieces of data using the fetchall() method.

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

Example:

Query all data in the EMPLOYEE table whose salary field is greater than 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 update operation

The update operation is used to update the data of the data table. The following example will modify all the SEX fields in the TESTDB table to 'M', and the AGE field will be 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()

Delete operation

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

#!/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()

Execute transactions

The transaction mechanism can ensure data consistency.

Transactions should have 4 attributes: atomicity, consistency, isolation, and durability. These four properties are often called ACID properties.

Atomicity. A transaction is an indivisible unit of work. All operations included in the transaction are either done or none of them are done.
Consistency. A transaction must change the database from one consistency state to another. Consistency and atomicity are closely related.
Isolation. The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and transactions executed concurrently cannot interfere with each other.
Durability. Continuity, also known as permanence, means 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 impact on it.

Python DB API 2.0 transactions provide two methods commit or rollback.

Example:

# 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 the cursor is created, an invisible database transaction is automatically started.

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

Error handling

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, such as the inserted data is truncated, etc. Must be a subclass of StandardError.
Error	All other error types except warning. Must be a subclass of StandardError.
InterfaceError	Triggered when an error occurs in the database interface module itself (not an error in the database). Must be a subclass of Error.
DatabaseError	Triggered when an error related to the database occurs. Must be a subclass of Error.
DataError	Triggered when an error occurs during data processing, such as: division by zero error, data out of range, etc. Must be a subclass of DatabaseError.
OperationalError	refers to errors that are not controlled by the user, but occur when operating the database. For example: connection unexpectedly disconnected, Database name not found, transaction processing failed, memory allocation error, etc. are 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 DatabaseError subclass.
InternalError	Internal errors in the database, such as cursor failure, transaction synchronization failure, etc. Must be a DatabaseError subclass.
ProgrammingError	Programming error, such as the data table (table) is not found or already exists, SQL statement syntax error, The number of parameters is wrong, etc. Must be a subclass of DatabaseError.
NotSupportedError	Not supported error refers to the use of functions or APIs that are not supported by the database. For example on the connection object Use the .rollback() function, but the database does not support transactions or the transaction has been closed. Must be a subclass of DatabaseError.

Python operates MySQL database

What is MySQLdb?

How to install MySQLdb?

Database connection

Examples:

Create database table

Database insertion operation

Example:

Database query operation

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

Error handling

php.cn