PythonによるInsert処理確認

by

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?

Posted in: DB, MYSQL, Script, 運用 ⋅ Tagged: , ,

Comments are closed.

Post Navigation