Database management with SQLAlchemy

SQLAlchemy is a Python based toolkit and Object Relation Mapper for managing database.

To start with, let's install the SQLAlchemy by creating an virtual env first

python3 -m venv venv
source venv/bin/activate
pip install sqlalchemy

First thing we do is to import the SQLAlchemy in our project. Then we connect to our database using the create_engine passing the URI of our database.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=True)

Typical URL of the database is of the following format :-

dialect+driver://username:password@host:port/database

In our example, I am just using the in-memory version of the SQLLITE database. Official documentation has very detailed instructions on connecting to database of your choice.

Explicit Connectionless Execution :
First way of interacting with the database is by running adhoc queries. Here the SQL expressions/query is passed to the execute method of the engine. SQLAlchemy in this case opens the connection, runs the query and return the results.

result = engine.execute([SQL QUERY])
for row in result:
    # ....
result.close()

Explicit Execution (Persistent Connection) : We can open a persistent connection to our database using engine.connect() method.

connection = engine.connect()
result = connection.execute([SQL QUERY])
for row in result:
   # ..connection.close()

Sessions : Session is a persistent database connection which allows to deal with database more effeciently using SQLAlchemy ORM. Most of the Python applications dealing with database use Sessions to interact with the database.

session.add()  :  Allows to quickly create a new record (instance of data model) to be added to database.
session.delete()  :  Allows to delete the instance of data model i.e. if the record exists, it will be staged for deletion.
session.commit() : Any changes done by add or delete are not committed to database until the session.commit() is called.
session.close() : Close the database

Creating Tables
To work with SQLAlchemy ORM and create new database tables, we will create Python Classes which will map to database table. Each table to be created in database needs to inherit from Declarative Base Class defined by SQLAlchemy. Each class must have :-

__tablenames__ attribute representing the table name in the database
A column defined as primary key

Let's create our first database table by defining the Python Class. Typically you would see the database classes in a seperate file "models.py"

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Person(Base):
    __tablename__ = 'persons'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __repr__(self):
        return f'Person : {self.name}, {self.id}'

So, here we have declared a class called Person, which will be represented in database as "users" because of __tablename__ attribute. Also, note that the table has an integer field as primary key to identify the records uniquely.

This model will be used in our primary application as :-

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Person

# Create the engine
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()

# Create All Tables
Person.metadata.create_all(engine)

Once the table is created, we can create new records and query the database

# Create a new entry
person1 = Person(name="John")
person2 = Person(name="Steve")

# Add the new person instance using session
session.add(person1)
session.add(person2)
session.commit()

# Query the database and print it
results = session.query(Person).filter_by(name='John')
for result in results:
    print(result)

results = session.query(Person).all()
for result in results:
    print(result)

Source code for this post is available at my Github repository