目的
pythonでmariaDBの操作を勉強している際に、
同じデータが重複して登録(INSERT)できてしまったため、それを回避するためにどうすればいいのかメモメモ。
前提
・プログラム
python3.7
・モジュール
pymysql
・データベース
mariaDB
失敗例
INSERTのPythonコードは下記で実施した。
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()
実行結果は問題ないが、同じデータで複数回書き込みを行うと、
書き込みした分だけ同じデータが追加される。
+-----+-------+
| 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コマンドを実行します。
alter table テーブル名 add unique (カラム名);
実行後再度確認します。
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分に移行する処理が必要かな～！
更新処理はこれから対応します！！
メモ終了。