mysqlclient:タグのアーカイブ｜ネットマーベラス㈱

Python MySQLデータベース操作その２(更新、トランザクション) mysqlclientモジュール

2018年7月12日Linux Tips, Python Bottle Framework, Security Tips, テクニカル, トピックス, ノウハウ

投稿者：担当者

今回はアップデート処理を行いトランザクション処理としてロールバック機能等を入れてみる。ついでにSQLインジェクション対策のパラメータ挿入の仕方で記述します。パラメータの入れ方は多くの人がハマってしまうPython特有のぶら下がりカンマをあえて使います。入れる型に合わせて記述を考慮しなければいけませんがPython独特な書き方だと中々気づきにくいものです。他の言語では複数のリストやarrayを記述する場合は最後のパラメータの後方にはカンマを入れないのですがpythonは必要となるケースがありますのでご注意ください。尚、予め「pip install MySQL-python」or「pip install pymysql」でインストールしてください。mysql driverはpip側のアップデートで名前が変わったりすることもあります。また互換性のあるdriverであれば何でも良いと思いますのでpip searchで調べてみてください。

更新、トランザクション処理を入れたプログラム。SQLインジェクション対策の書き方にもなっている。

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()

print('----------------------------')

pass_data='5555999'
try:
    #通常よくあるケースははフォーマット文とパラメータを"%"で区切るがそれだとSQLインジェクションになる。
    #下記の様に型書式だけでOK。内部でSQLインジェクション対策してくれる。
    sql = "update users set passwd=%s where name='宮田望'"
    #挿入パラメータが１つの場合でもタプル形式なので、pass_data後方にぶら下げカンマ入れること。皆ハマるポイントです。複数だと動作し、シングルだとエラーになる。
    cur.execute(sql,(pass_data,))
    cons.commit()

    sql = "select * from users"
    cur.execute(sql)

    record = cur.fetchone()
    while record != None:
        print(record)
        record = cur.fetchone()


except MySQLdb.Error as e:
    cons.rollback()
    print("MySQL Database Error: ",e)

cons.close()

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()

print('----------------------------')

pass_data='5555999'

try:

#通常よくあるケースははフォーマット文とパラメータを"%"で区切るがそれだとSQLインジェクションになる。

#下記の様に型書式だけでOK。内部でSQLインジェクション対策してくれる。

sql = "update users set passwd=%s where name='宮田望'"

#挿入パラメータが１つの場合でもタプル形式なので、pass_data後方にぶら下げカンマ入れること。皆ハマるポイントです。複数だと動作し、シングルだとエラーになる。

cur.execute(sql,(pass_data,))

cons.commit()

sql = "select * from users"

cur.execute(sql)

record = cur.fetchone()

while record != None:

print(record)

record = cur.fetchone()

except MySQLdb.Error as e:

cons.rollback()

print("MySQL Database Error: ",e)

cons.close()

更新前と更新後を実行

root@LaserBeam5:~/database# python3.6 testc.py
(1, '田中広輔', 'Desgw110', 1)
(3, '島田大智', '123456', 1)
(4, '中村美紀子', '123456', 1)
(7, '高田延彦', 'Test231', 1)
(10, '音無典子', '12345678', 0)
(17, '宮田望', '5555888', 1)
----------------------------
(1, '田中広輔', 'Desgw110', 1)
(3, '島田大智', '123456', 1)
(4, '中村美紀子', '123456', 1)
(7, '高田延彦', 'Test231', 1)
(10, '音無典子', '12345678', 0)
(17, '宮田望', '5555999', 1)
root@LaserBeam5:~/database#

root@LaserBeam5:~/database# python3.6 testc.py

(1, '田中広輔', 'Desgw110', 1)

(3, '島田大智', '123456', 1)

(4, '中村美紀子', '123456', 1)

(7, '高田延彦', 'Test231', 1)

(10, '音無典子', '12345678', 0)

(17, '宮田望', '5555888', 1)

----------------------------

(1, '田中広輔', 'Desgw110', 1)

(3, '島田大智', '123456', 1)

(4, '中村美紀子', '123456', 1)

(7, '高田延彦', 'Test231', 1)

(10, '音無典子', '12345678', 0)

(17, '宮田望', '5555999', 1)

root@LaserBeam5:~/database#

タグ: mysqlclient, Python, SQLインジェクション, トランザクション

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テーブル

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)

mysql> desc users;

+--------+-------------+------+-----+---------+----------------+

+--------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+--------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

local_areaデータベース districtテーブル

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)

mysql> desc district;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

db_sampleのSQLダウンロード（ZIP形式）

下記の手順で一気にDB作成、テーブル作成、データ挿入までやります。

# mkdir workspace
# unzip db\sample.zip
# mysql -u root -p < user_db.sql
# mysql -u root -p < local_area.sql

# 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環境に合わせてユーザ名やパスワードを書き換えてください。

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

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で一つづつレコードを取り出すやり方で表示する場合

この方式は、データ量が多いときに有利です。１回毎に１レコードを取り出すためバッファの枯渇になることがありません。大規模ならこの方式を使うのが基本です。工夫次第で取得レコードを複数ライン取り出すことも可能でしょう。

# 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()

# 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()

実行結果

root@LaserBeam5:~/database# python3.6 db_fetchone.py                                                                       
(1, '田中広輔', 'Desgw110', 1)
(2, '宮田望', '653sSw', 0)
(3, '島田大智', '123456', 1)
(4, '中村美紀子', '123456', 1)

root@LaserBeam5:~/database# python3.6 db_fetchone.py

(1, '田中広輔', 'Desgw110', 1)

(2, '宮田望', '653sSw', 0)

(3, '島田大智', '123456', 1)

(4, '中村美紀子', '123456', 1)

fetchallで全てバッファに読み込んで表示する場合

メモリ空間が大きい場合は一度にバッファに詰め込んで取り出せるのでパフォーマンス的に有利です。しかしメモリの枯渇が生じる可能性があるので本格的に大規模系で使う場合はレコード数をチェックしてからメモリ容量を予測して不足かどうか判断の上、実行するか判断しないと行けない場合もあります。

#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()

#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()

実行結果

root@LaserBeam5:~/database# python3.6 db_fetchall.py                                                                       
(1, '田中広輔', 'Desgw110', 1)
(2, '宮田望', '653sSw', 0)
(3, '島田大智', '123456', 1)
(4, '中村美紀子', '123456', 1)

root@LaserBeam5:~/database# python3.6 db_fetchall.py

(1, '田中広輔', 'Desgw110', 1)

(2, '宮田望', '653sSw', 0)

(3, '島田大智', '123456', 1)

(4, '中村美紀子', '123456', 1)

DB２つを使ってjoinでinner結合して表示する。

# 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()

# 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()

実行結果

root@LaserBeam5:~/database# python3.6 dbjoin.py                                                                            
(1, '田中広輔', '鹿児島')
(2, '宮田望', '愛知県')
(3, '島田大智', '大阪府')
(4, '中村美紀子', '奈良県')

root@LaserBeam5:~/database# python3.6 dbjoin.py

(1, '田中広輔', '鹿児島')

(2, '宮田望', '愛知県')

(3, '島田大智', '大阪府')

(4, '中村美紀子', '奈良県')

他のデータベースを使用する場合

21行目の「cons = db_connect(config_db(),’DB1′)」の部分のDB1をDB2にすればDB2から情報を取り出すことが出来ます。あとは全て同じです。

タグ: MySQL, mysqlclient, Python, モジュール

Python MySQLデータベース操作その２(更新、トランザクション) mysqlclientモジュール

Python MySQLデータベース操作その２(更新、トランザクション) mysqlclientモジュール

更新、トランザクション処理を入れたプログラム。SQLインジェクション対策の書き方にもなっている。

更新前と更新後を実行

Python MySQLデータベース操作その１(基本編) mysqlclientモジュール

user_dbデータベース　usersテーブル

local_areaデータベース districtテーブル

DB接続情報をyamlファイルで保存

fetchoneで一つづつレコードを取り出すやり方で表示する場合

fetchallで全てバッファに読み込んで表示する場合

DB２つを使ってjoinでinner結合して表示する。

他のデータベースを使用する場合

Tags

本日の記事Rating(毎日PM0:00リセット)

人気の記事（総合）

人気(Python/Security)記事

Python MySQLデータベース操作その２(更新、トランザクション) mysqlclientモジュール

Python MySQLデータベース操作その２(更新、トランザクション) mysqlclientモジュール

更新、トランザクション処理を入れたプログラム。SQLインジェクション対策の書き方にもなっている。

更新前と更新後を実行

Python MySQLデータベース操作その１(基本編) mysqlclientモジュール

user_dbデータベース usersテーブル

local_areaデータベース districtテーブル

DB接続情報をyamlファイルで保存

fetchoneで一つづつレコードを取り出すやり方で表示する場合

fetchallで全てバッファに読み込んで表示する場合

DB２つを使ってjoinでinner結合して表示する。

他のデータベースを使用する場合

Tags

本日の記事Rating(毎日PM0:00リセット)

人気の記事（総合）

人気(Python/Security)記事

user_dbデータベース　usersテーブル