Accessデータベースに保存しているデータをMySQLデータベースにデータ移行しましたので、その方法を紹介します。
この処理はプロトタイプというよりも、本番作業になります。
手順は、次の通りです。
- AccessでテーブルをCSVファイルにエクスポートする
- CSVファイルの文字コードをシフトJISからUTF8にメモ帳を使って変更する
- Pythonの移行プログラムでCSVファイルのデータをMySQLデータベースへ出力する
1. AccessでテーブルをCSVファイルにエクスポートする
Accessを起動し、レシートのデータベースを選択します。
テーブル一覧の「レシート」テーブルを右クリック➡［エクスポート］➡［テキストファイル］の順にクリックします。
続いて表示される画面に、CSVファイルの出力先とファイル名を入力します。
「区切り記号付き－コンマやタブなどでフィールドが区切られている」が選択されていることを確認します。（デフォルト）
当画面の［設定］ボタンについて補足しておきます。
［設定］をクリックすると「エクスポート定義」画面が表示され、当画面の［コードページ］が「日本語（シフトJIS）」になっているはずです。（デフォルトなので設定をかえていなければ）
ここで、UTF8に変更することができますが、UTF8に変更すると、「￥」文字が文字化けを起こすようです。
MySQLの文字コードがUTF8なので、ここで変更したところ、「\280]など単価情報の「\」が文字化けを起こし、Pythonプログラム実行時、\記号を除外するコードでエラーが発生しました。
なので、デフォルトの「日本語（シフトJIS）」でエクスポートを進め、エクスポート完了後メモ帳を使って、UTF8を指定して保存し直します。
なお、MySQLの文字コードは、MySQLのコマンドラインから次のコマンドを実行すると、確認することができます。
show variables like “chara%”;
エクスポートの完了画面が表示されたら［閉じる］ボタンをクリックします。
デスクトップ上にエクスポートファイルが作成されます。
なお、私の場合、ActiveXのセキュリティポリシーの設定のため、CSVファイルへの出力ができませんでした。
Accessの［ファイル］➡［オプション］➡［トラストセンター］➡［トラストセンターの設定］➡［ActiveXの設定］で下図のように変更したら、エクスポートすることができました。
2. CSVファイルの文字コードをシフトJISからUTF8にメモ帳を使って変更する
receipt.csvファイルを開き、［名前を付けて保存］で文字コードを「UTF-8」にして保存し直してください。（同じファイル名で上書き保存します）
3. 移行プログラムでCSVファイルのデータをMySQLデータベースへ出力する
Jupyuter Notebookで移行プログラムを実行します。
MySQLにデータが移行されていることを確認します。
最後に移行プログラムのPythonコードを載せておきます。
import csv
import mysql.connector
#
# CSVファイルのパス名ファイル名を設定
#
csv_file_path = 'C:/Users/user1/Desktop/レシート.csv'
########################################################
# データベースに接続する処理
########################################################
def create_db_connection(host_name, user_name, user_password, db_name):
connection = None
try:
connection = mysql.connector.connect(
host=host_name,
user=user_name,
passwd=user_password,
database=db_name
)
except mysql.connector.Error as err:
print(f"Error: '{err}'")
return connection
########################################################
# CSVファイルのデータをデータベーステーブルに出力する処理
########################################################
if __name__ == "__main__":
host = "localhost"
user = "root"
password = "root"
database = "receipt"
# データベースに接続
db_connection = create_db_connection(host, user, password, database)
if db_connection:
cursor = db_connection.cursor()
# CSVファイルからデータを読み込んでreceiptテーブルに挿入
with open(csv_file_path, 'r', encoding='utf-8') as csvfile:
csv_reader = csv.reader(csvfile)
for row in csv_reader:
id, purchasedate, kamokucode, zeikubun, itemname, price, quantity, amount, shop, paymentcode = row
price = float(price.replace('\\', '').replace(',', '')) # \とカンマを除去して数値に変換
quantity = int(quantity)
amount = price * quantity
insert_sql = """
INSERT INTO receipt (purchasedate, shop, itemname, price, quantity, amount, kamokucode, zeikubun, paymentcode)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s);
"""
try:
cursor.execute(
insert_sql,
(
purchasedate,
shop,
itemname,
price,
quantity,
amount,
kamokucode,
zeikubun,
paymentcode
)
)
# insert文を実行
except mysql.connector.Error as err:
print(f"Error: '{err}'")
# コミットして接続を閉じる
db_connection.commit()
cursor.close()
db_connection.close()
print('データの挿入が完了しました！')
