python quickly exports the query result of sql statement (mssql) to Excel

In an awkward situation, SSMS's GridView support for large fields (varchar(max), text, etc.) is not very friendly.
Characters beyond 8000 lengths, SSMS tables are not visible (of course, not visible), nor can they be directly exported (more than 8000 characters outside).
This kind of problem is intolerable when others come to ask for help. So we quickly write a function of exporting data to Excel in Python.
The most important thing is that we can export the content of large fields, which is convenient and concise, harmless to people and animals, green and environmental protection. We don't like the next step like SSMS. Operation. The fields that can't be displayed in SMS can now be shown in their original form.

1. The required parameters are server address and sql statement. If the sql statement does not have a database name, then the database name must be passed in. Otherwise, the default is the master database, so sql should have the database name as much as possible.
2. The export path is C:WindowsTemp\\ which opens automatically after the export is completed for easy viewing and save.
3. According to sql statement, the finished query result is exported. The first line is the field name, and the rest is the query result.
4. Depends on the xlsxwriter package
5. Without too strict parameter checking and database account password support, self-improvement is needed.

import os
import re
import time
import pymssql
import xlsxwriter
import argparse

def get_data_from_db(host='',server='mssql',port=1433,database='master',sql_stament=''):
    conn = pymssql.connect(host=host, server=server,port=port, database=database,as_dict=True, autocommit=True)
    cursor = conn.cursor()
    if len(sql_statment)<6 or not (re.match(r'^select',sql_statment.lstrip())):
        print('sql statment is error,exit......')
        result = cursor.fetchall()
    return result

def export_to_excel(result):
    current_time = time.strftime("%Y%m%d%H%M%S", time.localtime())
    workbook = xlsxwriter.Workbook('C:\\Windows\\Temp\\'+current_time+'_temp.xlsx')
    worksheet = workbook.add_worksheet("sheet1")
    # head row
    col = 0
    for key in result[0].keys():
        worksheet.write(0, col, key)
        col = col+1
    # data row
    row = 1
    col = 0
    for dict in result:
        for key in dict:
            worksheet.write(row, col, str(dict[key]))
            col = col + 1
        col = 0
        row += 1

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument("--host",required=True, type=str,help="host name or ip")
    parser.add_argument("--db",required=False, type=str,help="database name")
    parser.add_argument("--sql", required=True, type=str, help="sql statment")
    args = parser.parse_args()
    host =
    database = args.db
    if not database:
    sql_statment = args.sql
    print('executing sql......')
    result = get_data_from_db(host = host,database = database,sql_stament =sql_statment)
    print('exporting data......')

    # example
    # python --host "" --sql "select * from tempdb.dbo.Test"


Below 10W, speed is still possible. This is demo.

Keywords: Python Database SQL Windows

Added by bond00 on Thu, 26 Sep 2019 14:42:27 +0300