python笔记（pymysql基本⽤法）⼀、python实现⽤户登陆
1、连接、关闭（游标）
execute():SQL注⼊
import pymysql
user = input('username:')
pwd  = input('password:')
conn = t(host='localhost',user='root',password='',database='db1')
cursor = conn.cursor()
sql = "select * from userinfo where user=%s and password=%s"
# sql = "select * from userinfo where user=%(u)s and password=%(p)s"
# ute(sql,{'u':user,'p':pwd})
ret = cursor.fetchone()
cursor.close()
conn.close()
if ret:
print('登陆成功！')
else:
print('登陆失败！')
2、MySQL保存数据
import pymysql
conn = t(host='localhost',user='root',password='',database='db1')
cursor = conn.cursor()
sql = "insert into userinfo(user,password) values('耿娃',06161088)"
connmit()
print(cursor.lastrowid)
cursor.close()
conn.close()
⼆、增、删、改、查
增、删、改：要⽤connmit()
获取数据：fetchone,fetchall,fetchmany
获取插⼊数据⾃增ID:cursor.lastrowid
1、增、改
user = '⼩⿊'
pwd  = '06161086'
conn = t(host='localhost',user='root',password='',database='db1') cursor = conn.cursor()
sql = "insert into userinfo(user,password) values(%s,%s)"#增
# sql = "update userinfo set password='06161088' where user='耿娃'"#改
r = ute(sql,user,pwd)#返回值r为受影响的⾏数
# utemany(sql,[
('⼩张','06161085'),
('⼩王','06161084'),
('⼩段','06161083'),
('⼩余','06161082')])提交多条数据
connmit()
print(cursor.lastrowid)
cursor.close()
conn.close()
2、查
import pymysql
conn = t(host='localhost',user='root',password='',database='db1') # cursor = conn.cursor()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from userinfo"#查
r = ute(sql)#返回值r为受影响的⾏数
# cursor.scroll(1,mode='relative')#相对当前位置移动
# cursor.scroll(2,mode='absolute')#相对绝对位置移动
# ret = cursor.fetchone()
# ret = utemany(4)  #⼀次取四个
ret = cursor.fetchall()
for rev in ret:
print(rev)
cursor.close()
conn.close()
三、练习：
权限表：
1、订单管理
2、⽤户管理
3、菜单管理
4、权限管理
5、bug管理
⽤户表：
⽤户权限关系表
某个⽤户登陆后，可以查看⾃⼰的权限
user = input('请输⼊姓名：')
pwd  = input('请输⼊密码：')
conn = t(host='localhost',user='root',password='',database='db2',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# sql = "insert into user(user_name,password,power_id) values(%s,%s,%s)"
sql = "select * from user where user_name=%s and password=%s"
# sql = "create table user(uid int auto_increment primary key,user_name varchar(20),password varchar(10),power_id int, constraint fk_user_power foreign key(power_id) references power(pid))engine=innodb default charset=utf8mb4"#查
ret = cursor.fetchone()
# print(ret)
# connmit()
if ret:
print('登陆成功！')
sql = "select power.power_list from user left join power on user.power_id=power.pid where user_name=%s"
ret = cursor.fetchone()
print(ret)
else:
print('登陆失败！')
cursor.close()
conn.close()
四、存储过程
cursor.callproc('p1') #执⾏存储过程
五、创建10000个数据的表
import pymysql
import random
gen = ['男','⼥']
conn = t(host='localhost',user='root',password='',database='db2',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "insert into userinfo30(name,gender,email) values(%s,%s,%s)"
mysql下载app# sql = "select * from user where user_name=%s and password=%s"
# sql = "create table userinfo30(id int auto_increment primary key,name varchar(20),gender varchar(5),email varchar(20))engine=innodb default charset=u tf8mb4"#查
for i in range(10,100000):
name = 'long'+str(i)
gender = gen[random.randint(0,1)]
email = name+'qq'
connmit()
cursor.close()
conn.close()

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。