A big gap in my knowledge of Python is how to access a database. Let us look what Python offers to persist data in a relational database like PostgreSQL, SQLite or SQL Server.
This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub.
Why SQLAlchemy?
SQLAlchemy is a database toolkit for Python. It is not the only way to access your databases, but it works nicely with Flask. Therefore, I think it is a good place to start.
SQLAlchemy consists of two main parts that gives you a lot of flexibility to access your data:
- The Core is a fully featured SQL toolkit that provides a layer of abstraction over various DBAPI (Python Database API Specification) implementations. It allows you to write your own SQL queries and gives you full flexibility on how you work with the database. In addition, you can use metadata to access your data without writing SQL statements at all.
- The ORM (Object Relational Mapper) is an optional package on top of the Core that takes care of mapping your Python objects to tables in your database. You can delegate a lot of work to SQLAlchemy, but you must follow conventions and rules to get that benefit.
The book The Architecture of Open Source Applications offers a nice high-level overview on the Core and ORM part of SQLAlchemy:
Installation
We can install SQLAlchemy with pip:
|
1 |
pip install SQLAlchemy |
Version check
If you do not know what version of SQLAlchemy you have in your environment, you can run this check to get an answer:
|
1 2 3 |
>>> import sqlalchemy >>> sqlalchemy.__version__ '1.4.15' |
SQLAlchemy gets a lot of updates. To follow along with my posts you should check that you have version 1.4.x.
Get the database
Before we can access a database, we need to have one. You can download the Northwind demo database for SQLite from GitHub and put it into the same directory from which you will run your Python examples. You can explore the database with one of these SQLite viewers.
A first example using SQLAlchemy Core
We can fetch the data from the Employee table with this code where we use the textual SQL feature of SQLAlchemy and write the query completely on our own:
|
1 2 3 4 5 6 7 8 |
from sqlalchemy import create_engine, text connection_string = "sqlite:///Northwind_small.sqlite" engine = create_engine(connection_string, echo=False) with engine.begin() as connection: result = connection.execute(text("SELECT LastName, FirstName, Title, BirthDate FROM Employee")) for row in result: print(f"{row['LastName']} {row['FirstName']} ({row['BirthDate']}): \t {row['Title']} ") |
I put this code into a file called sqlalchemy_firststeps.py and when I run it, I get this output:
|
1 |
python.exe .\sqlalchemy_firststeps.py |
Davolio Nancy (1980-12-08): Sales Representative
Fuller Andrew (1984-02-19): Vice President, Sales
Leverling Janet (1995-08-30): Sales Representative
Peacock Margaret (1969-09-19): Sales Representative
Buchanan Steven (1987-03-04): Sales Manager
Suyama Michael (1995-07-02): Sales Representative
King Robert (1992-05-29): Sales Representative
Callahan Laura (1990-01-09): Inside Sales Coordinator
Dodsworth Anne (1998-01-27): Sales Representative
What did we just do? We used a connection string to create an engine, opened a connection to our database, executed a SQL query and got a result back. We iterated through the result and printed the values in the different columns. Wow, there is a lot going on and that is what makes it so hard to start with SQLAlchemy. Over the next weeks we explore what all these words mean and how we can access our database.
Why SQLite?
You may ask why I use SQLite and not a “real” database for SQLAlchemy. The great benefit of SQLite is that you can access it from Python without the need to install a driver. If something goes wrong or you need to retry an example, you can delete the SQLite file or revert changes to that file (when you run your examples in a Git repository). That lets us focus on SQLAlchemy and when we understand the basics we can add the additional layer of complexity that comes with SQL Server or PostgreSQL.
Next
Even in a simple example that prints a few rows from the employee table, we have to bring a lot of parts together. Next week we take a closer look on what those parts are and how they fit together.
5 thoughts on “Python Friday #73: First Steps with SQLAlchemy”