CODEWITHSUNDEEP
sqlsqlite
dlanod
dlanod

Reputation: 8980

Is it possible to use a returned column value as a table name in an SQLite query?

I want to write a query that examines all the tables in an SQLite database for a piece of information in order to simplify my post-incident diagnostics (performance doesn't matter).

I was hoping to write a query that uses the sqlite_master table to get a list of tables and then query them, all in one query:

SELECT Name 
FROM sqlite_master
WHERE Type = 'table' AND (
    SELECT count(*)
    FROM Name
    WHERE conditions
    ) > 0;

However when attempting to execute this style of query, I receive an error no such table: Name. Is there an alternate syntax that allows this, or is it simply not supported?

Upvotes: 2

Views: 1032

Answers (1)

CL.
CL.

Reputation: 180070

SQLite is designed as an embedded database, i.e., to be used together with a 'real' programming language. To be able to use such dynamic constructs, you must go outside of SQLite itself:

cursor.execute("SELECT name FROM sqlite_master")
rows = cursor.fetchall()
for row in rows:
    sql = "SELECT ... FROM {} WHERE ...".format(row[0])
    cursor.execute(sql)

Upvotes: 2

Related Questions