Python SQLAlchemy，清除数据库内容但不删除架构
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4763472/
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
SQLAlchemy, clear database content but don't drop the schema
提问by Juliusz Gonera
I'm developing a Pylons app which is based on exisitng database, so I'm using reflection. I have an SQL file with the schema that I used to create my test database. That's why I can't simply use drop_alland create_all.
我正在开发一个基于现有数据库的 Pylons 应用程序，所以我正在使用反射。我有一个 SQL 文件，其中包含用于创建测试数据库的架构。这就是为什么我不能简单地使用drop_alland create_all。
I would like to write some unit tests and I faced the problem of clearing the database content after each test. I just want to erase all the data but leave the tables intact. Is this possible?
我想写一些单元测试，每次测试后我都面临清除数据库内容的问题。我只想擦除所有数据但保持表格完好无损。这可能吗？
The application uses Postgres and this is what has to be used also for the tests.
该应用程序使用 Postgres，这也必须用于测试。
采纳答案by aknuds1
I asked about the same thing on the SQLAlchemy Google group, and I got a recipe that appears to work well (all my tables are emptied). See the threadfor reference.
我在 SQLAlchemy Google 小组中询问了同样的问题，我得到了一个看起来运行良好的配方（我所有的表都被清空了）。请参阅线程以供参考。
My code (excerpt) looks like this:
我的代码（摘录）如下所示：
import contextlib
from sqlalchemy import MetaData
meta = MetaData()
with contextlib.closing(engine.connect()) as con:
trans = con.begin()
for table in reversed(meta.sorted_tables):
con.execute(table.delete())
trans.commit()
Edit: I modified the code to delete tables in reverse order; supposedly this should ensure that children are deleted before parents.
编辑：我修改了代码以相反的顺序删除表；据说这应该确保在父母之前删除孩子。
回答by Joe L.
How about using truncate:
如何使用截断：
TRUNCATE [ TABLE ] name [, ...]
截断 [ 表 ] 名称 [, ...]
(http://www.postgresql.org/docs/8.4/static/sql-truncate.html)
( http://www.postgresql.org/docs/8.4/static/sql-truncate.html)
This will delete all the records in the table, but leave the schema in tact.
这将删除表中的所有记录，但保持模式不变。
回答by kolypto
For PostgreSQL using TRUNCATE:
对于 PostgreSQL 使用TRUNCATE：
with contextlib.closing(engine.connect()) as con:
trans = con.begin()
con.execute('TRUNCATE {} RESTART IDENTITY;'.format(
','.join(table.name
for table in reversed(Base.metadata.sorted_tables))))
trans.commit()
Note: RESTART IDENTITY;ensures that all sequences are reset as well. However, this is slower than the DELETErecipe by @aknuds1 by 50%.
注意：RESTART IDENTITY;确保所有序列也被重置。然而，这比DELETE@aknuds1的配方慢了 50%。
Another recipe is to drop all tables first and then recreate them. This is slower by another 50%:
另一个方法是先删除所有表，然后重新创建它们。这又慢了 50%：
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
