CODEWITHSUNDEEP
pythonsqlite
Dmitreeey
Dmitreeey

Reputation: 99

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

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.

Upvotes: 1

Views: 352

Answers (2)

ThePyGuy
ThePyGuy

Reputation: 18426

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.

Upvotes: 1

Ajax1234
Ajax1234

Reputation: 71461

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')'''))

Upvotes: 2

Related Questions