Pythonを使用して、複数の表のロットをデータベースからExcelにエクスポートすることを実現します。
一、応用シーン
バックグラウンドデータベースからいくつかのデータテーブルからエクセルファイルへのハンドメイドの繰り返しを避けるために、複数のオフラインデータまで効率的にエクスポートします。
二、機能事項
複数のデータソーステーブルを一括でエクスポートし、各テーブルのフィールド名を自動的に取得することができます。
制御ロットの書き込み速度に対応します。例えば、5000行に1ロットずつエクセルに書き込みます。
同じ構造のテーブルを同じExcelファイルにインポートします。水平分割された分布式の表に適用できます。
三、主要実現
1、概要
A[クラスを作成]->|方法1|B（データベース接続を作成）
A[作成クラス]->|方法2|C（クエリ結果セットを取る）
A[作成クラス]->>|方法3|D（ハンドルでExcelに書き込む）
A[クラスを作成]->|方法4|E（複数のソーステーブルを読み出す）
B（データベース接続の作成）－>U（呼び出し例）
C（クエリー結果セット）－>U（呼び出し例）
D（ハンドルでExcelに書き込む）－>U（呼び出し例）
E（複数のソーステーブルを読み込む）－>U（呼び出し例）
2、主な方法
まず第三者ライブラリpymssqlをインストールしてSQLServerへの接続アクセスを実現し、カスタムメソッドを行う必要があります。getConn()は、サーバhost、ログインユーザ名user、ログインパスワードpwd、指定されたデータベースdb、文字コードcharsetの5つのパラメータを指定する必要があります。接続が成功したら、クルーズオブジェクトをcursor（）で取得し、データベーススクリプトを実行し、結果セットとデータ総量を返します。
データベース接続を作成して実行するSQLのソースコード:
writer=pd.Excel Writer(file)
df_fetchdata[rs_]startrow:i*N.to_excel(writer、header=isHeader、index=False、startrow=startRow)
バッチでターゲットExcelに書き込む時のもう一つの注意すべきパラメータは書き込みstartrowの設定です。書き込みが完了するたびに、次のロットのデータの初期位置値を見直す必要があります。各ロットのデータはそれぞれの所属ロット情報を記録します。
キーワードパラメータ＊＊argsで複数のデータソーステーブルとデータベース接続を指定します。
バックグラウンドデータベースからいくつかのデータテーブルからエクセルファイルへのハンドメイドの繰り返しを避けるために、複数のオフラインデータまで効率的にエクスポートします。
二、機能事項
複数のデータソーステーブルを一括でエクスポートし、各テーブルのフィールド名を自動的に取得することができます。
制御ロットの書き込み速度に対応します。例えば、5000行に1ロットずつエクセルに書き込みます。
同じ構造のテーブルを同じExcelファイルにインポートします。水平分割された分布式の表に適用できます。
三、主要実現
1、概要
A[クラスを作成]->|方法1|B（データベース接続を作成）
A[作成クラス]->|方法2|C（クエリ結果セットを取る）
A[作成クラス]->>|方法3|D（ハンドルでExcelに書き込む）
A[クラスを作成]->|方法4|E（複数のソーステーブルを読み出す）
B（データベース接続の作成）－>U（呼び出し例）
C（クエリー結果セット）－>U（呼び出し例）
D（ハンドルでExcelに書き込む）－>U（呼び出し例）
E（複数のソーステーブルを読み込む）－>U（呼び出し例）
2、主な方法
まず第三者ライブラリpymssqlをインストールしてSQLServerへの接続アクセスを実現し、カスタムメソッドを行う必要があります。getConn()は、サーバhost、ログインユーザ名user、ログインパスワードpwd、指定されたデータベースdb、文字コードcharsetの5つのパラメータを指定する必要があります。接続が成功したら、クルーズオブジェクトをcursor（）で取得し、データベーススクリプトを実行し、結果セットとデータ総量を返します。
データベース接続を作成して実行するSQLのソースコード:
def __init__(self,host,user,pwd,db):
self.host = host
self.user = user
self.pwd = pwd
self.db = db
def __getConn(self):
if not self.db:
raise(NameError,' ')
self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset='utf8')
cur = self.conn.cursor()
if not cur:
raise(NameError,' ')
else:
return cur
writer=pd.Excel Writer(file)
df_fetchdata[rs_]startrow:i*N.to_excel(writer、header=isHeader、index=False、startrow=startRow)
バッチでターゲットExcelに書き込む時のもう一つの注意すべきパラメータは書き込みstartrowの設定です。書き込みが完了するたびに、次のロットのデータの初期位置値を見直す必要があります。各ロットのデータはそれぞれの所属ロット情報を記録します。
キーワードパラメータ＊＊argsで複数のデータソーステーブルとデータベース接続を指定します。
def exportToExcel(self, **args):
for sourceTB in args['sourceTB']:
arc_dict = dict(
sourceTB = sourceTB,
path=args['path'],
startRow=args['startRow'],
isHeader=args['isHeader'],
batch=args['batch']
)
print('
：%s' %(sourceTB))
self.writeToExcel(**arc_dict)
return 'success'
#!/usr/bin/env python
# coding: utf-8
# ： 、 excel
# ，
# ：to_excel ， ，
# Pandas ExcelWriter() ， ，
import pymssql
import pandas as pd
import datetime
import math
class MSSQL(object):
def __init__(self,host,user,pwd,db):
self.host = host
self.user = user
self.pwd = pwd
self.db = db
def __getConn(self):
if not self.db:
raise(NameError,' ')
self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset='utf8')
cur = self.conn.cursor()
if not cur:
raise(NameError,' ')
else:
return cur
def executeQuery(self,sql):
cur = self.__getConn()
cur.execute(sql)
#
# fetchall()
# ，
resList, rowcount = cur.fetchall(),cur.rowcount
self.conn.close()
return (resList, rowcount)
# excel
def writeToExcel(self,**args):
sourceTB = args['sourceTB']
columns = args.get('columns')
path=args['path']
fname=args.get('fname')
startRow=args['startRow']
isHeader=args['isHeader']
N=args['batch']
#
if columns is None:
columns_select = ' * '
else:
columns_select = ','.join(columns)
if fname is None:
fname=sourceTB+'_exportData.xlsx'
file = path + fname
# ， ，
writer = pd.ExcelWriter(file)
sql_select = 'select '+ columns_select + ' from '+ sourceTB
fetch_data, rowcount = self.executeQuery(sql_select)
# print(rowcount)
df_fetch_data = pd.DataFrame(fetch_data)
# roucount ， N batch excel
times = math.floor(rowcount/N)
i = 1
rs_startrow = 0
# >
print(i, times)
is_while=0
while i <= times:
is_while = 1
# ， ，
if i==1:
# isHeader = True
startRow = 1
else:
# isHeader = False
startRow+=N
# ,
# startrow: 。0 1 ，1 2 。。。
df_fetch_data['batch'] = 'batch'+str(i)
df_fetch_data[rs_startrow:i*N].to_excel(writer, header=isHeader, index=False, startrow=startRow)
print(' ',str(i),' ， ',rs_startrow,' ',i*N,' ',' ',startRow,' ')
print(' ',str(i),' ：',df_fetch_data[rs_startrow:i*N])
#
rs_startrow =i * N
i+=1
#
# ，
if is_while == 0:
startRow = startRow
else:
startRow+=N
df_fetch_data['batch'] = 'batch'+str(i)
print(' {0} ， {1} ， {2} ！'.format(str(i), str(rs_startrow), str(startRow)))
print(' ',str(i),' ：',df_fetch_data[rs_startrow:i*N])
df_fetch_data[rs_startrow:i*N].to_excel(writer, header=isHeader, index=False, startrow=startRow)
# ： saver() ！！！！！！！！！！！！！！！！！！！！！1
writer.save()
start_time=datetime.datetime.now()
# excel
def exportToExcel(self, **args):
for sourceTB in args['sourceTB']:
arc_dict = dict(
sourceTB = sourceTB,
path=args['path'],
startRow=args['startRow'],
isHeader=args['isHeader'],
batch=args['batch']
)
print('
：%s' %(sourceTB))
self.writeToExcel(**arc_dict)
return 'success'
start_time=datetime.datetime.now()
if __name__ == "__main__":
ms = MSSQL(host="localhost",user="test",pwd="test",db="db_jun")
args = dict(
sourceTB = ['tb2', 'tb1'],#
path='D:\\myPC\\Python\\',#
startRow=1,# ， 2
isHeader=False,#
batch=5
)
#
ms.exportToExcel(**args)