在py中使用MySQL

1. 基本语法使用

#coding=utf-8
# python 中操作mysql,基本语法
import pymysql
# (1) 创建连接,host,user,password,database这四个参数是必须要设定的
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619",charset="utf8",port=3306)

# (2) 创建游标对象,该对象可以进行增删改查操作
cursor = conn.cursor()

# (3) 执行sql语句
sql = "select * from t1"
res = cursor.execute(sql)
print(res) # 打印结果是查询到的数据的总条数

# (4) 获取数据
res = cursor.fetchone()
print(res) # 打印一条数据

# (5) 释放游标对象
cursor.close()

# (6) 关闭连接
conn.close()

2.  创建,删除表格

# 2. 创建,删除表
import pymysql
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619",charset="utf8",port=3306)
cursor = conn.cursor()

sql = """
create table t2(
id int unique auto_increment,
name char(5) not null,
hobby char(20)
);
"""
res = cursor.execute(sql)
print(res) # 建表时返回值是0

sql = "desc t2"
res = cursor.execute(sql)
print(res) # 3　查看表结构时,返回的是字段的总个数

# 3. 删除表
try:
    sql = "drop table t2"
    res = cursor.execute(sql)
    print(res) #　0 删除表操作时,返回值是0
except:
    print("该表已删除")

cursor.close()
conn.close()

3. 事务处理

# 事务处理
# Python操作事务处理,必须通过commit提交数据,否则会rollback回滚,恢复到原来的状态
import pymysql
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619",charset="utf8",port=3306)
cursor = conn.cursor()
sql1 = "begin"
sql2 = "update t1 set name = 'eric' where id = 1"
sql3 = "insert into t1 values(null,'jim')"
sql4 = "commit"

res1 = cursor.execute(sql1)
res2 = cursor.execute(sql2)
res3 = cursor.execute(sql3)
res4 = cursor.execute(sql4)
print(res1,res2,res3,res4) # 0 0 1 0

cursor.close()
conn.close()

4. sql 注入

# sql 注入
user = input("username:").strip()
pwd = input("password:").strip()

import pymysql
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619",charset="utf8",port=3306)
cursor = conn.cursor()
sql = "select * from tuser where user='%s' and password='%s' "%(user,pwd)
print(sql)  #　select * from tuser where user='fasdf' or 3=3 -- ljsdfa' and password='safas'
res = cursor.execute(sql)
print(res) #　1

if res:
    print("登录成功")
else:
    print("登录失败")
cursor.close()
conn.close()
"""
输入类似 erwe234' or 10 = 10 -- 234dfsdf 的账号时
select * from usr_pwd where username='erwe234' or 10 = 10 -- 234dfsdf' and password='dfsdf'  

where username='erwe234' or 10 = 10  username的判断是假的 但是 后面or拼接的条件是真的,所以可以查询成功
-- 代表后面的代码是注释
把用户名和密码都绕开了,进行sql注入攻击
"""


# 解决办法
"""
使用预处理机制,可以避免绝大多数的sql注入问题
execute 参数1是一个sql语句,如果sql语句和里面的参数值分开执行,默认开启预处理
execute(sql , (参数1,参数2,参数3))
"""
user = input("username:").strip()
pwd = input("password:").strip()
import pymysql
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619",charset="utf8",port=3306)
cursor = conn.cursor()
sql = "select * from tuser where user = %s and password = %s"
res = cursor.execute(sql,(user,pwd))
print(res)

if res:
    print("登录成功")
else:
    print("登录失败")

5. python 操作mysql增删改查

# 操作mysql时候, 默认开启事务, 必须在增删改之后,提交数据, 才会对数据库产生影响, 否则默认回滚
# 提交数据: conn.commit(),回滚数据: conn.rollback()
import pymysql
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619",charset="utf8",port=3306)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 查询数据默认是元祖,当把sursor()里改为 cursor=pymysql.cursors.DictCursor时,再查询就是字典的形式
# 增:
sql = "insert into t1(name) values(%s)"
res = cursor.execute(sql,("jim")) # 一次增加一条数据
print(res) # 1
print(cursor.lastrowid)
res = cursor.executemany(sql,[("zhangsan"),("lisi"),("wnagwu")]) # 一次增加多条数据
print(res) # 3

# 获取最后插入这条数据的id号(针对于单条数据执行,获取最后的id,如果多条数据的执行,以第一条数据的id为主)
print(cursor.lastrowid)
# 针对于多条数据最后的id,可以通过倒序查询,找到id号
# select id from t1 order by id desc limit 1

# 删:
sql = "delete from t1 where id = 1"
res = cursor.execute(sql)
print(res)
if res:
    print("删除成功")
else:
    print("删除失败,请确认是否已删除")

# 改:
sql = "update t1 set name = %s where id = %s"
res = cursor.execute(sql,("yiyi",2))
print(res)
if res:
    print("更新成功")
else:
    print("更新失败,请确认是否已更新")


# 查:
# (1) 获取一条数据
sql = "select * from t1"
res = cursor.execute(sql)
res1 = cursor.fetchone()
print(res1) # {'id': 2, 'name': 'yiyi'}
res2 = cursor.fetchone()
print(res2) # {'id': 3, 'name': 'jim'}
# 默认从上一条数据继续向下搜索(性质类似迭代器)

# (2) 获取多条数据
res3 = cursor.fetchmany() # 如果没有参数,默认查询一条数据
print(res3) # [{'id': 4, 'name': 'jim'}]
res4 = cursor.fetchmany(2)
print(res4) #　[{'id': 5, 'name': 'lisa'}, {'id': 7, 'name': 'jim'}]
# 拼接数据
for row in res4:
    id = row["id"]
    name = row["name"]
    print("id号码:{},姓名:{}".format(id,name))
# id号码:5,姓名:lisa
# id号码:7,姓名:jim

# (3) 自定义查询的起始位置
# 方式一: 先指定ID号,再查找
sql = "select * from t1 where id = 5"
res5 = cursor.execute(sql)
res6 = cursor.fetchone()
print(res6) # {'id': 5, 'name': 'lisa'}

# 方式二:相对滚动
sql = "select * from t1"
res = cursor.execute(sql)
cursor.scroll(3,mode="relative") # 向后滚动
res7 = cursor.fetchone()
print(res7) # id起始在2号,向下查找一条是5号
# {'id': 5, 'name': 'lisa'}

cursor.scroll(-2,mode="relative") # 向前滚动
res8 = cursor.fetchone()
print(res8) # 此时ID在5号,在基于下一条数据的基础上再向上两条数据, 5+1-2=4
# {'id': 4, 'name': 'jim'}

# 方式三:绝对滚动,永远基于第一条数据滚动
sql = "select * from t1"
res = cursor.execute(sql)
cursor.scroll(0,mode="absolute")
print(cursor.fetchone()) # {'id': 2, 'name': 'yiyi'} # 滚动0次,第一条数据
cursor.scroll(3,mode="absolute")
print(cursor.fetchone()) # {'id': 5, 'name': 'lisa'} # 滚动3次,第五条数据

# 绝对滚动没有向前滚动,因为前面没数据
cursor.scroll(-1,mode="absolute")
print(cursor.fetchone()) # IndexError: out of range

conn.commit()
cursor.close()
conn.close()

6. 数据导出

第一步: 先退出mysql

第二步: 切换到要存放导出文件的地址

第三步: 执行mysqldump -uroot -p db0619(要导出的数据库) > db0619_2.sql(导出数据库重命名)

导出所有
mysqldump -uroot -p 数据库名 > 数据库名.sql
导出单个
mysqldump -uroot -p db0619 t1 > t1.sql
# 如果是Navicat操作的话,先把要导出的表转储文件>数据和结构,然后打开要导入的数据库,将导出的数据表拖进来,F5刷新

7. 数据库导入操作

第一步: 登录mysql,选择要导入的数据库

第二步: 执行source D:db0619_2(导出的数据库存放的位置)

8. 数据表恢复

(1) 数据是用MyISAM引擎存储的, 直接在数据库文件里复制数据表的三个存储文件, 放到新的数据库文件夹里, 这张表就可以恢复了

(2) 数据是用InnoDB引擎存贮的

# innodb 在只有frm和ibd文件的情况下,如何恢复数据;

安装 MySQL Utilities
https://downloads.mysql.com/archives/utilities/ 

cmd中找到frm那个文件
切换到对应目录,执行下面语句,不要加分号
mysqlfrm --diagnostic ./文件目录/t1.frm


查出建表语句,复制查询出来的建表语句在mysql中创建的新数据中使用


对已创建的表进行表空间卸载 删除ibd文件
alter table employee discard tablespace;

把要恢复的idb文件替换进去

#对已创建的表进行空间装载
alter table employee import tablespace;