点击上方“Python3X”,选择“置顶或者星标”
第一时间收到精彩推送!
Python Every Day, 第 20 天
在工作中,不管Web开发或者爬虫,数据分析等凡是与数据有接触的都离不开数据库。数据库又分为关系型数据库还是非关系型数据库,关系型数据库中最常用应该就是MySQL了。
安装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=latin1DATABASE `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, 用于执行sqlcursor = 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 查看执行的全部结果,返回一个tupleresult_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.00cursor.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连接
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)'zxc@163.com';
+--------+
| amount |
+--------+
| 99.00 |
+--------+
1 row in set (0.00 sec)
import pymysqlconnection = pymysql.connect(host='127.0.0.1', # MySQL服务器地址 port=3306, # 服务端口号 user='root', # MySQL用户名 password='root', # MySQL密码 db='test', # 要操作的数据库名称 charset='utf8mb4') # 连接编码# 创建cursor, 用于执行sqlcursor = connection.cursor()# 代表消费的金额price = 15# 此时账户zxc的总金额为99# 账户zxc@163.com 总金额 - pricesql_1 = f'update fund_info set amount = amount - {price} where account = "zxc@163.com"'# 账户zxc@163.com 消费金额 + pricesql_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()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)'zxc@163.com';
+--------+
| amount |
+--------+
| 84.00 |
+--------+
1 row in set (0.00 sec)
import pymysqlconnection = pymysql.connect(host='127.0.0.1', # MySQL服务器地址 port=3306, # 服务端口号 user='root', # MySQL用户名 password='root', # MySQL密码 db='test', # 要操作的数据库名称 charset='utf8mb4') # 连接编码# 创建cursor, 用于执行sqlcursor = connection.cursor()# 代表消费的金额price = 15# 此时账户zxc的总金额为99# 账户zxc@163.com 总金额 - pricesql_1 = f'update fund_info set amount = amount - {price} where account = "zxc@163.com"'# 账户zxc@163.com 消费金额 + pricesql_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('执行完毕')
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)'zxc@163.com';
+--------+
| amount |
+--------+
| 84.00 |
+--------+
1 row in set (0.00 sec)
以上,便是今天的分享,希望大家喜欢,觉得内容不错的,欢迎点击「在看」支持,谢谢各位。
往期Python Every Day
[每日一题]19、简单理解Python中的__name__ == '__main__'
[每日一题]3、Python实现删除list中重复元素的几种方式
[每日一题]2、Python中的类的定义和装饰器@classmethod与@staticmethod
![640?](https://i-blog.csdnimg.cn/blog_migrate/75b7b0e338a5418cb8eb765cf0f9df6b.png)
感谢您的阅读