# Brewing with SQLAlchemy

By Yonatan Vega · September 24, 2025

> Source: https://fullstack.rocks/article/brewing-with-sqlalchemy

![A friendly covet of witches standing around a green glowing orb.](https://storage.googleapis.com/fullstack-rocks-media/brewing-with-sqlalchemy-960-ef6bad94e788.webp)

We are now ready to begin writing queries, but to do so, first we need to create a `connection engine` which is a fancy way of saying we need to create an object that we can use to create connections to our database.

The engine also operates as a container of `connections` to the database, otherwise known as a `connection pool`.

## Creating a connection engine

There are multiple ways of defining connection engines in SQLAlchemy, the most straightforward way is to use the `create_engine`  function and specify the database URL.

for the sake of simplicity, for the first few articles I will be using SQLite, and later will move to PostgreSQL.

```python
from sqlalchemy import create_engine

engine = create_engine("sqlite:///:memory:")
"""
If you would like to see everthing that is happening in the database, you can use the echo flag.
Like so:
engine = create_engine("sqlite:///:memory:", echo=True)
"""
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/brewing-with-sqlalchemy#code-6ac165385e2182646328687e)_

We now have an engine we can use to get a connection to the database.

## Why SQLite?

For this tutorial, we will initially use SQLite, which will make things easier while we go through the first few chapters.

> **Note:** SQLite **can** operate as an in-memory database if explicitly configured (e.g., by using the `:memory:` keyword when opening a database connection). However, this is not its default behavior.
>
> This makes SQLite great for experimentation, as it is very lightweight and easy to use.
>
> Many mobile app developers use SQLite when they have access to a device's internal storage and want to leverage data querying capabilities. This is common in mobile app development (e.g., Android and iOS).

Going through the basics of SQLAlchemy using a memory-based database doesn’t force us to install or set up databases. If you would like to work against a real database, all of the examples should also work with PostgreSQL. For that reason, the `psycopg2-binary` was specified in the previous chapter as a requirement. 

## Our first SQLAlchemy queries

As we've seen in the introduction to SQLAlchemy, there are many ways of querying our database. The simplest way is to just send our query as a string to the database, and get the result.

```python
from sqlalchemy import text

with engine.connect() as connection:
  result = connection.execute(text("SELECT 'Hello, SQLAlchemy!'"))
  print(result.all())
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/brewing-with-sqlalchemy#code-6ac165385e21826463286880)_

When querying the database, there are many functions we can use to fetch the results into variables, the most common ones are `.all()` and `.first()` 

In the code above, we begin by getting a connection variable using a context manager, and in the next line we execute the command we want to send to the database, in this case we are just sending a command directly in the form of a string, but as we will see soon, that is not the only way.

### Making changes in the DB

It is worth noting at this point that SQLAlchemy does not auto-commit (not by default) when we make database queries. This can be a great feature for debugging applications or if you would like to make sure all of your application logic is executed correctly before you push changes to the database (otherwise known as an early abort).

But in any case, if we want to persist with our changes, we can do this in multiple ways.

```python
# Create a table and commit using the .commit() method
with engine.connect() as conn1:
  conn1.execute(text("CREATE TABLE my_table (x int, y int)"))
  conn1.commit()
  # print(result.scalar())

# Insert data using the .begin() method and the .execute() method 
with engine.begin() as conn2:
  result = conn2.execute(
      text("INSERT INTO my_table (x, y) VALUES (:x, :y)"),
      [{"x": 1, "y": 1}, {"x": 2, "y": 4}]
  )
  print(result.rowcount) # Outputs: 2
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/brewing-with-sqlalchemy#code-6ac165385e21826463286881)_

When using `conn1`, we use the `commit` method to commit the transaction, this style is referred to as the “commit as you go” approach.

> **Note:** SQLAlchemy creates `transactions` automatically.
>
> Transactions can be described as a temporary container of statements you would like to push to the database. When using transactions you can safely make any number of statements that will not persist unless you explicitly ask for the transaction to be `committed` 
>
> While you work within a transaction, using the resources you defined during the transaction is possible.
>
> If you would like to cancel the changes your transaction will perform, you can safely use the `rollback` keyword. This will cancel all the changes you made during the transaction.

When using `conn2` we introduce the “begin once” approach, which commits automatically by creating our connection using the `begin` method. 

## Basic SELECTs

To fetch data we can perform a query like so:

```python
with engine.connect() as conn3:
  result = conn3.execute(text("SELECT * FROM my_table"))
  for row in result:
      x = row.x
      y = row.y
      print(x, y)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/brewing-with-sqlalchemy#code-6ac165385e21826463286883)_

By default queries return `named tuples`, which means we can access the data in each row just as we would any other named tuple.

Another way would be to spread our variables before we access them.

```python
with engine.connect() as conn3:
  result = conn3.execute(text("SELECT * FROM my_table"))
  for x, y in result:
      print(x, y)
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/brewing-with-sqlalchemy#code-6ac165385e21826463286884)_

# The Session class

So far, we accessed the database using a basic engine and connections. However, at some point, SQLAlchemy introduced the `Session` class as an alternative, which is instrumental when actually working with the ORM architecture of SQLAlchemy.

When executing statements to the database in the form of a string, as we have up to this point, the `Session` class actually just works as a wrapper for the engine and does not do much more.

But as we will soon see, the Session class is actually incredibly powerful when working on medium—to large-scale applications with many different Models and queries during the execution lifespan.

> **Note:** 👌 More of the capabilities of the Session class will be detailed as we delve deeper into the ORM.

## Using the session

To use the `Session` class, we can

```python
from sqlalchemy.orm import Session

with Session(engine) as session:
  result = session.execute(
      text("DELETE FROM my_table WHERE x = :x"),
      {"x": 1},
  )
  print(result.rowcount)
  session.commit()
```

_[▶ Run this on fullstack.rocks](https://fullstack.rocks/article/brewing-with-sqlalchemy#code-6ac165385e21826463286886)_

As we can see, the session is used only as a connection provider for the engine; not much differs from our previous code examples.

In the next chapter, we will describe ORM models and start writing our own.