Databases MySQL: Python Part 1

Introduction

In previous post we saw how to install and use an SQL database, now I will introduce how to use inside Python

We have several connectors from SQL to Python that are “flavour” dependant. For example, we can use psycopg2 to connect to a PostgreSQL database or MySQL Connector to connect to MySQL.

I like to take a step back, and use a package that is not dependant of the brand of database we will install. For that my favourite is SQLAlchemy

As usual, we can start installing the package

!!pip3 install sqlalchemy

['Requirement already satisfied: sqlalchemy in /home/luis/.local/lib/python3.8/site-packages (1.3.20)']

And before using the library, we need to import it

import sqlalchemy as db

# and for visualisation
import pandas as pd

Connecting to the database

database = db.create_engine('mysql+pymysql://user:password@localhost/datab')

Creating a table

with database.connect() as connection:
    connection.execute(
        "CREATE TABLE Pets (\
        PetID int NOT NULL AUTO_INCREMENT,\
        Name varchar(50) NOT NULL,\
        Category varchar(50) NOT NULL,\
        PRIMARY KEY (PetID)\
        )"
    )

Adding entries

with database.connect() as connection:
    connection.execute("INSERT into Pets(Name, Category) values ('Luna', 'Dog')")
    connection.execute("INSERT into Pets(Name, Category) values ('Silvester', 'Cat')")
    connection.execute("INSERT into Pets(Name, Category) values ('Twetee', 'Bird')")

Read data

with database.connect() as connection:
    results = connection.execute("SELECT * FROM Pets")
    display(pd.DataFrame(results.fetchall(), columns=['PetID', 'Name', 'Category']))

	PetID	Name	Category
0	1	Luna	Dog
1	2	Silvester	Cat
2	3	Twetee	Bird

Delete an entry

with database.connect() as connection:
    results = connection.execute("DELETE FROM Pets WHERE PetID=3")

with database.connect() as connection:
    results = connection.execute("SELECT * FROM Pets")
    display(pd.DataFrame(results.fetchall(), columns=['PetID', 'Name', 'Category']))

	PetID	Name	Category
0	1	Luna	Dog
1	2	Silvester	Cat

Delete a table

with database.connect() as connection:
    results = connection.execute("SHOW TABLES")
    display(pd.DataFrame(results.fetchall()))

	0
0	Pets

with database.connect() as connection:
    connection.execute("DROP TABLES Pets")

with database.connect() as connection:
    results = connection.execute("SHOW TABLES")
    display(pd.DataFrame(results.fetchall()))

We see an empty dataframe, and therefore we have deleted the only table in our database

This method of accesing data is not very pythonic, and in the next post we will see how to create the structure of the table in python and interact with our database using methods, rather than executing SQL commands.