blocks|key|857312|text|所有表都收集在SQLAlchemy+MetaData对象的tables属性中。要获取这些表的名称列表，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|857313|>>>+metadata.tables.keys()
['posts',+'comments',+'users']|code-block|syntax|javascript|857314|如果您使用的是声明性扩展，那么您可能不会自己管理元数据。幸运的是，元数据仍然存在于基类中，|857315|>>>+Base+=+sqlalchemy.ext.declarative.declarative_base()
>>>+Base.metadata
MetaData(None)|857316|如果您正在尝试找出数据库中存在哪些表，甚至是那些您甚至还没有告诉SQLAlchemy的表，那么您可以使用表反射。然后，SQLAlchemy将检查数据库并使用所有缺少的表更新元数据。|857317|>>>+metadata.reflect(engine)|857318|对于Postgres，如果您有多个模式，则需要遍历引擎中的所有模式：|857319|from+sqlalchemy+import+inspect
inspector+=+inspect(engine)
schemas+=+inspector.get_schema_names()

for+schema+in+schemas:
++++print("schema:+%25s"+%25+schema)
++++for+table_name+in+inspector.get_table_names(schema=schema):
++++++++for+column+in+inspector.get_columns(table_name,+schema=schema):
++++++++++++print("Column:+%25s"+%25+column)|857320|entityMap^0|T|6|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|14|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|16|8|@]|D|@]|E|$]]|$1|U|3|V|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|W|3|-4|5|6|7|18|8|@]|D|@]|E|$]]]|X|$]]

All of the tables are collected in the <code>tables</code> attribute of the SQLAlchemy MetaData object. To get a list of the names of those tables:

<pre><code>&gt;&gt;&gt; metadata.tables.keys()
['posts', 'comments', 'users']
</code></pre>

If you're using the declarative extension, then you probably aren't managing the metadata yourself. Fortunately, the metadata is still present on the baseclass, 

<pre><code>&gt;&gt;&gt; Base = sqlalchemy.ext.declarative.declarative_base()
&gt;&gt;&gt; Base.metadata
MetaData(None)
</code></pre>

If you are trying to figure out what tables are present in your database, even among the ones you haven't even told SQLAlchemy about yet, then you can use table reflection. SQLAlchemy will then inspect the database and update the metadata with all of the missing tables.

<pre><code>&gt;&gt;&gt; metadata.reflect(engine)
</code></pre>

For Postgres, if you have multiple schemas, you'll need to loop thru all the schemas in the engine:

<pre><code>from sqlalchemy import inspect
inspector = inspect(engine)
schemas = inspector.get_schema_names()

for schema in schemas:
 print("schema: %s" % schema)
 for table_name in inspector.get_table_names(schema=schema):
 for column in inspector.get_columns(table_name, schema=schema):
 print("Column: %s" % column)
</code></pre>

blocks|key|857346|text|用于创建表的元数据对象在字典中具有该对象。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|857347|metadata.tables.keys()|code-block|syntax|javascript|857348|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The metadata object that you created the tables with has that in a dictionary.

<pre><code>metadata.tables.keys()
</code></pre>

blocks|key|857378|text|在engine对象中有一个方法可以获取表名列表。engine.table_names()|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|857379|entityMap^0|1|6|O|K|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

There is a method in <code>engine</code> object to fetch the list of tables name. <code>engine.table_names()</code>

blocks|key|640044|text|同时反映所有的表使你也可以检索隐藏的表名。我创建了一些临时表，它们显示为|type|unstyled|depth|inlineStyleRanges|entityRanges|data|640045|meta+=+MetaData()
meta.reflect(bind=myengine)
for+table+in+reversed(meta.sorted_tables):
++++print+table|code-block|syntax|javascript|640046|参考http://docs.sqlalchemy.org/en/latest/core/reflection.html|offset|length|640047|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.sqlalchemy.org/en/latest/core/reflection.html^0|0|0|2|1L|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Reflecting All Tables at Once allows you to retrieve hidden table names too. I created some temporary tables and they showed up with 

<pre><code>meta = MetaData()
meta.reflect(bind=myengine)
for table in reversed(meta.sorted_tables):
 print table
</code></pre>

Reference <a href="http://docs.sqlalchemy.org/en/latest/core/reflection.html" rel="nofollow noreferrer">http://docs.sqlalchemy.org/en/latest/core/reflection.html</a>

blocks|key|857425|text|from+sqlalchemy+import+create_engine
engine+=+create_engine('postgresql://use:pass@localhost/DBname')
print+(engine.table_names())|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|857426|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>from sqlalchemy import create_engine
engine = create_engine('postgresql://use:pass@localhost/DBname')
print (engine.table_names())
</code></pre>

blocks|key|640121|text|我正在解决同样的问题，并找到了这篇文章。经过一些尝试运行后，我建议使用下面列出所有的表：(由zerocog提到)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|640122|metadata+=+MetaData()
metadata.reflect(bind=engine)
for+table+in+metadata.sorted_tables:
++++print(table)|code-block|syntax|javascript|640123|这对于直接处理表格很有用，我觉得这是推荐的。|640124|并使用以下代码获取表名：|640125|for+table_name+in+engine.table_names():
++++print(table_name)|640126|Metadata.tables提供表名和表对象的字典。这对于快速查询也是有用的。|640127|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

I'm solving same problem and found this post. After some try run, I would suggest use below to list all tables: (mentioned by zerocog)

<pre><code>metadata = MetaData()
metadata.reflect(bind=engine)
for table in metadata.sorted_tables:
 print(table)
</code></pre>

This is useful for direct table handling and I feel is recommended.

And use below code to get table names:

<pre><code>for table_name in engine.table_names():
 print(table_name)
</code></pre>

"metadata.tables" provides a Dict for table name and Table object. which would also be useful for quick query.

blocks|key|857552|text|在python解释器中使用db.engine.table_names()|type|unstyled|depth|inlineStyleRanges|entityRanges|data|857553|$+python
>>>+from+myapp+import+db
>>>+db.engine.table_names()|code-block|syntax|javascript|857554|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Within the python interpreter use db.engine.table_names()

<pre><code>$ python
&gt;&gt;&gt; from myapp import db
&gt;&gt;&gt; db.engine.table_names()
</code></pre>

blocks|key|640227|text|就是这么简单：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|640228|engine.table_names()|code-block|syntax|javascript|640229|另外，要测试表是否存在：|640230|engine.has_table(table_name)|640231|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Just this simple:

<pre class="lang-py prettyprint-override"><code>engine.table_names()
</code></pre>

Also, to test whether a table exists:

<pre class="lang-py prettyprint-override"><code>engine.has_table(table_name)
</code></pre>

blocks|key|857673|text|显示所有列信息的完整示例。假设变量df包含要写入SQL数据库的数据帧。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|857674|from+sqlalchemy+import+create_engine,+inspect
from+sqlalchemy_utils.functions+import+database_exists,+create_database

engine+=+create_engine('sqlite:///mydb.sqlite',+echo=True)

if+not+database_exists(engine.url):
++++create_database(engine.url)
else:
++++engine.connect()

df.to_sql('MyTable',+con=engine,+if_exists='replace',+index=False)+#+index=False+avoids+auto-creation+of+level_0+(name+tiebreaker)

inspector+=+inspect(engine)
table_names+=+inspector.get_table_names()
for+table_name+in+table_names:
++++print(f"Table:{table_name}")
++++column_items+=+inspector.get_columns(table_name)
++++print('\t'.join(n+for+n+in+column_items[0]))
++++for+c+in+column_items:
++++++++assert+len(c)+==+len(column_items[0])
++++++++print('\t'.join(str(c[n])+for+n+in+c))|code-block|syntax|javascript|857675|entityMap^0|H|2|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Complete example of displaying all column information. Assumes variable <code>df</code> contains a dataframe to be written to the SQL database.
<pre><code>from sqlalchemy import create_engine, inspect
from sqlalchemy_utils.functions import database_exists, create_database

engine = create_engine('sqlite:///mydb.sqlite', echo=True)

if not database_exists(engine.url):
 create_database(engine.url)
else:
 engine.connect()

df.to_sql('MyTable', con=engine, if_exists='replace', index=False) # index=False avoids auto-creation of level_0 (name tiebreaker)

inspector = inspect(engine)
table_names = inspector.get_table_names()
for table_name in table_names:
 print(f&quot;Table:{table_name}&quot;)
 column_items = inspector.get_columns(table_name)
 print('\t'.join(n for n in column_items[0]))
 for c in column_items:
 assert len(c) == len(column_items[0])
 print('\t'.join(str(c[n]) for n in c))
</code></pre>

blocks|key|857736|text|这是我从2021-10-22开始使用的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|857737|import+sqlalchemy+as+sql

engine+=+sql.create_engine("connection_string")

sql.inspect(engine).get_table_names()|code-block|syntax|javascript|857738|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is what I'm using as of 2021-10-22:
<pre class="lang-py prettyprint-override"><code>import sqlalchemy as sql

engine = sql.create_engine(&quot;connection_string&quot;)

sql.inspect(engine).get_table_names()
</code></pre>

blocks|key|640419|text|如果您使用的是Flask-SQLAlchemy，则可以从db实例中获取它们|type|unstyled|depth|inlineStyleRanges|entityRanges|data|640420|[...]
db+=+SQLAlchemy(app)
db.engine.table_names()+#+you'll+get+a+list+of+all+the+table+names+in+your+database|code-block|syntax|javascript|640421|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you are using Flask-SQLAlchemy, you can get them from your db instance
<pre class="lang-py prettyprint-override"><code>[...]
db = SQLAlchemy(app)
db.engine.table_names() # you'll get a list of all the table names in your database
</code></pre>

I couldn't find any information about this in the documentation, but how can I get a list of tables created in SQLAlchemy?

I used the class method to create the tables.

SQLAlchemy - Getting a list of tables

我在文档中找不到任何关于这方面的信息，但是如何才能获得在SQLAlchemy中创建的表列表呢？我使用类方法来创建表。

问SQLAlchemy -获取表列表
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQLAlchemy -获取表列表EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQLAlchemy -获取表列表
EN