本文主要介绍了DB-API规范以及Python 操作MySQL数据库 连接 创建 插入 查询 更新 删除
大约需要10min阅读时间
目录
1. DB-API规范
1.1 全局变量
全局变量：DB-API规范规定数据库接口模块必须实现一些全局属性以保证兼容性。Python提供了3个描述数据库模块特性的全局变量。
| 变量名 | 用途 |
|---|---|
| apilevel | 所使用的Python DB-API的版本 |
| threadsafety | 模块的线程安全等级 |
| paramstyle | 在SQL查询中使用的参数风格 |
apilevel ：是API级别，是一个字符串常量，表示这个DB-API模块所兼容的DB-API最高的版本号。例如，若版本号是1.0、2.0，则最高版本是2.0，如果未定义，就默认是1.0。
线程安全等级 threadsafety 是一个整数，取值范围如下：
-
0表示不支持线程安全，多个线程不能共享此模块。
-
1表示初级线程安全支持，线程可以共享模块，但不能共享连接。
-
2表示中级线程安全支持，线程可以共享模块和连接，但不能共享游标。
-
3表示完全线程安全支持，线程可以共享模块、连接及游标。
paramstyle（参数风格）表示执行多次类似查询时，参数如何被拼接到SQL查询中。值format表示标准字符串格式化（使用基本的格式代码），可以在参数中进行拼接的地方插入%s。值pyformt表示扩展的格式代码，用于字典拼接，如%（foo）。除了Python风格之外，还有3种接合方式：qmark的意思是使用问号，numeric表示使用:1或:2格式的字段（数字表示参数的序号），而named表示:foobar这样的字段。其中，foobar为参数名。
1.2 异常
| 异常 | 超类 | 描述 |
|---|---|---|
| StandardError | 所有异常的泛型基类 | |
| Warning | StandardError | 在非致命错误发生时引发 |
| Error | StandardError | 错误异常基类 |
| InterfaceError | Error | 数据库接口错误 |
| DataBaseEroor | Error | 与数据库相关的错误基类 |
| DataError | DatabaseError | 处理数据时出错 |
| OperationalError | DatabaseError | 数据库执行命令时出错 |
| IntegrityError | DatabaseError | 数据完整性错误 |
| InternalError | DatabaseError | 数据库内部出错 |
| ProgrammingError | DatabaseError | SQL 执行失败 |
| NotSupportedError | DatabaseError | 试图执行数据库不支持的特性 |
1.3 连接与游标
连接数据库需要使用具有恰当名称的connect函数。
1.3.1 connect 函数常用参数
| 参数名 | 描述 | 是否可选 |
|---|---|---|
| dsn | 数据源名称, 给出该参数表示数据库依赖 | 否 |
| user | 用户名 | 是 |
| password | 用户密码 | 是 |
| host | 主机名 | 是 |
| database | 数据库名称 | 是 |
1.3.2 连接对象支持的方法
| 方法名 | 描述 |
|---|---|
| close() | 关闭连接后, 连接对象和它的游标均不可用 |
| commit() | 如果支持就提交挂起的事务, 否则不做任何事 |
| rollback() | 回滚挂起的事务(可能不可用) |
| cursor() | 返回链接的游标对象 |
1.3.3 游标对象方法
| 名称 | 描述 |
|---|---|
| callproc(func[, args]) | 使用给定的名称和参数(可选)调用已命名的数据库程序 |
| close() | 关闭游标后，游标不可用 |
| execute(op[, args]) | 执行 SQL 操作, 可能使用参数 |
| executemany(op, args) | 对序列中的每个参数执行 SQL 操作 |
| fetchone() | 把查询结果集中的下一行保存为序列 或 None |
| fetchmany([size]) | 获取查询结果集的多行, 默认尺寸为 arraysize |
| fetchall() | 将所有 (剩余) 行作为结果序列 |
| nextset() | 调至下一个可用的结果集 (可选) |
| setinputsizes(sizes) | 为参数预先定义内存区域 |
| setoutputsize(size[, col]) | 为获取大数据值设定缓冲区尺寸 |
1.3.4 游标对象特性
| 名称 | 描述 |
|---|---|
| arraysize | fetchmany 中返回的行数, 只读 |
| description | 结果列描述的序列, 只读 |
| rowcount | 结果中的行数, 只读 |
1.4 类型
在操作数据库的过程中，为了能够正确与基础SQL数据库进行数据交互操作，DB-API定义了用于特殊类型和值的构造函数及常量，所有模块都要求实现下表所示的构造函数和特殊值。
| 构造函数和特殊值的名称 | 描述 |
|---|---|
| Date(yr, mo, dy) | 日期值对象 |
| Time(hr, min, sec) | 时间值对象 |
| Timestamp(yr, mo, dy, hr, min, sec) | 时间戳对象 |
| DateFromTicks(ticks) | 创建自新纪元以来秒数的对象 |
| TimeFromTicks(ticks) | 创建自新纪元以来秒数的时间值对象 |
| TimestampFromTicks(ticks) | 创建自新纪元以来秒数的时间戳值对象 |
| Binary(string) | 对应二进制长字符串值对象 |
| STRING | 描述字符串列对象, 如 VARCHAR |
| BINARY | 描述二进制长列对象, 如 RAW, BLOB |
| NUMBER | 描述数字列对象 |
| DATETIME | 描述日期时间列对象 |
| ROWID | 描述 row ID 列对象 |
安装了
Python PyMySQL模块
pip install pymysql -i https://pypi.douban.com/simple2. 数据库操作
2.1 数据库连接
import pymysql
def db_connect():
# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="root", db="test")
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute()方法执行SQL查询
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据
data = cursor.fetchone()
# data返回值为一个元组
# print(data)
print(f"Database version : {data[0]} ")
# 关闭数据库连接
db.close()
def main():
db_connect()
if __name__ == "__main__":
main()
2.2 创建数据库表
import pymysql
def create_table():
db = pymysql.connect(host="localhost", user="root", password="root", db="test")
# 使用 cursor() 方法创建一个游标对象cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL，如果表存在就删除
cursor.execute("drop table if exists employee")
# 使用预处理语句创建表
sql = """
create table employee (
first_name varchar(20) not null,
last_name varchar(20),
age int,
sex varchar(1),
income float,
create_time datetime
);
"""
try:
cursor.execute(sql)
print("CREATE TABLE SUCCESS.")
except Exception as ex:
print(f"CREATE TABLE FAILED,CASE:{ex}")
finally:
# 关闭数据库连接
db.close()
def main():
create_table()
if __name__ == "__main__":
main()2.3 数据库插入
import pymysql
import datetime
def insert_record():
db = pymysql.connect(host='localhost', user='root', password='root', db='test')
# 获取游标
cursor = db.cursor()
# SQL 插入语句
sql = "insert into employee (first_name, last_name, age, sex, income, create_time) values " \
"('%s', '%s', %d, '%s', %d, '%s')" % ('张', '三', 20, '男', 30000, datetime.datetime.now())
# 执行 SQL 语句
try:
cursor.execute(sql)
# 提交到数据库执行
db.commit()
print('数据插入成功...')
except Exception as e:
print(f'数据插入失败: {e}')
# 如果发生错误就回滚
db.rollback()
finally:
# 关闭数据库连接
db.close()
if __name__ == '__main__':
insert_record()
2.4 数据库查询
Python查询MySQL使用fetchone()方法获取单条数据，使用fetchall()方法获取多条数据。
fetchone()：该方法获取下一个查询结果集。结果集是一个对象。
fetchall()：接收全部返回结果行。
rowcount：这是一个只读属性，返回执行execute()方法后影响的行数。
import pymysql
def query_data():
# 打开数据库连接
db = pymysql.connect(host='localhost', user='root', password='root', db='test')
# 获取游标
cursor = db.cursor()
# 查询语句
sql = "select * from employee where income > %d" % 10000
try:
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
for row in results:
first_name = row[0]
last_name = row[1]
age = row[2]
sex = row[3]
income = row[4]
create_time = row[5]
# 打印结果
print(f'first_name: {first_name}, last_name: {last_name}, age: {age}, sex: {sex}, income: {income}, '
f'create_time: {create_time}')
except Exception as e:
print(f'查询错误: {e}')
finally:
db.close()
if __name__ == '__main__':
query_data()2.5 数据库更新
import pymysql
def update_table():
db = pymysql.connect(host='localhost', user='root', password='root', db='test')
cursor = db.cursor()
sql = "update employee set age=age+1 where sex='%s'" % '男'
try:
cursor.execute(sql)
db.commit()
print('数据更新成功...')
except Exception as e:
print(f'更新失败: {e}')
db.rollback()
finally:
db.close()
if __name__ == '__main__':
update_table()2.6 数据库删除
import pymysql
def delete_record():
db = pymysql.connect(host='localhost', user='root', password='root', db='test')
cursor = db.cursor()
sql = "delete from employee where age > %d" % 22
try:
cursor.execute(sql)
db.commit()
print('数据删除成功...')
except Exception as e:
print(f'数据删除失败: {e}')
db.rollback()
finally:
db.close()
if __name__ == '__main__':
delete_record()
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
