Execution: Executing .sql files from python

Question

Over a year ago, someone asked this question: Run the .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 GB) .sql file.

It seems like SQLCMD requires downloading and installing SQL Server Express. Are there other ways to execute the .sql file from python without requiring everyone using my script to download and install SQL Server? Does pyodbc have this feature?

EDIT:

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

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

It seems to me that there should be a way to do this using one of the Python DB API API libraries, but I did not find it, so I am looking for * .exe, for example SQLCMD or MYSQL, which I can use to run the file from the command line.

PS Please feel free to correct me if I do not look at it correctly. Perhaps the code below is as effective as running from the command line:

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

Answer 1

I found that it is actually faster to read the file in python and execute packages using pyodbc than to use the external SQLCMD utility (and I do not need to install SQLCMD on every computer I run scripts on!).

Here is the code I used (since pyodbc does not 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() 

Answer 2

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

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

Answer 3

SQLCMD and other management utilities are available for download. They are part of what Microsoft calls the Feature Pack for SQL Server.

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

With SQL, you can also execute BULK INSERT. This command can insert data from a file into SQL db.

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