Python MySQLデータベース操作その１(基本編) mysqlclientモジュール
2018年7月10日Python Bottle Framework, テクニカル, トピックス, ノウハウ
pythonでMySQLの操作をする場合現在ではmysqlclientモジュールを利用するユーザ数が多いようだ。フレームワークではbottleに限らず使えます。MySQL操作ライブラリーはPyMySQLが多いけど、今回はmysqlclientモジュールを使った場合の基本操作をまとめて行こうと思います。どのライブラリーでも考え方は同じですから改造は簡単だと思います。本テストではカーソルは意図的にcloseしていません。規模が多くなりメモリの再利用が必要な場合は必要かと思いますが通常の小規模では不要なケースのほうが多いと思います。予め「pip install MySQL-python」or「pip install pymysql」をインストールしてください。mysql driverはpip側のアップデートで名前が変わったりすることもあります。また互換性のあるdriverであれば何でも良いと思いますのでpip searchで調べてみてください。
とりあえず、データベースを実験用に２つ用意します。
user_dbデータベース usersテーブル
|
1 2 3 4 5 6 7 8 9 10 |
mysql> desc users; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(40) | NO | UNI | NULL | | | passwd | varchar(50) | NO | | NULL | | | role | tinyint(4) | NO | | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) |
local_areaデータベース districtテーブル
|
1 2 3 4 5 6 7 8 |
mysql> desc district; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | area | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
下記の手順で一気にDB作成、テーブル作成、データ挿入までやります。
|
1 2 3 4 |
# mkdir workspace # unzip db\sample.zip # mysql -u root -p < user_db.sql # mysql -u root -p < local_area.sql |
DB接続情報をyamlファイルで保存
viかnanoエディターでdb.yamlと開いて接続情報を次のような感じで記述保存してください。プログラムに記述しないでDB接続を書いておくとやはり便利です。とくに沢山のDBやテーブルを操作するのであればそうした方が合理的になると思います。ご自分のMySQL環境に合わせてユーザ名やパスワードを書き換えてください。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DB1: host: localhost user: user_kanri password: bochibochi25321 dbname: user_db charset: utf8mb4 DB2: host: localhost user: user_kanri password: bochibochi25321 dbname: local_area charset: utf8mb4 |
もし、yamlがインストールされていなかったら「apt-get install libyaml-dev」「yum install python-yaml」「pip3 install pyyaml」とかでインストールできると思います。
fetchoneで一つづつレコードを取り出すやり方で表示する場合
この方式は、データ量が多いときに有利です。１回毎に１レコードを取り出すためバッファの枯渇になることがありません。大規模ならこの方式を使うのが基本です。工夫次第で取得レコードを複数ライン取り出すことも可能でしょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
# coding:utf-8 import yaml import MySQLdb def config_db(): with open("db.yaml", "r") as file: config_set = yaml.load(file) return config_set def db_connect(data,db_name): item = data[db_name] dret = MySQLdb.connect( user = item['user'], passwd = item['password'], host = item['host'], db = item['dbname'], charset = item['charset']) return dret cons = db_connect(config_db(),'DB1') cur = cons.cursor() sql = "select * from users" cur.execute(sql) record = cur.fetchone() while record != None: print(record) record = cur.fetchone() cons.close() |
実行結果
|
1 2 3 4 5 |
root@LaserBeam5:~/database# python3.6 db_fetchone.py (1, '田中広輔', 'Desgw110', 1) (2, '宮田望', '653sSw', 0) (3, '島田大智', '123456', 1) (4, '中村美紀子', '123456', 1) |
fetchallで全てバッファに読み込んで表示する場合
メモリ空間が大きい場合は一度にバッファに詰め込んで取り出せるのでパフォーマンス的に有利です。しかしメモリの枯渇が生じる可能性があるので本格的に大規模系で使う場合はレコード数をチェックしてからメモリ容量を予測して不足かどうか判断の上、実行するか判断しないと行けない場合もあります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
#coding:utf-8 import yaml import MySQLdb def config_db(): with open("db.yaml", "r") as file: config_set = yaml.load(file) return config_set def db_connect(data,db_name): item = data[db_name] dret = MySQLdb.connect( user = item['user'], passwd = item['password'], host = item['host'], db = item['dbname'], charset = item['charset']) return dret cons = db_connect(config_db(),'DB1') cur = cons.cursor() sql = "select * from users" cur.execute(sql) # fetchall方式で取り出す場合 for row in cur.fetchall(): print(row) cons.close() |
実行結果
|
1 2 3 4 5 |
root@LaserBeam5:~/database# python3.6 db_fetchall.py (1, '田中広輔', 'Desgw110', 1) (2, '宮田望', '653sSw', 0) (3, '島田大智', '123456', 1) (4, '中村美紀子', '123456', 1) |
DB２つを使ってjoinでinner結合して表示する。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
# coding:utf-8 import yaml import MySQLdb def config_db(): with open("db.yaml", "r") as file: config_set = yaml.load(file) return config_set def db_connect(data,db_name): item = data[db_name] dret = MySQLdb.connect( user = item['user'], passwd = item['password'], host = item['host'], db = item['dbname'], charset = item['charset']) return dret cons = db_connect(config_db(),'DB1') cur = cons.cursor() sql ="select user_db.users.id,name,area from user_db.users inner join local_area.district on user_db.users.id=local_area.district.id" cur.execute(sql) for row in cur.fetchall(): print(row) cons.close() |
実行結果
|
1 2 3 4 5 |
root@LaserBeam5:~/database# python3.6 dbjoin.py (1, '田中広輔', '鹿児島') (2, '宮田望', '愛知県') (3, '島田大智', '大阪府') (4, '中村美紀子', '奈良県') |
他のデータベースを使用する場合
21行目の「cons = db_connect(config_db(),’DB1′)」の部分のDB1をDB2にすればDB2から情報を取り出すことが出来ます。あとは全て同じです。
タグ: MySQL, mysqlclient, Python, モジュール