数据库（MySQL 8.0）--6

MySQL与Python的交互

安装模块

sudo apt-get install python-mysqldb #这是pyhton2里面的模块，python3是PyMySQL

Connection对象
作用：用于建立与数据库的连接
创建对象：调用connect()方法conn = connect(参数列表)
参数host：连接的mysql主机，本机是"localhost"
参数port：连接的mysql主机的端口，默认是3306
参数db：数据库的名称
参数user：连接的用户名
参数passwd：连接的用户的密码
参数charset：通信采用的编码方式，推荐使用"utf8"

对象conn的方法

close() #关闭连接
commit() #事务提交才能生效
rollback() #事务放弃之前的操作
cursor() #返回Cursor对象，用于执行sql语句并获得结果

Cursor对象
作用：执行sql语句
创建对象：调用Connection对象的cursor()方法

cursor1 = conn.cursor()

对象cursor1的方法

close() #关闭
execute(operation[,parameters]) #执行语句，返回结果
fetchone() #执行查询语句时获得查询结果集的第一行数据，返回元组
next() #执行查询语句时获得当前行的下一行
fetchall() #执行查询时获得结果集的所有行，一行构成一个元组，再将这些元组放在一个大元组
scroll(value[,mode]) #将行指针移动到某个位置
#mode表示移动方式，默认值为relative，表示基于当前行移动到value（value为+则向下移动为-则向上移动）
#mode的值为absolute表示基于第一条数据的位置（为0）

对象cursor1的属性
rowcount只读属性，表示最近一次execute()执行后受影响的行数；
connection获得当前连接对象

pycharm连接mysql
添加：
添加前表是这样的

#coding=utf-8

from pymysql import *

#打开数据库连接
db = connect(host="localhost" ,port=3306, user="root",passwd='******', db='python3')
#注意这里赋值要把前面的赋值的对象写出来，如host="localhost"，对应项赋值，不要会报错

#使用cursor()方法创建一个游标对象
cursor = db.cursor()

#使用execute()方法执行SQL查询
sql = """insert into students values(0,'许会芬',0,'1995-08-11',1)"""
cursor.execute(sql)

db.commit() #因为默认是打开事务模式，所以要commit是执行生效
cursor.close()
db.close()

在pycharm中执行上面的代码，结果是这样的

注：修改，删除等操作就是更改sql的语句

sql语句参数化
目的：执行的时候提供额外的参数，为了数据库数据操作的安全性
比如：a’ or 1=1 or ’
select * from students where name=’’
也就是select * from students where name=‘a’ or 1=1 or ''就会出现问题了

#coding=utf-8

from pymysql import *

#打开数据库连接
db = connect(host="localhost" ,port=3306, user="root",passwd='123123', db='python3')

#使用cursor()方法创建一个游标对象
cursor = db.cursor()

#使用execute()方法执行SQL查询
#sql = "insert into students values(0,'许会芬',0,'1995-08-11',1)" #添加
#sql1 = "delete from students where id=11" #删除

paramList = ["曹旭",'1996-04-09'] #将参数列表化
sql2 = "insert into students(sname,birthday) values(%s,%s)" #不管数据类型都是用%s占位
cursor.execute(sql2,paramList) #列表顺序应该对应(sname,birthday)

db.commit() #因为默认是打开事务模式，所以要commit是执行生效
cursor.close()
db.close()

封装
从上面的代码可以看出很多代码都在重复使用，所以我们考虑用封装

#coding=utf-8

from pymysql import *

#用类来封装
class MysqlOperation(object): #创建类，类名自己定
    def __init__(self,host,port,user,passwd,db,charset='utf8'): #有默认参数的要放在后面，否则报错
        self.host = host
        self.port = port
        self.user = user
        self.passwd = passwd
        self.db = db
        self.charset = charset

    def ConnOpen(self): #创建打开函数
    #定义了两个对象，连接对象和游标对象
        self.conn = connect(host=self.host,port=self.port,user=self.user,
                            passwd=self.passwd,db=self.db,charset=self.charset)
        self.cursor = self.conn.cursor()

    def ConnClose(self): #有打开就要有关闭
        self.cursor.close()
        self.conn.close()

    def CUD(self,sql,params): #增改删操作
        try:
            self.ConnOpen() #创建两个对象

            self.cursor.execute(sql,params) #执行
            self.conn.commit() #提交事务

            self.ConnClose() #关闭两个对象

            print("OK") #如果成功则打印OK

        except Exception as e: #如果出错则返回错误信息
            print(e.message)

    def ReturnAll(self,sql,params): #封装了查询所有数据的操作
        try:
            self.ConnOpen()

            self.cursor.execute(sql, params)
            result = self.cursor.fetchall() #返回查询的所有数据

            self.ConnClose()

            return result #要有返回值作为接受值

        except Exception as e:
            print(e.message)

#调用封装的类
params = ["飞飞","1998-09-24"]
sql = "insert into students(sname,birthday) values(%s,%s)"

mysql = MysqlOperation("localhost",3306,"root","123123","python3") #传参数
mysql.CUD(sql,params) #执行函数

可以看到在终端的结果

小练习：用户登录
思路：

首先我们创建一个用户信息表，passwd是SHA1加密过的

添加数据（这里的密码是1234qwer）

在pycharm添加以下代码

#coding=utf-8

from pymysql import *
#用类来封装
class MysqlOperation(object): #创建类，类名自己定
    def __init__(self,host,port,user,passwd,db,charset='utf8'): #有默认参数的要放在后面，否则报错
        self.host = host
        self.port = port
        self.user = user
        self.passwd = passwd
        self.db = db
        self.charset = charset

    def ConnOpen(self): #创建打开函数
    #定义了两个对象，连接对象和游标对象
        self.conn = connect(host=self.host,port=self.port,user=self.user,
                            passwd=self.passwd,db=self.db,charset=self.charset)
        self.cursor = self.conn.cursor()

    def ConnClose(self): #有打开就要有关闭
        self.cursor.close()
        self.conn.close()

    def CUD(self,sql,params): #增改删操作
        try:
            self.ConnOpen() #创建两个对象

            self.cursor.execute(sql,params) #执行
            self.conn.commit() #提交事务

            self.ConnClose() #关闭两个对象

            print("OK") #如果成功则打印OK

        except Exception as e: #如果出错则返回错误信息
            print(e.message)

    def ReturnAll(self,sql,params): #封装了查询所有数据的操作
        try:
            self.ConnOpen()

            self.cursor.execute(sql, params)
            result = self.cursor.fetchall() #返回查询的所有数据

            self.ConnClose()

            return result #要有返回值作为接受值

        except Exception as e:
            print(e.message)

#主代码：
from hashlib import sha1 #python自带的加密包hashlib，选中hashlib按Ctrl+B可以看到源码

#接受用户数输入
name = input("请输入用户名：")
pwd = input("请输入密码：")

#对密码加密，在源码里面可以看见模块的使用方法
sh = sha1()
sh.update(b"pwd")
pwd2 = sh.hexdigest()
print("密码加密后的结果是：",pwd2)

#根据用户名查询密码
sql = "select passwd from users where name=%s"
mySql = MysqlOperation("localhost",3306,"root","123123","python3")
result = mySql.ReturnAll(sql,name) #查到就按元组返回(('37fa265330ad83eaa879efb1e2db6380896cf639'),)
#如果结果为空就返回空元组()
#print(result)

#也就是说如果返回的是空元组，则用户名不存在
if len(result)==0:
    print("用户名错误")

elif result[0][0]==pwd2:
    print("登录成功")

else:
    print("密码错误")

在pycharm上的执行结果：