Python and Dynamic SQL

[ns309]

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.

[ns309]

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.

[ns309]

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

[ns309]

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

[ns309]

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