Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – For loop through specific columns of table and pass to relational SQL statement

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.

Tags:

2

Answers


  1. 0

    Construct the query dynamically in Python in a loop.

    cols = ['A', 'B', 'C']

for col in cols:
    sql = f'SELECT * FROM table_name WHERE `{col}` IS NULL'
    cursor.execute(sql)
    # process the results of the query
    Login or Signup to reply.
  2. 0

    Taken sqlite example. But queries will be similar for other sequential database like mysql or mssql.
    The following will execute queries for each column being null. And for each query, it’ll create a 2d list and store as the value of the dict. The key names of the dict will be the respective column names. You can process the data from the dict

    import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

dct = {}

columns = ['A', 'B', 'C']

for column in columns:

    qry = f'SELECT * FROM table_name WHERE {column} IS NULL'

    cursor.execute(qry)

    # fetch the results
    r = cursor.fetchall()
    
    # create a 2d list of each colum
    dct[column] = [list(row) for row in r]

conn.close()

    For mysql, you can use mysql.connector module and for MSSQL you can use pyodbc module for connection.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search