Mixing
Python and MSSQL: Filtering techniques while retrieving data from SQL
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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.
python
sql-server edited Jan 3 at 5:57
Dale Burrell
3,43452655
asked Jan 3 at 5:49
pythondumbpythondumb
14510
add a comment |
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.
python
sql-server edited Jan 3 at 5:57
Dale Burrell
3,43452655
asked Jan 3 at 5:49
pythondumbpythondumb
14510
What is the actual value ofsqlthat you pass to the server?
– DYZ
Jan 3 at 5:57
add a comment |
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.
python
sql-server edited Jan 3 at 5:57
Dale Burrell
3,43452655
asked Jan 3 at 5:49
pythondumbpythondumb
14510
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.
python
sql-server python
sql-server edited Jan 3 at 5:57
Dale Burrell
3,43452655
asked Jan 3 at 5:49
pythondumbpythondumb
14510
edited Jan 3 at 5:57
Dale Burrell
3,43452655
asked Jan 3 at 5:49
pythondumbpythondumb
14510
edited Jan 3 at 5:57
Dale Burrell
3,43452655
edited Jan 3 at 5:57
Dale Burrell
3,43452655
edited Jan 3 at 5:57
Dale Burrell
3,43452655
3,43452655
asked Jan 3 at 5:49
pythondumbpythondumb
14510
asked Jan 3 at 5:49
pythondumbpythondumb
14510
asked Jan 3 at 5:49
pythondumbpythondumb
14510
14510
What is the actual value ofsqlthat you pass to the server?
– DYZ
Jan 3 at 5:57
add a comment |
What is the actual value ofsqlthat you pass to the server?
– DYZ
Jan 3 at 5:57
What is the actual value of
sql that you pass to the server?– DYZ
Jan 3 at 5:57
What is the actual value of
sql that you pass to the server?– DYZ
Jan 3 at 5:57
add a comment |
2 Answers
2
active
oldest
votes
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 at 6:43
SQL_MSQL_M
1,755718
Thanks SQL_M. It worked. Actually, what I did wassql='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.
– pythondumb
Jan 3 at 8:04
Glad that it worked, you are welcome.
– SQL_M
Jan 3 at 8:13
add a comment |
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 at 6:16
Edmond SesayEdmond Sesay
574
parse_datesis applied after the query is executed.
– DYZ
Jan 3 at 6:20
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.
– Edmond Sesay
Jan 3 at 6:38
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54016965%2fpython-and-mssql-filtering-techniques-while-retrieving-data-from-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 at 6:43
SQL_MSQL_M
1,755718
Thanks SQL_M. It worked. Actually, what I did wassql='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.
– pythondumb
Jan 3 at 8:04
Glad that it worked, you are welcome.
– SQL_M
Jan 3 at 8:13
add a comment |
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 at 6:43
SQL_MSQL_M
1,755718
Thanks SQL_M. It worked. Actually, what I did wassql='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.
– pythondumb
Jan 3 at 8:04
Glad that it worked, you are welcome.
– SQL_M
Jan 3 at 8:13
add a comment |
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 at 6:43
SQL_MSQL_M
1,755718
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 at 6:43
SQL_MSQL_M
1,755718
answered Jan 3 at 6:43
SQL_MSQL_M
1,755718
answered Jan 3 at 6:43
SQL_MSQL_M
1,755718
answered Jan 3 at 6:43
SQL_MSQL_M
1,755718
1,755718
Thanks SQL_M. It worked. Actually, what I did wassql='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.
– pythondumb
Jan 3 at 8:04
Glad that it worked, you are welcome.
– SQL_M
Jan 3 at 8:13
add a comment |
Thanks SQL_M. It worked. Actually, what I did wassql='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.
– pythondumb
Jan 3 at 8:04
Glad that it worked, you are welcome.
– SQL_M
Jan 3 at 8:13
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.– pythondumb
Jan 3 at 8:04
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.– pythondumb
Jan 3 at 8:04
Glad that it worked, you are welcome.
– SQL_M
Jan 3 at 8:13
Glad that it worked, you are welcome.
– SQL_M
Jan 3 at 8:13
add a comment |
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 at 6:16
Edmond SesayEdmond Sesay
574
parse_datesis applied after the query is executed.
– DYZ
Jan 3 at 6:20
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.
– Edmond Sesay
Jan 3 at 6:38
add a comment |
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 at 6:16
Edmond SesayEdmond Sesay
574
parse_datesis applied after the query is executed.
– DYZ
Jan 3 at 6:20
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.
– Edmond Sesay
Jan 3 at 6:38
add a comment |
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 at 6:16
Edmond SesayEdmond Sesay
574
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 at 6:16
Edmond SesayEdmond Sesay
574
answered Jan 3 at 6:16
Edmond SesayEdmond Sesay
574
answered Jan 3 at 6:16
Edmond SesayEdmond Sesay
574
answered Jan 3 at 6:16
Edmond SesayEdmond Sesay
574
574
parse_datesis applied after the query is executed.
– DYZ
Jan 3 at 6:20
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.
– Edmond Sesay
Jan 3 at 6:38
add a comment |
parse_datesis applied after the query is executed.
– DYZ
Jan 3 at 6:20
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.
– Edmond Sesay
Jan 3 at 6:38
parse_dates is applied after the query is executed.– DYZ
Jan 3 at 6:20
parse_dates is applied after the query is executed.– DYZ
Jan 3 at 6:20
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.
– Edmond Sesay
Jan 3 at 6:38
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.
– Edmond Sesay
Jan 3 at 6:38
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54016965%2fpython-and-mssql-filtering-techniques-while-retrieving-data-from-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is the actual value of
sqlthat you pass to the server?– DYZ
Jan 3 at 5:57