[Django] #29350: Sqlite3 Introspection (get_primary_key_column) Always Returns None
Django
-------------------------------------+-------------------------------------
Reporter: Zackary | Owner: nobody
Troop |
Type: Bug | Status: new
Component: Database | Version: 2.0
layer (models, ORM) |
Severity: Normal | Keywords: sqlite3
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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'>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29350>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3 | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29350#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3 | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Zackary Troop):
Small tweak in the introspection.py in the sqlite3 backend will fix this.
I can setup the PR tonight.
{{{
>>> 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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29350#comment:2>
Django
-------------------------------------+-------------------------------------
| Troop
Type: Bug | Status: assigned
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3 | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Zackary Troop
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/29350#comment:3>
Django
-------------------------------------+-------------------------------------
| Troop
Type: Bug | Status: assigned
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: sqlite3 | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/9899 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/29350#comment:4>
Django
-------------------------------------+-------------------------------------
| Troop
Type: Bug | Status: assigned
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/29350#comment:5>
Django
-------------------------------------+-------------------------------------
| Troop
Component: Database layer | Version: 2.0
(models, ORM) |
Keywords: sqlite3 | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"30f8642f2e9f4ed64ddb09163e09c1ca073b935a" 30f8642]:
{{{
#!CommitTicketReference repository=""
revision="30f8642f2e9f4ed64ddb09163e09c1ca073b935a"
Fixed #29350 -- Fix get_primary_key_column() method in sqlite3 backend
Thanks Tim Graham and Mariusz Felisiak for the reviews.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29350#comment:6>
Django
-------------------------------------+-------------------------------------
| Troop
Type: Bug | Status: closed
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: sqlite3 | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by GitHub <noreply@…>):
In [changeset:"7ac3008fe473f8015bda9789be23657d3b329e6d" 7ac3008]:
{{{
#!CommitTicketReference repository=""
revision="7ac3008fe473f8015bda9789be23657d3b329e6d"
Refs #29350 -- Fixed 'invalid escape sequence' warning in SQLite
introspection.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29350#comment:7>