How do I create a NumPy array from results of a pyodbc query?

Question

I would like to create an array or list from values pulled from a SQL query. From research I believe the data I pull from SQL is a tuple.

How do format the data into a list I can use in python?

In my current code I try to use the numpy command np.asarray. I'm not sure if numpy arrays allow datetime.

import numpy as np
import pyodbc

conn = pyodbc.connect('login')
cursor = conn.cursor()
cursor.execute("SELECT PTIME, PVALUE FROM HISTORY_TABLE WHERE POINT = 'Value' AND PTIME> '2017-04-12' AND PTIME<'2017-04-13' AND HISTTYPE='AVG' AND PERIOD=7200")

sample = cursor.fetchall()
rockin = np.asarray(sample)
print rockin

cursor.close()
conn.close()

My result looks like this:

[[datetime.datetime(2017, 4, 12, 0, 0) 232.83]
 [datetime.datetime(2017, 4, 12, 2, 0) 131.49]
 [datetime.datetime(2017, 4, 12, 4, 0) 36.67]
 ..., 
 [datetime.datetime(2017, 4, 12, 18, 0) 82.08]
 [datetime.datetime(2017, 4, 12, 20, 0) 368.83]
 [datetime.datetime(2017, 4, 12, 22, 0) 435.79]]

Thanks Henry, you were right, "Why not Pandas?" So much easier. So, So, much easier. Thank you. — getaglow
– getaglow, Commented May 25, 2017 at 17:52
@getaglow btw I think there's panda examples in the beta docs... (By me?? Can't remember now..) — Rachel Gallen
– Rachel Gallen, Commented May 25, 2017 at 18:41
@getaglow back home and on computer (late!).. no, i started the scipy docs not the panda docs .. knew i did something snake-ish! ;) But yeah, plenty on pandas in the docs if you want to explore.. — Rachel Gallen
– Rachel Gallen, Commented May 25, 2017 at 23:44
@getaglow hi, do you know what those arrows are for beside the answers? I see from you profile you don't vote very much. Upvotes award your helping assistants time and effort (just click the top arrow) .. Reserve your accepts (the hover+click - which you seem to only have done once) for working answers, but you can upvote up to 30 (or is 40?) times in a day, and you should upvote the people who help you along the way to recognise their efforts. (how do you think you got your rep?.. people upvoted your questions! Now you have the privilege to vote up others! Use it.. thanks).. — Rachel Gallen
– Rachel Gallen, Commented Jun 3, 2017 at 9:57

Gord Thompson · Accepted Answer · 2017-05-23 21:29:53Z

2

From research I believe the data I pull from SQL is a tuple.

Not exactly. pyodbc's fetchall() method does not return a list of tuples, it returns a list of pyodbc.Row objects:

>>> rows = crsr.execute("SELECT 1 AS foo UNION ALL SELECT 2 AS foo").fetchall()
>>> rows
[(1, ), (2, )]
>>> type(rows[0])
<type 'pyodbc.Row'>

It could be that np.asarray does not know how to handle pyodbc.Row objects. If you want to convert each row to an actual tuple you can use

>>> rows_as_tuples = [tuple(x) for x in rows]
>>> rows_as_tuples
[(1,), (2,)]
>>> type(rows_as_tuples[0])
<type 'tuple'>

answered May 23, 2017 at 21:29

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

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

Collectives™ on Stack Overflow

How do I create a NumPy array from results of a pyodbc query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related