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

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?

CodePudding user response：

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.

CodePudding user response：

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().