Python-高斯数据库连接，查询结果写入表格

高斯数据库:

#!/usr/bin/python
# -*- coding:  -*-
# security:数据库相关的敏感参数最好经过传参形式传入而非写入脚本

import jaydebeapi
import xlwt
import os

'''
简单的高斯数据库连接，查询结果

'''


def dbConnection():
    user = 'projectdb'
    password = '123456'
    url = 'jdbc:zenith:@127.0.0.1:32083'
    driver = 'com.gauss.jdbc.ZenithDriver'
    jar = 'ZenithDriver-V300R001C00SPC100B210.jar'
    sqlStr = "select *from TBL_PROJECT_INFO WHERE CODE='P-202109061718310008'"

    # 返回一个数据库实例
    conn = jaydebeapi.connect(driver, url, [user, password], jar)
    curs = conn.cursor()
    curs.execute(sqlStr)

    # 结果是个list
    result = curs.fetchall()
    print(result)
    curs.close()
    conn.close()


'''
高斯数据库查询结果导出表格

'''

# import pandas as pd

# database conn
def dbConnect(dburl):
    user = 'projectdb'
    password = 'Changeme_123'
    # url = 'jdbc:zenith:@127.0.0.1:32083'
    driver = 'com.gauss.jdbc.ZenithDriver'
    jar = 'ZenithDriver-V300R001C00SPC100B210.jar'
    # sqlStr = "select *from TBL_PROJECT_INFO WHERE CODE='P-202109061718310008'"

    # 返回一个数据库实例
    conn = jaydebeapi.connect(driver, dburl, [user, password], jar)
    return conn


# 获取列名
def getCol(sql):
    db_conn = dbConnect("jdbc:zenith:@127.0.0.1:32083")
    cursor = db_conn.cursor()
    cursor.execute(sql)
    col = tuple([tuple[0] for tuple in cursor.description])
    db_conn.close()
    return col


# 获取数据
def sqlOpt(opt):
    db_conn = dbConnect("jdbc:zenith:@127.0.0.1:32083")
    cursor = db_conn.cursor()
    cursor.execute(opt)
    data = cursor.fetchall()
    # final_data = col + data
    db_conn.close()
    return data


# 写入文件
def write_into_excel(col_name, content):
    os.chdir(r"D:python")
    filename = 'dbTest.xls'
    wbk = xlwt.Workbook(encoding='utf-8')
    test = wbk.add_sheet('test', cell_overwrite_ok=True)

    fileds = list(col_name)
    trans_data = list(content)
    # 写入列名
    for filed in range(0, len(fileds)):
        test.write(0, filed, fileds[filed])
    for row in range(1, len(trans_data) + 1):
        for col in range(0, len(fileds)):
            test.write(row, col, u'%s' % str(trans_data[row - 1][col]))
    wbk.save(filename)


if __name__ == '__main__':
    sql = "select *from TBL_PROJECT_INFO WHERE CODE='P-202109061718310008'"
    result = sqlOpt(sql)
    col_name = getCol(sql)
    # print(result)
    # print(col_name)
    write_into_excel(col_name, result)