18--Python 数据库操作

@Author : Roger TX (425144880@qq.com)
@Link : https://github.com/paotong999

一、Python数据库API

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

  • 调用 connect() 方法打开数据库连接，该方法返回数据库连接对象。
  • 通过数据库连接对象打开游标。
  • 使用游标执行 SQL 语句（包括 DDL、DML、select 查询语句等）。如果执行的是查询语句，则处理查询数据。
  • 关闭游标。
  • 关闭数据库连接。

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

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()
  1. 操作数据之后需要提交事务，否则对数据库的修改（包括插入数据、修改数据、删除数据）不会生效
  2. 调用 executemany() 方法执行一条 insert 语句，但调用该方法的第二个参数是一个元组，该元组的每个元素都代表执行该 insert 语句一次，在执行 insert 语句时这些元素负责为该语句中的“?”占位符赋值
  3. executemany() 也可以重复执行 update 语句或 delete 语句，只要其第二个参数是一个序列，序列的每个元素都可对被执行 SQL 语句的参数赋值即可
import sqlite3
conn = sqlite3.connect('first.db')
c = conn.cursor()

# 调用执行select语句查询数据
c.execute('select * from user_tb where _id > ?', (2,))
print('查询返回的记录数:', c.rowcount)
# 通过游标的description属性获取列信息
for col in (c.description):
    print(col[0], end='\t')
print('\n--------------------------------')
while True:
    # 获取一行记录，每行数据都是一个元组
    row = c.fetchone()
    # 如果抓取的row为None，退出循环
    if not row :
        break
    print(row)

c.close()
conn.close()
  1. 不能使用 executemany() 执行查询语句，executemany() 只支持DML语句
  2. 对于查询语句，rowcount返回的值为-1
  3. fetchone() 方法每次获取一条记录，也可以使用 fetchmany(n) 或 fetchall() 方法一次获取多条记录
while True:
    rows = c.fetchmany(3)
    # 如果抓取的rows为None，退出循环
    if not rows :
        break
    # 再次使用循环遍历获取的列表
    for r in rows:
        print(r)

三、Python操作数据库MySQL

安装MySQL驱动
由于MySQL服务器以独立的进程运行，并通过网络对外服务，所以需要支持Python的MySQL驱动来连接MySQL服务器。
MySQL官方提供了mysql-connector-python驱动，但是安装的时候需要给pip命令加上参数--allow-external：

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

如果上面的命令安装失败，可以试试另一个驱动：

$ pip install mysql-connector

我们演示如何连接到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()
  1. 与连接 SQLite 相比，最大的区别在于第 5 行代码，程序要连接 localhost 主机上 3306 端口服务的 python 数据库。
  2. 与连接 SQLite 相比，MySQL 语句中的占位符：%s
  3. MySQL 数据库模块的连接对象有一个 autoconunit 属性，如果将该属性设为 True，则意味着关闭该连接的事务支持，程序每次执行 DML 语句之后都会自动提交，这样程序就无须调用连接对象的 commit() 方法来提交事务了

MySQL callproc方法：调用数据库存储过程
MySQL 数据库模块为游标对象提供了一个非标准的 callproc(self, procname, args=()) 方法，该方法用于调用数据库存储过程。

callproc 方法的 procname 参数代表存储过程的名字，而 args 参数则用于为存储过程传入参数。

存储过程

delimiter //
create procedure add pro(a int, b int, out sum int)
begin
set sum = a + b;
end;
//

MySQL 数据库模块来调用存储过程

# 导入访问MySQL的模块
import mysql.connector
# ①、连接数据库
conn = conn = mysql.connector.connect(user='root', password='32147',
    host='localhost', port='3306',
    database='python', use_unicode=True)
# ②、获取游标
c = conn.cursor()
# ③、调用callproc()方法执行存储过程
# 虽然add_pro存储过程需要3个参数，但最后一个参数是传出参数，
# 因此程序不会用它的值
result_args = c.callproc('add_pro', (5, 6, 0))
# 返回的result_args既包含了传入参数的值，也包含了传出参数的值
print(result_args)
# 如果只想访问传出参数的值，可直接访问result_args的第3个元素，如下代码
print(result_args[2])
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()

运行上面的程序，可以看到如下输出结果：

(5, 6, 11)
11

四、ORM框架SQLAlchemy

首先通过pip安装SQLAlchemy：

pip install sqlalchemy

第一步，导入SQLAlchemy，并初始化DBSession：

# 导入:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # 一对多:
    books = relationship('Book')

class Book(Base):
    __tablename__ = 'book'

    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # “多”的一方的book表是通过外键关联到user表的:
    user_id = Column(String(20), ForeignKey('user.id'))

# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

create_engine()用来初始化数据库连接：

'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'

第二步，向数据库表中添加一行记录：

# 创建session对象:
session = DBSession()
# 创建新User对象:
new_user = User(id='5', name='Bob')
# 添加到session:
session.add(new_user)
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.close()

第三步，从数据库表中查询数据：

# 创建Session:
session = DBSession()
# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
user = session.query(User).filter(User.id=='5').one()
# 打印类型和对象的name属性:
print('type:', type(user))
print('name:', user.name)
# 关闭Session:
session.close()
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 157,298评论 4赞 360
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 66,701评论 1赞 290
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 107,078评论 0赞 237
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,687评论 0赞 202
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,018评论 3赞 286
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,410评论 1赞 211
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,729评论 2赞 310
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,412评论 0赞 194
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,124评论 1赞 239
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,379评论 2赞 242
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,903评论 1赞 257
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,268评论 2赞 251
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 32,894评论 3赞 233
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,014评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,770评论 0赞 192
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,435评论 2赞 269
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,312评论 2赞 260