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;