import openpyxl as xl import psycopg2 as pg
import_file_path = 'ファイルパス' wb = xl.load_workbook(import_file_path, data_only=True)
tables_json = { 'Sheet1': { 'min_row': 2, 'table_name': 'dummy_table_name', 'column': { 'name': {'type': 'text', 'index': 1}, 'age': {'type': 'int', 'index': 2}, } } }
conn_param = { 'user': 'user_name', 'password': 'pw', 'host': 'localhost', 'port': '5432', 'dbname': 'testdb' }
with pg.connect(**conn_param) as conn: with conn.cursor() as cur: for ws_name in tables_json: ws = wb[ws_name] tbl = tables_json[ws_name] table_name = tbl['table_name'] min_row = tbl['min_row'] col_label = [] col_name = [] col_index = [] for k, v in tbl['column'].items(): col_label.append(f"{k} {v['type']}") col_name.append(k) col_index.append(v['index']) col_label = ','.join(col_label) col_name = ','.join(col_name) cur.execute(f'DROP TABLE IF EXISTS {table_name}') cur.execute(f'CREATE TABLE {table_name}({col_label})') for r in ws.iter_rows(min_row=min_row): fields = [] if r[0].value is None: break for i in col_index: val = r[i - 1].value if type(val) is str or type(val) is int: fields.append(val) else: fields.append(str(val)) place_holder = ','.join(['%s' for _ in range(len(fields))]) sql = f'INSERT INTO {table_name} ({col_name}) VALUES({place_holder})' cur.execute(sql, tuple(fields)) conn.commit()
|