Python -Run a transactional query in MySQL on Ubuntu No.103

How run a transactional query in MySQL on Ubuntu using Python is shown here.

Contents

▼1. Run a transactional query in MySQL on Ubuntu
▼2. Prerequisites
▼3. Creating Python code to insert data with False of “autocommit” in MySQL on Ubuntu
▼4. Reference

▼1. Run a transactional query in MySQL on Ubuntu

Regarding SQL Server on Ubuntu, how to execute a transactional query using Java and Python were shown in the past. regarding MySQL on Ubuntu, pyodbc is used for the connection to MySQL. autocommit setting in pyodbc is true by default to commit a transaction automatically. if autocommit is false, commit or rollback method is needed to control transaction.

▼2. Prerequisites

How to create an environment to run python code for MySQL is shown in this blog.

Python – Connect to MySQL on Ubuntu 20.04 by pyodbc No.98

▼3. Creating Python code to insert data with False of “autocommit” in MySQL on Ubuntu

Table “sampletb” has columns “c1” and “c2”. the value (10,10) is inserted using transaction and then select is executed to get data. after that, this insert query end without commit. autocommit is falise in pyodbc.connection.

3-1. Creating Python code

Autocommit is False and an insert query is executed without commit or 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. Results

Results of select are below.

3-3. Confirming results of select query by connecting to MySQL from a terminal

Since the insert query is executed without commit, the value of record 10 is not inserted.

> select * from testdb.testtbl;
> go
c1          c2         
----------- -----------
          1           1
          2           2
          3           3

(3 rows affected)

3-4. Results when commit is executed

When removing comment-out cursor0.commit, commit is executed.

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. Confirming results after connecting to MySQL from a terminal.

Since commit is executed, the value of record 10 is inserted.

> select * from testdb.testtbl;
> go
c1          c2         
----------- -----------
          1           1
          2           2
          3           3
          10         10

(4 rows affected)

▼4. Reference

That’s all. Have a nice day ahead !!!