Introduction

Yes! As I learned to work with databases in the WAD course using SQL, I encountered a few limitations that prevented me from building flexibly. One of them, which is the focus of this series, is working with databases within your applications.

Most development environments for Flask applications use SQLite databases because they are easy and fast to spin up (with no installation necessary), and Python already provides a library called sqlite3 that makes it easy to work with these databases.

import sqlite3

connection = sqlite3.connect('database.db')
cursor = connection.cursor()

result = cursor.execute('SELECT * FROM customers')
print(result.fetchall())

I can start talking to the database and singing SQL to myself with the code above. But there is a problem with this approach! Assuming...

When I completed the awesome Flask project that I spent millions of moments building and finally deployed it to the internet for my family to know how talented I had become, I slowly realized that the data my application was handling had grown in size and was needing a separate database server setup to manage it.

The genie in me, like every programmer, suggested that I integrate a Postgres database server to handle this. After setting up the server, I ran into a new problem.

"Programmers are usually afraid of refactoring their code because the number of bugs that may surface may be equal or greater than the number of characters they removed and added to the code in the refactoring process" - OVECJOE

Some of the SQL statements or queries I had written when using an SQLite database were not compatible with the Postgres version of SQL. As a result, in order to suppress the error, I must change all incompatible SQL statements... or, like a true alchemist, seek a better solution.

What would you do in my shoes?

The answer is:

Don't ever be in my shoes... Do your duty as an alchemist from the start of your project.

Have you ever noticed that it is difficult to write all the SQL queries manually when you are working on a mid-size or large project?

You can avoid the problem altogether by introducing an ORM to your web application. An ORM (short for Object Relational Mapper), Wikipedia says, is a programming technique for converting data between a relational database and the heap of an object-oriented programming language. This helps you visualize relational databases in an object-oriented sense.

This means that I can map user-defined Python classes (which are blueprints for objects) to database tables and also perform CRUD operations using a more simplistic interface that is pythonic in touch.

In simple words, you don't write SQL queries directly to talk to the database; you can use an API that is provided by an ORM library to perform database operations using Python code. The ORM library beneath the scene knows how to convert your Python code into the resulting SQL queries and do the duties you request with elegance.

One of those ORM libraries we will discuss in this article is SQLAlchemy!

Connecting to a Database

When using SQLitesqlite3, connecting to an SQLite database is as simple as calling the connect method.

import sqlite3

connection = sqlite3.connect('database.sqlite')
# ...

In the case of SQLAlchemy, two distinct APIs make up the library: the Core and the ORM.

'SQLAlchemy Core is the foundational architecture for SQLAlchemy as a “database toolkit”. The library provides tools for managing connectivity to a database, interacting with database queries and results, and programmatic construction of SQL statements.' - SQLAlchemy docs

'SQLAlchemy ORM builds upon the Core to provide optional object relational mapping capabilities. The ORM provides an additional configuration layer allowing user-defined Python classes to be mapped to database tables and other constructs, as well as an object persistence mechanism known as the Session. It then extends the Core-level SQL Expression Language to allow SQL queries to be composed and invoked in terms of user-defined objects.' - Same Source as Above

To connect to a database, we need the Core API, which we can use to create an SQLAlchemy engine. Engine is the start of any SQLAlchemy application and acts as the central source of connections to a particular database. It is a global object and must only be created once for any application instance.

from sqlalchemy import create_engine

DATABASE_URL = 'sqlite:///application.db'

engine = create_engine(DATABASE_URL)

The URL passed to the create_engine function must be valid since it represents the location of the database. There are two commonly used formats:

1. DIALECT+DRIVER://USERNAME:PASSWORD@HOST:PORT/DATABASE

   Many of the parameters are optional such as the DRIVER. If the driver is not specified in the URL, SQLAlchemy uses the default for the database management system you are using.

2. DIALECT://USERNAME:PASSWORD@HOST/DATABASE

   This is the format you may use often. The dialect can be any of the supported database management system types such as Postgres, MySQL, Oracle, etc. As you may have noticed, the second format is more like a shorthand for the first.

In the code above, you may notice that the URL is missing the USERNAME, PASSWORD, and HOSTNAME. This is because we were connecting to an SQLite database...and SQLite databases are usually in the same folder as your project files.

Working with Transactions in SQLAlchemy

A transaction is a sequence of operations, involving one or more SQL statements, performed on a database as a single logical work unit. A single read, write, update, or delete operation may make up a transaction, or any combination of these operations may.

The sole objective of transactions (or grouping activities together) is to make it simple to undo any changes that were performed to maintain consistency whenever an error occurs. For example:

When you make a transfer, think of it as an ATM transaction: money is transferred from your account to the recipient's account, your account is updated by deducting the transferred amount from your original account balance, and the recipient's account is updated with the new balance. What would happen if the deal fell through? Back in your account, the funds are transferred (this is called a rollback in SQL).

In real life, after the engine has been built, a connection can be made to carry out a transaction.

from sqlalchemy import text

with engine.connect() as connection:
    query = text('SELECT * FROM customers')
    result = connection.execute(query)

    print(result.all())

From the example above, you can see that SQLAlchemy Core can be used to execute SQL statements directly using the text function. After the transactions are finished, the connection is automatically closed by the context manager.

Since the above SQL statement was a query that made no changes to the database, there was no need to commit the changes. In a situation where statements such as the INSERT INTO or CREATE TABLE are used, the changes must be committed to persist in the database. For example:

from sqlalchemy import text

with engine.connect() as conn:
    conn.execute(text('CREATE TABLE points (x int, y int)'))
    conn.execute(text(
        'INSERT INTO points VALUES (?, ?)'
    ), [(1, -1), (-2, 1), (3, 4)])

    conn.commit()

The two statements were executed, which led to the creation of a table and the insertion of some data into the table. If the commit() method wasn't called, the changes wouldn't reflect on the database because the DBAPI connection is non-auto-committing.

If the begin method was used (rather than connect), the commit or rollback method would be called automatically at the end of the with block based on the transaction's status. The above transaction code snippet can be rewritten as seen below:

from sqlalchemy import text

with engine.begin() as conn:
    conn.execute(text('CREATE TABLE points (x int, y int)'))
    conn.execute(text(
        'INSERT INTO points VALUES (?, ?)'
    ), [(1, -1), (-2, 1), (3, 4)])

"The fundamental transactional / database interactive object when using the ORM is called the Session. In modern SQLAlchemy, this object is used in a manner very similar to that of the Connection, and in fact as the Session is used, it refers to a Connection internally which it uses to emit SQL.

When the Session is used with non-ORM constructs, it passes through the SQL statements we give it and does not generally do things much differently from how the Connection does directly, so we can illustrate it here in terms of the simple textual SQL operations we’ve already learned." - SQLAlchemy docs

To create a session using a context manager, the Session class is used to create a session object.

from sqlalchemy import Session, text

with Session(engine) as session:
    result = session.execute(text(
        'SELECT * FROM points AS p WHERE p.x > 10'
    ))

    for row in result:
        print(f'x={row.x}; y={row.y}')

Working with Database Metadata

"SQL Expression Language is the central element for the Core and the ORM allowing for fluent, composable construction of queries. The foundations of the queries are Python objects that represent database concepts like tables and columns. " - SQLAlchemy docs

The most common of them are known as MetaData, Table, and Column. In SQLAlchemy, a database table is represented by a Python object called Table. To start using the expression language provided by SQLAlchemy, there must be Table objects constructed that represent all the tables we are interested in working with.

The Table object is created programmatically, either directly by using the Table constructor or indirectly by using the ORM classes (to be discussed in Part 2).

Regardless of the approach used, a collection must be created as a nursery for the tables we will be using. This nursery is called MetaData.

from sqlalchemy import MetaData

metadata = MetaData()

Once this has been created, we can declare some Table objects. Here, we will be working with three tables: products, customers, and orders tables. We will use a very simplistic version of this table.

from uuid import uuid4
from sqlalchemy import Table, Column, String

user_table = Table(
    "user_account", # the name of the table.
    metadata, # the nursery where the table would be placed.
    # the columns present in the table
    Column('id', String(255), primary_key=True, default=uuid4().hex),
    Column('username', String(50)),
    Column('email', String(255), unique=True)
)

To learn more about the data types available, visit here.

Components of a Table Object

The name of the table can be accessed by using the name attribute.

print(user_table.name) # 'user_account'

The list of tables available in the metadata can be accessed using the tables property on the MetaData object.

print(metadata.tables)

# FacadeDict({'user_account': Table('user_account', MetaData(), Column('id', String(length=255), table=<user_account>, primary_key=True, nullable=False, default=ScalarElementColumnDefault('2027b65643404ce6a0b4ca49bcbba40a')), Column('username', String(length=50), table=<user_account>), Column('email', String(length=255), table=<user_account>), schema=None)})

OR

print(metadata.sorted_tables) # prints a list of all the tables in the metadata.

What about the table columns?

print(user_table.c.keys()) # ['id', 'username', 'email']
print(user_table.c.values()) # [Column('id', String(length=255), table=<user_account>, primary_key=True, nullable=False, default=ScalarElementColumnDefault('2027b65643404ce6a0b4ca49bcbba40a')), Column('username', String(length=50), table=<user_account>), Column('email', String(length=255), table=<user_account>)]

How do we access the primary key of the table?

print(user_table.primary_key)

# PrimaryKeyConstraint(Column('id', String(length=255), table=<user_account>, primary_key=True, nullable=False, default=ScalarElementColumnDefault('2027b65643404ce6a0b4ca49bcbba40a')))

Question: What would print(user_table.c.username) output?

One database constraint that is explicitly declared is the ForeignKeyConstraint object that corresponds to the database foreign key constraint. When tables that are related to each other are declared, SQLAlchemy uses the presence of these foreign key constraint declarations to assist in constructing SQL expressions.

For example, the orders table is related to the users table and this must be specified when the orders table is declared.

from sqlalchemy import ForeignKey, DateTime
from datetime import datetime

order_table = Table(
    "orders",
    metadata,
    Column("id", String(255), primary_key=True, default=uuid4().hex),
    Column("user_id", ForeignKey('user_account.id'), nullable=False),
    Column("product_id", ForeignKey('products.id'), nullable=False),
    Column("occurred_at", DateTime(), server_default=datetime.now().isoformat())
)

You may have noticed that I didn’t declare the products table, and the orders table is dependent on it.

Activity: Declare a products table with three columns: id, name, and price.

We can use the object structure we created to represent the three database tables to carry out the majority of Core and ORM activities.

Emitting DDL to the Database

DDL stands for Data Definition Language. It is a subset of the SQL language and is used to describe data and its relationships in a database.

“DDL includes SQL statements to create and drop databases, aliases, locations, indexes, tables and sequences. It also includes statements to alter these objects and impose or drop certain constraints on tables such as UNIQUE*,* NULL*, etc.”* — TechTarget

Despite the fact that we have already established the object structure for our database, we still need to send the DDL it generates — a series of CREATE TABLE statements—to the database in order to insert and query data into the tables. To do this, we can use the create_all() method on the MetaData object and pass in the engine object as a parameter.

print(metadata.create_all(engine))

# 2023-02-13 05:33:32,696 INFO sqlalchemy.engine.Engine BEGIN (implicit)
# 2023-02-13 05:33:32,697 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user_account")
# 2023-02-13 05:33:32,698 INFO sqlalchemy.engine.Engine [raw sql] ()
# ...
# CREATE TABLE user_account (
#       id VARCHAR(255) NOT NULL, 
#        username VARCHAR(50), 
#        email VARCHAR(255), 
#        PRIMARY KEY (id), 
#        UNIQUE (email)
# )
# ...

This will create a database and execute all the DDL statements generated by the object structure, thus creating all the tables we had specified.

Conclusion

In this article, we discussed how SQLAlchemy works and how it helps us create more scalable and reliable applications. We learned how to create an engine, which is the starting point of any SQLAlchemy application, and how to use this Engine object to create a connection and perform transactions. We also discussed how we can create and work with metadata directly.

Note: To be able to use SQLAlchemy in your application, you must first install it.

pip install sqlalchemy

This is the first article in a 3-part series, and I hope you enjoyed (and learned from) the article.

Intermission

How many times have you goals and shedules been squashed by unknown, unexpected, and unpleasant incidents or events?

I have lost count of mine.

And these past weeks have been affected by two particular events: a lack of electricity (blackouts) and poor internet connection.

Because I've had a bad internet connection for a long time, I've learned to adapt. I have studied the patterns in network connectivity and have drawn out a map in my mind of how good the internet connection would be at a given instant of time.

But, the same cannot be said about blackouts. It is a factor I have no control over and is also influenced by many other factors. When I got hit by a severe blackout last week, I was the most disturbed being on all the planets.

I was lagging in C4S and PMD and had planned to catch up on both that week, but I was (or we were) visited on Monday evening by a cooing, violent, and satisfying wind. I could remember vividly saying:

I love the feel on this wind on my skin. I hope it rains.

Note that, for a while now, we have usually been supplied with power at night.

It did not eventually rain, but that night greeted us with darkness. And the next night. And the next night.

I suddenly realized that the wind had damaged something somewhere (possibly wires or electrical components), and it was challenging to fix. I sought power from generators, but due to the fuel scarcity we are experiencing in our country, it was difficult to find one that was entirely free.

Five days of darkness; five days of being cut off from the internet; five days of hot searching; five days of torments; five days of missing classes; five days of not learning.

Dare I say, those days were one of the worst moments of my life?

An issue with a non-deterministic pattern is typically challenging to solve, and a problem with a pricey potential solution is nonetheless challenging for people who lack access to it.

Fortunately, the Kibo team recognized this need and provided us with hubs where we could study without being interrupted by a bad internet connection or a lack of electricity.

Despite the hub’s distance from my location, having a choice is preferable to having none.

I'd like to use this opportunity to express my gratitude to the Kibo staff for always being there for the students when they were having difficulties. We are one big family here at Kibo.

---

Lastly, thanks for reading.