Introduction

In previous post we saw how to use SQLAlchemy and execute SQL commands to operate our database.

Now we will see that there is a more convenient way to operate in Python using the SQLAlchemy

In case you have reset your environment, 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

from sqlalchemy import Table, Column, Integer, String

metadata = db.MetaData()

pets_table = Table(
                     "Pets",
                     metadata,
                     Column('PetID', Integer, primary_key=True, autoincrement=True),
                     Column('Name', String(50)),
                     Column('Category', String(50))
                    )

metadata.create_all(database)

Adding entries

from sqlalchemy import insert

def add_pet(name, category):
    statement = insert(pets_table).values(Name=name, Category=category)

    with database.connect() as connection:
        connection.execute(statement)
    

add_pet('Luna', 'Dog')
add_pet('Silvester', 'Cat')
add_pet('Twetee', 'Bird')

Read data

from sqlalchemy import select

def get_pets():
    stmt = select([pets_table])
    
    with database.connect() as connection:
        results = connection.execute(stmt)
        return results.fetchall()

get_pets()

[(1, 'Luna', 'Dog'), (2, 'Silvester', 'Cat'), (3, 'Twetee', 'Bird')]

Delete an entry

from sqlalchemy import delete

def delete_pet(id):
    stmt = delete(pets_table).where(pets_table.c.PetID == id)
    with database.connect() as connection:
        connection.execute(stmt)

delete_pet(1)

get_pets()

[(2, 'Silvester', 'Cat'), (3, 'Twetee', 'Bird')]

Delete a table

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

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}


pets_table.drop(database)

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

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

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

This method of accesing data is more pythonic, and we can build classes and reusable functions easily.