是否可以在 SQLite 查询中使用返回的列值作为表名? [英] Is it possible to use a returned column value as a table name in an SQLite query?

问题描述

我想编写一个查询来检查 SQLite 数据库中的所有表以获取一条信息，以简化我的事件后诊断(性能无关紧要).

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

我希望编写一个使用 sqlite_master 表 来获取列表的查询表，然后查询它们，全部在一个查询中:

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;

然而，当我尝试执行这种查询方式时，我收到一个错误no such table: Name.是否有其他语法允许这样做，或者根本不支持?

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?

推荐答案

SQLite 被设计为嵌入式数据库，即与真正的"编程语言一起使用.为了能够使用这样的动态结构，你必须跳出 SQLite 本身:

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)

这篇关于是否可以在 SQLite 查询中使用返回的列值作为表名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！