方式一:更新 update
import pymysql
def main():
db = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
db='test',
charset='utf8')
cursor = db.cursor()
sql = "update test.user set name='吴老师' where age=26"
cursor.execute(sql)
db.commit()
if __name__ == '__main__':
main()
方式二:追加 insert into
import pymysql
def main():
db = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
db='test',
charset='utf8')
cursor = db.cursor()
sql = "INSERT INTO user (name,age)VALUES('Jack','150')"
cursor.execute(sql)
db.commit()
if __name__ == '__main__':
main()
方式三:存在的数据-更新;新增数据-追加 repalce into
import pymysql
def main():
db = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
db='test',
charset='utf8')
cursor = db.cursor()
list1 = []
list1.extend([imsi,twoLevelCustId,twoLevelCustName, iccid, operatorsId, operatorsName, msisdn,tradeId,apnName,apnState,totalFlow,poolId,insert_time])
sql_insert = "replace into test.simcard_lenovo_life_cycle_dtl(imsi,twoLevelCustId, twoLevelCustName, iccid, operatorsId,operatorsName,msisdn,tradeId,apnName,apnState,totalFlow,poolId,insert_time)value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
cursor.execute(sql_insert,list1)
db.commit()
if __name__ == '__main__':
main()
注:replace into方式用之前,需要设置数据库表中字段有唯一键,或者为唯一索引。否则依然是insert into 追加。(例如设置ICCID为主键后,表中已有iccid数据会进行数据更新)