Python 2.7 – Working with MySQL databases

Overview

Most modern applications need to interact with a database some time or the other. The most common databases in use are MySQL if its a Linux platform and MS SQL Server or MS Access if its a Windows platform. In this article we look how to work with MySQL databases. This article assumes that you are already familiar with MySQL and know basic database commands and concepts.

There are quite a few Python libraries available for working with MySQL . For our purposes we will use PyMySQL because its easy to work with and its small. It can be installed with pip:

pip3 install PyMySQL

PyMySql Usage

This package has only a few classes:

• pymysql.connections.Connection – the main database connection class
• pymysql.cursors.Cursor – the default buffered cursor which returns data as Python lists
• pymysql.cursors.SSCursor – unbuffered cursor which returns data as Python lists but meant for handling large amounts of data or connecting to remote servers over a slow network
• pymysql.cursors.DictCursor – returns data as Python dictionaries instead of lists.
• pymysql.cursors.SSDictCursor – same as DictCursor but with the functionality of SSCursor

You can find the detailed documentation here: https://pymysql.readthedocs.io/en/latest/modules/index.html

Sample Code

Given below is sample code which shows how to interact with MySQL. Basically it is your responsibility to create the SQL queries and sanitize the data before passing it to the server since PyMySQL does not do things like string escaping or parametrization. The Python Database API specifications provides a lot of capabilities but it is up to the libraries that implement this specification, to decide how much of the specification they want to put in place.

What we have below is code which takes in a database connection and dumps out the details of all the tables within that database. It executes and parses the following SQL commands:

• SHOW TABLES
• DESCRIBE <table>
• SHOW INDEX FROM <table>

We use the DictCursor mode for all the SQL queries. This is specified when opening the database connection.

import pymysql.cursors


"""
 Get the list of all the tables 
"""
def getTables(connection):
    tables = []
    with connection.cursor() as cursor:
        sql = "show tables"
	cursor.execute(sql)
        result = cursor.fetchall()
	for row in result:
		for key, value in row.items():
			tables.append(value)
    return tables

"""
 Get all the info for a single table
"""
def getTableInfo(connection, tableName):
   with connection.cursor() as cursor:
        sql = "describe `" + tableName + "`"
	cursor.execute(sql)
        result = cursor.fetchall()
	for row in result:
		print row["Field"], " ", row["Type"], " ", row["Key"]

"""
  Get the list of all the indexes in a table
"""
def getTableIndexes(connection, tableName):
   with connection.cursor() as cursor:
        sql = "show index from  `" + tableName + "`"
	cursor.execute(sql)
        result = cursor.fetchall()
	print "Key name, Column"
	for row in result:
		print row["Key_name"], ",", row["Column_name"]





# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='******',
                             password='****',
                             db='sampledb',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
	tables = getTables(connection)
	
	for table in tables:
		print "====================================="
		print table
		print "....................................."
		getTableInfo(connection, table)
		print "....................................."
		print "Indexes"
		getTableIndexes(connection, table)
		print "\n\n"


 
finally:
    connection.close()

A part of the output is given below:

=====================================
Billing
.....................................
id bigint(20) PRI
person_id bigint(20) MUL
payment_type tinyint(4)
account_id varchar(100)
card_number varchar(20)
exp_month varchar(2)
exp_year varchar(4)
pfp_string varchar(200)
authorization_id varchar(200)
cvv_code varchar(10)
card_name varchar(100)
address_1 varchar(100)
address_2 varchar(100)
city varchar(30)
state varchar(30)
country varchar(30)
zip varchar(30)
status tinyint(4)
last_billing_type varchar(20)
last_billing_action varchar(20)
last_billing_error varchar(255)
last_billing_timestamp datetime
date_created timestamp
last_updated timestamp
.....................................
Indexes
Key name, Column
PRIMARY , id
BILLING_PERSON_ID , person_id

=====================================
Billing_Information
.....................................
id bigint(20) PRI
business_id bigint(20)
payment_type tinyint(4)
card_number varchar(20)
expiry_month varchar(2)
expiry_year varchar(4)
pfp_string varchar(50)
authorization_id varchar(50)
cvv_code varchar(10)
card_name varchar(100)
address_1 varchar(100)
address_2 varchar(100)
city varchar(30)
state varchar(30)
zip varchar(30)
active tinyint(4)
last_billing_type varchar(20)
last_paypal_action varchar(20)
last_paypal_error varchar(255)
last_paypal_timestamp datetime
.....................................
Indexes
Key name, Column
PRIMARY , id

=====================================
Bookmarks
.....................................
id bigint(20) PRI
person_id bigint(20) MUL
graph_id bigint(20) MUL
date_added datetime
.....................................
Indexes
Key name, Column
PRIMARY , id
person_id , person_id
graph_id , graph_id
person_id_graph_id , person_id
person_id_graph_id , graph_id

In the next articlewe see how to work with MS SQL Server databases.