Pandas read_sql not reading specified date

Question

I'm trying to filter my SQL code in Python using a specified date in my where statement. For some reason it isn't filtering based on the date and I'm unsure why. Want to filter on report_dt > '2018-01-01' but result output is giving all data

import pandas as pd
startdate='2018-01-01'
test=pd.read_sql("""SELECT top 10 report_dt FROM db.table where report_dt>{} """.format(startdate) , connection)
print(test)


REPORT_DT
0  2014-02-15
1  2014-02-15
2  2014-02-15

Can you post a sample from this table? Also, are you sure `select top 10` is valid for whatever db you are connecting to? — SuperStew, Feb 20 '18 at 15:19
Maybe you can try something like this: `report_dt>"{}".format(xxx)`. (i.e, quotes around your date) — Zhiya, Feb 20 '18 at 15:20
I was using select top 10 just to get a sample of the data since it's a very large data set. — user3242036, Feb 20 '18 at 15:28

Vikash Singh · Answer 1 · 2018-02-20T15:37:00.943

0

sample Number is how it's done as per the link below.

startdate = '2018-01-01'
test = pd.read_sql("SELECT report_dt FROM db.table where report_dt > ? sample 10", connection, params=(startdate,))

Source: http://community.teradata.com/t5/Database/Limit-rows/td-p/5238

edited Feb 20 '18 at 15:37

answered Feb 20 '18 at 15:24

Vikash Singh

13,213
8
40
70

score 0 · Accepted Answer · answered Feb 20 '18 at 15:35

It's best to pass parameters rather than formatted strings to queries. Give this a try:

startdate = '2018-01-01'
test = pd.read_sql("SELECT top 10 report_dt FROM db.table where report_dt > ?", connection, params=(startdate,))

A better explanation than I can give can be found here (doesn't relate to Teradata specifically, but the concepts apply).

score 0 · Answer 3 · answered Apr 17 '20 at 11:06

Check this link for which syntax paramstyle is configured on the underlying database. https://www.python.org/dev/peps/pep-0249/#id41 - paramstyle section. Paramstyle depends on the underlying database configuration.

Eg- for teradata database-

**
import teradatasql
teradatasql.paramstyle
result- 'qmark'

or

import importlib

def get_paramstyle(conn):
   name = conn.__class__.__module__.split('.')[0]
   mod = importlib.import_module(name)
   return mod.paramstyle

  get_paramstyle(conn=DBconnection) # DBconnection is connection object to the 
  database you want to connect.

#Pass date parameters to pd.read_sql.
date_param = ['2020-04-17']
SQL_string =  " SELECT * FROM tablename WHERE tablename.load_date >= ? "
df = pd.read_sql(sql=SQL_string, con= DBconnection, 
index_col='load_date',parse_dates='load_date',params=(date_param))

Pandas read_sql not reading specified date

3 Answers3