mysql pymysql_MySQL之pymysql模块

连接mysql

import pymysql

mysql_addres = {

"host": "localhost",

"user": "root",

"password": "123456",

"charset": "utf8"

}

conn = pymysql.connect(**mysql_addres)# 连接数据库

status = conn.server_status# 判断数据库连接是否异常。

if status:

print("连接数据库异常！")

return status

cursor = con.cursor(pymysql.cursors.DictCursor)# pymysql.cursors.DictCursor 返回数据为({},{},{},)。好处在于返回的结果带数据类型

use_database = "use day40_3_zuoye"

sql1 = "select * from course"

cursor.execute(use_database)# 执行sql

cursor.execute(sql1)# 执行sql

res = cursor.fetchall()# 查询结果

res1 = cursor.fetchall()# 游标已经到最后了，此时取结果是取不到的。需要移动游标

cursor.scroll(-1,mode="relative") # mode：relative或者absolute ,relative相对路径，从游标的位置进行移动，单位为负数。absolute绝对路径从最开始的地方进行偏移，单位为正数。

sql注入攻击

sql注入指的是,用户在输入数据时,按照sql的语法,来编写带有攻击目的的sql语句,并插入到原始语句中执行.

例如:登录功能,需要用户输入用户名和密码

import pymysql

try:

mysql_addres = {

"host": "localhost",

"user": "root",

"password": "123456",

"charset": "utf8"

}

conn = pymysql.connect(**mysql_addres)# 连接数据库

user = input("username:")

password = input("password:")

count = cursor.execute("select *from user where name = '%s' and password = '%s'" % (user,password))

if count:

print("登录成功!")

else:

print("登录失败!")

except Exception as e:

print(type(e),e)

finally:

if cursor:cursor.close()

if conn: conn.close()

上述代码有被注入攻击的危险

尝试在用户名中输入一下内容，密码随意

尝试在用户名中输入以下内容,密码随意

jerry' — ass

或者连用户名都不用写

' or 1 = 1 -- asaa

解决方法：

客户端在发送sql给服务器前进行re判断

这样的问题在于一些程序可以模拟客户端直接发送请求给服务器

在服务器端将sql交给mysql是作进一步处理,相关的代码其实pymysql已经做了封装

我们只要保证不要自己来拼接sql语句即可,将拼接参数操作交给pymysql.

import pymysql

try:

conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)

print("连接服务器成功!")

cursor = conn.cursor(pymysql.cursors.DictCursor)

user = input("username:")

password = input("password:")

sql = "select *from user where name = %s and password = %s"

print(sql)

count = cursor.execute(sql,(user,password)) # 参数交给模块

if count:

print("登录成功!")

else:

print("登录失败!")

except Exception as e:

print(type(e),e)

finally:

if cursor:cursor.close()

if conn: conn.close()

pymysql增删改查

pymysql默认开启了事务

# 开启了事务

def test():

mysql_addres = {

"host": "localhost",

"user": "root",

"password": "123456",

"charset": "utf8",

"db":"test",

"autocommit":False # 默认为False

}

con = pymysql.connect(**mysql_addres)

cursor = con.cursor(pymysql.cursors.DictCursor)

# 转账业务,张三需要跟李四转账500块钱。

sql1 = "update plf set money = money - 500 where name = %s"

cursor.execute(sql1,("张三",))

sql2 = "update plf set money = money + 500 where name = %s"

cursor.execute(sql2, ("李四",))

con.commit()

cursor.close()

con.close()

test()

pymysql 不开启事务

def test_one():

mysql_addres = {

"host": "localhost",

"user": "root",

"password": "123456",

"charset": "utf8",

"db":"test",

"autocommit":True # 默认为False

}

con = pymysql.connect(**mysql_addres)

cursor = con.cursor(pymysql.cursors.DictCursor)

try:

# 转账业务,张三需要跟李四转账500块钱。

cursor.execute("start transaction")

sql1 = "update plf set money = money - 500 where name = %s"

cursor.execute(sql1,("张三",))

sql2 = "update plf set money = money + 500 where name = %s"

cursor.execute(sql2, ("李四",))

cursor.execute("commit")

cursor.close()

con.close()

except Exception as e:

con.rollback()

test_one()

增删改

import pymysql

# 1.建立连接

try:

conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)

print("连接服务器成功!")

cursor = conn.cursor(pymysql.cursors.DictCursor)

#增

#sql = "insert into user values(null,%s,%s,%s)"

#count = cursor.execute(sql,("tom","man","123321"))

# 一次性插入多条记录

#sql = "insert into user values (null,%s,%s,%s)"

#count = cursor.executemany(sql, [("周芷若","woman","123"), ("赵敏","woman","321")])

#删

# count = cursor.execute("delete from user where id = 1")

#改

count = cursor.execute("update user set name = '刘大炮' where id = 1")

if count:

print("执行成功!")

else:

print("执行失败!")

# 获取最新的id

# print(cursor.lastrowid)

except Exception as e:

print(type(e),e)

finally:

if cursor:cursor.close()

if conn: conn.close()