python - Django 模型从旧版 mysql 数据库自动生成：我是否必须从 models.DO_NOTHING 更改 on_delete 参数的值

Question

我为mysql数据库编写了一个sql文件，如下所示：

create table bank (
    bankname    VARCHAR(20)     not null,
    city        VARCHAR(20)     not null,
    money       DOUBLE          not null,
    constraint PK_BANK primary key (bankname)
);

create table department (
    departID        CHAR(4)         not null,
    departname      VARCHAR(20)     not null,
    departtype      VARCHAR(15),
    manager         CHAR(18)        not null,
    bank            VARCHAR(20)     not null,
    constraint PK_DEPARTMENT primary key (departID),
    Constraint FK_BANK_DEPART Foreign Key(bank) References bank(bankname)
);

create table employee (
    empID           CHAR(18)        not null,
    empname         VARCHAR(20)     not null,
    empphone        CHAR(11),
    empaddr         VARCHAR(50),
    emptype         CHAR(1),
    empstart        DATE            not null,
    depart          CHAR(4),
    constraint PK_EMPLOYEE primary key (empID),
    Constraint FK_DEPART_EMP Foreign Key(depart) References department(departID),
    Constraint CK_EMPTYPE Check(emptype IN ('0','1'))
);

create table customer (
    cusID           CHAR(18)        not null,
    cusname         VARCHAR(10)     not null,
    cusphone        CHAR(11)        not null,
    address         VARCHAR(50),
    contact_phone   CHAR(11)        not null,
    contact_name    VARCHAR(10)     not null,
    contact_Email   VARCHAR(20),
    relation        VARCHAR(10)     not null,
    loanres         CHAR(18),
    accres          CHAR(18),
    constraint PK_CUSTOMER primary key (cusID),
    Constraint FK_CUS_LOANRES Foreign Key(loanres) References employee(empID),
    Constraint FK_CUS_ACCRES Foreign Key(accres) References employee(empID)
);

create table accounts(
    accountID       CHAR(6)         not null,
    money           DOUBLE          not null,
    settime         DATETIME,
    accounttype     VARCHAR(10),
    constraint PK_ACC primary key (accountID),
    Constraint CK_ACCOUNTTYPE Check(accounttype IN ('SaveAccount','CheckAccount'))
);

create table saveacc (
    accountID       CHAR(6)         not null,
    interestrate    float,
    savetype        CHAR(1),
    constraint PK_SAVEACC primary key (accountID),
    Constraint FK_SAVE_ACC Foreign Key(accountID) References accounts(accountID) On Delete Cascade
);

create table checkacc (
    accountID       CHAR(6)         not null, 
    overdraft       DOUBLE,
    constraint PK_CHECKACC primary key (accountID),
    Constraint FK_CHECK_ACC Foreign Key(accountID) References accounts(accountID) On Delete Cascade
);

create table cusforacc (
    accountID       CHAR(6)         not null,
    bank            VARCHAR(20),
    cusID           CHAR(18)        not null,
    visit           DATETIME,
    accounttype     VARCHAR(10),
    constraint PK_CUSACC primary key (accountID, cusID),
    Constraint FK_BANK_ACCOUT Foreign Key(bank) References bank(bankname),
    Constraint FK_CUS Foreign Key(cusID) References customer(cusID),
    Constraint FK_CUS_ACC Foreign Key(accountID) References accounts(accountID) On Delete Cascade,
    Constraint UK Unique Key(bank, cusID, accounttype)
);

create table loan (
    loanID          CHAR(4)         not null,
    money           DOUBLE,
    bank            VARCHAR(20),
    state           CHAR(1)         default '0',
    constraint PK_LOAN primary key (loanID),
    Constraint FK_BANK_LOAN Foreign Key(bank) References bank(bankname)
);

create table cusforloan (
    loanID          CHAR(4),
    cusID           CHAR(18),
    constraint PK_CUSLOAN primary key (loanID, cusID),
    Constraint FK_LOAN Foreign Key(loanID) References loan(loanID) On Delete Cascade,
    Constraint FK_CUSL Foreign Key(cusID) References customer(cusID)
);

create table payinfo (
    loanID          CHAR(4),
    cusID           CHAR(18),
    money           DOUBLE,
    paytime         DATETIME,
    constraint PK_PAYINFO primary key (loanID, cusID, money, paytime),
    Constraint FK_PAY_LOAN Foreign Key(loanID) References loan(loanID) On Delete Cascade,
    Constraint FK_PAY_CUS Foreign Key(cusID) References customer(cusID)
);  

create view checkaccounts
as select accounts.accountID,bank,accounttype,money,settime,overdraft 
from accounts,checkacc,(select distinct accounts.accountID,bank from accounts,cusforacc where accounts.accountID=cusforacc.accountID) tmp 
where accounts.accountID=checkacc.accountID and accounts.accountID=tmp.accountID;

create view saveaccounts (accountID,bank,accounttype,money,settime,interestrate,savetype)
as select accounts.accountID,bank,accounttype,money,settime,interestrate,savetype
from accounts,saveacc,(select distinct accounts.accountID,bank from accounts,cusforacc where accounts.accountID=cusforacc.accountID) tmp 
where accounts.accountID=saveacc.accountID and accounts.accountID=tmp.accountID;

create view checkstat (bank, totalmoney,totalcustomer)
as select t1.bank,totalmoney,totalcustomer 
from (select bank,sum(money) as totalmoney from checkaccounts group by bank) t1, 
(select bank,count(distinct cusID) as totalcustomer from cusforacc where accounttype='CheckAccount' group by bank) t2
where t1.bank=t2.bank;

create view savestat (bank, totalmoney,totalcustomer)
as select t1.bank,totalmoney,totalcustomer 
from (select bank,sum(money) as totalmoney from saveaccounts group by bank) t1, 
(select bank,count(distinct cusID) as totalcustomer from cusforacc where accounttype='SaveAccount' group by bank) t2
where t1.bank=t2.bank;

create view loanstat (bank, totalmoney,totalcustomer)
as select t1.bank,totalmoney,totalcustomer 
from (select bank,sum(money) as totalmoney from loan group by bank) t1, 
(select bank, count(distinct cusID) as totalcustomer 
from loan,cusforloan where loan.loanID=cusforloan.loanID group by bank) t2
where t1.bank=t2.bank;


DELIMITER //
create trigger loanDelete
Before delete on loan
for each row
begin
    declare a int;
    select state into a from loan where old.loanID=loan.loanID;
    if a = 1 then
        signal sqlstate 'HY000' set message_text = 'Loan in processing';
    end if;
end //
DELIMITER ;


DELIMITER //
create trigger loanState
After insert on payinfo
for each row
begin
    declare pay int;
    declare total int;
    select sum(money) into pay from payinfo where payinfo.loanID=new.loanID;
    select money into total from loan where loan.loanID=new.loanID;
    if pay > 0 and pay < total then
        update loan set state='1' where loan.loanID=new.loanID;
    elseif pay=total then
        update loan set state='2' where loan.loanID=new.loanID;
    elseif pay>total then
        delete from payinfo where payinfo.loanID=new.loanID and payinfo.cusID=new.cusID and payinfo.paytime=new.paytime and payinfo.money=new.money;
        signal sqlstate 'HY001' set message_text = 'Exceed the loan amount';
    end if;
end //
DELIMITER ;

然后我在 mysql 服务器中运行它以创建一个名为“BankSystem”的模式

之后，我在我的 django 项目中设置了 settings.py，如下所示：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'username',
        'PASSWORD': 'password',
        'NAME': 'BankSystem',
        'HOST': '127.0.0.1',
        'PORT': 3306
    }
}

然后在我的 cmd 运行python manage.py inspectdb > models.py中自动生成一个 models.py 文件，如下所示：

# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Make sure each ForeignKey and OneToOneField has `on_delete` set to the desired behavior
#   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
from django.db import models


class Accounts(models.Model):
    accountid = models.CharField(db_column='accountID', primary_key=True, max_length=6)  # Field name made lowercase.
    money = models.FloatField()
    settime = models.DateTimeField(blank=True, null=True)
    accounttype = models.CharField(max_length=10, blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'accounts'


class Bank(models.Model):
    bankname = models.CharField(primary_key=True, max_length=20)
    city = models.CharField(max_length=20)
    money = models.FloatField()

    class Meta:
        managed = False
        db_table = 'bank'


class Checkacc(models.Model):
    accountid = models.OneToOneField(Accounts, models.DO_NOTHING, db_column='accountID', primary_key=True)  # Field name made lowercase.
    overdraft = models.FloatField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'checkacc'


class Cusforacc(models.Model):
    accountid = models.OneToOneField(Accounts, models.DO_NOTHING, db_column='accountID', primary_key=True)  # Field name made lowercase.
    bank = models.ForeignKey(Bank, models.DO_NOTHING, db_column='bank', blank=True, null=True)
    cusid = models.ForeignKey('Customer', models.DO_NOTHING, db_column='cusID')  # Field name made lowercase.
    visit = models.DateTimeField(blank=True, null=True)
    accounttype = models.CharField(max_length=10, blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'cusforacc'
        unique_together = (('accountid', 'cusid'), ('bank', 'cusid', 'accounttype'),)


class Cusforloan(models.Model):
    loanid = models.OneToOneField('Loan', models.DO_NOTHING, db_column='loanID', primary_key=True)  # Field name made lowercase.
    cusid = models.ForeignKey('Customer', models.DO_NOTHING, db_column='cusID')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'cusforloan'
        unique_together = (('loanid', 'cusid'),)


class Customer(models.Model):
    cusid = models.CharField(db_column='cusID', primary_key=True, max_length=18)  # Field name made lowercase.
    cusname = models.CharField(max_length=10)
    cusphone = models.CharField(max_length=11)
    address = models.CharField(max_length=50, blank=True, null=True)
    contact_phone = models.CharField(max_length=11)
    contact_name = models.CharField(max_length=10)
    contact_email = models.CharField(db_column='contact_Email', max_length=20, blank=True, null=True)  # Field name made lowercase.
    relation = models.CharField(max_length=10)
    loanres = models.ForeignKey('Employee', models.DO_NOTHING, db_column='loanres', blank=True, null=True)
    accres = models.ForeignKey('Employee', models.DO_NOTHING, db_column='accres', blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'customer'


class Department(models.Model):
    departid = models.CharField(db_column='departID', primary_key=True, max_length=4)  # Field name made lowercase.
    departname = models.CharField(max_length=20)
    departtype = models.CharField(max_length=15, blank=True, null=True)
    manager = models.CharField(max_length=18)
    bank = models.ForeignKey(Bank, models.DO_NOTHING, db_column='bank')

    class Meta:
        managed = False
        db_table = 'department'


class Employee(models.Model):
    empid = models.CharField(db_column='empID', primary_key=True, max_length=18)  # Field name made lowercase.
    empname = models.CharField(max_length=20)
    empphone = models.CharField(max_length=11, blank=True, null=True)
    empaddr = models.CharField(max_length=50, blank=True, null=True)
    emptype = models.CharField(max_length=1, blank=True, null=True)
    empstart = models.DateField()
    depart = models.ForeignKey(Department, models.DO_NOTHING, db_column='depart', blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'employee'


class Loan(models.Model):
    loanid = models.CharField(db_column='loanID', primary_key=True, max_length=4)  # Field name made lowercase.
    money = models.FloatField(blank=True, null=True)
    bank = models.ForeignKey(Bank, models.DO_NOTHING, db_column='bank', blank=True, null=True)
    state = models.CharField(max_length=1, blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'loan'


class Payinfo(models.Model):
    loanid = models.OneToOneField(Loan, models.DO_NOTHING, db_column='loanID', primary_key=True)  # Field name made lowercase.
    cusid = models.ForeignKey(Customer, models.DO_NOTHING, db_column='cusID')  # Field name made lowercase.
    money = models.FloatField()
    paytime = models.DateTimeField()

    class Meta:
        managed = False
        db_table = 'payinfo'
        unique_together = (('loanid', 'cusid', 'money', 'paytime'),)


class Saveacc(models.Model):
    accountid = models.OneToOneField(Accounts, models.DO_NOTHING, db_column='accountID', primary_key=True)  # Field name made lowercase.
    interestrate = models.FloatField(blank=True, null=True)
    savetype = models.CharField(max_length=1, blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'saveacc'

您可以发现所有on_delete参数都models.DO_NOTHING如第 5 行中的注释所述：Make sure each ForeignKey and OneToOneField has on_delete set to the desired behavior

我的问题是我创建的数据库'BankSystem'已经设置了约束，我是否必须on_delete在models.py中设置模型参数以匹配数据库（因为我不想创建新数据库但使用数据库是由我以前的 sql 脚本创建的）。如果我不必on_delete在 models.py 中设置模型参数，数据库可以像我在我的 sql 脚本中设置的那样工作吗？换句话说，我希望 models.py 中的模型仅作为我以前创建的数据库的 API，而不是创建新数据库的源。

标签： pythonmysqldjango

Answer 1

在 cmdpython manage.py makemigrations和中使用命令后python manage.py migrate，我检查了 mysql 服务器，发现遗留数据库只添加了一些 django 需要的表，这个数据库中的以前的表没有改变。

因此，在这种情况下，对于遗留数据库，自动生成模型仅作为 API 应用于数据库，不会更改之前创建的表的结构。换句话说，我不必更改 on_delete 参数的值，只需将它们保留为 model.DO_NOTHING 在这种情况下似乎没问题。