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?



CREATE VIEW V_CITYN_COUNTRYN (CityName, CountryName)
AS SELECT City.Name, Country.Name FROM City, Country
WHERE City.CountryCode = Country.Code
AND City.CountryCode = 'JPN';

view_update_test


update V_CITYN_COUNTRYN SET CityName = 'Yokohama'
where CityName = 'Jokohama [Yokohama]';

update_view


update V_CITYN_COUNTRYN SET CityName = 'TOKYO',
CountryName = 'JAPAN'
where CityName = 'Tokyo'
AND CountryName = 'Japan';

上記UPDATE文は二つのテーブルからなるVIEWにて同時にそれぞれのテーブルを
更新しようとしている為エラーになっている。

ERROR 1393 (HY000): Can not modify more than one base table through a join view
tables_two

update_view_multiple

insert into V_CITYN_COUNTRYN values('Toukyou','Japan');
insert into V_CITYN_COUNTRYN(CityName,CountryName) values('Toukyou','Japan');

上記INSERTも2つのテーブルを同時にINSERTしようとしてエラーになっている。
(the view is insertable only if a single table is affected)

ERROR 1393 (HY000): Can not modify more than one base table through a join view ‘STUDY.V_CITYN_COUNTRYN’

view_insert_table

VIEW経由で一つのテーブルに対してINSERTしてみるとINSERTは出来たが、
VIEWは2つのテーブルをJOINして成り立っているのでVIEWをSELECTしてもINSERTしたデータは出てこない。

view_insert_single_table

VIEWは便利ですが、色々な制限があるので色々を参考になるサイトを読んでテストしてみた方がいいですね。
GROUP BY,COUNT,列+1などの関数を利用しているVIEWは他のRDBMS同様にINSERT、UPDATE出来ません。

以下のいずれかを含んでいるとビューは更新可能となりません。
——————————————————————————————
* 集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)
*DISTINCT
*GROUP BY
*HAVING
*UNION もしくはUNION ALL
*選択リスト中のサブ・クエリ
その他………………………..
——————————————————————————————

(例)以下のVIEWは集計ファンクションやGroup byを使用しているので、更新処理は出来ません。
The view is not updatable because it uses aggregate functions and GROUP BY.


Create View V_Country_Area
(Continent,Total_Surface,Average_Surface)
as
select Continent,sum(SurfaceArea),avg(SurfaceArea)
from Country group by Continent;

view_group_by

またMYSQL5.1では以下のような固有の制限もあるようです。
ビューの FROM 句でサブクエリを使用することはできません。

    この制限はいずれ取り除かれる予定です。

参考サイト
D.4. ビューの規制
20.2. CREATE VIEW 構文


ビュー(更新可能なビューを含む) はMySQL Server 5.1から入手可能。
旧バージョンMySQLを5.1にアップグレードした場合、ビューの使用はビュー関連の
権限を含むようにグラントテーブルもアップグレードが必要です。

———————————
メモ
———————————
mysql_upgrade コマンドは、古い方、つまり mysql_fix_privilege_tables より優先です。
MySQL 5.1.7 では、シェル スクリプト として mysql_upgrade が加えられ、Unix システムだけで機能します。
MySQL 5.1.10 以降は、mysql_upgrade は実行可能なバイナリとして、すべてのシステムで使用できます。
mysql_upgrade をサポートしているものより古いシステムでは、手動で mysqlcheck コマンドを実行し、
システム テーブルのアップグレードを行ないます

ビューの現在の実装には欠点があります。もしユーザがビューの作成に必要な基本権限
( CREATE VIEW と SELECT 権限 ) を取得した場合、SHOW VIEW 権限も取得しない限り、
そのユーザはオブジェクトの SHOW CREATE VIEW を呼び出すことはできない。

PREPARE によって準備されたステートメントがビューを参照する場合、後でステートメントが実行される度
に参照されるビューの内容が、ステートメントが準備できた時のビューの内容になります。
これは、ステートメントが準備された後、実行される前にビュー定義が変更されても同じ。


CREATE VIEW v AS SELECT 1;
PREPARE s FROM 'SELECT * FROM v';
ALTER VIEW v AS SELECT 2;
EXECUTE s;

view11

幾つかのビューは更新可能です。すなわち、基礎をなすテーブルの内容を更新するため、
UPDATE、DELETEもしくはINSERTのようなステートメントの中でそれらを使うことができます。
ビューを更新可能にするため、ビュー中の行と基礎テーブル中の行の間に1対1の関係が存在しなければなりません。
ビューを更新不能にするその他の生成子もあります。もっと具体的に言うと、それが以下のいずれかを含んでいると
ビューは更新可能となりません。

* 集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)
* DISTINCT
* GROUP BY
* HAVING
* UNION もしくはUNION ALL
* 選択リスト中のサブ・クエリ
* 特定結合(このセクション中の後の部分に追加した結合の説明参照)
* FROM節中の更新不能ビュー
* FROM節中のテーブルを参照するWHERE 節中のサブ・クエリ
* 文字値だけを参照(この場合、更新する基礎となるテーブルは存在しません)
* ALGORITHM = TEMPTABLE (テンポラリテーブルの使用は常にビューを更新不能にする)

(INSERTステートメントで更新不能となる)挿入性に関して、それがビューカラムに対する
これらの追加条件も満たすと、更新不能ビューが挿入可能になります。

* ビューカラム名に重複があってはなりません。
* ビューには、デフォルト値を持っていないベーステーブル内にある全てのカラムを含んでいなくてはなりません。
* ビューカラムは派生カラムではなく、単純なカラムリファレンスでなければなりません。
派生カラムは単純なカラムリファレンスでなく、表現から派生したものです。これらは派生カラムの例です。

3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)

UPDATE,INSERTが可能か確認検証


CREATE VIEW V_Region (CityName, CountryName)
AS SELECT City.Name, Country.Name FROM City, Country
WHERE City.CountryCode = Country.Code
AND City.CountryCode = 'DEU';

VIEW
create_view

    UPDATE確認


update V_Region set CityName = 'HAMBURG'
where CityName = 'Hamburg';

1つのテーブルしか更新されないのでエラーにはならない。
view_update

複数テーブルを更新しようとするとUPDATEもエラーになる

update V_Region set
CityName = 'Hamburg',
CountryName = 'Japan'
where CityName = 'HAMBURG';

複数テーブルを更新しようとしているのでエラーになる
view_update_multi

    INSERT確認

insert into V_Region(CityName,CountryName) values('NewCity','Japan');

※ 複数のテーブルを更新することになるのでエラーになる。
view_insert1

参考サイト
20.2. CREATE VIEW 構文


D.4. ビューの規制


4.5.4. mysql_upgrade — MySQL アップグレードのテーブル チェック


CItyテーブルから首都のみを選んでCapitalテーブルを作成。

city

1) テーブル作成
CREATE TABLE Capitals LIKE City;

create_table_like

2) 首都データの投入

INSERT INTO Capitals SELECT * FROM City
where ID IN(select Capital from Country where Capital is not null);

insert_into

select * from Capitals where name like 'to%' order by name;

tokyo

=================
その他の方法(全てのデータをいれてから削除
=================


mysql> CREATE TABLE Capitals LIKE City;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Capitals SELECT * FROM City;
Query OK, 4079 rows affected (0.17 sec)
Records: 4079 Duplicates: 0 Warnings: 0

mysql> delete from Capitals where ID NOT IN
-> (select Capital from Country where Capital is not null);
Query OK, 3847 rows affected (4.86 sec)

mysql> select count(*) from Capitals;
+----------+
| count(*) |
+----------+
| 232 |
+----------+
1 row in set (0.00 sec)

mysql> select * from Capitals where name like 'to%' order by name;
+------+---------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------+-------------+----------------+------------+
| 1532 | Tokyo | JPN | Tokyo-to | 7980230 |
| 3503 | Toskent | UZB | Toskent Shahri | 2117500 |
+------+---------+-------------+----------------+------------+
2 rows in set (0.01 sec)

mysql>

delete


テーブル(tinyint, char,date )に以下のようなINSERT文を実行した場合どのような結果になるか?
INSERT INTO test_numbers VALUES (1,22,333);
検証してみました。

結果としては、22は文字列 ’22’ に変換してInsertされたがdate型は333をおかしな日付として認識
してしまい、0 date( 0000-00-00 )が格納されました。


mysql> CREATE TABLE test_numbers (number tinyint(3) UNSIGNED,string char(5),dates date);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test_numbers;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(5) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO test_numbers VALUES (1,22,333);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1264 | Out of range value for column 'dates' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_numbers;
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
+--------+--------+------------+
1 row in set (0.00 sec)

mysql>

numbers

—————————————————————————————–
「検証」    tinyint unsignedへの255以上の数値,char(5)への5を超えた値,
date型への’1000-01-01’以前の日付のinsert結果。
—————————————————————————————–


mysql> INSERT INTO test_numbers VALUES (1000,'yahoo','999-01-01');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'number' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_numbers;
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
| 255 | yahoo | 0999-01-01 |
+--------+--------+------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO typetest VALUES (1000,'yoodoo','999-12-31');
ERROR 1146 (42S02): Table 'STUDY.typetest' doesn't exist
mysql> INSERT INTO test_numbers VALUES (1000,'yoodoo','999-12-31');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'number' at row 1 |
| Warning | 1265 | Data truncated for column 'string' at row 1 |
+---------+------+-------------------------------------------------+
2 rows in set (0.01 sec)

mysql> select * from test_numbers;
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
| 255 | yahoo | 0999-01-01 |
| 255 | yoodo | 0999-12-31 |
+--------+--------+------------+
3 rows in set (0.00 sec)

mysql>

mysql> INSERT INTO test_numbers VALUES (255,'google','1000-01-01');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'string' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test_numbers;
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
| 255 | yahoo | 0999-01-01 |
| 255 | yoodo | 0999-12-31 |
| 255 | googl | 1000-01-01 |
+--------+--------+------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO test_numbers VALUES (255,'goo','1000-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_numbers;
+--------+--------+------------+
| number | string | dates |
+--------+--------+------------+
| 1 | 22 | 0000-00-00 |
| 255 | yahoo | 0999-01-01 |
| 255 | yoodo | 0999-12-31 |
| 255 | googl | 1000-01-01 |
| 254 | googl | 1000-01-01 |
| 254 | googl | 1000-01-01 |
| 255 | goo | 1000-01-01 |
+--------+--------+------------+
7 rows in set (0.00 sec)

mysql>


mysql> CREATE TABLE test_numbers2 (number tinyint(3) UNSIGNED,string char(10),dates date);
Query OK, 0 rows affected (0.01 sec)

mysql> desc test_numbers2;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | tinyint(3) unsigned | YES | | NULL | |
| string | char(10) | YES | | NULL | |
| dates | date | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> INSERT INTO test_numbers2 VALUES (254,'google.com','1000-01-01'),(255,'yahoo.com','1000- 12-31');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test_numbers2;
+--------+------------+------------+
| number | string | dates |
+--------+------------+------------+
| 254 | google.com | 1000-01-01 |
| 255 | yahoo.com | 1000-12-31 |
+--------+------------+------------+
2 rows in set (0.00 sec)

mysql>

char


※ DELAYED が指定されていると、サーバはレコードをバッファに挿入する。
INSERT DELAYED ステートメントを発行したクライアントは処理を続行することができる。
※ テーブルが使用されていると、サーバはレコードを保持。テーブルが解放されると、
サーバはレコードの挿入を開始し、そのテーブルに対する新しい読み取り要求がない事
   を定期的にチェックする。新しい読み取り要求があると、そのテーブルが再び解放される
まで、遅延されたレコードのキューの処理は中断される。

mysql> show variables like ‘delayed%’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
+————————+——-+
3 rows in set (0.01 sec)

キューを作った行は、テーブルに挿入されるまでメモリ内だけで保持されます。
これは、もしmysqld を強制的に終了させたり (例えば、kill -9 を利用して)、mysqld
が突然停止してしまったりすると、ディスクに書き込まれる前のキューを作った行は
全て失われてしまう という事を意味します。

INSERT DELAYED は MyISAM、MEMORY、 ARCHIVE テーブルのみ機能

——————————————————————————————-
INNODB
——————————————————————————————-
mysql> show table status like ‘TABLE007’\G
*************************** 1. row ***************************
Name: TABLE007
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 9
Avg_row_length: 1820
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 35651584
Auto_increment: 10
Create_time: 2009-02-25 11:23:38
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql>
————————————- INNODBではエラーになる —————————–
mysql> insert delayed into TABLE007(comment) values (‘Insert deleyed’);
ERROR 1616 (HY000): DELAYED option not supported for table ‘TABLE007’

——————————————————————————————-
MYISAM
——————————————————————————————-

mysql> show table status like ‘MYSQLIMP’\G
*************************** 1. row ***************************
Name: MYSQLIMP
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 24
Data_length: 48
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2009-02-14 08:31:05
Update_time: 2009-02-14 08:32:13
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql>

insert_delayed

注: テーブルが使用中でない場合、INSERT DELAYED は通常の INSERT より遅くなります。