Execute .sql files that are used to run in SQL Management Studio in python

Question

As part of the delivery of artifacts, our developers provide data scripts and structures in .sql files. I usually double-click on these files to open them in Microsoft SQL Server Management Studio. Management Studio will prompt me to enter the database server and user / pwd. I enter them manually and click the Execute button to execute these scripts.

These scripts contain sql structure and data commands. Each script can contain more than one data command (e.g. select, insert, update, etc.). Data structure and scripts are provided in separate .sql files.

These scripts also contain stored procedures and functions, etc. They also contain comments / descriptions.

I want to automate the execution of these scripts through python. I looked at pyobbc and pymssql, but they do not look like a solution to my problem. Through pyodbc, I need to read every .sql file and read the sql commands and execute them one by one. Since files may have comments / description / SPs / etc, reading files will be a bit difficult.

Can someone give a suggestion on how to automate this?

Thanks in advance.

Answer 1

You can simply run them using sqlcmd. Sqlcmd is a command line utility that will allow you to run .sql scripts from the command line, and I'm sure you can start through python.

Answer 2

If the file is not too large for memory, you can parse it with this code and run each statement using pymssql .

It will be executed whenever it finds the string GO or ; at the end of the line.

  _conn = pymssql.connect(** connection settings **) _cur = _conn.cursor() with open(filename, 'r') as f: script = f.read().decode('utf-8') # or whatever its encoding is script = re.sub(r'\/\*.*?\*\/', '', script, flags=re.DOTALL) # remove multiline comment script = re.sub(r'--.*$', '', script, flags=re.MULTILINE) # remove single line comment sql = [] do_execute = False for line in script.split(u'\n'): line = line.strip() if not line: continue elif line.upper() == u'GO': do_execute = True else: sql.append(line) do_execute = line.endswith(u';') if do_execute and filter(None, sql): # ignore if only blank lines cursor.execute(u'\n'.join(sql).encode("cp1252")) # I have experienced problems when executing utf-8 do_execute = False sql = [] _conn.close()