iT邦幫忙

0

使用 Python 获取 SQL Server 数据库内表的第一行(列的名称)

使用 pyodbc 库连接到 SQL Server,执行 “select * from SysColumns Where id=Object_Id('Table_Name')” 获取表格,但显示不了表的第一行(列的名称)。

怎么才能显示第一行(列的名称)

import pyodbc

print("Connecting to database using pyodbc...")

db = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; SERVER=' + DB_Host + '; DATABASE=' + DB_Name + '; UID=' + DB_User + '; PWD=' + DB_Password)

print("Succesfully Connected to database using pyodbc!")

table = db.cursor()
table.execute("select * from SysColumns Where id=Object_Id('MonitorData.Application') ")

for name in table:
    print(name)

结果:

"C:\Users\Administrator\PycharmProjects\Python Challenge\venv\Scripts\python.exe" "C:/Users/Administrator/PycharmProjects/Python Challenge/7.py"
Connecting to database using pyodbc...
Succesfully Connected to database using pyodbc!
('Id', 1669580986, 36, 1, 36, 16, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, None, 2, 0, 0, 0, 37, 0, None, 0, None, 0, 0, 0, None, b'\x00\x00\x00\x00\x00')
('Name', 1669580986, 231, 3, 231, 510, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 2, None, -1, 299016, 0, 16, 39, 0, None, 255, None, 0, 0, 0, 'Latin1_General_100_CI_AS_KS', b'\t\x04\x90 \x00')
('PublishedName', 1669580986, 231, 3, 231, 510, 0, 0, 3, 0, 0, 0, 0, 0, 0, 0, 3, None, -2, 299016, 0, 16, 39, 0, None, 255, None, 0, 0, 0, 'Latin1_General_100_CI_AS_KS', b'\t\x04\x90 \x00')
('ApplicationType', 1669580986, 56, 0, 56, 4, 10, 0, 4, 0, 0, 0, 0, 0, 0, 0, 4, None, -3, 0, 0, 8, 38, 7, None, 10, 0, 0, 0, 1, None, b'\x00\x00\x00\x00\x00')
('Enabled', 1669580986, 104, 0, 104, 1, 1, 0, 5, 0, 0, 0, 0, 0, 0, 0, 5, None, 18, 0, 0, 8, 50, 16, None, 1, None, 0, 0, 1, None, b'\x00\x00\x00\x00\x00')
('AdminFolder', 1669580986, 231, 2, 231, 800, 0, 0, 6, 0, 0, 0, 0, 0, 0, 0, 6, None, -4, 299016, 0, 24, 39, 0, None, 400, None, 0, 0, 1, 'Latin1_General_100_CI_AS_KS', b'\t\x04\x90 \x00')
('LifecycleState', 1669580986, 56, 1, 56, 4, 10, 0, 7, 0, 0, 0, 0, 0, 0, 0, 7, None, 19, 0, 0, 0, 56, 7, None, 10, 0, 0, 0, 0, None, b'\x00\x00\x00\x00\x00')
('Path', 1669580986, 231, 2, 231, 800, 0, 0, 8, 0, 0, 0, 0, 0, 0, 0, 8, None, -5, 299016, 0, 24, 39, 0, None, 400, None, 0, 0, 1, 'Latin1_General_100_CI_AS_KS', b'\t\x04\x90 \x00')
('BrowserName', 1669580986, 231, 2, 231, 100, 0, 0, 9, 0, 0, 0, 0, 0, 0, 0, 9, None, -6, 299016, 0, 24, 39, 0, None, 50, None, 0, 0, 1, 'Latin1_General_100_CI_AS_KS', b'\t\x04\x90 \x00')
('CreatedDate', 1669580986, 61, 1, 61, 8, 23, 3, 10, 0, 0, 0, 0, 0, 0, 0, 10, None, 23, 0, 0, 0, 61, 12, None, 23, 3, 0, 0, 0, None, b'\x00\x00\x00\x00\x00')
('ModifiedDate', 1669580986, 61, 1, 61, 8, 23, 3, 11, 0, 0, 0, 0, 0, 0, 0, 11, None, 31, 0, 0, 0, 61, 12, None, 23, 3, 0, 0, 0, None, b'\x00\x00\x00\x00\x00')

Process finished with exit code 0

直接输出了数据

这是在 SQL Server Management Studio 里面查询的结果:
https://ithelp.ithome.com.tw/upload/images/20200709/20099494igaZH7yfZm.png

Dabuk iT邦新手 5 級 ‧ 2020-07-10 12:08:40 檢舉
columns = [column[0] for column in table.description]

1 個回答

1
海綿寶寶
iT邦大神 1 級 ‧ 2020-07-09 09:58:10

如果要匯出成 CSV 的話
可以參考這篇

如果只是要 SELECT 出來的話
最快最笨的方法就是

SELECT 'name','id','xtype','typestat','xuertype',...,'collationid','language',...
UNION ALL
select * from SysColumns Where id=Object_Id('MonitorData.Application

我要發表回答

立即登入回答