在表delete()之后重置ID计数 [英] Reset id count after table delete()
问题描述
出于测试目的,我在执行代码之前清除(删除)了每个表。
For testing purposes, I clear (delete) every table before executing code.
for table in reversed(db.metadata.sorted_tables):
engine.execute(table.delete())
do_stuff()
但是,新数据的 id
值从上一个 id
的左侧开始off:
However, the new data's id
values start from where the previous id
's left off:
第一次迭代:
id | value
-----+---------
1 | hi
2 | there
第二次迭代(删除表,插入新数据):
Second iteration (delete table, insert new data):
id | value
-----+---------
3 | good
4 | day
有什么方法可以重置 id
删除表格时计数吗?
Is there any way to reset the id
count when I delete the table?
编辑:似乎我把它弄坏了,表格现在根本没有清除
seems I broke it, table is not cleared at all now
config.py
SQLALCHEMY_DATABASE_URI = 'postgresql://postgres:myPassword@localhost/myDatabase'
app.py
app = Flask(__name__)
app.config.from_pyfile('config.py')
db = SQLAlchemy(app)
models.py
from app import app, db
def clear():
for table in reversed(db.metadata.sorted_tables):
db.engine.execute('TRUNCATE TABLE ' + table.name + ' RESTART IDENTITY CASCADE')
该表仍在添加(使用 db.session.add_all()
和 db.session.commit()
)。但是, clear()
不执行任何操作。当我以 postgres
用户在 terminal
中登录并直接执行 TRUNCATE TABLE myTable RESTART IDENTITY时CASCADE
,它起作用。
The table is still being added to (using db.session.add_all()
and db.session.commit()
). However, clear()
does nothing. When I log in as the postgres
user in terminal
and directly execute TRUNCATE TABLE myTable RESTART IDENTITY CASCADE
, it works.
table.name
给出正确的名称。这使我相信 db.engine.execute()
出了点问题,但是自 db.session.add_all以来这没有多大意义。 ()
有效
table.name
gives the correct names. That leads me to believe there is something wrong with db.engine.execute()
, but that does not make much sense since db.session.add_all()
works
推荐答案
调用 TRUNCATE TABLE MyTable重新启动身份;
循环中的每个表,而不是调用 table.delete()
-这将重置自动递增序列。
Call TRUNCATE TABLE MyTable RESTART IDENTITY;
for every table in the loop instead of calling table.delete()
- this should reset the auto increment sequence.
这篇关于在表delete()之后重置ID计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!