PyMySQL CheatSheet

Example

Create a simple table

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1

Now try inserting and selecting

import pymysql.cursors

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

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

        # connection is not autocommit by default. So you must commit to save your changes.
        connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

Which will print

{'password': 'very-secret', 'id': 1}

Simple Example (row[0] etc)

import pymysql.cursors

dbhost = 'localhost'
dbase  = 'db1'
dbuser = 'user1'
dbpass = 'stupidPasswd'

connection = pymysql.connect(host=dbhost, user=dbuser, password=dbpass, db=dbase, charset='utf8')
name = 'barney'
sql = "SELECT id FROM " + table + " WHERE name=%s"

cursor = connection.cursor()
cursor.execute(sql, name.strip() )
for row in cursor:
    print(row['id'])

cursor.close()
connection.close()

Simple Example (row[‘id’] etc)

import pymysql.cursors

dbhost = 'localhost'
dbase  = 'db1'
dbuser = 'user1'
dbpass = get_password(dbuser) # assume method exists to get the password secret
connection = pymysql.connectionect(host=dbhost, user=dbuser, password=dbpass, db=dbase, charset='utf8', cursorsorclass=pymysql.cursorsors.Dictcursorsor)
name = 'barney'
sql = "SELECT id FROM " + table + " WHERE name=%s"

cursor = connection.cursorsor()
cursor.execute(sql, name.strip() )
for row in cursor:
    print(row['id'])
cursor.close()
connection.close()


# try select statement
cursor.execute("SHOW TABLES")
print(cursor.description)

# try insert statement
sql = "INSERT INTO `test` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('webmaster@python.org', 'very-secret'))
connection.commit()

cursor.close()
connection.close()

PlaceHolders

• use ‘%s’ (not ‘?’ like Perl DBI and other API’s)

Hint: following is an example of getting it wrong ‘?’ (instead of ‘%s’).

# Traceback (most recent call last):
#   File "./pymysql-test.py", line 81, in <module>
#     import_billing_info()
#   File "./pymysql-test.py", line 36, in import_billing_info
#     cur.execute(sql, ('9', '2500.0', '2016', '0', '1.0', '2500.0') )
#   File "/home/y/lib/python2.7/site-packages/pymysql/cursors.py", line 156, in execute
#     query = self.mogrify(query, args)
#   File "/home/y/lib/python2.7/site-packages/pymysql/cursors.py", line 135, in mogrify
#     query = query % self._escape_args(args, conn)
# TypeError: not all arguments converted during string formatting