python学习笔记(73) pymysql模块

有连接，有关闭，有游标

execute()　　# 有SQL注入问题

增删改需要conn.commit()

fetchone  fetchall　　# 游标

cursor.lastrowid　　# 最后一条的自增ID executemany以后也是最后一条

################################################

SQL注入：

import pymysql

user = input(‘用户名：‘)
pdw = input(‘密码：‘)

conn = pymysql.connect(host = ‘localhost‘,user = ‘root‘,password = ‘‘,database =‘db666‘)
cursor = conn.cursor()
# sql = "select * from userinfo where username = ‘%s‘ and password = ‘%s‘"%(user,pwd)
# cursor.execute(sql)   # 自己进行字符串拼接会被SQL注入
# select * from userinfo where username = ‘uu‘ or 1=1 -- ‘and password = ‘%s‘   #SQL注入
sql = "select * from userinfo where username = %s and password = %s"
cursor.execute(sql,user,pwd)    # 或者][user,pwd]或者字典拼接
result = cursor.fetchone()

cursor.close()
conn.close()

if result:
    print(‘登陆成功‘)
else:
    print(‘登陆失败‘)

################################################

查：

import pymysql

conn = pymysql.connect(host = ‘localhost‘,user = ‘root‘,password = ‘‘,database =‘db666‘)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 以字典形式显示，常用
sql = ‘select * from userinfo‘
cursor.execute(sql)

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

result = cursor.fetchone()
result = cursor.fetchone()  # 一次取一个

result = cursor.fetchmany(4)    # 取4个
result = cursor.fetchall()

cursor.close()
conn.close()

################################################

改：

import pymysql

conn = pymysql.connect(host = ‘localhost‘,user = ‘root‘,password = ‘‘,database =‘db666‘)

sql1 = "insert into userinfo(username,password) values(%s%s)"
r = cursor.executemany(sql1,[(‘egg‘,‘sb‘),(‘alex‘,‘sb‘)])
print(r)# 一次提交多个，返回受影响行数
conn.commit()   # 有修改操作必须commit

cursor.close()
conn.close()

python学习笔记(73) pymysql模块

原文：https://www.cnblogs.com/farion/p/10050902.html