Management command to update a primary key and update all child-tables with a foreign key to this table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | # -*- coding: utf-8 -*-
# code is in the public domain
# (c) 2012 Thomas Güttler http://www.thomas-guettler.de/
#
# http://djangosnippets.org/snippets/2691/
# myapp/management/commands/update_primary_key.py
u'''
Management command to update a primary key and update all child-tables with a foreign key to this table.
Does use django's db introspection feature. Tables don't need to have django ORM models.
Usage: manage.py update_primary_key table_name column_name value_old value_new
'''
import logging
from django.conf import settings
from django.core.management.base import BaseCommand, CommandError
from django.db import connection
from django.db.transaction import commit_on_success
table_list=None
def get_table_list(cursor):
global table_list
if not table_list:
table_list=connection.introspection.get_table_list(cursor)
return table_list
relations={} # Cache
def get_relations(cursor, table_name):
rels=relations.get(table_name)
if rels is None:
rels=connection.introspection.get_relations(cursor, table_name)
relations[table_name]=rels
return rels
def get_back_relations(cursor, table_name):
backs=[]
relations_back={}
for ref_table in get_table_list(cursor):
ref_relations=get_relations(cursor, ref_table)
for ref_col_idx, ref_relation in ref_relations.items():
to_col=ref_relation[0]
to_table=ref_relation[1]
if to_table!=table_name:
continue
# Found a reference to table_name
backs=relations_back.get(to_col)
if not backs:
backs=[]
relations_back[to_col]=backs
backs.append((ref_col_idx, ref_table))
return (backs, relations_back)
class Command(BaseCommand):
args = 'table_name column_name value_old value_new'
help = 'Update a primary key and update all child-tables with a foreign key to this table.'
@commit_on_success
def handle(self, *args, **options):
rootLogger = logging.getLogger('')
rootLogger.setLevel(logging.INFO)
if len(args)!=4:
raise CommandError('Need args: %s' % self.args)
table_name, column_name, value_old, value_new = args
cursor=connection.cursor()
descr=connection.introspection.get_table_description(cursor, table_name)
for idx, col in enumerate(descr):
if col.name==column_name:
break
else:
raise CommandError('Column %r not in table %r' % (column_name, table_name))
backs, relations_back = get_back_relations(cursor, table_name)
sql='select count(*) from "%s" where "%s" = %%s' % (table_name, column_name)
cursor.execute(sql, [value_old])
count=cursor.fetchone()[0]
sql=sql % value_old
if count==0:
raise CommandError('No row found: %s' % sql)
if count>1:
raise CommandError('More than one row found???: %s' % sql)
def execute(sql, args):
logging.info('%s %s' % (sql, args))
cursor.execute(sql, args)
execute('update "%s" set "%s" = %%s where "%s" = %%s' % (table_name, column_name, column_name), [value_new, value_old])
for col_idx, ref_table in relations_back[idx]:
cursor.execute('update "%s" set "%s" = %%s where "%s" = %%s' % (table_name, column_name, column_name), [value_new, value_old])
ref_descr=connection.introspection.get_table_description(cursor, ref_table)
ref_col=ref_descr[col_idx]
execute('update "%s" set "%s" = %%s where "%s" = %%s' % (ref_table, ref_col.name, ref_col.name), [value_new, value_old])
|
More like this
- Template tag - list punctuation for a list of items by shapiromatron 2 months, 2 weeks ago
- JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 2 months, 3 weeks ago
- Serializer factory with Django Rest Framework by julio 9 months, 3 weeks ago
- Image compression before saving the new model / work with JPG, PNG by Schleidens 10 months, 1 week ago
- Help text hyperlinks by sa2812 11 months ago
Comments
What database type was this written for? MySQL? Postgres? SQLite?
I'm having trouble testing it on an SQLite DB, but will need to eventually use it on a MySQL DB.
Thanks for creating this!
#
Nothing happens in the postgres tables. I run script, he says that made update but postges give me not updated data.
#
I take my words back. Very useful script. Thanks
#
Updated for Django 1.11 here:
https://djangosnippets.org/snippets/10619/
#
Please login first before commenting.