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