How to use MySQL in Python

How to use MySQL/MariaDB with Python, tutorial

by

MySQL is the most popular open-source database management system. Today we will look at the usage of MySQL in Python via module mysql-connector-python also known as mysql.connector.

Requirements

Before you start, make sure you have the following:

  • Basic Python 3 knowledge.
  • Python and Pip installed. If not, run this command (for Debian and Ubuntu):
    sudo apt update && sudo apt install python3 python3-pip
  • MySQL database. If you have no MySQL (MariaDB) installed, follow this tutorial till “Python venv” section to install it.

Once all prerequisites are met, proceed to the next step.

Installation

In this tutorial, we will use mysql-connector-python module. To install it run the following command (preferably in virtual environment):

pip3 install mysql-connector-python

If the above command completed with no errors, then Python MySQL connector is installed.

Basic usage of mysql-connector-python

Now let’s create a python script to perform queries on the database.

Initialization

First, import this module and connect to the database (use your credentials and database name):

import mysql.connector

db = mysql.connector.connect(
    host='localhost',
    user='name',
    passwd='your-password',
    database='db_name',
)

Next, create a cursor. It is an object used to manipulate data. Create it like this:

cursor = db.cursor()

If you read a large amount of data with your cursor, set the buffered argument of cursor to True in order to prevent “Unread result found” error: cursor = db.cursor(buffered=True)

Reading table row by row

When you need to read a table, you should select the needed rows from it. Then iterate over queried rows. Do it like this:

cursor.execute('SELECT * FROM table_name')
while row := cursor.fetchone():
    print(row)

The row is a tuple containing a table column. You can retrieve values from it by subscripting the row. Use square brackets for it like this: row[0]

The cursor.fetchone() method reads one result from previous query and returns it. We used new Python 3.8 “the walrus operator” which assigns a variable and returns the assigned. For older Python versions, you can use for loop with cursor.fetchall() method:

cursor.execute('SELECT * FROM table_name')
for row in cursor.fetchall():
    print(row)

In second case, the program reads the whole table at once, while in the first one it reads row by row.

Closing the connection

At the end of your script, you need to close the connection (the cursor). If you have several cursors, close them all. Use the close method for this:

cursor.close()

How to process table row by row

If you already know the basics of mysql-connector-python, move to the real-world use case of using this module.

To iterate over the table, use two cursor: a reader and a writer. The reader will fetch the table and the writer will update the data. In this example we will iterate over the users table from website database to uppercase the name field. Here is the full code:

import mysql.connector

db = mysql.connector.connect(
    host='localhost',
    user='name',
    passwd='your-password',
    database='website',
)
reader = db.cursor(buffered=True)
writer = db.cursor()

reader.execute('SELECT id, name FROM users')
while row := reader.fetchone():
    new_name = row[1].upper()
    writer.execute('UPDATE users SET name=%s WHERE id=%s', (new_name, row[0]))
    db.commit()

reader.close()
writer.close()

There are few things to note here:

  1. The cursor.execute() accepts optional params. They are used in places of %s tokens. You may wonder why we cannot use string concatenation here. By using %s (indirect formatting) we prevent SQL injection vulnerability in our application.
  2. The db.commit() function is used it to save changes made to the database.
  3. You can select any column you want using an SQL query. For example, you can use SELECT COUNT(*) FROM users and get user count using cursor.fetchone()[0]
Note: in this example we could use single MySQL query: UPDATE users SET name=UPPER(name);

For complex operations on data you should use Python. If you need to “find and replace” or something like this (convert names to uppercase) just use MySQL queries.

Conclusion

We learned how to use Python mysql.connector module to process data row-by-row. This is one of the most frequent real-life problems that you learned to solve. To recap, here is the layout of script that uses MySQL in Python:

  1. Initialize the connection, create the cursors.
  2. Run queries, process the table.
  3. Commit the changes, close the connection.

Leave a Comment