Detailed explanation of examples of Python's data operations on MySQL

This article introduces Python3 to use PyMySQL to connect to the database and implement simple additions, deletions, modifications and queries. Has very good reference value. Let’s take a look at it with the editor

This article introduces Python3 to use PyMySQL to connect to the database and implement simple additions, deletions, modifications and queries.

What is PyMySQL?

PyMySQL is a library used to connect to the MySQL server in Python 3.x. Mysqldb is used in Python 2.x.

PyMySQL installation

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

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

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

$ pip install PyMySQL

If your system does not support it pip command, you can use the following methods to install:

1. Use the git command to download and install the installation package (you can also download it manually):

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

2. If you need to specify 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

Note: Please Make sure you have root access to install the above modules.

Database connection

Before connecting to the database, please confirm the following:

• You have created the database TESTDB

• In the TESTDB database you have created the table EMPLOYEE

• 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. For Mysql database user authorization, please use the Grant command

• The PyMySQL module has been installed on your machine

Example:

The following example links to Mysql TESTDB database:

#!/usr/bin/python3
__author__ = 'mayi'
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()

Create database table

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

#!/usr/bin/python3
__author__ = 'mayi'
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 operation

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

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

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 in the following form:

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

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

Database query operation

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

• fetchone(): This method gets the next query result set. The result set is an object

• fetchall(): Receives all returned result rows

• rowcount: This is a read-only property and returns Number of rows affected after executing execute() method

#Example:

Query all data in the EMPLOYEE table whose salary field is greater than 1000 ：

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

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

Database update operation

The update operation is used to update the data in the data table. The following example will modify all the SEX fields in the TESTDB table to 'M', AGE Field increment by 1:

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

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 operation

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

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

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

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.

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 process is automatically started. database transactions.

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

Exception	Description
Warning	Triggered when there is a serious warning, for example, inserting data is Truncated and so on. 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: the connection is unexpectedly disconnected, the database name is not found, transaction processing fails, memory allocation errors, 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.
InternamError	Internal errors in the database, such as cursor failure, transaction synchronization failure, etc. Must be a DatabaseError subclass.
ProgrammingError	Programming errors, such as the data table (table) is not found or already exists, SQL statement syntax error, wrong number of parameters, 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, the rollback() function is used on the connection object, but the database does not support transactions or the transaction has been closed. Must be a subclass of DatabaseError.

The above is the detailed content of Detailed explanation of examples of Python's data operations on MySQL. For more information, please follow other related articles on the PHP Chinese website!