Follow up: Execute .sql files from python

Question

Over a year ago someone asked this question: Execute .sql files that are used to run in SQL Management Studio in python.

I am writing a script in python that connects to a SQL server and creates and populates a database based on SQL commands in a large (several GBs) .sql file.

It looks like SQLCMD requires a download and install of SQL Server Express. Are there other ways to execute a .sql file from python without requiring everyone who uses my script to download and install SQL Server? Does pyodbc have this capability?

EDIT:

Here's another similar question: execute *.sql file with python MySQLdb

Here, again, the solution is to call a utility from command (in this case, mysql.exe) with the file listed as an argument.

It seems to me that there should be a way to do this using one of Python's DB API libraries, but I haven't found it so I'm looking for an *.exe like SQLCMD or MYSQL that I can use to run the file from command line.

P.S. Please feel free to correct me if I'm not looking at this correctly. Maybe the code below is just as efficient as running from command line:

for line in open('query.sql','r'):
    cursor.execute(line)

Solution

I found it's actually faster to read the file in python and execute in batches using pyodbc than it is to use the SQLCMD utility externally (and I don't have to install SQLCMD on every computer I run the scripts on!).

Here is the code I used (because pyodbc doesn't seem to have an executescript() method):

with open(scriptPath, 'r') as inp:
    for line in inp:
        if line == 'GO\n':
            c.execute(sqlQuery)
            sqlQuery = ''
        elif 'PRINT' in line:
            disp = line.split("'")[1]
            print(disp, '\r')
        else:
            sqlQuery = sqlQuery + line
inp.close()

Comments

[Neal Kruis]

I have been using pyodbc, Which works fine for the most part until you have a GB size .sql file that you need to execute. Does pymssql allow you to execute a query from a file?

[Neal Kruis]

Thanks, Christian. Not exactly the answer I wanted to hear, but it does answer my question for now. SQLCMD is working well enough. I suppose I can include the exe's with my scripts to run them on other machines.

[FistOfFury]

cool idea, but it doesn't work for complex scripts with multiple objects. I think SQLCMD will be easier.