Python操作MySQL之pymysql

pymsql是Python中操作MySQL的模块。

一 安装

pip3 install pymysql

二 使用操作

2.1 验证用户名密码

#!/usr/bin python
# -*- coding:utf-8 -*-
import pymysql

user = input("username：>>")
pwd = input("password: >>")

# 创建连接
conn = pymysql.connect(host="localhost",user="root",password="",database="test1")
# 创建游标
cursor = conn.cursor()
# 建立游标,以字典的形式展示
# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 方式一：不推荐，容易SQL注入
# 建立SQL语句
sql = "select * from userinfo where username ='%s' and password ='%s'" % (user, pwd)  # 注意%s需要加引号
# 执行SQL，并返回收影响行数
effect_row = cursor.execute(sql)

# 方式二：
# 建立SQL语句
# sql = "select * from userinfo where username =%s and password =%s"
# 执行SQL，并返回收影响行数
# effect_row = cursor.execute(sql,(user, pwd))
# effect_row = cursor.execute(sql,[user,pwd])

# 方式三：
# 建立SQL语句
# sql = "select * from userinfo where username =%(u)s and password =%(p)s"
# 执行SQL，并返回收影响行数
# effect_row = cursor.execute(sql,{'u':user,'p':pwd})

# 获取查询结果
res = cursor.fetchone()
print(effect_row)  # 1；失败为：0
print(res)  # (1, 'joe1', '123')；失败为：None

# 关闭游标
cursor.close()
# 关闭连接
conn.close()

if effect_row:
    print("登陆成功！")
else:
    print("登陆失败！")

2.2 增、删、改

import pymysql

conn = pymysql.connect(host="localhost",user="root",password="",database="test1")
cursor = conn.cursor()

# 添加一条数据
# sql = "insert into userinfo(username,password) VALUES('aaa','123')"
# cursor.execute(sql)

# 添加多条数据方式一
# sql = "insert into userinfo(username,password) VALUES('bbb','123'),('ccc','123')"
# cursor.execute(sql)

# 添加多条数据方式二
# sql = "insert into userinfo(username,password) VALUES(%s,%s)"
# cursor.executemany(sql,[("eee","123"),("fff","123")])

# 添加多条数据方式三，其实与方式二一样
# cursor.executemany("insert into userinfo(username,password) VALUES(%s,%s)", [("ggg","123"),("hhh","123")])

# 不定单条添加
# sql = "insert into userinfo(username,password) VALUES(%s,%s)"
# cursor.execute(sql,("iii","123"))

# effect_row受影响的行数
# sql = "insert into userinfo(username,password) VALUES('jjj','123')"
# effect_row = cursor.execute(sql)
# print(effect_row)

# 删除
# cursor.execute("delete from userinfo where id > 10")

# 更新
# cursor.execute("update userinfo set password = '1234' where id > %s", (4,))

conn.commit()  # 增、删、改必须加入此语句
cursor.close()
conn.close()

2.3 查

import pymysql

conn = pymysql.connect(host="localhost",user="root",password="",database="test1")
# 以字典的形式进行查询
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
sql = "select * from userinfo"
cursor.execute(sql)
# 获取第一行数据
res = cursor.fetchone()
# 获取指定行数据
# res = cursor.fetchmany(4)
# 获取所有数据
# res = cursor.fetchall()     # 通过SQL语句（limit）控制能拿到的数据条数

print(res)
cursor.close()
conn.close()

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

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

2.4 获取自增ID

import pymysql

conn = pymysql.connect(host="localhost",user="root",password="",database="test1")
cursor = conn.cursor()
# 添加一条数据
sql = "insert into userinfo(username,password) VALUES('joe111','123')"
cursor.execute(sql)
# 获取最后一条插入的数据
new_id = cursor.lastrowid
print(new_id)
# 增、删、改必须加入此语句
conn.commit()
cursor.close()
conn.close()

三 execute()之SQL注入

还是引用“验证用户名密码”的例子，我们采用第一种方式，SQL语句如下：

sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)     # 注意：%s需要加引号

此时有两种注入方式：

# 1、sql注入之：用户存在，绕过密码
joe1' -- 任意字符  # 注意--后有空格

# 2、sql注入之：用户不存在，绕过用户与密码
xxx' or 1=1 -- 任意字符  # 注意--后有空格

原因分析：

因为我们sql语句运用了字符串拼接name= ‘%s’，在执行用户名和密码输入时，我们不管以哪种方式，前面的name肯定能过匹配成功，而后面的密码被-- 给注释掉了，所以我们可以登录成功！！！

解决方法：

用execute做字符串拼接，如“验证用户名密码”例子中的方式二。

四 调用存储过程

# 执行存储过程操作
cursor.callproc("proc_name",(temp1,temp2))   # proc_name代表存储过程名称，temp1,temp2代表需要传入的参数
res1 = cursor.fetchall()
print(res1)

# 执行值返回
cursor.execute("select @_proc_name_0,@_proc_name_1")    # @_proc_name_0,@_proc_name_1代表返回值，proc_name代表存储过程名称，0/1代表下标
res2 = cursor.fetchall()
print(res2)