PythonでCSVファイルのデータをmariadbに取り込む

Shift_JISのCSVファイルの特定の列の情報をmariadbに登録します。

あらかじめ、PythonからMariadbにつなぐためのコネクタをインストールします。

    $ sudo pip3 install mysql-connector-python

ソースコードです。

    import csv
    import mysql.connector as dbconnector

    file_path = './sample.csv'
    file_encoding = 'shift_jis'

    db_host = '127.0.0.1'
    db_port = '3306'
    db_user = 'user'
    db_password = 'password'
    db_name = 'mydb'

    try:
        conn = dbconnector.connect(
            host = db_host,
            port = db_port,
            user = db_user,
            password = db_password,
            database = db_name 
        )
        cur = conn.cursor()

        cur.execute("DROP TABLE IF EXISTS test")
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS test (
            `id` int auto_increment primary key,
            `data1` varchar(7) not null,
            `data2` varchar(1024) not null,
            )
            """
        )

        insert_sql = 'INSERT INTO test (data1, data2) VALUES (%s, %s)'

        with open(file_path, 'r', encoding=file_encoding) as csvfile:
            csv_reader = csv.reader(csvfile, delimiter=',', quotechar='"')
            for row in csv_reader:
                cur.execute(insert_sql, tuple(row[2:4]))

        cur.close()
        conn.commit()
        conn.close()

    except dbconnector.Error as e:
        print(e)
    except FileNotFoundError as e:
        print(e)
    except csv.Error as e:
        print(e)