可视化工具Navicat的使用/pymysql模块的使用

一.可视化工具Navicat的使用

1.官网下载:http://www.navicat.com/en/products/navicat-for-mysql

2.网盘下载:http://pan.baidu.com/s/1bpo5maj

3.需要掌握的基本操作:

1.测试+链接数据库
2.新建库
3.新建表,新增字段+类型+约束
4.设计表:外键
5.新建查询
6.备份库/表

注意:
批量添加注释:Ctrl+/ 键
批量去注释:Ctrl+shift+/ 键

PS:在生产环境中操作MySQL数据库还是推荐使用命令工具mysql,但我们自己开发测试时,可以使用可视化工具Navicat,以图形界面的形式操作MySQL数据库

二.pymysql模块的使用

1.pymysql的下载和使用

之前我们都是通过MySQL自带的命令行客户端mysql来操作数据库,那如何在python程序中操作数据库呐?这就用到了pymysql模块,该模块的本质就是一个套接字客户端软件,使用前需要事先安装

(1).pymysql模块的下载

pip3 install pymysql

(2).pymysql的使用(数据库和数据都已存在)

#实现:使用python实现用户登录,如果用户存在则登录成功
import pymysql
user = input("请输入用户名:")
pwd = input("请输入密码:")

#1.连接
conn = pymysql.connect(host = "localhost",
                       port = 3306,
                       user = "root",
                       password = "",
                       database = "db8",
                       charset = "utf8")

#2.创建游标
cursor = conn.cursor()

#注意%S需要加引号
sql = "select * from userinfor where name = '%s' and pwd = '%s'"%(user,pwd)
print(sql)

#执行sql语句,返回sql查询成功的记录数目
result = cursor.execute(sql)
print(result)

#关闭连接,游标和连接都要关闭
cursor.close()
conn.close()

if result:
    print("登陆成功!!")
else:
    print("登陆失败!!")

2.execute()之sql注入

#最后那一个空格,在一条sql语句中如果select * from userinfor where username = "wahaha" -- asadsadf" and pwd = "" 则--之后的条件被注释掉了(注意--后面还有一个空格)

#1.sql注入之:用户存在,绕过密码
wahaha" -- 任意字符
#2.sql注入之:用户不存在,绕过用户与密码
xxx" or 1=1 -- 任意字符

(1).解决方法:

#原来是我们自己对sql字符串进行拼接
#sql = "select * from userinfo where username='%s' and pwd='%s'" %(user, pwd)
#print(sql)
#result = cursor.execute(sql)

#该写为(execute帮我们做好的字符串拼接,我们无需且一定不要再为%s加引号了)
sql = "select * from userinfor where name = %s and password = %s"    #注意%s需要去掉引号,因为pymysql会自动帮我们加上
result = cursor.execute(sql,[user,pwd])    #pymysql模块自动帮我们解决sql注入的问题,只要我们按照pymysql的规矩来

3.增,删,改: conn.commit()

commit()方法: 在数据库里增,删,改的时候,必须要进行提交,否则插入的数据不生效

import pymysql
#连接
conn = pymysql.connect(host = "localhost", port = 3306, user = "root", password = "", db = "db8", charset = "utf8")

#创建游标
cursor = conn.cursor()

# #增:
# sql = "insert into userinfor(name,pwd) values (%s,%s)"
# ope_data = cursor.execute(sql,(user,pwd))
#
# #同时插入多条数据
# # cursor.executemany(sql,[("乳娃娃","111"),("爽歪歪","222")])
# print(ope_data)

# #删
# sql = "delete from userinfor where id = 2"
# ope_data = cursor.execute(sql)
# print(ope_data)

# #改
# user = input("输入新的用户名:")
# sql = "update userinfor set name = %s where id = 4"
# ope_data = cursor.execute(sql,user)
# print(ope_data)

#一定要记得commit
conn.commit()

#关闭游标
cursor.close()
#关闭连接
conn.close()

4.查:fetchone,fetchmany,fetchall

fetchone():获取下一行数据,第一次为首行
fetchall():获取所有行数据源
fetchmany(4):获取4行数据

(1).表中内容

(2).使用fetchone():

import pymysql

conn = pymysql.connect(host = "localhost", port = 3306, user ="root", password = "", db  ="db8", charset = "utf8")

cursor = conn.cursor()

#sql语句
sql = "select * from userinfor"
cursor.execute(sql)

#查询第一行的数据
row = cursor.fetchone()
print(row)

#查询第二行的数据
row = cursor.fetchone()
print(row)

cursor.close()
conn.close()

(3).使用fetchall():

import pymysql
conn = pymysql.connect(host = "localhost", port = 3306, user = "root", password = "", db = "db8", charset = "utf8")

cursor = conn.cursor()

sql = "select * from userinfor"
cursor.execute(sql)

rows = cursor.fetchall()
print(rows)

cursor.close()
conn.close()

#运行结果
# ((1, 'wahaha', '123'), (2, '冰红茶', '111'), (3, '黑茶', '222'), (4, '花茶', '333'))

默认情况下,我们获取到的返回值是一个元组,只能看到每行的数据,却不知道没列代表的是什么,这个时候可以使用以下方式来返回字典,每行的数据都会生成一个字典:

#返回字典
#在实例化的时候，将属性cursor设置为pymysql.cursors.DictCursor
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

在fetchone示例中,在获取行数据的时候,可以理解开始的时候,有一个行指针指着第一行的上方,获取一行,它就向下移动一行,所以当行指针到最后一行的时候,就不能再获取到行的内容,所以我们可以使用如下方法来移动行指针:

cursor.scroll(1,mode='relative')  # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
第一个值为移动的行数，整数为向下移动，负数为向上移动，mode指定了是相对当前位置移动，还是相对于首行移动

import pymysql

conn = pymysql.connect(host = "localhost", port = 3306, user ="root", password = "", db  ="db8", charset = "utf8")

cursor = conn.cursor()

#sql语句
sql = "select * from userinfor"
cursor.execute(sql)

#查询第一行的数据
row = cursor.fetchone()
print(row)

#查询第二行的数据
row = cursor.fetchone()
print(row)

cursor.scroll(-1,mode="relative")   #设置之后,光标相对于当前位置往前移动了一行,所以打印的结果为第二行的数据
row = cursor.fetchone()
print(row)

cursor.scroll(0,mode="absolute")    #设置之后,光标相对于首行没有任何变化,所以打印的结果为第一行数据
row = cursor.fetchone()
print(row)

cursor.close()
conn.close()

#结果:
(1, 'wahaha', '123')
(2, '冰红茶', '111')
(2, '冰红茶', '111')
(1, 'wahaha', '123')

(4).使用fetchmany():

import pymysql

conn = pymysql.connect(host = "localhost", port = 3306, user = "root", password = "", db = "db8", charset = "utf8")
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

sql = "select * from userinfor"
cursor.execute(sql)

#获取两条数据
rows = cursor.fetchmany(2)
print(rows)

#关闭游标
cursor.close()
#关闭连接
conn.close()

# 结果:
[{'id': 1, 'name': 'wahaha', 'pwd': '123'}, {'id': 2, 'name': '冰红茶', 'pwd': '111'}]