How to Connect an Existing SQLite Database with Custom Primary Keys to Django?

Using Django Using the ORM
1

I am developing a web application in Python Django for managing vehicle parts. I have already designed the database in SQLite3 with multiple tables and pre-populated data (without using Django). Each table has its own primary key that is different from the others. For example:

  • The Price table uses a primary key of the form Year+Counter on 8 length.
  • The Stock table uses a primary key of the form Year+Counter on 5 length
  • The Article table has a primary key that is a 10-character string.

I created these keys using triggers and other custom logic.

Now, I want to connect this existing SQLite database to Django. However, I am facing issues because Django expects its own primary key logic. This doesn’t suit my needs.

Is there a way to connect my existing SQLite database with its custom primary keys to Django?
Any help or guidance would be greatly appreciated. Thank you!
2

Are you looking at using these tables as “read only”, or are you expecting to update them?

If they’re going to be “read only”, that’s relatively easy and straight-forward.

You can use the inspectdb command to have Django scan those tables and give you a skeleton model class to start with. You can then modify that model to account for things it was unable to directly detect.

If you’re going to be updating these tables, then it may become more difficult. However, this is one of those situations where it’s a lot easier to talk about this in the context of real code and not in the abstract, because there are different situations that may have their own implications. If you want to work through one such example, I would suggest you provide the database schema definition of a table, and what you have done so far with creating a model for it.

3

I want to update the tables in the database, knowing that there are links between the tables. Here is an example of the tables.:

/* Table : Article */
CREATE TABLE IF NOT EXISTS [Article] (
    [ID_REFERENCE] VARCHAR(11) PRIMARY KEY,
    [DATEINREFERENCE] DATE NOT NULL,
    [ID_SRCFILEARTICLE] CHAR(1) NOT NULL,
    [DESIGNATION] VARCHAR(500) NOT NULL,
    [DATENDREFERENCE] DATE,
    [ID_CLASSFAMILLE] VARCHAR(2) NOT NULL,
    [ID_MODELVEHICULE] INTEGER NULL,
    [INFORMATION] TEXT NULL,
    FOREIGN KEY (ID_MODELVEHICULE) REFERENCES Model_Vehicule(ID_MODELVEHICULE),
    FOREIGN KEY (ID_SRCFILEARTICLE) REFERENCES T_Src_File_Article(ID_SRCFILEARTICLE)
    FOREIGN KEY (ID_CLASSFAMILLE) REFERENCES Famille_Article(ID_CLASSFAMILLE)
);

/* Table : SRCFILEARTICLE */

CREATE TABLE [T_Src_File_Article] (
    [ID_SRCFILEARTICLE] CHAR PRIMARY KEY,
    [SRCFILEARTICLE_COMMENT] VARCHAR(10) NOT NULL UNIQUE,
    [SRCFILEARTICLE_DESCR] TEXT NOT NULL,
);

/* Table : Prix_Article_Imporateur */
CREATE TABLE [Prix_Article_Imporateur] (
    [ID_PRIXIMPORT] TEXT PRIMARY KEY, /*YEAR+COUNT*/
    [DATE_PRIXIMPORT] DATE NOT NULL,
    [ID_REFERENCE] VARCHAR(11) NOT NULL,
    [ANC_PRIXIMPORT] REAL,
    [NOUV_PRIXIMPORT] REAL,
    [ID_SRCFILEARTICLE] CHAR(1) NOT NULL,
    FOREIGN KEY (ID_SRCFILEARTICLE) REFERENCES T_Src_File_Article(ID_SRCFILEARTICLE)
    FOREIGN KEY (ID_REFERENCE) REFERENCES Article(ID_REFERENCE)
);

-- ============================================================
--   TRIGGER : Prix_Article_Imporateur_Index
-- ============================================================

CREATE TRIGGER IF NOT EXISTS increment_counter_and_set_prix_article_importateur_id
AFTER INSERT ON Prix_Article_Imporateur
FOR EACH ROW
BEGIN
    UPDATE Prix_Article_Imporateur
    SET ID_PRIXIMPORT = (SELECT strftime('%Y', 'now') || printf('%08d', ifnull(max(cast(substr(ID_PRIXIMPORT,5) as integer)),0)+1) FROM Prix_Article_Imporateur WHERE substr(ID_PRIXIMPORT,1,4) = strftime('%Y', 'now'))
    WHERE rowid=NEW.rowid;
END;

/* Table : Prix_Article_Client */
CREATE TABLE [Prix_Article_Client] (
    [ID_PRIXCLIENT] TEXT PRIMARY KEY, /*YEAR+COUNT*/
    [DATE_PRIXCLIENT] DATE NOT NULL,
    [ID_REFERENCE] VARCHAR(11) NOT NULL,
    [ANC_PRIXCLIENT] REAL,
    [NOUV_PRIXCLIENT] REAL,
    [ID_SRCFILEARTICLE] CHAR(1) NOT NULL,
    FOREIGN KEY (ID_SRCFILEARTICLE) REFERENCES T_Src_File_Article(ID_SRCFILEARTICLE)
    FOREIGN KEY (ID_REFERENCE) REFERENCES Article(ID_REFERENCE)
);

-- ============================================================
--   TRIGGER : Prix_Article_Client_Index
-- ============================================================

CREATE TRIGGER IF NOT EXISTS increment_counter_and_set_prix_article_client_id
AFTER INSERT ON [Prix_Article_Client]
FOR EACH ROW
BEGIN
    UPDATE [Prix_Article_Client]
    SET ID_PRIXCLIENT = (SELECT strftime('%Y', 'now') || printf('%08d',
    ifnull(max(cast(substr(ID_PRIXCLIENT,5) as integer)),0)+1) FROM
    Prix_Article_Client WHERE substr(ID_PRIXCLIENT,1,4) = strftime('%Y', 'now'))
    WHERE rowid=NEW.rowid;
END;
4

You wrote:

Please be more specific here about the issues you’re facing. Show the models you have created and what those issues are.

Side note: When posting code (or any other preformatted text) here, enclose the code between lines of three backtick - ` characters. This means you’ll have a line of ```, then your code, then another line of ```. This forces the forum software to keep your code properly formatted. (I’ve taken the liberty of correcting your post above with the database schema.)

5

At this stage, I am at the SQL tables step

/* Table : Famille_Article */
CREATE TABLE [Famille_Article] (
    [ID_CLASSFAMILLE] VARCHAR(2) PRIMARY KEY,
    [LB_FAMILLEARTICLE] TEXT NOT NULL
);

/* Table : Article */
CREATE TABLE IF NOT EXISTS [Article] (
    [ID_REFERENCE] VARCHAR(11) PRIMARY KEY,
    [DATEINREFERENCE] DATE NOT NULL,
    [ID_SRCFILEARTICLE] CHAR(1) NOT NULL,
    [DESIGNATION] VARCHAR(500) NOT NULL,
    [DATENDREFERENCE] DATE,
    [ID_CLASSFAMILLE] VARCHAR(2) NOT NULL,
    [INFORMATION] TEXT NULL,
    FOREIGN KEY (ID_SRCFILEARTICLE) REFERENCES T_Src_File_Article(ID_SRCFILEARTICLE)
    FOREIGN KEY (ID_CLASSFAMILLE) REFERENCES Famille_Article(ID_CLASSFAMILLE)
);

/* Table : SRCFILEARTICLE */

CREATE TABLE [T_Src_File_Article] (
    [ID_SRCFILEARTICLE] CHAR PRIMARY KEY,
    [SRCFILEARTICLE_COMMENT] VARCHAR(10) NOT NULL UNIQUE,
    [SRCFILEARTICLE_DESCR] TEXT NOT NULL
);

/* Table : Prix_Article_Imporateur */
CREATE TABLE [Prix_Article_Imporateur] (
    [ID_PRIXIMPORT] TEXT PRIMARY KEY, /*YEAR+COUNT*/
    [DATE_PRIXIMPORT] DATE NOT NULL,
    [ID_REFERENCE] VARCHAR(11) NOT NULL,
    [ANC_PRIXIMPORT] REAL,
    [NOUV_PRIXIMPORT] REAL,
    [ID_SRCFILEARTICLE] CHAR(1) NOT NULL,
    FOREIGN KEY (ID_SRCFILEARTICLE) REFERENCES T_Src_File_Article(ID_SRCFILEARTICLE)
    FOREIGN KEY (ID_REFERENCE) REFERENCES Article(ID_REFERENCE)
);

-- ============================================================
--   TRIGGER : Prix_Article_Imporateur_Index
-- ============================================================

CREATE TRIGGER IF NOT EXISTS increment_counter_and_set_prix_article_importateur_id
AFTER INSERT ON Prix_Article_Imporateur
FOR EACH ROW
BEGIN
    UPDATE Prix_Article_Imporateur
    SET ID_PRIXIMPORT = (SELECT strftime('%Y', 'now') || printf('%08d', ifnull(max(cast(substr(ID_PRIXIMPORT,5) as integer)),0)+1) FROM Prix_Article_Imporateur WHERE substr(ID_PRIXIMPORT,1,4) = strftime('%Y', 'now'))
    WHERE rowid=NEW.rowid;
END;

/* Table : Prix_Article_Client */
CREATE TABLE [Prix_Article_Client] (
    [ID_PRIXCLIENT] TEXT PRIMARY KEY, /*YEAR+COUNT*/
    [DATE_PRIXCLIENT] DATE NOT NULL,
    [ID_REFERENCE] VARCHAR(11) NOT NULL,
    [ANC_PRIXCLIENT] REAL,
    [NOUV_PRIXCLIENT] REAL,
    [ID_SRCFILEARTICLE] CHAR(1) NOT NULL,
    FOREIGN KEY (ID_SRCFILEARTICLE) REFERENCES T_Src_File_Article(ID_SRCFILEARTICLE)
    FOREIGN KEY (ID_REFERENCE) REFERENCES Article(ID_REFERENCE)
);

-- ============================================================
--   TRIGGER : Prix_Article_Client_Index
-- ============================================================

CREATE TRIGGER IF NOT EXISTS increment_counter_and_set_prix_article_client_id
AFTER INSERT ON [Prix_Article_Client]
FOR EACH ROW
BEGIN
    UPDATE [Prix_Article_Client]
    SET ID_PRIXCLIENT = (SELECT strftime('%Y', 'now') || printf('%08d',
    ifnull(max(cast(substr(ID_PRIXCLIENT,5) as integer)),0)+1) FROM
    Prix_Article_Client WHERE substr(ID_PRIXCLIENT,1,4) = strftime('%Y', 'now'))
    WHERE rowid=NEW.rowid;
END;

Here is the model I created from the command:

python manage.py inspectdb > models.py

class Article(models.Model):
    id_reference = models.CharField(db_column='ID_REFERENCE', primary_key=True, blank=True, null=True)  # Field name made lowercase.
    dateinreference = models.DateField(db_column='DATEINREFERENCE')  # Field name made lowercase.
    id_srcfilearticle = models.ForeignKey('TSrcFileArticle', models.DO_NOTHING, db_column='ID_SRCFILEARTICLE')  # Field name made lowercase.
    designation = models.CharField(db_column='DESIGNATION')  # Field name made lowercase.
    datendreference = models.DateField(db_column='DATENDREFERENCE', blank=True, null=True)  # Field name made lowercase.
    id_classfamille = models.ForeignKey('FamilleArticle', models.DO_NOTHING, db_column='ID_CLASSFAMILLE')  # Field name made lowercase.
    information = models.TextField(db_column='INFORMATION', blank=True, null=True)  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'Article'


class FamilleArticle(models.Model):
    id_classfamille = models.CharField(db_column='ID_CLASSFAMILLE', primary_key=True, blank=True, null=True)  # Field name made lowercase.
    lb_famillearticle = models.TextField(db_column='LB_FAMILLEARTICLE')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'Famille_Article'


class PrixArticleClient(models.Model):
    id_prixclient = models.TextField(db_column='ID_PRIXCLIENT', primary_key=True, blank=True, null=True)  # Field name made lowercase.
    date_prixclient = models.DateField(db_column='DATE_PRIXCLIENT')  # Field name made lowercase.
    id_reference = models.ForeignKey(Article, models.DO_NOTHING, db_column='ID_REFERENCE')  # Field name made lowercase.
    anc_prixclient = models.FloatField(db_column='ANC_PRIXCLIENT', blank=True, null=True)  # Field name made lowercase.
    nouv_prixclient = models.FloatField(db_column='NOUV_PRIXCLIENT', blank=True, null=True)  # Field name made lowercase.
    id_srcfilearticle = models.ForeignKey('TSrcFileArticle', models.DO_NOTHING, db_column='ID_SRCFILEARTICLE')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'Prix_Article_Client'


class PrixArticleImporateur(models.Model):
    id_priximport = models.TextField(db_column='ID_PRIXIMPORT', primary_key=True, blank=True, null=True)  # Field name made lowercase.
    date_priximport = models.DateField(db_column='DATE_PRIXIMPORT')  # Field name made lowercase.
    id_reference = models.ForeignKey(Article, models.DO_NOTHING, db_column='ID_REFERENCE')  # Field name made lowercase.
    anc_priximport = models.FloatField(db_column='ANC_PRIXIMPORT', blank=True, null=True)  # Field name made lowercase.
    nouv_priximport = models.FloatField(db_column='NOUV_PRIXIMPORT', blank=True, null=True)  # Field name made lowercase.
    id_srcfilearticle = models.ForeignKey('TSrcFileArticle', models.DO_NOTHING, db_column='ID_SRCFILEARTICLE')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'Prix_Article_Imporateur'


class TSrcFileArticle(models.Model):
    id_srcfilearticle = models.CharField(db_column='ID_SRCFILEARTICLE', primary_key=True, blank=True, null=True)  # Field name made lowercase.
    srcfilearticle_comment = models.CharField(db_column='SRCFILEARTICLE_COMMENT')  # Field name made lowercase.
    srcfilearticle_descr = models.TextField(db_column='SRCFILEARTICLE_DESCR')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'T_Src_File_Article'

You can notice that the triggers are not imported into the models.py file.My goal is to obtain the following query:
Example1

ID_REFERENCE DATEINREFERENCE ID_SRCFILEARTICLE DESIGNATION
83302356857 2024-02-15 N SUPPORT MODULHEBER HV
83302356858 2024-05-15 U DISPOSTIF MODULHEBER
83302356943 2024-02-15 N CLÉ HAKEN A-D
83302356945 2024-05-15 N MESSHILFE LASER

Example2:

ID_PRIXIMPORT DATE_PRIXIMPORT ID_REFERENCE ANC_PRIXIMPORT NOUV_PRIXIMPORT ID_SRCFILEARTICLE
202400039005 2024-02-15 71245B3BB76 12.95 12.69
202400039006 2024-02-15 71245B3BB76 12.69 12.69
202400039007 2024-02-15 71245B3BB77 8.32 8.16
202400039008 2024-02-15 71245B3BB77 8.16 8.16
6

Correct. Triggers are not in any way managed or handled by the ORM. They exist solely in the database. That, by itself, should not cause any problems.

Side note: To avoid confusion and reduce redundancy in naming, I’d strongly suggest removing the id_ prefix from the foreign key fields.

Otherwise, this all looks good.

So, what are the problems that you are facing?

7

Additionally, I need to load the existing data from the SQLite database. My question is how to implement this model in Django with these different types of IDs. :slight_smile: I don’t know where to start. I find this step difficult.

8

I don’t know what you mean by this. Your models give you access to those tables.

I’m getting the impression that you’re trying to make this more difficult than it really is.

What I would suggest is that now that you have your model defined, that you use the Django shell to play with some queries to see what results you can get.

9

I find this step difficult. If I insist on keeping these IDs, will I not encounter problems as the code evolves?

10

Why would you?

Keep in mind that the Django ORM is not the database, it is a layer on top of the database, creating an interface between your application and the physical tables.

12

“Given the difficulty I’m having with Django, I have one last thing left: how to implement triggers or something equivalent, as I have no idea how to do it. Do you have any code examples, as I’m eager to move on with the application development?”

13

Django does nothing directly with triggers. You implement them in the database as you would typically do.

14

Alright, in which file of the Django project should I write these triggers, so that when I run migrate, Django doesn’t erase my changes and implements its own code instead?

15

Code example generate by ChatGPT:
1- Create a Custom Auto Increment Field

# fields.py
from django.db import models
from datetime import datetime

class CustomIDField(models.CharField):
    def __init__(self, *args, **kwargs):
        kwargs['max_length'] = 12  # 4 for year + 8 for the counter
        super().__init__(*args, **kwargs)

    def pre_save(self, model_instance, add):
        if add:
            current_year = datetime.now().year
            last_instance = model_instance.__class__.objects.order_by('-id').first()
            if last_instance and last_instance.id.startswith(str(current_year)):
                counter = int(last_instance.id[4:]) + 1
            else:
                counter = 1
            new_id = f"{current_year}{str(counter).zfill(8)}"
            setattr(model_instance, self.attname, new_id)
            return new_id
        return super().pre_save(model_instance, add)

2- Use the Custom Field in Your Model:

# models.py
from django.db import models
from .fields import CustomIDField

class MyModel(models.Model):
    id = CustomIDField(primary_key=True, editable=False)
    name = models.CharField(max_length=100)
    # other fields...

3- Verify the Behavior

# shell or view
from myapp.models import MyModel

# Creating new instances
instance1 = MyModel.objects.create(name="Test 1")
print(instance1.id)  # Should print something like 202400000001

instance2 = MyModel.objects.create(name="Test 2")
print(instance2.id)  # Should print something like 202400000002

This implementation ensures that each new instance of MyModel has a unique identifier in the format “Year + 8-position counter”, starting from 1 each year. The custom field is defined such that it automatically generates this ID when a new instance is created.

Comment ? suggestion ?.

16

You’re running on an existing database. Why would you be using migrate? Your model definitions have managed = False - Django isn’t going to do anything with those models.

I have found that ChatGPT is fundamentally useless to the point of being dangerous with Django. I’m not going to bother to review or comment on code generated by it, it’s not worth my time.