Python3 MySQL database connection – PyMySQL driver

foreword

What is PyMySQL?

PyMySQL installation

Database Connectivity

Example:

Create database tables

database insert operation

Database query operation

Example:

Database update operation

delete operation

Execute business

example

error handling

---

Foreword

What is PyMySQL?

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

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

PyMySQL installation

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

PyMySQL download link: GitHub – PyMySQL/PyMySQL: Pure Python MySQL Client.

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

$ pip3 install PyMySQL

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

1. Use the git command to download the installation package and install it (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 the version number, you can use the curl command to install:

$ # X.X is the version number of PyMySQL
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install
$ # Now you can delete the PyMySQL* directories

Note: Please make sure you have root privileges to install the above modules.

During the installation process, the error message “ImportError: No module named setuptools” may appear, which means that you have not installed setuptools. You can visit setuptools · PyPI to find the installation method of each system.

Linux system installation example:

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

Database Connection

Before connecting to the database, please confirm the following:

• You have created the database TESTDB.
• In TESTDB database you have created table EMPLOYEE
• The EMPLOYEE table fields 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 use the root user name and its password directly. For Mysql database user authorization, please use the Grant command.
• The Python pymysql module is already installed on your machine.

Example:

The following example links Mysql’s TESTDB database:

#!/usr/bin/python3
 
import pymysql
 
# Open the database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')
 
# Use the cursor() method to create a cursor object cursor
cursor = db. cursor()
 
# Execute SQL query using execute() method
cursor. execute("SELECT VERSION()")
 
# Use the fetchone() method to get a single piece of data.
data = cursor. fetchone()
 
print ("Database version : %s " % data)
 
# Close the database connection
db. close()

The output of executing the above script is as follows:

Database version: 5.5.20-log

Create database table

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

#!/usr/bin/python3
 
import pymysql
 
# Open the database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')
 
# Use the cursor() method to create a cursor object cursor
cursor = db. cursor()
 
# Execute SQL using the execute() method, and delete the table if it exists
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
 
# create table using prepared 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 SQL INSERT statement to insert records into the table EMPLOYEE:

#!/usr/bin/python3
 
import pymysql
 
# Open the database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')
 
# Use the 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:
   # execute sql statement
   cursor. execute(sql)
   # Submit to the database for execution
   db. commit()
except:
   # rollback if an error occurs
   db. rollback()
 
# Close the database connection
db. close()

The above example can also be written as follows:

#!/usr/bin/python3
 
import pymysql
 
# Open the database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')
 
# Use the 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', %s, '%s', %s)" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # execute sql statement
   cursor. execute(sql)
   # execute sql statement
   db. commit()
except:
   # rollback on error
   db. rollback()
 
# Close the database connection
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))
...................................

Database query operation

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

• fetchone(): This method fetches the next query result set. The result set is an object
• fetchall(): Receive all 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 salary (salary) field greater than 1000 in the EMPLOYEE table:

#!/usr/bin/python3
 
import pymysql
 
# Open the database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')
 
# Use the cursor() method to get the operation cursor
cursor = db. cursor()
 
# SQL query statement
sql = "SELECT * FROM EMPLOYEE\
       WHERE INCOME > %s" % (1000)
try:
   # Execute the SQL statement
   cursor. execute(sql)
   # Get 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=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")
 
# Close the database connection
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 in the data table. The following example increments the AGE field whose SEX is ‘M’ in the TESTDB table by 1:

#!/usr/bin/python3
 
import pymysql
 
# Open the database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')
 
# Use the 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:
   # Execute the SQL statement
   cursor. execute(sql)
   # Submit to the database for execution
   db. commit()
except:
   # rollback on error
   db. rollback()
 
# Close the database connection
db. close()

Delete Action

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

#!/usr/bin/python3
 
import pymysql
 
# Open the database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')
 
# Use the cursor() method to get the operation cursor
cursor = db. cursor()
 
# SQL delete statement
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # Execute the SQL statement
   cursor. execute(sql)
   # Submit changes
   db. commit()
except:
   # rollback on error
   db. rollback()
 
# close the connection
db. close()

execute transaction

The transaction mechanism can ensure data consistency.

Transactions should have four attributes: atomicity, consistency, isolation, and durability. These four properties are often referred to as ACID properties.

• Atomicity. A transaction is an indivisible unit of work, and all operations included in the transaction are either performed or not performed.
• Consistency. A transaction must change the database from one consistent state to another. Consistency and atomicity are closely related.
• isolation. The execution of a transaction cannot be interfered by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrently executed transactions cannot interfere with each other.
• Durability. Persistence, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent other operations or failures should not have any effect on it.

The transaction of Python DB API 2.0 provides two methods commit or rollback.

Instance

# SQL delete record statement
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # Execute the SQL statement
   cursor. execute(sql)
   # Submit to the database
   db. commit()
except:
   # rollback on error
   db.rollback()

For databases that support transactions, in Python database programming, when a cursor is created, an invisible database transaction is automatically started.

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

Error Handling

Some database operation errors and exceptions are defined in the DB API. The following table lists these errors and exceptions:

Exception	Description
Warning	Triggered when there is a serious warning, For example, the inserted data is truncated and so on. Must be a subclass of StandardError.
Error	All other error classes except warnings. Must be a subclass of StandardError.
InterfaceError	Triggered when there is an error in the database interface module itself (rather than 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 an error that is not controlled by the user, but occurs when operating the database. For example: the connection is disconnected unexpectedly, the database name is not found, the transaction processing fails, the memory allocation error, etc. are the errors that occur when operating the database. Must be a subclass of DatabaseError.
IntegrityError	Integrity-related errors, such as foreign key check failures, 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	Procedural error, such as data table (table) not found or already exists, SQL statement syntax error, parameter number error, etc. Must be a subclass of DatabaseError.
NotSupportedError	Does not support errors, refers to the use of functions or APIs that are not supported by the database. For example, use the .rollback() function on the connection object, but the database does not support transactions or the transaction is closed. Must be a subclass of DatabaseError.

The following is the exception inheritance structure:

Exception
|__Warning
|__Error
   |__InterfaceError
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError