Empowering Python Developers with an Elegant ORM: SQLAlchemy

Introduction

A library called SQLAlchemy makes it easier for Python applications and databases to communicate together. This library is typically an Object Relational Mapper (ORM) tool to instantly translate function calls into SQL statements and convert Python classes to tables in relational databases.

Creating an Engine

We must build an Engine each time we want to use SQLAlchemy to communicate with a database. On SQLAlchemy, engines handle Pools and Dialects, two important components. These two ideas will be explained in more detail in the following two sections, but for the time being, it is sufficient to know that SQLAlchemy employs them to communicate with DBAPI functions.

We must import and execute the create_engine function from the sqlalchemy library to create an engine and begin working with databases:

In this example, a PostgreSQL engine is created to connect to a local instance operating on port 5432. (the default one). Additionally, it specifies that to access the sqlalchemy database. It will use the user of the password and pass. Building an engine does not immediately establish a connection to the database. When necessary (such as when we submit a query or create/update a row in a table), this procedure is delayed.

Data Types for SQLAlchemy

We can be confident that when using SQLAlchemy, we will receive support for the most prevalent data types found in relational databases. Among the kinds, SQLAlchemy offers abstractions, booleans, dates, times, strings, and numeric values are just a few examples. In addition to these fundamental types, SQLAlchemy supports a few vendor-specific types (such as JSON) and allows developers to build new types and redefine existing ones.

Creating a Table

Let’s examine the following example to see how we can use SQLAlchemy data types to convert Python class properties into fields on a relational database table:

SQLAlchemy is informed by the __tablename__ property that the rows of the products table must be mapped to this class.

By using the engine now that SQA can build tables!

The Book model specification is contained in the metadata of the Base class from which our models inherited it, so to create a table, we execute the create_all method with the engine.

Instead, we would use the drop_all function to delete this table as well as every other table in the database:

This presumes that we, as is typically the case, have an engine in the global realm.

The engine and other database operations are typically located in a file with the name db.py or crud.py, where CRUD stands for Create, Read, Update, and Delete.

Using Session:

Let us create a session object as global

A factory for individual sessions, the global Session object enables us to connect our engine just once and have clear session states whenever we need them. Every time we want to interact with the database, we import Session and create a new s to work with; you’ll see a better way to do this in the last section. Session and engine are typically specified globally at the beginning of the db.py or crud.py.

To free up connections and resources, you must always end the session when you are finished using it.

Inserting Data:

Using our model class this time, we’ll make the same book that we did in the SQLite example:

All we’re doing here is instantiating the class like in Python. You’ll see that passing a DateTime is possible without first converting it to a timestamp. SQA will change the Postgres Date column style to match the Python datetime. Now we are prepared to add a book to the database using the session object.

Basic Querying rows:

The model we want to query is passed to the query function from the session object, which then returns the first() item, which is the only item currently available:

Output:

Conclusion

Most data collection, web scraping, and web application projects are created using SQLAlchemy. One difference is that Flask is usually the web framework of choice in web applications, and SQLAlchemy is paired into the flask-sqlalchemy library. In that case, this library makes projects simple for migrations.

About CloudThat