[每日一题]20、Python操作MySQL

第一时间收到精彩推送！

Python Every Day， 第 20 天

---

    

在工作中，不管Web开发或者爬虫，数据分析等凡是与数据有接触的都离不开数据库。数据库又分为关系型数据库还是非关系型数据库，关系型数据库中最常用应该就是MySQL了。

在Python中可以通过PyMySQL库完成对其的操作。

安装PyMySQL

pip3 install pymysql


操作过程大概分为如下几步

0、在MySQL中创建库和表

1、创建数据库连接

2、创建cursor，用于执行sql

3、编写并执行SQL

4、获取数据

5、关闭连接

通过下面一个简单的例子给大家演示一下

创建test库，并在该库中创建fund_info（资产信息表）

CREATE DATABASE `test` ;
use test;
CREATE TABLE `fund_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(50) NOT NULL COMMENT '账号',
  `amount` decimal(10,2) DEFAULT NULL COMMENT '总金额',
  `consume` decimal(10,2) DEFAULT '0.00' COMMENT '消费金额',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `modify_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

具体操作(增删改查)看示例代码吧，在代码中尽可能的都注释了。

import pymysql

# 创建MySQL连接
connection = pymysql.connect(host='127.0.0.1',  # MySQL服务器地址
                             port=3306,         # 服务端口号
                             user='root',       # MySQL用户名
                             password='root',   # MySQL密码
                             db='test',         # 要操作的数据库名称
                             charset='utf8mb4') # 连接编码
# 创建cursor, 用于执行sql
cursor = connection.cursor()

#  增加两条记录。
insert_sql = 'insert into fund_info (account, amount) values ('[e-mail]', 100.00)'
insert_sql1 = 'insert into fund_info (account, amount) values ('[e-mail]', 99.00)'
# 执行上面两个sql，
cursor.execute(insert_sql)
cursor.execute(insert_sql1)
# 执行增 删 改时 需要commit，不然不会生效
connection.commit()

# 查询刚才插入的两条数据
cursor.execute('select * from fund_info')
# fetchall 查看执行的全部结果，返回一个tuple
result_all = cursor.fetchall()
''' 输出：
result_all : 
((2, '[e-mail]', Decimal('100.00'), Decimal('0.00'), datetime.datetime(2019, 8, 7, 16, 57, 49), datetime.datetime(2019, 8, 7, 16, 57, 49)), 
(3, '[e-mail]', Decimal('99.00'), Decimal('0.00'), datetime.datetime(2019, 8, 7, 16, 57, 49), datetime.datetime(2019, 8, 7, 16, 57, 49)))
'''
print('result_all :', result_all)

# 通过id查询
cursor.execute('select amount from fund_info where account='[e-mail]'')
# fetone 仍然返回元组
result_amount = cursor.fetchone()
print(result_amount) # (Decimal('100.00'),)


# 更新 账号：[e-mail] 的amount值为200.00
cursor.execute('update fund_info set amount=200.00 where account='[e-mail]'')
# 执行增 删 改时 需要commit，不然不会生效
connection.commit()
print('更新成功.')

cursor.execute('delete from fund_info where account='[e-mail]'')
# 执行增 删 改时 需要commit，不然不会生效
connection.commit()
print('删除成功.')

# 操作完毕之后，必须要关闭连接
cursor.close()
connection.close()


以上是用PyMySQL操作MySQL的基本知识。这里想再啰嗦一下事务。

事务

事务: 事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。

通俗的将：我执行多个sql如果其中一个失败了，那要将之前修改的数据进行还原。回滚到执行前的状态。

拿我们上面的sql来举例，对于一个账号 account，资金amount、消费金额consume。我们购物的时候，消费金额增加的同时，钱包里的金额就要对应的减少。  这个增加和减少就是一个事务，不可能只增加消费金额，而不减少总金额。

今天只说一下PyMySQL对事务的处理(如果在MySQL终端进行事务操作，需要手动开启 -- start transaction;)

直接看下面的例子

当前金额：

mysql> select amount from fund_info where account = '[e-mail]';
+--------+
| amount |
+--------+
|  99.00 |
+--------+
1 row in set (0.00 sec)

执行消费代码

import pymysql

connection = pymysql.connect(host='127.0.0.1',  # MySQL服务器地址
                             port=3306,         # 服务端口号
                             user='root',       # MySQL用户名
                             password='root',   # MySQL密码
                             db='test',         # 要操作的数据库名称
                             charset='utf8mb4') # 连接编码
# 创建cursor, 用于执行sql
cursor = connection.cursor()

# 代表消费的金额
price = 15

# 此时账户zxc的总金额为99
# 账户[e-mail] 总金额 - price
sql_1 = f'update fund_info set amount = amount - {price} where account = '[e-mail]''
# 账户[e-mail] 消费金额 + price
sql_2 = f'update fund_info set consume = consume + {price} where account = '[e-mail]''

try:
    # 查询余额是否足够
    cursor.execute('select amount from fund_info where account = '[e-mail]'')
    result = cursor.fetchone()
    print(result[0])
    # 如果余额不足 抛出异常.
    if not result or result[0] < price:
        raise Exception('余额不足...')
    cursor.execute(sql_1)
    print('========= 其他业务逻辑  执行中....')
    cursor.execute(sql_2)
except Exception as e:
    # 事务回滚
    connection.rollback()
    print(e)
finally:
    # 提交sql
    connection.commit()
    # 关闭连接
    cursor.close()
    connection.close()


执行完毕，查看剩余金额：

mysql> select amount from fund_info where account = '[e-mail]';
+--------+
| amount |
+--------+
|  84.00 |
+--------+
1 row in set (0.00 sec)

在代码中通过raise 模拟异常。演示一下事务的作用

import pymysql

connection = pymysql.connect(host='127.0.0.1',  # MySQL服务器地址
                             port=3306,         # 服务端口号
                             user='root',       # MySQL用户名
                             password='root',   # MySQL密码
                             db='test',         # 要操作的数据库名称
                             charset='utf8mb4') # 连接编码
# 创建cursor, 用于执行sql
cursor = connection.cursor()

# 代表消费的金额
price = 15

# 此时账户zxc的总金额为99
# 账户[e-mail] 总金额 - price
sql_1 = f'update fund_info set amount = amount - {price} where account = '[e-mail]''
# 账户[e-mail] 消费金额 + price
sql_2 = f'update fund_info set consume = consume + {price} where account = '[e-mail]''

try:
    # 查询余额是否足够
    cursor.execute('select amount from fund_info where account = '[e-mail]'')
    result = cursor.fetchone()
    print(result[0])
    # 如果余额不足 抛出异常.
    if not result or result[0] < price:
        raise Exception('余额不足...')
    cursor.execute(sql_1)
    print('========= 其他业务逻辑  执行中....')
    raise Exception('模拟业务逻辑异常......')
    cursor.execute(sql_2)
except Exception as e:
    print('---- 开始事务回滚')
    # 事务回滚
    connection.rollback()
    print(e)
finally:
    # 提交sql
    connection.commit()
    # 关闭连接
    cursor.close()
    connection.close()
    print('执行完毕')


控制条输出

========= 其他业务逻辑  执行中....
---- 开始事务回滚
模拟业务逻辑异常......
执行完毕

查询mysql

mysql> select amount from fund_info where account = '[e-mail]';
+--------+
| amount |
+--------+
|  84.00 |
+--------+
1 row in set (0.00 sec)


即使执行了sql_1（减少总金额的sql语句），金额并没有被改变。因为在修改consume(执行sql_2)之前程序出现了异常，程序就进行了事务回滚，即：connection.rollback()。所以数据被还原到了执行事务之前的样子。

---

以上，便是今天的分享，希望大家喜欢，觉得内容不错的，欢迎点击「在看」支持，谢谢各位。