标签归档：pyramid

知识问答

SQLAlchemy-获取表列表

问题：SQLAlchemy-获取表列表

我在文档中找不到与此有关的任何信息，但是如何获得在SQLAlchemy中创建的表的列表？

我使用了类方法来创建表。

点击查看英文原文

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.

回答 0

所有表都收集在tablesSQLAlchemy MetaData对象的属性中。要获取这些表的名称列表：

>>> metadata.tables.keys()
['posts', 'comments', 'users']

如果使用声明性扩展，则可能不是您自己管理元数据。幸运的是，元数据仍然存在于基类中，

>>> Base = sqlalchemy.ext.declarative.declarative_base()
>>> Base.metadata
MetaData(None)

如果您试图弄清楚数据库中存在哪些表，甚至还没有告诉SQLAlchemy的表，那么可以使用表反射。然后，SQLAlchemy将检查数据库并使用所有缺少的表更新元数据。

>>> metadata.reflect(engine)

对于Postgres，如果您有多个架构，则需要遍历引擎中的所有架构：

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)
点击查看英文原文

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

>>> metadata.tables.keys()
['posts', 'comments', 'users']

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, 

>>> Base = sqlalchemy.ext.declarative.declarative_base()
>>> Base.metadata
MetaData(None)

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.

>>> metadata.reflect(engine)

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

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)

回答 1

engine对象中有一个方法来获取表名称列表。engine.table_names()

点击查看英文原文

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

回答 2

from sqlalchemy import create_engine
engine = create_engine('postgresql://use:pass@localhost/DBname')
print (engine.table_names())
点击查看英文原文 
from sqlalchemy import create_engine
engine = create_engine('postgresql://use:pass@localhost/DBname')
print (engine.table_names())

回答 3

在python解释器中，使用db.engine.table_names（）

$ python
>>> from myapp import db
>>> db.engine.table_names()
点击查看英文原文

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

$ python
>>> from myapp import db
>>> db.engine.table_names()

回答 4

我一直在寻找这样的东西：

from sqlalchemy import create_engine
eng = create_engine('mysql+pymysql://root:password@localhost:3306', pool_recycle=3600)
q = eng.execute('SHOW TABLES')

available_tables = q.fetchall()

它执行并返回所有表。

更新：

Postgres：

eng = create_engine('postgresql+psycopg2://root:password@localhost/
q = eng.execute('SELECT * FROM pg_catalog.pg_tables')
点击查看英文原文

I was looking for something like this:

from sqlalchemy import create_engine
eng = create_engine('mysql+pymysql://root:password@localhost:3306', pool_recycle=3600)
q = eng.execute('SHOW TABLES')

available_tables = q.fetchall()

It does an execute and returns all of the tables.

update:

Postgres:

eng = create_engine('postgresql+psycopg2://root:password@localhost/
q = eng.execute('SELECT * FROM pg_catalog.pg_tables')

回答 5

用于创建表的元数据对象在字典中具有该对象。

metadata.tables.keys()
点击查看英文原文

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

metadata.tables.keys()

回答 6

我正在解决相同的问题，并找到了这篇文章。经过一些尝试运行后，我建议使用下面的方法列出所有表：（由zerocog提及）

metadata = MetaData()
metadata.reflect(bind=engine)
for table in metadata.sorted_tables:
    print(table)

这对于直接处理表很有用，我建议您这样做。

并使用以下代码获取表名：

for table_name in engine.table_names():
    print(table_name)

“ metadata.tables”为表名和表对象提供了一个Dict。这对于快速查询也很有用。

点击查看英文原文

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)

metadata = MetaData()
metadata.reflect(bind=engine)
for table in metadata.sorted_tables:
    print(table)

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

And use below code to get table names:

for table_name in engine.table_names():
    print(table_name)

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

回答 7

一次反映所有表允许您也检索隐藏的表名。我创建了一些临时表，他们出现了

meta = MetaData()
meta.reflect(bind=myengine)
for table in reversed(meta.sorted_tables):
    print table

参考http://docs.sqlalchemy.org/en/latest/core/reflection.html

点击查看英文原文

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

meta = MetaData()
meta.reflect(bind=myengine)
for table in reversed(meta.sorted_tables):
    print table

Reference http://docs.sqlalchemy.org/en/latest/core/reflection.html

回答 8

就这么简单：

engine.table_names()

另外，要测试表是否存在：

engine.has_table(table_name)
点击查看英文原文

Just this simple:

engine.table_names()

Also, to test whether a table exists:

engine.has_table(table_name)