CODEWITHSUNDEEP
pythonmysqlpandasdatabasedataframe
Baljindra IT
Baljindra IT

Reputation: 1

Unable to fetch the data between 2 dateTime

from pymysql import *
import pandas.io.sql as sql
from datetime import datetime, date, timedelta

current_date = date.today()
print(current_date)

current_date2 = current_date - timedelta(1)
print("Yesterday was: ", current_date2)

# connect the mysql with the python
con = connect(user="root", password="12345", host="localhost", database="test")

df = sql.read_sql('select * from tbl1 where datetime1 >= "2020-11-15" and datetime1 <= '"current_date"' ', con)
# print the data
print(df)

When I try to pass the DateTime as a variable then it generates an error:

pandas.io.sql.DatabaseError: Execution failed on sql 'select * from tbl1 where datetime1 >= "current_date2" and datetime1 <= current_date ': (1525, "Incorrect DATETIME value: 'current_date2'")

Upvotes: 0

Views: 215

Answers (2)

bonifacio_kid
bonifacio_kid

Reputation: 1063

No need to make variable current_date just use CURDATE() inside select.

conn = pymysq.connect(user,psswd,db,host)
cursor = conn.cursor()
cursor.execute("SELECT * FROM table WHERE datetime1>'2020-11-15' AND datetime1<=CURDATE()")
df = cursor.fetchall()
print (df)

Upvotes: 0

Jay
Jay

Reputation: 11

Try this:

'select * from tbl1 where datetime1 >= "2020-11-15" and datetime1 <= "{0}"'.format(current_date)

It should look like this now

df = sql.read_sql('select * from tbl1 where datetime1 >= "2020-11-15" and datetime1 <= "{0}"'.format(current_date), con)

if 2 parameters then:

df = sql.read_sql('select * from tbl1 where datetime1 >= "{0}" and datetime1 <= "{1}"'.format(current_date2,current_date), con)

Upvotes: 1

Related Questions