Python3 MySQL Database Connection - PyMySQL Driver

This article introduces the use of Python3PyMySQLto connect to the database and implement simple CRUD operations.

What is PyMySQL?

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

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 ensure 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:

$ pip3 install PyMySQL

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

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
$ # 现在你可以删除 PyMySQL* 目录

Note:Please ensure that you have root permissions to install the above modules.

During installation, the error prompt "ImportError: No module named setuptools" may appear, which means you have not installed setuptools. You can visithttps://pypi.python.org/pypi/setuptoolsto find the installation methods for 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 already created the database TESTDB.
  • You have already created the table EMPLOYEE in the TESTDB database.
  • The EMPLOYEE table fields are FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
  • The username used to connect to the TESTDB database is "testuser", and the password is "test123". You can set them yourself or directly use the root username and password. For MySQL database user authorization, please use the GRANT command.
  • On your machine, Python is already installed.pymysqlmodule.
  • If you are not familiar with SQL statements, you can visit ourSQL Basic Tutorial

Example:

The following example connects to the TESTDB database of MySQL:

Example (Python 3.0+)

#!/usr/bin/python3 import pymysql # Open 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() # Use the execute() method to execute SQL query 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 shown below to create the table EMPLOYEE:

Example (Python 3.0+)

#!/usr/bin/python3 import pymysql # Open 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() # Use the execute() method to execute SQL, drop the table if it exists cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") # Use a prepared statement to create the table 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 EMPLOYEE table:

Example (Python 3.0+)

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

The above example can also be written as follows:

Example (Python 3.0+)

#!/usr/bin/python3 import pymysql # Open database connection db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Use the cursor() method to obtain an 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: # Roll back when an error occurs 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 using the fetchone() method to get a single piece of data, and uses the fetchall() method to get 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 attribute, and returns the number of rows affected after executing the execute() method.

Example:

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

Example (Python 3.0+)

#!/usr/bin/python3 import pymysql # Open database connection db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Use the cursor() method to obtain an operation cursor cursor = db.cursor() # SQL query statement sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > %s" % (1000) try: # Execute 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 execution result of the above script is as follows:

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

Database Update Operation

Update operations are used to update data in data tables. The following example increments the AGE field by 1 for records where SEX is 'M' in the TESTDB table:

Example (Python 3.0+)

#!/usr/bin/python3 import pymysql # Open database connection db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Use the cursor() method to obtain an operation cursor cursor = db.cursor() # SQL update statement sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') try: # Execute SQL statement cursor.execute(sql) # Commit to the database db.commit() except: # Roll back when an error occurs db.rollback() # Close the database connection db.close()

Delete Operation

Delete operations are used to delete data in data tables. The following example demonstrates deleting all data in the EMPLOYEE table where AGE is greater than 20:

Example (Python 3.0+)

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

Execute Transactions

The transaction mechanism can ensure data consistency.

A transaction should have 4 attributes: atomicity, consistency, isolation, and durability. These four attributes are usually called ACID characteristics.

  • Atomicity. A transaction is an indivisible unit of work. The operations included in a transaction are either all performed or none are performed.
  • Consistency. A transaction must change the database from one consistent state to another. Consistency and atomicity are closely related.
  • Isolation. The execution of one transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from concurrent transactions, and concurrent transactions cannot interfere with each other.
  • Durability. Also known as permanence, it 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 effect on it.

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

Example

Example (Python 3.0+)

# SQL statement to delete records sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20) try: # Execute SQL statement cursor.execute(sql) # Commit to the database db.commit() except: # Roll back when an error occurs db.rollback()

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

The commit() method commits 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

The DB API defines some database operation errors and exceptions. The following table lists these errors and exceptions:

ExceptionDescription
WarningTriggered when there is a serious warning, for example when inserted data is truncated, etc. Must be a subclass of StandardError.
ErrorAll other error classes except warnings. Must be a subclass of StandardError.
InterfaceErrorTriggered when an error occurs in the database interface module itself (rather than a database error). Must be a subclass of Error.
DatabaseErrorTriggered when a database-related error occurs. Must be a subclass of Error.
DataErrorTriggered when a data processing error occurs, such as division by zero, data out of range, etc. Must be a subclass of DatabaseError.
OperationalErrorRefers to errors that are not user-controlled but occur when operating the database. For example: unexpected connection disconnection, database name not found, transaction processing failure, memory allocation error, and other errors that occur when operating the database. Must be a subclass of DatabaseError.
IntegrityErrorErrors related to integrity, such as foreign key check failures, etc. Must be a subclass of DatabaseError.
InternalErrorInternal database errors, such as an invalid cursor, transaction synchronization failure, etc. Must be a subclass of DatabaseError.
ProgrammingErrorProgram errors, such as a data table not found or already existing, SQL statement syntax errors, incorrect number of parameters, etc. Must be a subclass of DatabaseError.
NotSupportedErrorUnsupported error, referring to the use of functions or APIs not supported by the database. For example, calling the .rollback() function on a connection object when the database does not support transactions or the transaction has been closed. Must be a subclass of DatabaseError.

The following is the inheritance structure of exceptions:

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