I am trying to retrieve data from an SQL server using pyodbc and print it in a table using Python. However, I can only seem to retrieve the column name and the data type and stuff like that, not the actual data values in each row of the column.
Basically I am trying to replicate an Excel sheet that retrieves server data and displays it in a table. I am not having any trouble connecting to the server, just that I can't seem to find the actual data that goes into the table.
Here is an example of my code:
import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=SQLSRV01;DATABASE=DATABASE;UID=USER;PWD=PASSWORD')
cursor = cnxn.cursor()
cursor.execute("SELECT * FROM sys.tables")
tables = cursor.fetchall()
#cursor.execute("SELECT WORK_ORDER.TYPE,WORK_ORDER.STATUS, WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID FROM WORK_ORDER")
for row in cursor.columns(table='WORK_ORDER'):
print row.column_name
for field in row:
print field
However the result of this just gives me things like the table name, the column names, and some integers and 'None's and things like that that aren't of interest to me:
STATUS_EFF_DATE
DATABASE
dbo
WORK_ORDER
STATUS_EFF_DATE
93
datetime
23
16
3
None
0
None
None
9
3
None
80
NO
61
So I'm not really sure where I can get the values to fill up my table. Would it should be in table='WORK_ORDER', but could it be under a different table name? Is there a way of printing the data that I am just missing?
Any advice or suggestions would be greatly appreciated.
9条答案
按热度按时间ifsvaxew1#
You are so close!
(the "columns()" function collects meta-data about the columns in the named table, as opposed to the actual data).
aemubtdh2#
you could try using Pandas to retrieve information and get it as dataframe
I hope this helps! Cheers!
t30tvxxf3#
In order to receive actual data stored in the table, you should use one of fetch...() functions or use the cursor as an iterator (i.e. "for row in cursor"...). This is described in the documentation :
46scxncf4#
Just do this:
rlcwz9us5#
Instead of using the pyodbc library, use the pypyodbc library... This worked for me.
91zkwejq6#
bwitn5fc7#
Why
pyodbc
you can try withpymssql
. For more information follow this link: https://stackoverflow.com/a/70445445/8614314 .5m1hhzi48#
Upvoted answer din't work for me, It was fixed by editing connection line as follows(replace semicolons with coma and also remove those quotes):
lx0bsm1f9#
You could use this query as an example of what you need, here is the complete code: