Reputation: 33
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.
Upvotes: 0
Views: 3767
Reputation: 107767
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")
Upvotes: 0
Reputation: 697
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)
Upvotes: 1