Python3 MySQL database connection

Python3 MySQL database connection

In this article, we introduce Python3 to connecting databases using PyMySQL and implement simple additions and deletions.

What is PyMySQL?

PyMySQL is a library used to connect MySQL servers in Python3.x, and mysqldb is used in Python2.

PyMySQL follows the Python Database API v2.0 specification and includes the pure-Python MySQL client library.

---

PyMySQL installation

Before we can use PyMySQL, we need to make sure that PyMySQL is installed.

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

If we haven't, we can install the latest version of PyMySQL using the following commands:

$ pip install PyMySQL

If your system does not support pip commands, you can install them in the following ways:

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

$ 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 that you have root permission to install the modules above.

During installation, you may get an error prompt for "ImportError: No module named setuptools", which means that you have not installed setuptools and you can visit https://pypi.python.org/pypi/setuptools to find the installation methods for each system.

Linux system installation instance:

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

---

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 tutorial

Instance:

The following example links mysql's TESTDB database:

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

The output of the above script is as follows:

Database version : 5.5.20-log

---

Create a database table

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

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

---

The database insert operation

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

#!/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 examples 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 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:

#!/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 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:

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

---

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

---

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. O ther 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.