一篇文章解决Python操作数据库问题[其它数据库]

Z时代
2024-01-10
分类：综合

python

一、Python数据库API

Python 需要为操作不同的数据库使用不同的模块，但基本都遵守 Python 制订的 DB API 协议，目前该协议的最新版本是 2.0。

使用 Python DB API 2.0 操作数据库的基本流程如下

调用 connect() 方法打开数据库连接，该方法返回数据库连接对象。

通过数据库连接对象打开游标。

使用游标执行 SQL 语句（包括 DDL、DML、select 查询语句等）。如果执行的是查询语句，则处理查询数据。

关闭游标。

关闭数据库连接。

使用 Python DB API 2.0 操作数据库的基本流程如图

数据库 API 的核心类

遵守 DB API 2.0 协议的数据库模块通常会提供一个 connect() 函数，该函数用于连接数据库，并返回数据库连接对象。数据库连接对象通常会具有如下方法和属性：

cursor(factory=Cursor)：打开游标。

commit()：提交事务。

rollback()：回滚事务。

close()：关闭数据库连接。

isolation_level：返回或设置数据库连接中事务的隔离级别。

in_transaction：判断当前是否处于事务中。

游标对象通常会具有如下方法和属性：

execute(sql[, parameters])：执行 SQL 语句。parameters 参数用于为 SQL 语句中的参数指定值。

executemany(sql, seq_of_parameters)：重复执行 SQL 语句。可以通过 seq_of_parameters 序列为 SQL 语句中的参数指定值，该序列有多少个元素，SQL 语句被执行多少次。

fetchone()：获取查询结果集的下一行。如果没有下一行，则返回 None。

fetchmany(size=cursor.arraysize)：返回查询结果集的下 N 行组成的列表。如果没有更多的数据行，则返回空列表。

fetchall()：返回查询结果集的全部行组成的列表。

close()：关闭游标。

rowcount：该只读属性返回受 SQL 语句影响的行数。对于 executemany() 方法，该方法所修改的记录条数也可通过该属性获取。

lastrowid：该只读属性可获取最后修改行的 rowid。

arraysize：用于设置或获取 fetchmany() 默认获取的记录条数，该属性默认为 1。有些数据库模块没有该属性。

description：该只读属性可获取最后一次查询返回的所有列的列名信息。

connection：该只读属性返回创建游标的数据库连接对象。有些数据库模块没有该属性。

二、Python操作数据库SQLite

SQLite是一种嵌入式数据库，它的数据库就是一个文件。

Python内置了SQLite3，在Python中使用SQLite，不需要安装任何东西，直接使用。

# 导入访问SQLite的模块
import sqlite3
# ①、打开或创建数据库
# 也可以使用特殊名：:memory:代表创建内存中的数据库
conn = sqlite3.connect('first.db')
# ②、获取游标
c = conn.cursor()
# ③、执行DDL语句创建数据表
c.execute('''create table user_tb(
    _id integer primary key autoincrement,
    name text,
    pass text,
    gender text)''')
# 执行DDL语句创建数据表
c.execute('''create table order_tb(
    _id integer primary key autoincrement,
    item_name text,
    item_price real,
    item_number real,
    user_id inteter,
    foreign key(user_id) references user_tb(_id) )''')
# 执行DML的insert语句插入数据
c.execute('insert into user_tb values(null, ?, ?, ?)',
    ('孙悟空', '123456', 'male'))
c.execute('insert into order_tb values(null, ?, ?, ?, ?)',
    ('鼠标', '34.2', '3', 1))
# SQLite 数据库 API 默认是开启了事务，需要提交事务
conn.commit()
# 调用executemany()方法把同一条SQL语句执行多次
c.executemany('insert into user_tb values(null, ?, ?, ?)',
    (('sun', '123456', 'male'),
    ('bai', '123456', 'female'),
    ('zhu', '123456', 'male'),
    ('niu', '123456', 'male'),
    ('tang', '123456', 'male')))
# 通过rowcount获取被修改的记录条数
print('修改的记录条数：', c.rowcount)
conn.commit()
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()

三、Python操作数据库MySQL

安装MySQL驱动

由于MySQL服务器以独立的进程运行，并通过网络对外服务，所以需要支持Python的MySQL驱动来连接MySQL服务器。

MySQL官方提供了mysql-connector-python驱动，但是安装的时候需要给pip命令加上参数--allow-external：

$ pip install mysql-connector-python --allow-external mysql-connector-python

我们演示如何连接到MySQL服务器的test数据库：

# 导入访问MySQL的模块
import mysql.connector
# ①、连接数据库
conn = conn = mysql.connector.connect(user='root', password='123456',
    host='localhost', port='3306',
    database='python', use_unicode=True)
# ②、获取游标
c = conn.cursor()
# ③、执行DDL语句创建数据表
c.execute('''create table user_tb(
    user_id int primary key auto_increment,
    name varchar(255),
    pass varchar(255),
    gender varchar(255))''')
# 执行DDL语句创建数据表
c.execute('''create table order_tb(
    order_id integer primary key auto_increment,
    item_name varchar(255),
    item_price double,
    item_number double,
    user_id int,
    foreign key(user_id) references user_tb(user_id) )''')
# 执行DML的insert语句插入数据
c.execute('insert into user_tb values(null, %s, %s, %s)',
    ('孙悟空', '123456', 'male'))
c.execute('insert into order_tb values(null, %s, %s, %s, %s)',
    ('鼠标', '34.2', '3', 1))
# 调用executemany()方法把同一条SQL语句执行多次
c.executemany('insert into user_tb values(null, %s, %s, %s)',
    (('sun', '123456', 'male'),
    ('bai', '123456', 'female'),
    ('zhu', '123456', 'male'),
    ('niu', '123456', 'male'),
    ('tang', '123456', 'male')))
# 关闭事务支持conn.autocommit = True
conn.commit()
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()