SQLAlchemy基础教程

Python DB-API

在没有 Python DB-API 之前，各数据库之间的应用接口非常混乱，实现各不相同。如果项目需要更换数据库时，则需要做大量的修改，非常不便。Python DB-API 的出现就是为了解决这样的问题。

Python 所有的数据库接口程序都在一定程度上遵守 Python DB-API 规范。DB-API 是一个规范，它定义了一系列必须的对象和数据库存取方式，以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口。由于 DB-API 为不同的数据库提供了一致的访问接口， 在不同的数据库之间移植代码成为一件轻松的事情。

PEP 249 – Python Database API Specification v2.0

DB-API 操作数据库流程

这里写图片描述

Connection 对象

• close(): Close the connection now.
• commit(): Commit any pending transaction to the database.
• rollback(): This method is optional since not all databases provide transaction support.
• curosr(): Return a new Cursor Object using the connection.

Cursor 对象

• 属性: description, rowcount
• callproc()
• close()
• execute()
• executemany()
• fetchone()
• fetchmany()
• fetchall()
• nextset()
• arraysize
• setinputsizes()
• setoutputsize()

DB-API 操作 PostgreSQL

启动 PostgreSQL

为了演示，这里使用 Docker 启动 PostgreSQL。

首先编写 docker-compose file，其中设置登录密码为 123456，映射主机端口到5432并挂载数据目录到主机。

# docker-compose.yml

version: '3.1'
services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: 123456
    ports:
      - 5432:5432
    volumes:
      - ./data:/var/lib/postgresql/data

使用 docker-compose 启动服务：

$ docker-compose up -d

使用 docker-compose ps 查看服务已正常启动：

        Name               Command        State            Ports
------------------------------------------------------------------------------
postgres_db_1 docker-entrypoint.sh postgres Up 0.0.0.0:5432->5432/tcp

创建数据库 test

首先使用docker ps -l查询容器 id，我这里为 ebe4f81cc5fb。

进入容器:

$ docker exec -it ebe4f81cc5fb /bin/bash

使用 psql 以 postgres 用户登录 PostgreSQL:

$ psql -U postgres

创建数据库 test:

postgres=# create database test;

出现 CREATE DATABASE 说明以及创建成功，可使用\l查看所有数据库，\c test进入数据库 test，\dt查看当前数据库下所有表。显然，现在 test 下什么也没有。

使用 Python 交互 PostgreSQL

首先安装 psycopg2 用于连接:

$ pip install psycopg2

import psycopg2
from faker import Factory

faker = Factory.create()  # faker 用于生成假数据

# 创建 connection
conn = psycopg2.connect(host='10.202.43.36', port=5432,
                        user='postgres', password='123456',
                        database='test')

# 创建 cursor
cursor = conn.cursor()

# create table
cursor.execute('''CREATE TABLE users (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(20) NOT NULL);''')

# insert
cursor.execute('''INSERT INTO users (id, name)
    VALUES (%s, %s);''', [1, faker.name()])

# commit
conn.commit()

# query
cursor.execute('SELECT * FROM user WHERE id=1')
print(cursor.fetchone())  # (1, 'Jacqueline Patton')

# 关闭 cursor
cursor.close()

# 关闭 connection
conn.close()

对于支持事务的数据库，当游标建立之时，就自动开始了一个隐形的数据库事务。执行 INSERT/UPDATE 等操作后使用conn.commit()提交事务。

执行过程发生错误，可以捕获异常并使用conn.rollback回滚。

什么是 ORM

数据库表是一个二维表，包含多行多列。把一个表的内容用 Python 的数据结构表示出来的话，可以用一个 list 表示多行，list 的每一个元素是 tuple，表示一行记录，比如，包含 id 和 name 的 user 表：

[
    (1, 'Jacqueline Patton'),
    (2, 'Jeremiah Fisher'),
    (3, 'Ann Donaldson')
]

Python 的 DB-API 返回的数据结构就是像上面这样表示的。但是用 tuple 表示一行很难看出表的结构。如果把一个 tuple 用 class 实例来表示，就可以更容易地看出表的结构来：

class User(object):
    def __init__(self, id, name):
        self.id = id
        self.name = name

[
    User(1, 'Jacqueline Patton'),
    User(2, 'Jeremiah Fisher'),
    User(3, 'Ann Donaldson')
]

这就是 ORM (Object-Relational Mapping) 技术，把关系数据库的表结构映射到对象上。但是由谁来做这个转换呢？ORM 框架应运而生。

Python 的 ORM 框架

作为一个美妙的语言，Python 有很多 ORM 库：

• SQLAlchemy
• SQLObject
• Storm
• Django ORM
• peewee

各框架的优缺点参考：
1. SQLAlchemy 和其他的 ORM 框架
2.