Windows 10 Pro x64 + Python3 + pyodbc で Ubuntu 16.04 + SQL Server 2017 Express にCSVファイルを登録してみる

目的

以下の環境で郵便番号検索の17ISHIKA.CSVを書き込んでみる
Windows 10 Pro x64 + Python3 + pyodbc -> Ubuntu 16.04 LTS + SQL Server 2017 Express

対象テーブル

データの並びは以下に準拠
郵便番号データの説明
対象データにユニークキーになりそうなデータが無いので
8桁のテキストを追加する
※Accessかリンクを張るときにも ユニークキーは必須なので
自分の環境では 124183行の挿入(KEN_ALL.CSV)で exited with code=0 in 84.049 seconds

CREATE TABLE [dbo].[ZIPCODE](
    [SEQ] [nchar](8) NOT NULL,
    [PREFCODE] [nchar](3) NULL,
    [KUBUNCODE] [nchar](8) NULL,
    [POSTAL5] [nchar](5) NULL,
    [POSTAL] [nchar](8) NULL,
    [PREFKANA] [nchar](20) NULL,
    [CITIESKANA] [nchar](40) NULL,
    [POADDRKANA] [nchar](80) NULL,
    [PREFKANJI] [nchar](20) NULL,
    [CITIESKANJI] [nchar](40) NULL,
    [POADDRKANJI] [nchar](80) NULL,
    [FLG1] [int] NULL,
    [FLG2] [int] NULL,
    [FLG3] [int] NULL,
    [FLG4] [int] NULL,
    [FLG5] [int] NULL,
    [FLG6] [int] NULL
) ON [PRIMARY]

環境変数の追加

Windows版のVsCodeでPythonを使う場合、出力ターミナルで日本語の文字化けが発生する
これを回避する手段はいくつかあるようだけど、自分は環境変数に以下を追加して回避している
　変数:PYTHONIOENCODING
　値：UTF-8

pyodbcのインストール

$ sudo pip3 install pyodbc

サンプルコード

0埋め8桁の文字列：('00000000' + str(count))[-8:]
0埋め8桁の文字列：str(count).rjust(8, '0') に修正
左から２文字ary[0][:2]

test002.py

import pyodbc
# Windows Add env PYTHONIOENCODING = UTF-8 & restart vscode
# Some other example server values are
# server = 'localhost\sqlexpress' # for a named instance
# server = 'myserver,port' # to specify an alternate port
# SQLSV2UBUNTU は以下の内容で設定済
server   = 'tcp:xxx.xxx.xxx.xxx' 
database = 'demo' 
username = 'demo' 
password = 'demo' 
count = 0
# 接続文字列
# constr = 'DSN=SQLSV2UBUNTU;UID=' + username + ';PWD=' + password 
constr = 'DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password
cnxn = pyodbc.connect(constr)
# autocommit しない
cnxn.autocommit = False
cnxn.execute('TRUNCATE TABLE ZIPCODE')

# 一行ずつ読み込んで挿入する：17ISHIKA.CSVはsjis
with open("17ISHIKA.CSV", "r", encoding='shift-jis') as f:
    for line in f:
        count = count + 1
        ary = line[:-1].replace('"', '').split(",")
        sql = 'INSERT INTO ZIPCODE (' \
            + '  SEQ' \
            + ', PREFCODE' \
            + ', KUBUNCODE' \
            + ', POSTAL5' \
            + ', POSTAL' \
            + ', PREFKANA' \
            + ', CITIESKANA' \
            + ', POADDRKANA' \
            + ', PREFKANJI' \
            + ', CITIESKANJI' \
            + ', POADDRKANJI' \
            + ', FLG1' \
            + ', FLG2' \
            + ', FLG3' \
            + ', FLG4' \
            + ', FLG5' \
            + ', FLG6' \
            + ') VALUES (' \
# 実コードでは削除 
# + '  \'' + ('00000000' + str(count))[-8:] + '\'' 
# 実コードでは削除　           
            + '  \'' + str(count).rjust(8, '0') + '\'' \
            + ', \'' + ary[0][:2] + '\'' \
            + ', \'' + ary[0] + '\'' \
            + ', \'' + ary[1] + '\'' \
            + ', \'' + ary[2] + '\'' \
            + ', \'' + ary[3] + '\'' \
            + ', \'' + ary[4] + '\'' \
            + ', \'' + ary[5] + '\'' \
            + ', \'' + ary[6] + '\'' \
            + ', \'' + ary[7] + '\'' \
            + ', \'' + ary[8] + '\'' \
            + ',   ' + ary[9]  \
            + ',   ' + ary[10] \
            + ',   ' + ary[11] \
            + ',   ' + ary[12] \
            + ',   ' + ary[13] \
            + ',   ' + ary[14] \
            + ')'
        cnxn.execute(sql)

cnxn.commit()
# 挿入したデータの件数を確認する
cursor = cnxn.cursor()
cursor.execute("SELECT COUNT(*) FROM ZIPCODE")
row = cursor.fetchone()
if row:
    print(row)

cnxn.close()

参考にしたのは以下のサイト

Python SQL ドライバー - pyodbc
mkleehammer/pyodbc
In Python, Using pyodbc, How Do You Perform Transactions?