Python 使用 SQLAlchemy 列出数据库表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21310549/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List database tables with SQLAlchemy
提问by drgxfs
I want to implement a function that gives information about all the tables (and their column names) that are present in a database (not only those created with SQLAlchemy). While reading the documentation it seems to me that this is done via reflection but I didn't manage to get something working. Any suggestions or examples on how to do this?
我想实现一个函数，该函数提供有关数据库中存在的所有表（及其列名）的信息（不仅是使用 SQLAlchemy 创建的表）。在阅读文档时，在我看来这是通过反射完成的，但我没有设法使某些事情起作用。有关如何执行此操作的任何建议或示例？
采纳答案by zzzeek
start with an engine:
从引擎开始：
from sqlalchemy import create_engine
engine = create_engine("postgresql://u:p@host/database")
quick path to all table /column names, use an inspector:
所有表/列名称的快速路径，使用检查器：
from sqlalchemy import inspect
inspector = inspect(engine)
for table_name in inspector.get_table_names():
for column in inspector.get_columns(table_name):
print("Column: %s" % column['name'])
文档：http: //docs.sqlalchemy.org/en/rel_0_9/core/reflection.html?highlight= inspector#fine-grained-reflection-with- inspector
alternatively, use MetaData / Tables:
或者，使用元数据/表格：
from sqlalchemy import MetaData
m = MetaData()
m.reflect(engine)
for table in m.tables.values():
print(table.name)
for column in table.c:
print(column.name)
docs: http://docs.sqlalchemy.org/en/rel_0_9/core/reflection.html#reflecting-all-tables-at-once
文档：http: //docs.sqlalchemy.org/en/rel_0_9/core/reflection.html#reflecting-all-tables-at-once
回答by PepperoniPizza
Hey I created a small module that helps easily reflecting all tables in a database you connect to with SQLAlchemy, give it a look: EZAlchemy
嘿，我创建了一个小模块，它有助于轻松反映您使用 SQLAlchemy 连接的数据库中的所有表，请看：EZAlchemy
from EZAlchemy.ezalchemy import EZAlchemy
DB = EZAlchemy(
db_user='username',
db_password='pezzword',
db_hostname='127.0.0.1',
db_database='mydatabase',
d_n_d='mysql' # stands for dialect+driver
)
# this function loads all tables in the database to the class instance DB
DB.connect()
# List all associations to DB, you will see all the tables in that database
dir(DB)
回答by user2682863
While reflection/inspectionis useful, I had trouble getting the data out of the database. I found sqlsoupto be much more user-friendly. You create the engine using sqlalchemy and pass that engine to sqlsoup.SQlSoup. ie:
虽然反射/检查很有用，但我无法从数据库中获取数据。我发现sqlsoup更加用户友好。您使用 sqlalchemy 创建引擎并将该引擎传递给 sqlsoup.SQlSoup。IE：
import sqlsoup
def create_engine():
from sqlalchemy import create_engine
return create_engine(f"mysql+mysqlconnector://{database_username}:{database_pw}@{database_host}/{database_name}")
def test_sqlsoup():
engine = create_engine()
db = sqlsoup.SQLSoup(engine)
# Note: database must have a table called 'users' for this example
users = db.users.all()
print(users)
if __name__ == "__main__":
test_sqlsoup()
If you're familiar with sqlalchemy then you're familiar with sqlsoup. I've used this to extract data from a wordpress database.
如果您熟悉 sqlalchemy，那么您就会熟悉 sqlsoup。我用它从 wordpress 数据库中提取数据。
回答by Rohit
First set up the sqlalchemy engine.
首先设置sqlalchemy引擎。
from sqlalchemy import create_engine, inspect, text
from sqlalchemy.engine import url
connect_url = url.URL(
'oracle',
username='db_username',
password='db_password',
host='db_host',
port='db_port',
query=dict(service_name='db_service_name'))
engine = create_engine(connect_url)
try:
engine.connect()
except Exception as error:
print(error)
return
Like others have mentioned, you can use the inspect method to get the table names.
就像其他人提到的那样，您可以使用检查方法来获取表名。
But in my case, the list of tables returned by the inspect method was incomplete.
但就我而言，inspect 方法返回的表列表不完整。
So, I found out another way to find table names by using pure SQL queries in sqlalchemy.
所以，我找到了另一种通过在 sqlalchemy 中使用纯 SQL 查询来查找表名的方法。
query = text("SELECT table_name FROM all_tables where owner = '%s'"%str('db_username'))
table_name_data = self.session.execute(query).fetchall()
Just for sake of completeness of answer, here's the code to fetch table names by inspect method (if it works good in your case).
只是为了回答的完整性，这里是通过检查方法获取表名的代码（如果它在你的情况下效果很好）。
inspector = inspect(engine)
table_names = inspector.get_table_names()
