Using Python variables for table name and column value in an SQL query

Question

I have a Python function to read from an SQL table into a pandas DataFrame:

def project_cable_collector(dbase, table, project):
    engine = create_engine(dbase)
    df = pd.read_sql('SELECT * from table WHERE project_id = project', engine)
    return (df)

However it returns sqlalchemy.exc.ProgrammingError:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "table"
LINE 1: SELECT * from table WHERE project_id = project

I tried editing quotation marks to see if that's a fix, but it fails. Any ideas?

I think that the topic of using placeholder or creating queries with variables should be part of any SQLAlchemy tutorial, just keep reading. As a new user here, please also take the tour and read How to Ask. — Ulrich Eckhardt
– Ulrich Eckhardt, Commented Dec 19, 2022 at 17:14

Tim Biegeleisen · Accepted Answer · 2022-12-19 15:55:26Z

1

An exact fix to your current problem might be to use an f-string:

def project_cable_collector(dbase, table, project):
    engine = create_engine(dbase)
    sql = f"SELECT * FROM {table} WHERE project_id = {project}"
    df = pd.read_sql(sql, engine)
    return (df)

However, note that it is highly undesirable to build a SQL query string this way using concatenation and substitution. The reason is that your function invites something called SQL injection, which means that someone could pass in a malicious SQL code fragment into the function and try to get your Python script to execute it. Instead, you should read about using prepared statements.

answered Dec 19, 2022 at 15:55

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

1 Comment

Mhargr Over a year ago

Thank you for the answer - I'll look into prepared statements!

Gord Thompson · Accepted Answer · 2022-12-19 17:10:30Z

0

Further to Tim's answer, you'll need to use an f-string to insert the table name into the SQL text, but you should use a parameter to specify the column value:

from sqlalchemy import text

# …

def project_cable_collector(dbase, table, project):
    engine = create_engine(dbase)
    sql = f"SELECT * FROM {table} WHERE project_id = :project_id"
    df = pd.read_sql_query(text(sql), engine, params=dict(project_id=project))
    return df

Note also that read_sql_query() is preferable to read_sql().

answered Dec 19, 2022 at 17:10

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Collectives™ on Stack Overflow

Using Python variables for table name and column value in an SQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related