pypyodbc does not commit

Question

I have a SQLServer TSQL query that has multiple INSERT statements that range from pretty basic to somewhat complex.

This query works in SQLServer Management Studio.

When I use Python pypyodbc package and run the script, the script runs but does not commit. I have tried with and without the commit() function.

BUT if I specify a SELECT statement at the end, the script commits the inserts.

So it's all good because it works, but I am putting an inapplicable SELECT statement at the end of all of my scripts.

Does anyone have any ideas how I can get these to commit without the SELECT statement at the end? I do not want to split the queries up into multiple queries.

Thank you!

    def execute_query(self,
                  query,
                  tuple_of_query_parameters,
                  commit=False,
                  return_insert_id=False,
                  return_results=True):
    self.open_cursor()

    try:
        self.connection_cursor.execute(query,
                                       tuple_of_query_parameters)

        result_set = None
        if return_results:
            if return_insert_id:
                result_set = self.connection_cursor.fetchone()[0]
            else:
                result_set = self.connection_cursor.fetchall()

            if commit:
                self.connection_cursor.commit()

    except pypyodbc.Error as e:
        print('Check for "USE" in script!!!')
        raise
    finally:
        self.close_cursor()

    return result_set

Please edit your question to include an [mvce](https://stackoverflow.com/help/mcve). — ruohola, Sep 24 '18 at 13:01
Toss in your Python code (MVCE please). From what you wrote having `commit` should do the trick, it's probably just a small syntax problem. — sniperd, Sep 24 '18 at 13:03
@sniperd you may be right... I might have buried the commit function. Please see the execution code I am using. I will test it and report back. Thanks. — DonkeyKong, Sep 24 '18 at 13:07

score 2 · Accepted Answer · answered Sep 24 '18 at 13:10

2

Try this:

self.connection_cursor.execute(query,
                                   tuple_of_query_parameters)
if commit:
    self.connection_cursor.commit() #put commit here, immediately after execute

I think that will do the trick.

answered Sep 24 '18 at 13:10

sniperd

5,124
6
28
44

Thank you for correcting the placement of my code. This definitely should be located directly below the query. – DonkeyKong Sep 24 '18 at 13:21
OK. So I have a very important update on this... the placement BELOW the fetchone() and fetchall() is CORRECT! I have spent the last hour trying to retreive my @@IDENTITY value and was unable to do that because of the commit being placed before the fetch() – DonkeyKong Sep 24 '18 at 21:51
1

@DonkeyKong You probably want to use `SCOPE_IDENTITY()` and not `@@IDENTITY` If you simply want the `PK` of what you just inserted, otherwise you _might_ get something else: https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide – sniperd Sep 25 '18 at 02:07
you are 2 for 2! – DonkeyKong Sep 25 '18 at 11:42
However, the placement must be below the results. The order of events in my original code is correct. – DonkeyKong Sep 25 '18 at 11:48

pypyodbc does not commit

1 Answers1