Python and MSSQL: Filtering techniques while retrieving data from SQL

Question

I have a MS SQL Table as follows

Device ID       Timestamp               Avg_PF  THDV_Sum
863071010842661 2014-01-01 22:05:57     4.0       7.0   
865733020495321 2016-08-19 17:20:09     0.0       0.0  
865733020495321 2016-08-19 17:20:41     0.0       0.0   
865733020495321 2016-08-19 17:20:41     0.0       0.0

There are 287,533 rows comprising data for 30 devices (i.e. there are 30 unique Device ID) at 10/15 mins interval. I want to retrieve data where TimeStamp date >=2018-10-01. In SSMS (SQL server 2014 Management Tool) I am able to do this easily using the following SQL

SELECT Device ID, Timestamp, Avg_PF, THDV_Sum 
FROM mytable
WHERE Timestamp >= '2018-10-01'

Now I am trying to the same on python using the following way

conn = pyodbc.connect('details of SQL server')
df_select = pd.read_sql_query(sql,conn)

Here I am using the above SQL statement as sql string. However, it is retrieving the entire data starting from timestamp = 2014-01-01. I think I need to modify the sql string in the pd.read_sql_query. My question is how can I add filter like stuffs in sql string which I can use in pd.read_sql_query.

What is the actual value of sql that you pass to the server? — DYZ
– DYZ, Commented Jan 3, 2019 at 5:57

SQL_M · Accepted Answer · 2019-01-03 06:43:36Z

2

I would go about it like this:

from sqlalchemy import create_engine
%%time -- just to measure

# Parameters
ServerName = "SQLSRV01" -- your input
Database = "Database"
Driver = "driver=SQL Server Native Client 11.0"

# Create the connection
engine = create_engine('mssql+pyodbc://' + ServerName + '/' + Database + "?" + Driver)

df = pd.read_sql_query ("SELECT Device ID, Timestamp, Avg_PF, THDV_Sum 
                         FROM mytable
                         WHERE Timestamp >= '2018-10-01'"
                       , engine)

answered Jan 3, 2019 at 6:43

SQL_M

2,4852 gold badges18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pythondumb Over a year ago

Thanks SQL_M. It worked. Actually, what I did was sql='SELECT Device ID, Timestamp, Avg_PF, THDV_Sum FROM mytable WHERE Timestamp >= "2018-10-01" ' i.e. just reverse the position of single quote by double quote.

Edmond Sesay · Accepted Answer · 2019-01-03 06:16:06Z

0

Use the parse_dates argument of the read_sql_query function like so:

df_select = pd.read_sql_query(sql, conn, parse_dates=['Timestamp'])

answered Jan 3, 2019 at 6:16

Edmond Sesay

996 bronze badges

2 Comments

DYZ Over a year ago

parse_dates is applied after the query is executed.

Edmond Sesay Over a year ago

I'm not sure about that. Could you provide some documentation for this assumption? Anyway, I think what is important for OP is what is retrieved after the query is ran and using the parse_dates argument as mentioned would get the job done.

Collectives™ on Stack Overflow

Python and MSSQL: Filtering techniques while retrieving data from SQL

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related