Python3操作mysql

来自：

https://blog.csdn.net/e62ces0iem/article/details/73195451

https://blog.csdn.net/zupzng/article/details/80035412

 

python3.x 可以使用pymysql操作mysql，python2.x可以使用mysqldb操作mysql。pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。但目前pymysql支持python3.x而后者不支持3.x版本。

数据库连接对象connection：

Function	描述
connection	创建connection对象
cursor()	使用该链接创建+返回游标
commit()	提交当前事务
rollback()	回滚当前事务
close()	关闭连接

举例--打开数据库连接：

# 打开数据库连接
conn = pymysql.connect(host="192.168.xxx.xxx", user="root",
                     password="123456", db="test", port=3306,charset='utf8')

介绍一下connection的参数：

1. host mysql服务器地址
2. port 数字类型 端口
3. user 用户名
4. passwd 密码
5. db 数据库名称
6. charset 连接编码，需要显式指明编码方式

注：指明的charset必须是"UTF8",不是"utf-8"/"UTF-8"，不然会报'NoneType' object has no attribute 'encoding' (存在中文的时候，连接需要添加charset='utf8'，否则中文显示乱码。)

 

举例--使用cursor()方法获取操作游标：

# 使用cursor()方法获取操作游标
cur = conn.cursor()

介绍一下数据库游标对象cursor：

Function	描述
execute(op[,args])	执行一个数据库查询和命令
fetchone()	取得结果集下一行
fetchmany(size)	取得结果集size行
fetchall()	取得结果集剩下所有行
rowcount	最近一次execute返回数据的行数或影响行数
close()	关闭cursor

1、查询操作实例：

import pymysql  # 导入 pymysql

# 打开数据库连接
conn = pymysql.connect(host="192.168.2.117", user="root",
                     password="123456", db="test", port=3306,charset='utf8')

# 使用cursor()方法获取操作游标
cur = conn.cursor()

# 1.查询操作
# 编写sql 查询语句  grade、student 对应我的表名
sql = '''
select b.id,a.name,avg(b.score) as avg_score
from grade  b,student a
WHERE a.id = b.id
GROUP BY id
'''
try:
    cur.execute(sql)  # 执行sql语句
    
    results = cur.fetchall()  # 获取查询的所有记录
    print(type(results),results) # 查看查询结果及其数据类型
    print("id", "name", "avg_score")
    # 遍历结果
    for row in results:
        id = row[0]
        name = row[1]
        avg_score = row[2]
        print(id, name, avg_score)
except Exception as e:
    raise e
finally:
    conn.close()  # 关闭连接

 注：若涉及同一服务器多数据库查询，可以不填写具体数据库名称（db=""），在操作表时指明该表对应的数据库即可，如：

sql = '''
select b.id,a.name,avg(b.score) as avg_score
from test.grade  b,test.student a
WHERE a.id = b.id
GROUP BY id
'''

 

2、更新数据库insert/update/delete操作实例：

不同于select操作，这三个操作修改了数据库内容，所以需要commit()，否则数据库没有做相应的更改，但是也不会报错。

按照一般的思路，一般是以下套路：

1. 关闭自动commit:conn.autocommit(False)
2. 出现：conn.rowback()回滚
3. 未出现：conn.commit()

 

插入/修改/删除操作

下面这段脚本，实现insert/update/delete操作。其实这种检错模式不对，这里只做简单raise，后面有更好的方法。 

import pymysql

conn = pymysql.connect(host="192.168.xxx.xxx", user="root",
                     password="123456", db="test", port=3306,charset='utf8')

# 使用cursor()方法获取操作游标
cur = conn.cursor()

sql_insert = """insert into grade values(11,'1001','英语',100);""" # 插入
sql_update = "update grade set score = '%d' where no = %d" # 修改
sql_delete = "delete from grade where no = 11" # 删除
try:
    cur.execute(sql_insert)
    print(cur.rowcount)
    cur.execute(sql_update % (96, 11))
    print(cur.rowcount)
    cur.execute(sql_delete)
    print(cur.rowcount)
    # 提交
    conn.commit()
    print("执行影响行数为：",cur.rowcount)
except Exception as e:
    # 错误回滚
    conn.rollback()
    print(e)
finally:
    print("这里一定会打印")
    conn.close()

 

3、实例 银行转账

可以看一下类思想的SQL操作，其中之前提到过的高级报错模式用到了之前看似无用的rowcount函数，通过查看操作对于数据库的影响来检错。

import os
import sys
import pymysql

class transferMoney(object):
    def __init__(self, conn):
        self.conn = conn

    def transfer(self, sourceID, targetID, money):
        #   其他函数中若是有错会抛出异常而被检测到。
        try:
            self.checkIdAvailable(sourceID)
            self.checkIdAvailable(targetID)
            self.ifEnoughMoney(sourceID, money)
            self.reduceMoney(sourceID, money)
            self.addMoney(targetID, money)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e

    def checkIdAvailable(self, ID):
        cursor = self.conn.cursor()
        try:
            sql = "select * from account where id = %d" % ID  # select语句判断可以用len(rs)
            cursor.execute(sql)
            rs = cursor.fetchall()
            if len(rs) != 1:  # 数据库类思想的报错模式，检查操作对数据库的影响条目。没有达到目标，抛出异常
                raise Exception("账号 %d 不存在" % ID)
        finally:
            cursor.close()

    def ifEnoughMoney(self, ID, money):
        cursor = self.conn.cursor()
        try:
            sql = "select * from account where id = %d and money>=%d" % (ID, money)
            cursor.execute(sql)
            rs = cursor.fetchall()
            if len(rs) != 1:
                raise Exception("账号 %d 不存在 %d Yuan" % (ID, money))
        finally:
            cursor.close()

    def reduceMoney(self, ID, money):
        cursor = self.conn.cursor()
        try:
            sql = "update account set money = money-%d where id=%d" % (money, ID)
            cursor.execute(sql)
            if cursor.rowcount != 1:
                raise Exception("失败减钱")
        finally:
            cursor.close()

    def addMoney(self, ID, money):
        cursor = self.conn.cursor()
        try:
            sql = "update account set money = money+%d where id=%d" % (money, ID)
            cursor.execute(sql)
            if cursor.rowcount != 1:
                raise Exception("失败加款")
        finally:
            cursor.close()


if __name__ == "__main__":
    if len(sys.argv) >= 2:
        sourceID = int(sys.argv[1])
        targetID = int(sys.argv[2])
        money = int(sys.argv[3])

        conn = pymysql.Connect(host='127.0.0.1', port=3306, user='root', passwd='dyx240030', db='imooc', charset='utf8')
        trMoney = transferMoney(conn)

        try:
            trMoney.transfer(sourceID, targetID, money)
        except  Exception as e:
            print("出现问题" + str(e))
        finally:
            conn.close()