Python:CSVをMySQLへ高速読込み「LOAD DATA INFILE」（VSCode）その２

この項10/27追記：フォルダ名適宜ご変更のほど

この記事内のスクリプトに載せているフォルダ「C:/pyfiles/....」関係です。東証株式相場表：PDFMinerで解析しMySQLへの流れ（１） - pagetakaBlog掲載図では「C:/pys/....」としています。適宜、ご自身の環境に合わせご変更のほど。

ファイル名を変数にする悪あがき、マニュアルでは文字列定数…みたいだけど

「LOAD DATA INFILE」で処理する際、「ファイル名は、リテラル文字列として指定する必要があります。」とMySQL8.0マニュアルにありました。複数ファイルを処理するには変数（リストとか）を使いますが、できないのか…とちょっと焦りました。「mysqlimport」はよくわからず…結局、「’’’」と「+」、変数を使って無理やり…。その備忘録です。

【東証株式相場表(PDF)→CSV（BOM無し）→MySQLの対応関係】

※上図「市場日」はCSVファイル名から取得（例:20220301.csv→20220301→日付型）

前説：CSV単ファイルを処理できました。

前の記事は、VSCode上でPython3スクリプトを書き、実行してみたら、MySQLへCSV単ファイルを高速読込みできました。CSVはBOM無しでできました、というお話でした。

pagetaka.hatenablog.jp

「LOAD DATA INFILE」でフォルダ内CSVを一気にMySQLテーブルへ

CSV複数ファイル、実際にはフォルダ内のCSVを一括して、ということですが、これをなんとか実現したいと思いました。
前記事の該当部分です。

sql = '''LOAD DATA INFILE 'C:/pyfiles/csv/from_pdf2.csv'
 INTO TABLE market_data 
 fields terminated by ','
 optionally enclosed by '\"'
 LINES TERMINATED BY "\r\n"
 (@1, @2) 
 SET m_date = @1, cd = @2
 '''

「'C:/pyfiles/csv/from_pdf2.csv'」部分を変数に置き換え、forで回せば何とかなるだろうと妄想しましたが、「’’’ ～　’’’」の間は単に変数を置いてもNG…さて…。

「set」が使えるかもと思ったのですがこれも爺の努力ではダメでした。

恥ずかしながらのPythonスクリプト

db(stock)、table(market_data)はphpMyAdminなどで作成済、ということでよろしくお願いします。CSVは、東証株式相場表（日報、PDF）を解析してUTF8(BOM無し)で作成しました。銘柄コードで代用できるので、銘柄名はCSV作成時点で含めていません。

import mysql.connector as mydb
import glob #フォルダ内ファイル一括取得など
#csv(BOM無し)をフォルダ内一括取得
files = glob.glob('csv/*.csv') #Pythonスクリプトから相対的位置
#MySQL接続
conn= mydb.connect(
    host='localhost',
    user='root',
    password='',
    database='stock'
)
print(conn.is_connected())
#csv(BOM無し)
f_folder = 'C:/pyfiles/csv/'   #ドライブ名からのフルパス用準備

for file in files:
    f_path = f_folder + file[4:] #フルパス（ドライブからファイル拡張子迄）
    cursor = conn.cursor()
    sql = '''LOAD DATA INFILE "''' + f_path + '''" INTO TABLE market_data \
        fields terminated by ',' optionally enclosed by '\"' \
        LINES TERMINATED BY "\r\n" \
        (@1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15, @16) \
        set m_date = @1, cd = @2, unit = @3, mng_open = @4, mng_high = @5, \
        mng_low = @6, mng_close = @7, afn_open = @8, afn_high = @9, afn_low = @10, \
        afn_close = @11, vwap = @14, trd_volume = @15 '''
    #set 不要でスキップ：、最終気配＝@12、前日比＝@13、売買代金＝@16
    cursor.execute(sql)
conn.commit()
conn.close()

「@1」「set」、行末「 \」などは別項で説明。

LOAD DATA INFILE　でファイル名を無理やり変数にする「'''」「”」「+」

文字列リテラルと指示されているのを、なんとか変数で処理したいということで、やってみました。ポイントになる部分を抽出すると以下の通りです。

 '''LOAD DATA INFILE "''' + f_path + '''" INTO TABLE market_data \
～(略)～
'''

直接関係する記号部分を抜き出すと、「'''」～「”」「’’’」「+」（変数）「+」「’’’」「”」～「’’’」になります。「”」を最初「’」でやってみましたが、うまくいきませんでした。
「f_folder = 'C:/pyfiles/csv/' 」と「files = glob.glob('csv/*.csv')」で処理対象（「file」が指す）をくっつけると「csv/」がダブるので4文字分削除し「C:/pyfiles/csv/xxxxxxxx.csv」になるようしました。具体的には「f_path = f_folder + file[4:] 」の「file[4:] 」部分です。「f_folder」「file」のどちらかの「csv/」を削除しておけばよさそうなモノですが、爺にはうまく処理することができず、今回のような処理になりました。どなたか良いアイデアがありましたら、ご教示のほど。

「@1」「set」、行末「 \」で長い文と不要項目の処理をしました。

「@1」「set」：CSVとMySQLの項目を関係づける

先頭図の「Python-script」の仮変数「@1」とデータベース・テーブル項目「m_date」を関係づけるのが、「set m_date = @1 」部分です。CSVの不要項目は、スクリプト中の仮変数で指定しつつ「set」部分でスルーするという方法で実現できました。具体的には、先頭図「@12、@13」で指定し「set」内で関係記述が無いという仕掛けです。

行末「 \」：改行が一文の終わりじゃないよ、次の行も一緒だよ～という目印……らしい

１項上の「sql=」に続いて書いている部分、Pythonスクリプトの約束事で、行末で改行すると普通は、一文が終わり、次行は別の命令、となるらしいです。では、横へ横へと長く書かなくてはいけない…というのを救済するため、行末に「\」（Windowsだと半角円マーク）を置くことによって、区切りでなく次行も続きだから、と宣言していることになるらしいです。