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()