1. 项目背景
由于项目需要连接第三方Oracle数据库，并从第三方Oracle数据库中查询出数据并且显示，而第三方的Oracle数据库是Oracle11的数据库。而django4.1框架支持支持 Oracle 数据库服务器 19c 及以上版本，需要 7.0 或更高版本的 cx_Oracle Python 驱动；django3.2支持 Oracle 数据库服务器 12.2 及以上版本。需要 6.0 或更高版本的 cx_Oracle Python 驱动。这就很矛盾。。。。。所以领导建议不要用django框架去连Oracle数据库，而是使用python代码去连接Oracle，以防报错
2. 代码实现
def vote_test():
query = Votes.objects.all()
colname = [field.name for field in Votes._meta.get_fields()]
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'
connections = cx_Oracle.connect('GYDC', '123456', '192.168.110.200:1521/HELOWIN')
if query:
sheets = []
local_sheets = []
last_id = Votes.objects.last().id
Ear_time = Votes.objects.values().order_by("PERMITSTARTTIME").first()
with connections.cursor() as cursor:
cursor.execute("""SELECT SHEETID from GYDC.WS_SHEETINDEX
WHERE
(PATTERNID = 47 or PATTERNID = 48 or PATTERNID = 49 or PATTERNID = 50 or PATTERNID = 65 or PATTERNID = 58 or PATTERNID = 62 or PATTERNID = 68) and
ISMAINSHEET = 1 and
SHEETTYPE = 1 and
TO_DATE('%s', 'YYYY-MM-DD HH24:MI:SS')<= TO_DATE(PERMITSTARTTIME, 'YYYY-MM-DD HH24:MI:SS') and
TO_DATE(PERMITSTARTTIME, 'YYYY-MM-DD HH24:MI:SS') < sysdate and
ENDTIME is null""" %(Ear_time["PERMITSTARTTIME"],))
vote_data = cursor.fetchmany()
for i in vote_data:
sheets.append(int(i[0]))
local_data = Votes.objects.values("SHEETID").all()
for vote in local_data:
local_sheets.append(vote["SHEETID"])
for sheet in set(local_sheets)-set(sheets):
Votes.objects.filter(SHEETID = sheet).delete()
for sheet in set(sheets)-set(local_sheets):
with connections.cursor() as cursor:
cursor.execute("""SELECT * from GYDC.WS_SHEETINDEX WHERE SHEETID = '%s'""" % (sheet))
vote_data = cursor.fetchmany()
map = data_clear(colname,vote_data,last_id)
# print(map)
Votes.objects.create(**map[0])
else:
objs = []
with connections.cursor() as cursor:
cursor.execute("""SELECT * from GYDC.WS_SHEETINDEX
WHERE
(PATTERNID = 47 or PATTERNID = 48 or PATTERNID = 49 or PATTERNID = 50 or PATTERNID = 65 or PATTERNID = 58 or PATTERNID = 62 or PATTERNID = 68) and
ISMAINSHEET = 1 and
SHEETTYPE = 1 and
sysdate - interval '30' day < TO_DATE(PERMITSTARTTIME, 'YYYY-MM-DD HH24:MI:SS') and
ENDTIME is null""")
vote_data = cursor.fetchall()
map = data_clear(colname,vote_data,0)
# Votes.objects.create(**map)
for i in range(0,len(map)):
objs.append(Votes(**map[i]))
Votes.objects.bulk_create(objs)
上述代码是逻辑实现，目的是通过这个方法实现我们的数据库与第三方库的同步，拟后端通过5-10分钟的定时任务，定时从第三方库中获取正在进行的数据，并将正在进行的数据导入我闷的MySQL数据库
def data_clear(local_col,vote_value,maxid):
maps =[]
for vote in vote_value:
map = dict(zip(local_col[1:-9], vote))
for key, value in map.items():
if key in ["MAKETIME", "PLANSTARTTIME", "PLANFINISHTIME", "RECEIVETIME", "PERMITSTARTTIME",
"APPROVESTARTTIME", "APPROVEWORKTIME", "APPROVEFINISHTIME", "DELAYTIME", "ENDTIME",
"TOTALOPERTIMES", "ACTUALOPERTIMES", "LASTOPERATETIME", "NEWPAGENUMTIME", "start_time",
"end_time"]:
if value:
value = datetime.datetime.strptime(value, '%Y-%m-%d %H:%M:%S')
map[key] = value
else:
map[key] = None
if map["PATTERNID"] == 47:
map["name"] = "电气1"
elif map["PATTERNID"] == 48:
map["name"] = "电气2"
elif map["PATTERNID"] == 49:
map["name"] = "热力1"
elif map["PATTERNID"] == 50:
map["name"] = "热力2"
elif map["PATTERNID"] == 65:
map["name"] = "热控1"
elif map["PATTERNID"] == 58:
map["name"] = "热控2"
elif map["PATTERNID"] == 62:
map["name"] = "电气倒闸"
elif map["PATTERNID"] == 68:
map["name"] = "热力机械"
elif map["PATTERNID"] == 6:
map["name"] = "综合操作"
map['id'] = maxid + 1
maxid += 1
maps.append(map)
print(maps)
return maps