第一时间收到精彩推送!
Python Every Day, 第 20 天
安装PyMySQL
pip3 install pymysql
操作过程大概分为如下几步
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 ('abc@163.com', 100.00)'
insert_sql1 = 'insert into fund_info (account, amount) values ('zxc@163.com', 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, 'abc@163.com', Decimal('100.00'), Decimal('0.00'), datetime.datetime(2019, 8, 7, 16, 57, 49), datetime.datetime(2019, 8, 7, 16, 57, 49)),
(3, 'zxc@163.com', 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='abc@163.com'')
# fetone 仍然返回元组
result_amount = cursor.fetchone()
print(result_amount) # (Decimal('100.00'),)
# 更新 账号:abc@163.com 的amount值为200.00
cursor.execute('update fund_info set amount=200.00 where account='abc@163.com'')
# 执行增 删 改时 需要commit,不然不会生效
connection.commit()
print('更新成功.')
cursor.execute('delete from fund_info where account='abc@163.com'')
# 执行增 删 改时 需要commit,不然不会生效
connection.commit()
print('删除成功.')
# 操作完毕之后,必须要关闭连接
cursor.close()
connection.close()
事务
mysql> select amount from fund_info where account = 'zxc@163.com';
+--------+
| 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
# 账户zxc@163.com 总金额 - price
sql_1 = f'update fund_info set amount = amount - {price} where account = 'zxc@163.com''
# 账户zxc@163.com 消费金额 + price
sql_2 = f'update fund_info set consume = consume + {price} where account = 'zxc@163.com''
try:
# 查询余额是否足够
cursor.execute('select amount from fund_info where account = 'zxc@163.com'')
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 = 'zxc@163.com';
+--------+
| amount |
+--------+
| 84.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
# 账户zxc@163.com 总金额 - price
sql_1 = f'update fund_info set amount = amount - {price} where account = 'zxc@163.com''
# 账户zxc@163.com 消费金额 + price
sql_2 = f'update fund_info set consume = consume + {price} where account = 'zxc@163.com''
try:
# 查询余额是否足够
cursor.execute('select amount from fund_info where account = 'zxc@163.com'')
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 = 'zxc@163.com';
+--------+
| amount |
+--------+
| 84.00 |
+--------+
1 row in set (0.00 sec)
联系客服