手撸一个DjangoORM反向迁移
情况摘要
近期遇到了这样一个情况：我现在有一个SQL server数据库要反向迁移到Django中。数据库中表的列名是"aA"的形式，要求返回的字段名和数据库的列名格式相同。但使用原生的反向迁移命令生成的models字段名都是小写的（"aa"）形式。这使得我用orm返回的字段格式就不符合要求了。最简单的解决方法就是重写返回结果或将models的字段名改成字段"db_column"属性的值。但我现有数据库中的表数量很多、字段很杂，手动操作很耗费时间（主要是我懒）。在网上搜寻未果后，决定自己写个反向迁移。
上代码
import pymssql
import os
#### settings
db = {
'NAME': '',
'USER': '',
'PASSWORD': '',
'HOST': '',
'PORT': '',
}
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])
题外话
Q：为什么没有写成类？
W：临时写的时间紧。（因为我懒）
Q：为什么出现了c,i之类的魔鬼变量？
W：这些变量大多都是临时使用的，对与不同用户使用并没有太大影响。（因为我懒）
Q：为什么没有做成命令？
W：水平未到还未熟悉，而且反向迁移基本就做几次使用频率不高。（因为我懒）
Q：你原生SQL是什么意思，干了什么？
W：那段SQL是获取表结构。我好久没玩过SQL sever了，那段SQL我网上抄的。
