Alright, let’s roll up our sleeves and get to the good stuff. Forget the high-level ORM magic for a moment—we’re going to talk about the foundation it’s all built on: SQLAlchemy Core. This is where you get to talk to the database in its own language (SQL) but with all the power and safety of Python. It’s like having a brilliant translator who not only converts your words but also stops you from accidentally insulting the king. We’ll cover the absolute essentials: the Engine, the Connection, defining a Table, and crafting a Select statement.

The Engine: Your Database Portal

Think of the Engine as your all-access pass to the database. It’s not the connection itself; it’s the factory that creates connections and a home for the dialect (the system that translates SQLAlchemy’s operations into the specific SQL flavor your database uses, like SQLite, PostgreSQL, etc.). You create it once at the beginning of your application and then reuse it. Its most important job is managing a connection pool, so you’re not starting a new expensive database conversation for every single query.

Here’s how you create one for a SQLite database that will live in a file called mydatabase.db:

from sqlalchemy import create_engine

engine = create_engine("sqlite:///mydatabase.db", echo=True)

That echo=True is your best friend when you’re learning or debugging. It makes the Engine log all the SQL it generates right to the console. You’ll see exactly what it’s sending to the database, which is invaluable for understanding what’s happening and for spotting problems. For production, you’d turn this off unless you’re specifically troubleshooting, unless you enjoy log files that rival the length of War and Peace.

Connections and the Magic of Context Managers

A Connection is the actual, live wire to the database. You get one from the engine. The most Pythonic way to do this is using a context manager (with statement). This is non-negotiable best practice. It ensures the connection is properly closed and any transactional state is handled when you’re done, even if an error occurs. Forgetting to close connections is a classic rookie mistake that leads to applications that mysteriously grind to a halt.

with engine.connect() as conn:
    result = conn.execute(text("SELECT 'Hello, World!'"))
    print(result.scalar())  # Prints 'Hello, World!'

See that text() construct? We have to use it to tell SQLAlchemy “hey, this is a literal string of SQL.” For simple stuff like this, it’s fine. But the real power comes when we let SQLAlchemy generate the SQL for us. Which brings us to…

Defining Tables: The Source of Truth

To let SQLAlchemy generate SQL for us, we first need to tell it about our table schemas. We do this with the Table object and Column objects. This isn’t just busywork; it’s a single source of truth. Define your table structure here, and you can use it to build queries, and even to create the table itself. We use the MetaData object as a registry to collect all these table definitions.

from sqlalchemy import MetaData, Table, Column, Integer, String, Text

metadata = MetaData()

user_table = Table(
    "user_account",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(30)),  # String length is... *sigh* mostly suggested in SQLite.
    Column("fullname", Text),
)

Why String(30) and Text? It’s a holdover from other databases where string length constraints are enforced. SQLite, in its glorious, anarchic simplicity, largely ignores the length you specify on a String type. It’s a perfect example of a “questionable choice” we have to live with. Using Text is often clearer for longer strings, as it maps to the database’s TEXT type instead of VARCHAR.

Crafting Queries with Select()

Now for the fun part. We don’t write SELECT * FROM user_account. We construct a Select object using the select() function and our Table object. This is the heart of SQLAlchemy Core: building a query object that can be passed to a connection to be executed.

from sqlalchemy import select

# The most basic select: all columns
simple_query = select(user_table)

# A more specific select: just two columns
name_query = select(user_table.c.name, user_table.c.fullname)

# A select with a filter (WHERE clause)
specific_user_query = select(user_table).where(user_table.c.name == "spongebob")

Notice the .c attribute on the table? It’s a shorthand for “column.” It’s how you access the columns you defined to use them in your queries. The beauty of this is that it’s all just Python. You can build up these queries programmatically.

To actually run it, you pass the query object to your connection’s execute() method. What you get back is a Result object.

with engine.connect() as conn:
    result = conn.execute(specific_user_query)
    for row in result:
        print(f"User: {row.name} ({row.fullname})")
    # Alternatively, you can access by key: row['name'] or by index: row[0]

The Result is an iterable of Row objects. Each Row acts like a named tuple—you can access data by the column name as an attribute (.name), by the string key (['name']), or by index. The first way is usually the most readable.

This is the foundation. It’s meticulous, but this precision is what gives you immense power and control. You’re not just throwing strings at a database; you’re building structured, composable, and safe queries. Now you’re talking to the database like a pro.