SQLAlchemy returns script parameters instead of script results

I’m trying to use SQLAlchemy 1.4 to query an Oracle database:

SELECT MAX(:created_date) FROM EXAMPLE_TABLE

This should return some kind of datetime object.

When I execute this code (roughly) using SQLAlchemy, it decides to return the value of the :created_date parameter instead.

from sqlalchemy import create_engine as SQLAlchemyEngine
from sqlalchemy import text as SQLAlchemyText

with SQLAlchemyEngine("example").connect() as sqlalchemy_connection:
    with open(sql_script_path) as sql_script:

        sql_query = SQLAlchemyText(sql_script.read())
        parameters = {"created_date": "blah"}

        result = connection.execute(sql_query, parameters)

        for row in result:
            print(row)

Why is the result (literally) “blah“?

EDIT:: See snakecharmerb’s answer below; you can’t bind column or table names with SQLAlchemy. It’s a bit is hidden in the SQLAlchemy docs:

Binding Column and Table Names

Column and table names cannot be bound in SQL queries. You can concatenate text to build up a SQL statement, but make sure you use an Allow List or other means to validate the data in order to avoid SQL Injection security issues.

Answer

The behaviour described in the question is consistent with trying to set column names in the query using parameter binding. For example given this initial setup:

import datetime            
    
import sqlalchemy as sa                                           

engine = sa.create_engine('sqlite://', future=True)
    
tbl = sa.Table(    
    't',    
    sa.MetaData(),    
    sa.Column('id', sa.Integer, primary_key=True),    
    sa.Column('created_date', sa.Date),    
)    
tbl.create(engine)    
    
dates = [datetime.date(2022, 9, 30), datetime.date(2022, 10, 1)]    
with engine.begin() as conn:    
    conn.execute(tbl.insert(), [{'created_date': d} for d in dates])    
    
del tbl 

Then executing this code:

q = 'SELECT :created_date FROM t'    
    
with engine.connect() as conn:    
    rows = conn.execute(sa.text(q), {'created_date': 'blah'})    
    for row in rows:    
        print(row)

results in this output:

('blah',)
('blah',)

The problem is that parameter binding is designed for values, not identifiers like column or table names. To use dynamic identifiers you need to either construct the query string manually, or consider using SQLAlchemy’s reflection capabilities to build the query using objects:

# Reflect the table.
reflected_tbl = sa.Table('t', sa.MetaData(), autoload_with=engine)
# Specify the column(s) to be selected.
q2 = sa.select(reflected_tbl.c['created_date'])

with engine.connect() as conn:
    rows = conn.execute(q2)
    for row in rows:
        print(row)

outputs:

(datetime.date(2022, 9, 30),)
(datetime.date(2022, 10, 1),)