I want to loop through columns ['A', 'B', 'C'] and pass the column names to a select statement in SQL, such as
SELECT * FROM table_name
WHERE table_name.`A` IS NULL
Thus each select statement would yield a result set based on the column name. This would be contained in a sql script. I would then read each result through a python DB connection, such as mysql-connector. How would I go about doing this? Thank you!
Edit - I want to keep the solution strictly within SQL.
Maybe you could read all your data from database and generate sql commands. If I got it right you need formated sql commands to fetch the data where individual columns IS NULL.
Lets create two tables to work with :
-- S a m p l e D a t a :
Create Table tbl_1 ( id Int, a Varchar(12), b Varchar(12), c Decimal );
Insert Into tbl_1 VALUES ( 1, 'ABCD', 'EFGH', 3.5 ),
( 2, 'OPQR', Null, 1.2 ),
( 3, Null, 'STUV', 7.1 ),
( 4, 'KLMN', 'WXYZ', Null);
Create Table tbl_2 ( id Int, x Varchar(12), y Varchar(12), z Varchar(12) );
Insert Into tbl_2 VALUES ( 1, 'XX', 'YY', '*' ),
( 2, 'AA', Null, '**' ),
( 3, Null, 'BB', '***'),
( 4, 'CC', 'DD', Null);
... column names in your tables could be fetched FROM INFORMATION_SCHEMA.COLUMNS
WITH
tbl_cols AS
( SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_schema_name'
/* And TABLE_NAME = tbl_1 (if you want just that table) */
ORDER BY TABLE_NAME, ORDINAL_POSITION
),
... create cte to generate sql SELECT command for every column (id column is excluded in this answer)
cmds AS
( Select Row_Number() Over(Order By TABLE_NAME, ORDINAL_POSITION) as id,
TABLE_NAME, COLUMN_NAME,
Concat( 'Select ', TABLE_NAME, '.*',
' From ', TABLE_NAME,
' Where ', TABLE_NAME, '.', COLUMN_NAME, ' Is Null'
) as SQL_CMD
From tbl_cols
Where COLUMN_NAME != 'id' /* filtering columns here */
)
... get the data from above cte
Select *
From cmds;
/* R e s u l t :
id TABLE_NAME COLUMN_NAME SQL_CMD
-- ---------- ----------- --------------------------------------------------
1 tbl_1 a Select tbl_1.* From tbl_1 Where tbl_1.a Is Null
2 tbl_1 b Select tbl_1.* From tbl_1 Where tbl_1.b Is Null
3 tbl_1 c Select tbl_1.* From tbl_1 Where tbl_1.c Is Null
4 tbl_2 x Select tbl_2.* From tbl_2 Where tbl_2.x Is Null
5 tbl_2 y Select tbl_2.* From tbl_2 Where tbl_2.y Is Null
6 tbl_2 z Select tbl_2.* From tbl_2 Where tbl_2.z Is Null */
... above is set of sql commands defined for each table having a column specific sql code ...
Furthermore, if you create another cte that will collect above sql commands and union them for the same table then you could get all the table data that specific sql commands would fetch ...
cmds_union AS
( SELECT TABLE_NAME,
for_columns,
REPLACE(SubStr(SQL_CMD, 1, Length(SQL_CMD) - 11), ',', '') as SQL_CMD
FROM
( Select TABLE_NAME,
GROUP_CONCAT(COLUMN_NAME) as for_columns,
GROUP_CONCAT(SQL_CMD, ' UNION ALL
' ) as SQL_CMD
From cmds
Group By TABLE_NAME
) a
)
Select *
From cmds_union;
/* R e s u l t :
TABLE_NAME for_columns SQL_CMD
---------- ----------- -------------------------------------------------------
tbl_1 a,b,c Select tbl_1.* From tbl_1 Where tbl_1.a Is Null UNION ALL
Select tbl_1.* From tbl_1 Where tbl_1.b Is Null UNION ALL
Select tbl_1.* From tbl_1 Where tbl_1.c Is Null
tbl_2 x,y,z Select tbl_2.* From tbl_2 Where tbl_2.x Is Null UNION ALL
Select tbl_2.* From tbl_2 Where tbl_2.y Is Null UNION ALL
Select tbl_2.* From tbl_2 Where tbl_2.z Is Null */
See the fiddle here.
NOTE:
You can filter tables/columns of interest and/or adjust the code to better fit your needs. This is just a possible option to do it strictly using sql.