1

I am using pyodbc to connect to a database on my local machine. I have two .ipynb files that are responsible for populating two of the relations in this database respectively.

There are two observations I have made that are troubling:

  1. If one a cursor is connected to my database in one file, and another cursor is connected to the database in another file, only one cursor is able to perform DML and retrieve statements from the database.

  2. If one cursor in one file inserts tuples into a relation and then is disconnected from the database, and then the cursor in the other file attempts to retrieve the information from the relation that was updated by the first cursor, no information is retrieved. This is the same for performing a select statement in SQL Server.

Here is a generalization of what is going on in each file:

File1.ipynb:

    cursor.connect(...)
    cursor = conn.cursor()
    # big array of commands
    commands = ["insert into ... values ...", "insert into ... values ..."]

    for line in commands:
        cursor.execute(line)

Printing out the contents of the cursor shows me that the statement was executed successfully.

Now in file2.ipynb:

    cursor.connect(...)
    cursor = conn.cursor()

    cursor.execute("select * from updatedTable") # this line hangs
    for row in cursor:
        print(row)        # if cursor is disconnected from file1, still nothing is printed

Any help resolving multiple connections and confirming the persistence of data would be greatly appreciated.

Update: Have to perform cnxn.commit() in order to preserve changes.

omri
  • 352
  • 2
  • 18

1 Answers1

2

I wasn't aware of the command cnxn.commit(), after using this I have achieved the intended behavior.

omri
  • 352
  • 2
  • 18
  • Update your question with the answer,. Also note that you'll get better insert performance with fast_executemany, and commit in batches. You should confirm your autocommit behavior and be sure to handle any errors from the batch crsr.fast_executemany = True crsr.executemany(sql, params) – jCisco Nov 07 '19 at 06:58