Python3 MySQL database connection – PyMySQL driver

Python 3 MySQL Database Connection – PyMySQL Driver

This article introduces how to use Python 3 to connect to a database using PyMySQL and implement simple CRUD.

What is PyMySQL?

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

PyMySQL adheres to the Python Database API v2.0 specification and includes the 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’s not installed yet, we can install the latest version of PyMySQL using the following command:

$ pip3 install PyMySQL

If your system doesn’t support the pip command, you can install it using the following method:

1. Use the git command to download and install the 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 is the PyMySQL version number
$ 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* directory

Note: Make sure you have root privileges to install the above modules. During installation, you may receive the error “ImportError: No module named setuptools.” This means setuptools is not installed. You can visit https://pypi.python.org/pypi/setuptools to find installation instructions for various operating systems.

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 the TESTDB database, you have created the table EMPLOYEE.
  • The EMPLOYEE table has fields FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
  • Connecting to the TESTDB database uses the username “testuser” and the password “test123.” You can set your own username or use the root username and password. To grant MySQL database user authorization, use the Grant command.
  • The Python MySQLdb module has been installed on your machine.
  • If you are not familiar with SQL statements, you can visit our SQL Basics Tutorial.

Example:

The following example connects to the TESTDB database in MySQL:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

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

# Create a cursor object using the cursor() method
cursor = db.cursor()

# Execute using the execute() method href="https://geek-docs.com/sql/sql-top-tutorials/1000100_sql_index.html" rel="noopener" target="_blank" title="SQL Tutorial">SQL Query
cursor.execute("SELECT VERSION()")

# Use the fetchone() method to retrieve a single row of data.
data = cursor.fetchone()

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

# Close the database connection
db.close()

Executing the above script will produce the following output:

Database version: 5.5.20-log

Creating Database Tables

If a database connection exists, we can use the execute() method to create a table for the database. To create the EMPLOYEE table, follow these steps:

Example (Python) 3.0+)

#!/usr/bin/python3

import pymysql

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

# Use the cursor() method to create a cursor object
cursor = db.cursor()

# Use the execute() method to execute the SQL statement (SQL), deleting the table if it exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create a table using a 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 EMPLOYEE table:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

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

# Use the cursor() method to obtain 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 the SQL statement
cursor.execute(sql)
# Commit the statement to the database
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:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

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

# Use the cursor() method to obtain 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 the SQL statement
cursor.execute(sql)
# Execute the SQL statement
db.commit()
except:
# Rollback if an error occurs
db.rollback()

# Close the database connection
db.close()

The following code uses variables to pass parameters to the SQL statement:

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values( %s, %s)' %
(user_id, password))
..................................

Database Query Operations

Python queries MySQL use the fetchone() method to retrieve a single row of data, and the fetchall() method to retrieve multiple rows of data.

  • fetchone(): This method retrieves the next query result set. The result set is an object.
  • fetchall(): Receives all returned result rows.
  • rowcount: This is a read-only property that returns the number of rows affected by executing the execute() method.

Example:

Query all records in the EMPLOYEE table where the salary field is greater than 1000:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

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

# Use the cursor() method to obtain 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 results
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 in the TESTDB table where SEX is ‘M’. 1:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

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

# Use the cursor() method to obtain 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)
# Commit the execution to the database
db.commit()
except:
# Rollback if an error occurs
db.rollback()

# Close the database connection
db.close()

Delete operation

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

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

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

# Use the cursor() method to obtain 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 the changes
db.commit()
except:
# Rollback if an error occurs
db.rollback()

# Close the connection
db.close()

Executing Transactions

The transaction mechanism ensures data consistency.

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

  • Atomicity. A transaction is an indivisible unit of work; all operations within a transaction must be performed, or none must be performed.
  • Consistency. A transaction must move the database from one consistent 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 concurrently executing transactions cannot interfere with each other.
  • Durability. Durability, also known as permanence, means that once a transaction is committed, its changes to the database data are permanent. Subsequent operations or failures should not affect them.

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

Example

Example (Python 3.0+)

# SQL delete record statement
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
# Execute SQL statement
cursor.execute(sql)
# Commit to the database
db.commit()
except:
# Rollback if an error occurs
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 commits 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

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

Exception Description
Warning Triggered when a serious warning occurs, such as when inserted data is truncated. Must be a subclass of StandardError.
Error All other error classes except warnings. Must be a subclass of StandardError.
InterfaceError Triggered when an error occurs in the database interface module itself (not a database error). Must be a subclass of Error.
DatabaseError Triggered when a database-related error occurs. Must be a subclass of Error.
DataError Triggered when a data processing error occurs, such as division by zero, out-of-range data, etc. Must be a subclass of DatabaseError.
OperationalError Errors that are not user-controlled but occur during database operations. Examples include unexpected connection loss, database name not found, transaction failure, memory allocation error, and other database operations. Must be a subclass of DatabaseError.
IntegrityError Errors related to integrity, such as foreign key check failures. Must be a subclass of DatabaseError.
InternalError Internal database error, such as a cursor failure or transaction synchronization failure. Must be a subclass of DatabaseError.
ProgrammingError Programming error, such as a table not found or already exists, SQL statement syntax errors, an incorrect number of parameters, etc. Must be a subclass of DatabaseError.
NotSupportedError Unsupported error, which occurs when a function or API not supported by the database is used. For example, calling the .rollback() function on a connection object when the database does not support transactions or the transaction is closed. Must be a subclass of DatabaseError.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *