Introduction
MySQL is an ubiquitous database server. Many hosts, like Interserver.net offer unlimited MySQL databases with a $4/month plan. With their hosting, you can also setup as many PHP, Python, Ruby, or Node.js apps.
There are a few libraries available, but the one I'm focusing on is PyMySQL, a pure Python implementation. This version is the easiest to install with the least amount of dependencies and most portable.
You could use an ORM like SQLAlchemy or Django. Those options are good if you need to easily switch between MySQL and another database like SQLite. Here, we will focus only on MySQL and assume this is the only database being used.
If you're interested in learning how to setup a Python web app on Interserver, it is really easy using cPanel. Check out my tutorial Setup Python WSGI apps on cPanel (Flask/Django).
Installation
Install pymysql package from pypi.org with pip like this:
# Python 3.5+ required
python -m pip install pymysql
python -m pip install pymysql[rsa] # To use sha256 password option
If you prefer to install it from source, you can clone the repository
from GitHub and use the setup.py file to install it.
git clone https://github.com/PyMySQL/PyMySQL
cd PyMySQL
python setup.py install
Common tasks
Let's look at the most common tasks you will perform when working with MySQL in Python. This assumes you already have a MySQL server setup with a user that has permissions to operate on the database. If you need a database, check out the hosting at Interserver.net.
Connect to a database
This example will demonstrate how to connect to a database with the basic options.
Tip: Don't put passwords directly in your code files. Put passwords in a separate settings file that is ignored by version control. For example, a settings.py file that is ignored in .gitignore.
from pymysql import connect
DB_HOST = 'localhost' # IP or hostname of database
DB_NAME = 'my_test_db' # Name of the database to use
DB_USER = 'nanodano' # Username for accessing database
DB_PASS = 'My-$ecr3t' # Password for database user
db_connection = connect(host=DB_HOST,
user=DB_USER,
password=DB_PASS,
db=DB_NAME)
db_connection.close()
Optionally, there are many many more options you can set on the Connection object.
For example, in the connect() function you could also pass:
charset='utf8mb4', # UTF-8 multi-byte character set
cursorclass=pymysql.cursors.DictCursor, # Will allow dictionary access of results
If you get an error saying user is denied, you may need to grant your user remote access to the database.
In cPanel, you can fix this by going to the "Remote MySQL" option under "Databases" and adding your hostname, IP, or the % wildcard.
If you want to create a user using MySQL, the statement is:
GRANT ALL PRIVILEGES ON mydb.* TO nanodano@% IDENTIFIED BY 'mypassword';
The % will allow the nanodano user to connect from any remote host. Replace % with a specific IP or hostname to be more specific.
Create a table
This example will build on top of the previous example and after it connects, it will create a table.
After you obtain a database connection, you must open a cursor which will let you execute statements and track the results. In my example, I am using a with statement so it will automatically close the cursor when we're done with the block, otherwise you will need to manually close the cursor.
The execute statement simply returns 0 for a create table statement and will typically not even be stored.
Note that the CREATE TABLE statment does not require a commit for the changes to be stored, but
other statements are done as transactions and must be committed as you will see later.
from pymysql import connect
db_connection = connect(host='localhost',
user='nanodano',
password='My-$ecr3t',
db='my_test_db')
with db_connection.cursor() as cursor:
statement = """
CREATE TABLE IF NOT EXISTS users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255),
password VARCHAR(255),
email VARCHAR(255)
) ENGINE=INNODB
"""
result = cursor.execute(statement)
print(type(result)) # <class 'int'>
print(result) # 0
db_connection.close()
Insert rows
This example will demonstrate how to insert rows in to a database table.
It will use a cursor to execute the insert statement. The insert statement
will return the number of rows inserted. You can use cursor.lastrowid to
get the ID of the last row inserted. The execute statment returns the number of rows inserted.
It is very important to remember that the transaction is only temporary until you commit it.
You can do that by calling commit() on the database shown at the end of the script.
from pymysql import connect
db_connection = connect(host='localhost',
user='nanodano',
password='My-$ecr3t',
db='my_test_db')
with db_connection.cursor() as cursor:
statement = """
INSERT INTO users
(username, password, email)
VALUES
('nanodano', 'mysecret', 'nanodano@devdungeon.com');
"""
result = cursor.execute(statement)
print(type(result)) # <class 'int'>
print(result) # 1 (number of rows)
# Get ID of last row inserted
print(f'Last row ID inserted: {cursor.lastrowid}')
db_connection.commit()
db_connection.close()
Update rows
This example demonstrates how to update existing row(s). It is just like the previous example where we inserted rows, except instead of providing hard-coded values like in the previous example, we pass a variable for the set email clause. This is the safest option when dealing with any values that are provided from the user. Never insert user provided data in to your statement as a simple concatenation and always used the parameterized queries like this demonstrates.
The %s inserts a string variable. The variables to insert in the statement are passed as a tuple to the second argument in the cursor.execute() function call. The execute statement returns the number of rows that were modified.
Note you must also commit after performing an update.
from pymysql import connect
db_connection = connect(host='localhost',
user='nanodano',
password='My-$ecr3t',
db='my_test_db')
with db_connection.cursor() as cursor:
statement = """
UPDATE users
SET email=%s
WHERE username='nanodano';
"""
result = cursor.execute(statement, ('nanodano@devdungeon.com',))
print(type(result)) # <class 'int'>
print(result) # Number of rows modified
db_connection.commit()
db_connection.close()
Delete rows
This next example demonstrates how to delete a row.
You execute a DELETE statement just like the previous examples.
The results will return the number of rows deleted.
The transaction must be committed for the changes to become permanent.
from pymysql import connect
db_connection = connect(host='localhost',
user='nanodano',
password='My-$ecr3t',
db='my_test_db')
with db_connection.cursor() as cursor:
statement = """
DELETE FROM users
WHERE username=%s;
"""
result = cursor.execute(statement, ('nanodano',))
print(type(result)) # <class 'int'>
print(result) # Number of rows deleted
db_connection.commit()
db_connection.close()
Roll back a transaction
In all the previous examples, we have run commit() to make our changes stay.
If you decide you don't want to go through with the transaction and you want to
cancel any changes you made since the last commit, you can
rollback with rollback() on the database connection.
To deminstrate this, let's take the previous example with the delete command and rollback instead of committing, leaving the row instead of deleting it permanently.
from pymysql import connect
db_connection = connect(host='localhost',
user='nanodano',
password='My-$ecr3t',
db='my_test_db')
with db_connection.cursor() as cursor:
statement = """
DELETE FROM users
WHERE username=%s;
"""
result = cursor.execute(statement, ('nanodano',))
print(type(result)) # <class 'int'>
print(result) # Number of rows deleted
db_connection.rollback() # Roll back instead of committing the changes
db_connection.close()
Execute many statements at once
For convenience, there is an executemany() function on the
cursor object. This works just like the execute() function,
except when you pass the arguments as the second parameter,
you pass a list of tuples instead of just one.
from pymysql import connect
db_connection = connect(host='localhost',
user='nanodano',
password='My-$ecr3t',
db='my_test_db')
with db_connection.cursor() as cursor:
users_to_insert = [
('nanodano', 'Pa$$', 'nanodano@devdungeon.com'),
('admin', '$ecret', 'admin@devdungeon.com'),
('test_user', 'test123!', 'tester@devdungeon.com'),
]
statement = """
INSERT INTO users
(username, password, email)
VALUES
(%s, %s, %s)
"""
result = cursor.executemany(statement, users_to_insert)
db_connection.commit()
db_connection.close()
Query data
So far, all of the statements we have looked at make changes to the database in some way: creating a table, inserting data, updating and deleting rows. The results usually return a simple integer, a 0 or the number of rows modified. Those types of statements all require commits too because they change the database.
When you simply need to read the data, using a SELECT statement, you do not
need to commit anything since there are no changes being made.
Additionally, retrieving the data is a bit more involved then running any of the other statements.
This is more complicated because you have to check if there are any results, fetch the results, and extract the data for each column.
In this example, we'll show how to query for rows using a SELECT statement.
The execute statement will return an integer with the number of rows selected.
The row data itself must be pulled from the cursor object using fetchone(), fetchmany(), or fetchall().
You can scroll the cursor forward or backward just like seeking a position in a file using scroll() on the cursor object. This is demonstrated in the next code example to show how you can reset the cursor back to the beginning.
The data comes back as a tuple, ordered by the columns from the database.
If you want to get the name of the columns in order, you can get it from the description property
of the cursor object. Note the description also has more details than just the column names, like its size.
from pymysql import connect
db_connection = connect(host='localhost',
user='nanodano',
password='My-$ecr3t',
db='my_test_db')
with db_connection.cursor() as cursor:
results = cursor.execute("SELECT * FROM users")
print(type(results)) # <class 'int'>
print(results) # Number of rows selected
# Extract data from the cursor
single_row = cursor.fetchone() # or `fetchmany()`
print(type(single_row)) # <class 'tuple'>
print(single_row) # Column data, e.g. (9, 'nanodano', 'mysecret', 'nanodano@devdungeon.com')
# You can move the cursor back to the beginning (or a 'relative' position)
# Moving it back to the beginning will let you iterate through the results again
cursor.scroll(0, 'absolute')
# You can also use `fetchall()` and iterate through list of tuples
for row in cursor.fetchall():
print(row)
# Get the column names for the row tuples
for column_details in cursor.description:
print(column_details[0]) # Print column name
db_connection.close()
Get dictionary results instead of tuple
In the last example we saw how the cursor returns tuples with our row data,
and we have to use
the description property from the cursor object to find the column names.
It returns tuples by default to be more efficient. If you prefer,
you can have the cursor return a dictionary object with the key as
the column name and the value as the row data.
We can tell PyMySQL to return a dictionary result by specifying in the
connection call: cursorclass=pymysql.cursors.DictCursor.
Then instead of a tuple, we will get a dictionary for each row.
from pymysql import connect
from pymysql.cursors import DictCursor
db_connection = connect(host='localhost',
user='nanodano',
password='My-$ecr3t',
db='my_test_db',
cursorclass=DictCursor)
with db_connection.cursor() as cursor:
statement = "SELECT * FROM users"
cursor.execute(statement)
for row in cursor.fetchall():
print(type(row)) # <class 'dict'>
print(row) # {'id': 9, 'username': 'nanodano', 'password': 'mysecret', 'email': 'nanodano@devdungeon.com'}
print(row['id'])
print(row['username'])
print(row['email'])
db_connection.close()
Conclusion
After reading this guide, you should understand how to use MySQL in Python for common tasks like creating tables, inserting rows, updating rows, deleting rows, and querying data.