Python 3 – MySQL Database Access

Python 3 – MySQL Database Access

The standard for Python database interfaces is the Python DB-API. Most Python database interfaces follow this standard.

You can choose the right database for your application. Python The Database API supports various database servers, such as −

  • GadFly
  • mSQL
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server 2000
  • Informix
  • Interbase
  • Oracle
  • Sybase
  • SQLite

This is a list of available Python database interfaces: Python Database Interfaces and APIs . You must download a separate DB API module for each database you need to access. For example, if you need to access both Oracle and MySQL, you must download both the Oracle and MySQL database modules.

The DB API provides a minimal, standard way to work with databases using Python constructs and syntax. The API includes the following:

  • Importing the API module.
  • Obtaining a connection to the database.
  • Issuing SQL statements and stored procedures.
  • Closing the Connection

Python has built-in support for SQLite. In this section, we will learn all the concepts involved in using MySQL. The MySQLdb module, a popular interface to MySQL, is incompatible with Python. Instead, we will use the PyMySQL module.

What is PyMySQL?

PyMySQL is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and includes a pure-Python MySQL client library. PyMySQL aims to be a drop-in replacement for MySQLdb.

How to Install PyMySQL?

Before proceeding further, make sure you have PyMySQL installed on your computer. Simply type the following into a Python script and execute it: −

#!/usr/bin/python3

import pymysql

If it produces the following, it means the MySQLdb module is not installed: −

Traceback (most recent call last):
File "test.py", line 3, in <module>
Import pymysql
ImportError: No module named pymysql

The latest stable version is available on PyPI and can be installed using pip: −

pip install pymysql

Alternatively (if pip is not available), you can download it from GitHub Download the tarball and install it using Setuptools as follows −

$ # X.X is the desired pymysql version (e.g., 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # You can now safely delete the PyMySQL* folder.

Note − Ensure you have root privileges to install the above modules.

Database Connection

Before connecting to the MySQL database, ensure the following −

  • You have created the database TESTDB.
  • You have created the table EMPLOYEE in TESTDB.
  • The table has the fields FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
  • User ID “testuser” and password “test123” are set to access TESTDB.
  • The Python module PyMySQL is correctly installed on your computer.

Example

The following is an example of connecting to the MySQL database “TESTDB” −

#!/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
cursor = db.cursor()

# Use the execute() method to execute the SQL query
cursor.execute("SELECT VERSION()")

# Use the fetchone() method to retrieve a single piece of data.
data = cursor.fetchone()
print ("Database version: %s " % data)

# Close the database connection
db.close()

Running this script will produce the following output.

Database version: 5.5.20-log

If a connection is established with the data source, the Connection object is returned and saved to db for further use. Otherwise, db is set to None. Next, a cursor object is created using the db object, which is then used to execute a SQL query. Finally, before exiting, ensure that the database connection is closed and resources are released.

Creating Database Tables

Once a database connection is established, we can use the execute method of the created cursor to create tables or insert records into database tables.

Example

Let’s create a database table called EMPLOYEE –

#!/usr/bin/python3

import pymysql

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

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

# If the EMPLOYEE table already exists, delete it
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create a database table, for example
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()

Insert Operation

The insert operation is used to insert records into a database table.

Example

The following example uses an SQL INSERT statement to create a record in the EMPLOYEE table – 

#!/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()

# 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 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 be used to dynamically create a SQL query:

#!/usr/bin/python3

import pymysql

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

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

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

# Close the database connection
db.close()

Example

The following code snippet shows an alternative execution method. You can pass parameters directly –

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

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

Read Operations

A read operation on any database involves extracting some useful information from the database.

Once a database connection is established, you can query the database. You can use the fetchone() method to retrieve a single record, or the fetchall() method to retrieve multiple values from a database table.

  • fetchone() – This method retrieves the next row from a query result set. When a cursor object is used to query a table, a result set object is returned.

  • fetchall() – This method retrieves all rows from a result set. If some rows have already been fetched from the result set, the remaining rows are retrieved from the result set.

  • rowcount – This is a read-only property that returns the number of rows affected by the execute() method.

Example

The following procedure queries all records in the EMPLOYEE table whose salary exceeds $1,000 – 

#!/usr/bin/python3

import pymysql

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

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

# Prepare the SQL query to insert records into the database.
sql = "SELECT * FROM EMPLOYEE
WHERE INCOME > '%d'" % (1000)
try:
# Execute the SQL command
cursor.execute(sql)
# Get all rows from the list.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# Now print the fetched results
print ("fname = %s, lname = %s, age = %d, sex = %s, income = %d" %
(fname, lname, age, sex, income ))
except:
print ("Error: unable to fetch data")

# Disconnect from the server
db.close()

Output

This will produce the following results – 

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

Update Operations

An UPDATE operation on any database means updating one or more records already in the database.

The following procedure updates all records with a gender of ‘M’. Here, we increment the age of all males by one year.

Example

#!/usr/bin/python3

import pymysql

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

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

# Prepare the SQL query to update the required record
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
WHERE SEX = '%c'" % ('M')
try:
# Execute the SQL command
cursor.execute(sql)
# Commit the changes to the database
db.commit()
except:
# Rollback if there are any errors
db.rollback()

# Disconnect from the server
db.close()

Delete Operation

When you want to delete records from the database, you need to use the DELETE operation. Following is the procedure to delete all records from EMPLOYEE where age is greater than 20 –

Example

#!/usr/bin/python3

import pymysql

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

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

# Prepare SQL query to delete required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
# Execute SQL command
cursor.execute(sql)
# Commit changes in database
db.commit()
except:
# Rollback if any error occurs
db.rollback()

# Disconnect from the server
db.close()

Executing Transactions

Transactions are a mechanism for ensuring data consistency. Transactions have the following four properties:

  • Atomicity − A transaction either completes in full or completes without any completion.

  • Consistency − A transaction must begin in a consistent state and leave the system in a consistent state.

  • Isolation − Intermediate results of a transaction are not visible outside the current transaction.

  • Persistence − Once a transaction is committed, its effects are persistent, even in the event of a system failure.

The Python DB API 2.0 provides two methods, commit and rollback, for executing transactions.

Example

You already know how to implement transactions. Here is a similar example −

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()

COMMIT Operation

Commit is an operation that signals to the database that changes are complete. After this operation, no changes can be undone.

The following is a simple example of calling the commit method. 

db.commit()

ROLLBACK Operation

If you are not satisfied with one or more changes and want to completely revert them, use the rollback() method.

The following is a simple example of calling the rollback() method. 

db.rollback()

Disconnecting from the Database

To disconnect from the database, use the close() method. 

db.close()

If the user closes the connection to the database using the close() method, all incomplete transactions are rolled back by the database. However, rather than relying on any low-level database implementation details, it is best for your application to explicitly call commit or rollback.

Handling Errors

There are many sources of errors. Some examples are syntax errors in the executed SQL statement, connection failures, or calling the fetch method on a canceled or completed statement handle.

The DB API defines several error exceptions that must exist in every database module. The following table lists these exceptions.

Order Number Exception and Description
1 Warning For non-fatal problems. Must be a subclass of StandardError.
2 Error The base class for errors. Must be a subclass of StandardError.
3 InterfaceError This is for errors within the database module, not errors in the database itself. Must be a subclass of Error.
4 DatabaseError This is for errors within the database. Must be a subclass of Error.
5 DataError This is for errors within the data and is a subclass of DatabaseError.
6 OperationalError This is for errors such as lost connection to the database. These errors are generally beyond the control of the Python script programmer.
7 Integrity Error This refers to situations that may violate the integrity of a relationship, such as unique constraints or foreign keys. This is a subclass of DatabaseError.
8 Internal Error This refers to errors within the database module, such as a cursor that is no longer valid. This is a subclass of DatabaseError.
9 Programming Error This refers to errors caused by incorrect table names and other human errors. This is a subclass of DatabaseError.
10 Unsupported Error This refers to calls to unsupported functionality. This is a subclass of DatabaseError.

Your Python script should handle these errors. Before using any of the above exceptions, make sure your MySQLdb instance supports them. You can learn more about them by reading the DB API 2.0 specification.

Related Posts

Leave a Reply

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