# encoding: utf-8 """ @time: 2021/12/15 15:57 """ import pymysql db = pymysql.
connect(host="localhost", user="root", port=3306, db="spiders") cursor = db.
cursor() def create_data(): cursor.execute("SELECT VERSION()") data = cursor.
fetchone() print("data", data) cursor.execute("CREATE DATABASE spiders DEFAULT
CHARACTER set utf8mb4") db.close() def create_table(): sql = 'create table if
not exists students (id varchar(255) not null,name varchar(255) not null,age
int not null, primary key (id))' cursor.execute(sql) print("crate table") db.
close() data = { 'id': '20120001', 'name': 'Bob', 'age': 21 } def insert_data():
table= "students" keys = ",".join(data.keys()) values = ','.join(['%s'] * len(
data)) sql = "insert into {table}({keys}) values ({values})".format(table=table,
keys=keys, values=values) try: if cursor.execute(sql, tuple(data.values())):
print("ok") db.commit() except: print("no") db.rollback() finally: db.close()
def update_data(): table = "students" keys = ",".join(data.keys()) values = ','.
join(['%s'] * len(data)) sql = "update students set age = %s where name = %s"
try: if cursor.execute(sql, (25, "Bob")): print("ok") db.commit() except
Exceptionas e: print("no", e) db.rollback() finally: db.close() def
insert_or_update_data(): """ 1:ON DUPLICATE KEY
UPDATE需要有在INSERT语句中有存在主键或者唯一索引的列,并且对应的数据已经在表中才会执行更新操作。
而且如果要更新的字段是主键或者唯一索引,不能和表中已有的数据重复,否则插入更新都失败。
2:不管是更新还是增加语句都不允许将主键或者唯一索引的对应字段的数据变成表中已经存在的数据。 :return: """ table = "students"
keys= ",".join(data.keys()) values = ','.join(['%s'] * len(data)) insert_sql =
"insert into {table}({keys}) value ({values}) on duplicate key update ".format(
table=table, keys=keys, values=values) update_sql = ",".join(["{key} = %s".
format(key=key) for key in data]) sql = insert_sql + update_sql # insert into
students(id,name,age) value (%s,%s,%s) on duplicate key update id = %s,name =
%s,age = %s try: if cursor.execute(sql, tuple(data.values()) * 2): print("ok")
db.commit() except Exception as e: print("no", e) db.rollback() finally: db.
close() def delete_data(): table = "students" condition = "age > 20" sql =
"delete from {table} where {condition}".format(table=table, condition=condition)
try: if cursor.execute(sql): print("ok") db.commit() except Exception as e:
print("no", e) db.rollback() finally: db.close() def query_data(): table =
"students" condition = "age >= 20" sql = "select * from {table} where
{condition}".format(table=table, condition=condition) try: if cursor.execute(sql
): print("ok") print('Count:', cursor.rowcount) one = cursor.fetchone() #
查询会导致指针偏移 print("one", one) results = cursor.fetchall() print("results", results
) for row in results: print("row", row) except Exception as e: print("no", e)
finally: db.close() if __name__ == '__main__': # create_data() # create_table()
# insert_data() # update_data() # insert_or_update_data() # delete_data()
query_data() pass

技术
今日推荐
阅读数 5973
阅读数 1710
阅读数 1466
阅读数 1241
阅读数 1169
阅读数 1128
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:ixiaoyang8@qq.com
QQ群:766591547
关注微信