1

Having learned SQL before learning any Python, I have a fairly lengthy program/query that I wrote in SQL server which heavily transforms and calculates the data (Basically taking forecast, inventory, and Bill of Materials, and efficiency data and then automatically generating a production plan. While I am sure there are things I could optimize, the query/program itself is aorund 3,000 lines).

While I have figured out how to update the data in SQL Server using a combination of pandas, pyodbc, and fast_to_sql, I have not been able to find a simple method for running a SQL Server script through Python.

I am sure that I could achieve the same thing by just having the data manipulation occur in python rather than SQL Server, it would be fairly time intensive to translate everything.

If there is anything I can do to clarify please let me know. For reference I am using the 2017 version of Microsoft SQL Server python version 3.8.3.

OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
kanderson
  • 178
  • 2
  • 13
  • If you can run a plain query as a string, you could read the entire file to a string to execute – OneCricketeer Oct 26 '20 at 15:39
  • Does this answer your question? [Follow up: Execute .sql files from python](https://stackoverflow.com/questions/10387892/follow-up-execute-sql-files-from-python) – SMor Oct 26 '20 at 15:44
  • Have you had a look at the documentation [Python SQL Driver](https://learn.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server?view=sql-server-ver15)? – Thom A Oct 26 '20 at 15:44

1 Answers1

1

Try to combine all of your MSSQL scripts into Stored Procedures and then call it from Python.

Tushar Koley
  • 164
  • 3
  • Thanks for the advice on this. This seemed to mostly solve the problem, but I am running into a little bit of an issue where it seems like the connection closes out too early (the actual query can take up to 7 seconds or so to run). Currently I basically am just executing like this: cursor.execute("{CALL storedprocedure}"), then sql_conn.commit(), sql_conn.close(). I am not sure what would be the best way to have it only close the connection once the procedure is finished, any ideas? – kanderson Oct 26 '20 at 17:32
  • Sorry, answered my own question. Just adding "SET NOCOUNT ON" to the SQL Script seemed to do the trick for others experiencing the same problem. – kanderson Oct 26 '20 at 17:37