Python and Dynamic SQL

Python
1

I am very new to python.

I have created a stored procedure in SSMS where I loop through the tables names and run select statements on each of those tables and insert the results of those queries into a table.

I would like to achieve the same result using python.

Fo eaxmple I have a table in ssms with tablename and schema name
tablename - employees, sales, fruits, admin, products
schemaname - dbo.test2023, dbo.test2023, dbo.test2023,dbo.test2023,dbo.test2023

I would like to create a python script where I connect to SSMS and loop through the above tablename and run the below sql statements on each of the table
select count(id) from tablename;
select count(id) from tablename where apple = 1;
select count(id) from tablename where apple = 0;

And insert the results of the above queries into another table

I don’t know if this can be achieved in python.

Please can anybody help me with the code for the above.

Many thanks for your help.

2

Are all of the tables in the same database? If so, then why not have the stored procedure select from all of the tables and union them together and insert into the other table? You would not need Python at all.

3

Yes, all the tables are in the same database. I have got the stored procedure working. But I want to learn python and was hoping that there is way to do this in python.

4

Post a copy of your working stored procedure and we can possibly guide you on a way to implement it in Python.

5

I am not able to post the code as it keep getting error message only two users can be mentioned!

6

An error occurred: Sorry, new users can only mention 2 users in a post.

7 
USE [test_dbepictype_WIP]
GO

SET ANSI_NULLS ON
GO

IF OBJECT_ID ( 'sp_CheckMissingRows', 'P' ) IS NOT NULL   
    DROP PROCEDURE [dbo].[sp_CheckMissingRows];  
GO

-- Add the parameters for the stored procedure here
CREATE PROCEDURE [dbo].[sp_CheckMissingRows]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--decalre scalar variables
‘’’
DECLARE @I INT,
            @RowCount INT,
            @SQL VARCHAR(MAX),
            @Table VARCHAR(100)
--declare table variable to store list of tables to loop through
        DECLARE @T TABLE
            (
              [ID] [int] IDENTITY(1, 1),
              [TableName] [varchar](100),
 [SchemaName] [Varchar](100)
            )

        INSERT  INTO @T ( TableName, SchemaName )
                SELECT  TableName, SchemaName
                FROM    [test_10012023].[dbo].[tbl_reference1]
      
 --count how many rows in the table variable
         SET @RowCount = ( SELECT COUNT(*) FROM @T)   
         SET @I = 1

--loop though the rows in the table variable 
--and build a sql statement using the table details for each row
WHILE ( @I <= @RowCount )
            BEGIN
--fetch the table name and schema name for the current row into variables
SET @Table = ( SELECT   SchemaName+'.'+TableName
  FROM     @T
  WHERE    ID = @i
 )

PRINT 'Starting ' + @Table

set @sql = 'Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) SELECT  ''' + @Table +''' as Tablename, ''Count_of_id''as variable_name, count(distinct(id)) as Result FROM ' + @Table +';
Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) select ''' + @Table +''' as Tablename, ''Count_of_id_realrow1'' as variable_name,count(id) as Result FROM ' + @Table +' where RealRow = 1;
Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) select ''' + @Table +''' as Tablename, ''Count_of_id_realrow0'' as variable_name,count(serno) as Result FROM ' + @Table +' where RealRow = 0;
Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) select ''' + @Table +''' as Tablename,''duplicate_id'' as variable_name, id as Result FROM ' + @Table +' WHERE NOT EXISTS 
(
SELECT [id] FROM [test_10012023].[dbo].[tbltable1]
);
Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) select ''' + @Table +''' as Tablename, ''Count_of_id_missing'' as Variable_name, count(A.id) as Result FROM ' + @Table +' A
LEFT JOIN [test_10012023].[dbo].[tbltable1] B
ON A.id = B.id
WHERE B.id IS NULL;
Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) select ''' + @Table +''' as Tablename, ''Count_of_id_missing_realrow0''as Variable_name,count(A.id) as Result FROM ' + @Table +' A
LEFT JOIN [test_10012023].[dbo].[tbltable1] B
ON A.id = B.id
where B.id is not null
and A.RealRow = 0;'
print @SQL
EXEC (@SQL)
SET @I = @I+1
‘’’
END
END
GO
EXEC [dbo].[sp_CheckMissingRows] 
GO