python操作mysql数据库
本节内容:
一、数据库的基本操作
二、sql注入攻击
三、pymysql 增删改查
一、数据库的基本操作
1、数据库的连接
# (1) 连接数据库,创建连接对象 conn = pymysql.connect( host="127.0.0.1" , user = "root" , password = "123456" , database="db102" , charset="utf8" , port= 3306) print(conn) # (2) 创建游标对象( 用来做增删改查 mysql操作) cursor = conn.cursor() # (3) 执行sql语句 sql = "select * from employee" # 返回的是查询的条数 res = cursor.execute(sql) print(res) # (4) 获取数据 """ fetchone 获取一条数据""" res = cursor.fetchone() res = cursor.fetchone() print(res) # (5) 释放游标对象 cursor.close() # (6) 释放连接对象 conn.close()
2.创建/删除表相关操作
#创建连接对象 conn = pymysql.connect( host="127.0.0.1" , user = "root" , password = "123456" ,database = "db102") # 使用 cursor() 方法创建一个游标对象 cursor cursor = conn.cursor() # 使用预处理语句创建表 sql = """ create table t1( id int primary key auto_increment, first_name varchar(255) not null , last_name varchar(255) not null , sex tinyint not null, age tinyint unsigned not null , money float(10,3) ) """ cursor.execute(sql) try: sql = "desc t1" res = cursor.execute(sql) # 返回字段个数 print(res) res = cursor.fetchone() res = cursor.fetchone() print(res) # 删除表 sql = "drop table t1" res = cursor.execute(sql) print(res) except: pass
3.事务处理
事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
- 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
- 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
- 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
- 持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
sql = "update employee set salary=2021000 where id = 2 " try: # 执行SQL语句 cursor.execute(sql) # 向数据库提交 db.commit() except: # 发生错误时回滚 db.rollback()
二、sql 注入攻击
1、创建一个表
1 conn = pymysql.connect( host="127.0.0.1",user="root",password="123456" , database="db102" ) 2 cursor = conn.cursor() 3 4 sql = """ 5 create table user_pwd( 6 id int unsigned primary key auto_increment, 7 username varchar(255) unique not null, 8 passwrod varchar(255) not null 9 ) 10 """ 11 cursor.execute(sql) 12 13 cursor.close() 14 conn.close()
2、注入攻击现象
user = input("请输入您的账号") pwd = input("请输入您的密码") conn = pymysql.connect( host="127.0.0.1",user="root",password="123456" , database="db102" ) cursor = conn.cursor() # 账号: sdfsdfsdf' or 5=5 -- adfa 密码: 随便写 # -- 在sql中代表的注释的含义,可以把密码注释掉 sql = "select * from user_pwd where username = '%s' and passwrod = '%s' " % (user,pwd) print(sql) res = cursor.execute(sql) print(res) if res: print("恭喜你~ 登录成功~") else: print("抱歉~ 登录失败~")
3、预处理机制(解决方案)
在执行sql语句之前, 提前对sql中的特殊符号进行过滤优化, 避免sql注入攻击
execute( sql , (参数1,参数2,参数3) )
user = input("请输入您的账号") pwd = input("请输入您的密码") conn = pymysql.connect( host="127.0.0.1",user="root",password="123456" , database="db102" ) cursor = conn.cursor() sql = "select * from user_pwd where username = %s and passwrod = %s" print(sql) res = cursor.execute(sql,(user , pwd )) print( "恭喜你~ 登录成功~" if res else "抱歉~ 登录失败~") cursor.close() conn.close()
三、pymysql增删改查
1、数据库插入操作
(1) 一次插入一个数据 sex:0=> 男 1=>女
import pymysql conn = pymysql.connect( host="127.0.0.1",user="root",password="123456" , database="db102" ) # 设置返回的数据为字典,而默认是元组 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = "insert into t1(first_name , last_name , sex , age ,money) values(%s,%s,%s,%s,%s)" try: # 执行sql语句 cursor.execute(sql,("荷","叶",0,83,83)) # 提交到数据库执行 conn.commit() except: # 如果发生错误则回滚 conn.rollback() cursor.close() conn.close()
(2)、一次插入多个数据,返回插入几条数据
import pymysql conn = pymysql.connect( host="127.0.0.1",user="root",password="123456" , database="db102" ) # 设置返回的数据为字典,而默认是元组 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = "insert into t1(first_name , last_name , sex , age ,money) values(%s,%s,%s,%s,%s)" try: # 执行sql语句 res = cursor.executemany(sql , [ ("荷","叶",0,83,83) , ("王","永娟",1,18,18) , ("吴","裴显",0,18,1) , ("梁","新宇",0,25,25) ] ) print(res)
# 获取插入第一个值的主键
print(cursor.lastrowid)
# 提交到数据库执行 conn.commit() except: # 如果发生错误则回滚 conn.rollback() cursor.close() conn.close()
2、删除操作
import pymysql # 打开数据库连接 conn = pymysql.connect( host="127.0.0.1",user="root",password="123456" , database="db102" ) # 设置返回的数据为字典,而默认是元组 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # SQL 删除语句 sql = "delete from db102 where id > %s" try: # 执行SQL语句 res=cursor.execute(sql,(5)) #返回删除几条数据 print(res) # 提交修改 conn.commit() except: # 发生错误时回滚 conn.rollback() # 关闭连接 conn.close()
3、数据库更新操作
import pymysql # 打开数据库连接 conn = pymysql.connect( host="127.0.0.1",user="root",password="123456" , database="db102" ) # 设置返回的数据为字典,而默认是元组 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # SQL 更新语句 sql = "update t1 set last_name = '花' where id = %s " try: # 执行SQL语句 res=cursor.execute(sql,(5)) #返回更新几条数据 print(res) # 提交修改 conn.commit() except: # 发生错误时回滚 conn.rollback() # 关闭连接 conn.close()
4、数据库查询操作
- fetchone 查询一条
- fetchmany 查询多条
- fetchall 查询所有
(1) fetchone 查询一条
import pymysql conn = pymysql.connect( host="127.0.0.1",user="root",password="123456" , database="db102" ) # 设置返回的数据为字典,而默认是元组 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = "select * from t1" try： cursor.execute(sql) res = cursor.fetchone() print(res) except： pass cursor.close() conn.close()
(2) fetchmany 查询多条
参数默认获取1条,可以指定条数
import pymysql conn = pymysql.connect( host="127.0.0.1",user="root",password="123456" , database="db102" ) # 设置返回的数据为字典,而默认是元组 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = "select * from t1" try： cursor.execute(sql) data = cursor.fetchmany(3) strvar = "" for i in data: id = i["id"] first_name = i["first_name"] last_name = i["last_name"] sex = "男性" if i["sex"] == 0 else "女性" age = i["age"] money = i["money"] strvar += "id:{},姓:{},名:{},性别:{},年龄:{},收入:{} \n".format(id,first_name,last_name,sex,age,money) print(strvar) except： pass cursor.close() conn.close()
(3) fetchall 查询所有
import pymysql conn = pymysql.connect( host="127.0.0.1",user="root",password="123456" , database="db102" ) # 设置返回的数据为字典,而默认是元组 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = "select * from t1" try： cursor.execute(sql) res = cursor.fetchall() print(res) except： pass cursor.close() conn.close()
5、调整查询位置
(1) 相对滚动 relative
往上滚是负数,往下滚正数
"""相对于上一条数据往下搜""" cursor.scroll(3,mode="relative") res = cursor.fetchone()
print(res)
"""相对于上一条数据往上搜""" cursor.scroll(-5,mode="relative") res = cursor.fetchone() print(res)
(2) 绝对滚动 absolute
永远从开头位置滚动 , 0代表第一条 , 不能为负值
cursor.scroll(0,mode="absolute") res = cursor.fetchone() print(res)
