The big challenge for me in learning SQLAlchemy was that it comes with a lot of terms and concepts that you need to know before you can start to understand this toolkit. Some are familiar and work the same way as in other programming languages, while others are an additional level of abstraction that were new for me. To clear things up, I wrote this post to explain the meaning of the most important concepts in SQLAlchemy.
This post is part of my journey to learn Python. You can find the other parts of this series here.
Connection string
The connection string in SQLAlchemy has the same purpose as in most ORM tools: it tells SQLAlchemy which database we want to use. Depending on the database vendor you use, the connection string may contain a server name and/or a username and a password. The generic format for a connection string looks like this:
‘dialect+driver://username:password@host:port/database’
Depending on the dialect and the driver your connection string may look different. Therefore, check the documentation for the expected syntax and write it exactly that way.
(Database) Connection
The database connection is the communication channel over which your application talks to the database. For SQL Server this is a network connection, while for SQLite this is something like the file object you use when you write to a file. You use the connection directly in SQLAlchemy Core to send SQL statements to your database.
Dialect
The dialect describes how SQLAlchemy talks to a database. If you use SQLite you need a different style of SQL than with SQL Server or PostgreSQL. SQLAlchemy comes with a few built-in dialects:
If you need to talk to a different database you can use one of the external dialects.
Driver
The driver helps SQLAlchemy to access a specific database type. While the dialect makes sure SQLAlchemy talks in the right language to the database, the driver bridges the gap from Python to the database itself. For most of the built-in dialects of SQLAlchemy you find multiple drivers you can use (like PyODBC, mxODBC, pymssql for SQL Server).
Engine
The engine is the entry point into SQLAlchemy. Whenever you want to access a database through SQLAlchemy Core, you first need to create an engine. The engine encapsulates all the database related stuff, manages the connection pool and abstracts the whole DBAPI:
(Connection) Pool
The connection pool is a standard technique used to maintain long running connections in memory. Establishing a connection to a database costs time and resources (in your application and in the database). The connection pool creates a few connections early on and manages them in a way that your application can access the database without a delay. At the same time the pool makes sure that not every request you send to the database ends up with its own connection. That would quickly end in a denial-of-service attack on your database and bring your application to a halt.
Session = Unit of Work
In the ORM part of SQLAlchemy you use the session instead of the connection to talk to the database. The session implements the Unit of Work pattern where you can add modified objects and as soon as all your changes are ready, save them in one go to the database.
Transaction
A database transaction helps you to keep your database in a consistent state. You can group multiple changes (like create, update, delete) into one logical unit and save them only when all changes succeed. You can use this safety mechanism explicitly with SQLAlchemy Core, while in SQLAlchemy ORM the session manages the transaction in the background for you.
Next
This was a lot of words. Next week we put those concepts into action and use the raw SQL feature of SQLAlchemy to access our database.