SQLAlchemy returns script parameters instead of script results

Question

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.

in your for loop youre looping through "result" with a variable "row", that you probably want to print, but are printing "result" instead — Nelala_
– Nelala_, Commented Sep 29, 2022 at 20:40
@Nelala_ I fixed this in the code example. In either case, result does always up being ('blah',). — alex
– alex, Commented Sep 29, 2022 at 20:44
Could you please provide a simple example of the table* structure with an example row of dummy data? — Nelala_
– Nelala_, Commented Sep 29, 2022 at 21:00
@Nelala_ It returns the column name, but I don't need to know the column name, I need to know the maximum "created date" present in my table. — alex
– alex, Commented Sep 29, 2022 at 21:14

snakecharmerb · Accepted Answer · 2022-09-30 08:55:55Z

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

Brutal - I assumed that bind variables / textual SQL are just performing normal string interpolation prior to SQL execution. I guess not! Thanks.

Collectives™ on Stack Overflow

SQLAlchemy returns script parameters instead of script results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related