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
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.
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.
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
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)
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:
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.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:
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.upper() writer.execute('UPDATE users SET name=%s WHERE id=%s', (new_name, row)) db.commit() reader.close() writer.close()
There are few things to note here:
cursor.execute()accepts optional params. They are used in places of
%stokens. You may wonder why we cannot use string concatenation here. By using
%s(indirect formatting) we prevent SQL injection vulnerability in our application.
db.commit()function is used it to save changes made to the database.
- You can select any column you want using an SQL query. For example, you can use
SELECT COUNT(*) FROM usersand get user count using
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.
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:
- Initialize the connection, create the cursors.
- Run queries, process the table.
- Commit the changes, close the connection.