SQLAlchemy for Data Scientists: Querying Databases Without Raw SQL Strings

1. Opening: The Problem With Raw SQL Strings

Picture this: You’re a data scientist, and you’ve just spent three hours building a beautiful data pipeline in a Jupyter notebook. You’ve got your SQL queries working perfectly in your local SQLite database. Your stakeholders are thrilled with the dashboard you’re about to deploy. Life is good.

Then you push your code to the production server, which runs PostgreSQL. Your pipeline crashes with a cryptic error message: near "?": syntax error. It turns out PostgreSQL uses %s for parameter placeholders, not ?. You spend the next two hours rewriting every query.

Sound familiar?

If you’ve ever worked with raw SQL strings in Python, you’ve probably experienced this pain. Raw SQL is brittle. A single missing comma or mismatched quote crashes your pipeline at runtime, not at parse time. It’s database-specific — switching from SQLite to PostgreSQL means rewriting every query. And if you’re using string interpolation to build dynamic WHERE clauses, you’re one misplaced quote away from a SQL injection vulnerability that could expose your entire customer database.

A 2020 Reddit discussion on the tradeoffs between raw SQL and ORMs captures the sentiment well: “Raw SQL is fine until you need to maintain it at scale. Then it becomes a nightmare of string concatenation and debugging.” A popular YouTube video titled “Why You SHOULDN’T Be Writing Raw SQL In 2023” argues that modern tools eliminate the need for hand-written SQL entirely.

But here’s the promise: SQLAlchemy lets you write queries in Python, and the library handles the SQL generation, parameter binding, and database dialect differences for you. No more switching between SQL and Python. No more runtime crashes from typos. No more security scares.

2. What Is SQLAlchemy? (The Two-Layer Architecture)

Before we write any code, let’s build a mental model of what SQLAlchemy actually is.

SQLAlchemy is not just an ORM (Object-Relational Mapper). It’s a database toolkit with two distinct layers:

• Core: The lower-level API. You write Python expressions that compile to SQL strings. Think of it as “SQL in Python clothes.” You’re in full control — no object mapping, no automatic flushing, no identity map. You write select(users).where(users.c.age > 30), and SQLAlchemy compiles that to SELECT * FROM users WHERE age > 30 for whatever database you’re using.

• ORM: The higher-level layer built on top of Core. You define Python classes that map to database tables. You work with objects like user.name = 'Alice', and the ORM translates your object changes into INSERT/UPDATE/DELETE automatically. This is great for ETL pipelines where you’re loading and transforming records.

The SQLAlchemy documentation describes Core as “command oriented” and “schema-centric,” while the ORM is “state oriented” and “domain centric.” For data scientists, Core is often more natural for read-heavy analytical queries — SELECT, GROUP BY, JOIN, aggregates. The ORM shines when you’re building data pipelines that need to insert, update, and track object state.

This article focuses on Core because it maps most directly to the SELECT/WHERE/JOIN queries you already know, without the extra complexity of sessions and identity maps.

Here’s a tiny preview of what we’re building toward. A raw SQL query:

SELECT name, age FROM users WHERE age > 30 ORDER BY age DESC LIMIT 10;

And the SQLAlchemy equivalent:

stmt = select(users.c.name, users.c.age).where(users.c.age > 30).order_by(users.c.age.desc()).limit(10)

No strings. No dialect-specific syntax. Pure Python.

3. Setting Up: Your First Engine

The Engine is the “home base” of everything you do with SQLAlchemy. It’s a long-lived object that manages two critical things:

• Connection pool: A pool of database connections that can be reused across queries. You don’t open and close a connection each time — that’s slow.
• Dialect: The part that translates SQLAlchemy’s generic SQL expressions into the specific SQL dialect of your database. This is how you get portability.

Here’s how to create one:

from sqlalchemy import create_engine

# Create an Engine for a SQLite file database
engine = create_engine('sqlite:///example.db', echo=True)

That echo=True parameter tells SQLAlchemy to print the actual SQL it generates. This is great for learning — you get to see the Python expression and the resulting SQL side by side.

Now let’s verify it works:

import sqlalchemy
from sqlalchemy import create_engine

engine = create_engine('sqlite:///example.db', echo=True)

with engine.connect() as conn:
    result = conn.execute(sqlalchemy.text("SELECT 1"))
    print("Connection successful! Result:", result.fetchone())

When you run this, you’ll see something like:

2024-01-15 10:30:42,123 INFO sqlalchemy.engine.Engine SELECT 1
2024-01-15 10:30:42,124 INFO sqlalchemy.engine.Engine [no key 0.00013s] ()
Connection successful! Result: (1,)

What just happened:

• The Engine lazily connected to the database the first time we called engine.connect().
• SQLAlchemy logged the SQL it executed (the text("SELECT 1") we passed).
• It bound no parameters (the () empty tuple).
• We got back the result (1,), confirming the connection works.

Best practice: Create your Engine once at module level or in a config file, then reuse it everywhere. One Engine per database. Don’t create a new Engine for each query — that defeats the purpose of the connection pool.

4. Defining Your Schema: Tables and Columns Without DDL

In traditional SQL, you’d write a CREATE TABLE statement as a string:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
);

With SQLAlchemy Core, you define your schema as Python objects using MetaData and Table. Let’s see how:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Float, ForeignKey

engine = create_engine('sqlite:///example.db', echo=True)
metadata = MetaData()

# Define the users table
users = Table(
    'users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(100)),
    Column('age', Integer)
)

# Define the orders table, referencing users
orders = Table(
    'orders', metadata,
    Column('id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('amount', Float),
    Column('product', String(200))
)

# Create all tables that don't already exist
metadata.create_all(engine)

When you run metadata.create_all(engine), SQLAlchemy issues the necessary CREATE TABLE statements, but only for tables that don’t already exist. Run it multiple times — it’s safe. No hand-written DDL, no risk of accidentally dropping existing data.

Now here’s the magic part: reflection. If you’re connecting to an existing database, you can load its schema automatically:

from sqlalchemy import create_engine, MetaData, Table

engine = create_engine('sqlite:///example.db', echo=True)
metadata = MetaData()

# Reflect the users table from the database
users_reflected = Table('users', metadata, autoload_with=engine)

# Print the column names
for column in users_reflected.columns:
    print(f"Column: {column.name}, Type: {column.type}")

Output:

Column: id, Type: INTEGER
Column: name, Type: VARCHAR(100)
Column: age, Type: INTEGER

This is huge for data scientists. You don’t need to write any schema definition code — just reflect the existing tables and start querying. The autoload_with=engine parameter tells SQLAlchemy to inspect the database and build the Table object for you.

5. Your First Query: SELECT Without Strings

Now for the core payoff. Let’s build a SELECT query using SQLAlchemy’s expression language — no raw SQL strings anywhere.

First, let’s insert some sample data so we have something to query:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Float, ForeignKey, insert

engine = create_engine('sqlite:///example.db', echo=True)
metadata = MetaData()

users = Table(
    'users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(100)),
    Column('age', Integer)
)

orders = Table(
    'orders', metadata,
    Column('id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('amount', Float),
    Column('product', String(200))
)

metadata.create_all(engine)

# Insert users
with engine.connect() as conn:
    conn.execute(insert(users), [
        {'name': 'Alice', 'age': 35},
        {'name': 'Bob', 'age': 42},
        {'name': 'Charlie', 'age': 28},
        {'name': 'Diana', 'age': 55},
        {'name': 'Eve', 'age': 31},
    ])
    conn.commit()  # For SQLite, commit is needed

Wait — we used insert(users) to insert data. We’ll cover inserts properly in a later article. For now, just see it as a way to get data into the table.

Now the query you came for:

from sqlalchemy import create_engine, MetaData, Table, select

engine = create_engine('sqlite:///example.db', echo=True)
metadata = MetaData()
users = Table('users', metadata, autoload_with=engine)

# Build the query step by step
stmt = (
    select(users.c.name, users.c.age)
    .where(users.c.age > 30)
    .order_by(users.c.age.desc())
    .limit(10)
)

# Execute and fetch results
with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"Name: {row.name}, Age: {row.age}")

Output:

Name: Diana, Age: 55
Name: Bob, Age: 42
Name: Alice, Age: 35
Name: Eve, Age: 31

Let’s interpret what happened:

• select(users.c.name, users.c.age) — We selected two columns from the users table. No strings.
• .where(users.c.age > 30) — Filter to users older than 30. The users.c.age is a Column object, not the data. It’s a reference to the column in SQL expressions.
• .order_by(users.c.age.desc()) — Sort by age descending. .desc() is a method on the Column object.
• .limit(10) — Return at most 10 rows.
• We got 4 users older than 30, sorted from oldest (Diana, 55) to youngest (Eve, 31). If there were more users, the limit of 10 would kick in.

The hardest part for newcomers is the .c. attribute. Remember: users.c is a collection of Column objects. users.c.age is a Column object used in expressions — it’s not the data in the database. It’s the reference to the column.

6. Joins and Aggregates: The Queries Data Scientists Actually Write

Single-table queries are fine, but the real world involves JOINs and aggregates. Let’s see how SQLAlchemy handles those.

First, let’s add some order data:

from sqlalchemy import create_engine, MetaData, Table, insert

engine = create_engine('sqlite:///example.db', echo=True)
metadata = MetaData()
users = Table('users', metadata, autoload_with=engine)
orders = Table('orders', metadata, autoload_with=engine)

with engine.connect() as conn:
    conn.execute(insert(orders), [
        {'user_id': 1, 'amount': 150.0, 'product': 'Laptop'},
        {'user_id': 1, 'amount': 75.0, 'product': 'Mouse'},
        {'user_id': 2, 'amount': 200.0, 'product': 'Monitor'},
        {'user_id': 3, 'amount': 50.0, 'product': 'Keyboard'},
        {'user_id': 1, 'amount': 25.0, 'product': 'USB Cable'},
        {'user_id': 2, 'amount': 100.0, 'product': 'Headphones'},
    ])
    conn.commit()

Now let’s write a JOIN query to get user names with their order amounts:

from sqlalchemy import create_engine, MetaData, Table, select

engine = create_engine('sqlite:///example.db', echo=True)
metadata = MetaData()
users = Table('users', metadata, autoload_with=engine)
orders = Table('orders', metadata, autoload_with=engine)

# INNER JOIN: users and orders
stmt = (
    select(users.c.name, orders.c.amount, orders.c.product)
    .join_from(users, orders, users.c.id == orders.c.user_id)
    .order_by(users.c.name, orders.c.amount.desc())
)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"{row.name} ordered {row.product} for ${row.amount:.2f}")

Output:

Alice ordered Laptop for $150.00
Alice ordered Mouse for $75.00
Alice ordered USB Cable for $25.00
Bob ordered Monitor for $200.00
Bob ordered Headphones for $100.00
Charlie ordered Keyboard for $50.00

Notice that Diana and Eve don’t appear — they have no orders, so an INNER JOIN excludes them. If you want users with no orders to show up, use a LEFT JOIN:

from sqlalchemy import create_engine, MetaData, Table, select

engine = create_engine('sqlite:///example.db', echo=True)
metadata = MetaData()
users = Table('users', metadata, autoload_with=engine)
orders = Table('orders', metadata, autoload_with=engine)

# LEFT OUTER JOIN: all users, even those without orders
stmt = (
    select(users.c.name, orders.c.amount, orders.c.product)
    .select_from(users.outerjoin(orders, users.c.id == orders.c.user_id))
    .order_by(users.c.name)
)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"{row.name}: ${row.amount if row.amount is not None else 'No orders'}")

Output:

Alice: $150.00
Alice: $75.00
Alice: $25.00
Bob: $200.00
Bob: $100.00
Charlie: $50.00
Diana: No orders
Eve: No orders

Now for aggregates — the bread and butter of data science queries:

from sqlalchemy import create_engine, MetaData, Table, select, func

engine = create_engine('sqlite:///example.db', echo=True)
metadata = MetaData()
users = Table('users', metadata, autoload_with=engine)
orders = Table('orders', metadata, autoload_with=engine)

# Aggregate: count orders per user
stmt = (
    select(users.c.name, func.count(orders.c.id).label('order_count'))
    .select_from(users.outerjoin(orders, users.c.id == orders.c.user_id))
    .group_by(users.c.id)
    .order_by(func.count(orders.c.id).desc())
)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"{row.name}: {row.order_count} orders")

Output:

Alice: 3 orders
Bob: 2 orders
Charlie: 1 order
Diana: 0 orders
Eve: 0 orders

Let’s break down what’s happening:

• func.count(orders.c.id).label('order_count') — The func object wraps SQL functions. .label() gives the result a name you can reference in Python as row.order_count.
• .select_from(users.outerjoin(orders, ...)) — We use a LEFT JOIN so users with no orders still appear.
• .group_by(users.c.id) — Group by user ID so the count applies per user.
• .order_by(func.count(orders.c.id).desc()) — Most orders first.

Why this matters for data scientists: you can build these queries dynamically. Loop over a list of columns to GROUP BY. Conditionally add a WHERE clause. Compose queries programmatically — impossible with raw SQL strings without messy string concatenation.

7. Pandas Integration: From Query to DataFrame in One Line

This is the killer feature for data scientists. Instead of iterating over query results and building a DataFrame manually, you can use pd.read_sql_query() directly:

import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, select, func

engine = create_engine('sqlite:///example.db')
metadata = MetaData()
users = Table('users', metadata, autoload_with=engine)
orders = Table('orders', metadata, autoload_with=engine)

# Build the query
stmt = (
    select(users.c.name, func.count(orders.c.id).label('order_count'), func.sum(orders.c.amount).label('total_spent'))
    .select_from(users.outerjoin(orders, users.c.id == orders.c.user_id))
    .group_by(users.c.id)
    .order_by(func.sum(orders.c.amount).desc().nulls_last())
)

# Load directly into a DataFrame
df = pd.read_sql_query(stmt, engine)
print(df.head())
print(f"\nSummary statistics:\n{df.describe()}")

Output:

     name  order_count  total_spent
0    Alice            3       250.0
1      Bob            2       300.0
2  Charlie            1        50.0
3    Diana            0         NaN
4      Eve            0         NaN

Summary statistics:
       order_count  total_spent
count     5.000000     3.000000
mean      1.200000   200.000000
std       1.303840   125.299082
min       0.000000    50.000000
25%       0.000000   100.000000
50%       1.000000   250.000000
75%       2.000000   275.000000
max       3.000000   300.000000

What just happened:

• pd.read_sql_query(stmt, engine) executed the query through the engine and returned a DataFrame. The column names match the ones we specified (or labeled) in the query.
• The total_spent column shows NaN for Diana and Eve because they have no orders — pandas handles NULL values naturally.
• df.describe() gives us summary statistics: Alice spent the most ($250), Bob spent the most total ($300), and the average customer with orders spent $$200.

You can also read an entire table into a DataFrame without writing any query:

import pandas as pd
from sqlalchemy import create_engine, MetaData, Table

engine = create_engine('sqlite:///example.db')

df_users = pd.read_sql_table('users', engine)
print(df_users.head())

This is the workflow: define schema (or reflect) → build query with SQLAlchemy expressions → execute into DataFrame → analyze in pandas. No raw SQL strings at any step.

8. SQLAlchemy vs. Raw SQL: The Honest Tradeoffs

I’ve been singing the praises of SQLAlchemy, but let’s be honest about the tradeoffs. No tool is perfect for every situation.

Performance overhead: SQLAlchemy adds overhead. A benchmark of a ~500-row PostgreSQL table found that psycopg2 (raw SQL driver) completed a SELECT * in about 1.15 seconds, while SQLAlchemy took about 2.60 seconds — roughly 2x slower. For simple queries on small tables, that overhead matters.

However, for analytical queries on large datasets (millions of rows), the bottleneck is almost always the database itself, not the Python library. The query execution time dwarfs the overhead of SQLAlchemy’s query compilation and result processing. A complex JOIN with aggregates that takes 30 seconds to run in the database might see a difference of only 0.5 seconds between raw SQL and SQLAlchemy.

Learning curve: You have to learn SQLAlchemy’s API on top of knowing SQL. If you already know SQL well, writing queries in SQL is faster initially. But once you’re comfortable with SQLAlchemy, you get the benefits of portability, security, and programmatic composition.

The escape hatch: For the 10% of queries that are too complex or database-specific to express cleanly in the expression language, you can always fall back to raw SQL with text():

from sqlalchemy import create_engine, text

engine = create_engine('sqlite:///example.db')

with engine.connect() as conn:
    stmt = text("SELECT * FROM users WHERE age > :min_age")
    result = conn.execute(stmt, {'min_age': 30})
    for row in result:
        print(f"Name: {row.name}, Age: {row.age}")

The text() function wraps a raw SQL string and allows parameter binding with :param syntax, safe from SQL injection.

The hybrid approach: Use SQLAlchemy Core for the 90% of queries that fit naturally into the expression language. Fall back to text() for the rest — database-specific functions like DISTINCT ON in PostgreSQL, or extremely complex window functions. You get the best of both worlds.

9. Recap: What You Learned

Let’s summarize what you’ve learned in this article:

• SQLAlchemy has two layers: Core (SQL Expression Language) and ORM (object mapping). We focused on Core, which maps most directly to the SELECT/WHERE/JOIN queries data scientists write.
• The Engine is your connection pool and dialect manager: Create one Engine per database, reuse it everywhere. The Engine handles lazy connection, pooling, and dialect translation.
• Define schema with MetaData and Table objects: Or reflect existing tables from the database. No hand-written DDL needed.
• Build SELECT queries with select(), .where(), .order_by(), .limit(), .join(), .group_by(), and func.aggregate(): All in Python, no raw SQL strings.
• Execute queries with engine.connect() and fetch results as named tuples: Iterate over rows with for row in result.
• Pandas integration with pd.read_sql_query(stmt, engine) or pd.read_sql_table('table', engine): One line from query to DataFrame.
• SQLAlchemy is not always faster than raw SQL: But it’s safer, more portable, and easier to compose programmatically. Use the hybrid approach for the best of both worlds.

10. Next Steps: What’s Coming in Part 2

In the next article of this series, we’ll dive into the SQLAlchemy ORM. You’ll learn:

• How to define models with DeclarativeBase and Mapped annotations
• How to work with Session objects and the unit-of-work pattern
• How the ORM handles INSERT/UPDATE/DELETE automatically

We’ll build on the same users/orders schema from this article, but now you’ll work with Python objects instead of Table/Column references. If you’re doing ETL or data pipeline work, the ORM will save you even more time than Core does for read queries.

11. Check Your Understanding

Remember: What is the difference between SQLAlchemy Core and SQLAlchemy ORM?

Understand: Explain in your own words why the Engine is created once and reused, rather than creating a new Engine for each query.

Apply: Given a table products with columns id, name, price, category, write a SQLAlchemy Core query to select the name and price of all products in category ‘Electronics’ with a price greater than 100, sorted by price descending.

Analyze: Compare the SQLAlchemy approach to querying a database with raw SQL strings. What are the specific advantages and disadvantages in a data science workflow?

Evaluate: You’re building a data pipeline that queries a PostgreSQL database, transforms the data, and loads it into a data warehouse. Would you recommend using SQLAlchemy Core, raw SQL with psycopg2, or a hybrid approach? Justify your answer.

Create: Design a small schema for a blog database (users, posts, comments) using SQLAlchemy Core’s MetaData/Table. Write a query that returns the top 5 users by number of posts, including the user’s name and post count.

12. Apply What You Learned