MySQL Shell経由でPythonで書いた処理をそのまま流し込めるので、
Twitter APIからJSONデータを取得して100件程データベースに流し込むデモを行いました。
PS: ネットワークがなぜか、共有WIFI、プライベートWIFI共に調子が悪く微妙な感じになってしまいましたが。。。

JSONデータ型、Generated Column(生成列)、MySQL Shellの挙動を簡易的にデモするものなので内容的には微妙ですが、
もしAPI経由でJSONデータを取得して、バッチ処理で定期的にサービスで使えそうなデータを取得して、
データを蓄積したり検索したい場合などに使えるかもしれません。

demo

————————————————————————
1) Create a Sample Table for Importing Data from Twitter API
————————————————————————
name列は、Tweetした人の名前が入ります。
text列は、Tweetしたコメントが入ります。


Create Table: CREATE TABLE `X_PYTHON` (
  `doc` json DEFAULT NULL,
  `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$._id'))) STORED NOT NULL,
  `name` varchar(64) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$.user.name'))) STORED,
  `text` varchar(512) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$.text'))) STORED,
  UNIQUE KEY `_id` (`_id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

————————————————————————
2) Create python file ( demo_python_twitter.py )
————————————————————————
デモ用サンプルなので色々とコメントアウトしてます。またエラー処理も入れてません。
動作確認のみでご利用下さい。
※ Twitterキー、スキーマ、アカウントに関しては、適宜変更して下さい。
※ 必要に応じて、”pip install requests-oauthlib”でOauthもインストールしておいてください。


# coding: utf-8

print "======================================================"
print "MySQL5.7.12 Basic CRUD Operations by Python"
print "MySQL Innovation Daysデモ"
print "======================================================"

from requests_oauthlib import OAuth1Session
import json
import mysqlx

##############################################################
#    This section is just used for my private account.
##############################################################

api_key      = "Please put your own key"
api_secret   = "Please put your own key"
token        = "Please put your own key"
token_secret = "Please put your own key"

##############################################################
#    This section is API and Auth.
##############################################################

url = "https://api.twitter.com/1.1/statuses/home_timeline.json"
params = {'count':'100',}

auth = OAuth1Session(api_key, api_secret, token, token_secret)
res = auth.get(url, params = params)

##############################################################
#     Connect Session to the MySQL Instance.
##############################################################

if res.status_code == 200: # In case of successfully connect to API

 mySession = mysqlx.getSession({
 'host': 'localhost', 'port': 33060,
 'dbUser': 'demo_user', 'dbPassword': 'password'} )
 myDb = mySession.getSchema('NEW57')


### Create a new collection 'my_collection' ###
### This section is turned off for demo.    ###
# myColl = myDb.createCollection('X_PYTHON')


### Insert documents ###

 timeline = json.loads(res.text)
 for tweet in timeline:
   myDb.X_PYTHON.add(tweet).execute()
#  myDb.X_PYTHON.add(tweet).executeAsync() # ASYNCはmysqlshでは対象外.
#  print(tweet["text"])


####################################################
###         Drop the collection(Table)
####################################################
# mySession.dropCollection('NEW57','X_PYTHON')

else: # 失敗した場合
        print ("Error: %d" % req.status_code)

————————————————————————
3) Please Execute the Script through mysqlsh
————————————————————————
こちらは、Pythonモードで処理しています。


mysqlsh --py < demo_python_twitter.py

————————————————————————
4) You can find who is the most active user in twitter.
————————————————————————
mysqlshではパイプでつないで、リダイレクトする事が出来ます。


echo "select name,count(name) from X_PYTHON group by name order by count(name) desc limit 10;" | mysqlsh -u demo_user -ppassword --sql --schema=NEW57

実行結果(直近100件のTweet件数毎のデータ確認)


mysqlx: [Warning] Using a password on the command line interface can be insecure.
+---------------------------------------+-------------+
| name                                  | count(name) |
+---------------------------------------+-------------+
<snip>
| Tony Darnell                          |          13 |
| Giuseppe Maxia                        |           2 |
| planetmysql                           |           2 |
<snip>

name以外にtextにも生成列を設定しているので、コメントをフィルターする事も出来ます。
簡易デモなので何ですが、実用的な用途があれば簡単にJSONデータ処理出来るので色々なケースで使えるかもしれません。


root@localhost [NEW57]> desc X_PYTHON;
+-------+--------------+------+-----+---------+------------------+
| Field | Type         | Null | Key | Default | Extra            |
+-------+--------------+------+-----+---------+------------------+
| doc   | json         | YES  |     | NULL    |                  |
| _id   | varchar(32)  | NO   | PRI | NULL    | STORED GENERATED |
| name  | varchar(64)  | YES  | MUL | NULL    | STORED GENERATED |
| text  | varchar(512) | YES  |     | NULL    | STORED GENERATED |
+-------+--------------+------+-----+---------+------------------+
4 rows in set (0.00 sec)

root@localhost [NEW57]> 

【補足資料】
先日、MyNA(MySQL User Group)のイベントでMySQL AS Document Storeのデモした時の資料です。


mysql fabricにて書き込みのRANGEシャーディングしたテーブルから、
データを読み込みの確認。

分割定義


# Create Sharding Grop1
mysqlfabric group create shard1
mysqlfabric group add shard1 127.0.0.1:63304
mysqlfabric group add shard1 127.0.0.1:63305
mysqlfabric group add shard1 127.0.0.1:63306
mysqlfabric group promote shard1
mysqlfabric group activate shard1

# Create Sharding Group2
mysqlfabric group create shard2
mysqlfabric group add shard2 127.0.0.1:63307
mysqlfabric group add shard2 127.0.0.1:63308
mysqlfabric group add shard2 127.0.0.1:63309
mysqlfabric group promote shard2
mysqlfabric group activate shard2

# Add sharding to employee
mysqlfabric sharding add_table 1 test.employees emp_no
mysqlfabric sharding add_shard 1 shard1/1,shard2/300 --state=enabled

select_shard

それぞれのグループに分散されたテーブルからデータを読み込み
0 ~ 299は、shard1
300以上はshard2


# coding: utf-8

import mysql.connector 
from mysql.connector import fabric

import time

def find_employee(conn, emp_no):
    try:
       conn.set_property(tables=["test.employees"], key=str(emp_no), mode=fabric.MODE_READONLY, scope=fabric.SCOPE_LOCAL)
       cur = conn.cursor()
       cur.execute(
           "SELECT concat('Hostname:',@@hostname),concat(': ',@@port,' ; '), first_name, last_name "
           "FROM employees WHERE emp_no = %s", (emp_no,)
        )
    except mysql.connector.Error: 
       print "find_employee: Database connection error, trying to reconnect ..." 
       conn=connect() 
    for row in cur:
        print row[0],row[1],row[2],row[3]

# Address of the Fabric, not the host we are going to connect to.
def connect():
    try:
       conn=mysql.connector.connect(
          fabric={"host" : "localhost", "port" : 32274, "username": "admin", "password": "admin"},
          user="app", database="test", password="app",
          autocommit=True
       )
    except mysql.connector.Error as e: 
        print "Error trying to get a new database connection"
        print "Error code:", e.errno 
        print "SQLSTATE value:", e.sqlstate
        print "Error message:", e.msg
        quit()
    return conn

conn = connect()
for x in range(295, 310):
    find_employee(conn, x)


実行結果
メモ:それぞれのFabric Groupのスレーブから分散してデータを持ってきている。
select_shard_range


申し訳無かったのですが、日々慌ただしく過ごしていて、
これまでMySQL Fabricについて理解仕切れてない部分もあり、
自信をもって話す事が出来なかった。

反省の意味も含めて、きちんとMySQL Fabricを理解する為に検証してみた。
丁度、スウェーデンから来た仲間から、色々とMySQL Fabricの詳細を教えて頂き、
短時間でかなり理解が深まり非常に助かりました。 Thank you very much!!

もう少し検証して、これからMySQL Fabricを使う皆さんに情報提供出来ればと思います。
Fabricはネット企業でMySQL Replicationを運用していた自分からすると、
かなり便利な機能だと思います。 GTIDも良い機能で運用を楽にしてくれましたが、
MySQL Fabricはそれにも増して良い機能です。
実際に開発、ステージング環境で利用して頂き、運用に慣れて頂いたら、
是非本番環境でも利用して頂き、マスターの冗長化、スケールアウトに使ってみて頂ければと思います。
コネクターは、今後更に増えて行く予定です。

connector

mysqlfabricコマンドリスト

-bash-4.2$ mysqlfabric --version
mysqlfabric 1.5.3
-bash-4.2$ mysqlfabric help commands
statistics node             Retrieve statistics on the Fabric node.
statistics group            Retrieve statistics on Procedures.
statistics procedure        Retrieve statistics on Procedures.
group activate              Activate failure detector for server(s) in a group.
group description           Update group's description.
group deactivate            Deactivate failure detector for server(s) in a group.
group create                Create a group.
group remove                Remove a server from a group.
group add                   Add a server into group.
group health                Check if any server within a group has failed and report health
                            information.
group lookup_servers        Return information on existing server(s) in a group.
group destroy               Remove a group.
group demote                Demote the current master if there is one.
group promote               Promote a server into master.
group lookup_groups         Return information on existing group(s).
dump fabric_nodes           Return a list of Fabric servers.
dump shard_index            Return information about the index for all mappings matching any of
                            the patterns provided.
dump sharding_information   Return all the sharding information about the tables passed as
                            patterns.
dump servers                Return information about servers.
dump shard_tables           Return information about all tables belonging to mappings matching any
                            of the provided patterns.
dump shard_maps             Return information about all shard mappings matching any of the
                            provided patterns.
manage teardown             Teardown Fabric Storage System.
manage stop                 Stop the Fabric server.
manage setup                Setup Fabric Storage System.
manage ping                 Check whether Fabric server is running or not.
manage start                Start the Fabric server.
manage logging_level        Set logging level.
server set_mode             Set a server's mode.
server clone                Clone the objects of a given server into a destination server.
server list                 Return information on existing machine(s) created by a provider.
server set_weight           Set a server's weight.
server lookup_uuid          Return server's uuid.
server set_status           Set a server's status.
server destroy              Destroy a virtual machine instance.
server create               Create a virtual machine instance:  mysqlfabric server create provider
                            --image name=image-mysql         --flavor
                            name=vm-template --meta db=mysql --meta
                            version=5.
role list                   List roles and associated permissions
user roles                  Change roles for a Fabric user  * protocol: Protocol of the user (for
                            example 'xmlrpc') * roles: Comma separated
                            list of roles, IDs or names (see `role
                            list`)
user usercommand            Base class for all user commands
user list                   List users and their roles
user add                    Add a new Fabric user.
user password               Change password of a Fabric user.
user delete                 Delete a Fabric user.
threat report_error         Report a server error.
threat report_failure       Report with certantity that a server has failed or is unreachable.
provider unregister         Unregister a provider.
provider register           Register a provider.
provider list               Return information on existing provider(s).
sharding list_definitions   Lists all the shard mapping definitions.
sharding remove_definition  Remove the shard mapping definition represented by the Shard Mapping
                            ID.
sharding move_shard         Move the shard represented by the shard_id to the destination group.
sharding disable_shard      Disable a shard.
sharding remove_table       Remove the shard mapping represented by the Shard Mapping object.
sharding split_shard        Split the shard represented by the shard_id into the destination
                            group.
sharding create_definition  Define a shard mapping.
sharding add_shard          Add a shard.
sharding add_table          Add a table to a shard mapping.
sharding lookup_table       Fetch the shard specification mapping for the given table
sharding enable_shard       Enable a shard.
sharding remove_shard       Remove a Shard.
sharding list_tables        Returns all the shard mappings of a particular sharding_type.
sharding prune_shard        Given the table name prune the tables according to the defined
                            sharding specification for the table.
sharding lookup_servers     Lookup a shard based on the give sharding key.
snapshot destroy            Destroy snapshot images associated to a machine.
snapshot create             Create a snapshot image from a machine.
event trigger               Trigger an event.
event wait_for_procedures   Wait until procedures, which are identified through their uuid in a
                            list and separated by comma, finish their
                            execution.
-bash-4.2$ 


Fabricで模擬障害を発生させた状態
MySQLFabric

ループさせたスクリプトが数秒で元に戻り、無事にマスターが切り替わっている事を確認出来た
Switch

スレーブの追加

[root@Fabric01 fabric]# mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
1711c4ac-7cd7-11e4-b640-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
17b7d8c5-7cd7-11e4-b640-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
183d8f8b-7cd7-11e4-b640-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0


[root@Fabric01 fabric]# mysqlfabric group add global 127.0.0.1:63304
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
5f8a1955-17de-48cf-af49-50a6fe82d08f        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1417822757.64 Triggered by <mysql.fabric.events.Event object at 0x2266410>.
    4       2 1417822757.64                               Executing action (_add_server).
    5       2  1417822757.9                                Executed action (_add_server).


[root@Fabric01 fabric]# mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
1711c4ac-7cd7-11e4-b640-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
17b7d8c5-7cd7-11e4-b640-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
183d8f8b-7cd7-11e4-b640-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
18da4983-7cd7-11e4-b640-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0


[root@Fabric01 fabric]# 

スレーブ追加後は読み込みも分散されている事を確認出来た。
ノードの追加もスムーズに対応可能

(u'hostname:Fabric01', u'port:63302', u'John:800', u'Doe')
(u'hostname:Fabric01', u'port:63303', u'John:801', u'Doe')
(u'hostname:Fabric01', u'port:63304', u'John:802', u'Doe')

6ノード追加した状態

-bash-4.2$ mysqlfabric group add global 127.0.0.1:63306
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
5c150bd9-2124-42db-9880-a48099cdb0e6        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2 1418042190.25 Triggered by <mysql.fabric.events.Event object at 0x2a4e410>.
    4       2 1418042190.26                               Executing action (_add_server).
    5       2 1418042190.65                                Executed action (_add_server).


-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
6b22b751-7ed4-11e4-833a-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
6bb7b696-7ed4-11e4-833a-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
6c57c468-7ed4-11e4-833a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
6cef3a69-7ed4-11e4-833a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
6d8a7a63-7ed4-11e4-833a-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
6e2582b7-7ed4-11e4-833a-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0

-bash-4.2$ 

fabric6

疑似障害でマスターを停止して、Fabricログテーブルを確認してみた

-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
6b22b751-7ed4-11e4-833a-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
6bb7b696-7ed4-11e4-833a-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
6c57c468-7ed4-11e4-833a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
6cef3a69-7ed4-11e4-833a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
6d8a7a63-7ed4-11e4-833a-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
6e2582b7-7ed4-11e4-833a-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-bash-4.2$ kill 2430
-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
6b22b751-7ed4-11e4-833a-08002766cefe 127.0.0.1:63301    FAULTY READ_WRITE    1.0
6bb7b696-7ed4-11e4-833a-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
6c57c468-7ed4-11e4-833a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
6cef3a69-7ed4-11e4-833a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
6d8a7a63-7ed4-11e4-833a-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
6e2582b7-7ed4-11e4-833a-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
6b22b751-7ed4-11e4-833a-08002766cefe 127.0.0.1:63301    FAULTY READ_WRITE    1.0
6bb7b696-7ed4-11e4-833a-08002766cefe 127.0.0.1:63302   PRIMARY READ_WRITE    1.0
6c57c468-7ed4-11e4-833a-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
6cef3a69-7ed4-11e4-833a-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0
6d8a7a63-7ed4-11e4-833a-08002766cefe 127.0.0.1:63305 SECONDARY  READ_ONLY    1.0
6e2582b7-7ed4-11e4-833a-08002766cefe 127.0.0.1:63306 SECONDARY  READ_ONLY    1.0


-bash-4.2$ 
 mysql> select * from fabric.log order by reported desc limit 0,20;
+----------------------+----------------------------+----------------------+-------------------------------------------------------------------------------------------------------+----------+------+
| subject              | reported                   | reporter             | message                                                                                               | category | type |
+----------------------+----------------------------+----------------------+-------------------------------------------------------------------------------------------------------+----------+------+
| dump.servers         | 2014-12-08 12:53:36.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:53:36.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| group.lookup_servers | 2014-12-08 12:52:41.000000 | mysql.fabric.command | Started command (group, lookup_servers).                                                              |        1 |    0 |
| group.lookup_servers | 2014-12-08 12:52:41.000000 | mysql.fabric.command | Finished command (group, lookup_servers).                                                             |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:36.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:36.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:35.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:35.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| group.lookup_servers | 2014-12-08 12:52:35.000000 | mysql.fabric.command | Started command (group, lookup_servers).                                                              |        1 |    0 |
| group.lookup_servers | 2014-12-08 12:52:35.000000 | mysql.fabric.command | Finished command (group, lookup_servers).                                                             |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:34.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:34.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| global               | 2014-12-08 12:52:33.000000 | mysql.fabric.server  | Master has changed from 6b22b751-7ed4-11e4-833a-08002766cefe to 6bb7b696-7ed4-11e4-833a-08002766cefe. |        3 |    3 |
| dump.servers         | 2014-12-08 12:52:33.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:33.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:30.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:30.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:28.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
| dump.servers         | 2014-12-08 12:52:28.000000 | mysql.fabric.command | Finished command (dump, servers).                                                                     |        1 |    1 |
| dump.servers         | 2014-12-08 12:52:06.000000 | mysql.fabric.command | Started command (dump, servers).                                                                      |        1 |    0 |
+----------------------+----------------------------+----------------------+-------------------------------------------------------------------------------------------------------+----------+------+
20 rows in set (0.00 sec)

mysql> 

動作確認中 [ Promote ] 2014/12/12現在

-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
35c1ed6f-80d4-11e4-9043-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
360cc0ab-80d4-11e4-9043-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
373d787b-80d4-11e4-9044-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
375c9e12-80d4-11e4-9044-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0


-bash-4.2$ mysqlfabric group promote global --slave_id=375c9e12-80d4-11e4-9044-08002766cefe
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
267fa866-c5c9-4f5c-a5a4-6a9d90711d90        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2  1418262234.4 Triggered by <mysql.fabric.events.Event object at 0x17c8810>.
    4       2  1418262234.4                      Executing action (_define_ha_operation).
    5       2 1418262234.44                       Executed action (_define_ha_operation).
    3       2 1418262234.41 Triggered by <mysql.fabric.events.Event object at 0x1942bd0>.
    4       2 1418262234.44                   Executing action (_check_candidate_switch).
    5       2 1418262234.73                    Executed action (_check_candidate_switch).
    3       2 1418262234.52 Triggered by <mysql.fabric.events.Event object at 0x1942c10>.
    4       2 1418262234.73                       Executing action (_block_write_switch).
    5       2 1418262234.79                        Executed action (_block_write_switch).
    3       2 1418262234.77 Triggered by <mysql.fabric.events.Event object at 0x1942c50>.
    4       2 1418262234.79                       Executing action (_wait_slaves_switch).
    5       2 1418262234.86                        Executed action (_wait_slaves_switch).
    3       2 1418262234.86 Triggered by <mysql.fabric.events.Event object at 0x1942c90>.
    4       2 1418262234.86                      Executing action (_change_to_candidate).
    5       2 1418262236.49                       Executed action (_change_to_candidate).


-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
35c1ed6f-80d4-11e4-9043-08002766cefe 127.0.0.1:63301 SECONDARY  READ_ONLY    1.0
360cc0ab-80d4-11e4-9043-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
373d787b-80d4-11e4-9044-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
375c9e12-80d4-11e4-9044-08002766cefe 127.0.0.1:63304   PRIMARY READ_WRITE    1.0


-bash-4.2$ 



-bash-4.2$ mysqlfabric group health global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid is_alive    status is_not_running is_not_configured io_not_running sql_not_running io_error sql_error
------------------------------------ -------- --------- -------------- ----------------- -------------- --------------- -------- ---------
35c1ed6f-80d4-11e4-9043-08002766cefe        1 SECONDARY              0                 0              0               0    False     False
360cc0ab-80d4-11e4-9043-08002766cefe        1 SECONDARY              0                 0              0               0    False     False
373d787b-80d4-11e4-9044-08002766cefe        1 SECONDARY              0                 0              0               0    False     False
375c9e12-80d4-11e4-9044-08002766cefe        1   PRIMARY              0                 0              0               0    False     False

issue
-----


-bash-4.2$ 

スレーブはロードバランサーで分散出来るし、障害も分散されていれば気にしないで良いけど、
MySQL Fabricはマスター障害でも自動でマスターを切り替えてくれるので便利です。
また、マスターサーバーの切り替えもコマンドで出来て、マスターサーバーの入れ替えが楽になりそうです。
これから、また色々と勉強と検証重ねていき、皆さんと共有して行きたいと思います。

Bug #75188 Promoteする場合のアプリケーションユーザー権限に関する留意点

参照: http://www-jp.mysql.com/products/enterprise/fabric.html


PythonからXLSを操作するモジュール
Extract data from Excel spreadsheets (.xls and .xlsx, versions 2.0 onwards) on any platform.
Pure Python (2.6, 2.7, 3.2+). Strong support for Excel dates. Unicode-aware.

$ cd 'C:\Python27\xls'
$ pwd
/c/Python27/xls
$ ls -l
合計 175
-rw-r--r-- 1 python_user Administrators 178490 Aug  5 11:10 xlrd-0.9.3.tar.gz

$ tar zxvf xlrd-0.9.3.tar.gz
xlrd-0.9.3/
xlrd-0.9.3/PKG-INFO
xlrd-0.9.3/README.html
xlrd-0.9.3/scripts/
xlrd-0.9.3/setup.py
.. 省略
xlrd-0.9.3/tests/test_xldate.py
xlrd-0.9.3/tests/test_xldate_to_datetime.py
xlrd-0.9.3/tests/test_xlsx_comments.py
xlrd-0.9.3/tests/text_bar.xlsx
xlrd-0.9.3/tests/xf_class.xls
xlrd-0.9.3/scripts/runxlrd.py

$ ls -l
合計 179
drwxr-xr-x 5 python_user Administrators   4096 Apr  9 16:24 xlrd-0.9.3
-rw-r--r-- 1 python_user Administrators 178490 Aug  5 11:10 xlrd-0.9.3.tar.gz

$ cd xlrd-0.9.3

$ ls -l
合計 20
-rw-r--r-- 1 python_user Administrators  994 Apr  9 16:24 PKG-INFO
-rw-r--r-- 1 python_user Administrators 4672 Jun 11  2013 README.html
drwxr-xr-x 2 python_user Administrators    0 Aug  5 11:13 scripts
-rwxr-xr-x 1 python_user Administrators 1887 Jun 11  2013 setup.py
drwxr-xr-x 2 python_user Administrators 8192 Aug  5 11:13 tests
drwxr-xr-x 2 python_user Administrators 4096 Apr  9 16:24 xlrd

$ python setup.py install
running install
running build
running build_py
creating build
creating build\lib
creating build\lib\xlrd
copying xlrd\biffh.py -> build\lib\xlrd
copying xlrd\book.py -> build\lib\xlrd
copying xlrd\compdoc.py -> build\lib\xlrd
.. 省略
byte-compiling c:\Python27\Lib\site-packages\xlrd\info.py to info.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\licences.py to licences.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\sheet.py to sheet.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\timemachine.py to timemachine.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\xldate.py to xldate.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\xlsx.py to xlsx.pyc
byte-compiling c:\Python27\Lib\site-packages\xlrd\__init__.py to __init__.pyc
running install_scripts
creating c:\Python27\Scripts
copying build\scripts-2.7\runxlrd.py -> c:\Python27\Scripts
running install_egg_info
Writing c:\Python27\Lib\site-packages\xlrd-0.9.3-py2.7.egg-info
$
$ cat read_xls_file.py
# coding: utf-8

import xlrd
import urllib

def read_xls(url):
    webpage = urllib.urlopen(url)
    webdata = webpage.read()
    webpage.close()
    book = xlrd.open_workbook(file_contents=webdata)
    sheet1 = book.sheet_by_index(0)
    for col in range(sheet1.ncols):
        print "----------------------------"
        for row in range(sheet1.nrows):
            cell=sheet1.cell(row,col)
            if cell.ctype == xlrd.XL_CELL_TEXT:
                print 'col=', col, 'row=', row, cell.value.encode('UTF-8')
            else:
                print 'col=', col, 'row=', row, cell.value
if __name__ == '__main__':
    import sys
    if len( sys.argv ) > 1:
        url = sys.argv[1]
    read_xls(url)

$

電力情報をWebから取得してデータ作成して値段推移を確認してみる。

$ python read_xls_file.py  'http://www.enecho.meti.go.jp/about/whitepaper/2013html/data/whitepaper2013_214-1-7.xls' | grep 'row= 4' | awk '{print $5}'

電灯
24.805595108
24.6026352946
24.2067276624
24.4931131033
23.3280284336
23.061707533
23.0761062356
22.7901832667
21.8335163592
21.4982452593
21.2212843648
20.7917694214
20.7261067007
20.7846825484
21.8873581716
20.5422138002
20.3707924016
21.2596934385

$ python read_xls_file.py  'http://www.enecho.meti.go.jp/about/whitepaper/2013html/data/whitepaper2013_214-1-7.xls' | grep 'row= 5' | awk '{print $5}'

電力
17.1488350119
16.9583292586
16.5184136265
16.7650452054
15.8949172913
15.4675782507
15.4433739677
15.4572175847
14.3915064494
14.0749796905
13.7543726135
13.5120790035
13.6176359413
13.6556277198
15.2149111291
13.7677260803
13.6462341174
14.5917638787

$ python read_xls_file.py  'http://www.enecho.meti.go.jp/about/whitepaper/2013html/data/whitepaper2013_214-1-7.xls' | grep 'row= 6' | awk '{print $5}'

電灯・電力計
19.3784749229
19.2269525733
18.7837216319
19.0340122112
18.1364447046
17.7751731119
17.7624823149
17.7215241422
16.7213643101
16.3852613132
16.1059097541
15.8322628265
15.8420723513
15.9017966876
17.3563940883
16.0163599156
15.9032620599
16.8325761746

$

Reference:
http://d.hatena.ne.jp/addition/20140104/1388832149
http://www.python-izm.com/contents/external/xlrd.shtml
http://stackoverflow.com/questions/15588713/sheets-of-excel-workbook-from-a-url-into-a-pandas-dataframe
http://stackoverflow.com/questions/3665379/django-and-xlrd-reading-from-memory
http://geeks-squad.com/access-excel-file-in-python
http://java.dzone.com/articles/reading-excel-spreadsheets


10,000件のデータをInsertしてみて1件ずつのCommitと
全件InsertしてからCommitした場合で実行時間を確認してみた。

サンプルコード

# coding: utf-8

try:
 # import

 import mysql.connector
 import string
 from random import randrange


 # 接続
 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',())

 for i in  range(0, 10000):

  # Random Charactor Create
  LENGTH = 20
  alphabets = string.digits + string.letters

  def randstr(n):
      return ''.join(alphabets[randrange(len(alphabets))] for i in xrange(n))
  if __name__ == '__main__':


   randstr_ins =  randstr(LENGTH)
   insert_stmt = 'insert into language(name) values("%s")' % randstr_ins
   cursor.execute(insert_stmt)
   #print insert_stmt
   #connect.commit()

 else:
    print('Finish Creating Data')

 # フェッチ
 #
 # rows = cursor.fetchall()
 # print(rows)
 connect.commit()
 cursor.close()
 # 切断
 connect.close()

except Exception as myout:

 print(myout)

■全てのデータInsertが完了してからCommitした場合

1回目
$ time python mysql_loop.py
Finish Creating Data

real 0m2.017s
user 0m0.000s
sys 0m0.062s

$

2回目
$ time python mysql_loop.py

real 0m1.917s
user 0m0.000s
sys 0m0.015s

$

3回目(100,000件で試してみた)
$ time python mysql_loop.py
Finish Creating Data

real 0m17.972s
user 0m0.000s
sys 0m0.031s

$

   randstr_ins =  randstr(LENGTH)
   insert_stmt = 'insert into language(name) values("%s")' % randstr_ins
   cursor.execute(insert_stmt)
   #print insert_stmt
   #connect.commit()

 else:
    print('Finish Creating Data')

 # フェッチ
 #
 # rows = cursor.fetchall()
 # print(rows)
 connect.commit()

■1件Insert毎にCommitした場合

1回目
$ time python mysql_loop.py
Finish Creating Data

real 0m5.950s
user 0m0.000s
sys 0m0.031s

$

2回目
$ time python mysql_loop.py
Finish Creating Data

real 0m6.006s
user 0m0.000s
sys 0m0.062s

$

3回目(100,000件で試してみた)
$ time python mysql_loop.py
Finish Creating Data

real 0m58.563s
user 0m0.000s
sys 0m0.046s

$

   randstr_ins =  randstr(LENGTH)
   insert_stmt = 'insert into language(name) values("%s")' % randstr_ins
   cursor.execute(insert_stmt)
   #print insert_stmt
   connect.commit()

 else:
    print('Finish Creating Data')

 # フェッチ
 #
 # rows = cursor.fetchall()
 # print(rows)
 # connect.commit()

結果

mysql> select * from language order by language_id desc limit 0,10;
+-------------+----------------------+---------------------+
| language_id | name                 | last_update         |
+-------------+----------------------+---------------------+
|       80300 | TFEGGvjH8b74uFawV7OS | 2014-08-05 10:11:14 |
|       80299 | ZMAF25n87TaIjgT8qJgr | 2014-08-05 10:11:14 |
|       80298 | sxV2CjebjaMpmOlQe9Yf | 2014-08-05 10:11:14 |
|       80297 | s9c0avx5UumRtcPdtDRu | 2014-08-05 10:11:14 |
|       80296 | MH0M5t7jD9F2jGs3jbM4 | 2014-08-05 10:11:14 |
|       80295 | Ap4ThCp5RsFHAOrUw0BV | 2014-08-05 10:11:14 |
|       80294 | cwLz0e8Vx5L73rzJd6uW | 2014-08-05 10:11:14 |
|       80293 | FAfnZReIagqi4mgCnsSV | 2014-08-05 10:11:14 |
|       80292 | YfxFjzfJVATB5sEHnkR0 | 2014-08-05 10:11:14 |
|       80291 | wENaU30qX4aF51tpDSfY | 2014-08-05 10:11:14 |
+-------------+----------------------+---------------------+
10 rows in set (0.00 sec)

mysql>

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

mysql>

※ やはりCommitの回数が少ない方が断然早い。

※ InnoDB のデフォルト分離レベルは、REPEATABLE-READなので、
  Commitが終わったものが見る事が出来ます。
  Selectをバッチ中に確認すると1回毎にCommitしているQueryは、
  Selectを実施する毎にその時の最新の状況を確認する事が出来ます。

Reference: 13.5.10.3. InnoDB と TRANSACTION ISOLATION LEVEL
http://dev.mysql.com/doc/refman/5.1/ja/innodb-transaction-isolation.html


Pythonによる、乱数を用いたデータInsert処理確認

事前データ状況確認

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> 

乱数を用いてデータInsertする為に処理を入れる。
但し、データベースのテーブル内容とは関係無い文字列を利用しています。

$ vim mysql_loop.py
$ cat mysql_loop.py
# coding: utf-8

try:
 # import

 import mysql.connector
 import string
 from random import randrange

 # 接続
 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',())

  # Random Charactor Create
 LENGTH = 20
 alphabets = string.digits + string.letters

 def randstr(n):
     return ''.join(alphabets[randrange(len(alphabets))] for i in xrange(n))
 if __name__ == '__main__':

   randstr_ins =  randstr(LENGTH)
   insert_stmt = 'insert into language(name) values("%s")' % randstr_ins
   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_loop.py

20文字で作成された乱数文字が入力された事を確認。

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 |
|          11 | bihnbj3Mr4xcRxtaJaDE | 2014-08-04 16:04:56 |
+-------------+----------------------+---------------------+
11 rows in set (0.00 sec)

mysql>

上記スクリプトの確認出来たので、データ作成用のLoopが動くか確認。
1) 事前データ確認

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 |
|          11 | bihnbj3Mr4xcRxtaJaDE | 2014-08-04 16:04:56 |
|          12 | xadk5JJmgPLkw7kFQ2LT | 2014-08-04 16:10:46 |
+-------------+----------------------+---------------------+
12 rows in set (0.00 sec)

mysql>

2) PRINTコマンドで確認

$ vim mysql_loop.py
$ python mysql_loop.py
insert into language(name) values("t6hiIohEArPADMXA7P35")
insert into language(name) values("51akdy18UtKDJVeNXjdK")
insert into language(name) values("BiQnfmMX4hlBynVJpXKC")
insert into language(name) values("6NdLDWOS72vKy4hfYtgZ")
insert into language(name) values("j2WbDYCt63PO5XQtK5Qr")
insert into language(name) values("QR05Zd7Y8Y9EnKXFqjMr")
insert into language(name) values("JMIF6GKBF7yyHnVUxFc0")
insert into language(name) values("ScgelcWkaQVrLKkPkjtV")
insert into language(name) values("1bWLI0OMRv6CNEhe8Iax")
insert into language(name) values("ZTSCsBNyWqxN32ErJnxE")
Finish Creating Data

$ vim mysql_loop.py

3) PRINTをコメントして実際のSQLコマンド実行してみる。

$ cat mysql_loop.py
# coding: utf-8

try:
 # import

 import mysql.connector
 import string
 from random import randrange

 # 接続
 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',())

 for i in  range(0, 10):

  # Random Charactor Create
  LENGTH = 20
  alphabets = string.digits + string.letters

  def randstr(n):
      return ''.join(alphabets[randrange(len(alphabets))] for i in xrange(n))
  if __name__ == '__main__':

   randstr_ins =  randstr(LENGTH)
   insert_stmt = 'insert into language(name) values("%s")' % randstr_ins
   cursor.execute(insert_stmt)
   #print insert_stmt
   #connect.commit()

 else:
    print('Finish Creating Data')

 # フェッチ
 #
 # rows = cursor.fetchall()
 # print(rows)
 connect.commit()
 cursor.close()
 # 切断
 connect.close()

except Exception as myout:

 print(myout)

$
$ python mysql_loop.py
Finish Creating Data
$

4) Loopされた後にテーブルを確認してみて、テスト用のデータ作成確認

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 |
|          11 | bihnbj3Mr4xcRxtaJaDE | 2014-08-04 16:04:56 |
|          12 | xadk5JJmgPLkw7kFQ2LT | 2014-08-04 16:10:46 |
|          13 | 8yM0B3BgYbOt2KQW141i | 2014-08-04 16:51:28 |
|          14 | PKvnobsxLU3dTZZBItn7 | 2014-08-04 16:51:28 |
|          15 | aXu9jdW8OWL7ajtkYf4R | 2014-08-04 16:51:28 |
|          16 | 7BGNAekeHNeCSCM8kMxS | 2014-08-04 16:51:28 |
|          17 | pKwi8LY8HyAKKmEs7Th0 | 2014-08-04 16:51:28 |
|          18 | p5MADXZyinC9Yetr51nj | 2014-08-04 16:51:28 |
|          19 | P2hBaAK9HbCiVW8gBSPj | 2014-08-04 16:51:28 |
|          20 | Igddxz3UzmwEzYacX6iO | 2014-08-04 16:51:28 |
|          21 | FslzpwD8WKBbBhOkvTKz | 2014-08-04 16:51:28 |
|          22 | yE8AheE4dglD3lcojEfc | 2014-08-04 16:51:28 |
+-------------+----------------------+---------------------+
22 rows in set (0.00 sec)

mysql>

こんな感じでrandomで変数を取得する事も出来る。

$ cat randum_import.py
# coding: utf-8

import string
from random import randrange

import random


LENGTH = 20
alphabets = string.digits + string.letters

def randstr(n):
    return ''.join(alphabets[randrange(len(alphabets))] for i in xrange(n))
if __name__ == '__main__':


 print randstr(LENGTH)
 print random.random()


 mix_string = randstr(LENGTH) +  str(random.random())
 print mix_string

$ python randum_import.py
2GaN2hELQd7sDe9Hxdt6
0.28266427386
Zxp7xV3cMolzZFaXtB9p0.235414511771

$

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?


OpenStackでもMySQL UtilityでもPythonが使われていて、
関わっていく事も多いと思うので、少しずつPythonにCatch Upしておく。

Download the latest version for Windows 2.7.8
https://www.python.org/downloads/

admin@local#cat hello_world.py
# coding: utf-8

print "Hello World"
print "こんにちはPythonです"

import datetime # Import datetime Module
import locale   # import locale

d = datetime.datetime.today()

# year, month, day
print '%s年%s月%s日\n' % (d.year, d.month, d.day)

admin@local#python hello_world.py
Hello World
こんにちはPythonです
2014年7月30日

admin@local#

admin@local#cat count_row.py
# coding: utf-8

import sys
print "行数"
print len(sys.stdin.readlines())

admin@local#vim test
admin@local#cat test
1
2
3
4
5
6
7
admin@local#python count_row.py < test
行数
7
admin@local#
[/SHELL]


■MySQLと接続してみる
http://dev.mysql.com/downloads/connector/python/
Windows (Architecture Independent), MSI Installer
Python 2.7をインストールしました。

[SHELL]
$ cat mysql_test.py
# coding: utf-8

try:
 # import

 import mysql.connector

 # 接続
 connect = mysql.connector.connect(user='root', password='passowrd', host='localhost', database='test', charset='utf8')

 # カーソル
 cursor = connect.cursor()
 # SQL 発行
 cursor.execute('select language_id,name from language', ())
 # フェッチ
 rows = cursor.fetchall()
 print(rows)
 cursor.close()
 # 切断
 connect.close()

except Exception as myout:

 print(myout)

$
[/SHELL]

テーブルの中身はこんな感じ
[SQL]
mysql> select language_id,name from language;
+-------------+----------+
| language_id | name     |
+-------------+----------+
|           1 | English  |
|           2 | Italian  |
|           3 | Japanese |
|           4 | Mandarin |
|           5 | French   |
|           6 | German   |
+-------------+----------+
6 rows in set (0.00 sec)

mysql>
[/SQL]


■実行するとこんな感じでアウトプットが出てくる。

[SHELL]

$ python mysql_test.py
[(1, u'English'), (2, u'Italian'), (3, u'Japanese'), (4, u'Mandarin'), (5, u'French'), (6, u'German')]

$

■Sample ScriptフォルダーにあるGoogle Searchのスクリプトは
 引数を後ろに付けられる。試しに確認してみた。

$ cat google.py
#! /usr/bin/env python

import sys, webbrowser

def main():
    args = sys.argv[1:]
    if not args:
        print "Usage: %s querystring" % sys.argv[0]
        return
    list = []
    for arg in args:
        if '+' in arg:
            arg = arg.replace('+', '%2B')
        if ' ' in arg:
            arg = '"%s"' % arg
        arg = arg.replace(' ', '+')
        list.append(arg)
    s = '+'.join(list)
    url = "http://www.google.com/search?q=%s" % s
    webbrowser.open(url)

if __name__ == '__main__':
    main()

$