小编给大家分享一下django2.2连oracle11g版本冲突怎么办,希望大家阅读完这篇文章后大所收获,下面让我们一起去探讨方法吧!
上次用django2.2和oracle11g,在migrate的时候发生了版本冲突,最终将Oracle升级到了12c才解决问题
那么到底能不能用别的方法来解决这个冲突呢?想了个解决思路,实践一下:
用django2.2连Oracle12c环境下做migrate,创建基础表
将基础表导出,再导入到Oracle11g数据库中
用django2.2连Oracle11g
实施步骤
1、用django2.2连Oracle12c环境下做migrate,创建基础表
在前文中已经完成,连接到数据库,可以看到有10张基础表
看一张表,比如AUTH_GROUP表,发现有个ID字段是用了12c特有的generated语法,除了DJANGO_SESSION外,其他每张表都有一个自增序列的id字段作为主键。
-- 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. 将基础表导出,再导入到Oracle11g数据库中
导出django用户数据库,注意使用11g版本
接着导入到11g数据库中,非常顺利
再看AUTH_GROUP表,发现表结构是一样的,但是id上面自增序列的默认值没有了。
-- 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、用django2.2连Oracle11g
修改settings文件,连Oracle11g,然后启动django服务,果然成功启动
但是,但是,创建admin用户密码的时候就报错了,ORA-01400: cannot insert NULL into (“DJANGO”.“AUTH_USER”.“ID”)
PS D:\parttime\python\django\guanxiangzhiji> python manage.py createsuperuser 用户名 (leave blank to use 'administrator'): 电子邮件地址: Password: Password (again): 密码长度太短。密码必须包含至少 8 个字符。 这个密码太常见了。 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")
原因分析
很明显,插入到AUTH_USER表时,没有指定ID的值,而ID是主键,非空。
因为在12c的环境下,这个ID是自增序列,insert语句中不需要指定这个值。
解决方案
解决方案也应运而出了,只要为每个ID列创建一个11g的序列,创建触发器,在插入数据时补上id值就行了。
(1)生成序列。
用sql语句
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';
生成创建序列的批量执行语句,并执行。
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)创建触发器
用SQL语句
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';
生成触发器脚本:
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)此时再创建admin用户,就成功了
新增用户lurenjia成功!
看完了这篇文章,相信你对django2.2连oracle11g版本冲突怎么办有了一定的了解,想了解更多相关知识,欢迎关注亿速云行业资讯频道,感谢各位的阅读!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。