0

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)

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'"""

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)

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!

Improve this question
7
  • what about using string formatting? i.e. """SELECT * FROM TABLE WHERE DATE BETWEEN {st} AND sysdate-1""".format(st=starting_time) Commented Oct 31, 2017 at 19:56
  • emmm..it says 'ORA-00905: missing keyword', maybe its format has to be modified. not sure Commented Oct 31, 2017 at 20:54
  • @kstullich ... forgot the quotes around the formatted string. But don't interpolate. Use parameterization. Commented Oct 31, 2017 at 21:50
  • @Parfait no I didn't forget the quotes, starting_time is a variable. Commented Oct 31, 2017 at 21:52
  • @kstullich ... inside the SQL you would need to quote: BETWEEN '{st}' Commented Oct 31, 2017 at 21:52

1 Answer 1

Reset to default
0

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})
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

SQLAlchemy is not required to use parameters. You can use parameters with any database driver.
Correct @MartijnPieters. One can use SQLAlchemy or raw connection to pass params to pandas.read_sql. I hope above did not imply it was only method. I recommended the use of SQLAlchemy as pandas docs usually prefer it with raw connection as fallback mode and especially if manipulating database with DataFrame.to_sql.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.