pymssql and transactions

[Rob]

Hi

Can some one explain to me why executing "COMMIT TRANSACTION" in pymssql does not commit the transaction?

Here is an example that does not insert the row of data.

import sys

import pymssql

import logging

import logging.handlers

logger = logging.getLogger("DataBase")

logger.setLevel(logging.INFO)

file_log = logging.handlers.RotatingFileHandler('./example.log', maxBytes=10485760, backupCount=5)

file_log.setLevel(logging.DEBUG)

console_log = logging.StreamHandler()

console_log.setLevel(logging.DEBUG)

formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')

file_log.setFormatter(formatter)

console_log.setFormatter(formatter)

logger.addHandler(file_log)

logger.addHandler(console_log)

class DataBase:

def __init__(self, server, db, usr, pwd):

self.logger = logging.getLogger("DataBase")

try:

self.con = pymssql.connect(host=server, user=usr, password=pwd, database=db)

self.set_autocommit_mode(False)  

self.cursor = self.con.cursor()

self.cursor.execute("SELECT @@SERVERNAME")

self.logger.info("Server: %s" % self.cursor.fetchone()[0])

self.cursor.execute("SELECT DB_NAME()")

self.db_name = self.cursor.fetchone()[0]

self.logger.info("Database: %s" % self.db_name)

self.logger.info("Autocommit: %s " % self.get_autocommit_mode())

self.cursor.execute("SELECT @@VERSION")

self.logger.info(self.cursor.fetchone()[0])

except pymssql.Error as dbe:

try:

self.logger.error(" Error [%d]: %s" % (dbe.args[0], dbe.args[1]))

except IndexError:

self.logger.error(" Error: %s" % str(dbe))

sys.exit(1)

def get_autocommit_mode(self):

return self.con.autocommit_state

def set_autocommit_mode(self, mode):

self.con.autocommit(mode)

def start_transaction(self):

self.logger.info(" Starting Transaction")

self.execute("BEGIN TRANSACTION")

def commit_transaction(self):

self.logger.info(" Committing Transaction")

self.execute("COMMIT TRANSACTION")

def execute(self, sql):

"""Runs an sql query.  Returns None on error."""

try:

self.logger.info(" Executing SQL - %s" % sql)

self.cursor.execute(sql)

except pymssql.Error as dbe:

try:

self.logger.error(" Error [%d]: %s" % (dbe.args[0], dbe.args[1]))

except IndexError:

self.logger.error(" Error: %s" % str(dbe))

raise

return None

self.logger.info(' Rows affected - %d' % (self.cursor.rowcount))

return self.cursor.rowcount

def close(self):

self.logger.info(" Closing database connections")

try:

if self.cursor:

self.cursor.close()

if self.con:

self.con.close()

except pymssql.Error as dbe:

try:

self.logger.error("Error [%d]: %s" % (dbe.args[0], dbe.args[1]))

except IndexError:

self.logger.error("Error: %s" % str(dbe))

if __name__ == "__main__":

# Test Database Class

db = DataBase(r"localhost", r"dbname", r"user", r"password")

db.start_transaction()

db.execute("INSERT INTO some_table (name, data1, data2) VALUES ('someone', 'some data', 'more data')")

db.commit_transaction()

db.close()

sys.exit()

[Randy Syring]

Rob,

Can you tell me why you are messing with the auto commit settings?  What you want to do is already supported by pymssql without any additional configuration.  By default, pymssql will already be in a transaction, you do not need to start one (that is autocommit mode = False).

So, as the test indicates:

https://github.com/pymssql/pymssql/blob/f814fd9dedc7cd87fabbe92dfb1b6d19a5ed31a3/tests/test_pymssql.py#L43

All you really need is something like:

con = pymssql.connect(host=server, user=usr, password=pwd, database=db)

cursor = con.cursor()
cursor.execute("INSERT INTO some_table (name, data1, data2) VALUES ('someone', 'some data', 'more data')")
con.commit()

If the above doesn't result in data in your DB, then something else has gone wrong, maybe you aren't connect to the DB you think you are, etc.  Per the test in the link above, and per the DBAPI2 specification which pymssql adheres to (I gave you a link to that in one of the GH issues), that is how you use transactions.

If you are looking for more sophisticated library support, you may want to check out SQLAlchemy.



Randy Syring
Husband | Father | Redeemed Sinner

"For what does it profit a man to gain the whole world
and forfeit his soul?" (Mark 8:36 ESV)

--
You received this message because you are subscribed to the Google Groups "pymssql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pymssql+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rob]

So, "COMMIT TRANSACTION" is ignored?  I know understand what was failing.

When I execute "BEGIN TRANSACTION" then do some sql, and swapped out my commit code to use con.commit() it failed because there was no "COMMIT TRANSACTION" to match the "BEGIN TRANSACTION",  If I do both it works.  If I skip the "BEGIN TRANSACTION" and use con.commit() it works.  

I just assumed it would respect the SQL thrown at it and commit the transaction when I asked it to.

[Alan]

hi Rob,

I know this topic too late but I have taken the same problem.

I also put self.con.commit() but it's not worked.

How did you do to solve your problem?

My code as below:

class Connection():

    def __init__(self, database = db_config.DEFAULT_DATABASE):

      

        self.Connect = pymssql.connect(server = db_config.DATABSE_SERVER_NAME, 

                                    user = db_config.DATABASE_USERNAME, 

                                    password = db_config.DATABASE_PASSWORD, 

                                    database = database)            

       self.Cursor = self.Connect.cursor()

    def begin_transaction(self):

        self.Cursor.execute("BEGIN TRANSACTION")

       

    def commit(self):

        self.Connect.commit()

    def execute_stored_procedure(self, stored_name, parameters):

       self.Cursor.callproc(stored_name, parameters)

       

class ComponentRepository():

    def __init__(self, connection: Connection):

        self.Connect = connection

def insert(self, entity):                                

        cp_procedure_name = "PROC_ADD_OR_UPDATE_COMPONENT"

        cp_parameters = (pymssql.output(int, 0),

                         entity.Code, 

                         entity.Name, 

                         entity.IsActive,  

                         entity.Username)

        self.Connect.execute_stored_procedure(cp_procedure_name, cp_parameters)

class ComponentService():

    

    def __init__(self):

        self.conn = Connection()

        self.componentRepository = ComponentRepository(self.conn)

    

    def add_component(self, component):   

        self.conn.begin_transaction()

        self.componentRepository.insert(component)

        self.conn.commit()