python3 操作mysql数据库

着急下班，直接上代码，有时间回来补充说明：

pymysql 为第三方包，使用前需要自行安装，我直接联网安装的，当然，也可以使用源码安装，根据个人情况而定。

pymysql 主要步骤为：1. 得到链接，拿到游标（通过链接，得到cursor()），

2. 通过cursor,调用execute方法执行sql语句，

3. 若查询数，则通过fetchall()(得到所有数据）,fetchone()（得到1条数据）fetchmany()（得到指定条数，数据），

4. 其他的就是有关关闭游标，关闭数据库链接，提交数据，回滚数据，方法如下：

cursor.close()（关闭游标），conn.close()（关闭书库链接）,conn.commit()（提交更改，删除和更新数据时候用到）,conn.rollbak()（回滚数据，删除和更新时候用到。）

pip install pymysql 安装pymysql模块

代码如下：

# -*- coding: utf-8 -*-
"""
Created on Tue Dec 18 09:26:52 2018

@author: 
"""

import pymysql

#得到数据库链接
def getConnMySQL(host,port,user,password,db,charset):
    global MysqlConn
    try:
        MysqlConn = pymysql.connect(host = host,
                                    port = int(port),
                                    user = user,
                                    password = password,
                                    db = db,
                                    charset = charset)
    except:
        print('connect database failed')
        exit(1)
        
#创建表
def createTable():
    try:
        with MysqlConn.cursor() as cursor:
            dropsql = '''drop table if exists student;'''
            cursor.execute(dropsql)
            createsql = '''
                         create table student
                             (id VARCHAR(20) COMMENT 'id',
                              sname VARCHAR(20) COMMENT '姓名',
                              sex VARCHAR(2) comment '性别',
                              age VARCHAR(20) comment '年龄'
                             )
                         ENGINE=INNODB DEFAULT CHARSET = utf8;
                   '''
            b = cursor.execute(createsql)
            print(b)
            
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        
#插入数据
def insertData():
    ids = '3'
    sname = '韩红'
    sex = '女'
    age = '26'

    try:
        with MysqlConn.cursor() as cursor:
            #定义插入语句
            insertsql = """insert into student(id,sname,sex,age)
                           values('%s','%s','%s','%s') """% \
                           (ids,sname,sex,age)
            #执行插入语句
            cursor.execute(insertsql)
            #数据库提交
            MysqlConn.commit()
    except Exception as e:
        MysqlConn.rollback()
        print(e)
    finally:
        cursor.close()

#删除数据
def deleteData():
    try:
        with MysqlConn.cursor() as cursor:
            delsql = "delete from student where id = %s"%(3)
            #执行sql
            cursor.execute(delsql)
            #提交
            MysqlConn.commit()
    except Exception as e:
        print(e)
        #失败回滚
        MysqlConn.rollback()
    finally:
        cursor.close()

#更新数据
def upadteData():
    try:
        with MysqlConn.cursor() as cursor:
            updatesql = "update student set id = '%s' where id = '%s' \
            and age = '%s'"% (4,3,26)
            #执行sql
            cursor.execute(updatesql)
            #提交
            MysqlConn.commit()
    except Exception as e:
        print(e)
        #失败则回滚
        MysqlConn.rollback()
    finally:
        cursor.close()
            
#fetchone通过查询，得到一条数据
def queryDataOne():
    try:
        with MysqlConn.cursor() as cursor:
            #定义查询sql
            querysql = 'select id,sname,sex,age from student;'
            #执行sql
            count = cursor.execute(querysql)
            #简单逻辑，若有需要则在逻辑内加内容
            if count <=0:
                print("not data")
            elif count > 0:
                #得到一条数据
                result = cursor.fetchone()
                #此处返回值为一个元组，要得到具体的值，转为列表，根据索引取值
                print(type(result))
                reslist = list(result)
                ids = reslist[0]
                sname = reslist[1]
                sex = reslist[2]
                age = reslist[3]
                print(ids,sname,sex,age)
            else:
                pass
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        
#得到所查询出来的全部数据        
def queryDataAll():
    try:
        with MysqlConn.cursor() as cursor:
            querysql = 'select id,sname,sex,age from student;'
            sqlQuerycnt = cursor.execute(querysql)
            if sqlQuerycnt <= 0:
                print('table is empty')
            elif sqlQuerycnt > 0:
                res = cursor.fetchall()
                print(type(res))
                for rows in list(res):
                    print('ids = ' +  rows[0])
            else:
                pass
    except Exception as e:
        print(e)
    finally:
        cursor.close()

#获取前N行数据，若库中没有那么多数据，则有多少取多少。
def queryDataMany():
    try:
        with MysqlConn.cursor() as cursor:
            quersql = 'select id,sname,sex,age from student;'
            querCnt = cursor.execute(quersql)
            if querCnt <= 0 :
                print('table is empty')
            elif querCnt > 0:
                res = cursor.fetchmany(10)
#                返回元组（多行情况下，为双重元组，也就是嵌套关系）
#                根据需求，来指定具体取几条，然后再确定逻辑
                print(type(res))
                print(res)
            else:
                pass
    except Exception as e:
        print(e)
    finally:
        cursor.close()

if __name__ == '__main__':
    
    #此处可以写个配置文件，通过读取配置文件的方式来得到这些参数，不宜写死。
    host = '192.168.2.2'
    port = '3306'
    user = 'root'
    password = '123456'
    db = 'mysql'
    charset = 'utf8'
    #链接数据库
    getConnMySQL(host,port,user,password,db,charset)
    #创建表
    createTable()
    #插入数据
    insertData()
    #删除数据
    deleteData()
    #更新数据
    upadteData()
    #查询一条数据
    queryDataOne()
    #查询全部数据
    queryDataAll()
    #得到指定条数数据
    queryDataMany()
    #关闭数据库链接
    MysqlConn.close()