在Python中将日期变量设置为SQL查询 [英] Set a date variable to SQL query in Python

问题描述

我想在python中执行sql查询。我可以使用cx_oracle在python中连接数据库：

I want to do sql query in python. I could use cx_oracle to connection database in python:

# Build connection
conn_str = u'username/password@host:1521/sid'
conn = cx_Oracle.connect(conn_str)

现在尝试使用Python中的SQL查询从数据库检索数据：

Now I'm trying to retrieve data from the database by using SQL query in Python:

sql_select_statement = """SELECT * FROM TABLE 
WHERE DATE BETWEEN '20-oct-2017' AND '30-oct-2017'"""

假设我们不知道开始日期，我们只有一个名为starting_time的日期变量，其值为日期时间％m /％d /％Y。另外，Ending_time是昨天，我想将我的SQL查询修改为：

Assume we don't know the starting date, we only have a date variable called starting_time, and its value is a datetime %m/%d/%Y. Also, ending_time is yesterday, I would like to modify my SQL query as:

sql_select_statement = """SELECT * FROM TABLE 
WHERE DATE BETWEEN '20-oct-2017' AND sysdate-1"""

df = pd.read_sql(sql_select_statement, conn)

它可以工作并生成新的df，但是如何用变量starting_time替换'20 -oct-2017'？它位于sql查询中，并且采用日期时间格式，因此常规的python方法（如dd％变量）无效。如何解决这个问题呢？谢谢！

It works and generate a new df, but how to replace '20-oct-2017' with the variable starting_time? It's inside the sql query, and it's datetime format, so general python method like 'd%' % variable doesn't work. How to solve this problem? Thanks!

推荐答案

考虑 SQLAlchemy 连接熊猫并使用params 参数版本/0.20/generated/pandas.read_sql.html rel = nofollow noreferrer> pandas.read_sql 将变量绑定到SQL语句：

Consider SQLAlchemy to connect pandas and use the params argument of pandas.read_sql to bind variable to SQL statement:

from sqlalchemy import create_engine

engine = create_engine("username/password@host:1521/sid")

sql_select_statement = "SELECT * FROM TABLE WHERE DATE BETWEEN :my_date AND sysdate-1"

my_var = '20-oct-2017'
df = pd.read_sql(sql_select_statement, engine, params={'my_date':my_var})

继续使用原始与参数化的连接：

Alternatively, continue to use the raw connection with parameterization:

sql_select_statement = "SELECT * FROM TABLE WHERE DATE BETWEEN :my_date AND sysdate-1"

my_var = '20-oct-2017'
df = pd.read_sql(sql_select_statement, conn, params={'my_date':my_var})

这篇关于在Python中将日期变量设置为SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！