Python3: MariaDB のデータを作成 (Create)

プログラム

フォルダー構造

$ tree -a
.
├── .env
├── config_mariadb.py
└── maria_create.py

.env
config_mariadb.py
はこちら
Python3: MariaDB のバージョンを表示

maria_create.py

#! /usr/bin/python
#
#	maria_create.py
#
#					Feb/22/2023
#
# --------------------------------------------------------
import	sys
import	string
import	mariadb
#
from config_mariadb import config_mariadb_proc
# --------------------------------------------------------
def	table_insert_proc	(cursor,dict_aa):
	for key in dict_aa:
		unit = dict_aa[key]
		try:
			sql_insert_proc	(cursor,key,unit['name'], \
				unit['population'],unit['date_mod'])
		except Exception as ee:
			sys.stderr.write ("*** error *** table_insert_proc ***\n")
			sys.stderr.write (str (ee) + "\n")
			sys.stderr.write (key + "\n")
#
# --------------------------------------------------------
def	sql_insert_proc	(cursor_aa,id_in,name_in,ipop_in,date_mod_in):
#	print ("*** sql_insert_proc ***")
#
	ft_aa="insert into cities (id,name,population,date_mod) values ("
	ft_bb ="'%s','%s',%d,'%s')" % (id_in,name_in,ipop_in,date_mod_in)
	sql_str=ft_aa + ft_bb
#	print (sql_str)
	cursor_aa.execute (sql_str)
#
#
# --------------------------------------------------------
def	create_table_proc (cursor_aa):
	sql_str="create table cities (id varchar(10) primary key, name varchar(20)," \
		+ " population int, date_mod date)"
#
	try:
		cursor_aa.execute (sql_str)
	except Exception as ee:
		sys.stderr.write ("*** error *** create_table_proc ***\n")
		sys.stderr.write (str (ee) + "\n")
#
# --------------------------------------------------------
def	drop_table_proc (cursor_aa):
	sql_str=u"drop table cities"
	try:
		cursor_aa.execute (sql_str)
	except Exception as ee:
		sys.stderr.write ("*** error *** drop_table_proc ***\n")
		sys.stderr.write (str (ee) + "\n")
#
# --------------------------------------------------------
def dict_append_proc(dict_aa,key,name,population,date_mod):
	dict_aa[key] = {'name':name,'population':population,'date_mod':date_mod}
#
	return dict_aa
#
# --------------------------------------------------------
def	data_prepare_proc ():
#
	dict_aa = {} 
#
	dict_aa = dict_append_proc (dict_aa,'t3321','岡山',529176,'2003-9-20')
	dict_aa = dict_append_proc (dict_aa,'t3322','倉敷',791835,'2003-2-15')
	dict_aa = dict_append_proc (dict_aa,'t3323','津山',163754,'2003-8-18')
	dict_aa = dict_append_proc (dict_aa,'t3324','玉野',369172,'2003-1-9')
	dict_aa = dict_append_proc (dict_aa,'t3325','笠岡',237451,'2003-3-4')
	dict_aa = dict_append_proc (dict_aa,'t3326','井原',518397,'2003-5-21')
	dict_aa = dict_append_proc (dict_aa,'t3327','総社',248156,'2003-7-23')
	dict_aa = dict_append_proc (dict_aa,'t3328','高梁',478294,'2003-10-26')
	dict_aa = dict_append_proc (dict_aa,'t3329','新見',823751,'2003-12-17')
#
	return	dict_aa
#
# --------------------------------------------------------
sys.stderr.write ("*** 開始 ***\n")
#
dict_aa = data_prepare_proc ()
#
host,user,password,data_base = config_mariadb_proc()
conn = mariadb.connect(user=user, password=password, \
                              host=host,database=data_base)
#
cursor = conn.cursor ()
#
drop_table_proc (cursor)
create_table_proc (cursor)
#
table_insert_proc (cursor,dict_aa)
#
conn.commit ()
#
cursor.close ()
conn.close ()
sys.stderr.write ("*** 終了 ***\n")
#
# --------------------------------------------------------

実行結果

$ ./maria_create.py 
*** 開始 ***
*** 終了 ***