Python Django框架连接金仓数据库_django 适配金仓数据库

Kingbase（金仓数据库）是北京人大金仓信息技术股份有限公司经过多年努力，自主研制开发的具有自主知识产权的通用关系型数据库管理系统，属于国产数据库，在与其他软件的连接上参考资料较少。作者由于实验要求使用此数据库开发，期间遇到很多问题，最后经过老师的一篇文档完成实验。写下此文章以便参考。

一、安装软件

Python 3.6
Django 1.8
KingbaseES 7.0

1.安装python

下载地址： https://www.python.org/downloads/
在此网站下载python 并安装。一定要勾选pip。注意勾选第一个安装界面的下部分加入path的选项。
图片：

2.安装Django

• 在命令提示符下用安装django：
A. 打开命令提示符窗口(win+R,输入cmd)
B. 执行django 安装命令： pip install Django

3.安装psycopg2

使用pip install psycopg2 安装

4.安装kingbase ES 7.0数据库

(1) 安装数据库
(2) 初始化数据库
使用下图所示的“数据库初始化工具”。 完成后，任务管理器的“服务”会发现对应的kingbase_instance。

具体安装教程参照kingbase相关文件。

二、Django项目开发

1. 使用django管理工具 django-admin.py在想要的文件目录下(如D:\djangoProject)创建一个项目HelloWord

django-admin.py startproject HelloWorld
生成结果如下：

2.在项目HelloWorld中创建叫TestModel 的app

使用命令 django-admin startapp TestModel

配置TestModel参照：http://www.runoob.com/django/django-tutorial.html

3.5. 在项目配置文件setting.py中配置kingbase数据库（本文重点）

Django中内置了多种数据库，如postgresql、oracle、mysql，如果使用kingbase数据库，需要自己定制数据库后台，详见第三部分：定制数据库后台。
HelloWorld/HelloWorld/settings.py: 文件代码

DATABASES = {
    'default': {
        ‘ENGINE’: ‘TestModel.backends.kingbase’,     # kingbase
        ‘NAME’: ‘HELLO’,                          # 数据库名，已创建
        ‘USER’: ‘SYSTEM’,                         #用户名
        ‘PASSWORD’: ‘123456’,                    #密码
        ‘HOST’: ‘127.0.0.1’,                       #服务器所在ip
        ‘PORT’: ‘54321’,                          #端口号
    }
}

1
2
3
4
5
6
7
8
9
10
11

4.同步model到数据库

A .使用python manage.py makemigrations , 通知Django修改Model， TestModel\migrations下生成0001_initial.py

B. python manage.py sqlmigrate , 查看model对应的SQL语句

C . python manage.py migrate , 执行SQL语句到数据库

注意：如果使用命令行语句不能自动执行，则需要在python平台手动执行，对于第三步则需要将生成的0001_initial中SQL语句复制到数据库中执行。

查看kingbase中HELLO数据库，发现自动生成数据库表。
7 在view中使用model，渲染相应的模板(html)
8 在url中创建请求和响应(view)的映射
9 将在模板(html)中使用Form表单简化html控件
7.8.9同网上参考教程.

三、定制后台数据库

由于kingbase的底层是postgresql，使用psycog2能够连接kingbase，因此我们继承已有的postgresql数据库后台，修改kingbase特有的部分。

A． 在TestModel目录下创建backends\kingbase文件夹，在kingbase文件夹下创建3个文件： init.py，base.py，introspection.py
B． init.py 内容为空，告诉python这是python文件而已。
C． base.py 中代码如下：

from django.db.backends.postgresql import base     # 从postgresql中导入base
from .introspection import DatabaseIntrospection   #从introspection.py导入DatabaseIntrospection类
class DatabaseWrapper(base.DatabaseWrapper):    #继承postgresql的DatabaseWrapper
data_types = {
        'AutoField': 'int identity(1,1)',     #设置 Model Field和kingbase数据类型的转换 
        'BigAutoField': 'bigserial',
        'BinaryField': 'bytea',
        'BooleanField': 'boolean',
        'CharField': 'varchar(%(max_length)s)',
        'CommaSeparatedIntegerField': 'varchar(%(max_length)s)',
        'DateField': 'date',
        'DateTimeField': 'timestamp with time zone',
        'DecimalField': 'numeric(%(max_digits)s, %(decimal_places)s)',
        'DurationField': 'interval',
        'FileField': 'varchar(%(max_length)s)',
        'FilePathField': 'varchar(%(max_length)s)',
        'FloatField': 'double precision',
        'IntegerField': 'integer',
        'BigIntegerField': 'bigint',
        'IPAddressField': 'inet',
        'GenericIPAddressField': 'inet',
        'NullBooleanField': 'boolean',
        'OneToOneField': 'integer',
        'PositiveIntegerField': 'integer',
        'PositiveSmallIntegerField': 'smallint',
        'SlugField': 'varchar(%(max_length)s)',
        'SmallIntegerField': 'smallint',
        'TextField': 'text',
        'TimeField': 'time',
        'UUIDField': 'uuid',
    }
    
    introspection_class = DatabaseIntrospection   # introspection_class类使用introspection.py中DatabaseIntrospection类
    
    
    def get_new_connection(self, conn_params):   
        conn = super(DatabaseWrapper,
        self).get_new_connection(conn_params) 
        return conn
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

D． introspection.py 中代码如下：
主要修改kingbase与postgresql不同的系统命名表(postgresql中用pg_作前缀，kingbase中用sys_做前缀)和其他不一致的地方。

from django.db.backends.base.introspection import  TableInfo
from django.db.backends.postgresql import introspection

class DatabaseIntrospection(introspection.DatabaseIntrospection):

    _get_indexes_query = """
        SELECT attr.attname, idx.indkey, idx.indisunique, idx.indisprimary
        FROM sys_catalog.sys_class c, sys_catalog.sys_class c2,
            sys_catalog.sys_index idx, sys_catalog.sys_attribute attr
        WHERE c.oid = idx.indrelid
            AND idx.indexrelid = c2.oid
            AND attr.attrelid = c.oid
            AND attr.attnum = idx.indkey[0]
            AND c.relname = %s"""
            
    def get_table_list(self, cursor):
        """
        Returns a list of table and view names in the current database.
        """
        cursor.execute("""
            SELECT c.relname, c.relkind
            FROM sys_catalog.sys_class c
            LEFT JOIN sys_catalog.sys_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r', 'v')
                AND n.nspname NOT IN ('sys_catalog', 'sys_toast')
                AND sys_catalog.sys_table_is_visible(c.oid)""")
        return [TableInfo(row[0], {'r': 't', 'v': 'v'}.get(row[1]))
                for row in cursor.fetchall()
                if row[0] not in self.ignored_tables]
                
                


    def get_relations(self, cursor, table_name):
        """
        Returns a dictionary of {field_name: (field_name_other_table, other_table)}
        representing all relationships to the given table.
        """
        cursor.execute("""
            SELECT c2.relname, a1.attname, a2.attname
            FROM sys_constraint con
            LEFT JOIN sys_class c1 ON con.conrelid = c1.oid
            LEFT JOIN sys_class c2 ON con.confrelid = c2.oid
            LEFT JOIN sys_attribute a1 ON c1.oid = a1.attrelid AND a1.attnum = con.conkey[1]
            LEFT JOIN sys_attribute a2 ON c2.oid = a2.attrelid AND a2.attnum = con.confkey[1]
            WHERE c1.relname = %s
                AND con.contype = 'f'""", [table_name])
        relations = {}
        for row in cursor.fetchall():
            relations[row[1]] = (row[2], row[0])
        return relations
        


    def get_constraints(self, cursor, table_name):
        """
        Retrieve any constraints or keys (unique, pk, fk, check, index) across
        one or more columns. Also retrieve the definition of expression-based
        indexes.
        """
        constraints = {}
        # Loop over the key table, collecting things as constraints. The column
        # array must return column names in the same order in which they were
        # created.
        # The subquery containing generate_series can be replaced with
        # "WITH ORDINALITY" when support for PostgreSQL 9.3 is dropped.
        cursor.execute("""
            SELECT
                c.conname,
                array(
                    SELECT attname
                    FROM (
                        SELECT unnest(c.conkey) AS colid,
                               generate_series(1, array_length(c.conkey, 1)) AS arridx
                    ) AS cols
                    JOIN sys_attribute AS ca ON cols.colid = ca.attnum
                    WHERE ca.attrelid = c.conrelid
                    ORDER BY cols.arridx
                ),
                c.contype,
                (SELECT fkc.relname || '.' || fka.attname
                FROM sys_attribute AS fka
                JOIN sys_class AS fkc ON fka.attrelid = fkc.oid
                WHERE fka.attrelid = c.confrelid AND fka.attnum = c.confkey[1]),
                cl.reloptions
            FROM sys_constraint AS c
            JOIN sys_class AS cl ON c.conrelid = cl.oid
            JOIN sys_namespace AS ns ON cl.relnamespace = ns.oid
            WHERE ns.nspname = %s AND cl.relname = %s
        """, ["public", table_name])
        for constraint, columns, kind, used_cols, options in cursor.fetchall():
            constraints[constraint] = {
                "columns": columns,
                "primary_key": kind == "p",
                "unique": kind in ["p", "u"],
                "foreign_key": tuple(used_cols.split(".", 1)) if kind == "f" else None,
                "check": kind == "c",
                "index": False,
                "definition": None,
                "options": options,
            }
        # Now get indexes
        # The row_number() function for ordering the index fields can be
        # replaced by WITH ORDINALITY in the unnest() functions when support
        # for PostgreSQL 9.3 is dropped.
        cursor.execute("""
            SELECT
                indexname, array_agg(attname), indisunique, indisprimary,
                array_agg(ordering), amname, exprdef, s2.attoptions
            FROM (
                SELECT
                    row_number() OVER () as rnum, c2.relname as indexname,
                    idx.*, attr.attname, am.amname,
                    CASE
                        WHEN idx.indexprs IS NOT NULL THEN
                            sys_get_indexdef(idx.indexrelid)
                    END AS exprdef,
                    CASE am.amname
                        WHEN 'btree' THEN
                            CASE (option & 1)
                                WHEN 1 THEN 'DESC' ELSE 'ASC'
                            END
                    END as ordering,
                    c2.reloptions as attoptions
                FROM (
                    SELECT
                        *, unnest(i.indkey) as key, unnest(i.indoption) as option
                    FROM sys_index i
                ) idx
                LEFT JOIN sys_class c ON idx.indrelid = c.oid
                LEFT JOIN sys_class c2 ON idx.indexrelid = c2.oid
                LEFT JOIN sys_am am ON c2.relam = am.oid
                LEFT JOIN sys_attribute attr ON attr.attrelid = c.oid AND attr.attnum = idx.key
                WHERE c.relname = %s
            ) s2
            GROUP BY indexname, indisunique, indisprimary, amname, exprdef, attoptions;
        """, [table_name])
        for index, columns, unique, primary, orders, type_, definition, options in cursor.fetchall():
            if index not in constraints:
                constraints[index] = {
                    "columns": columns if columns != [None] else [],
                    "orders": orders if orders != [None] else [],
                    "primary_key": primary,
                    "unique": unique,
                    "foreign_key": None,
                    "check": False,
                    "index": True,
                    "type": Index.suffix if type_ == 'btree' else type_,
                    "definition": definition,
                    "options": options,
                }
        return constraints
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152

至此，Django搭建完成，读者可以根据自己的需求开发相应的系统。