Use Python to manipulate MySQL data

This article introduces that Python3 USES PyMySQL to connect to the database, and realizes simple addition, deletion, alteration and check.

What is PyMySQL?

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

PyMySQL installation

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

PyMySQL download address: https: / / github com/PyMySQL/PyMySQL.

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

$ pip install PyMySQL

If your system does not support the pip command, you can install it by:

1. Download the installation package using the git command (you can also download it manually) :

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

2. If you need a version number, you can use the curl command to install:

$ # X.X  for PyMySQL  The version number of the 
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install

Note: make sure you have root permissions to install the above module.

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 EMPLOYEE table fields are FIRST_NAME,LAST_NAME,AGE,SEX and INCOME The user name used to connect TESTDB to the database is "testuser", and the password is "test123". You can set or directly use the root user name and password. For the authorization of Mysql database users, please use the Grant command The PyMySQL module has been installed on your machine

Example:

The following example links to Mysql's TESTDB database:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

#  Open database connection 
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

#  use  cursor()  Method to create 1 Vernier objects  cursor
cursor = db.cursor()

#  use  execute()  Methods to perform  SQL  The query  
cursor.execute("SELECT VERSION()")

#  use  fetchone()  Method to get a single piece of data .
data = cursor.fetchone()

print ("Database version : %s " % data)

#  Close the database connection 
db.close()

Creating a database table

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

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

#  Open database connection 
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

#  use  cursor()  Method to create 1 Vernier objects  cursor
cursor = db.cursor()

#  use  execute()  Methods to perform  SQL , delete if the table exists 
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#  Create a table using a preprocessing statement 
sql = """CREATE TABLE EMPLOYEE (
 FIRST_NAME CHAR(20) NOT NULL,
 LAST_NAME CHAR(20),
 AGE INT, 
 SEX CHAR(1),
 INCOME FLOAT )"""

cursor.execute(sql)

#  Close the database connection 
db.close()

Database insert operation

The following example USES the execute SQL Insert statement to insert records into the table EMPLOYEE:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

#  Open database connection 
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

#  use cursor() Method to get the operation cursor  
cursor = db.cursor()

# SQL  Insert statement 
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
 LAST_NAME, AGE, SEX, INCOME)
 VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
 #  perform sql statements 
 cursor.execute(sql)
 #  Commit to the database for execution 
 db.commit()
except:
 #  Roll back if an error occurs 
 db.rollback()

#  Close the database connection 
db.close()

The above examples can also be written as follows:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

#  Open database connection 
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

#  use cursor() Method to get the operation cursor  
cursor = db.cursor()

# SQL  Insert statement 
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
 LAST_NAME, AGE, SEX, INCOME) \
 VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
 ('Mac', 'Mohan', 20, 'M', 2000)
try:
 #  perform sql statements 
 cursor.execute(sql)
 #  perform sql statements 
 db.commit()
except:
 #  Roll back when an error occurs 
 db.rollback()

#  Close the database connection 
db.close()

Database query operation

Python queries Mysql to obtain a single piece of data using the fetchone() method and multiple pieces of data using the fetchall() method.

fetchone() : this method gets the next query result set. The result set is 1 object fetchall() : receives all the returned result rows rowcount: this is a read-only property and returns the number of rows affected by the execution of the execute() method

Example:

Query all data in the EMPLOYEE table where the salary (salary) field is greater than 1000:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

#  Open database connection 
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

#  use cursor() Method to get the operation cursor  
cursor = db.cursor()

# SQL  The query 
sql = "SELECT * FROM EMPLOYEE \
 WHERE INCOME > '%d'" % (1000)
try:
 #  perform SQL statements 
 cursor.execute(sql)
 #  Gets a list of all records 
 results = cursor.fetchall()
 for row in results:
 fname = row[0]
 lname = row[1]
 age = row[2]
 sex = row[3]
 income = row[4]
 #  Print the result 
 print ("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
 (fname, lname, age, sex, income ))
except:
 print ("Error: unable to fecth data")

#  Close the database connection 
db.close()

Database update operation

The update operation is used to update the data in the data table. The following example modifies the SEX field in the TESTDB table to 'M', and the AGE field is incremented by 1:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

#  Open database connection 
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

#  use cursor() Method to get the operation cursor  
cursor = db.cursor()

# SQL  Update statement 
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
 WHERE SEX = '%c'" % ('M')
try:
 #  perform SQL statements 
 cursor.execute(sql)
 #  Commit to the database for execution 
 db.commit()
except:
 #  Roll back when an error occurs 
 db.rollback()

#  Close the database connection 
db.close()

Delete operation

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

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

#  Open database connection 
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

#  use cursor() Method to get the operation cursor  
cursor = db.cursor()

# SQL  Delete statements 
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
 #  perform SQL statements 
 cursor.execute(sql)
 #  Commit changes 
 db.commit()
except:
 #  Roll back when an error occurs 
 db.rollback()

#  Close the connection 
db.close()

Perform transactions

The transaction mechanism ensures data 1 alignment.

Transactions should have four properties: atomicity, 1 tropism, isolation, and persistence. These four properties are commonly referred to as ACID properties.

Atomicity (atomicity). A transaction is an indivisible unit of work in which all operations are either done or not done. 1 tropism (consistency). The transaction must be to change the database from one 1 - induced state to another 1 - induced state. 1 tropism is closely related to atomicity. Isolation (isolation). The execution of a transaction cannot be interrupted by other transactions. That is, the operation and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other. Persistence (durability). Persistence, also known as permanence (permanence), refers to the fact that when a transaction is committed, its changes to the data in the database should be permanent. Other operations or failures that follow should not have any impact on it.

The instance

# SQL Delete record statement 
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
 #  perform SQL statements 
 cursor.execute(sql)
 #  Commit to the database 
 db.commit()
except:
 #  Roll back when an error occurs 
 db.rollback()

For transaction-enabled databases, in Python database programming, an invisible database transaction is automatically started when the cursor is set up.

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

Error handling

异常	描述
Warning	当有严重警告时触发，例如插入数据是被截断等等。必须是StandardError的子类。
Error	警告以外所有其他错误类。必须是StandardError的子类。
InterfaceError	当有数据库接口模块本身的错误（而不是数据库的错误）发生时触发。必须是Error的子类。
DatabaseError	和数据库有关的错误发生时触发。必须是Error的子类。
DataError	当有数据处理时的错误发生时触发，例如：除零错误，数据超范围等等。必须是DatabaseError的子类。
OperationalError	指非用户控制的，而是操作数据库时发生的错误。例如：连接意外断开、数据库名未找到、事务处理失败、内存分配错误等等操作数据库是发生的错误。必须是DatabaseError的子类。
IntegrityError	完整性相关的错误，例如外键检查失败等。必须是DatabaseError子类。
InternamError	数据库的内部错误，例如游标（cursor）失效了、事务同步失败等等。必须是DatabaseError子类。
ProgrammingError	程序错误，例如数据表（table）没找到或已存在、SQL语句语法错误、参数数量错误等等。必须是DatabaseError的子类。
NotSupportedError	不支持错误，指使用了数据库不支持的函数或API等。例如在连接对象上使用rollback()函数，然而数据库并不支持事务或者事务已关闭。必须是DatabaseError的子类。