How To Use MySQL and MariaDB In Python

How To Use MySQL and MariaDB In Python

MySQL and MariaDB are relational database engines that are very popular systems used with Python. They use a relational model with tables to describe relationships between data. MySQL and MariaDB follow a client-server relationship. In other words, the database exists on a server. To make changes to the database, you send a request to the server. The request contains various SQL statements or instructions selecting, updating, or deleting data. The server then accepts that request, performs the action, and sends a response back to the client with the results. In this tutorial, we’ll see how to connect to MariaDB using Python to create, read, update, and delete data.


Creating Database And Tables

To get started, we can first create the database and tables we need to store some projects and tasks. We can start right at the command line of the database.

# mysql -u root
Welcome to the MariaDB monitor.  Commands end with; or \g.
Your MariaDB connection id is 54
Server version: 10.4.18-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab, and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database projects;
Query OK, 1 row affected (0.003 sec)

MariaDB [(none)]> use projects;
Database changed

MariaDB [projects]> create table projects(project_id INT(11) not null auto_increment, title varchar(30), description varchar(255), primary key(project_id));
Query OK, 0 rows affected (0.027 sec)

MariaDB [projects]> show tables;
+--------------------+
| Tables_in_projects |
+--------------------+
| projects           |
+--------------------+
1 row in set (0.001 sec)

MariaDB [projects]> create table tasks(task_id INT(11) not null auto_increment, project_id INT(11) not null, description varchar(255), primary key(task_id), foreign key(project_id) references projects(project_id));
Query OK, 0 rows affected (0.025 sec)

MariaDB [projects]> show tables;
+--------------------+
| Tables_in_projects |
+--------------------+
| projects           |
| tasks              |
+--------------------+
2 rows in set (0.012 sec)

Adding Some Sample Data

Now let’s add some data in the form of projects and tasks to the database. The two tables are related to each other via the foreign key that we configured on the tasks table. Therefore, any time we add new tasks, we must specify the ID of the project that the task is related to. This is how relational database management systems work.

MariaDB [projects]> insert into projects(title, description) values ("Plant Lettuce", "Move lettuces seedlings from nursery to garden");
Query OK, 1 row affected (0.006 sec)

MariaDB [projects]> insert into tasks(description, project_id) values ("Prepare row for Great Lakes Lettuce", 1);
Query OK, 1 row affected (0.006 sec)

MariaDB [projects]> insert into tasks(description, project_id) values ("Prepare row for Lolla Rosa Lettuce", 1);
Query OK, 1 row affected (0.004 sec)

MariaDB [projects]> insert into projects(title, description) values ("Lawn Care", "Take care of various lawn tasks");
Query OK, 1 row affected (0.004 sec)

MariaDB [projects]> insert into tasks(description, project_id) values ("Fertilize Lawn", 2);
Query OK, 1 row affected (0.004 sec)

MariaDB [projects]> insert into tasks(description, project_id) values ("Mow the Lawn", 2);
Query OK, 1 row affected (0.004 sec)

Viewing Database Records

Viewing the records from the command line shows that we’re ready to start working with this database from Python.

MariaDB [projects]> select * from projects;
+------------+---------------+------------------------------------------------+
| project_id | title         | description                                    |
+------------+---------------+------------------------------------------------+
|          1 | Plant Lettuce | Move lettuces seedlings from nursery to garden |
|          2 | Lawn Care     | Take care of various lawn tasks                |
+------------+---------------+------------------------------------------------+
2 rows in set (0.001 sec)

MariaDB [projects]> select * from tasks;
+---------+------------+-------------------------------------+
| task_id | project_id | description                         |
+---------+------------+-------------------------------------+
|       1 |          1 | Prepare row for Great Lakes Lettuce |
|       2 |          1 | Prepare row for Lolla Rosa Lettuce  |
|       3 |          2 | Fertilize Lawn                      |
|       4 |          2 | Mow the Lawn                        |
+---------+------------+-------------------------------------+
4 rows in set (0.000 sec)

Connect To MySql Using Python

To connect to MySQL using Python, we can use the Python MySql Connector. It can be installed using pip install mysql-connector-python. Once it is installed, the code below shows how to use the connect() function to establish a connection to the database.

import mysql.connector as mysql

db = mysql.connect(
    host='localhost',
    user='root',
    password='',
    database='projects'
)

print(db)
<mysql.connector.connection.MySQLConnection object at 0x000002586BDF43A0>

Use The Cursor To Execute SQL Queries

The connection now gives us access to the cursor(), which is how we can execute SQL queries against the database we are connected to.

import mysql.connector as mysql

db = mysql.connect(
    host='localhost',
    user='root',
    password='',
    database='projects'
)

cursor = db.cursor()
cursor.execute("select * from projects")

Fetch Records Using Cursor fetchall()

Executing the query does not immediately return the results of the query. To access the results from the query, we can use the fetchall() function as we see below. When we print out those results, we can see both of the projects that we had already manually entered into the database. Cool!

import mysql.connector as mysql

db = mysql.connect(
    host='localhost',
    user='root',
    password='',
    database='projects'
)

cursor = db.cursor()
cursor.execute("select * from projects")

project_records = cursor.fetchall()
print(project_records)

db.close()
[(1, 'Plant Lettuce', 'Move lettuces seedlings from nursery to garden'), (2, 'Lawn Care', 'Take care of various lawn tasks')]

Database Operations In Functions

What are some of the things we want to be able to do with the database from Python? We want to be able to view all projects, view all tasks, add a new project, and so on. We can put these operations inside of a python function and then just call the function when we want to complete one of those operations. Here is the function to view all projects.

import mysql.connector as mysql

db = mysql.connect(
    host='localhost',
    user='root',
    password='',
    database='projects'
)


def view_projects():
    cursor = db.cursor()
    cursor.execute("select * from projects")
    project_records = cursor.fetchall()
    print(project_records)


view_projects()

db.close()
[(1, 'Plant Lettuce', 'Move lettuces seedlings from nursery to garden'), (2, 'Lawn Care', 'Take care of various lawn tasks')]

Viewing All Tasks

This function will display all the tasks in the database.

def view_tasks():
    cursor = db.cursor()
    cursor.execute("select * from tasks")
    task_records = cursor.fetchall()
    print(task_records)


view_tasks()
[(1, 1, 'Prepare row for Great Lakes Lettuce'),
 (2, 1, 'Prepare row for Lolla Rosa Lettuce'), 
(3, 2, 'Fertilize Lawn'), 
(4, 2, 'Mow the Lawn')]

Inserting Records

This function inserts a new project and its associated tasks. This is a little bit more tricky because we need to insert into two tables in the same function. Each project has a title and description, so we use execute() to insert the project. Then, we need to grab the ID that was used on that insert using cursor.lastrowid. This is needed so that when we start inserting the related tasks, we use the correct project id that the task is related to. For the tasks, we accept a list of the tasks we need to do for the project. Then we build a tuple with those tasks, and use the executemany() function to insert the data.

import mysql.connector as mysql

db = mysql.connect(
    host='localhost',
    user='root',
    password='',
    database='projects'
)


def insert_project(title, description, tasks):
    project_data = (title, description)
    cursor = db.cursor()
    cursor.execute('''
    insert into projects(title, description) values (%s, %s)
    ''', project_data)

    project_id = cursor.lastrowid

    tasks_data = []

    for description in tasks:
        task_data = (project_id, description)
        tasks_data.append(task_data)

    cursor.executemany('''
    insert into tasks(project_id, description) values (%s, %s)
    ''', tasks_data)


tasks = ["Go to store", "Buy Paint", "Start Painting"]
insert_project("Paint Shed", "New Coat Of Paint On Shed", tasks)
db.commit()

db.close()

We can now go back to the view_projects() function to see that the new project we just inserted was successful. Note each project has its own unique ID.

import mysql.connector as mysql

db = mysql.connect(
    host='localhost',
    user='root',
    password='',
    database='projects'
)


def view_projects():
    cursor = db.cursor()
    cursor.execute("select * from projects")
    project_records = cursor.fetchall()
    print(project_records)


view_projects()

db.close()
[(1, 'Plant Lettuce', 'Move lettuces seedlings from nursery to garden'), 
(2, 'Lawn Care', 'Take care of various lawn tasks'), 
(3, 'Paint Shed', 'New Coat Of Paint On Shed')]

The same holds true for viewing all related tasks.

import mysql.connector as mysql

db = mysql.connect(
    host='localhost',
    user='root',
    password='',
    database='projects'
)


def view_tasks():
    cursor = db.cursor()
    cursor.execute("select * from tasks")
    task_records = cursor.fetchall()
    print(task_records)


view_tasks()

db.close()
[(1, 1, 'Prepare row for Great Lakes Lettuce'), 
(2, 1, 'Prepare row for Lolla Rosa Lettuce'), 
(3, 2, 'Fertilize Lawn'), 
(4, 2, 'Mow the Lawn'), 
(8, 3, 'Go to store'), 
(9, 3, 'Buy Paint'), 
(10, 3, 'Start Painting')]

Python MySql Query With Variable

By using a variable with the query, we can look at specific records rather than just selecting all records. The function below lets us view tasks that are associated with a given project id.

import mysql.connector as mysql

db = mysql.connect(
    host='localhost',
    user='root',
    password='',
    database='projects'
)


def view_project_tasks(id):
    cursor = db.cursor()
    cursor.execute("select * from tasks where project_id = %s", (id,))
    task_records = cursor.fetchall()
    print(task_records)


view_project_tasks(1)
view_project_tasks(2)
view_project_tasks(3)

db.close()
[(1, 1, 'Prepare row for Great Lakes Lettuce'), (2, 1, 'Prepare row for Lolla Rosa Lettuce')]
[(3, 2, 'Fertilize Lawn'), (4, 2, 'Mow the Lawn')]
[(8, 3, 'Go to store'), (9, 3, 'Buy Paint'), (10, 3, 'Start Painting')]

How To Order Result Data

You may want the results of a query to come back in either descending or ascending order. This can be done by using the “order by” clause in your query.

def view_projects():
    cursor = db.cursor()
    cursor.execute("select * from projects order by title desc")
    project_records = cursor.fetchall()
    print(project_records)


view_projects()
[(1, 'Plant Lettuce', 'Move lettuces seedlings from nursery to garden'), 
(3, 'Paint Shed', 'New Coat Of Paint On Shed'), 
(2, 'Lawn Care', 'Take care of various lawn tasks')]
def view_projects():
    cursor = db.cursor()
    cursor.execute("select * from projects order by title asc")
    project_records = cursor.fetchall()
    print(project_records)


view_projects()
[(2, 'Lawn Care', 'Take care of various lawn tasks'), 
(3, 'Paint Shed', 'New Coat Of Paint On Shed'), 
(1, 'Plant Lettuce', 'Move lettuces seedlings from nursery to garden')]

Updating A Record

We want to be able to update a record in the database. Maybe we want to change the title of a project. We can do that with the “update” SQL command inside of our Python code like so.

import mysql.connector as mysql

db = mysql.connect(
    host='localhost',
    user='root',
    password='',
    database='projects'
)


def update_project(title, id):
    cursor = db.cursor()
    cursor.execute("update projects set title = %s where project_id = %s", (title, id,))


update_project("Plant The Lettuce!", 1)
db.commit()

db.close()

This first project now has the title of “Plant The Lettuce!” instead of “Plant Lettuce”.

How To Use MySQL and MariaDB In Python Summary

  • MySql uses tables to describe relationships between data
  • MySql runs on its own server
  • This creates a client/server relationship
  • To send requests to the database, a driver is needed
  • The mysql-connector-python can be used for this purpose
  • MySql is not SQL
  • MySql is an RDBMS (relational database management system)
  • SQL is a query language to interact with an RDBMS
  • MySql is very fast and easy to use
  • MySql can scale to extremely large use cases
  • SQLalchemy can be used as an ORM with MySql