SQLAlchemy: using variable table name

Question

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.

Gord Thompson · Accepted Answer · 2021-04-05 14:09:49Z

4

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]')]

edited Apr 5, 2021 at 14:09

answered Dec 24, 2019 at 14:06

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BT3 Over a year ago

Can I truncate a table by this way?

Gord Thompson Over a year ago

Re: TRUNCATE TABLE - Look here

Collectives™ on Stack Overflow

SQLAlchemy: using variable table name

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related