1

I'm using to_sql to export my dataframe into a SQL Server table however I want to delay the execution of the next line of python code until the insertion has been completed.

I have a OKish understanding of database connections but I'm a bit of a noob when trying to understand how python and sql server communicate.

engine = db.create_engine('mssql+pyodbc://username:password@ServerName/Database?driver=SQL+Server+Native+Client+11.0')

df.to_sql('Table', engine, schema = 'dbo', if_exists='append',index=False)

#Don't execute this until all the records have been added
print("All my records have been added to SQL")

Any help would be greatly appreciated.

Thanks

Big_Daz
  • 141
  • 1
  • 7
  • Python executes line by line. So it should not move on untill the `df.to_sql` line has finished. – Erfan May 28 '19 at 13:40
  • It immediately prints the message as soon as to_sql has been executed even though the insert hasn't finished. I have tested this by triggering a SQL query immediately afterwards to capture the inserted records which returns nothing – Big_Daz May 28 '19 at 13:46
  • I write to an ms sql daily, it definitely executes line by line. Are you sure you're connected to the right dB? – Umar.H May 29 '19 at 00:42

1 Answers1

0

Finally got it working as wasn't using autocommit = true

Big_Daz
  • 141
  • 1
  • 7
  • hi, I need to do something similar, do you mind showing where you have to put in autocommit? thanks @Big_Daz – gdubs Feb 07 '20 at 03:27
  • Hi, luckily I save all my code for future use so this is how I solved it: #Start of the process to export the result back into SQL import sqlalchemy as db from sqlalchemy import event #SQL Connection string engine = db.create_engine('mssql+pyodbc://**USERNAME**:**PASSWORD**@**SERVER NAME/**DATABSE**?driver=SQL+Server+Native+Client+11.0') – Big_Daz Feb 09 '20 at 11:45
  • #Export the data to the table finaldf.to_sql('**TABLE NAME**', engine, schema = 'dbo', if_exists='append',index=False) – Big_Daz Feb 09 '20 at 11:47
  • so no need for the autocommit then? ill try this out thanks! – gdubs Feb 10 '20 at 09:05