How to run a SQL command with python?

Question

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

Answered already - please see stackoverflow.com/questions/33725862/… — benjamin moskovits
– benjamin moskovits, Commented Mar 27, 2017 at 19:24

WillySchu · Accepted Answer · 2017-03-27 21:44:56Z

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()

edited Mar 27, 2017 at 21:44

answered Mar 27, 2017 at 19:26

WillySchu

1661 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ben Over a year ago

Do I need to close this connection or will it close itself?

WillySchu Over a year ago

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.

Collectives™ on Stack Overflow

How to run a SQL command with python?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related