Django 2.2 with Oracle 11g to resolve version conflict

Keywords: Django Anaconda Oracle SQL

The last time we used Django 2.2 and Oracle 11g, there was a version conflict during migrate, and finally Upgrade Oracle to 12c to solve the problem

So can we solve this conflict in other ways? Think of a solution and practice it:

Using Django 2.2 to migrate under Oracle 12C environment to create basic tables
Export the base tables and import them into Oracle 11g database
Connect Oracle 11g with Django 2.2

Implementation steps

1. Using Django 2.2 to migrate under Oracle 12C environment to create basic tables

This is done in the previous article. Connecting to the database, you can see 10 basic tables.

Looking at a table, such as the AUTH_GROUP table, it is found that an ID field is generated using a 12c-specific syntax. In addition to DJANGO_SESSION, each table has a self-increasing sequence of ID fields as the primary key.

-- Create table
create table AUTH_GROUP
(
  id   NUMBER(11) generated by default on null as identity,
  name NVARCHAR2(150)
)
tablespace DJANGO;
-- Create/Recreate primary, unique and foreign key constraints 
alter table AUTH_GROUP
  add primary key (ID)
  using index 
  tablespace DJANGO;
alter table AUTH_GROUP
  add unique (NAME)
  using index 
  tablespace DJANGO;

2. Export the basic tables and import them into Oracle 11g database

Export django user database, pay attention to using 11g version

Then import to 11g database, very smoothly

Looking at the AUTH_GROUP table, we find that the structure of the table is the same, but the default value of the self-increasing sequence on id is missing.

-- Create table
create table AUTH_GROUP
(
  id   NUMBER(11) not null,
  name NVARCHAR2(150)
)
tablespace DJANGO;
-- Create/Recreate primary, unique and foreign key constraints 
alter table AUTH_GROUP
  add primary key (ID)
  using index 
  tablespace DJANGO;
alter table AUTH_GROUP
  add unique (NAME)
  using index 
  tablespace DJANGO;

3. Connect Oracle 11g with Django 2.2

Modify the settings file, connect Oracle11g, and then start the django service, and it started successfully.

However, when creating admin user password, an error is reported, ORA-01400: cannot insert NULL in ("DJANGO", "AUTH_USER". "ID").

PS D:\parttime\python\django\guanxiangzhiji> python manage.py createsuperuser
//User name (leave blank to use'administrator'):
//E-mail address:
Password:
Password (again):
//The password length is too short. The password must contain at least eight characters.
//This password is too common.
Bypass password validation and create user anyway? [y/N]: y
Traceback (most recent call last):
  File "D:\app\anaconda\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "D:\app\anaconda\lib\site-packages\django\db\backends\oracle\base.py", line 510, in execute
    return self.cursor.execute(query, self._param_generator(params))
cx_Oracle.IntegrityError: ORA-01400: cannot insert NULL into ("DJANGO"."AUTH_USER"."ID")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "D:\app\anaconda\lib\site-packages\django\core\management\__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "D:\app\anaconda\lib\site-packages\django\core\management\__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "D:\app\anaconda\lib\site-packages\django\core\management\base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "D:\app\anaconda\lib\site-packages\django\contrib\auth\management\commands\createsuperuser.py", line 61, in execute
    return super().execute(*args, **options)
  File "D:\app\anaconda\lib\site-packages\django\core\management\base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "D:\app\anaconda\lib\site-packages\django\contrib\auth\management\commands\createsuperuser.py", line 156, in handle
    self.UserModel._default_manager.db_manager(database).create_superuser(**user_data)
  File "D:\app\anaconda\lib\site-packages\django\contrib\auth\models.py", line 162, in create_superuser
    return self._create_user(username, email, password, **extra_fields)
  File "D:\app\anaconda\lib\site-packages\django\contrib\auth\models.py", line 145, in _create_user
    user.save(using=self._db)
  File "D:\app\anaconda\lib\site-packages\django\contrib\auth\base_user.py", line 66, in save
    super().save(*args, **kwargs)
  File "D:\app\anaconda\lib\site-packages\django\db\models\base.py", line 741, in save
    force_update=force_update, update_fields=update_fields)
  File "D:\app\anaconda\lib\site-packages\django\db\models\base.py", line 779, in save_base
    force_update, using, update_fields,
  File "D:\app\anaconda\lib\site-packages\django\db\models\base.py", line 870, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "D:\app\anaconda\lib\site-packages\django\db\models\base.py", line 908, in _do_insert
    using=using, raw=raw)
  File "D:\app\anaconda\lib\site-packages\django\db\models\manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "D:\app\anaconda\lib\site-packages\django\db\models\query.py", line 1186, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "D:\app\anaconda\lib\site-packages\django\db\models\sql\compiler.py", line 1335, in execute_sql
    cursor.execute(sql, params)
  File "D:\app\anaconda\lib\site-packages\django\db\backends\utils.py", line 99, in execute
    return super().execute(sql, params)
  File "D:\app\anaconda\lib\site-packages\django\db\backends\utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "D:\app\anaconda\lib\site-packages\django\db\backends\utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "D:\app\anaconda\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "D:\app\anaconda\lib\site-packages\django\db\utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "D:\app\anaconda\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "D:\app\anaconda\lib\site-packages\django\db\backends\oracle\base.py", line 510, in execute
    return self.cursor.execute(query, self._param_generator(params))
django.db.utils.IntegrityError: ORA-01400: cannot insert NULL into ("DJANGO"."AUTH_USER"."ID")

Cause analysis
Obviously, when inserting into the AUTH_USER table, no value of the ID is specified, and the ID is the primary key, not empty.
Because in the 12c environment, this ID is a self-increasing sequence, the insert statement does not need to specify this value.

Solution
Solutions have emerged as the times require, as long as each ID column to create a 11g sequence, create triggers, when inserting data to fill in the ID value.
(1) Generating sequence.
Using sql statement

select 'create sequence seq_'||table_name||' minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;'
  from user_tab_columns
  where column_name='ID';

Generate and execute batch execution statements for creating sequences.

create sequence seq_DJANGO_ADMIN_LOG minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;
create sequence seq_AUTH_USER minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;
create sequence seq_AUTH_USER_GROUPS minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;
create sequence seq_DJANGO_CONTENT_TYPE minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;
create sequence seq_AUTH_GROUP minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;
create sequence seq_AUTH_GROUP_PERMISSIONS minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;
create sequence seq_DJANGO_MIGRATIONS minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;
create sequence seq_AUTH_PERMISSION minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;
create sequence seq_AUTH_USER_USER_PERMISSIONS minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;

(2) Create triggers
Using SQL statements

select 'create or replace trigger tri_'||table_name||'
					before insert
					on '||table_name||' 
					for each row
				declare
				begin
					:new.id:=seq_'||table_name||'.nextval;
				end tri_'||table_name||';
				/'
  from user_tab_columns
  where column_name='ID';

Generate trigger scripts:

create or replace trigger tri_DJANGO_MIGRATIONS
	before insert
	on DJANGO_MIGRATIONS
	for each row
declare
begin
	:new.id:=seq_DJANGO_MIGRATIONS.nextval;
end tri_DJANGO_MIGRATIONS;
/
create or replace trigger tri_DJANGO_CONTENT_TYPE
	before insert
	on DJANGO_CONTENT_TYPE
	for each row
declare
begin
	:new.id:=seq_DJANGO_CONTENT_TYPE.nextval;
end tri_DJANGO_CONTENT_TYPE;
/
create or replace trigger tri_AUTH_PERMISSION
	before insert
	on AUTH_PERMISSION
	for each row
declare
begin
	:new.id:=seq_AUTH_PERMISSION.nextval;
end tri_AUTH_PERMISSION;
/
create or replace trigger tri_AUTH_GROUP
	before insert
	on AUTH_GROUP
	for each row
declare
begin
	:new.id:=seq_AUTH_GROUP.nextval;
end tri_AUTH_GROUP;
/
create or replace trigger tri_AUTH_GROUP_PERMISSIONS
	before insert
	on AUTH_GROUP_PERMISSIONS
	for each row
declare
begin
	:new.id:=seq_AUTH_GROUP_PERMISSIONS.nextval;
end tri_AUTH_GROUP_PERMISSIONS;
/
create or replace trigger tri_AUTH_USER
	before insert
	on AUTH_USER
	for each row
declare
begin
	:new.id:=seq_AUTH_USER.nextval;
end tri_AUTH_USER;
/
create or replace trigger tri_AUTH_USER_GROUPS
	before insert
	on AUTH_USER_GROUPS
	for each row
declare
begin
	:new.id:=seq_AUTH_USER_GROUPS.nextval;
end tri_AUTH_USER_GROUPS;
/
create or replace trigger tri_AUTH_USER_USER_PERMISSIONS
	before insert
	on AUTH_USER_USER_PERMISSIONS
	for each row
declare
begin
	:new.id:=seq_AUTH_USER_USER_PERMISSIONS.nextval;
end tri_AUTH_USER_USER_PERMISSIONS;
/
create or replace trigger tri_DJANGO_ADMIN_LOG
	before insert
	on DJANGO_ADMIN_LOG
	for each row
declare
begin
	:new.id:=seq_DJANGO_ADMIN_LOG.nextval;
end tri_DJANGO_ADMIN_LOG;
/

(3) Creating admin users at this time is successful

New user lurenjia succeeded!

Posted by PBD817 on Sat, 17 Aug 2019 05:16:59 -0700

Programmer Group

Django 2.2 with Oracle 11g to resolve version conflict

Implementation steps

1. Using Django 2.2 to migrate under Oracle 12C environment to create basic tables

2. Export the basic tables and import them into Oracle 11g database

3. Connect Oracle 11g with Django 2.2

Hot Keywords