MySQL与Python的交互

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()

最后编辑于：2017.12.08 05:42:12

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,456评论 5赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,370评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,337评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,583评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,596评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,572评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,936评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,595评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,850评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,601评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,685评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,371评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,951评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,934评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,167评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,636评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,411评论 2赞 342

MySQL与Python的交互

1.交互类型

1.安装引入模块

1.Connection对象

2.对象的方法

3.对象的属性

2.增删改查(CRUD)

1.增

2.修改

3.删除

4.查

3.封装

1.测试查询多条数据

2.测试查询一条数据

3.测试增删改

4.登录和注册

推荐阅读更多精彩内容