python中sql相关(查询、更新)，以及sql参数化

python连接数据库+查询+更新

import pymysql

def beta_mysql():
    connect = pymysql.Connect(
        host='1.1.1.1',
        port=3906,
        user='user',
        passwd='pwd',
        db='db',
        charset='utf8'
    )
    return connect
def select(sql):
    connect=beta_mysql()
    # 获取游标
    cursor = connect.cursor()
    #执行sql
    cursor.execute(sql)
    myresult = cursor.fetchall()  # fetchall() 获取所有记录
    for x in myresult:
        print(x)
    connect.close()
def update(sql):
    connect = beta_mysql()
    cursor = connect.cursor()# 获取游标
    cursor.execute(sql)  # 执行sql语句
    connect.commit()  # 执行update操作时需要写这个，否则就会更新不成功
    result = cursor.fetchone()
    connect.close()
    return result

if __name__ == '__main__':
    sql2="update account  SET balance='80000' WHERE id='20200922201821636132000000846374'"
    update(sql2)
    sql1 = "select balance from account  WHERE id='20200922201821636132000000846374'"
    select(sql1)

sql参数化—

方法1：%s

def select1v1couse():
    connect = ronghe1v1plan_mysql()
    zk_course_ids=selectzkcourse()
    l=[]
    for zk_course_id in zk_course_ids:
        print(zk_course_id)
        # 获取游标
        cursor = connect.cursor()
        # 执行sql
        sql1=" update c_user_extend set nature='hao性格好哈哈哈或或或,顶顶顶顶,哈哈哈HHH,不显示',hobby='爱好多多多,多督导,多偶读,不显示HHH'where user_id=%s;" % user_id#此处的占位符统统是%s字符串类型，不再区分字符串，数字或者其他类型。另外%s不能加引号
    	print(sql1)
        cursor.execute(sql1)
        myresult = cursor.fetchall()  # fetchall() 获取所有记录
        for x in myresult:
            l.append(x[0])
    print(l)
    connect.close()

方法2：变量法

sql3 = "select * from c_user  WHERE plat_id='{}';".format(user_id)#方法2

综合案例

import pymysql.cursors
def updateuser(user_id):
    # 连接数据库
    connect = pymysql.Connect(
        host='rm-.mysql.rds.aliyuncs.com',
        port=3306,
        user='rw',
        passwd='111',
        db='bist',
        charset='utf8'
    )
    # 获取游标
    cursor = connect.cursor()

    #1)执行update接口--更新某一个用户爱好与性格
    sql1=" update c_user_extend set nature='hao性格好哈哈哈或或或,顶顶顶顶,哈哈哈HHH,不显示',hobby='爱好多多多,多督导,多偶读,不显示HHH'where user_id=%s;" % user_id#此处的占位符统统是%s字符串类型，不再区分字符串，数字或者其他类型。另外%s不能加引号
    print(sql1)
    #执行sql语句
    cursor.execute(sql1)
    #提交到数据库执行
    connect.commit()

    #2)执行update接口--更新某一个用户基本信息
    sql2=" update c_user set busis_id='20220315141526226001900001',signature='DD个性签名个个性签名个个性签名个个性签名个个性签名个个性签名个个性签名个个性签名个个性签名个个性签名个',role=4,province_code=15,city_code=1508,education=3,star_sign=1,animal_sign=7,height=160,weight=45 where plat_id=%s; "% user_id
    print(sql2)
    # 执行sql语句
    cursor.execute(sql2)
    # 提交到数据库执行
    connect.commit()

    #3)执行查询sql
    sql3 = "select * from c_user  WHERE plat_id='{}';".format(user_id)#方法1
    cursor.execute(sql3)
    #获取查询结果数据
    data =list(cursor.fetchone())
    print(data)
    #关闭数据库连接
    connect.close()
    return data
if __name__ == '__main__':
    user_id='20220324175732700000290001'
    updateuser(user_id)