2

I would like to connect to MS SQL Server and execute a SQL command with python. I am familiar with using SQLAlchemy to create SQL tables, pandas DataFrames, etc., but how can I execute the SQL in a .sql file with python/pandas/SQLAlchemy? Is there a better way to do it?

For example I have the file 'update.sql' that contains the SQL text:

truncate table dev.dbo.jobs
truncate table dev.dbo.workers
go
insert into dev.dbo.jobs select * from test.dbo.jobs
insert into dev.dbo.workers select * from test.dbo.workers
Ben
  • 153
  • 1
  • 2
  • 8

1 Answers1

2

You can use SQLAlchemy's connection.execute to run raw SQL queries. If you have the sql statements stored in a file then it might look something like this:

from sqlalchemy import create_engine
from sqlalchemy.sql import text

engine = create_engine('urltodb')
conn = engine.connect()

with open('file.sql', 'r') as f:
    for l in f:
        stmt = text(l)
        conn.execute(stmt)

conn.close()
WillySchu
  • 166
  • 8
  • Do I need to close this connection or will it close itself? – Ben Mar 27 '17 at 20:30
  • If you have finished using the connection, it would be best to close it in order to release it to the pool. I'll update the example. – WillySchu Mar 27 '17 at 21:43