title: How to do SQL Server transactions with Python through SQLAlchemy without blocking access to db tables in SQL Server Management Studio?
body: <p>I've been stuck for a while now on this question and wasn't able to find the right answer/topic on the internet.</p>
  <p>Basically, I have a table on SQL Server that I try to 'replace' with an updated one in a pandas dataframe form. I need to do a transaction for this task so that my original table isn't lost if something goes wrong while transferring data from dataframe (<em>rollback</em> functionality). I found a solution for this - SQLAlchemy library.
  My code for this:</p>
  <pre><code>engine = create_engine(&quot;mssql+pyodbc://server_name:password@user/database?driver=SQL+Server+Native+Client+11.0&quot;)
  with engine.begin() as conn:
      df.to_sql(name = 'table_name', schema = 'db_schema', con = conn, if_exists = 'replace', index = False)
  </code></pre>
  <p>The problem occurs when I try to access the tables in this specific database through SQL Server Management Studio 18 while doing this transaction because it somehow manages to block the whole database and no one can access any tables in it (access time limits exceeded). The code above works great, I've tried to transfer a small chunk of dataframe, but the problem still persists, because I need to transfer a large dataframe.</p>
  <p>What I've tried:</p>
  <ol>
  <li><p>The concept of isolation levels, but this isn't the right thing as it's about the rules of connecting to a table that is already being used.
  Example:</p>
  <p><code>engine = create_engine(&quot;mssql+pyodbc://server_name:password@user/database?driver=SQL+Server+Native+Client+11.0&quot;, isolation_level=&quot;SERIALIZABLE&quot;)</code></p>
  </li>
  <li><p>Adjusting such parameters as <strong>pool_size</strong> and <strong>max_overflow</strong> in <em>create_engine()</em> statement and <strong>chunksize</strong> in <em>df.to_sql()</em> statement but they don't seem to have an effect. Example:</p>
  <p><code>engine = create_engine(&quot;mssql+pyodbc://server_name:password@user/database?driver=SQL+Server+Native+Client+11.0&quot;, pool_size = 1, max_overflow = 0)</code></p>
  <p><code>with engine.begin() as conn: df.to_sql(name = 'table_name', schema = 'db_schema', con = conn, if_exists = 'replace', chunksize = 1, index = False)</code></p>
  </li>
  <li><p>Excluding <em>schema</em> parameter from <em>df.to_sql()</em> query doesn't work either</p>
  </li>
  </ol>
  <p>Basic SQL code and functionality I'm trying to achieve for this task would look something like this:</p>
  <pre><code>BEGIN TRANSACTION
     BEGIN TRY
     
           DELETE FROM [db].[schema].[table];
           INSERT INTO [db].[schema].[table] &lt;--- dataframe
           COMMIT TRANSACTION
     
     END TRY
     BEGIN CATCH
  
           ROLLBACK TRANSACTION
           SELECT ERROR_NUMBER() AS [Error_number], ERROR_MESSAGE() AS [Error_description]
  
     END CATCH
  </code></pre>
  <p>I could create another buffer table and parse df data into it and do a transaction of this table afterwards, but I'm looking for a solution to bypass these steps.</p>
  <p>If there is a better way to do this task please let me know as well.</p>