python笔记(pymysql基本用法)

一、python实现用户登陆

1、连接、关闭（游标）
execute():SQL注入

import pymysql

user = input('username:')
pwd  = input('password:')

conn = pymysql.connect(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"
cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{'u':user,'p':pwd})
ret = cursor.fetchone()

cursor.close()
conn.close()

if ret:
    print('登陆成功！')
else:
    print('登陆失败！')

2、MySQL保存数据

import pymysql
conn = pymysql.connect(host='localhost',user='root',password='',database='db1')
cursor = conn.cursor()
sql = "insert into userinfo(user,password) values('耿娃',06161088)"
cursor.execute(sql)
conn.commit()
print(cursor.lastrowid)
cursor.close()
conn.close()

二、增、删、改、查
增、删、改：要用conn.commit()
获取数据：fetchone,fetchall,fetchmany
获取插入数据自增ID:cursor.lastrowid

1、增、改

import pymysql

user = '小黑'
pwd  = '06161086'
conn = pymysql.connect(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 = cursor.execute(sql,user,pwd)#返回值r为受影响的行数
# cursor.executemany(sql,[
                          ('小张','06161085'),
                          ('小王','06161084'),
                          ('小段','06161083'),
                          ('小余','06161082')])提交多条数据
conn.commit()
print(cursor.lastrowid)
cursor.close()
conn.close()

2、查

import pymysql
conn = pymysql.connect(host='localhost',user='root',password='',database='db1')
# cursor = conn.cursor()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from userinfo"#查
r = cursor.execute(sql)#返回值r为受影响的行数

# cursor.scroll(1,mode='relative')#相对当前位置移动
# cursor.scroll(2,mode='absolute')#相对绝对位置移动

# ret = cursor.fetchone()
# ret = cursor.executemany(4)  #一次取四个
ret = cursor.fetchall()
for rev in ret:
    print(rev)
cursor.close()
conn.close()

三、练习：
权限表：
1、订单管理
2、用户管理
3、菜单管理
4、权限管理
5、bug管理
用户表：
用户权限关系表
某个用户登陆后，可以查看自己的权限

import pymysql
user = input('请输入姓名：')
pwd  = input('请输入密码：')
conn = pymysql.connect(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"#查
cursor.execute(sql,[user,pwd])#返回值r为受影响的行数
ret = cursor.fetchone()
# print(ret)
# conn.commit()
if ret:
    print('登陆成功！')
    sql = "select power.power_list from user left join power on user.power_id=power.pid where user_name=%s"
    cursor.execute(sql,user)
    ret = cursor.fetchone()
    print(ret)
else:
    print('登陆失败！')
cursor.close()
conn.close()

四、存储过程

cursor.callproc('p1') #执行存储过程

五、创建10000个数据的表

 import pymysql
 import random

gen = ['男','女']
conn = pymysql.connect(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)"
# 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=utf8mb4"#查
for i in range(10,100000):
    name = 'long'+str(i)
    gender = gen[random.randint(0,1)]
    email = name+'qq.com'
    cursor.execute(sql,(name,gender,email))#返回值r为受影响的行数
    conn.commit()
cursor.close()
conn.close()