在python中使用f-string sql查询

Question

假设我有一个函数返回pandas-read sql调用：

def recommendations_for_user(user_id, threshold=4.5):
# Join with the courses table
query = """
SELECT title, rating FROM recommendations
INNER JOIN courses ON courses.course_id = recommendations.course_id
WHERE user_id=%(user_id)s AND rating>%(threshold)s
ORDER BY rating DESC
"""
# Add the threshold parameter
predictions_df = pd.read_sql(query, db_engine, params = {"user_id": user_id, 
"threshold": threshold})
return predictions_df.title.values

我试着使用f-string，因为这是推荐的新方法：

def recommendations_for_user(user_id, threshold=4.5):
# Join with the courses table
query = f"""
SELECT title, rating FROM recommendations
INNER JOIN courses ON courses.course_id = recommendations.course_id
WHERE user_id={user_id} AND rating>{threshold}
ORDER BY rating DESC
"""
# Add the threshold parameter
predictions_df = pd.read_sql(query, db_engine, params = {"user_id": user_id, 
"threshold": threshold})
return predictions_df.title.values

但这似乎并不奏效。

Answer 1

对于SQL查询，我一直使用f字符串。Redshift、RDS、MySql、Sqlite没有问题。

我的猜测是user是一个字符串，所以它需要单引号。如果它确实是一个long或int，那么它一定是其他问题。

query = f"""
SELECT title, rating FROM recommendations
INNER JOIN courses ON courses.course_id = recommendations.course_id
WHERE user_id='{user_id}' AND rating>{threshold}
ORDER BY rating DESC;
"""