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)

sorry, what? you're trying to create a database without a server installed??? otherwise sqlcmd should alerady be installed with the server (although it may not be on your PATH). - - - of course you could also use pyodbc or pymssql to connect to the database and execute the commands from the file. — mata, Apr 30 '12 at 19:17
No. The server is already installed. I just need to connect to the server and execute SQL commands remotely from computers without SQL Server installed. — Neal Kruis, Apr 30 '12 at 19:23
I know I could read the file and execute it's contents as strings using pyodbc, but it seems excessive to read the scripts from the file and into memory instead of having SQL Server read directly from the file. — Neal Kruis, Apr 30 '12 at 19:42
and how would you get the file to the server to "read it directly"? it won't be directly accessible to the server if it's on a remote computer. and you don't need to read the whole file at once, just one command at a time... — mata, Apr 30 '12 at 19:49
SQLCMD has connection arguments to connect to a remote server, that's not the problem (and if it was, I could still make the connection remotely and reference the file stored on the server--the file location isn't what I'm worried about). I think my edit above should help clarify what I'm looking for. — Neal Kruis, Apr 30 '12 at 20:01

Neal Kruis · Accepted Answer · 2012-05-09T16:43:24.590

16

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

edited May 09 '12 at 16:43

answered May 09 '12 at 16:36

Neal Kruis

2,055
3
26
49

cool idea, but it doesn't work for complex scripts with multiple objects. I think SQLCMD will be easier. – FistOfFury Sep 07 '18 at 16:25

score 0 · Answer 2 · answered Apr 30 '12 at 19:26

0

Not sure if this is what you are asking but why not use MS SQL directly from Python? There are libraries like pymssql that will allow you to do that. Or you can use ODBC.

See http://wiki.python.org/moin/SQL%20Server for a list of Python MS SQL drivers

answered Apr 30 '12 at 19:26

Christian V

2,010
17
26

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 Apr 30 '12 at 19:44

score 0 · Answer 3 · answered Apr 30 '12 at 20:17

0

SQLCMD and other management utilities are freely available for download. They are part of what Microsoft calls "Feature pack for SQL Server".

I understand you want to perform large bulk imports. To do this you may want to check out the the BCP utility. Download http://www.microsoft.com/en-us/download/details.aspx?id=16978

Using SQL you may also perform a BULK INSERT. This command can insert data from a file to a SQL db.

Another way is of course to use SQL Server Integration Services for pure ETL jobs.

answered Apr 30 '12 at 20:17

Christian V

2,010
17
26

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. – Neal Kruis Apr 30 '12 at 21:35

Follow up: Execute .sql files from python

3 Answers3

Linked