自己投資としてチャレンジしている内容を Blog で公開しています。
今回は pyodbc を使って python で MySQL に接続し、クエリのコミット、ロールバックを行う、トランザクションの実装方法を紹介します。
▼1. MySQL on Ubuntu トランザクションでクエリを制御する方法
本 blog で以前 トランザクションを利用したクエリを以下のように紹介しました。今回 MySQL の接続には pyodbc を使っています。Pyodbc では既定で autocommit が True で、自動でトランザクションの commit が行われます。autocommit を False にし、commit や rollback のメソッドをクエリの後に入れることでトランザクションを制御することができます。
- Java – SQL Server on Linux トランザクションでクエリを制御する方法 No9
- Python – SQL Server on Linux トランザクションでクエリを制御する方法 No.94
- SQL_ATTR_AUTOCOMMIT (ODBC 1.0)
▼2. 事前準備
環境、事前準備などに関しては以下の blog を参考にしています。
Python – pyodbc で MySQL on Ubuntu 20.04 に接続する方法 No.98
▼3. autocommit を False にしトランザクションで insert を実行する Python コード
以下の例ではテーブル sampletb の c1, c2 列に (10,10) の 行をトランザクションを開始後 insert し、実行結果を確認するための select を実行しています。この例では commit せずに insert を完了しています。
3-1. Python コードの作成
autocommit を False にし、insert 後、commit も rollback もせずクエリを完了します。
import pyodbc
driver = '{MYSQL ODBC 8.0 Driver}'
server = 'localhost'
dbname = 'testdb'
user = 'hiveuser'
pw = 'hivepassword'
insertquery="insert into testtbl values(10,10)"
query = "select * from testtbl"
try:
conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+dbname+';UID='+user+';PWD='+pw+';',autocommit=False)
cursor0= conn.cursor()
cursor0.execute(insertquery)
#cursor0.commit()
#cursor0.rollback()
cursor = conn.cursor()
cursor.execute(query)
row = cursor.fetchone()
while row:
print(str(row[0]) + " " + str(row[1]))
row = cursor.fetchone()
except pyodbc.Error as ex:
sqlstate = ex.args[1]
print(sqlstate)
cursor0.close()
cursor.close()
3-2. 実行結果
エラーなく実行した結果が以下です。Select の実行結果が表示されます。
1 1 2 2 3 3 10 10
3-3. ターミナルから MySQL に接続しテーブルを参照
Insert を commit していないため、10 の record は insert されていないことが確認できます。
> select * from testdb.testtbl;
> go
c1 c2
----------- -----------
1 1
2 2
3 3
(3 rows affected)
3-4. commit を実行した場合
以下のように cursor0.commit() のコメントアウトを外して実行すると、commit が行われ insert の結果がデータに反映されます。
import pyodbc
driver = '{MYSQL ODBC 8.0 Driver}'
server = 'localhost'
dbname = 'testdb'
user = 'hiveuser'
pw = 'hivepassword'
insertquery="insert into testtbl values(10,10)"
query = "select * from testtbl"
try:
conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+dbname+';UID='+user+';PWD='+pw+';',autocommit=False)
cursor0= conn.cursor()
cursor0.execute(insertquery)
cursor0.commit()
#cursor0.rollback()
cursor = conn.cursor()
cursor.execute(query)
row = cursor.fetchone()
while row:
print(str(row[0]) + " " + str(row[1]))
row = cursor.fetchone()
except pyodbc.Error as ex:
sqlstate = ex.args[1]
print(sqlstate)
cursor0.close()
cursor.close()
3-5. MySQL に接続しテーブルを参照
Insert を commit たため、10 の record は insert されていることが確認できます。
> select * from testdb.testtbl;
> go
c1 c2
----------- -----------
1 1
2 2
3 3
10 10
(4 rows affected)
▼4. 参考情報
- Java – SQL Server on Linux トランザクションでクエリを制御する方法 No9
- Python – SQL Server on Linux トランザクションでクエリを制御する方法 No.94
- SQL_ATTR_AUTOCOMMIT (ODBC 1.0)
- Python – pyodbc で MySQL on Ubuntu 20.04 に接続する方法 No.98
以上です。参考になりましたら幸いです。