Latest web development tutorials

Python3 MySQL database connection

In this paper, we introduce Python3 use PyMySQL connect to the database, and simple CRUD.

What is PyMySQL?

PyMySQL for a database connection MySQL server version in Python3.x, Python2 in use mysqldb.

PyMySQL follow Python Database API v2.0 specification and contains pure-Python MySQL client library.


PyMySQL installation

Before using PyMySQL, we need to ensure PyMySQL installed.

PyMySQL Download: https: //github.com/PyMySQL/PyMySQL.

If not already installed, we can use the following command to install the latest version of PyMySQL:

$ pip install PyMySQL

If your system does not support pip command, you can use the following installed:

1, using the git command to download the installation package (you can also manually download):

$ git clone https://github.com/PyMySQL/PyMySQL
$ cd PyMySQL/
$ python3 setup.py install

2, if you need to develop a version number, you can use the curl command to install:

$ # X.X 为 PyMySQL 的版本号
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install
$ # 现在你可以删除 PyMySQL* 目录

Note: Make sure you have root privileges to install the modules described above.

The installation process may appear "ImportError: No module named setuptools" error message, which means you do not have to install setuptools, you can access https://pypi.python.org/pypi/setuptools find the installation method of each system.

Linux installation Example:

$ wget https://bootstrap.pypa.io/ez_setup.py
$ python3 ez_setup.py

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/python3

import pymysql

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

# 使用 cursor() 方法创建一个游标对象 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.5.20-log

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/python3

import pymysql

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

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute() 方法执行 SQL,如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 使用预处理语句创建表
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/python3

import pymysql

# 打开数据库连接
db = pymysql.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:
   # 如果发生错误则回滚
   db.rollback()

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

The above example can also be written as follows:

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.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)
   # 执行sql语句
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

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

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/python3

import pymysql

# 打开数据库连接
db = pymysql.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/python3

import pymysql

# 打开数据库连接
db = pymysql.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/python3

import pymysql

# 打开数据库连接
db = pymysql.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.

Examples

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