mysql cursor 分页_mysql pymysql ,分页

pymysql

# 1.先安装pymysql 模块 pip3 install pymysql

import pymysql

# 相当于mysql的客户端程序

# 前端中获取的用户名和密码

username = input('请输入用户名:')

pwd = input('请输入密码:')

# 建立连接

conn = pymysql.connect(

host = '127.0.0.1',

port = 3306,

db = 'db20',

user = 'root',

password='',

charset ='utf8'

)

# 创建游标

cur = conn.cursor()

# select * from userinfo where name = 'alex' and password = '123';

sql = "select * from userinfo where name = '%s' and password = '%s'" %(username,pwd)

print(sql)

# 执行sql 返回是查询的成功的记录

result = cur.execute(sql) 使用这个可以防止sql注入

print(result)

# 游标关闭 连接关闭

cur.close()

conn.close()

if result:

# 响应 数据 到前端

print('登录成功')

else:

print('登录失败')

sql = "select * from userinfo where name = %(name)s and password = %(password)s"

print(sql)

# sql --

# 执行sql 返回是查询的成功的记录

result = cur.execute(sql,{"name":username,"password":pwd})

print(result)# 字典

# 1.先安装pymysql 模块 pip3 install pymysql

import pymysql

# 相当于mysql的客户端程序

# 建立连接

conn = pymysql.connect(

host = '127.0.0.1',

port = 3306,

db = 'db20',

user = 'root',

password='',

charset ='utf8'

)

# 创建游标

cur = conn.cursor()

# 插入

# select * from userinfo where name = 'alex' and password = '123';

sql = "insert into userinfo(name,password) values (%s,%s)"

# 更改

# sql = "update userinfo set name = %s where id = 3"

# 更改

# sql = "delete from userinfo where id = 3"

# print(sql)

# sql --

# 执行sql 插入数据 删除数据 更改数据 一定记得commit()

# 插入一条数据

# result = cur.execute(sql)

# 插入多条数据

result = cur.executemany(sql,[('alex2','321'),('alex3','678')])

print(result)

# 一定要提交

conn.commit()

# 游标关闭 连接关闭

cur.close()

conn.close()

查询

:

创建游标 查询出来的记录 是字典的形式

cur = conn.cursor(cursor=pymysql.cursors.DictCursor)

# select * from userinfo where name = 'alex' and password = '123';

sql = "select * from userinfo"

print(sql)

# sql --

# 执行sql 返回是查询的成功的记录

result = cur.execute(sql)

print(result)

# rows = cur.fetchone()

# print(rows)

# rows = cur.fetchone()

# print(rows)

# rows = cur.fetchone()

# print(rows)

# rows = cur.fetchone()

# print(rows)

# 查询多条数据

# rows = cur.fetchmany(2)

# 查询所有的数据

rows = cur.fetchall()

print(rows)

elect * from userinfo where name = 'alex' and password = '123';

sql = "select * from userinfo"

print(sql)

# sql --

# 执行sql 返回是查询的成功的记录

result = cur.execute(sql)

print(result)

rows = cur.fetchone()

print(rows)

rows = cur.fetchone()

print(rows)

cur.scroll(1,mode='absolute')

rows = cur.fetchone()

print(rows)

# rows = cur.fetchone()

# print(rows)

# rows = cur.fetchone()

# print(rows)

# rows = cur.fetchone()

# print(rows)

只有一页 和下一页

1,记录当前页的最大id或者最小的id

下一页:

select * from user where id > max_id limit 10,

select 8 from user where id< min order by id desc limit 10;

2,页面有页码的情况

select * from user where id in (

select id from (select *from user where id > pre_max_id limit (cu)max_id-pre_max_id)*10 ) as A order by A.id desc limit 10);

select * from (select * from user where id > 1500011 limit 30) as  A order by id order by desc;