Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 37 ยท Engine and Connections

Running Text SQL with Bound Parameters

Run hand-written SQL safely with text(), named bound parameters, typed bindparam, executemany, IN lists with expanding parameters, and exec_driver_sql.

SQLAlchemy does not force you to build every query from Python objects. Sometimes the best tool is a SQL string you already have: a report query from an analyst, a vendor-specific feature, or a quick check in a script. For these cases use text(). It marks a string as SQL and lets you add bound parameters so values never get pasted into the statement.

This lesson teaches the safe way to run raw SQL, and the mistakes to avoid. The later Core chapter shows how to build the same statements without strings.

Named parameters

In a text() string, :name is a placeholder. You supply a dictionary of values when you execute.

Python text() with :name parameters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Passing a list of dictionaries (the INSERT above) runs the statement once per dictionary in an efficient executemany call. Passing a single dictionary runs it once.

Why parameters, not f-strings

Compare these two approaches with hostile input:

Python Injection with f-strings vs bound parameters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The f-string version changed the meaning of the query and returned every secret. With a bound parameter the whole string x' OR '1'='1 is treated as one value to compare against, so nothing matches. Never build SQL by formatting user input into a string. You can only bind values; table and column names cannot be parameters.

Be careful with a colon inside a string that is not a parameter, such as a time literal '12:30' or a PostgreSQL cast x::int. text() treats :name as a placeholder. Escape a literal colon with a backslash (\:) or use CAST(x AS integer).

Typed parameters with bindparam

By default SQLAlchemy infers the type of each value from the Python object. When you want to force a type, for example to convert dates or to bind a NULL, use bindparam() and .bindparams() on the text construct.

Python bindparam with a type

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Attaching .columns(...) tells SQLAlchemy what types to expect back, so the day column arrives as a real datetime.date rather than a string.

IN lists

A WHERE id IN (...) list has a variable length, which a fixed placeholder cannot express. Use an expanding parameter, and SQLAlchemy renders one placeholder per item at execution time.

Python Expanding IN parameters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Other ways to run SQL strings

  • conn.exec_driver_sql("SELECT ?", (1,)) bypasses text() and sends the string to the driver untouched, using that driver's own placeholder style (?, %s). Use it only when you need exact control.
  • conn.execute(text(...)) is the right default: portable named parameters and consistent results.
  • You can mix: build most of a query with select() and drop in a fragment with text(), for example select(table).where(text("lower(name) = :n")).

Raw SQL is a tool, not a failure. Keep it for cases where the expression language gets in the way, and keep every value bound.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main security benefit of text("... WHERE name = :n") with {"n": value}?

    Bound parameters keep user data out of the SQL text, which prevents SQL injection.
  2. 2. How do you run one INSERT for many rows efficiently?

    A list of parameter dictionaries triggers executemany.
  3. 3. Which feature supports WHERE col IN :values with a list of any length?

    An expanding bind parameter renders one placeholder per element when the statement runs.
  4. 4. Can a table name be supplied as a bound parameter?

    Identifiers are part of the SQL structure. Validate them against an allow-list or use Core table objects.

Summary

  • text() runs raw SQL with :name placeholders and a dictionary of values.
  • A list of dictionaries means executemany; never format user input into the SQL string.
  • bindparam() sets types; expanding=True handles IN lists; .columns() types the results.
  • Only values can be bound; identifiers cannot.

Next: Working with Results and Rows