一、pymysql的基本使用
import pymysql
user = input('user>>>:').strip()
pwd = input('password>>>:').strip()
# 建立连接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='112233',
db='db8',
charset='utf8'
)
# 拿到游标
cursor = conn.cursor()
# 执行sql语句
sql = 'select * from userinfo where user = "%s" and pwd="%s"' % (user, pwd)
rows = cursor.execute(sql)
cursor.close()
conn.close()
# 进行判断
if rows:
print('登陆成功')
else:
print('登陆失败')
二、解决sql注入问题
import pymysql
user = input('user>>>:').strip()
pwd = input('password>>>:').strip()
# 建立连接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='112233',
db='db8',
charset='utf8'
)
# 拿到游标
cursor = conn.cursor()
# 执行sql语句
# sql = 'select * from userinfo where user = "%s" and pwd="%s"' % (user, pwd)
# print(sql)
'''
方式一
# select * from userinfo where user = "egon" -- xxxxxx" and pwd=""
# 在sql中 --之后的都代表注释掉。所以等于只查 where user ="egon"
方式二
# select * from userinfo where user = "xxx" or 1=1 -- hahahaha" and pwd=""
# select * from userinfo where user = "xxx" or 1=1 其实是执行了这个,1=1永远成立
'''
# 防止sql注入的方法,过滤到非法操作
sql = 'select * from userinfo where user = %s and pwd= %s '
rows = cursor.execute(sql, (user, pwd)) # 将用户密码在这里传入,pymysql内置的功能就能帮助你过滤
cursor.close()
conn.close()
# 进行判断
if rows:
print('登陆成功')
else:
print('登陆失败')
三、pymysql之增删改查
# 1、增删改
import pymysql
# 建立链接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='112233',
db='db8',
charset='utf8'
)
# 拿游标
cursor = conn.cursor()
# 执行sql
# 增、删、改
sql = 'insert userinfo(user,pwd) values(%s,%s)' # 删改把insert换成相应的关键字就行
# cursor.execute(sql,('wxx','123'))
# rows = cursor.executemany(sql, [('yxx', '123'), ('egon1', '123'), ('egon2', '123')]) # 插入多条记录
# print(rows) # 3
rows = cursor.executemany(sql,[('egon3','123'),('egon4','123'),('egon5','123')])
print(cursor.lastrowid) # 7 插入之前id走到哪了,在插入三条就是7、8、9
conn.commit() # 执行commit才会对数据库进行操作
# 关闭游标和链接
cursor.close()
conn.close()
# 2、查询
import pymysql
# 建立链接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='112233',
db='db8',
charset='utf8'
)
# 拿游标
cursor = conn.cursor(pymysql.cursors.DictCursor) # 以字典的形式取出来
# 执行sql
# 查询
rows = cursor.execute('select * from userinfo;')
# print(rows) 6
# print(cursor.fetchone()) # 取一行,直到取到没有就显示None
# print(cursor.fetchone())
# print(cursor.fetchmany(5)) # 指定取几行
# print(cursor.fetchall()) # 取全部
# cursor.scroll(3,'absolute') # 绝对位置
# print(cursor.fetchone()) # 第四条
print(cursor.fetchone())
cursor.scroll(2,mode='relative') # 基于当前的位置往后在跳两个
print(cursor.fetchone()) # 4 ,如果用absolute就是3
# 关闭游标和链接
cursor.close()
conn.close()