PyODPS 学习 实现查询数据 并更新数据

 PyODPS是MaxCompute的Python版本的SDK，提供简单方便的Python编程接口。PyODPS支持类似Pandas的快速、灵活和富有表现力的数据结构。您可以通过PyODPS提供的DataFrame API使用Pandas的数据结果处理功能。本文用于帮助您快速开始使用PyODPS，并且能够用于实际项目。

 Pyodps提供了两种执行SQL语句的方法，execute_sql与run_sql，前者会阻塞调起SQL实例，而后者是不会阻塞的，可实现并行

from odps import ODPS
import sys
reload(sys)
#修改系统默认编码 查询出的中文乱吗解决
sys.setdefaultencoding("utf-8")

# print ('ds=' + args['bizdate'])
ds = args['bizdate']
sql = 'select c.cid,l.name from xgj.classes_out as c ,xgj.locations_deal as l where c.ds ='+ds+' and l.ds = '+ds+ ' and c.cid = l.cls_id'
instance = o.execute_sql(sql)
with instance.open_reader() as reader:
    # print (reader)
    # for record in reader 遍历这2万条数据，这里通过切片只取10条。
    #切片只取1条
    for record in reader[:1]:   
        print(record)
        name = record.name
        cid = record.cid
        u_sql = '''UPDATE  xgj.classes_out SET school_name = '%s'  where ds='%s' and  cid = '%s' '''%(name,ds,cid)
        print(u_sql)
        # print(name)
        #odps.execute_sql(u_sql) # 阻塞方法
        odps.run_sql(u_sql)   # 异步方法

异常错误

Please add put { "odps.sql.submit.mode" : "script"} for multi-statement query in settings

 想实现拼接多条sql一起执行，报以上的问题，解决方式 参考

sql = '''
UPDATE  xgj.classes_out SET school_name = '韩店镇中心小学'  where ds='20211110' and  cid = '612b34c2996163490ab8adb5' ;
UPDATE  xgj.classes_out SET school_name = '山东省泰安市岱岳区开元中学'  where ds='20211110' and  cid = '5e905a37a499a70e9c5b30f0' ;
UPDATE  xgj.classes_out SET school_name = '蔡亭小学'  where ds='20211110' and  cid = '6147ca898f2bd2cd28739529' ;
'''

odps.execute_sql(sql,hints={"odps.sql.submit.mode":"script"})

资料

• 阿里云api
• Pyodps之使用pyodps实现sql的循环