手撸一个DjangoORM反向迁移

转载

mob604756fd5175 2021-07-23 13:40:00

情况摘要

近期遇到了这样一个情况：我现在有一个SQL server数据库要反向迁移到Django中。数据库中表的列名是"aA"的形式，要求返回的字段名和数据库的列名格式相同。但使用原生的反向迁移命令生成的models字段名都是小写的（"aa"）形式。这使得我用orm返回的字段格式就不符合要求了。最简单的解决方法就是重写返回结果或将models的字段名改成字段"db_column"属性的值。但我现有数据库中的表数量很多、字段很杂，手动操作很耗费时间（主要是我懒）。在网上搜寻未果后，决定自己写个反向迁移。

上代码

import pymssql
import os

#### settings
db = {
    'NAME': 'BRM_DB',
    'USER': 'kf',
    'PASSWORD': 'kf123',
    'HOST': '172.16.15.241',
    'PORT': '1433',
}
table_name_list = []    # 表名列表，如果为空则查询库中所有的表
address = ""            # 存放文件的位置，如果为空则为当前路径
#### settings_end

# 对结果集美化方法
def dictfetchall(cursor):
    # 获取游标描述
    desc = cursor.description
    return [
        dict(zip([col[0] for col in desc], row))
        for row in cursor.fetchall()
    ]


type = {
    'nvarchar': "CharField",
    'varchar': "CharField",
    'char': "CharField",
    'int': "IntegerField",
    'decimal': "DecimalField",
    'datetime': "DateTimeField",
    'real': "FloatField",
    "varbinary": "CharField",
    'text': "TextField",
    'date': "DateField",
    "datetime2": "DateTimeField",
    "float": "FloatField",
    "bit": "BooleanField",
    "smallint": "IntegerField",
    "pk": "AutoField",
}



conn = pymssql.connect(
    host=db['HOST'], user=db['USER'], password=db['PASSWORD'], database=db['NAME'])
cur = conn.cursor()
if not cur:
    raise (NameError, "连接数据库失败")
if not table_name_list:
    cur.execute("Select Name FROM SysObjects Where XType='U'")
    table_name_list = dictfetchall(cur)
    table_name_list = [i['Name'] for i in table_name_list]
print(table_name_list)
address = "models.py" if not address else os.path.join(address,'models.py')
with open(address, mode="w", encoding="utf-8") as f:
    f.write('from django.db import models\n')
    tc = []
    for table_name in table_name_list:  # table_name    表名
        if table_name in tc:
            continue
        else:
            tc.append(table_name)
            sql = """
            SELECT  CASE WHEN col.colorder = 1 THEN obj.name
                          ELSE ''
                     END AS 表名,
                col.colorder AS 序号 ,
                col.name AS 列名 ,
                ISNULL(ep.[value], '') AS 列说明 ,
                t.name AS 数据类型 ,
                col.length AS 长度 ,
                ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
                CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
                     ELSE ''
                END AS 标识 ,
                CASE WHEN EXISTS ( SELECT   1
                                   FROM     dbo.sysindexes si
                                            INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
                                                                      AND si.indid = sik.indid
                                            INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
                                                                      AND sc.colid = sik.colid
                                            INNER JOIN dbo.sysobjects so ON so.name = si.name
                                                                      AND so.xtype = 'PK'
                                   WHERE    sc.id = col.id
                                            AND sc.colid = col.colid ) THEN '√'
                     ELSE ''
                END AS 主键 ,
                CASE WHEN col.isnullable = 1 THEN '√'
                     ELSE ''
                END AS 允许空 ,
                ISNULL(comm.text, '') AS 默认值
        FROM    dbo.syscolumns col
                LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype
                inner JOIN dbo.sysobjects obj ON col.id = obj.id
                                                 AND obj.xtype = 'U'
                                                 AND obj.status >= 0
                LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id
                LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id
                                                              AND col.colid = ep.minor_id
                                                              AND ep.name = 'MS_Description'
                LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
                                                                 AND epTwo.minor_id = 0
                                                                 AND epTwo.name = 'MS_Description'
        WHERE   obj.name = %s
        ORDER BY col.colorder ;
            """
            cur.execute(sql, (table_name,))
            l_list = dictfetchall(cur)  # 这张表的数据
            print(l_list)
            table_modol = "\nclass %s(models.Model):" % "".join([i.capitalize() for i in table_name.split('_')])
            c = []  # 重复去除
            ac = len([i for i in l_list if i['主键']])
            for l in l_list:
                # l {'表名': '', '序号': 2, '列名': 'basketName', '列说明': b'', '数据类型': 'nvarchar', '长度': 1000, '小数位数': 0,
                # '标识': '', '主键': '', '允许空': '', '默认值': ''}
                if l['长度'] == -1:
                    l['数据类型'] = 'text'
                if ac > 1:
                    if l["列名"] == 'ID':
                        continue
                elif ac == 0:
                    if l['列名'] == "id":
                        l['主键'] = 1

                if l["列名"] in c:
                    continue
                else:
                    c.append('l["列名"]')
                    l_modol_in = 'db_column = "%s"' % l['列名']
                    if l['长度'] and type[l['数据类型']] == "DecimalField":
                        l_modol_in += ',max_digits=%s,decimal_places=%s' % (l['长度'], l['小数位数'])
                    elif l['长度'] and l['长度'] > 0 and type[l['数据类型']] != "IntegerField" and type[l['数据类型']] != "DateTimeField":
                        l_modol_in += ',max_length=%s' % (l['长度'])
                    if l['主键']:
                        l['数据类型'] = 'pk'
                        l_modol_in += ' , primary_key=True'
                    if l['允许空']:
                        l_modol_in += ' , blank=True,null=True'

                    l_modol = "\n\t%s = models.%s(%s)" % (l['列名'], type[l['数据类型']], l_modol_in)  # orm的每列字段
                    table_modol += l_modol

            meta_info = "\n\tclass Meta:\n\t\tmanaged = False\n\t\tdb_table = '%s'\n\n" % table_name

            f.write(table_modol)
            f.write(meta_info)
conn.close()
print("models生成完成\n生成表为%s"%[i for i in tc])