Python学习笔记(十一)关系项目数据库

涉项目数据库的一些意义:

  • 多用户同时做客数;
  • 用户采取数据的掩护;
  • 敏捷地蕴藏和查找数据;
  • 数量为模式定义以及为封锁限制;
  • Joins 通过连接发现不同数量中的关联;
  • 声明式(非命令式)查询语言,SQL(Structured Query Language)

之所以让誉为关系型(relational)是以数据库展现了表单(table)形式的异门类数据
之间的关联。
表单是一个所有实践与排的老二首先组,和电子数码表类似。要开创一个表单,需要被她定名,
明确次序、每一样起的号及各国一样排列的类别。每一行还见面存在一样之排,即使允许缺失项
(也称 null)。
有平实践抑某几乎履通常作为表单的主键,在表单中主键的价是无可比拟的,防止重复增添
数据项。这些键在询问时吃很快索引,类似于书本的目,方便快捷地找到指定行。
列一个表单都附属于某个数据库,类似于一个文件都留存吃某某目录下。两层的层次结构便于
更好地组织及保管。
数据库一歌词起多就此法 ,用于代替服务器、表单容器与存储的数量。如
果你还要代表它们,可以称其为数据库服务器(database server)、数据库
(database)和数量(data)。
只要我们怀念使透过不主键的排列的值查找数据,可以定义一个二级索引,否则数据库服务器需要扫描整个表单,暴力搜索每一行找到匹配列的值。
表单中可透过外键建立关系,列的值受这些键的格。


SQL

SQL 既不是一个 API
也非是均等种植协议,而是同种声明式语言,只需要报告其做呀即可。
它是涉项目数据库的通用语言。SQL查询是客户端发送给数据库服务器的文本字符串,指明要操作的具体操作。

SQL
语言在多正经定义格式,但是富有的数据库制造商还见面多其自己的扩张,导
致产生很多 SQL 方言。如果你管多少存储在论及项目数据库中,SQL
会带来一定的但是移栽
性,但是方言与操作差异还会导致难以用数据移植到其它一样种植档次的数据库中。

SQL语句有有限栽要品种:

  • DDL(数据定义语言)
    拍卖用户,数据库及表单的开创,删除,约束和权杖等。
  • DML(数据操作语言)
    拍卖多少插入,选择,更新

基本的SQL DDL命令

操作 SQL模式 SQL示例
创办数据库 CREATE DATABASE dbname CREATE DATABASE d
择时数据库 USE dbname USE d
去数据库和表单 DROP DATABASE dbname DROP DATABASE d
缔造表单 CREATE TABLE tbname (coldefs) CREATE TABLE t(id INT, count
INT)
删除表单 DROP TABLE tbname DROP TABLE t
剔除表单中有所的行 TRUNCATE TABLE tbname TRUNCATE TABLE t

SQL是无区分轻重缓急写的。

SQL关系项目数据库的根本DML操作可以缩略为CRUD。

  • Create:使用 INSERT 语句创建
  • Read:使用 SELECT 语句子选择
  • Update:使用 UPDATE 语句更新
  • Delete:使用 DELETE 语句删除

基本的SQL DML命令

操作 SQL模式 SQL示例
增加行 INSERT INTO tbname VALUES(…) INSERT INTO t VALUES(7,40)
分选任何尽和满列 SELECT * FROM tbname SELECT * FROM t
选料任何行与有些列 SELECT cols FROM tbname SELECT id,count from t
挑选一些实行有列 SELECT cols FROM tbname WHERE condition SELECT
id,count from t WHERE count > 5 AND id = 9
修改一列的一部分行 UPDATE tbname SET col = value WHERE condition UPDATE
t SET count=3 WHERE id=5
除去部分行 DELETE FROM tbname WHERE condition DELETE FROM t WHERE
count <= 10 OR id = 16


DB-API

应用程序编程接口(API)是造访一些服务之函数集合。DB-API是Python中访问关系数据库的正统API。使用它好编制简单的程
序来处理多种类型的关系项目数据库,不欲也每种数据库编写独立的次。
它的最主要函数如下所示:

  • connect()
    总是数据库,包含参数用户称,密码,服务器地址,等等
  • cursor()
    创造一个cursor()对象来惯例查询
  • execute()和executemany()
    针对数据库执行一个要多独SQL命令
  • fetchone(),fetchmany()和fetchall()
    得到execute之后的结果

SQLite

SQLite(http://www.sqlite.org)是平种植轻量级的、优秀的开源关系项目数据库。它是故
Python
的标准库实现,并且存储数据库在普通文书被。这些文件于不同机器与操作系统的
间是可移栽的,使得 SQLite
成为简单关系项目数据库应用的可是移栽的化解方案。它不像功能 全面的 MySQL 或者
PostgreSQL,SQLite 仅仅支持原生 SQL 以及多用户并发操作。浏览
器、智能手机和任何以会管 SQLite 作为嵌入数据库。

率先用 connect() 函数连接本地的 SQLite
数据库文件,这个文件以及目录型数据库(管理 其他的表单)是当价格的。字符串
‘:memory:’ 仅用于在内存中创建数据库,有助于方便高效
地测试,但是程序结束或者计算机关闭时所发生数量还见面丢。

脚的栗子会创建一个多少库enterprise.db(自己预先创造一个文书) 和表单 zoo
用以管理路边繁华之宠物动物园 业务。表单的排列如下所示。
• critter 可变换长的字符串,作为主键。
• count 某动物之总数的整数值。
• damages 人和动物的互中损失的美元数目。

In [1]: import sqlite3

In [2]: conn = sqlite3.connect('enterprise.db') 

In [3]: curs = conn.cursor()

In [4]: curs.execute('''CREATE TABLE zoo
   ...: (critter VARCHAR(20) PRIMARY KEY,
   ...: count INT,
   ...: damages FLOAT)''') 
ERROR:root:An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line string', (1, 14))

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-4-819479c0680a> in <module>()
      2 (critter VARCHAR(20) PRIMARY KEY,
      3 count INT,
----> 4 damages FLOAT)''') 

OperationalError: near "KEY,": syntax error

In [5]: curs.execute('''CREATE TABLE zoo
   ...: (critter VARCHAR(20)  PRIMARY KEY, 
   ...: count INT,
   ...: damages FLOAT)''') 
Out[5]: <sqlite3.Cursor at 0x7fc4d86e2f10>
Python 只有在创建长字符串时才会用到三引号('''),例如 SQL 查询。
现在往动物园中新增一些动物:
In [6]: curs.execute('INSERT INTO zoo VALUES("duck", 5, 0.0)') 
Out[6]: <sqlite3.Cursor at 0x7fc4d86e2f10>

In [7]: curs.execute('INSERT INTO zoo VALUES("bear", 2, 1000.0)') 
Out[7]: <sqlite3.Cursor at 0x7fc4d86e2f10>

使用 placeholder 是一种更安全的、插入数据的方法:
In [8]: ins = 'INSERT INTO zoo (critter,count,damages) VALUES (?,?,?)' 

In [9]: curs.execute(ins,('weasel',1,2000.0)) 
Out[9]: <sqlite3.Cursor at 0x7fc4d86e2f10>

在 SQL 中应用三个问号表示要插入三单价值,并将它当一个列表传入函数
execute()。
这些占位符用来拍卖部分冗余的底细,例如引用(quoting)。它们会防止 SQL
注入:一栽 常见的 Web 外部攻击方式,向网插入恶意之 SQL 命令。

现在使用 SQL 获取所有动物:
In [10]: curs.execute('SELECT * from zoo')
Out[10]: <sqlite3.Cursor at 0x7fc4d86e2f10>

In [11]: rows = curs.fetchall() 

In [12]: print(rows) 
[('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]
需要按照降序得到它们:
In [13]: curs.execute('SELECT * from zoo ORDER BY count DESC') 
Out[13]: <sqlite3.Cursor at 0x7fc4d86e2f10>

In [14]: curs.fetchall()
Out[14]: [('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]
哪种类型的动物花费最多呢?
In [15]: curs.execute('''SELECT * FROM zoo WHERE 
    ...: damages = (SELECT MAX(damages) FROM zoo)''')  
Out[15]: <sqlite3.Cursor at 0x7fc4d86e2f10>

In [16]: curs.fetchall() 
Out[16]: [('weasel', 1, 2000.0)]
我们打开了一个连接(connection)或者游标 (cursor),不需要时应该关掉它们:
In [17]: curs.close()
In [18]: conn.close() 

SQLAlchemy

对于有所的关联项目数据库而言,SQL 是不完全相同的,并且 DB-API
仅仅实现共有的部
分。每一样种植数据库实现的凡富含自己特点的以及哲学的白话。许多库函数用于破除其中的歧异,最显赫的越数据库的
Python 库是 SQLAlchemy。

她不以 Python 的标准库,但让广大承认,使用者多。在Linux系统中应用下
这漫漫命令安装她:

pip install sqlalchemy

咱们好于偏下层级上是用SQLAlchemy:

  • 根负责处理数据库连接池,执行SQL命令和返回结果,这和DB-API相似;
  • 复向上是SQL表达式语言,更像Python的SQL生成器。
  • 比高级的是目标关系模型(ORM),使用SQK表达式语言,将应用程序代码和关系型数据结构结合起来。
    SQLAlchemy实现以前面几节约提到的数据库驱动程序的功底及。因此不欲导入驱动程序,初始化的连字符串会作出分配,例如:

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

字符串中的值代表如下含义。

  • dialect
    数据库类型
  • driver
    使用该数据库的一定驱动程序
  • user和password
    数据库认证字符串
  • host和port
    数据库服务器的职(只有一定情景下会动用端口号:port)
  • dbname
    开始连接到服务器遭受之数据库

SQLAlchemy连接

数据库类型 驱动程序
sqlite pysqlite(可以忽略)
mysql mysqlconnector
mysql pymysql
mysql oursql
postgresql psycopg2
postgresql pypostgresql

1.引擎层

率先,我们试用一下 SQLAlchemy 的最底层,它可实现多于基本 DB-API 的作用。

因为放开于 Python 的 SQLite 为例,连接字符串忽略 host、port、user 和
password。dbname 表示存储 SQLite
数据库的文书,如果省去dbname,SQLite会在内存中开创数据库。如果 dbname
以相反斜线(/)开头,那么它们是文件所在的绝对路径(Linux 和 OS X
是倒转斜线,而于 Windows 是像 C:\
的路途径名)。否则她是当前目录下的相对路径。

下面是一个小栗子:

导入库函数,并起别名 sa
In [22]: import sqlalchemy as sa 
连接到数据库,并在内存中存储它(参数字符串 'sqlite:///:memory:' 也是可行的):
In [23]: conn = sa.create_engine('sqlite://')
创建包含三列的数据库表单 zoo:
In [24]: conn.execute('''CREATE TABLE zoo 
    ...: (critter VARCHAR(20) PRIMARY KEY,
    ...: count INT, 
    ...: damages FLOAT)''') 
Out[24]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2e6de48>
运行函数 conn.execute() 返回到一个 SQLAlchemy 的对象 ResultProxy
现在向空表单里插入三组数据:
In [25]: ins ='INSERT INTO zoo (critter, count, damages) VALUES (?, ?, ?)'  

In [26]: conn.execute(ins, 'duck', 10, 0.0)  
Out[26]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2eb7fd0>

In [27]: conn.execute(ins, 'bear', 2, 1000.0)  
Out[27]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2efc518>

In [28]:  conn.execute(ins, 'weasel', 1, 2000.0)  
Out[28]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2eadc50>

In [29]: rows = conn.execute('SELECT * FROM zoo) 
  File "<ipython-input-29-337b763205d0>", line 1
    rows = conn.execute('SELECT * FROM zoo)
                                            ^
SyntaxError: EOL while scanning string literal

查询所有放进去的数据:
In [30]: rows = conn.execute('SELECT * FROM zoo') 
在SQLAlchemy中,row不是一个列表,不能直接输出:
In [31]: print(rows)
<sqlalchemy.engine.result.ResultProxy object at 0x7fc4d2e6d9e8>
但它可以像列表一样迭代,每次可以得到其中的一行:
In [32]: for row in rows: 
    ...:     print(row) 
    ...:      
('duck', 10, 0.0)
('bear', 2, 1000.0)
('weasel', 1, 2000.0)

这例子几乎跟 SQLite DB-API
提到的示范是如出一辙的。一个优势是于程序开始经常未欲导入数据库驱动程序,SQLAlchemy
从连字符串(connection string)已经指定了。改变连
接字符串就可以使得代码可移栽到其它一样栽数据库。

2.SQL表达式语言

还朝着上同样重叠是SQLAlchemy的SQL表达式语言。它介绍了创造多种SQL操作的函数。相比引擎层,他会处理又多SQL方言的反差,对于涉及项目数据库应用是如出一辙种好的中间层解
决方案。

脚介绍如何创建和保管数据表 zoo。

In [33]: import sqlalchemy as  sa 

In [34]: conn = sa.create_engine('sqlite://')
在定义表单 zoo 时,开始使用一些表达式语言代替 SQL:
In [35]: meta = sa.MetaData() 

In [36]: zpptb = sa.Table('zoo',meta,
    ...: sa.Column('critter', sa.String, primary_key=True), 
    ...: sa.Column('count',sa.Integer),
    ...: sa.Column('damages',sa.Float) 
    ...: ) 
下面的的代码创建了数据表
In [37]: meta.create_all(conn)

留神多行调用时之圆括号。Table()
方法的调用结构及表单的布局相平等,此表单中包含 三列,在 Table()
方法调用时括号里也调用三差 Column()。
并且,zpptb是接连 SQL 数据库和 Python 数据结构的一个目标。
使用表达式语言的再度多函数插入数据:

In [38]: conn.execute(zpptb.insert(('bear',2,1000.0)))
Out[38]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2e6d0b8>

In [39]: conn.execute(zoo.insert(('weasel', 1, 2000.0))) 
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-39-1f816288e04e> in <module>()
----> 1 conn.execute(zoo.insert(('weasel', 1, 2000.0)))

NameError: name 'zoo' is not defined

In [40]: conn.execute(zpptb.insert(('weasel', 1, 2000.0))) 
Out[40]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2e64278>

In [41]: conn.execute(zpptb.insert(('duck', 10, 0))) 
Out[41]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2e4a550>

接下来创建 SELECT 语句(zpptb.select() 会选择出 zpptb 对象表单的所有项,和 SELECT * FROM zoo 在普通 SQL 做的相同)
In [42]: result = conn.execute(zpptb.select()) 

最终得到的结果
In [43]: rows = result.fetchall() 

In [44]: print(rows) 
[('bear', 2, 1000.0), ('weasel', 1, 2000.0), ('duck', 10, 0.0)]

3.目标关联映射(Object Relation Mapping)

当直达等同节约吃,对象 zpptb 是 SQL 和 Python 之间的中间层连接。在 SQLAlchemy
的顶层,对象关系映射(ORM)使用 SQL
表达式语言,但尽可能隐藏实际数据库的建制。我们和好定义 类,ORM
负责处理如何读写数据库的数码。在 ORM 这个复杂短语背后,最基本的眼光
是:同样以一个关乎项目数据库,但操作数据的不二法门还是与Python保持类似。

我们定义一个接近Zoo,并将其挂接到ORM。这无异于赖我们使用SQLite的zoo.db文件以便验证ORM是否行得通。

初始的import 还是一样,这一次需要导入新的东西:
In [1]: import sqlalchemy as  sa
In [2]: from sqlalchemy.ext.declarative import declarative_base
连接数据库:
In [3]: conn = sa.create_engine('sqlite:///zoo.db')
现在进入 SQLAlchemy 的 ORM,定义类 Zoo,并关联它的属性和表单中的列:
In [4]: Base = declarative_base()
In [5]: 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)
   ...:      
下面这行代码创建数据库和表单:
In [6]: Base.metadata.create_all(conn)
然后通过创建 Python 对象插入数据,ORM 内部会管理这些:
In [7]: first = Zoo('duck',10,0.0) 

In [8]: second = Zoo('bear', 2, 1000.0)  

In [9]: third = Zoo('weasel', 1, 2000.0)  

In [10]: first 
Out[10]: <Zoo(duck, 10, 0.0)>
接下来,利用 ORM 接触 SQL,创建连接到数据库的会话(session):
In [11]: from sqlalchemy.orm import sessionmaker 
In [12]: Session = sessionmaker(bind=conn) 
In [13]: session = Session() 
借助会话,把创建的三个对象写入数据库。add() 函数增加一个对象,而 add_all() 增加一 个列表
In [14]: session.add(first) 

In [15]: session.add_all([second,third])
最后使整个过程完整:
In [16]: session.commit()

我们现当当前目录下创造了文本zoo.db,可以采用命令行的 SQLite3 程序验证
一下:

[root@wangerxiao ~]# sqlite3 zoo.db 
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> .tables           
zoo
sqlite> select * from zoo;
duck|10|0.0
bear|2|1000.0
weasel|1|2000.0

本节底目的是介绍 ORM 和其当顶层的贯彻过程。

季个层级按照需求选择:

  • 普通 DB-API
  • SQLAlchemy 引擎层
  • SQLAlchemy 表达式语言
  • SQLAlchemy ORM

MySQL

MySQL(http://www.mysql.com)是一律款款大流行的开源关系项目数据库。不同为
SQLite,
它是实在的数据库服务器,因此客户端好透过网络由不同之装备连接它。

MySQL的驱动程序:Connector,PYMySQL,oursql

PostgreSQL

PostgreSQL(http://www.postgresql.org/)
是如出一辙暂缓效果完善的开源关系项目数据库,在多方 面超过 MySQL.

名称 链接 Pypi包 导入 注意
psycopg2
http://initd.org/psycopg/
psycopg2 psycopg2 需要来 PostgreSQL 客户端工具 的 pg_config

极致盛的驱动程序是 psycopg2,但是它的安依赖 PostgreSQL
客户端的相关库。

注:本文内容来《Python语言及其使用》欢迎购买原书阅读

网站地图xml地图