我有一个Python函数从SQL表中读取到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)
但是它返回sqlalchemy.exc.ProgrammingError
:
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "table"
LINE 1: SELECT * from table WHERE project_id = project
我试着编辑引号,看看这是不是一个修复,但它失败了。什么好主意吗?
解决当前问题的确切方法可能是使用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)
但是,请注意,使用连接和替换以这种方式构建SQL查询字符串是非常不可取的。原因是您的函数会引起SQL注入,这意味着有人可以将恶意SQL代码片段传递到函数中,并试图让您的Python脚本执行它。相反,您应该阅读有关使用预处理语句的内容。
根据Tim的回答,您需要使用f-string将表名称插入到SQL文本中,但是您应该使用参数来指定列值:
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
还注意read_sql_query()
比read_sql()
更可取。