我正在尝试使用SQLAlchemy来编写Pylons应用程序,我很喜欢它,只有一件事情让我困惑,那就是在执行Table().create()
之前,是否有可能打印出所生成的原始SQL CREATE TABLE
数据?
我正在尝试使用SQLAlchemy来编写Pylons应用程序,我很喜欢它,只有一件事情让我困惑,那就是在执行Table().create()
之前,是否有可能打印出所生成的原始SQL CREATE TABLE
数据?
from sqlalchemy.schema import CreateTable
print(CreateTable(table))
如果您正在使用声明性语法:print(CreateTable(Model.__table__))
更新:
由于我已经有了被接受的答案,并且klenwell的回答中有重要信息,所以我也会在这里添加它。
通过编译您的引擎,您可以获取特定数据库(MySQL、Postgresql等)的SQL语句。
print(CreateTable(Model.__table__).compile(engine))
更新2:
@jackotonye在评论中添加了一种不需要引擎的方法。
print(CreateTable(Model.__table__).compile(dialect=postgresql.dialect()))
您可以设置引擎以转储元数据创建序列,使用以下内容:
def metadata_dump(sql, *multiparams, **params):
# print or write to log or file etc
print(sql.compile(dialect=engine.dialect))
engine = create_engine(myDatabaseURL, strategy='mock', executor=metadata_dump)
metadata.create_all(engine)
这种方法的一个优点是枚举和索引包含在打印输出中。使用CreateTable
则会排除它们。
另一个优点是模式定义的顺序是正确的,(几乎)可以用作脚本。
print(str(sql.compile(dialect=engine.dialect)) + ";")
,以获取输出中的分号。 - Tim Cokerengine = create_mock_engine("postgresql://", metadata_dump)
和
metadata.create_all(engine, checkfirst=False)
- Alexander我需要获取原始表格的SQL以便为一些现有模型设置测试。这是我基于Antoine的答案创建的适用于SQLAlchemy 0.7.4的成功单元测试,作为概念验证:
from sqlalchemy import create_engine
from sqlalchemy.schema import CreateTable
from model import Foo
sql_url = "sqlite:///:memory:"
db_engine = create_engine(sql_url)
table_sql = CreateTable(Foo.table).compile(db_engine)
self.assertTrue("CREATE TABLE foos" in str(table_sql))
from sqlalchemy.dialects import postgresql
from sqlalchemy.schema import CreateTable
from sqlalchemy import Table, Column, String, MetaData
metadata = MetaData()
users = Table('users', metadata,
Column('username', String)
)
statement = CreateTable(users)
print(statement.compile(dialect=postgresql.dialect()))
CREATE TABLE users (
username VARCHAR
)
How do I render SQL expressions as strings, possibly with bound parameters inlined?
...
or without an Engine:
from sqlalchemy.dialects import postgresql print(statement.compile(dialect=postgresql.dialect()))
来源:http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html#faq-sql-expression-string
#!/usr/bin/env python
import csv
from sqlalchemy.dialects import postgresql
from sqlalchemy import bindparam, Table, Column, String, MetaData
metadata = MetaData()
users = Table('users', metadata,
Column('username', String)
)
renames = []
with open('users.csv') as csvfile:
for row in csv.DictReader(csvfile):
renames.append({
'from': row['sAMAccountName'],
'to': row['mail']
})
for rename in renames:
stmt = (users.update()
.where(users.c.username == rename['from'])
.values(username=rename['to']))
print(str(stmt.compile(dialect=postgresql.dialect(),
compile_kwargs={"literal_binds": True})) + ';')
处理这个 users.csv 文件时:
sAMAccountName,mail
bmcboatface,boaty.mcboatface@example.com
ndhyani,naina.dhyani@contoso.com
UPDATE users SET username='boaty.mcboatface@example.com' WHERE users.username = 'bmcboatface';
UPDATE users SET username='naina.dhyani@contoso.com' WHERE users.username = 'ndhyani';users.username = 'ndhyani';
为什么一艘研究船有一个电子邮件地址尚未确定。我已经联系了Example公司的IT团队,但没有得到回复。
也许你指的是sqlalchemy.create_engine()函数中的echo
参数?
/tmp$ cat test_s.py
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Department(Base):
__tablename__ = "departments"
department_id = sa.Column(sa.types.Integer, primary_key=True)
name = sa.Column(sa.types.Unicode(100), unique=True)
chief_id = sa.Column(sa.types.Integer)
parent_department_id = sa.Column(sa.types.Integer,
sa.ForeignKey("departments.department_id"))
parent_department = sa.orm.relation("Department")
engine = sa.create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(bind=engine)
/tmp$ python test_s.py
2011-03-24 15:09:58,311 INFO sqlalchemy.engine.base.Engine.0x...42cc PRAGMA table_info("departments")
2011-03-24 15:09:58,312 INFO sqlalchemy.engine.base.Engine.0x...42cc ()
2011-03-24 15:09:58,312 INFO sqlalchemy.engine.base.Engine.0x...42cc
CREATE TABLE departments (
department_id INTEGER NOT NULL,
name VARCHAR(100),
chief_id INTEGER,
parent_department_id INTEGER,
PRIMARY KEY (department_id),
UNIQUE (name),
FOREIGN KEY(parent_department_id) REFERENCES departments (department_id)
)
2011-03-24 15:09:58,312 INFO sqlalchemy.engine.base.Engine.0x...42cc ()
2011-03-24 15:09:58,312 INFO sqlalchemy.engine.base.Engine.0x...42cc COMMIT
__table_args__
成员定义的索引。有任何想法吗? - Pehatprint(CreateTable(Model.__table__).compile(dialect=postgresql.dialect()))
- jackotonyefrom sqlalchemy.dialects.mysql import mysqlconnector print(CreateTable(AttribResults.__table__).compile(dialect=mysqlconnector.dialect()))
- glefaitdeclarative_base
的类之一。例如:class User(Base):
(在该示例中,User
是Model
)。 - Antoine Leclair