使用 pyodbc 从 SQL 检索数据 [英] Retrieving Data from SQL Using pyodbc

问题描述

我正在尝试使用 pyodbc 从 SQL 服务器检索数据，并使用 Python 将其打印在表中.但是，我似乎只能检索列名和数据类型之类的东西，而不是该列每一行中的实际数据值.

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.

基本上，我试图复制一个 Excel 工作表，该工作表可检索服务器数据并将其显示在表格中.我连接到服务器没有任何问题，只是我似乎无法找到进入表中的实际数据.

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.

这是我的代码示例:

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

所以我不太确定从哪里可以获得值来填满我的表格.它应该在 table='WORK_ORDER' 中，但可以在不同的表名下吗?有没有办法打印我刚刚丢失的数据?

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.

推荐答案

你离我很近！

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=SQLSRV01;DATABASE=DATABASE;UID=USER;PWD=PASSWORD')
cursor = cnxn.cursor()

cursor.execute("SELECT WORK_ORDER.TYPE,WORK_ORDER.STATUS, WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID FROM WORK_ORDER")
for row in cursor.fetchall():
    print row

(columns()"函数收集有关命名表中列的元数据，而不是实际数据).

(the "columns()" function collects meta-data about the columns in the named table, as opposed to the actual data).

这篇关于使用 pyodbc 从 SQL 检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！