一、python操作 mysql
# pip install pymysql
1、基本语法
# (1) 创建连接 host user password database 这四个参数必须写 conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db0826",charset="utf8",port=3306) # (2) 创建游标对象(该对象可以操作数据库增删改查) cursor = conn.cursor() print(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、创建\删除 表
2.1、创建一张表
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db0826") cursor = conn.cursor() # 1.创建一张表 sql = """ create table t1( id int unsigned primary key auto_increment, first_name char(10) not null, last_name char(10) not null, age int unsigned, sex tinyint, money float ) """ res = cursor.execute(sql) print(res)
2.2、查看表结构
sql = "desc t1" res = cursor.execute(sql) print(res) # 6条字段数据 print(cursor.fetchone()) print(cursor.fetchone()) print(cursor.fetchone()) print(cursor.fetchone()) print(cursor.fetchone()) print(cursor.fetchone())
2.3、删除表
try: sql = "drop table t1" res = cursor.execute(sql) print(res) except: pass cursor.close() conn.close()
3、事务处理
""" pymysql 操作事务处理时,需要commit提交数据,才会变化,否则rollback回滚.恢复到最初状态 """
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db0826") cursor = conn.cursor() sql1 = "begin" sql2 = "update employee set emp_name = ‘123egon‘ where id = 1" sql3 = "commit" res1 = cursor.execute(sql1) res2 = cursor.execute(sql2) res3 = cursor.execute(sql3) # print(res1,res2,res3) # 返回值没有意义 # fetchone 与查询sql有关 , 增删改无效; # tup = cursor.fetchone() # print(tup) cursor.close() conn.close()
二、python操作mysql 增删改查
""" python 操作mysql时,默认开启事务,必须在增删改之后 提交数据,才会真正对数据库发生变化,默认默认是回滚 提交数据: conn.commit() 回滚数据: conn.rollback() execute 一次插入一条 executemany 一次插入多条 """
# 1.创建mysql 链接 conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db0826") # 查询数据,默认是元组,可以设置返回的类型为字典 pymysql.cursors.DictCursor cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # cursor=pymysql.cursors.DictCursor
1、增
sql = "insert into t1(first_name,last_name,age,sex,money) values(%s,%s,%s,%s,%s)" # 一次插入一条 res = cursor.execute(sql, ("宋","云杰",30,0,15000) ) print(res) # 1 # 获取最后插入这条数据的id号(针对单条数据插入) print(cursor.lastrowid) # 3 res = cursor.executemany( sql, [ ("高","云峰",50,1,16000) , ("戈","隆",80,1,17000) , ("袁","伟倬",120,0,130000) , ("刘","欣慰",150,0,18000) ] ) print(res) # 插入的条数 # 针对于多条数据,搜最后的id 可以通过倒序查询id sql = "select id from t1 order by id desc limit 1" res = cursor.execute(sql) print(res) # 获取最后一个id号 res = cursor.fetchone() print(res)
2、删
sql = "delete from t1 where id = %s" res = cursor.execute(sql , (3,)) print(res) if res: print("删除成功") else: print("删除失败")
3、改
sql = "update t1 set first_name = %s where id = %s" res = cursor.execute(sql,("王",4)) print(res) if res: print("修改成功") else: print("修改失败")
4、查
""" fetchone fetchmany fetchall 都是基于上一条数据往下查询. """
sql = "select * from t1" res = cursor.execute(sql) print(res) # 总条数
4.1、获取一条数据 fetchone
# {‘id‘: 4, ‘first_name‘: ‘王‘, ‘last_name‘: ‘云杰‘, ‘age‘: 30, ‘sex‘: 0, ‘money‘: 15000.0} res = cursor.fetchone() print(res)
4.2、获取多条数据 fetchmany
data = cursor.fetchmany() # 默认搜索的的是一条数据 print(data) data = cursor.fetchmany(3) print(data)
for row in data : # print(row) first_name = row["first_name"] last_name = row["last_name"] age = row["age"] if row["sex"] == 0: sex = "女性" else: sex = "男性" money = row["money"] print("姓:{},名:{},年龄:{},姓名:{},收入:{}".format(first_name,last_name,age,sex,money) )
4.3、获取所有数据 fetchall
data = cursor.fetchall() print(data)
4.4、自定义搜索查询的位置
4.4.1、相对滚动
sql = "select * from t1 where id >= 20" res = cursor.execute(sql) print(res) # 1.相对滚动 (正数相对于当前位置往后滚,负数反之.) cursor.scroll(3,mode="relative") res = cursor.fetchone() print(res) cursor.scroll(3,mode="relative") res = cursor.fetchone() print(res) # 2 往前滚 cursor.scroll(-2,mode="relative") res = cursor.fetchone() print(res)
4.4.2、绝对滚动
cursor.scroll(0,mode="absolute") print(cursor.fetchone()) cursor.scroll(1,mode="absolute") print(cursor.fetchone()) cursor.scroll(3,mode="absolute") print(cursor.fetchone()) # 往前滚没有数据,超出范围error """ cursor.scroll(-1,mode="absolute") print(cursor.fetchone())
4.4.3、提交数据
# 在进行增删改查时,必须提交数据,才会产生影响. conn.commit() cursor.close() conn.close()
三、sql注入攻击
创建表：
create table usr_pwd( id int unsigned primary key auto_increment, username varchar(255) not null, password varchar(255) not null )
1、sql注入的现象
import pymysql user = input("请输入用户名: >>> ").strip() pwd = input("请输入密码: >>> ").strip() conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db0826") cursor = conn.cursor() sql = "select * from usr_pwd where username=‘%s‘ and password=‘%s‘ " % (user,pwd) print(sql) res = cursor.execute(sql) print(res) # 查询的条数 if res: print("登录成功") else: print("登陆失败") cursor.close() conn.close()
运行：
""" 输入: sfsdf‘ or 3=3 -- sdfsd # -- 后面的字符串都会被注释掉, 前面账号虽然是错的 但是 2=2是真的 绕开了账号和密码的判断; select * from usr_pwd where username=‘afasdfasdfasdf‘ or 2=2 -- sfasdf‘ and password=‘3434 """
2、使用预处理，提前对sql语句中的特殊符号进行处理
""" 使用预处理机制,可以避免绝大多数sql注入的问题 execute 如果参数为2个,将默认开启预处理 execute(sql , (参数1,参数2,参数3 .... ) ) """
import pymysql user = input("请输入用户名: >>> ").strip() pwd = input("请输入密码: >>> ").strip() conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db0826") cursor = conn.cursor() sql = "select * from usr_pwd where username=%s and password=%s" res = cursor.execute(sql, (user,pwd) ) print("登陆成功" if res else "登录失败") cursor.close() conn.close()
四、导入导出数据库
1、导出数据库
""" 第一步: 先退出数据库 第二部: 切换到对应的路径 mysqldump -uroot -p db0824 > db0824.sql 第三部: # 导出所有内容 mysqldump -uroot -p db0824 > db0824.sql # 导出单个表 mysqldump -uroot -p db0824 t1 > t1.sql """
2、导入数据库
""" 第一步 : 先创建一个空的数据库 第二部 : 找到sql对应文件目录 第三部 : source 路径/文件 use 数据库 source D:\db0824.sql """