SQLAlchemy for absolute beginners

Creating a database engine and execute SQL from Python

Mike Huls

Oct 17, 2022 — 6 min read

Some proper Alchemy (image by Elena Mozhvilo on Unsplash)

SqlAlchemy is a simple and quick way to allow Python to work with data from databases. It’s very simple to create a connection to your database with an engine. In addition its very easy to query your database and retrieve data with the engine and ORM.

In this article we’ll do two things:

Create a connection to our database with an engine)
Use the engine to execute raw SQL

Lastly we’ll focus on the next steps: all the other things we can use the database-engine for; like a migration model for instance. We’ll start of creating the connection; let’s code!

1. Create the database engine

The database-engine allows us to communicate with our database. It’s not difficult to create one.

Step 1 -create a connection string

First we’ll create a connection-string. This is specifies details about our database: where it’s hosted and credentials.import sqlalchemy as sa# Step 1: Create connection string
constring: sa.engine.url.URL = sa.engine.URL.create(
drivername="postgresql",
username="postgres",
password="mysecretpassword",
host="localhost",
port=5432,
database="mydb"
)

As you can see we’re connecting to a Postgres database on my localhost.

Normally it’s a very bad idea to just hardcode your credentials. If I commit this file to my repo on Github everyone can see my credentials. It’s highly recommended to keep your credentials in an .env file which is a simple technique that prevents your confidential information from falling into the wrong hands. Check out the article below on how to achieve this.

Step 2: Creating the engine

Then we use the constring to create an engine.# Step 2: Create and configure engine with the connection string
dbEngine= sa.create_engine(
url=constring,
# fast_executemany=True,
)

I pass fast_executemany as an additional argument. This is a SQL Server specific optimization for faster inserts. Read more in the article below:

Step 3: Connecting and testing the engine

In the previous step we’ve defined our engine. In this step we’ll try to connect and find out if the connection is valid.# Step 3: Testing my engine
try:
with dbEngine_sqlserver_localhost.connect() as con:
con.execute("SELECT 1")
print("Engine valid")
except Exception as e:
print(f"Engine invalid: {e}")

We try to execute some simple SQL. If this doesn’t raise an error we know we’re connected properly. Otherwise we can inspect the error to see what went wrong.

You may, for example, get an error because SQLAlchemy is missing required packages. For connecting to a Postgres database we need to pip install psycopg2 for example. SQL Server needs pyodbc. SQLAlchemy is pretty clear about this so just follow instructions.

2. Executing SQL with the engine

Now that we have our database-engine created we can start using it. In the previous chapter we already go a sneak-peek executing the SELECT 1. In this chapter we’ll go about the advantages and disadvantages of executing raw SQL on the engine.

Raw SQL example: Creating a table

In the example below we’ll see the disadvantage of executing raw SQL:statement_create = """
CREATE TABLE IF NOT EXISTS Students (
ID SERIAL PRIMARY KEY,
Name TEXT,
Age INT
)
"""
with dbEngine_sqlserver_localhost.connect() as con:
con.execute(statement_create)

We’ll use some PostgreSQL to create a statement and then use the connection to execute it.

The problem with raw SQL

Easy as it may be; there are some downsides executing raw SQL:

Error-prone: It’s pretty easy to make a mistake in the SQL syntax
Database-specific: maybe you only want to test and develop on Postgres but your production database is of another type. If you use SQL Server for example you cannot use ID SERIAL PRIMARY KEY but maybe rather use something like ID INT IDENTITY(1,1) PRIMARY KEY.
Organization: your repository will be littered with SQL-statements. Imagine if a column name changes; you’ll need to go through all of these SQL statements and adjust your queries.

The solution: database-agnostic models

SqlAlchemy solves the problem by creating objects that are to be mapped to tables in your database (this is the Object-Relation-Mapping in ORM). This has many advantages:

The models are database-agnostic. This means that the objects have no idea about which database and syntax is required. At the moment of, for example, inserting SQLAlchemy compiles the object to a statement that fits the database(-engine) we’re using. This means that your Python code runs even if your dev-environment uses another database compared to your production-environment. This makes using databases even more flexible and is especially useful when using migration models.
Less prone to error: The SQL is generated for you so it’s near impossible to make syntax-errors. In addition you only work with Python classes in your Python-IDE so it’s a bit harder to make typo’s for example.
All your models are neatly organized in one place and imported throughout your project. If someone changes a column name of your database table you only need to adjust one model in stead of fixing up many raw SQL queries.

I’m currently in the process of writing an article that demonstrates how to use SQLAlchemy’s ORM with the database-engine. Follow me to stay tuned!

Conclusion

In this article we’ve explored the start of SQL Alchemy; we know how to connect to the database and how to execute raw SQL. Also we’ve discussed to pro’s and con’s of executing raw SQL and understand that for most projects using the ORM is the better option. In the next article we’ll check out the ORM and how to use it.

I hope everything was as clear as I hope it to be but if this is not the case please let me know what I can do to clarify further. In the meantime, check out my other articles on all kinds of programming-related topics like these: