情況摘要

? 近期遇到了這樣一個(gè)情況：我現(xiàn)在有一個(gè)SQL server數(shù)據(jù)庫要反向遷移到Django中。數(shù)據(jù)庫中表的列名是"aA"的形式，要求返回的字段名和數(shù)據(jù)庫的列名格式相同。但使用原生的反向遷移命令生成的models字段名都是小寫的（"aa"）形式。這使得我用orm返回的字段格式就不符合要求了。最簡單的解決方法就是重寫返回結(jié)果或?qū)odels的字段名改成字段"db_column"屬性的值。但我現(xiàn)有數(shù)據(jù)庫中的表數(shù)量很多、字段很雜，手動(dòng)操作很耗費(fèi)時(shí)間（主要是我懶）。在網(wǎng)上搜尋未果后，決定自己寫個(gè)反向遷移。

上代碼

import pymssql
import os

#### settings
db = {
    'NAME': '',
    'USER': '',
    'PASSWORD': '',
    'HOST': '',
    'PORT': '',
}
table_name_list = []    # 表名列表，如果為空則查詢庫中所有的表
address = ""            # 存放文件的位置，如果為空則為當(dāng)前路徑
#### settings_end

# 對結(jié)果集美化方法
def dictfetchall(cursor):
    # 獲取游標(biāo)描述
    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, "連接數(shù)據(jù)庫失敗")
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 數(shù)據(jù)類型 ,
                col.length AS 長度 ,
                ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小數(shù)位數(shù) ,
                CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
                     ELSE ''
                END AS 標(biāo)識(shí) ,
                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 默認(rèn)值
        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)  # 這張表的數(shù)據(jù)
            print(l_list)
            table_modol = "\nclass %s(models.Model):" % "".join([i.capitalize() for i in table_name.split('_')])
            c = []  # 重復(fù)去除
            ac = len([i for i in l_list if i['主鍵']])
            for l in l_list:
                # l {'表名': '', '序號': 2, '列名': 'basketName', '列說明': b'', '數(shù)據(jù)類型': 'nvarchar', '長度': 1000, '小數(shù)位數(shù)': 0,
                # '標(biāo)識(shí)': '', '主鍵': '', '允許空': '', '默認(rèn)值': ''}
                if l['長度'] == -1:
                    l['數(shù)據(jù)類型'] = '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['數(shù)據(jù)類型']] == "DecimalField":
                        l_modol_in += ',max_digits=%s,decimal_places=%s' % (l['長度'], l['小數(shù)位數(shù)'])
                    elif l['長度'] and l['長度'] > 0 and type[l['數(shù)據(jù)類型']] != "IntegerField" and type[l['數(shù)據(jù)類型']] != "DateTimeField":
                        l_modol_in += ',max_length=%s' % (l['長度'])
                    if l['主鍵']:
                        l['數(shù)據(jù)類型'] = '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['數(shù)據(jù)類型']], 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])

題外話

Q：為什么沒有寫成類？

W：臨時(shí)寫的時(shí)間緊。（因?yàn)槲覒校?/p>

Q：為什么出現(xiàn)了c,i之類的魔鬼變量？

W：這些變量大多都是臨時(shí)使用的，對與不同用戶使用并沒有太大影響。（因?yàn)槲覒校?/p>

Q：為什么沒有做成命令？

W：水平未到還未熟悉，而且反向遷移基本就做幾次使用頻率不高。（因?yàn)槲覒校?/p>

Q：你原生SQL是什么意思，干了什么？

W：那段SQL是獲取表結(jié)構(gòu)。我好久沒玩過SQL sever了，那段SQL我網(wǎng)上抄的。