目的

pythonでmariaDBの操作を勉強している際に、
同じデータが重複して登録(INSERT)できてしまったため、それを回避するためにどうすればいいのかメモメモ。

前提

・プログラム
python3.7
・モジュール
pymysql

・データベース
mariaDB

失敗例

INSERTのPythonコードは下記で実施した。

mysql_insert.py

connection = pymysql.connect(host='127.0.0.1',
                             port = 3306,
                             user='root',
                             password='password',
                             db='db_name',
                             charset='utf8',
                             # cursorclassを指定することで
                             # Select結果をtupleではなくdictionaryで受け取れる
                             cursorclass=pymysql.cursors.DictCursor)
# Insert処理
try:
    with connection.cursor() as cursor:
        sql = "INSERT INTO テーブル名 (id, name) VALUES (%s, %s)"
        r = cursor.execute(sql, (id, name))
        print(r) # -> 1
        cursor.close()
    # autocommitではないので、明示的にコミットする
    connection.commit()
# MySQLから切断する
finally:
    connection.close()

実行結果は問題ないが、同じデータで複数回書き込みを行うと、
書き込みした分だけ同じデータが追加される。

テーブル名.db

+-----+-------+
| id  | name  |
+-----+-------+
| 1   | name1 |
| 1   | name1 | # 同じデータが登録される
+-----+-------+

これではデータの更新ができず、永遠に同じデータが登録されてしまう。

[修正1]データベースの修正

そもそもデータベースの基礎がわかってればここは回避できる。
必要なことは「ユニークキー制約」が設定されていること。

ユニークキー制約とは？
データベースの項目に付与する「他の行の値と重複しちゃダメよ制約」のこと。

まずはテーブルのカラム情報を確認していきます。

 show columns from テーブル名;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

上記カラム情報の「Key」を確認すると何も記載されていないのがわかる。

今回はすでにカラムまで作成していたのでユニークキーを追加。
下記SQLコマンドを実行します。

実行後再度確認します。

show columns from テーブル名;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  | UNI | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

idのカラムのKey情報に「UNI」と追加されているのがわかります。
これでデータベースはOK。

[修正2]Pythonの修正

データベースを修正後、Pythonを実行したらエラーとなった。

Traceback (most recent call last):
  File "get_profiles.py", line 60, in <module>
  File "E:\anaconda\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "E:\anaconda\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "E:\anaconda\lib\site-packages\pymysql\connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "E:\anaconda\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
    result.read()
  File "E:\anaconda\lib\site-packages\pymysql\connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "E:\anaconda\lib\site-packages\pymysql\connections.py", line 684, in _read_packet
    packet.check_error()
  File "E:\anaconda\lib\site-packages\pymysql\protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "E:\anaconda\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1062, "Duplicate entry '1' for key 'id'")

これはSQL分に「IGNORE」を追加することで回避可能。

sql = "INSERT IGNORE INTO テーブル名 (id, name) VALUES (%s, %s)"

そもそも「IGNORE」は
INSERT ステートメントの実行中に発生したエラーは無視するだけのもの。
使い方には要注意かもね！
ちなみに「IGNORE」追加後は警告がでるが実行はされる。

E:\anaconda\lib\site-packages\pymysql\cursors.py:329: Warning: (1062, "Duplicate entry '1' for key 'id'")
  self._do_get_result()
0

修正後のコード

connection = pymysql.connect(host='127.0.0.1',
                             port = 3306,
                             user='root',
                             password='password',
                             db='db_name',
                             charset='utf8',
                             # cursorclassを指定することで
                             # Select結果をtupleではなくdictionaryで受け取れる
                             cursorclass=pymysql.cursors.DictCursor)
# Insert処理
try:
    with connection.cursor() as cursor:
        sql = "INSERT IGNORE INTO  テーブル名 (id, name) VALUES (%s, %s)"
        r = cursor.execute(sql, (id, name))
        print(r) # -> 1
        cursor.close()
    # autocommitではないので、明示的にコミットする
    connection.commit()
# MySQLから切断する
finally:
    connection.close()

注意

これではデータの更新はできないので更新したい場合はUPDATE文が必要。
新規登録時は上記対応で。登録済みの場合はUPDATE分に移行する処理が必要かな～！
更新処理はこれから対応します！！

メモ終了。