Install required modules:
- pip install redshift_connector
- pip install openpyxl
"""自动生成数据表定义
"""
import redshift_connector
import time
import os
import re
from redshift_connector import Connection
from openpyxl import Workbook
from openpyxl.styles import Font, colors,Alignment,Border, Side
class TableDefinitionExporter():
def __init__(self) -> None:
self.conn = self._get_connection()
self.header = ['No.','論 理 名','物 理 名','型','長さ','精度','可空','主キー','備 考']
def _get_connection(self) -> Connection:
conn = redshift_connector.connect(
host='host name',
database='db name',
user='username',
password='password'
)
return conn
def close(self):
self.conn.close()
def _get_all_tables(self) -> list:
"""获取所有的表
"""
cursor = self.conn.cursor()
cursor.execute("""
select "table" from SVV_TABLE_INFO where "schema"='gated' order by 1;
""")
r = [item[0] for item in cursor.fetchall()]
cursor.close()
return r
def export_table(self,schema:str,tablename:str,dest_file:str):
cursor = self.conn.cursor()
cursor.execute("""
SELECT
column_name,
ordinal_position,
data_type,
column_default,
is_nullable,
encoding,
distkey,
sortkey,
column_acl,
remarks
FROM svv_redshift_columns
WHERE database_name = '{0}'
AND TABLE_NAME = '{1}';
""".format(schema,tablename))
r = cursor.fetchall()
data = []
for row in r:
data.append({
'column_name': row[0],
'data_type': row[2],
'column_default': row[3],
'is_nullable': row[4],
'encoding': row[5],
'distkey': row[6],
'remarks': row[9]
})
self.save_file(schema,tablename,dest_file,data)
cursor.close()
def _convert_type(self, typestr:str):
if 'character' in typestr:
items = re.split('[\(\),]', typestr)
return items[0], items[1], ''
elif 'numeric' in typestr:
items = re.split('[\(\),]', typestr)
return items[0], items[1], '' if len(items) < 3 else items[2]
else:
return typestr,'',''
def save_file(self, schema, tablename, dest_file, data=None):
book = Workbook()
sheet = book.active
font_bold = Font('Meiryo UI', bold=True)
font = Font('Meiryo UI')
align_center = Alignment(horizontal='center', vertical='center', wrap_text=True)
align_ver_center = Alignment(horizontal='left', vertical='center', wrap_text=True)
border_header = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'))
border_cell = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
bottom=Side(border_style='dashed', color='cccccc'))
border_bottom = Border(top=Side(border_style='thin', color='000000'))
sheet['A3'] = "エンティティ名:"
sheet['D3'] = "システム名"
sheet['A5'] = "テーブル名"
sheet['A6'] = tablename
sheet['D5'] = "スキーマ"
sheet['D6'] = schema
sheet['A3'].font = font_bold
sheet['D3'].font = font_bold
sheet['A5'].font = font_bold
sheet['D5'].font = font_bold
# header
for h in range(len(self.header)):
sheet.cell(row=9,column=h+1).value = self.header[h]
sheet.cell(row=9,column=h+1).font = font_bold
sheet.cell(row=9,column=h+1).alignment = align_center
sheet.cell(row=9,column=h+1).border = border_header
# table data
if data is not None:
index = 10
for row in data:
# no
dtype,size,jd=self._convert_type(row.get("data_type"))
rd = [index - 9, row.get("remarks"), row.get("column_name"), dtype, size, jd, row.get("is_nullable"), row.get("distkey"), '' ]
for x in range(len(rd)):
sheet.cell(row=index,column=x+1).value = rd[x]
sheet.cell(row=index,column=x+1).border = border_cell
sheet.cell(row=index,column=x+1).font = font
if x+1 in [1,5,6,7,8]:
sheet.cell(row=index,column=x+1).alignment = align_center
else:
sheet.cell(row=index,column=x+1).alignment = align_ver_center
sheet.row_dimensions[index].height = 20
index += 1
for i in range(9):
sheet.cell(row=index,column=i+1).border = border_bottom
# col width
sheet.column_dimensions['A'].width = 5
sheet.column_dimensions['B'].width = 15
sheet.column_dimensions['C'].width = 20
sheet.column_dimensions['D'].width = 20
sheet.column_dimensions['I'].width = 50
book.save(dest_file)
save_dir = r'D:\work_temp\table definition'
exp = TableDefinitionExporter()
table_name = 'user'
tbls = []
if table_name == 'All':
tbls = exp._get_all_tables()
else:
tbls = [table_name]
for tbl in tbls:
exp.export_table('po',tbl,dest_file=os.path.join(save_dir, tbl + '.xlsx'))
exp.close()
Export excel：
