#! /usr/bin/python
#
#	oracle_create.py
#
#					May/09/2023
#
# --------------------------------------------------------
import	sys
import	datetime
import	oracledb
import	string
#
sys.path.append ('../../../python_common')
from sql_manipulate import sql_display_proc,table_insert_proc,sql_insert_proc, \
		create_table_proc,drop_table_proc
#
from text_manipulate import dict_append_proc
# --------------------------------------------------------
def	data_prepare_proc():
#
	dict_aa = {} 
#
	dict_aa = dict_append_proc(dict_aa,'t0131','函館',38176,'2003-4-30')
	dict_aa = dict_append_proc(dict_aa,'t0132','札幌',47935,'2003-5-10')
	dict_aa = dict_append_proc(dict_aa,'t0133','帯広',21354,'2003-6-14')
	dict_aa = dict_append_proc(dict_aa,'t0134','釧路',83672,'2003-2-9')
	dict_aa = dict_append_proc(dict_aa,'t0135','旭川',42391,'2003-8-4')
	dict_aa = dict_append_proc(dict_aa,'t0136','北見',35187,'2003-1-21')
	dict_aa = dict_append_proc(dict_aa,'t0137','室蘭',81246,'2003-7-23')
	dict_aa = dict_append_proc(dict_aa,'t0138','根室',24784,'2003-10-26')
	dict_aa = dict_append_proc(dict_aa,'t0139','稚内',75823,'2003-12-15')
#
	return	dict_aa
#
# --------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
dict_aa = data_prepare_proc()
#
host = "172.17.0.2"
user = "scott"
password = "tiger123"

str_connect = user + "/" + password + "@" + host + ":1521/XEPDB1"
conn = oracledb.connect(str_connect)

#
cursor = conn.cursor()
#
#
drop_table_proc(cursor)
create_table_proc(cursor)
#
for key in dict_aa:
	unit = dict_aa[key]
	name = unit['name']
	population = unit['population']
	date_mod = unit['date_mod']
	ddx = datetime.datetime.strptime(date_mod, '%Y-%m-%d')
	dd_oracle = ddx.strftime('%d-%b-%Y')
#	print (key,name,population,date_mod,dd_oracle)
	ft_aa="insert into cities (id,name,population,date_mod) values ("
#
	ft_bb ="'%s','%s',%d,'%s')" % (key,name,population,dd_oracle)
#	print (ft_bb)
	sql_str=ft_aa + ft_bb
#	print (sql_str)
	cursor.execute (sql_str)
#
conn.commit ()
#
sql_display_proc(cursor)
#
cursor.close()
conn.close()
sys.stderr.write("*** 終了 ***\n")
#
# --------------------------------------------------------