-->

python连接Oracle数据库实现数据查询并将Oracle数据导入MySQL数据库

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

上面这个方法主要用于服务前面的方法，因为第三方Oracle数据库中时间字段都是用的字符串类型，而MySQL数据库中用的时间类型，同时，MySQL数据库还相比较Oracle数据库多了一些字段，而data_clear方法就是将两个数据库中的字段转换成对应其类型的键值对存入MySQL

Oracle数据库SQL语句查询出来的中文都会变为？，解决办法如下：

 os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'

利用python代码实现查询数据编码的格式转化

代码大致如上述，解决过程并不算复杂，但是后续真正的难点在于在Ubuntu系统利用python连接Oracle数据库，上述代码设计之初是在window系统，因此在报无法连接Oracle数据库时需要将Oracle客户端中的如下三个.dll脚本放在当前项目下才可以连接，而在Ubuntu系统中上述三个脚本是无法使用的，因此就需要寻找linux 系统中连接Oracle数据库的脚本，应该是几个.os文件，解决办法会放在最后
image

3. 实现windows系统与linux系统的Oracle连接

linux系统/windows系统连接Oracle数据库方法实现（初次使用cx_Oracle实现，后发现其需要Oracle客户端脚本协助，但是window脚本是3个dll文件，而Linux系统的.os文件是不能直接放在路径下使用的，故换了方法，在cx_Oracle基础上使用了sqlalchemy）：

3.1. windows系统

window系统连接Oracle（cx_Oracle+sqlalchemy，前提是在项目根目录下或当前目录下有oci.dll， oraocci11.dll,oraociei11.dll三个脚本，实际上在window系统中，cx_Oracle就能实现，但是linux系统需要用到sqlalchemy）：

def test2():
    # cx_Oracle.init_oracle_client(r"./instantclient_11_2")
    dsnstr = cx_Oracle.makedsn("192.168.110.219","1521",service_name = "HELOWIN")
    # dsnstr = "192.168.110.200:1521/HELOWIN"
    print(dsnstr)
    connects = "oracle://%s:%s@%s" %("GYDC","123456",dsnstr)
    print(connects)
    engine = create_engine(connects,echo=True)
    with engine.connect() as con:
        result = con.execute(text("select * from GYDC.WS_SHEETINDEX WHERE SHEETID=334796"))
        print(type(result))
        print(result.fetchmany())
        for row in result:
            print("============================")
            print(row)
            print("============================")
    # print(result)
    print("连接成功")

仅cx_oracle（同样需要上述三个.dll文件）：

# def test():
#     os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'
#     connections = cx_Oracle.connect('GYDC', '123456', '192.168.110.200:1521/HELOWIN')
#     with connections.cursor() as cursor:
#         cursor.execute("""SELECT * from GYDC.WS_SHEETINDEX WHERE SHEETID = 334796""")
#         vote_data = cursor.fetchmany()
#     print(vote_data)

3.2. Linux系统

cx_oracle + sqlalchemy（同样需要.os文件，同时需要配置环境变量）：
环境变量配置方法：
1、在Oracle官网下载好对应的Linux系统安装包
2、解压缩到固定路径下（我这里是放在的项目路径下）
3、不用安装直接配置，当然你也可以找个教程去安装Oracle客户端，反正我不想安装
配置方法：

sudo ln -s /home/uai901/PycharmProjects/huayang_2th/votes/instantclient_11_2/*.so* /usr/lib

需要做软连接的脚本如下：
image

若出现需要libnsl.so.1的情况，说明你的电脑需要oracle64位脚本，可以在docker中导出来，在Oracle的容器lib64文件夹中有，命令如下

docker cp oracle11g:/lib64/libnsl.so.1 /root/桌面/后端/

创建完软连接后，可以到/usr/lib下看一下，ls查看，若为红色，则软连接无效，图形界面可以去文件夹下查看，若软连接右上角有叉号，则无效，确定都有效后，可以用如下命令使其生效

ldconfig

建立软连接，我这里通过软连接实现，实现原理：程序发现你要用Oracle客户端，它会自动去根目录下的/usr/lib或者/lib中去寻找.so文件，建立软连接，就自然能找到了，当然，如果直接配置环境变量，让.so在任何路径下可以被发现也是可以的

参考配置文章：https://blog.csdn.net/fenglongmiao/article/details/129027848

用于定时任务实现逻辑MySQL数据库与第三方Oracle数据库同步：

def vote_test():
    # cx_Oracle.init_oracle_client(lib_dir='./instantclient_11_2')
    dsnstr = cx_Oracle.makedsn("192.168.110.219", "1521", service_name="HELOWIN")
    connects = "oracle://%s:%s@%s" % ("GYDC", "123456", dsnstr)
    connections = create_engine(connects, echo=True)
    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.connect() as cursor:
            result = cursor.execute(text("""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 = result.fetchmany()
            # for row in result:
            #     vote_data.append(row)
            # 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.connect() as cursor:
                result = cursor.execute(text("""SELECT * from GYDC.WS_SHEETINDEX WHERE SHEETID = '%s'""" % (sheet)))
                # vote_data = []
                # for row in result:
                #     vote_data.append(row)
                vote_data = result.fetchmany()
                map = data_clear(colname,vote_data,last_id)
                # print(map)
                Votes.objects.create(**map[0])
    else:
        objs = []
        with connections.connect() as cursor:
            result = cursor.execute(text("""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 = []
        # for row in result:
        #     vote_data.append(row)
            vote_data = result.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)

用于数据清洗（服务于上面的定时任务）：

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"] = "工作票电气一次"
        elif map["PATTERNID"] == 48:
            map["name"] = "工作票电气二次"
        elif map["PATTERNID"] == 49:
            map["name"] = "工作票热力一次"
        elif map["PATTERNID"] == 50:
            map["name"] = "工作票热力二次"
        elif map["PATTERNID"] == 65:
            map["name"] = "工作票热控一次"
        elif map["PATTERNID"] == 58:
            map["name"] = "工作票热控二次"
        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

注：如果上述代码报：sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) DPI-1047: Cannot locate a 64-bit Oracle Client library: "libaio.so.1: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help

则需要运行以下命令安装libaio:

sudo apt-get install libaio*

仅cx_oracle（同样需要.os文件，同时需要配置环境变量）：

def vote_test():
    # cx_Oracle.init_oracle_client(lib_dir='./instantclient_11_2')
    dsnstr = cx_Oracle.makedsn("192.168.110.219", "1521", service_name="HELOWIN")
    connects = "oracle://%s:%s@%s" % ("GYDC", "123456", dsnstr)
    # connections = create_engine(connects, echo=True)
    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.219: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:
            result = 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 = result.fetchmany()
            # for row in result:
            #     vote_data.append(row)
            # 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:
                result = cursor.execute("""SELECT * from GYDC.WS_SHEETINDEX WHERE SHEETID = '%s'""" % (sheet))
                # vote_data = []
                # for row in result:
                #     vote_data.append(row)
                vote_data = result.fetchmany()
                map = data_clear(colname,vote_data,last_id)
                # print(map)
                Votes.objects.create(**map[0])
    else:
        objs = []
        with connections.cursor() as cursor:
            result = 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 = []
        # for row in result:
        #     vote_data.append(row)
            vote_data = result.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)

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"] = "工作票电气一次"
        elif map["PATTERNID"] == 48:
            map["name"] = "工作票电气二次"
        elif map["PATTERNID"] == 49:
            map["name"] = "工作票热力一次"
        elif map["PATTERNID"] == 50:
            map["name"] = "工作票热力二次"
        elif map["PATTERNID"] == 65:
            map["name"] = "工作票热控一次"
        elif map["PATTERNID"] == 58:
            map["name"] = "工作票热控二次"
        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
posted @   ꧁ʚ星月天空ɞ꧂  阅读(429)  评论(0)  编辑  收藏  举报
相关博文：
·  python根据用户所选id上传图片视频过对应算法(python实现大文件的分块上传)
·  python初学细节笔记
·  Python操作Oracle数据库：cx_Oracle
·  python链接oracle数据库以及数据库的增删改查实例
·  python链接oracle数据库以及数据库的增删改查实例
阅读排行：
· winform 绘制太阳，地球，月球 运作规律
· AI与.NET技术实操系列（五）：向量存储与相似性搜索在 .NET 中的实现
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 上周热点回顾（3.3-3.9）
点击右上角即可分享
微信分享提示
AI IDE Trae
❄
❄
❄
❄
❄
❄