import pyodbc
import pandas as pd
# 創(chuàng)建連接字符串
conn_str = (
    r'DRIVER={SQL Server Native Client 10.0};'
    r'SERVER=BF-202403241716;'
    r'DATABASE=scott;'
    r'Trusted_Connection=Yes;'
)
# 建立連接
cnxn = pyodbc.connect(conn_str)
# 創(chuàng)建游標(biāo)對象
cursor = cnxn.cursor()
# 執(zhí)行SQL查詢
query = "SELECT * FROM dbo.salgrade"
cursor.execute(query)
# 獲取查詢結(jié)果
data1 = cursor.fetchall()
print(type(data1))
print(data1)

# 獲取列名
columns1 = [column[0] for column in cursor.description]
print(type(columns1))
print(columns1)

# 將元組列表展開為一維數(shù)組
data1 = [list(item) for item in data1]
print(type(data1))
print(data1)

# 將結(jié)果轉(zhuǎn)換為DataFrame
df1 = pd.DataFrame(data1, columns=columns1)
print(df1)


# 將數(shù)據(jù)寫入Excel文件
df1.to_excel('output.xlsx', index=False)

# 關(guān)閉數(shù)據(jù)庫連接
cursor.close()
cnxn.close()

# 創(chuàng)建連接字符串
conn_str = (
    r'DRIVER={SQL Server Native Client 10.0};'
    r'SERVER=BF-202403241716;'
    r'DATABASE=scott;'
    r'Trusted_Connection=Yes;'
)

# 將元組列表展開為一維數(shù)組
data1 = [list(item) for item in data1]
print(type(data1))
print(data1)

<class 'list'>
[(1, 700, 1200), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999)]
<class 'list'>
[[1, 700, 1200], [2, 1201, 1400], [3, 1401, 2000], [4, 2001, 3000], [5, 3001, 9999]]
   grade  losal  hisal
0      1    700   1200
1      2   1201   1400
2      3   1401   2000
3      4   2001   3000
4      5   3001   9999

import pyodbc
import pandas as pd

# 假設(shè)data是cursor.fetchall()返回的結(jié)果，它是一個包含元組的列表
data = [(1, 700, 1200), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999)]
print(type(data))
print(data)
# 獲取列名
columns = ['grade', 'losal', 'hisal']  # 確保這些列名與您的表中的列名相匹配
print(type(columns))
print(columns)

# 將結(jié)果轉(zhuǎn)換為DataFrame
df = pd.DataFrame(list(data), columns=columns)
print(df)

pip install some-package -i https://pypi.tuna.tsinghua.edu.cn/simple

(.venv) PS D:\code\test_3_29> pip install openpyxl
Collecting openpyxl
  Downloading openpyxl-3.1.2-py2.py3-none-any.whl.metadata (2.5 kB)
Collecting et-xmlfile (from openpyxl)
  Downloading et_xmlfile-1.1.0-py3-none-any.whl.metadata (1.8 kB)
Downloading openpyxl-3.1.2-py2.py3-none-any.whl (249 kB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 250.0/250.0 kB 547.4 kB/s eta 0:00:00
Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)
Installing collected packages: et-xmlfile, openpyxl
Successfully installed et-xmlfile-1.1.0 openpyxl-3.1.2