Opened 10 years ago

Closed 3 years ago

#1286 closed New feature (fixed)

Automatic detection of primary key with inspectdb for MySQL

Reported by: gandalf@… Owned by: nobody
Component: Core (Management commands) Version: master
Severity: Normal Keywords: inspectdb
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

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 += ')'

Attachments (1)

mysql.diff (3.3 KB) - added by Jan Rademaker <j.rademaker@…> 10 years ago.
Covers additional scenarios

Download all attachments as: .zip

Change History (13)

comment:1 Changed 10 years ago by adrian

  • Resolution set to fixed
  • Status changed from new to closed

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

Changed 10 years ago by Jan Rademaker <j.rademaker@…>

Covers additional scenarios

comment:2 Changed 10 years ago by Jan Rademaker <j.rademaker@…>

  • Resolution fixed deleted
  • Status changed from closed to reopened

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"),)

comment:3 Changed 10 years ago by anonymous

  • Summary changed from Automatic detection of primary key with inspectdb for Mysql to [patch] Automatic detection of primary key with inspectdb for Mysql

comment:4 Changed 10 years ago by jacob

  • Resolution set to fixed
  • Status changed from reopened to closed

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

comment:5 Changed 10 years ago by adrian

  • Resolution fixed deleted
  • Status changed from closed to reopened

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

comment:6 Changed 9 years ago by adrian

  • Component changed from django-admin.py to django-admin.py inspectdb

comment:7 Changed 9 years ago by Simon G. <dev@…>

  • Keywords inspectdb added
  • Needs tests set
  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Accepted

comment:8 Changed 4 years ago by gabrielhurley

  • Component changed from django-admin.py inspectdb to Core (Management commands)

comment:9 Changed 4 years ago by lrekucki

  • Severity changed from normal to Normal
  • Summary changed from [patch] Automatic detection of primary key with inspectdb for Mysql to Automatic detection of primary key with inspectdb for MySQL
  • Type changed from enhancement to New feature

comment:10 Changed 4 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:11 Changed 4 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:12 Changed 3 years ago by akaariai

  • Resolution set to fixed
  • Status changed from reopened to closed

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.

Note: See TracTickets for help on using tickets.
Back to Top