Python连接SQL Server数据库实现查询并导出数据到excel 含指定列格式化操作

使用到的库说明

python连接微软的sql server数据库用的第三方模块叫做pymssql。Python操作excel主要用到xlrd和xlwt这两个库，即xlrd是读excel，xlwt是写excel。

代码贴上

# -*- coding: utf-8 -*-
"""
@function:  export data to excel from SQL Server
Created on 2021-07-13
@author: Andy
"""
from datetime import datetime
import pymssql #引入pymssql模块 
import xlwt #引入xlwt模块 
import os

# Format Date
style_datetme = xlwt.XFStyle()
style_datetme.num_format_str = 'M/D/YY hh:mm'

# current runng file path
current_dir = os.path.abspath(os.path.dirname(__file__))+"\\"
connect = pymssql.connect('IP:Port', 'sa', 'password', 'dbName',charset="utf8") #服务器名,端口,账户,密码,数据库名

def export_excel():
   
    if connect:
        print("连接成功!")

    cursor = connect.cursor()   #创建一个游标对象,python里的sql语句都要通过cursor来执行
    sql = "select *  FROM tablename where 1=1"
    cursor.execute(sql)   #执行sql语句

    fields = [field[0] for field in cursor.description]  # 获取所有字段名
    all_data = cursor.fetchall()  # 所有数据
    aa=datetime.now().strftime('%Y%m%d%H%M%S')

    # 写入excel
    book = xlwt.Workbook()
    sheet = book.add_sheet('sheet1')
    
    for col,field in enumerate(fields):
        sheet.write(0,col,field)
    
    row = 1
    for data in all_data:
        for col,field in enumerate(data):

            # format Date  这里13/16/18列是日期数据，所以做了单独的格式化处理
           if col == 13 or col == 16 or col == 18 :
                sheet.write(row,col,field,style_datetme)
           else :
                sheet.write(row,col,field)

        row += 1
    book.save(current_dir+"File_"+"%s.xls" % aa)

    print("Export to excel success!")
   

if __name__ == '__main__':
    # export data from SQL server
    export_excel()

    # close database connection
    connect.close()