Using PyMySQL as MySQL Driver for Python3

When it comes to DBMS am pretty cool with MySQL, having used it for long (since I started programming though) I haven’t gone for another or tested another yet, so each language I move into I try to see if it incorporates MySQL. Back there in PHP I was pretty cool with it, now in Python I’ve got to find a way.

In Python there are different MySQL drivers but I chose one, of course I will choose what is good for me and I guess it’s gonna be good for you since MySQL Python Connector does not support Python3 (I code with Python3). Going for pyMySQL, these are reasons why I chose it:

  • Works more like default MySQL Python Connector, same syntax (more like a replacement)
  • Communication with database is faster
  • It’s simple and easy to understand

Installing PyMySQL:
We need pip here to install which is the default repo of your Linux distro, incase you don’t have pip installed already; install with

$ sudo apt-get install pip3

Then install PyMySQL with

$ sudo pip3 install PyMySQL

Using PyMySQL:
It’s quite easy using as mentioned before, lets try some examples by creating our first test table

$ mysql -u user -pPassword -e "CREATE DATABASE test"

Using PyMySQL we shall create our table

#!/usr/bin/env python3

#Importing needed modules of PyMySQL
from pymysql import connect, err, sys, cursors

#Doing our connection
conn = connect( host = 'localhost',
						port = 3306,
						user = 'user',
						passwd = 'Password',
						db = 'test' );
#Setting cursors and defining type of returns we need from Database, here it's gonna be returning as dictionary data
cursor = conn.cursor( cursors.DictCursor );

#Running a query easily
cursor.execute( """CREATE TABLE IF NOT EXISTS `test_user`
	(
		`ID` INT AUTO_INCREMENT NOT NULL,
		`user_name` VARCHAR( 200 ) NOT NULL,
		`user_email` VARCHAR( 200 ) NOT NULL,
		PRIMARY KEY ( `ID` )
	)Engine = MyISAM""" )
cursor.execute( "INSERT INTO `test_user` ( `user_name`, `user_email` ) VALUES( 'donjajo', '[email protected]' ) ")

Running the Python code above returns nothing but creates the tables and columns specified.

Returning Data from Database:
Executing this code just below the test code above

cursor.execute( "SELECT * FROM `test_user`" )
data = cursor.fetchall()
print( data )

Returns data similar to

[{'ID': 1, 'user_email': '[email protected]', 'user_name': 'donjajo'}]

Which you can loop through with for loop statement.

Escaping Strings:
Executing direct strings with no escaping gives error like example below:

user = "don'jajo"
cursor.execute( "SELECT * FROM `test_user` WHERE `user_name` = " + user )
data = cursor.fetchall()
print( data )

But doing escaping goes right, which the second argument must be a tuple

user = "donjajo"
cursor.execute( "SELECT * FROM `test_user` WHERE `user_name` = %s", ( user ) )
data = cursor.fetchall()
print( data )

The above is a simple example of using PyMySQL in Python3 – See more docs