PythonによるInsert処理確認
- コネクションやSQL実行時にcommitを入れておく。
現状確認
mysql> select * from language; +-------------+----------+---------------------+ | language_id | name | last_update | +-------------+----------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | +-------------+----------+---------------------+ 6 rows in set (0.00 sec)
前回のSQL処理を編集して再利用
$ vim mysql_test.py $ cat mysql_test.py # coding: utf-8 try: # import import mysql.connector # 接続 connect = mysql.connector.connect(user='root', password='password', host='localhost', database='test', charset='utf8') # カーソル cursor = connect.cursor() # SQL 発行 # cursor.execute('select language_id,name from language',()) insert_stmt = "insert into language (name) values ('Chinese')" cursor.execute(insert_stmt) connect.commit() # フェッチ # # rows = cursor.fetchall() # print(rows) cursor.close() # 切断 connect.close() except Exception as myout: print(myout) $ $ python mysql_test.py
実行後の結果
検証していたので、language_idのIdentityがずれているが
問題なく処理されている事が確認出来た。
mysql> select * from language; +-------------+----------+---------------------+ | language_id | name | last_update | +-------------+----------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | | 9 | Chinese | 2014-08-04 11:28:02 | +-------------+----------+---------------------+ 7 rows in set (0.00 sec) mysql>
とりあえず、確認出来たのでテーブルのデータを整理しておく為に、
おまけ処理で、不要なデータを削除して、Alter TableでIndentityを直しておく。
mysql> select * from language; +-------------+----------+---------------------+ | language_id | name | last_update | +-------------+----------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | | 9 | Chinese | 2014-08-04 11:28:02 | +-------------+----------+---------------------+ 7 rows in set (0.00 sec) mysql> delete from language where language_id = 9; Query OK, 1 row affected (0.00 sec) mysql> alter table language auto_increment = 7; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table language\G *************************** 1. row *************************** Table: language Create Table: CREATE TABLE `language` ( `language_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`language_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> mysql> select * from language; +-------------+----------+---------------------+ | language_id | name | last_update | +-------------+----------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | +-------------+----------+---------------------+ 6 rows in set (0.00 sec)
2回実行してデータをとりあえず、再度処理しておく。
$ python mysql_test.py
データ確認とデータの追加してIdentityを8まで追加。
mysql> select * from language; +-------------+----------+---------------------+ | language_id | name | last_update | +-------------+----------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | | 7 | Chinese | 2014-08-04 11:36:23 | +-------------+----------+---------------------+ 7 rows in set (0.00 sec) mysql> mysql> select @max := max(language_id)+ 1 from language; +-----------------------------+ | @max := MAX(language_id)+ 1 | +-----------------------------+ | 8 | +-----------------------------+ 1 row in set (0.01 sec) mysql> select * from language; +-------------+----------+---------------------+ | language_id | name | last_update | +-------------+----------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | | 7 | Chinese | 2014-08-04 11:36:23 | | 8 | Korean | 2014-08-04 14:20:16 | +-------------+----------+---------------------+ 8 rows in set (0.00 sec) mysql>
今後の検証環境構築用に、追加でMultiple Insertにて複数データ登録確認してみた
実行してみる
$ cat mysql_test.py # coding: utf-8 try: # import import mysql.connector # 接続 connect = mysql.connector.connect(user='root', password='password', host='localhost', database='test', charset='utf8') # カーソル cursor = connect.cursor() # SQL 発行 # cursor.execute('select language_id,name from language',()) # insert_stmt = "insert into language (name) values ('Korean')" insert_stmt = "insert into language (name) values ('Ainu'),('Irish')" cursor.execute(insert_stmt) # connect.commit() # フェッチ # # rows = cursor.fetchall() # print(rows) connect.commit() cursor.close() # 切断 connect.close() except Exception as myout: print(myout) $ $ python mysql_test.py $
データが登録されている事を確認
mysql> select * from language; +-------------+----------+---------------------+ | language_id | name | last_update | +-------------+----------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | | 7 | Chinese | 2014-08-04 11:36:23 | | 8 | Korean | 2014-08-04 14:20:16 | | 9 | Ainu | 2014-08-04 14:44:03 | | 10 | Irish | 2014-08-04 14:44:03 | +-------------+----------+---------------------+ 10 rows in set (0.00 sec) mysql>
Reference:
How to Reset an MySQL AutoIncrement using a MAX value from another table?