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

## 注意点
文字化けを避けるために、
<ol>
<li>テーブル作成に nvarchar を使う</li>
<li>insert時に N'string' とする</li>
</ol>

## プログラム

```py:mssql_create.py
#! /usr/bin/python
#
#	mssql_create.py
#
#					May/08/2023
#
# --------------------------------------------------------
import	sys
import	string
import	pymssql
#
# --------------------------------------------------------
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):
#
	ft_aa="insert into cities(id,name,population,date_mod) values ("
	ft_bb ="'%s',N'%s',%d,'%s')" % (id_in,name_in,ipop_in,date_mod_in)
	sql_str=ft_aa + ft_bb
	cursor_aa.execute(sql_str)
#
#
# --------------------------------------------------------
def	create_table_proc(cursor_aa):
	sql_str="create table cities (id nvarchar(10) primary key, name nvarchar(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="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,'t1071','前橋',47312,'2003-12-25')
	dict_aa = dict_append_proc(dict_aa,'t1072','高崎',71521,'2003-8-12')
	dict_aa = dict_append_proc(dict_aa,'t1073','桐生',28974,'2003-6-14')
	dict_aa = dict_append_proc(dict_aa,'t1074','沼田',53172,'2003-9-9')
	dict_aa = dict_append_proc(dict_aa,'t1075','伊勢崎',42391,'2003-7-4')
	dict_aa = dict_append_proc(dict_aa,'t1076','水上',35287,'2003-1-21')
	dict_aa = dict_append_proc(dict_aa,'t1077','太田',85943,'2003-5-23')
	dict_aa = dict_append_proc(dict_aa,'t1078','安中',21684,'2003-11-26')
	dict_aa = dict_append_proc(dict_aa,'t1079','みどり',48573,'2003-7-1')
#
	return	dict_aa
#
# --------------------------------------------------------
def drop_create_insert_proc(cursor,dict_aa):
	try:
		drop_table_proc(cursor)
		create_table_proc(cursor)
		table_insert_proc(cursor,dict_aa)
#
	except Exception as ee:
		sys.stderr.write("*** error *** drop_create_insert_proc ***\n")
		sys.stderr.write(str(ee) + "\n")
#
# --------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
dict_aa = data_prepare_proc()
#
server_mssql = '172.17.0.2'
user_mssql = 'sa'
#password_mssql = 'Sql12345'
password_mssql = 'Password1'
#
try:
	conn = pymssql.connect \
		(host=server_mssql,user=user_mssql,password=password_mssql,database='city')
#
	cursor = conn.cursor()
#
	drop_create_insert_proc(cursor,dict_aa)
#
	conn.commit()
#
	cursor.close()
	conn.close()
#
except Exception as ee:
	sys.stderr.write("*** error *** main ***\n")
	sys.stderr.write(str(ee) + "\n")

sys.stderr.write("*** 終了 ***\n")
#
# --------------------------------------------------------
```

## 実行コマンド

```bash
./mssql_create.py
```

## 確認したバージョン

```text
$ python --version
Python 3.11.3
```