Intro to SQL Alchemy

You can find a lot of useful applications to make and modify your own database on the Internet: Microsoft Access, MySQL, SQLite, Oracle, and so on. We can also use Python for that. SQLAlchemy is a powerful set of Python database tools. In this topic, we are going to overview the main features and connect our first database.

SQLAlchemy features

SQLAlchemy was released in 2006 and quickly became one of the most popular object-relation mapping (ORM) tools among Python developers. When we use an object-oriented programming language, it is important to think in terms of objects. SQL is not based on the object-oriented model, it is based on a relational model instead. This gap between the two model types can be overcome with the help of the ORM provided by SQLAlchemy.

You can use a model that takes a database and a Python program to transform the information from the database to Python objects, and vice versa. As a result, thanks to SQLAlchemy, we can work with objects and at the same time employ important database mechanisms.

Database API

SQLAlchemy is designed to work with different databases and Database APIs (DBAPI). Python Database API Specification is a standard for Python libraries that lets you connect any database. SQLAlchemy is not the only library that can connect databases, there are dozens, but all of them follow this standard. The standard specifies the functions that help you connect to a database, send queries, and get results. All the specifications are regulated by PEP 249.

SQLAlchemy uses different dialects to work with different DBAPI implementations. It supports the following dialects: MySQL, Oracle, Microsoft SQL Server, PostgreSQL, and SQLite. All dialects require an appropriate Database API driver.

Setup

Use pip to install the library:

pip install sqlalchemy

Now you can start working with the database. In this topic, let's deal with a local database that was created in SQLite. You can download it here. Here's what it looks like:

The database is called Building_Database.sqlite. It contains information about buildings in an imaginary city: addresses, construction dates, heights, number of dwellers, number of floors, and the year of the recent renovation.

To connect to it using SQLAlchemy, we need to create an engine and choose the right database dialect. You can do it with the help of the create_engine() function. Don't forget to import the library in advance:

from sqlalchemy import create_engine

engine = create_engine('sqlite:///Buildings_Database.sqlite', echo=True)

Inside the function, we need to specify the name of the dialect and write down the database name. As we connect to a local database, the URL format is slightly different. It requires three slashes. The echo attribute enables logging using the standard logging module. The Official SQLAlchemy Documentation contains more information about creating an engine for other dialects.

Now you can easily connect to the database. Just use the following statement:

connection = engine.connect()

Great! You are connected to the database.

Working with an existing database

Let's try to work with that database. In older versions, you could write the following command to print the names of all database tables:

print(engine.table_names())
# 2021-03-29 06:52:34,731 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master 
# WHERE type='table' ORDER BY name
# 2021-03-29 06:52:34,734 INFO sqlalchemy.engine.base.Engine ()
# ['Buildings']

You can see additional information on the left before the tables: the date and time of the command, the SQL command query, and so on. If the echo attribute is False, you won't be able to see it. However, table_names() is deprecated and removed in SQLAlchemy 2.0.

In newer SQLAlchemy versions, you should use the Inspector API, which is the officially supported way to get table names:

from sqlalchemy import inspect

inspector = inspect(engine)
print(inspector.get_table_names())
# ['Buildings']

Another important part of SQLAlchemy is reflection. Reflection means that you can get access to a database and get information on any SQL table objects already existing within the database. Take a look at the following code:

from sqlalchemy import MetaData, Table

metadata = MetaData()
buildings = Table('Buildings', metadata, autoload_with=engine)

The "Buildings" table is already defined in our database, and now we want to see its structure. To do this, first of all, we need to import MetaData and Table from SQLAlchemy. Then we need to create a MetaData() object — it works as a catalog that stores database information such as tables, columns, and constraints. Then we use Table() with the table name and pass autoload_with=engine. This tells SQLAlchemy to reflect the table definition directly from the database. The final step is to use the repr() on the table object to print its structure (columns, types, and metadata):

print(repr(buildings))
# 2021-03-29 07:07:18,682 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("Buildings")
# ...
# Table('Buildings', MetaData(), 
# Column('counter', INTEGER(), table=<Buildings>),
# Column('address', TEXT(), table=<Buildings>), 
# Column('date_of_building', INTEGER(), table=<Buildings>),
# Column('height', INTEGER(), table=<Buildings>), 
# Column('number_of_dwellers', INTEGER(), table=<Buildings>),
# Column('number_of_floors', INTEGER(), table=<Buildings>), 
# Column('recent_renovation', INTEGER(), table=<Buildings>),
# schema=None)

Summary

In this topic, we have discussed the basics of SQLAlchemy. Now, you can:

• install the library and connect to the existing database of any dialect;

• print the list of tables that are in your database;

• print all information about the existing columns.

If you want to learn more, please, take a look at the Official Documentation. Right now, let's proceed to practical tasks.