Python -Run a transactional query SQL Server on Ubuntu No.94

How to execute commit or rollback for a transactional query using python and pyodbc in SQL Server on Ubuntu is shown in this blog.

Contents

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

▼1. Run a transactional query in SQL Server on Ubuntu

Regarding Java, how to execute a transactional query was shown in this past blog as below. this blog is written to implement it by Python. the autocommit of pyodbc is true. it is meant to commit a transaction automatically by default.

▼2. Creating Python code to insert data with False of “autocommit”

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.connect.

2-1. Creating Python code

import pyodbc
import time
import datetime

server = 'localhost'
database = 'testdb'
username = 'sa'
password = 'yourpassword'
driver= '{ODBC Driver 17 for SQL Server}'
success = False
retryc = 0
max_retries = 3

while not success and retryc < max_retries:
    try:
        cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+',1433;DATABASE='+database+';UID='+username+';PWD='+ password+';',autocommit=False,Timeout=1,Trusted_Connection='no',Encrypt='no')
        cursor0 = cnxn.cursor()
        cursor0.execute("insert into sampledb.dbo.sampletb values (10, 10)")
        #cursor0.commit()
        #cursor0.rollback()

        cursor = cnxn.cursor()
        cursor.execute("SELECT * FROM sampledb.dbo.sampletb with(Serializable)")
        row = cursor.fetchone()
        
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()
            
        success = True
    except pyodbc.Error as ex:
        retryc += 1
        print("Current Timestamps:" + str(datetime.datetime.now()) + "retry count: " + str(retryc))
        print("==========================")
        print("Error: " + str(ex))
        print("==========================")
        time.sleep(3)

    finally:
        cursor.close()

2-2. Results of this code

The result of select query is below.

2-3. Confirming data in table after connecting to SQL Server by sqlcmd utility

We cannot confirm value (10,10) in the table since we did not implement commit.

> select * from sampledb.dbo.sampletb;
> go
c1          c2         
----------- -----------
          1           1
          2           2
          3           3

(3 rows affected)

▼3. Reference

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