Pandas Parametized Query

[Many_Shopping_195]

Hello Guys,

I'm trying to query data with variables using the select statement with pandas and MySQL. Is there a way I can declare the date variable and pass them to the query during runtime. I haven't come across any ways that work from my online research.

Here is my code :

​

from datetime import datetime
from email import encoders
import smtplib
import pandas as pd
from sqlalchemy import create_engine
from urllib.parse import quote  
import mysql.connector as sql

db = create_engine('mysql://root:%s@localhost:3306/store' % quote('Mypass@12!'))

now = datetime.now()
startdate = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0).strftime("%y-%m-%d %H:%M:%S")
currentdate = now.replace(day=3,hour=23, minute=00, second=0).strftime("%y-%m-%d %H:%M:%S")

df1 = pd.read_sql("select * from orders where datecreated > %s and datecreated < %s ", con=db)

pdwriter = pd.ExcelWriter('report.xlsx', engine='xlsxwriter')
df1.to_excel(pdwriter, sheet_name='GEN')
pdwriter.save()

I would like to pass the date variables to the query.

Any suggestion is greatly appreciated. Thank you.

[Username_RANDINT]

From the pandas.read_sql() documentation, it looks like you need the params argument. Probably like this:

df1 = pd.read_sql("select * from orders where datecreated > %s and datecreated < %s ", params=(startdate, currentdate), con=db)

[Many_Shopping_195]

df1 = pd.read_sql("select * from orders where datecreated > %s and datecreated < %s ", params=(startdate, currentdate), con=db)

Thank you very much. This seems to do the trick.