搞定python之八----操作mysql
本文是《搞定python》系列文章的第八篇,讲述利用python操作mysql数据库。相对来说,本文的综合性比较强,包含了操作数据库、异常处理、元组等内容,需要结合前面的知识点。
1、安装mysql模块
PyMySql模块相当于数据库的驱动,我们在用java时也是要先下载驱动的,同样的道理。
pip3 install PyMySql
2、数据库准备
--创建数据库
create database my_test_db_01
-- 创建表
CREATE TABLE `shoping_00` (
`shoping_id` bigint NOT NULL COMMENT '商品id',
`shoping_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
`shoping_price` int NOT NULL COMMENT '商品价格',
PRIMARY KEY (`shoping_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT;
3、python代码
其实过程和jdbc非常相似,不多说了,看代码。
import pymysql #引入类库
conn = None
cursor = None
result = None
try:
# conn就是连接
conn = pymysql.connect(host="192.168.99.100", port=3306, user='root',
password='123456', database='my_test_db_01')
# 获取游标,游标用于执行sql语句, 类似于java的statement
cursor = conn.cursor()
cursor.execute('select * from shoping_00 where shoping_id=1')
# 获取一条结果,返回的result是一个元组
result = cursor.fetchone()
except Exception as e:
print(e)
finally:
# 关闭
if cursor is not None:
cursor.close()
if conn is not None:
conn.close()
print("type(result): %s \n" % type(result))
print("shoping_id %s | shoping_name %s shoping_price %s" %( result[0],result[1],result[2]))
4、事务处理
下段代码就是加上了事务提交和回滚,基本思路和jdbc思路相同。
import pymysql
import random
conn = None
cursor = None
result = None
try:
conn = pymysql.connect(host="192.168.99.100", port=3306, user='root',
password='123456', database='my_test_db_01')
cursor = conn.cursor()
cursor.execute('select * from shoping_00 where shoping_id=1')
result1 = cursor.fetchone()
# 利用随机数,随机设置一个价格,便于看到效果
cursor.execute('update shoping_00 set shoping_price=%d where shoping_id=1' %(random.randint(1, 99999)))
cursor.execute('select * from shoping_00 where shoping_id=1')
result2 = cursor.fetchone()
# 事务提交
conn.commit()
except Exception as e:
print(e)
# 事务回滚
if conn is not None:
conn.rollback()
finally:
if cursor is not None:
cursor.close()
if conn is not None:
conn.close()
print("-"*4 + "result1:" + "-"*4)
print("type(result): %s " % type(result1))
print("shoping_id %s | shoping_name %s | shoping_price %s" % (result1[0], result1[1], result1[2]))
print("\n" + "-"*4 + "result2:" + "-"*4)
print("shoping_id %s | shoping_name %s | shoping_price %s" % (result2[0], result2[1], result2[2]))
好了,本节就到这里了。
//~~