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.

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)
"""

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.

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.

from sqlalchemy import text

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

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.

# 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

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

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:

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)

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.

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

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.

Using the session

To use the Session class, we can

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()

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.