fetchall
sql = "select * from students;" rows = cursor.execute(sql) # 记录数 print("there are %d students" % rows) # 可迭代对象 students = cursor.fetchall() # 循环输出 for i in students: print(i) # 输出结果 there are 1 students ('1', '张三', 20)
fetchone
# 查询数据 - fetchone sql = "select * from students;" rows = cursor.execute(sql) # 根据记录数循环 for i in range(rows): student = cursor.fetchone() print(student) # 输出结果 ('1', '张三', 20)
fetchmany
可以自定义返回多少条记录数
# 查询数据 - fetchmany sql = "select * from students;" rows = cursor.execute(sql) # 可迭代对象 students = cursor.fetchmany(3) # 循环结果集 for i in students: print(i) # 输出结果 ('100', '小菠萝', 24)
增加数据
db = pymysql.connect( host='localhost', port=3306, user='root', password='1234567890', db='school', charset='utf8' ) # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 增加数据 sno = 100 name = "小菠萝" age = 24 sql = 'insert into students(sno,name,age) VALUES("%s", "%s", %d)' % (sno, name, age) # 执行 insert sql rows = cursor.execute(sql) # 查看 insert 语句返回结果，其实就是执行成功了多少条数据 print('Insert %d students' % rows) # 只有调用了 commit 方法才能将数据落盘，即提交 insert 操作 db.commit() # 输出结果 Insert 1 students
修改数据
db = pymysql.connect( host='localhost', port=3306, user='root', password='1234567890', db='school', charset='utf8' ) # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 更新数据 sno = 10 name = "小菠萝" age = 44 sql = 'UPDATE students SET name="%s", age=%d WHERE sno="%s"' % (name, age, sno) # 执行 update sql rows = cursor.execute(sql) # 返回成功修改记录的条数 print('update %d students' % rows) # 调用 commit，才会将 update 操作提交 db.commit() # 输出结果 update 1 students
删除数据
db = pymysql.connect( host='localhost', port=3306, user='root', password='1234567890', db='school', charset='utf8' ) # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 更新数据 sno = 10 name = "小菠萝" age = 44 sql = 'DELETE FROM students' # 执行 delete sql rows = cursor.execute(sql) # 返回成功修改记录的条数 print('delete %d students' % rows) # 调用 commit，才会将 delete 操作提交 db.commit() # 输出结果 delete 2 students
