Python SQLite getting names of the tables that contain columns with specific name-CodePudding

There's a database with multiple tables. Is there a way to print out table names that contain columns related to customers, for example: customer_ID?

What I need to do is: There're two tables named "payment" and "customer" that have columns "customer_ID", so names of tables "payment" and "customer" have to be printed.

CodePudding user response：

You can use exists with a subquery:

select m.name from sqlite_master m where m.type = 'table' and exists 
    (select 1 from pragma_table_info(m.name) m1 where m1.name = 'customer_ID')

import sqlite3
conn = sqlite3.connect('test_db.db')
r = list(conn.cursor().execute('''select m.name from sqlite_master m where m.type = 'table' and exists 
       (select 1 from pragma_table_info(m.name) m1 where m1.name = 'customer_ID')'''))

CodePudding user response：

Here is a custom generator function to get the tables that contain at least one of the given column names:

def getTableNames(path, cols):
    con = sqlite3.connect(path)
    for (tableName, ) in con.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall():
        if any(col for col in [fields[1] for fields in con.execute(f"PRAGMA table_info({tableName})").fetchall()] if
               col in cols):
            yield tableName

Then call:

>>> list(getTableNames(path, ['customer_ID']))

The idea is to first get list of the tables, and then to get all the columns for any table that exists in sqlite, then to filter out the tables that contain any of the columns from the given list of the columns.