我如何在Django中临时禁用数据库完整性约束-Postgresql [英] How do i temporarily disable db integrity constraints in django - postgresql
问题描述
我正在编写Django命令以播种现有表,
我需要在播种前截断该表,但是该表上有外键约束。
因此,我在删除表时出现 django.db.utils.IntegrityError ,
如何在Django中暂时关闭外键检查?
我看到 SET FOREIGN KEY CHECK = 0
,但不知道将它们放在哪里:(
Django Command类:
class Command (BaseCommand):
help =为aws区域播种的命令
区域= [
{
'name':'Us East(N. Virginia)',
'region':'us-east-1',
},
{
'name':'US West(Oregon)',
'region':' us-west-2',
},
{
'name':'EU(爱尔兰)',
'region':'eu-west-1',
},
]
def handle(self,* args,** options):
self.stdout.write('seeding region ...')
AwsRegions.objects.all()。delete()#这是我得到错误的地方
的名称,区域self.regions:
self.stdout.write(region)
AwsRegions.objects.create(名称,区域)
self.stdout.write('完成播种区域')
我不得不禁用表上的触发器以停止外键约束检查。
p>禁用触发器
def disable_triggers(self ):
,并将connection.cursor()作为光标:
cursor.execute('ALTER TABLE Table Name DISABLE TRIGGER ALL;')
启用触发器
def enable_triggers (自身):
,具有connection.cursor()作为光标:
cursor.execute('ALTER TABLE Table Name启用所有触发器;')
重要说明:
-
根据此文档链接 ,您可以将列表作为第二个参数传递给
execute()
方法(例如:您可能希望动态传递表名) ,但这会自动转义变量,您可能最终形成语法错误的PostgreSQL查询(这花费了我很多时间来解决它) -
确保您已正确打开触发器
- 如果遇到 权限被拒绝错误 ,那么您可能想要检查数据库用户权限,我刚刚从PgAdmin打开了超级用户权限,这对我来说是可以的。一切恢复正常。 如何做到?
I am writing a Django command to seed an existing table,
I need to truncate the table before seeding, but there are foreign key constraints on that table.
because of that, I am getting django.db.utils.IntegrityError while truncating the table,
How do I turn the Foreign Key Checks off temporarily in Django?
I saw SET FOREIGN KEY CHECK = 0
but don't know where to put them :(
The Django Command class:
class Command(BaseCommand):
help = "Command to seed the aws regions"
regions = [
{
'name': 'Us East (N. Virginia)',
'region': 'us-east-1',
},
{
'name': 'US West (Oregon)',
'region': 'us-west-2',
},
{
'name': 'EU (Ireland)',
'region': 'eu-west-1',
},
]
def handle(self, *args, **options):
self.stdout.write('seeding regions...')
AwsRegions.objects.all().delete() # this is where i get errors
for name, region in self.regions:
self.stdout.write(region)
AwsRegions.objects.create(name, region)
self.stdout.write('done seeding regions')
Got the solution.
I had to disable the Triggers on the table to stop the foreign key constraint check.
Disable Triggers
def disable_triggers(self):
with connection.cursor() as cursor:
cursor.execute('ALTER TABLE "Table Name" DISABLE TRIGGER ALL;')
Enable Triggers
def enable_triggers(self):
with connection.cursor() as cursor:
cursor.execute('ALTER TABLE "Table Name" ENABLE TRIGGER ALL;')
Important Notes:
According to this doc link, you can pass a list as the second argument to the
execute()
method (eg: you might want to pass the table name dynamically), but this will automatically escape the variables and you might end up forming a syntactically wrong PostgreSQL query (which took a lot of my time to fix it)Make sure you turn the triggers back on properly
- If you are getting a Permission denied error Then you might want to check the DB user permissions, I just turned on superuser permissions from PgAdmin, which was ok for me. and everything back to work. How to do it ?
这篇关于我如何在Django中临时禁用数据库完整性约束-Postgresql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!