MySQL与Python的交互

浏览: 1789

1.交互类型

1.安装引入模块

  • 安装mysql模块，在windows和ubuntu中

windows里安装mysql模块

Linux里安装mysql模块

  • 在文件中引入模块
    import pymysql

1.Connection对象

  • 用于建立与数据库的连接
  • 创建对象：调用connect()方法
    conn=connect(参数列表)
  • 参数host：连接的mysql主机，如果本机是'localhost'
  • 参数port：连接的mysql主机的端口，默认是3306
  • 参数db：数据库的名称
  • 参数user：连接的用户名
  • 参数password：连接的密码
  • 参数charset：通信采用的编码方式，默认是'gb2312'，要求与数据库创建时指定的编码一致，否则中文会乱码

2.对象的方法

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

Cursor对象

  • 执行sql语句
  • 创建对象：调用Connection对象的cursor()方法
    cursor1=conn.cursor()

对象的方法

  • close()关闭
  • execute(operation [, parameters ])执行语句，返回受影响的行数
  • fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
  • next()执行查询语句时，获取当前行的下一行
  • fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
  • scroll(value[,mode])将行指针移动到某个位置
  • mode表示移动的方式
  • mode的默认值为relative，表示基于当前行移动到value，value为正则向下移动，value为负则向上移动。相对的
  • mode的值为absolute，表示基于第一条数据的位置，第一条数据的位置为0，绝对的。

举个例子便于理解

# 导入python操作mysql的模块

import pymysql



# 获取连接对象

conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='python01', port=3306, charset='utf8')

# 获取执行工具

cur = conn.cursor()

# sql语句,增删改

#sql = 'select birthday from t_user'

sql = 'select id,name,pwd,birthday from t_user'

# 执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数

count = cur.execute(sql)

print('查询的结果有%s条数据'%count)



#获取第一行

dateOne = cur.fetchone()

print(dateOne)



#向上移动一行

cur.scroll(-1)



#向下移动一行

cur.scroll(1)



cur.scroll(1,mode='absolute')   绝对的，这里指的是第一行

cur.scroll(1,mode='relative')   相对的



#获取所有行的数据

dataAll = cur.fetchall()

print(dataAll)



for temp in dataAll:

     print(temp)

     print(dataAll[-1][2])      #dataAll[-1]得到的是一个用户所有的信息，dataAll[-1][2]获取最后一个人的密码





for temp in cur:

     print(temp)





s = 'id:%s,name:%s,pwd:%s,birthday:%s'

for temp in dataAll:

    print(s%(temp[0],temp[1],temp[2],temp[3]))



# 关闭

cur.close()

conn.close()

3.对象的属性

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

2.增删改查(CRUD)

1.增

  • 创建testInsert.py文件，向学生表中插入一条数据
    #encoding=utf-8
    
import pymysql
    
try:
    
  conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    
  cs1=conn.cursor()
    
  count=cs1.execute("insert into students(sname) values('张良')")
    
  print(count)
    
  conn.commit()
    
  cs1.close()
    
  conn.close()
    
except Exception,e:
    
  print(e)

2.修改

  • 创建testUpdate.py文件，修改学生表的一条数据
    #encoding=utf-8
    
import pymysql
    
try:
    
  conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    
  cs1=conn.cursor()
    
  count=cs1.execute("update students set sname='刘邦' where id=6")
    
  print(count)
    
  conn.commit()
    
  cs1.close()
    
  conn.close()
    
except Exception,e:
    
  print(e)

3.删除

  • 创建testDelete.py文件，删除学生表的一条数据
    #encoding=utf-8
    
import pymysql
    
try:
    
  conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    
  cs1=conn.cursor()
    
  count=cs1.execute("delete from students where id=6")
    
  print(count)
    
  conn.commit()
    
  cs1.close()
    
  conn.close()
    
except Exception as e:
    
  print(e)

4.查

  • 创建testSelectOne.py文件，查询一条学生信息
    import Pymysql
    
try:
    
  conn=Pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    
  cur=conn.cursor()
    
  cur.execute('select * from students where id=7')
    
  result=cur.fetchone()
    
  print result
    
  cur.close()
    
  conn.close()
    
except Exception as e:
    
  print(e)
  • 创建testSelectMany.py文件，查询全部学生信息
    #encoding=utf8
    
import Pymysql
    
try:
    
  conn=Pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    
  cur=conn.cursor()
    
  cur.execute('select * from students')
    
  result=cur.fetchall()
    
  print result
    
  cur.close()
    
  conn.close()
    
except Exception as e:
    
  print(e)

实例一：参数

# 导入python操作mysql的模块

import pymysql

import time



# 获取连接对象

conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='python01', port=3306, charset='utf8')

# 获取执行工具

cur = conn.cursor()

# sql语句,增删改，sql注入

sql = 'insert into t_user(name,pwd,birthday) values(%s,%s,%s)'

# 参数列表

name = input('输入姓名:')

pwd = input('输入密码:')

birthday = input('输入生日:')     # 2017年10月01日-->日期struct_time(--->2017-10-01)

birthday = time.strptime(birthday,'%Y年%m月%d日')      #这里我们就用到了时间与字符串的相互转换(详情见MySQL高级)



params = [name,pwd,birthday]

# 执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数

count = cur.execute(sql,params)

#提交

conn.commit()

print('受影响的行数:%s'%count)

# 关闭

cur.close()

conn.close()

实例二：抛出异常

# 导入python操作mysql的模块

import pymysql



try:

    conn = None

    cur = None

    # 获取连接对象

    conn = pymysql.connect(host='127.0.0.1',

                           user='root',

                           password='123456',

                           database='python01',

                           port=3306,

                           charset='utf8')

    # 模拟异常

    # a = 1 / 0

    # 获取执行工具

    cur = conn.cursor()

    # sql语句,增删改

    sql = 'insert into t_user(name,pwd,birthday) values("小伊","123456",str_to_date("2017年10月20日","%Y年%m月%d日"))'

    # 执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数

    count = cur.execute(sql)

    # 提交

    conn.commit()

    print('受影响的行数:%s' % count)

except Exception as ex:

    # 打印异常信息，测试时候使用，项目上线，去掉

    print(str(ex))

    # 将异常继续抛出

    # raise

finally:

    if cur != None:

        cur.close()

    if conn != None:

        conn.close()

实例三：

# 导入python操作mysql的模块

import pymysql



# 获取连接对象

conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='python01', port=3306, charset='utf8')

# 获取执行工具

cur = conn.cursor()

# sql语句,增删改

#sql = 'select birthday from t_user'

sql = 'select id,name,pwd,birthday from t_user'

# 执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数

count = cur.execute(sql)

print('查询的结果有%s条数据'%count)



#获取第一行

# dateOne = cur.fetchone()

# print(dateOne)





# for temp in cur:

#     print(temp)





s = 'id:%s,name:%s,pwd:%s,birthday:%s'

for temp in dataAll:

    print(s%(temp[0],temp[1],temp[2],temp[3]))



# 关闭

cur.close()

conn.close()

3.封装

这个库的名字：mySqlHelper

    python操作mysql进行增删改查的封装



    1、增删改，代码类似

    2、查询



    代码分析

    1、获取连接对象

    2、sql语句不同，参数不同

    3、获取执行对象

        增删改

        查询

            1、fetchone

            2、fetchall

    4、处理结果

    5、关闭



    面向对象  建立类，封装属性和函数 '''



import pymysql

class MysqlHelper:

    '''python操作mysql的增删改查的封装'''



    def __init__(self, host, user, password, database, port=3306, charset='utf8'):

        '''

        初始化参数

        :param host:        主机

        :param user:        用户名

        :param password:    密码

        :param database:    数据库

        :param port:        端口号，默认是3306

        :param charset:     编码，默认是utf8

        '''

        self.host = host

        self.port = port

        self.database = database

        self.user = user

        self.password = password

        self.charset = charset



    def connect(self):

        '''

        获取连接对象和执行对象

        :return:

        '''

        self.conn = pymysql.connect(host=self.host,

                                    user=self.user,

                                    password=self.password,

                                    database=self.database,

                                    port=self.port,

                                    charset=self.charset)



        self.cur = self.conn.cursor()



    def fetchone(self, sql, params=None):

        '''

         根据sql和参数获取一行数据

       :param sql:          sql语句

       :param params:       sql语句对象的参数元组，默认值为None

       :return:             查询的一行数据

       '''

        dataOne = None

        try:

            count = self.cur.execute(sql, params)

            if count != 0:

                dataOne = self.cur.fetchone()

        except Exception as ex:

            print(ex)

        finally:

            self.close()

        return dataOne



    def fetchall(self, sql, params=None):

        '''

         根据sql和参数获取一行数据

       :param sql:          sql语句

       :param params:       sql语句对象的参数列表，默认值为None

       :return:             查询的一行数据

       '''

        dataall = None

        try:

            count = self.cur.execute(sql, params)

            if count != 0:

                dataall = self.cur.fetchall()

        except Exception as ex:

            print(ex)

        finally:

            self.close()

        return dataall



    def __item(self, sql, params=None):

        '''

        执行增删改

        :param sql:           sql语句

        :param params:        sql语句对象的参数列表，默认值为None

        :return:              受影响的行数

        '''

        count = 0

        try:

            count = self.cur.execute(sql, params)

            self.conn.commit()

        except Exception as ex:

            print(ex)

        finally:

            self.close()

        return count



    def update(self, sql, params=None):

        '''

        执行修改

        :param sql:     sql语句

        :param params:  sql语句对象的参数列表，默认值为None

        :return:        受影响的行数

        '''

        return self.__item(sql, params)



    def insert(self, sql, params=None):

        '''

        执行新增

        :param sql:     sql语句

        :param params:  sql语句对象的参数列表，默认值为None

        :return:        受影响的行数

        '''

        return self.__item(sql, params)



    def delete(self, sql, params=None):

        '''

        执行删除

        :param sql:     sql语句

        :param params:  sql语句对象的参数列表，默认值为None

        :return:        受影响的行数

        '''

        return self.__item(sql, params)



    def close(self):

        '''

        关闭执行工具和连接对象

        '''

        if self.cur != None:

            self.cur.close()

        if self.conn != None:

            self.conn.close()

1.测试查询多条数据

import mysqlHelper



# 初始化对象

helper = mysqlHelper.MysqlHelper('127.0.0.1', 'root', '123456', 'python01')

# 连接

helper.connect()

# sql

sql = 'select * from t_user where name = %s and id > %s'

# params

params = ['小茗',1]

# 执行

data = helper.fetchall(sql, params)

# 判断

if data:

    for temp in data:

        print(temp)

else:  # None,False,0

    print('没有数据.')



helper.close()

2.测试查询一条数据

import mysqlHelper



# 初始化对象

helper = mysqlHelper.MysqlHelper('127.0.0.1', 'root', '123456', 'python01')

# 连接

helper.connect()

# sql

sql = 'select * from t_user where id = %s'

#sql = 'select * from t_user where id = 1'

# params

params = [2]

# 执行

data = helper.fetchone(sql, params)

#data = helper.fetchone(sql)

# 判断

if data:

    print(data)

else:  # None,False,0

    print('没有数据.')

3.测试增删改

import mysqlHelper

import time



# 初始化对象

helper = mysqlHelper.MysqlHelper('127.0.0.1', 'root', '123456', 'python01')

# 连接

helper.connect()

# sql

sql = 'update t_user set name =%s,pwd=%s,birthday=%s where id=%s'

# params

id = input('输入编号:')

name = input('输入姓名:')

pwd = input('输入密码:')

birthday = time.strptime(input('输入生日:'), '%Y年%m月%d日')

params = [name, pwd, birthday,id]

# 执行

count = helper.update(sql, params)

# 判断

if count:

    print('操作成功.')

else:  # None,False,0

    print('操作失败.')

4.登录和注册

登录和注册的时候需要对密码进行加密

注意：

  • 需要对密码进行加密
  • 如果使用md5加密，则密码包含32个字符
  • 如果使用sha1加密，则密码包含40个字符，这里使用这种方式
create table userinfos(

    id int primary key auto_increment,

    uname varchar(20),

    upwd char(40),

    isdelete bit default 0

);



/*

ret = doPwd('123')

print(ret)

结果：40bd001563085fc35165329ea1ff5c5ecbdbbeef

              */



-- 插入如下数据，用户名为123,密码为123,这是sha1加密后的值

insert into userinfos 

values(1,'123','40bd001563085fc35165329ea1ff5c5ecbdbbeef',0);

登录与注册

from mysqlHelper import MysqlHelper

import hashlib





def login():

    '''登录'''

    name = input('输入用户名:')

    pwd = input('输入密码:')

    #加密

    pwd = doPwd(pwd)



    helper = MysqlHelper('127.0.0.1', 'root', '123456', 'python01')

    helper.connect()

    sql = 'select * from t_user where name=%s and pwd=%s'

    params = [name, pwd]

    data = helper.fetchone(sql, params)

    if data:

        print('登录成功.')

    else:  # None,False,0

        print('登录失败.')





def doPwd(pwd):

    '''sha1编码'''

    mysha1 = hashlib.sha1()

    mysha1.update(pwd.encode('utf-8'))

    pwd = mysha1.hexdigest()

    return pwd





def register():

    '''注册'''

    name = input('输入用户名:')

    pwd = input('输入密码:')

    # 加密

    pwd = doPwd(pwd)





    helper = MysqlHelper('127.0.0.1', 'root', '123456', 'python01')

    helper.connect()

    sql = 'insert into t_user(name,pwd) values(%s,%s)'

    params = [name, pwd]

    count = helper.insert(sql, params)

    if count:

        print('操作成功.')

    else:  # None,False,0

        print('操作失败.')





if __name__ == '__main__':

    #register()

    login()
推荐 1
扁舟
本文由 一只写程序的猿 创作，采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者，并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录或注册