Automatic detection of primary key with inspectdb for MySQL

[gandalf@…]

Manual adding of primary_key for inspectdb models is annoying. Here is my try at automatic detection of primary key. It also tries to identify unique fields and add unique=True property.

It should probably work with other databases as well but I did not test it.

gandalf@paw:~/django_src/django/core$ svn diff
Index: db/__init__.py
===================================================================
--- db/__init__.py      (revision 2156)
+++ db/__init__.py      (working copy)
@@ -37,6 +37,7 @@
 get_table_list = dbmod.get_table_list
 get_table_description = dbmod.get_table_description
 get_relations = dbmod.get_relations
+get_indexes = dbmod.get_indexes
 OPERATOR_MAPPING = dbmod.OPERATOR_MAPPING
 DATA_TYPES = dbmod.DATA_TYPES
 DATA_TYPES_REVERSE = dbmod.DATA_TYPES_REVERSE
Index: db/backends/ado_mssql.py
===================================================================
--- db/backends/ado_mssql.py    (revision 2156)
+++ db/backends/ado_mssql.py    (working copy)
@@ -118,6 +118,9 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 OPERATOR_MAPPING = {
     'exact': '= %s',
     'iexact': 'LIKE %s',
Index: db/backends/postgresql.py
===================================================================
--- db/backends/postgresql.py   (revision 2156)
+++ db/backends/postgresql.py   (working copy)
@@ -126,6 +126,9 @@
             continue
     return relations

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 # Register these custom typecasts, because Django expects dates/times to be
 # in Python's native (standard-library) datetime/time format, whereas psycopg
 # use mx.DateTime by default.
Index: db/backends/sqlite3.py
===================================================================
--- db/backends/sqlite3.py      (revision 2156)
+++ db/backends/sqlite3.py      (working copy)
@@ -134,6 +134,9 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 # Operators and fields ########################################################

 # SQLite requires LIKE statements to include an ESCAPE clause if the value
Index: db/backends/mysql.py
===================================================================
--- db/backends/mysql.py        (revision 2156)
+++ db/backends/mysql.py        (working copy)
@@ -135,6 +135,15 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    "Returns a dict of indexes for given table"
+    cursor.execute("SHOW INDEX FROM %s" % DatabaseWrapper().quote_name(table_name))
+    indexes = {}
+    for row in cursor.fetchall():
+        indexes[row[4]] = {'Key_name' : row[2],
+                           'Non_unique' : row[1]}
+    return indexes
+
 OPERATOR_MAPPING = {
     'exact': '= %s',
     'iexact': 'LIKE %s',
Index: management.py
===================================================================
--- management.py       (revision 2156)
+++ management.py       (working copy)
@@ -581,6 +581,12 @@
             relations = db.get_relations(cursor, table_name)
         except NotImplementedError:
             relations = {}
+
+        try:
+            indexes = db.get_indexes(cursor, table_name)
+        except NotImplementedError:
+            indexes = {}
+
         for i, row in enumerate(db.get_table_description(cursor, table_name)):
             column_name = row[0]
             if relations.has_key(i):
@@ -609,6 +615,14 @@
                 if field_type == 'CharField' and row[3]:
                     extra_params['maxlength'] = row[3]

+                if column_name in indexes:
+                    if indexes[column_name]['Key_name'] == 'PRIMARY':
+                        extra_params['primary_key'] = True
+                    elif indexes[column_name]['Non_unique'] == 0L:
+                        extra_params['unique'] = True
+                    else:
+                        print indexes[column_name]
+
                 field_desc = '%s = meta.%s(' % (column_name, field_type)
                 field_desc += ', '.join(['%s=%s' % (k, v) for k, v in extra_params.items()])
                 field_desc += ')'
gandalf@paw:~/django_src/django/core$ vim management.py
gandalf@paw:~/django_src/django/core$ svn diff
Index: db/__init__.py
===================================================================
--- db/__init__.py      (revision 2156)
+++ db/__init__.py      (working copy)
@@ -37,6 +37,7 @@
 get_table_list = dbmod.get_table_list
 get_table_description = dbmod.get_table_description
 get_relations = dbmod.get_relations
+get_indexes = dbmod.get_indexes
 OPERATOR_MAPPING = dbmod.OPERATOR_MAPPING
 DATA_TYPES = dbmod.DATA_TYPES
 DATA_TYPES_REVERSE = dbmod.DATA_TYPES_REVERSE
Index: db/backends/ado_mssql.py
===================================================================
--- db/backends/ado_mssql.py    (revision 2156)
+++ db/backends/ado_mssql.py    (working copy)
@@ -118,6 +118,9 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 OPERATOR_MAPPING = {
     'exact': '= %s',
     'iexact': 'LIKE %s',
Index: db/backends/postgresql.py
===================================================================
--- db/backends/postgresql.py   (revision 2156)
+++ db/backends/postgresql.py   (working copy)
@@ -126,6 +126,9 @@
             continue
     return relations

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 # Register these custom typecasts, because Django expects dates/times to be
 # in Python's native (standard-library) datetime/time format, whereas psycopg
 # use mx.DateTime by default.
Index: db/backends/sqlite3.py
===================================================================
--- db/backends/sqlite3.py      (revision 2156)
+++ db/backends/sqlite3.py      (working copy)
@@ -134,6 +134,9 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    raise NotImplementedError
+
 # Operators and fields ########################################################

 # SQLite requires LIKE statements to include an ESCAPE clause if the value
Index: db/backends/mysql.py
===================================================================
--- db/backends/mysql.py        (revision 2156)
+++ db/backends/mysql.py        (working copy)
@@ -135,6 +135,15 @@
 def get_relations(cursor, table_name):
     raise NotImplementedError

+def get_indexes(cursor, table_name):
+    "Returns a dict of indexes for given table"
+    cursor.execute("SHOW INDEX FROM %s" % DatabaseWrapper().quote_name(table_name))
+    indexes = {}
+    for row in cursor.fetchall():
+        indexes[row[4]] = {'Key_name' : row[2],
+                           'Non_unique' : row[1]}
+    return indexes
+
 OPERATOR_MAPPING = {
     'exact': '= %s',
     'iexact': 'LIKE %s',
Index: management.py
===================================================================
--- management.py       (revision 2156)
+++ management.py       (working copy)
@@ -581,6 +581,12 @@
             relations = db.get_relations(cursor, table_name)
         except NotImplementedError:
             relations = {}
+
+        try:
+            indexes = db.get_indexes(cursor, table_name)
+        except NotImplementedError:
+            indexes = {}
+
         for i, row in enumerate(db.get_table_description(cursor, table_name)):
             column_name = row[0]
             if relations.has_key(i):
@@ -609,6 +615,12 @@
                 if field_type == 'CharField' and row[3]:
                     extra_params['maxlength'] = row[3]

+                if column_name in indexes:
+                    if indexes[column_name]['Key_name'] == 'PRIMARY':
+                        extra_params['primary_key'] = True
+                    elif indexes[column_name]['Non_unique'] == 0L:
+                        extra_params['unique'] = True
+
                 field_desc = '%s = meta.%s(' % (column_name, field_type)
                 field_desc += ', '.join(['%s=%s' % (k, v) for k, v in extra_params.items()])
                 field_desc += ')'

[Adrian Holovaty]

(In [2346]) Fixed #1286 -- Improved 'inspectdb' so that it introspects primary_key=True and unique=True for MySQL. Thanks, gandalf@…

[Jan Rademaker <j.rademaker@…>]

Covers additional scenarios

[Jan Rademaker <j.rademaker@…>]

mysql.diff covers (some of) the following scenarios.

-- SHOW INDEX FROM test1
test1	0	PRIMARY	1	myid	A	0	NULL	NULL	
test1	0	f1_f2_f3	1	f1	A	NULL	NULL	NULL	
test1	0	f1_f2_f3	2	f2	A	NULL	NULL	NULL	
test1	0	f1_f2_f3	3	f3	A	NULL	NULL	NULL	
test1	0	f2_unique	1	f2	A	NULL	NULL	NULL	
test1	1	f1_f2	1	f1	A	NULL	NULL	NULL	
test1	1	f1_f2	2	f2	A	NULL	NULL	NULL	
test1	1	myid_f3	1	myid	A	NULL	NULL	NULL	
test1	1	myid_f3	2	f3	A	NULL	NULL	NULL	

• If a PRIMARY key spans serveral columns it is ignored
• A field is only considered unique when the index does not span more than 1 column
• In the case of 1 or more unique indexes that span more than 1 column the first is used as META.unique_together

Example output:

from django.core import meta

class Test1(meta.Model):
    myid = meta.IntegerField(primary_key=True)
    f1 = meta.IntegerField()
    f2 = meta.IntegerField(unique=True)
    f3 = meta.IntegerField()
    class META:
        db_table = 'test1'
        unique_together = (("f1", "f2", "f3"),)

[Jacob]

Auto-detection isn't going to be perfect; this works as well as it can at this point.

[Adrian Holovaty]

Reopening because this can be improved with the patch -- I just haven't had time to implement it.

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Anssi Kääriäinen]

I am pretty sure this has been fixed, MySQL passes introspection tests, and get_indexes, and get_primary_key_column in mysql.introspection seem to work correctly.