使用 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 里面查询的结果:
如果要匯出成 CSV 的話
可以參考這篇
如果只是要 SELECT 出來的話
最快最笨的方法就是
SELECT 'name','id','xtype','typestat','xuertype',...,'collationid','language',...
UNION ALL
select * from SysColumns Where id=Object_Id('MonitorData.Application