import openpyxl as xl
import psycopg2 as pg

# DBにインポートするxlsxファイルの読み込み
import_file_path = 'ファイルパス'
wb = xl.load_workbook(import_file_path, data_only=True)

# ExcelシートとDBテーブルの対応付け
tables_json = {
'Sheet1': {
'min_row': 2,
'table_name': 'dummy_table_name',
'column': {
'name': {'type': 'text', 'index': 1},
'age': {'type': 'int', 'index': 2},
}
}
}

# DB接続の開始
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()