Execute .sql Files That Are Used To Run In Sql Management Studio In Python

As part of artifacts delivery, our developers give the data and structure scripts in .sql files. I usually 'double click' on these files to open in 'Microsoft SQL Server Management

Solution 1:

You could just run them using sqlcmd. Sqlcmd is a command line utility that will let you run .sql scripts from the command line, which I'm sure you can kick off through python.

Solution 2:

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

It will execute whenever it finds a GO line or a ; at the end of the line.

    _conn = pymssql.connect(** connection settings **)
    _cur = _conn.cursor()
    withopen(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 = Falsefor line in script.split(u'\n'):
        line = line.strip()
        ifnot line:
            continueelif line.upper() == u'GO':
            do_execute = Trueelse:
            sql.append(line)
            do_execute = line.endswith(u';')

        if do_execute andfilter(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()