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.

You should try to make a minimal example of your code. See: stackoverflow.com/help/mcve — Enfenion
– Enfenion, Commented Aug 1, 2017 at 13:01
The challenge here is that TSQL (SQL Server's dialect) does not require semicolon between statements which could have been a convenient delimiter to use for splits. — Parfait
– Parfait, Commented Aug 1, 2017 at 14:03

Adam Tokarski · Accepted Answer · 2017-08-01 13:40:13Z

1

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)

answered Aug 1, 2017 at 13:40

Adam Tokarski

6975 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parfait · Accepted Answer · 2017-08-01 14:24:53Z

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

Collectives™ on Stack Overflow

How to parse sql file via python?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related