Sqlite3 Introspection (get_primary_key_column) Always Returns None

[Zackary Troop]

Hello, I've noticed that the get_primary_key_column method always returns None, even though a Primary Key is present in the table. I am using the sqlite3 backend. I've captured the steps to reproduce below.

Create new environment, install Django.

(env) system :: ~/OpenSource/testing % pip3 install django                                                                                                                       
Collecting django
  Downloading https://files.pythonhosted.org/packages/89/f9/94c20658f0cdecc2b6607811e2c0bb042408a51f589e5ad0cb0eac3236a1/Django-2.0.4-py3-none-any.whl (7.1MB)
    100% |████████████████████████████████| 7.1MB 504kB/s 
Collecting pytz (from django)
  Downloading https://files.pythonhosted.org/packages/dc/83/15f7833b70d3e067ca91467ca245bae0f6fe56ddc7451aa0dc5606b120f2/pytz-2018.4-py2.py3-none-any.whl (510kB)
    100% |████████████████████████████████| 512kB 694kB/s 
Installing collected packages: pytz, django
Successfully installed django-2.0.4 pytz-2018.4

Create new sqlite3 database called test.sqlite3 and add a new table with a Primary Key.

(env) system :: OpenSource/testing/testapp % sqlite3 test.sqlite3
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
sqlite> CREATE TABLE `food` (
   ...>   id int PRIMARY KEY NOT NULL,
   ...>   food_group_id int REFERENCES food_group(id) NOT NULL,
   ...>   long_desc text NOT NULL DEFAULT '',
   ...>   short_desc text NOT NULL DEFAULT ''
   ...> );
sqlite> .exit

Add database profile to settings.

(env) system :: OpenSource/testing/testapp % nano testapp/settings.py 

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'testdb': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'test.sqlite3'),
    }
}

Open Django shell, verify table existance and try to determine if a Primary Key is present.

(env) system :: OpenSource/testing/testapp % python manage.py shell
>>> from django.db import connections
>>> from django.db.backends.sqlite3 import introspection
>>> db_conn = connections['testdb']
>>> cur = db_conn.cursor()
>>> introspection.DatabaseIntrospection(db_conn).get_table_list(cur)
[TableInfo(name='food', type='t')]
>>> answer = introspection.DatabaseIntrospection(db_conn).get_primary_key_column(cur, 'food')
>>> type(answer)
<class 'NoneType'>

[Claude Paroz]

Looks like the regex used by Django to detect the primary key expects a quoted column name, which is not always the case for legacy tables.

[Zackary Troop]

Small tweak in the introspection.py in the sqlite3 backend will fix this. I can setup the PR tonight. Small proof-of-concept below:

>>> for field_desc in fields_sql.split(','):
...     field_desc = field_desc.strip()
...     m = re.match(r"(\w+).*PRIMARY KEY(?: AUTOINCREMENT)?.*", field_desc)
...     if m:
...             print(m.group(1))
... 
id

[Zackary Troop]

​PR

[Claude Paroz <claude@…>]

In 30f8642:

Fixed #29350 -- Fix get_primary_key_column() method in sqlite3 backend

Thanks Tim Graham and Mariusz Felisiak for the reviews.

[GitHub <noreply@…>]

In 7ac3008:

Refs #29350 -- Fixed 'invalid escape sequence' warning in SQLite introspection.