Getting Started with MySQL in Python

MySQL is one of the most popular relational database in the world. I started my career as a web developer and used PHP with MySQL intensively in the past. When I transitioned to Python I wanted to work with MySQL as well. This post is a small guide for those who want to query MySQL using Python. I have written the similar article about PostgreSQL & Python.

Installation

We are going to use PyMySQL package. This is a pure python implementation hence you do not need to install any additional C-libraries and headers. To install the package you need to:

pip install PyMySQL

Querying a database

To connect with MySQL using Python you have to call connect function:

import pymysql
from pymysql.cursors import DictCursor
connection = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    db='iata',
    charset='utf8mb4',
    cursorclass=DictCursor
)
...
connection.close()

I use DictCursor to get the results as a list of dictionaries where the key is a column name. If you prefer tuples just skip the cursorclass.

When you are ready to move forward you have to close the connection with a database. Some developers prefer using try/except but I prefer closing context manager:

from contextlib import closing
import pymysql
from pymysql.cursors import DictCursor
with closing(pymysql.connect(...)) as connection:
    ...

In order to query a database you have to get a cursor:

with closing(pymysql.connect(...)) as connection:
    with connection.cursor() as cursor:
        query = """
        SELECT
            airport_code
        FROM
            airports
        ORDER BY 
            airport_code DESC
        LIMIT 5
        """
        cursor.execute(query)
        for row in cursor:
            print(row)

# output
{'airport_code': 'ZZW'}
{'airport_code': 'ZZU'}
{'airport_code': 'ZZO'}
{'airport_code': 'ZZG'}
{'airport_code': 'ZYR'}

Let's create the simple table with a single column:

CREATE TABLE IF NOT EXISTS tweets (
    tweet VARCHAR(254)
)  ENGINE=INNODB; 

Now, let's insert some data:

with closing(pymysql.connect(...)) as conn:
    with conn.cursor() as cursor:
        tweets = [
            'Hello world!',
            'I love Python & MySQL',
            'Let\'s start programming ASAP',
            'Python is the coolest programming language'
        ]
        query = 'INSERT INTO tweets (tweet) VALUES (%s)'
        cursor.executemany(query, tweets)
        # you have to commit before querying the result
        conn.commit()

    with conn.cursor() as cursor:
        query = 'SELECT tweet FROM tweets'
        cursor.execute(query)
        for row in cursor:
            print(row['tweet'])

The output:

Hello world!
I love Python & MySQL
Let's start programming ASAP
Python is the coolest programming language