python mysql数据库基础（Python操作MySQL数据库）
python mysql数据库基础（Python操作MySQL数据库）数据库操作错误和异常描述DB API中定义了一些数据库操作的错误及异常，下表列出了这些错误和异常：查询employee表中income(工资)大于20000的所有数据# 1.查询employee表中工资大于20000的员工信息 import pymysql # 设置数据库配置项 HOSTNAME = 'node05' USERNAME = 'root' PASSWORD = 'Love88me' DATABASE = 'cayman' # 打开数据库连接 db = pymysql.connect(HOSTNAME USERNAME PASSWORD DATABASE) # 使用cursor对象创建一个流标对象 cursor = db.cursor() # 查询语句 sql = "select * from
1.什么是pymysql?PyMySQL是在Python3.x版本中用于连接MySQL服务器的一个库，Python2中使用mysqldb。PyMySql遵循Python数据库API v2.0的规范，并包含了pure-Python MySQL客户端库。
2.安装PyMySQL $ pip install pymysql3.MySQL数据库安装与配置
在用PyMySQL连接MySQL数据库之前，请确保MySQL数据库安装配置完成，具体如何安装与配置Mysql数据库，请参考另一篇文章。
二十四、CentOS7安装配置MySQL
4. 数据库连接与操作4.1.连接数据库操作import pymysql
# 数据库服务器名
HOSTNAME = 'node05'
# 数据库用户名
USER = 'root'
# 数据库名
DATABASE = 'cayman'
# 数据库密码
PASSWORD = 'Love88me'
# 打开数据库连接
conn = pymysql.connect(HOSTNAME USER PASSWORD DATABASE)
# 使用cursor()方法创建一个游标对象
cursor = conn.cursor()
# 使用execute()方法执行SQL查询语句
cursor.execute("select VERSION()")
# 使用fetchone()查询单条数据
data = cursor.fetchone()
print(f"Database Version: {data}")
# 关闭数据库连接
conn.close()4.2.创建表操作
import pymySQL
# 设置数据库配置项
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'
# 打开数据库连接
db = pymysql.connect(HOSTNAME USERNAME PASSWORD DATABASE)
# 使用cursor对象创建一个流标对象
cursor = db.cursor()
# 使用execute()方法执行SQL 如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS employee")
# 使用预处理语句创建表
sql = """ CREATE TABLE employee(
id bigint primary key auto_increment
user_name varchar(50) not null
age int
sex char(1)
income float
)
"""
# 执行sql语句
cursor.execute(sql)
# 关闭数据库连接
db.close()4.3.数据库插入操作4.3.1.插入单条语句
import pymysql
# 设置数据库配置项
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'
# 打开数据库连接
db = pymysql.connect(HOSTNAME USERNAME PASSWORD DATABASE)
# 使用cursor对象创建一个流标对象
cursor = db.cursor()
# SQL语句
sql = """
insert into employee(user_name age sex income) values ('风清扬' 64 '男' 22000);
"""
try:
# 执行sql语句
cursor.execute(sql)
# 提交
db.commit()
except:
# 如果发生错误就回滚
db.rollback()
# 关闭数据库连接
db.close()4.3.2.插入多条语句
import pymysql
# 设置数据库配置项
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'
# 打开数据库连接
db = pymysql.connect(HOSTNAME USERNAME PASSWORD DATABASE)
# 使用cursor对象创建一个流标对象
cursor = db.cursor()
# SQL语句
sql = " insert into employee(user_name age sex income) values (%s %s %s %s)"
data = (
('风清扬' 64 '男' 22000)
('令狐冲' 22 '男' 14000)
('任盈盈' 20 '男' 10000)
('东方不败' 32 '男' 18000)
('任我行' 56 '男' 17000)
('段誉' 33 '男' 19000)
('王语嫣' 26 '女' 9000)
('木婉清' 23 '女' 6000)
('乔峰' 38 '男' 23000)
('阿朱' 24 '女' 5000)
('阿紫' 22 '女' 5500)
('虚竹' 35 '男' 11000)
('梦姑' 25 '女' 6500)
('梅超风' 41 '女' 15000)
('陈玄风' 44 '男' 12000)
('杨过' 28 '男' 24000)
('小龙女' 38 '女' 15000)
('鸠摩智' 44 '男' 16000)
)
try:
# 执行sql语句
cursor.executemany(sql data)
# 提交
db.commit()
except:
# 如果发生错误就回滚
db.rollback()
# 关闭数据库连接
db.close()4.4.数据库查询
Python查询MySQL使用fetchone()获取单条数据，使用fetchall()方法获取多条数据。
查询employee表中income(工资)大于20000的所有数据
# 1.查询employee表中工资大于20000的员工信息
import pymysql
# 设置数据库配置项
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'
# 打开数据库连接
db = pymysql.connect(HOSTNAME USERNAME PASSWORD DATABASE)
# 使用cursor对象创建一个流标对象
cursor = db.cursor()
# 查询语句
sql = "select * from employee where income >'%d' "%(20000)
try:
# 执行sql语句
cursor.execute(sql)
# 获取所有满足条件的列表
ret = cursor.fetchall()
# 遍历打印结果
for row in ret:
user_name = row[1]
age = row[2]
sex = row[3]
income = row[4]
print(f"员工: {user_name}，年龄: {age} 性别: {sex} 工资: {income}")
except:
print("无满足条件的数据或查询出错!!")
# 关闭数据库连接
db.close()4.5.数据库更新操作
import pymysql
# 设置数据库配置项
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'
# 打开数据库连接
db = pymysql.connect(HOSTNAME USERNAME PASSWORD DATABASE)
# 使用cursor对象创建一个流标对象
cursor = db.cursor()
# 更新语句
sql = "update employee set income=income income*0.1 where sex='%c'"%('女')
try:
# 执行SQL语句
cursor.execute(sql)
# 提交
db.commit()
except:
# 发生错误时回滚
db.rollback()
# 关闭数据库
db.close()4.6.删除操作
import pymysql
# 设置数据库连接信息
HOSTNAME = 'node05'
USERNAME = 'root'
PASSWORD = 'Love88me'
DATABASE = 'cayman'
# 打开数据库连接
db = pymysql.connect(HOSTNAME USERNAME PASSWORD DATABASE)
# 使用cursor()方法获取游标
cursor = db.cursor()
# 构建删除数据SQL语句
sql = "delete from employee where user_name = '%s'"%('鸠摩智')
try:
# 执行sql语句
cursor.execute(sql)
# 提交
db.commit()
except:
# 发生异常时回滚
db.rollback()
# 关闭数据库连接
db.close()4.7 执行事务操作
在数据库操作中，事务机制可以保证数据的一致性。最基本的事务应当具备4个属性： 原子性、一致性、隔离性、和持久性。这四个属性被称作ACID特性。
Python DB API 2.0的事务提供了两个方法commit和rollback。对于支持事务的数据库编程中，当流标建立时，就自动开启了一个隐形的数据库事务。
4.8 错误处理DB API中定义了一些数据库操作的错误及异常，下表列出了这些错误和异常：
数据库操作错误和异常描述
数据库设计工具众所周知，良好的数据库设计能够大幅减少后期的运维工作，同时也能最大程度地减少软件项目出错的可能。由于我们所面临的真实项目需求往往五花八门，因此需要找到合适的设计工具，来实现事半功倍的效果。
本文将从如下四个方面和您一起比较四种优秀数据库设计工具的各自优缺点。用户界面
（1）可支持的数据库
（2）数据工具
（3）售价
1.DbSchema
官网：https://dbschema.com/
DbSchema是一种可用于复杂数据库设计和管理的可视化工具。该工具已经集成在大多数主流操作系统之中。
用户界面
DbSchema具有友好的用户界面，可简化数据库的设计。它能够为管理大型数据库提供友好的界面布局，以便用户更专注于数据库的特定功能。
如下图所示，DbSchema界面能够让用户自由地浏览各种视图，在布局中拖放表格，或通过双击的方式来编辑表格中的数据。
逆向工程
DbSchema可以轻松地对现有数据库的架构模式(schema)进行反向工程。
项目文件和架构模式同步
DbSchema将本地的模式设计保存到一个项目文件之中。使用此类项目文件，用户在无需连接数据库的情况下，直接修改目标架构模式的设计。这些更改将会被保存在对应的项目文件中。当再次与数据库连接时，用户可以通过比较该项目文件和目标数据库之间的差异，实现相互之间的同步。
共享
用户可以使用GIT或其他版本控制类系统，轻松地在团队内部共享DbSchema的各种项目文件。而且，单个架构模式可以在多台服务器之间自由迁移。由于此类项目文件是XML格式的，因此用户可以使用任何一种文本编辑器，去轻松地读取或编辑。
可视化数据工具
DbSchema集成了三种实用的数据工具：
（1）关系型数据浏览(Relational Data Browse)使用户可以同时浏览多个表中的数据。
（2）可视化查询生成器(Visual Query Builder)以可视化的方式创建复杂的SQL查询。
（3）SQL编辑器(SQL Editor)是使用文本自动完成(autocompletion)功能的强大编辑器。
支持的数据库
DbSchema能够与大多数数据库相集成，并可以通过JDBC驱动程序连接到其他数据库上。另外，DbSchema还能够通过反向工程来设计Mongo DB的架构模式。
售价
DbSchema提供15天的免费试用期。15天之后，您可以选择如下三种定价方案中的一个：
（1）学生 — 63美元 – 终身许可证。
（2）个人 - 127美元 - 终身许可证，一年内免费更新。一年后，用户每年需要为更新支付31美元。
（3）商业 — 197美元 - 终身许可证，一年内免费更新。一年后，用户每年需要为更新支付48美元。
如果您一次性购买了3个或更多DbSchema许可证，则可享受15%的批量折扣。
总结
DbSchema是一个多合一的数据库设计器与管理器。它被认为是管理复杂数据库的一款非常实用的工具。DbSchema的主要缺点是其表单和报表引擎比较难用。
2.Vertabelo
官网：
https://www.vertabelo.com/
作为一款在线的数据库设计工具，团队成员既可以通过Vertabelo轻松地在项目上开展协作，也可以通过电子邮件与团队成员共享访问的各种权限。目前，Vertabelo提供三种不同的访问级别：所有者、编辑者和查看者。
用户界面
Vertabelo集成了简洁明了的界面。用户可以在模型之间进行流畅的切换，而不必离开其设计区域。
如下图所示，该工具集成了快速搜索和快速导航菜单，它们对于大型数据库的使用是非常实用的。
逆向工程
Vertabelo允许用户对数据库中的架构模式进行反向工程。由于没有针对反向工程的图形界面，用户需要通过访问其官网，以获取命令行。另外，其反向工程的另一个缺点是：用户必须在上传之后手动安排各种表格。
模型
Vertabelo使用模型来保存设计。这些模型可以作为具有不同访问级别的文档，在团队之间共享。此外，开发者还可以：
（1）使用公共链接，在Vertabelo之外共享模型。
（2）通过克隆，以跟踪过往设计的旧版本。
（3）类似DbSchema项目文件那样被使用。
支持的数据库
Vertabelo仅支持八种主流的数据库，它们分别是：Oracle、MySQL、PostgreSQL、SQLite、IBM DB2、SQL Server、HyperSQL和Amazon Redshift。
售价
Vertabelo提供7天的免费试用期，7天之后，您可以选择如下三种定价方案中的一个：
基本方案 - 9美元/月，最多可有5个数据库模型，每个模型最多有25张表。
高级方案 - 29美元/月，最多可有20个数据库模型，每个模型最多有100个表。
团队方案 - 从79美元/月到799美元/月不等，具体取决于团队的规模。该方案提供了无限个数据库模型，以及每个模型里无限数量的表。
总结
作为优秀的数据库设计工具，Vertabelo具有友好且时尚的界面。它不但易于被访问和使用，并且易于在团队中实现工作的共享。不过，它也有着如下缺点：
（1）支持的数据库较少。
（2）逆向工程工具不太“友好”。
（3）缺少稳定的SQL编辑器。
3.Toad Data Modeler
官网：
https://www.toadworld.com/products/toad-data-modeler
作为一款数据库建模工具，Toad Data Modeler使用到了自动化且直观的工作流程，并且内置了专业的知识库。随着时间的推移，Toad建立了一个大型开发者社区，并成为了广受欢迎的数据库方案。
用户界面
Toad集成了一套用户界面，它可以帮助用户更好地了解现有的数据库和对应的表之间的关系，进而创建各种逻辑和物理的数据模型。
如下图所示，Toad可以自动地在物理模型中映射数据库的所有属性。
逆向工程
Toad可以通过连接到数据库，来对架构进行逆向工程。
模型与同步
Toad使用户能够将逻辑模型转换为选定的物理模型(例如Oracle和SQL Server等)。您也可以将既定的物理模型转换为另一种物理模型，例如：将Oracle模型转换为SQL Server。
在同步方面，用户可以使用该工具将模型与数据库进行比较，进而生成用于迁移的变更脚本。此外，Toad还可以让用户从数据库中直接更新模型。
自动化
Toad可以自动化诸如数据和架构模式之间的比较流程。
SQL代码的生成
Toad可以生成各种复杂的数据库DDL脚本。此外，它还可以在关联的应用程序中打开已生成的SQL脚本。
报告
Toad可以生成HTML、RTF、以及PDF格式的报告。
支持的数据库
Toad工具可以连接到多个数据库上，其中包括：Oracle、SAP、MySQL、SQL Server、以及PostgreSQL等。
售价
Toad提供30天的免费试用期。30天之后，用户可以637.45美元的价格购买Toad Data Modeler许可证，其中包含1年的技术支持。
总结
总的说来，Toad是一个具有许多出色功能的数据库建模平台。当然，它也具有如下的缺点：
无法提供非常直观的用户界面。
许可证比较昂贵。
4.DbVisualizer
官网：https://www.dbvis.com/
同样作为数据库管理与设计工具的DbVisualizer，集成了许多数据库，并且能够工作在各种主流操作系统上。
用户界面
DbVisualizer集成了一个易用的界面。用户可以用它来浏览复杂的架构和编辑数据。
如下图所示，该界面允许用户执行各种操作，其中包括：拖放、固定标签、在单独的窗口中分离标签、以及内联编辑(inline edit)等。
服务器管理
DbVisualizer使用户可以管理数据库服务器的实例、整体安全性、以及会话存储。此外，用户还可以计划和执行各种针对Oracle、MySQL、PostgreSQL和SQL Server的操作。
比较
DbVisualizer允许用户比较诸如表格数据和脚本之类的对象，以及两个表与过程的DDL。而且，其比较界面非常简洁明了。
SQL工具
DbVisualizer提供如下实用的SQL工具：
SQL Commander — 一种支持多个编辑器和结果集的查询工具。
Query Builder - 使用户可以直观地创建和执行查询。
Explain Plan - 一种用于分析如何在数据库中使用某种查询的工具。不过此功能仅适用于某些数据库产品。
DbVisualizer工具集成了一个SQL历史记录工具，用户可以在其中查看以往的查询信息。
支持的数据库
DbVisualizer能够与许多主流数据库集成到一起，并且可以使用JDBC驱动程序去连接其他数据库。
售价
虽然DbVisualizer提供了免费版的工具，但是该版本缺少一些重要的功能。其完整版工具的售价为：每位用户197美元/许可证，而每位用户的续证费用为69美元。当然DbVisualizer也会视用户团队的规模给予许可证一定的折扣。例如：对于拥有50名以上的团队而言，最便宜的价格为：每位用户141美元，以及50美元的续签费用。当然，该工具也提供了一种高级支持类型的许可证。
总结
作为出色的数据库管理工具，DbVisualizer具有友好的用户界面，并能够在所有流行的操作系统上运行。当然，DbVisualizer的缺点主要集中在：其免费版本没有足够的功能，而付费版本又过于昂贵。
