©2020-2024 ioDraw All rights reserved
# 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