python 將mysql數(shù)據(jù)庫(kù)信息寫入xlsx
2021-09-16 11:42:58

from openpyxl import Workbook
import pymysql

con = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="***", db="student",charset="utf8")
cur = con.cursor()
cur.execute("use student")


def sql_excel():
    wb = Workbook()
    ws = wb.active
    ws.title = "test"
    sql = "select * from student"
    cur.execute(sql)
    #description 獲取字段信息
    #(('id', 3, None, 11, 11, 0, False), ('name', 253, None, 20, 20, 0, False))
    for i in range(1, len(cur.description)+1):
        ws.cell(row=1, column=i, value=cur.description[i - 1][0])
    for row in range(2, cur.rowcount + 2):
        #fetchone 數(shù)據(jù)庫(kù)具體信息 (1, '張三', '男', 1)
        ws.append(cur.fetchone())
    wb.save("student.xlsx")


try:
    sql_excel()
except Exception as e:
    raise e
finally:
    cur.close()
    con.close()