How to parse sql file via python?

Question

I have test.sql file generated from mssql workbench as follows:

     /****** Object:  Database [sample_test]    Script Date: 7/19/2017 3:00:55 PM 
   ******/
    USE [sample_test]
    GO
    ALTER DATABASE [sample_test] SET COMPATIBILITY_LEVEL = 100
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [sample_test].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    ALTER DATABASE [sample_test] SET ANSI_NULL_DEFAULT OFF 
    GO
    ALTER DATABASE [sample_test] SET ANSI_NULLS OFF 
    GO
    ALTER DATABASE [sample_test] SET ANSI_PADDING OFF 
    GO
    ALTER DATABASE [sample_test] SET ANSI_WARNINGS OFF 
    GO
    USE [sample_test]
    GO
    /****** Object:  Schema [sample_test]    Script Date: 7/19/2017 3:00:55 PM ******/
    CREATE SCHEMA [sample_test]
    GO
    /****** Object:  Table [sample_test].[test_items]    Script Date: 7/19/2017 3:00:55 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [sample_test].[test_items](
        [test_detail] [nvarchar](max) NOT NULL,
        [test_id] [int] IDENTITY(1,1) NOT NULL,
        [test_date_time] [datetime2](0) NOT NULL,
     CONSTRAINT [PK_test_items_test_id] PRIMARY KEY CLUSTERED 
    (
        [test_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    /****** Object:  Table [sample_test].[test_history]    Script Date: 7/19/2017 3:00:55 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [sample_test].[test_history](
        [test_history_id] [int] IDENTITY(1,1) NOT NULL,
        [test_flag] [int] NOT NULL,
        [test_date_time] [datetime2](0) NOT NULL,
     CONSTRAINT [PK_test_history_test_history_id] PRIMARY KEY CLUSTERED 
    (
        [test_history_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    INSERT INTO [sample_test].[test_items] ([test_detail], [test_id]) VALUES (0, 'IN')
    INSERT INTO [sample_test].[test_items] ([test_detail], [test_id]) VALUES (0, 'OUT')
    INSERT INTO [sample_test].[test_items] ([test_detail], [test_id]) VALUES (1, 'NONE')

need to execute the above sql script on to some different server and get the tables on that server. For that purpose having a python script as:

   import pymssql
    conn = pymssql.connect(host='xyz', user='abc', password='123', database='sks')
    cursor=conn.cursor()

    with open("test.sql", "r") as inp:    
        for line in inp.read().split("\r"):
            cursor.execute(line)        
    conn.commit()
    conn.close()

But it throws error as its not able to parse and some other statements. Afterwards i executed the sql script as follow:

 import pymssql

    conn = pymssql.connect(host='xyz', user='abc', password='123', database='sks')
    cursor=conn.cursor()

    sql1=""" 
    USE [sample_test]
    /****** Object:  Schema [sample_test]    Script Date: 7/19/2017 3:00:55 PM ******/
    CREATE SCHEMA [sample_test]
    /****** Object:  Table [sample_test].[test_items]    Script Date: 7/19/2017 3:00:55 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    CREATE TABLE [sample_test].[test_items](
        [test_detail] [nvarchar](max) NOT NULL,
        [test_id] [int] IDENTITY(1,1) NOT NULL,
        [test_date_time] [datetime2](0) NOT NULL,
     CONSTRAINT [PK_test_items_test_id] PRIMARY KEY CLUSTERED 
    (
        [test_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    /****** Object:  Table [sample_test].[test_history]    Script Date: 7/19/2017 3:00:55 PM ******/
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    CREATE TABLE [sample_test].[test_history](
        [test_history_id] [int] IDENTITY(1,1) NOT NULL,
        [test_flag] [int] NOT NULL,
        [test_date_time] [datetime2](0) NOT NULL,
     CONSTRAINT [PK_test_history_test_history_id] PRIMARY KEY CLUSTERED 
    (
        [test_history_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    INSERT INTO [sample_test].[test_items] ([test_detail], [test_id]) VALUES (0, 'IN_RETRIVAL')
    INSERT INTO [sample_test].[test_items] ([test_detail], [test_id]) VALUES (0, 'IN_RETRIVAL')
    INSERT INTO [sample_test].[test_items] ([test_detail], [test_id]) VALUES (1, 'RETRIVAL_FAILED')

    """
    cursor.execute(sql1)    
    sql2= """  
    CREATE NONCLUSTERED INDEX [fk_case_id_idx] ON [sample_test].[test_items]
    (
        [case_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    """  
    cursor.execute(sql2)   
    conn.commit()
    conn.close()

Now the above piece works fine and tables along with the foreingnkey and priary key constraint are created. What i want now is to parse the test.sql file in a way that initially the create tables should be executed then the CREATE NONCLUSTERED INDEX shall be executed. So, how shall i parse the test.sql in the manner i execute the script sectionwise.

Answer 1

If permissions is granted for the connected user, consider creating a stored procedure from those SQL commands and then executing the stored procedure:

import pymssql
conn = pymssql.connect(host='xyz', user='abc', password='123', database='sks')
cursor = conn.cursor()

# READ SQL FILE CONTENT INTO STRING
with open("test.sql", "r") as f:
   sqlstr = f.read()   

# DROP STORED PROC IF EXISTS
cur.execute("IF EXISTS (SELECT * FROM sys.objects"  \
            "           WHERE type='P' AND name='myStoredProc')" \
            " DROP PROCEDURE myStoredProc"
conn.commit()

# CREATE STORED PROC (CONCATENATING SQL STRING)
cur.execute("CREATE PROCEDURE myStoredProc AS" + \
            " BEGIN" + \
            " {}".format(sqlstr) + \
            " END")
conn.commit()

# EXECUTE STORED PROC
cur.execute("EXEC myStoredProc")

Answer 2

Why exactly are you trying to parse it in different steps? It could just go in one run.

Anyway, the way you are looking for is to split your script by GO. Then you could run each part indepedently, for example something like this (not tested though):

with open("test.sql", "r") as inp:
    for section in inp.read().split("GO"):
        cursor.execute(section)