update primary key (and cascade to child tables)

 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

  1. update primary key (cascade to child tables and inherited models) by variant 11 months, 3 weeks ago
  2. PostgreSQL ON DELETE CASCADE by mjt 4 years, 5 months ago
  3. Disable ordering in the admin for a model by flupke 2 years, 6 months ago
  4. Clear nullable foreign keys on delete by psagers 5 years, 4 months ago
  5. BigIntegerField and BigAutoField by fnl 5 years, 4 months ago

Comments

lylepratt (on July 4, 2012):

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!

#

jbdi (on January 14, 2014):

Nothing happens in the postgres tables. I run script, he says that made update but postges give me not updated data.

#

jbdi (on January 14, 2014):

I take my words back. Very useful script. Thanks

#

(Forgotten your password?)