in our package that we use for connecting to database and perform stuff on it (like read table, insert data, ...) we have a special method that we pass to pandas.to_sql method to insert data to SQLServer using SQLalchemy. The method has a part (the method and the comment was made by person who created that method, and he is not working with me anymore) : # By using a new connection we secure the main connection # from getting invalidated in the case of connection drop. # Otherwise, the transaction for inserting the whole dataframe # gets rolled back once the main connection is invalidated with conn.engine.connect() as new_conn: new_conn.execute(table.table.insert(data)) and after updating the SQLalchemy in our repo to the newest version this part of code was raising errors: TypeError: TableClause.insert() takes 1 positional argument but 2 were given To fix it I had to do rewrite it as: stmt = sqlalchemy.insert(table.table).values(data) with conn.engine.connect() as new_conn: new_conn.execute(stmt) new_conn.commit() and stmt = sqlalchemy.insert(table.table).values(data) with conn.engine.begin() as new_conn: new_conn.execute(stmt) because the insert method from sqlalchemy is no longer part of Table object. But with rewritten with block the script is idling on that block and even lock whole database (so I cannot perform for example SELECT in SQLserver interpreter). It will be locked/"frozen" until I kill the process, and it gets frozen regardless of table size I'm inserting. If instead of that with block with new_conn I do: stmt = sqlalchemy.inserttable.table.values(data) conn.execute(stmt) everything works. The person who has made it with with block and new_conn was more experienced than me, so I suppose him doing it this way had some reasons. I wanted to know if with new SQLalchemy it is still necessary to use new_conn over existing connection in order to prevent corrupting data during data insertion. When inserting data to SQL we use batches and multithreding instead of multi method from pandas Continue reading...
