python学习-关系数据库

关系型数据库

数据库服务器（database server），数据库（database）和数据（data）

SQL

SQL是一种声明式语言，是关系型数据库的通用语言。

SQL语句主要有两种类型：

DDL（数据定义语言）：处理用户、数据库以及表单的创建、删除、约束和权限等。

DML（数据操作语言）：处理数据插入、选择、更新和删除。

DB-API

应用程序编程接口（API）是访问某些服务的函数集合。DB-API（http://legacy.python.org/dev/peps/pep-0249/）是Python中访问关系型数据库的标准API。

它的主要函数如下：

.connect() 连接数据库，包含参数用户名、密码、服务器地址，等等。

cursor() 创建一个cursor对象来管理查询

execute()和executemany() 对数据库执行一个或多个SQL命令

fetchone()、fetchmany()、fetchall() 得到execute之后的结果。

SQLite

SQLite(http://www.sqlite.org)是一种轻量级、优秀的开源关系型数据库。它用Python的标准库实现，并存储数据再普通的文件中。这些文件在不同机器和操作系统之间是可移植的。该数据库仅支持原生SQL以及多用户并发操作。浏览器、智能手机和其他应用会把SQLite作为嵌入数据库。

首先使用connect()函数连接本地的SQLite数据库文件，这个文件和目录型数据库是等价的。 字符串’:memory:’仅用于在内存中创建数据库，有助于方便快速地测试。 下一个例子会创建一个数据库enterprise.db和表单zoo用于管理宠物动物园业务。

表单的列如下：

. critter 可变长度的字符串，作为主键。 . count 某动物的总数的整数值。 . damages 人和动物的互动中损失的美元数目。

创建数据库 enterprise.db

>>> import sqlite3
>>> conn = sqlite3.connect('enterprise.db')
>>> curs = conn.cursor()
>

创建表单zoo

>>> curs.execute('''CREATE TABLE zoo
    (critter VARCHAR(20) PRIMARY KEY,
     count INT,
     damages FLOAT)''')
<sqlite3.Cursor object at 0x01EDA720>

插入数据的方法一

>>> curs.execute('INSERT INTO zoo VALUES("duck",5,0.0)')
<sqlite3.Cursor object at 0x01EDA720>
>>> curs.execute('INSERT INTO zoo VALUES("bear",2,1000.0)')
<sqlite3.Cursor object at 0x01EDA720>

更安全的placeholder插入数据方法二

>>> ins = 'INSERT INTO zoo (critter,count,damages) VALUES(?,?,?)'
>>> curs.execute(ins,('weasel',1,2000.0))
<sqlite3.Cursor object at 0x01EDA720>
>>> curs.execute(ins,('weasel',1,2000.0))
<sqlite3.Cursor object at 0x01EDA720>

查询表单zoo

>>> curs.execute('SELECT * FROM zoo')
<sqlite3.Cursor object at 0x01EDA720>
>>> rows = curs.fetchall()
>>> print(rows)
[('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]

按照count排序

>>> curs.execute('SELECT * FROM zoo ORDER BY count')
<sqlite3.Cursor object at 0x01EDA720>
>>> curs.fetchall()
[('weasel', 1, 2000.0), ('bear', 2, 1000.0), ('duck', 5, 0.0)]

哪个动物花费最多？

>>> curs.execute('''SELECT * FROM zoo WHERE damages = (SELECT MAX(damages) FROM zoo)''')
<sqlite3.Cursor object at 0x01EDA720
>>> curs.fetchall()
[('weasel', 1, 2000.0)]

已经打开的一个连接(connection)或者游标(cursor)，不需要时应该关闭：

>>> curs.close()
>>> conn.close()

MySQL

MySQL(http://www.mysql.com)是一款开源关系型数据库，不同于SQLite，它是真正的数据库服务器。MysqlDB(http://sourceforge.net/projects/mysql-python)是最常用的MySQL驱动程序，但至今没有支持Python3，Python连接MySQL的几个驱动程序如下：

名称 链接 Pypi包 导入 注意

MySQL http://dev.mysql.com/doc/connector-

mysql-connector- mysql.connector

Connector python/en/index.html python

PYMySQL https://github.com/petehunt/PyMySQL/

pymysql pymysql

oursql http://pythonhosted.org/oursql/

oursql oursql 需要SQL客户端的C依赖库

PostgreSQL

PostgreSQL（http://www.postgresql.org/）是一款功能全面的开源关系型数据库。下表列出了Python链接PostgreSQL的几个驱动程序：

名称 链接 Pypi包 导入 注意

psycopg2 http://initd.org/psycopg/

psycopg2 psycopg2 需要来自PostgreSQL客户端工具的pg_config

py-postgresql http://python.projects.pgfoundry.org

py-postgresql postgresql

SQLAlchemy

每一种数据库实现的是包含自己特征的SQL，用于消除各数据库SQL差异的Python库是SQLAlchemy(http://www.sqlalchemy.org)，不是Python标准库，使用下面的命令安装它：

$ pip install sqlalchemy

初始化连接字符串如下：

dialect + diver :// user : password @ host : port / dbname

含义如下：

dialect : 数据库类型

dirver : 使用该数据库的特定驱动程序

user\password: 数据库认证字符串

host\port: 数据库服务器的位置

dbname: 初始连接到服务器中的数据库

如下列出了常见方言和对应的驱动程序

方言 驱动程序

sqlite pysqlite（可忽略）

mysql mysqlconnector

mysql pymysql

mysql oursql

postgresql psycopg2

posgresql pypostgresql

在如下层级使用SQLAlchemy:

. 底层负责处理数据库连接池、执行SQL命令以及返回结果，和DB-API相似；

. 再往上是SQL表达式语言，更像Python的SQL生成器；

. 较高级的是对象关系模型(ORM)，使用SQL表达式语言，将应用程序代码和关系型数据结构结合起来。

1、引擎层

以内置SQLite为例，连接字符串忽略host、port、user和password。dbname表示存储SQLite数据库的文件，如果省去dbname，SQLite会在内存创建数据库。如果dbname以反斜线（/）开头，那么它是文件所在的绝对路径（Linux和OS X是反斜线，而在Windows是例如C:\的路径名），否则它是当前目录下的相对路径。

示例如下：

导入库函数

>>> import sqlalchemy as sa
>

连接到数据库，并在内存中存储它（参数字符串‘sqlite://:memory:’也是可行的）：

>>> conn = sa.create_engine('sqlite://')

创建包含三列的数据库表单zoo：运行函数conn.execute()返回一个SQLAlchemy的对象ResultProxy.

>>> conn.execute('''CREATE TABLE zoo
     (critter VARCHAR(20) PRIMARY KEY,
      count INT,
      damages FLOAT)''')
<sqlalchemy.engine.result.ResultProxy object at 0x026F3510>

向空表中插入三组数据：

>>> ins = 'INSERT INTO zoo(critter,count,damages) VALUES (?,?,?)'
>>> conn.execute(ins,'duck',10,0.0)
<sqlalchemy.engine.result.ResultProxy object at 0x026F3A10>
>>> conn.execute(ins,'bear',2,1000.0)
<sqlalchemy.engine.result.ResultProxy object at 0x026F3B30>
>>> conn.execute(ins,'weasel',1,2000.0)
<sqlalchemy.engine.result.ResultProxy object at 0x026F3990>
>>>
>

在数据库中查询放入的所有数据

>>> rows = conn.execute('SELECT * FROM zoo')
>>> print(rows)
<sqlalchemy.engine.result.ResultProxy object at 0x026F3B70>

在SQLAlchemy中，rows不是一个列表，不能直接输出，但它可以像列表一样迭代，每次可以得到其中的一行

>>> for row in rows :
    print(row)

('duck', 10, 0.0)
('bear', 2, 1000.0)
('weasel', 1, 2000.0)
>>>
>

要了解SQLAlchemy的连接池，可参阅文档（http://docs.sqlalchemy.org/en/latest/coe/pooling.html）

2、SQL表达式语言

相比引擎层，它能处理更多SQL方言的差异，对于关系型数据库应用是一种方便的中间层解决方案。 如下例，创建和管理数据表zoo：

导入和连接库

>>> import sqlalchemy as sa
>>> conn = sa.create_engine('sqlite://')
>

定义表单zoo，用表达式语言代替SQL：

>>> zoo = sa.Table('zoo',meta,
           sa.Column('critter',sa.String,primary_key=True),
           sa.Column('count',sa.Integer),
           sa.Column('damages',sa.Float)
           )
>>> meta.create_all(conn)
>

Table()方法的调用结构和表单的结构一致，此表单包含三列，在Table()方法调用时括号内部也调用三次Column()。zoo是连接SQL数据库和Python数据结构的一个对象

使用表达式语言函数插入数据：

>>> conn.execute(zoo.insert(('bear',2,1000.0)))
<sqlalchemy.engine.result.ResultProxy object at 0x027262F0>
>>> conn.execute(zoo.insert(('weasel',1,2000.0)))
<sqlalchemy.engine.result.ResultProxy object at 0x02726390>
>>> conn.execute(zoo.insert(('duck',10,0)))
<sqlalchemy.engine.result.ResultProxy object at 0x02726210>
>>>

下面创建SELECT语句(zoo.select()会选择出zoo对象表单的所有项，和SELECT * FROM zoo在普通SQL做的相同)：

>>> result = conn.execute(zoo.select())

得到结果

>>> result = conn.execute(zoo.select())
>>> rows = result.fetchall()
>>> print(rows)
[('bear', 2, 1000.0), ('weasel', 1, 2000.0), ('duck', 10, 0.0)]
>>>

3、对象关系映射

在SQLAlchemy的顶层，对象关系映射（ORM）使用SQL表达式语言，但尽量隐藏实际数据库的机制。自己定义类，ORM负责处理如何读写数据库的数据。最基本观点是：同样使用一个关系型数据库，但操作数据的方式仍然和Python保持接近。

示例：我们定义一个类zoo，把他挂接到ORM，使用zoo.db文件以便于验证ORM是否有效。

初始化导入：

>>> import sqlalchemy as sa
>>> from sqlalchemy.ext.declarative import declarative_base
>

连接数据库：

>>> conn = sa.create_engine('sqlite:///zoo.db')
>

进入SQLAlchemy的ORM，定义类zoo，并关联它的属性和表单的列：

>>> Base = declarative_base()
>>> class Zoo(Base):
    __tablename__='zoo'
    critter = sa.Column('critter',sa.String,primary_key=True)
    count = sa.Column('count',sa.Integer)
    damages = sa.Column('damages',sa.Float)
    def __init__(self,critter,count,damages):
        self.critter = critter
        self.count = count
        self.damages = damages
    def __repr__(self):
        return "<Zoo({},{},{})>".format(self.critter,self.count,self.damages)

创建数据库和表单：

>>> Base.metadata.create_all(conn)
>

通过创建Python对象插入数据，ORM内部会管理这些：

>>> first = Zoo('duck',10,0.0)
>>> second = Zoo('bear',2,1000.0)
>>> third = Zoo('weasel',1,2000.0)
>>> first
<Zoo(duck,10,0.0)>

利用ORM接触SQL，创建连接到数据库的会话（session）:

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=conn)
>>> session = Session()
>

借助会话，把创建的三个对象写入数据库。add()函数增加一个对象，add_all()增加一个列表：

>>> session.add(first)
>>> session.add_all([second,third])

最后使整个过程完整

>>> session.commit()

在当前目录下，创建了文件zoo.db，可以使用命令行的SQLite3程序验证一下：

$ sqlite3 zoo.db

问题：在执行时报不是内部命令的错误，暂未解决。

SQLAlchemy的作者撰写了完整的教程(http://docs.sqlalchemy.org/en/rel08/orm/tutorial.html)，阅读后决定哪一级最适合自己的需求：

. 普通DB-API

. SQLAlchemy 引擎层

. SQLAlchemy 表达式语言

. SQLAlchemy ORM