How to connect and CRUD on MySQL with Python

Table of Contents

Introduction to MySQL and Python
- MySQL connectors for Python
- Download & Install MySQL connector for Python
Connect MySQL with Python
CRUD operations with MySQL and Python
Ending Notes

Introduction to MySQL and Python

MySQL is an open-source, easy-to-use RDBMS, it's used by millions of websites worldwide. In this post, you will learn how to connect MySQL with Python and perform CRUD (Create, Read, Update, Delete) operations on it.

MySQL connectors for Python

There are multiple connector libraries that help you connect with MySQL database as follows :

mysql-connector-python : This library is the official MySQL driver written in Python. It's frequently updated, and well maintained. According to the project description on PyPi, this driver does not depend on any of the MySQL C client libraries and implements the DB API v2.0. You can find the source code and the PyPi repository here : https://pypi.org/project/mysql-connector-python/

Download & Install MySQL connector for Python

For this tutorial, we will be using the “mysql-connector-python” library to connect and perform CRUD operations on our database. To install the library follow these steps :

Make sure you have Python and pip installed on your system. If not, please follow the Python installation guide here
Activate your virtual environment by running the following command :

source <path-to-venv>/bin/activate

If you are not using any virtual environment you can skip this step. 3. Now we will install the mysql connector using pip

pip install mysql-connector-python

Mysql connector package for python is also available to download as deb, rpm, msi and dmg native packages. If you prefer installing a native package, download the package from the official MySQL website here. 4. Once you are done installing the connector, verify the installation by trying to import it in your python shell.

Type python in your terminal
```
python
```
Import mysql.connector in your python shell.
```
import mysql.connector
```
If the import is done without any error, you've installed the MySQL connector for python in your machine successfully.
and that's it, you have installed mysql-connector-python on your local development environment.

Connect MySQL with Python

Now that you are done with installation and initial setup, let's open a text-editor and write some code to make a connection with MySQL. We will be using VS Code editor feel free to use any text-editor of your choice.

To connect to a MySQL database we need a few details. Before we proceed, make sure to have the following details about your MySQL database ready :

Username : The username that has access to the target database.
Password : The password for your MySQL username.
Database : Target database's name
Host & Port : The address of your database server, and the port. By default the port for MySQL is 3306. Your host may look something like 23.23.34.45 or my.awesome-db.com.

Once you have all the required db details, let's create two Python files :

crud-script.py : This file will contain a mysql connector python example and CRUD operations code. To quickstart, paste the following code in crud-script.py :

## crud-script.py
import mysql.connector as mysql
import config
import traceback

def connect():
    try:
        conn = mysql.connect(
            user = config.user,
            password = config.password,
            database = config.database,
            host = config.host,
            port = config.port
        )
        return conn
    except Exception as err:
    print(“Error occurred in making connection …”)
        traceback.print_exc()

def print_version(connection):
    cursor = connection.cursor()
    cursor.execute('select version()')
    db_version= cursor.fetchone()
    print(db_version)
    cursor.close()
    connection.close()

if __name__ == ‘__main__':
    conn = connect()
    print_version(conn)

Here, we imported our connector, the config file and the traceback module. The traceback module along with handled exceptions will help us debug without breaking our code in event of an exception.

config.py : This file will contain your credentials, just for the sake of keeping secrets away from our primary code. Your config.py should look something like this :

user = “bond-james-bond”
password = “836547VESPER”
database = “casino_db”
host = “bond.007server.com”
port = 3306

Save both files, and run the crud-script.py with the following command :

python crud-script.py

If the code returns Error occurred in making connection ... then most likely the credentials are incorrect, recheck your host, port, password, dbname and username. Try connecting again if there was a connection error printed in the exception traceback.

CRUD operations with MySQL and Python

CRUD is short for Create, Read, Update and Delete. These are the most common operations that pretty much sums up the type of requests we can make to a database (or an API hehe). Let's get our hands dirty with some python code. We will be updating our crud-script.py again.

Create a table with MySQL Python

Make sure that the user has permission to create a table, or it may throw an error. First we need to know the query needed to create a table in MySQL, it is as follows :

CREATE TABLE casino_db.person (
    id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    city VARCHAR(100)
);

Now we will write the code that will execute our create table query on our MySQL server. Add the following function to our crud-script.py file :

def create(connection):
    cursor = connection.cursor()
    query = """
    create table person(
        id int primary key,
        first_name varchar(100),
        last_name varchar(100),
        city varchar(100)
    );
    """
    try:
        cursor.execute(query)
        connection.commit()
        print("table created successfully!")
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

This will create a table with name person in our database.

Inserting data with MySQL Python

Another query that falls under creating data are Insert queries. Insert queries help us add new data rows into a table. Again, before proceeding further make sure that your user has permission to insert into a table. The query to insert a data in our person table is :

INSERT INTO person (id, first_name, last_name, city)
VALUES
(
    1, “James”, “Bond”, “NY”
);

Similar to our last operation, we will add code for Inserting data into our crud-script.py :

def insert(connection):
    cursor = connection.cursor()
    query = """
    INSERT INTO person (id, first_name, last_name, city) VALUES (%s, %s, %s, %s);
    """
    try:
        data = (1, “James”, “Bond”, “NY”)
        cursor.execute(query, data)
        connection.commit()
        print("Record inserted successfully!")
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

This will insert a new row in our table “person”. On a successful call to this function, you will see the message Record inserted successfully! in your terminal output.

Read data with MySQL Python

Next in CRUD is Read operation. With this step, we will learn how to read data from MySQL databases using Python. To get all records from a table, we need to use select query, as follows :

SELECT * FROM <table_name> LIMIT 500;

Similar to our last operation, we will add code for reading data into our crud-script.py :

def read(connection):
    cursor = connection.cursor()
    try:
        cursor.execute("SELECT * FROM person LIMIT 500;")
        record = cursor.fetchone()
        print(f"Read successful, 1st row is : id = {record[0]}, name= {record[1]+' '+record[2]}, city= {record[3]}")
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

This will read all rows from the person table, we have also limited the number of rows to 500 here. On a successful call to this function, you will see the message “Read successful, 1st row is : id=1, name=James Bond, city=NY”

Update data with MySQL Python

Next in CRUD is Update operation. With this step, we will learn how to update existing data in a MySQL database using Python. To update a column for specific row in a table, we need to use update query, as follows :

UPDATE <table_name> SET <column_to_update>='%s' WHERE <condition>;

Similar to our last operation, we will add code for updating data into our crud-script.py :

def update(connection):
    cursor = connection.cursor()
    query = """
    UPDATE person SET city='%s' WHERE id=%s;
    """
    try:
        cursor.execute(query, ("Sydney", 1))
        cursor.execute("SELECT * FROM person WHERE id=1;")
        record = cursor.fetchone()
        print(f"Update successful : id = {record[0]}, name= {record[1]+' '+record[2]}, city= {record[3]}")
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

This code will update the city for the first row in our table. Previously the city was set to “NY”, after running our update function on the first row, the city will be changed to “Sydney”. On a successful call to this function, you will see the message “Update successful : id=1, name=James Bond, city=Sydney”

Delete data with MySQL Python

Last operation in CRUD is Delete operation. With this step, we will learn how to delete a row in a MySQL database using Python. To delete a row we need the following query :

DELETE FROM <table_name> WHERE <condition>;

Similar to our last operation, we will add code for deleting data into our crud-script.py :

def delete(connection):
    cursor = connection.cursor()
    query = """
    DELETE FROM person WHERE id=1;
    """
    try:
        cursor.execute(query)
        cursor.execute("select * from person;")
        record = cursor.fetchone()
        print(record)
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

This code will delete a row from our table where the id is 1.

Ending Notes

That is how you create a connection, create, delete, insert, update, read data with a MySQL database using Python. On completion of all the steps, your final crud-script.py should look something like this :

import mysql.connector as mysql
import config

def connect():
    try:
        conn = mysql.connect(
            user = config.user,
            password = config.password,
            database = config.database,
            host = config.host,
            port = config.port
        )
        return conn
    except Exception:
        raise Exception("Improperly Configured")

def print_version(connection):
    cursor = connection.cursor()
    cursor.execute('select version()')
    db_version= cursor.fetchone()
    print(db_version)
    cursor.close()
    connection.close()

def create(connection):
    cursor = connection.cursor()
    query = """
    create table person(
        id int primary key,
        first_name varchar(100),
        last_name varchar(100),
        city varchar(100)
    );
    """
    try:
        cursor.execute(query)
        connection.commit()
        print("table created successfully!")
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

def insert(connection):
    cursor = connection.cursor()
    query = """
    insert into person (id, first_name, last_name , city) values (%s, %s, %s, %s)
    """
    try:
        data =(1,"bhaskar", "chauhan", "dombivli")
        cursor.execute(query, data)
        connection.commit()
        print("records inserted successfully!")
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

def read(connection):
    cursor = connection.cursor()
    try:
        cursor.execute("select * from person;")
        record = cursor.fetchone()
        print(f"id = {record[0]}, name= {record[1]+' '+record[2]}, city= {record[3]}")
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

def update(connection):
    cursor = connection.cursor()
    query = """
    update person set city= %s where id= %s
    """
    try:
        cursor.execute(query, ("mumbai", 1))
        cursor.execute("select * from person;")
        record = cursor.fetchone()
        print(f"id = {record[0]}, name= {record[1]+' '+record[2]}, city= {record[3]}")
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

def delete(connection):
    cursor = connection.cursor()
    query = """
    drop table person;
    """
    try:
        cursor.execute(query)
        cursor.execute("select * from person;")
        record = cursor.fetchone()
        print(record)
        connection.commit()
    except Exception as err:
        print(err)
    cursor.close()
    connection.close()

delete(connect())