Navicat软件、python操作MySQL
目录
Navicat软件
1. 软件简介
第三方开发的用来充当数据库客户端的简单快捷的操作界面
无论第三方软件有多么的花里胡哨，底层的本质还是SQL
能够操作数据库的第三方可视化软件有很多，其中针对MySQL最出名的就是Navicat
1.浏览器搜索Navicat直接下载
版本很多、能够充当的数据库客户端也很多
# 它不是免费的，收费的，免费试用14天
2.破解方式
先试用再破解、直接下载破解版(老版本)、修改试用日期
去官网下载：https://www.navicat.com.cn/products/
Navicat就是MySQL的第三方客户端
2.navicat基本操作
1.连接MySQL
连接 --> MySQL -->
连接名(自己起个名字)
# 127.0.0.1---------目前记忆是一样的-------->localhost
# 补充
连接mysql命令：
mysql -h127.0.0.1 -P3306 -uroot -p123456
-h(host):主机，填写ip
-P:端口,3306
# 自己电脑
mysql -u root -p
2.新建数据库
右键选择新建数据库
字符集:unf8/utf8mb4
排序规则:utf8_general_ci
# 客户端和Navicat软件绑定了，客户端的数据会直接在Navicat中显示，而Navicat软件创建的表或数据，在客户端也能够查到
3.新建表
进入库--> 表右击新建表
table键切换
id --> 对于主键最好加上注释:主键id.自动递增在左下角
默认值：EMPTY STRING --> Null
SQL预览：建立字段还没有保存时，可以直接查看它，看SQL语句
注释:于字段同级别的注释是针对于表的注释。
4.添加记录
双击进入表
SQL语句：右侧DDL，是创建表的完整语法
填写完数据之后，左下角对勾保存
5.筛选
点击加号
点击字段，可以切换字段名；包含,可以切换筛选条件；<?>,可以切换值。也可以多条件筛选
点击应用
6.已经建好的表，更改表结构
右击表 --> 设计表
7.执行SQL语句
点击新建查询
选择连接和库
内部就可以直接写SQL语句了，有代码提示功能
点击运行
语句过长，可以使用：美化SQL
8.导出
右键--> 转储SQL文件
9.导入
方式1：新建库，直接复制SQL语句，运行
方式2：右击库，运行SQL文件
10.数据传输
# 复制表，库内的所有表
工具 --> 数据传输
11.模型
# 可以更好的理解表与表之间的关系
库右键 --> 逆向数据模型
python操作MySQL
# 需要借助于第三方模块
# mysqldb
# mysqlclient... 在Django中用，要比这两个模块好用,一般情况下，安装不上(后期讲解)
# pymysql
安装：
pip install pymysql;
1.链接、执行sql、关闭（游标）
import pymysql # 操作MySQL的第三方模块
# 1.连接MySQL服务端
# 此时python链接mysql，python就是一个客户端
# 既然是客户端，那就需要链接服务端，保证服务端的正常运行
conn = pymysql.connect( # conn链接对象
host='127.0.0.1',
port=3306,
user='root',
password='1113',
db='db6', # 数据库必须是存在的
charset='utf8'
)
# 2.获取一个游标
# 游标对象就相当于光标(闪着的哪个)
# cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示 ((),())
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # [{},{}]
# 3.先写SQL语句,查询数据
sql = 'select * from t2'
# 4.发送SQL语句
affect_rows = cursor.execute(sql) # execute也有返回值,接收的是SQL语句影响的行数
print(affect_rows) # 2，行数是2
# 5.获取SQL语句执行之后的结果，游标对象点方法
# ((1, 'kevin', 20), (2, 'tom ', 22))
# [{'id': 1, 'name': 'kevin', 'age': 20}, {'id': 2, 'name': 'tom ', 'age': 22}]
print(cursor.fetchall())
2.字典可以循环取值： # 这一步跟上一步的打印只能存在一个，同时存在，上一步完全输出了，循环只能是空。
for i in cursor1.fetchall():
print(i)
2.查询结果
1.获取数据
fetchall() 获取所有的结果
fetchone() 获取结果集的第一个数据
fetchmany() 获取指定数量的结果集
ps:注意三者都有类似于文件光标移动的特性
2.光标滚动(很少用)
cursor.scroll(移动个数,模式)
cursor.scroll(1) # 默认模式，基于当前位置往后移动
cursor.scroll(1,'relative')
cursor.scroll(0,'absolute') # 基于数据的开头往后移动
代码操作：
1.fetchall()
一次性拿到全部后，执行同样的语句，就输出空列表(第一次取完了，再取就没有了)
# 5.拿到查询结果
print(cursor1.fetchall()) # [{'id': 1, 'name': 'kevin', 'age': 20}, {'id': 2, 'name': 'tom ', 'age': 22}]
print(cursor1.fetchall()) # []
2.分布拿取fetchone()
# 5.拿到查询结果
print(cursor1.fetchone()) # {'id': 1, 'name': 'kevin', 'age': 20}
print(cursor1.fetchone()) # {'id': 2, 'name': 'tom ', 'age': 22}
print(cursor1.fetchone()) # None
3.指定拿取:fetchmany(1),括号内的数据是拿取数量，表中没有那么多就有几条取几条
# 5.拿到查询结果
# print(cursor1.fetchmany(1)) # [{'id': 1, 'name': 'kevin', 'age': 20}]
# print(cursor1.fetchmany(2)) # [{'id': 1, 'name': 'kevin', 'age': 20}, {'id': 2, 'name': 'tom ', 'age': 22}]
print(cursor1.fetchmany(5)) # [{'id': 1, 'name': 'kevin', 'age': 20}, {'id': 2, 'name': 'tom ', 'age': 22}]
3.增删改查
autocommit=True # 针对增、删、改，自动确认(直接配置)
conn.commit() # 针对增、删、改，需要二次确认(代码确认)
代码验证：
方式1：代码确认
'''插入数据'''
"""除了查询之外的语句，都需要二次确认"""
import pymysql # 操作MySQL的第三方模块
# 1.链接MySQL
# 此时python链接mysql，python就是一个客户端
# 既然是客户端，那就需要链接服务端，保证服务端的正常运行
conn = pymysql.connect( # conn链接对象
host='127.0.0.1',
port=3306,
user='root',
password='1113',
db='db6', # 数据库必须是存在的
charset='utf8'
)
# 2.获取一个游标
cursor2 = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.先写SQL语句
sql = 'insert into t2(name,age) values("tank",33)'
# 4.执行SQL语句
affect_rows = cursor2.execute(sql)
# 二次确认,链接对象.commit()
conn.commit()
print(affect_rows)
方式2：直接在连接对象时加一个参数，autocommit=True
就不需要再写conn.commit()这句话
conn1 = pymysql.connect( # conn链接对象
host='127.0.0.1',
port=3306,
user='root',
password='1113',
db='db6', # 数据库必须是存在的
charset='utf8',
autocommit=True
)
# 2.获取一个游标
cursor3 = conn1.cursor(cursor=pymysql.cursors.DictCursor)
# 3.先写SQL语句
sql = 'insert into t2(name,age) values("tank",33)'
# 4.执行SQL语句
affect_rows = cursor3.execute(sql)
print(affect_rows) # 1
4.使用pymysql写一个注册和登录的案例
# 先写一个注册和登录功能
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='1113',
db='db6',
charset='utf8',
autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 1.获取用户名和密码
username = input('请输入用户名：').strip()
password = input('请输入密码：').strip()
# 2.构造查询SQL语句
sql = 'select * from userinfo where name="%s" and pwd="%s"' %(username,password)
# 3.发送给服务端并执行SQL语句
cursor.execute(sql)
# 4.获取执行结果
res = cursor.fetchall()
if res:
print('登录成功')
print(res)
else:
peint('用户名或密码错误')
# 注册
# 1.获取用户名和密码
username = input('usename:>>>').strip()
password = input('password:>>>').strip()
# 2.构造查找SQL语句
sql = "insert into userinfo(name,password) values('%s','%s')" % (username, password)
# 3.发送给服务端并执行SQL语句
cursor.execute(sql)
# 4.获取执行结果
res = cursor.fetchall()
print(res)
5.SQL注入问题
1.什么是sql注入
怪像1:输对用户名就可以登录成功
kevin ' -- hgahgueaguaehg
怪像2:不需要对的用户名和密码也可以登录成功
xxx ' or 1=1 -- vhioshuihabzjb
SQL注入:利用特殊符合的组合产生特殊的含义，从而避开正常的业务逻辑
# 显示出的SQL语句
select * from userinfo where name='kevin ' -- hgahgueaguaehg' and password=''
select * from userinfo where name='xxx ' or 1=1 -- vhioshuihabzjb' and password=''
"""
实际生活中 尤其是在注册用户名的时候 会非常明显的提示你很多特殊符号不能用
原因也是一样的
结论:设计到敏感数据部分 不要自己拼接 交给现成的方法拼接即可
"""
2.如何解决
针对上述的SQL注入问题，核心在于手动拼接了关键数据，交给execute处理即可
sql = " select * from userinfo where name=%s and password=%s "
cursor.execute(sql, (username, password))
'''
补充说明
一次性传入多条数据
executemany(sql,[('oscar',123),('maria',123),('lili',123),('xiaohua',333)])
'''
问题解决后的注册和登录操作：
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='1113',
db='db6',
charset='utf8',
autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 1.获取用户名和密码
username = input('usename:>>>').strip()
password = input('password:>>>').strip()
# 2.构造查找SQL语句
sql = "select * from userinfo where name =%s and password=%s"
# 3.发送给服务端并执行SQL语句
cursor.execute(sql, (username, password))
# 4.获取执行结果
res = cursor.fetchall()
if res:
print('登陆成功')
print(res)
else:
print('用户名和密码错误')
# 注册
# 1.获取用户名和密码
username = input('usename:>>>').strip()
password = input('password:>>>').strip()
# 2.构造查找SQL语句
sql = "insert into userinfo(name,password) values(%s,%s)"
# 3.发送给服务端并执行SQL语句
cursor.execute(sql, (username, password))
# 4.获取执行结果
res = cursor.fetchall()
print(res)
print('注册成功')
