如何将SQLAlchemy查询Oracle的结果存入Python变量供后续插入使用

云原生阿岚

2026-10-7

需求可行性说明

该需求完全可以实现，你当前的代码逻辑框架已经搭建完成，仅存在两处影响结果取值的问题，修复后即可正常将查询结果存入变量，供后续插入数据库等操作使用。

原代码存在的问题

try块中提前调用了一次map_projn_id_search.fetchone()但未赋值，游标读取一次后会自动偏移，后续else块中再次调用fetchone()会拿到空值
fetchone()返回的是元组结构的行对象，你需要取第一个元素才能获取到实际的GEOFRAME_MAP_PROJN_ID值，直接赋值会把整个元组存入变量，不符合后续插入数据库的格式要求

修正后可直接运行的代码

try:
    map_projn_id_search = Session.execute("""
    SELECT GEOFRAME_MAP_PROJN_ID 
    FROM V_GEOFRAME_MAP_PROJN 
    WHERE COORD_REF_SYS_CODE = :crs AND TRNSFRM = :tfm"""
    , {"crs" : crs , "tfm" : tfm} )
    # 移除提前调用的fetchone()，避免游标偏移
except Exception as e:
    print("查询出错：", str(e))
else:
    if map_projn_id_search.rowcount == 0:
        instances_with_unkown = ['SAMSDB', 'CASSDB', 'CCUSDB', 'EMASDB', 'EMLSDB', 'HNOSDB', 'KULSDB', 'NAFSDB', 'RUSSDB', 'YUZSDB', 'AMESDB', 'LAGSDB']
        instances_with_UNKNWON =[ 'USASDB']
        if emsdb_instance in instances_with_unkown:
            projn_id = "Unknown"
            print(f'\n The EPSG code (ProjCRS:GeogCRS:Tfm) does not exist on EMSDB. Set {projn_id} as CRS. \n')
        elif emsdb_instance in instances_with_UNKNWON:
            projn_id = "UNKNOWN"
            print(f'\n The EPSG code (ProjCRS:GeogCRS:Tfm) does not exist on EMSDB. Set {projn_id} as CRS. \n')
        else:
            print("\n Cannot insert the EPSG code (ProjCRS:GeogCRS:Tfm) on that database. \n")
    else:
        # 取查询结果的第一个元素赋值给变量
        projn_id = map_projn_id_search.fetchone()[0]
        print(f"\n Set {projn_id} as the CRS of the survey. \n")