Using PyMySQL as MySQL Driver for Python3

April 2, 2015
Linux, Python
No comments

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( &quot;&quot;&quot;CREATE TABLE IF NOT EXISTS <code>test_user</code>
	(
		<code>ID</code> INT AUTO_INCREMENT NOT NULL,
		<code>user_name</code> VARCHAR( 200 ) NOT NULL,
		<code>user_email</code> VARCHAR( 200 ) NOT NULL,
		PRIMARY KEY ( <code>ID</code> )
	)Engine = MyISAM&quot;&quot;&quot; )
cursor.execute( &quot;INSERT INTO <code>test_user</code> ( <code>user_name</code>, <code>user_email</code> ) VALUES( 'donjajo', '[email protected]' ) &quot;)

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( &quot;SELECT * FROM <code>test_user</code>&quot; )
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 = &quot;don'jajo&quot;
cursor.execute( &quot;SELECT * FROM <code>test_user</code> WHERE <code>user_name</code> = &quot; + user )
data = cursor.fetchall()
print( data )

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

user = &quot;donjajo&quot;
cursor.execute( &quot;SELECT * FROM <code>test_user</code> WHERE <code>user_name</code> = %s&quot;, ( user ) )
data = cursor.fetchall()
print( data )

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

James John

Software Engineer

Using PyMySQL as MySQL Driver for Python3

Related

James John

Related Posts