CODEWITHSUNDEEP
pythonsqlalchemy
BT3
BT3

Reputation: 395

SQLAlchemy: using variable table name

I need to perform SQL statements using SQLAlchemy with a variable table name (come from an external source). I tried several options but no one was successful.

Attempt 1: With text():

def empty_table(table_name,testf):
        with db.connect() as conn:
            s = text("SELECT * FROM $table_name")
            rp = conn.execute(s)

receive:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "$" LINE 1: SELECT * FROM $table_name

Attempt 2: With select():

with db.connect() as conn:
            s = select([table_name]) 
            rp = conn.execute(s)

receive:

sqlalchemy.exc.ArgumentError: Textual column expression 'my_test_table' should be explicitly declared with text('my_test_table'), or use column('my_test_table') for more specificity

I found in How to do a sqlalchemy query using a string variable as table name? that it possible to resolve the problem using eval(my_variable) but there is no detailed explanation.

Upvotes: 1

Views: 4151

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123409

For a simple SELECT * FROM ... query you could just do

import sqlalchemy as sa

# ...

# test data
table_name = 'user_table'

# test code
some_table = sa.Table(table_name, sa.MetaData(), autoload_with=engine)
query = some_table.select()
with engine.begin() as conn:
    result = conn.execute(query).fetchall()
    print(result)
    # [(1, 'gord', '[email protected]'), (2, 'user2', '[email protected]')]

Upvotes: 4

Related Questions