PyMySQL模块使用方法

PyMySQL是Python链接数据库的时候使用的一款第三方模块,在Python2.x时代使用的名字是pymysqldb。

pymysql

0x00 PyMySQL的安装

由于PyMySQL是一款第三方的插件,所以需要使用PIP进行安装。

pip3 install pymysql

0x01 PyMySQL链接数据库

A. Pycharm中链接数据库

在专业版的Pycharm中支援链接数据库,这样就可以用过Pycharm中通过可视化窗口来看数据库了,从而减少对Navicat的依赖。

在Pycharm右侧有个一个竖向排列的Tool Buttons,点击Database通过“+”就可以链接数据库了。在链接数据库的时候需要使用到链接数据库的驱动,因而一定要注意下载驱动。

pycharm-database

在链接数据库的界面点击Download即可完成驱动的下载与安装了。

B. PyMySQL链接数据库

PyMySQL数据库通过建立一个链接,获取光标后即可执行命令。

import pymysql

#链接数据库
conn = pymysql.connect(
    host = "localhost",
    port = 3306,
    database = "pymysql",
    user = "root",
    password = "",
    charset = "utf8"
)

#获取光标
cursor = conn.cursor()

sql = "select * from py"
#执行SQL语句
ret = cursor.execute(sql)
print(ret)

#关闭光标和链接
cursor.close()
conn.close()

0x02 PyMySQL数据库操作

A. 增

给用户信息表中添加用户名与密码,数据库和表格创建略。
添加一行数据

import pymysql

conn = pymysql.connect(
    host = "localhost",
    port = 3306,
    database = "pymysql",
    user = "root",
    password = "",
    charset = "utf8"
)

cursor = conn.cursor()
#不安全SQL,仅仅示例
sql = "insert into py(user,pwd) VALUES ('test','testpwd')"
ret = cursor.execute(sql)
#添加完毕数据库后需要提交一下数据
conn.commit()

cursor.close()
conn.close()

添加多行数据
将执行SQL语句的函数execute(),写为executemany()即可

cursor = conn.cursor()

list =[('1user','1pwd'),('2user','2pwd'),('3user','3pwd'),('4user','4pwd')]
sql = "insert into py(user,pwd) VALUES (%s,%s)"
ret = cursor.executemany(sql,list)

conn.commit()

插入数据失败之回滚

cursor = conn.cursor()

list =[('1user','1pwd'),('2user','2pwd'),('3user','3pwd'),('4user')]
sql = "insert into py(user,pwd) VALUES (%s,%s)"

try:
    ret = cursor.executemany(sql,list)
    conn.commit()
except Exception as e:
    conn.rollback()

上面代码中,批量向数据库中提交一组数据,但是由于其中的一组数据有问题(少了密码),则执行到其的时候保存,那么数据进行回滚,之前的3组数据也并没有提交到数据库中。

获取插入数据的ID(关联表时用)

cursor = conn.cursor()

list =['aaa','ddd']
sql = "insert into py(user,pwd) VALUES (%s,%s)"

try:
    ret = cursor.execute(sql,list)
    conn.commit()

    last_id = cursor.lastrowid
    print(last_id)  #21
except Exception as e:
    conn.rollback()

在外键表的时候这个就有用了,使用executemany()使用lastrowid貌似有点问题,返回的是第一个数据的id,不知道什么原因,还是一般添加多行数据的时候貌似也用不到lastrowid。

B. 删

cursor = conn.cursor()

sql = "delete from py where user = 'aaa'"

try:
    ret = cursor.execute(sql)
    conn.commit()
except Exception as e:
    conn.rollback()

删除数据就是改了一下SQL的语句而已,不过值得注意的是需要进行commit提交一下数据。

C. 改

cursor = conn.cursor()

sql = "update py set pwd = 'mmmm' where user = '1user'"

try:
    ret = cursor.execute(sql)
    conn.commit()
except Exception as e:
    conn.rollback()

同样的,最后不要忘记提交事务即可!!!

D. 查

使用PyMySQL对数据库进行查询后,仅仅可以获得符合结果的数据行的数量,并不能得到数据结果。
单行数据查询
需要通过fetchone()来进行取出结果。

cursor = conn.cursor()

sql = "select * from py"

ret = cursor.execute(sql)
print(ret)  #8 总共8行数据
ret = cursor.fetchone() #取出第一行数据
print(ret)  #(1, 'xzy', 'moe')

多行数据查询
使用fetchall()将满足结果的数据都取出来。

cursor = conn.cursor()

sql = "select * from py where user LIKE '%user'"

ret = cursor.execute(sql)
print(ret)  #4 总共4行数据
ret = cursor.fetchall() #取出所有复核条件的数据
print(ret)
#((17, '1user', 'mmmm'), (18, '2user', '2pwd'), (19, '3user', '3pwd'), (20, '4user', '4pwd'))

关于SQL注

在一个用户名进行校验的sql中,一般sql写为”select * from table where username = ‘%s’ and password = ‘%s'”,由于字符串是进行动态拼接出来的,就可以通过在用户名中写入“–”来注释了后面的语句,因而实现SQL注入。

import pymysql

user = input("输入用户名")
password = input("输入密码")

conn = pymysql.connect(
    host = "127.0.0.1",
    port = 3306,
    database="pymysql",
    user = "root",
    password = "",
    charset = 'utf8'
)

cursor = conn.cursor()
sql = "select * from py where user = '%s' and pwd = '%s'"%(user,password)
print(sql)
ret = cursor.execute(sql)

if ret:
    print('success!')
else:
    print("error")

cursor.close()
conn.close()

注入方式1
输入一个库中有的密码,将sql中and部分给注释了!

输入用户名xzy ' -- '
输入密码
select * from py where user = 'xzy ' -- '' and pwd = ''
success!

注入方式2
即使不知道库中的用户名可以通过用or 1=1的方式,来满足条件,之后注释and部分即可

输入用户名fwjog' or 1=1 -- '
输入密码
select * from py where user = 'fwjog' or 1=1 -- '' and pwd = ''
success!

为了防止SQL注入,就不要自己去拼接字符串即可,使用PyMySQL中自带的方式去执行命令即可。

cursor = conn.cursor()
sql = "select * from py where user = %s and pwd = %s"
ret = cursor.execute(sql,(user,password))

将执行需要格式化的字符串放入execute中,让PyMySQL帮拼接SQL语句即可。

0x03 PyMySQL其他操作

A. 关于取值

取值可以通过fetchone()和fetchall()来分别取出一条数据或者所有数据,如果要取出指定数量的数据,则可以使用fetchmany(int)来取出指定数量的数据。如取出5条数据fetchmany(5)。

B. 关于光标移动

当fetchone后cursor则向下移动了一行,fetchmany后,则移动了指定数量的行。也可以通过cursor.scroll()函数来进行光标的移动。

# 光标按绝对位置移动1
cursor.scroll(1, mode="absolute")
# 光标按照相对位置(当前位置)移动1
cursor.scroll(1, mode="relative"

C. 返回字典格式数据

当使用fetchone之类的函数,获取数据值的时候,返回的数据都是一个元祖嵌套元祖的数据形式,而如果想返回一个字典格式的数据(方便操作),则可以在获取光标的时候就指定数据格式。

#指定字典格式数据
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = "select * from py where user = '1user'"

cursor.execute(sql)
ret = cursor.fetchall()
print(ret)
#[{'user': '1user', 'id': 17, 'pwd': 'mmmm'}]


发表评论