• 欢迎访问1024小神,一个只会Python的程序猿不是一个好司机
  • 有什么想对我说的可以在留言板里给我留言哦~
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏1024小神吧

Python修改Pg数据库的json数据

Python Jason 2周前 (06-04) 17次浏览 0个评论
import psycopg2
import <a href="https://1024shen.com/archives/tag/json" title="查看更多关于json的文章" target="_blank">json</a>
# postgresql://omni_pos_test:Plj5ejrBjkfs@pgm-uf64s9b1iak3x5wf168220.pg.rds.aliyuncs.com:1921/saas_qa_pos_sales_report

conn = psycopg2.connect(database="saas_qa_pos_sales_report",
                        user="omni_pos_test",
                        password="Plj5ejrBjkfs",
                        host="pgm-uf64s9b1iak3x5wf168220.pg.rds.aliyuncs.com",
                        port="1921")
print("Opened database successfully")

cur = conn.cursor()


def FindTicket():
    sql = """
    select id, ticket_id, partner_id, content
from sales_ticket_contents
where cast(content as text) like '%"POS",%';
    """
    cur.execute(sql)
    rows = cur.fetchall()
    for row in rows:
        ticket_id = row[1]
        content = row[3]
        print("ticket_id:", ticket_id)
        print("content: ", content)
        update_content(ticket_id, content)


def update_content(ticket_id, content):
    channel = content.get("channel", "")
    if channel:
        source = channel.get("source")
        print("获取到的渠道信息是:", source)
        content["channel"]["tpName"] = "POS"
    content_pg = <a href="https://1024shen.com/archives/tag/json" title="查看更多关于json的文章" target="_blank">json</a>.dumps(content, ensure_ascii=False)  # ensure_ascii防止将中文转成ascii
    sql = f"""
    UPDATE sales_ticket_contents set content = '{content_pg}' where ticket_id = '{ticket_id}';
    """
    print("更新的sql语句是:", sql)
    cur.execute(sql)
    conn.commit()
    print("Total number of rows updated :", cur.rowcount)


def run():
    FindTicket()
    print("Operation done successfully")
    conn.close()


if __name__ == '__main__':
    run()


如有失效,请留言告知丨转载请注明原文链接:Python修改Pg数据库的json数据
点赞 (0)

您必须 登录 才能发表评论!