トランザクション:タグのアーカイブ

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インジェクション, トランザクション

Python3 Bottleフレームワーク入門（その４）- ORM Peewee CRUD

2017年8月25日Python Bottle Framework, トピックス

投稿者：担当者

peeweeの続編です。前回では特定のデータを取得してWEBに表示するところまで行いましたが、今回は様々なパターンを実行してみたいと思います。

データベースから条件で抽出、条件で削除、条件でアップデート。表の結合(Join)の簡単なものを例に挙げます。

※テスト用に使うサンプルデータベースのimportファイルです。テストデータはご自分で入れてください。

MySQLテストインポートファイル

データのアップデート

既にあるフィールドを修正するケースです。まず最初に修正するレコードを探してそのポインターの変数を修正データに書き換えるという処理になります。下記はユーザ名（kasuo）を探してパスワードを書き換えています。

update = User.get(User.name=='kazuo')
update.passwd = 'hareruya5511'
update.save()

update = User.get(User.name=='kazuo')

update.passwd = 'hareruya5511'

update.save()

データの削除

指定したユーザの削除を行います。アップデート同様に特定ユーザを探して削除します。

deluser = User.get(User.name=='kazuo')
deluser.delete_instance()

1 2	deluser = User.get(User.name=='kazuo') deluser.delete_instance()

データを全部取得し表示

for person in User.select():
    print( person.name , person )

1 2	for person in User.select(): print( person.name , person )

UserとRoleテーブルを結合し出力

下記反転箇所でわかるように通常のSQL文を記述するよりも遥かにわかり易く記述が行える。

from peewee import *

database = MySQLDatabase('user_db', **{'user': 'root', 'host': 'localhost', 'password': 'test01'})

class UnknownField(object):
    def __init__(self, *_, **__): pass

class BaseModel(Model):
    class Meta:
        database = database

class User(BaseModel):
    name = CharField(unique=True)
    passwd = CharField()
    role = IntegerField() 

    class Meta:
        db_table = 'user'

class Role(BaseModel):
    role_name = CharField(unique=True)
    role_id = IntegerField(unique=True)

    class Meta:
        db_table = 'role'


database.connect()

joint = (User.role == Role.role_id) #結合条件を指定
query = User.select(User,Role).join(Role,on=joint).order_by(User.id)

for data in query: #クエリー結果を展開
    print(data.id,data.name,data.role.role_name)

database.close()

from peewee import *

database = MySQLDatabase('user_db', **{'user': 'root', 'host': 'localhost', 'password': 'test01'})

class UnknownField(object):

def __init__(self, *_, **__): pass

class BaseModel(Model):

class Meta:

database = database

class User(BaseModel):

name = CharField(unique=True)

passwd = CharField()

role = IntegerField()

class Meta:

db_table = 'user'

class Role(BaseModel):

role_name = CharField(unique=True)

role_id = IntegerField(unique=True)

class Meta:

db_table = 'role'

database.connect()

joint = (User.role == Role.role_id) #結合条件を指定

query = User.select(User,Role).join(Role,on=joint).order_by(User.id)

for data in query: #クエリー結果を展開

print(data.id,data.name,data.role.role_name)

database.close()

◯ 実行した結果は次のようになりました。

(newdev) [user01@xordes bdtest]$ python test.py
1 田岡光一 people
13 鈴木雄大 people
14 山田康之 admin
15 原田駿佑 admin
16 江川逞 admin
17 恵比寿大貴 admin
20 高市司 editor
21 湊修二 people
22 引田天功 admin

(newdev) [user01@xordes bdtest]$ python test.py

1 田岡光一 people

13 鈴木雄大 people

14 山田康之 admin

15 原田駿佑 admin

16 江川逞 admin

17 恵比寿大貴 admin

20 高市司 editor

21 湊修二 people

22 引田天功 admin

トランザクション処理の例

データベース書き込みでエクセプションエラーが発生した場合はロールバックが行われます。

try:
    database.create_tables([User, Role], True) #テーブルの自動生成

    with database.transaction():
         update = User.get(User.name=='田中光一')
         update.passwd = 'Yhdg745G%5cB'
         update.save() 

         deluser = User.get(User.name=='湊修二')
         deluser.delete_instance() 

         database.commit()

except IntegrityError as e:
    print (e)
    database.rollback()

try:

database.create_tables([User, Role], True) #テーブルの自動生成

with database.transaction():

update = User.get(User.name=='田中光一')

update.passwd = 'Yhdg745G%5cB'

update.save()

deluser = User.get(User.name=='湊修二')

deluser.delete_instance()

database.commit()

except IntegrityError as e:

print (e)

database.rollback()

合わせて呼んで欲しい記事
★Python MySQLデータベース操作その１
★Python MySQLデータベース操作その２

Python Bottle Framework入門全１３回: 1.基礎編サーバ起動; 2.リクエストメソッド; 3.ORM Peewee (MySQL); 4.ORM Peewee CRUD; 5.Cookie And Session; 6.Abort and Redirect; 7.マルチスレッドWEBサーバ; 8.デーモン化; 9.Json; 10.WSGI on SSL; 11.Apache連携起動(外部WSGI) SSL接続; 12.Apache連携起動(ReverseProxy)SSL接続; 13.hprox連携起動(ReverseProxy)SSL接続&HTTP2対応

合わせて読みたいPython MySQL操作関連

タグ: peewee, アップデート, トランザクション, 削除

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

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

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

更新前と更新後を実行

Python3 Bottleフレームワーク入門（その４）- ORM Peewee CRUD

データのアップデート

データの削除

データを全部取得し表示

UserとRoleテーブルを結合し出力

トランザクション処理の例

合わせて読みたいPython MySQL操作関連

Tags

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

人気の記事（総合）

人気(Python/Security)記事