Python核心编程笔记————数据库编程（一）

数据库简介

持久化存储

．　持久化存储一般有三种基础的存储机制：文件、数据库系统以及一些混合类型。这种混合类型包括现有系统上的 API、ORM、文件管理器、电子表格、配置文件等。

数据库基本操作和SQL

，　以下包括一些基础的数据库概念和SQL语句

底层存储

．　数据库通常使用文件系统作为基本的持久化存储。它可以是普通的操作系统文件、专用的操作系统文件，甚至是原始的磁盘分区。

用户接口

．　大多数数据库系统提供了命令行工具，可以用其执行 SQL 语句或查询。此外还有一些GUI 工具，使用命令行客户端或数据库客户端库，向用户提供更加便捷的界面。

数据库

．　一个关系数据库管理系统（RDBMS）通常可以管理多个数据库，MySQL 是一种基于服务的RDBMS，因为它有一个服务器进程始终运行以等待命令行输入；而 SQLite 和 Gadfly（一个完全使用 Python 编写的简单的 RDBMS） 则不会运行服务器。

组件

．　数据库存储可以抽象为一张表。每行数据都有一些字段对应于数据库的列。每一列的表定义的集合以及每个表的数据类型放到一起定义了数据库的模式（schema）。

SQL

．　数据库命令和查询操作是通过 SQL 语句提交给数据库的。并非所有数据库都使用SQL 语句，但是大多数关系数据库使用。大部分数据库都是不区分大小写的，尤其是针对数据库命令而言。一般来说，对数据库关键字使用大写字母是最为广泛接受的风格。大多数命令行程序需要一条结尾的分号（;）来结束这条 SQL语句。以下是一些SQL命令示例：

数据库与Python

．　访问数据库包括直接通过数据库接口访问和使用 ORM （对象关系映射）访问两种方式。其中使用 ORM 访问的方式不需要显式给出 SQL 命令，但也能完成相同的任务。
　　在Python中数据库是通过适配器的方式来访问的。适配器是一个Python模块，使用它可以与关系数据库的客户端库（通常是使用C语言进行编写的）接口相连。

Python的DB-API

．　DB-API 是阐明一系列所需对象和数据库访问机制的标准，它可以为不同的数据库适配器和底层数据库系统提供一致性的访问。DB-API 也是强需求驱动的。这种一致性产生的原因是曾经有很多的数据库和数据库适配器，在功能上完全没有一致性可言，这意味着使用这些接口的应用代码需要与他们选择的数据库模块进行定制化的处理，接口的任何改变都会导致应用代码的变更。
　　由此，为了解决 Python 数据库连接问题的特殊兴趣小组成立了，并且撰写了 1.0 版本的DB-API。该 API 为不同的关系数据库提供了一致性的接口，并且使不同数据库间移植代码变得更加简单，通常只需要修改几行代码即可。

模块属性

．　DB-API 标准要求必须提供下文列出的功能和属性。一个兼容 DB-API 的模块必须定义下表所示的几个全局属性。

数据属性

．　apilevel是一个字符串（注意，不是浮点型），它指明了模块需要兼容的DB-API 最高版本，默认值是1.0
　　threadsafety是一个整型值，可选值如下：
　　0：不支持线程安全。线程间不能共享模块。
　　1：最小化线程安全支持：线程间可以共享模块，但是不能共享连接。
　　2：适度的线程安全支持：线程间可以共享模块和连接，但是不能共享游标。
　　3：完整的线程安全支持：线程间可以共享模块、连接和游标。

参数风格

．　DB-API 支持以不同的方式指明如何将参数与SQL 语句进行整合，并最终传递给服务器中执行。该参数是一个字符串，用于指定构建查询行或命令时使用的字符串替代形式

函数属性

．　connect()函数通过 Connection 对象访问数据库。兼容模块必须实现 connect()函数，该函数创建并返回一个 Connection 对象，下表为connect()函数的参数：

．　使用 DSN 还是独立参数主要基于所连接的系统。比如，如果你使用的是像ODBC（OpenDatabase Connectivity）或 JDBC（Java Database Connectivity）的 API，则需要使用 DSN；而如果你直接使用数据库，则更倾向于使用独立的登录参数。另一个使用独立参数的原因是很多数据库适配器并没有实现对 DSN 的支持。

异常

．　异常同样需要包含在兼容的模块中，如下表所示：

connection对象

．　应用与数据库之间进行通信需要建立数据库连接，当一个连接（或一个连接池）建立后，可以创建一个游标，向数据库发送请求，然后从数据库中接收回应。

connection对象方法

．　Connection 对象不需要包含任何数据属性，不过应当定义以下方法：

．　当使用close时，这个连接将不能被使用，都则会进入异常处理中。
　　commit()和rollback()只有在支持事务处理的数据库中才能使用，如果数据库启用了自动提交的功能，commit()方法也不能使用；一般发生异常后，rollback()方法会将数据库的状态恢复到事务处理开始前的状态。
　　如果RDBMS不支持游标，那么cursor()方法仍会返回一个尽可能模仿游标的对象。
　　DB-API 建议适配器开发者为连接编写所有的数据库模块异常，但不是强制要求，如果没有，则认为 Connection 对象将会抛出对应模块级别的异常。

Cursor 对象

．　当建立连接后，就可以和数据库进行通信了。游标可以让用户提交数据库命令，并获得查询的结果行。以下为Cursor对象的属性和方法：

．　游标对象最重要的属性是 execute*()和 fetch*()方法，所有针对数据库的服务请求都
是通过它们执行的。arraysize 数据属性在为 fetchmany()设置默认大小时非常有用。

类型对象和构造函数

．　不同系统之间的接口是很令人头疼的，比如Python对象传给原生数据库对象的内容一般是一个字符串，但是数据库可能需要对其转换成不同的类型，比如传入的字符串是应该转换为VARCHAR、TEXT还是DATE对象呢？因此，DB-API的另一个需求是创建构造函数，从而构建可以简单地转换成适当数据库对象的特殊对象。下表是用于此目的的一些类：

使用数据库适配器的示例

MySQL

．　这里使用的是MySQL 官方提供的驱动器：mysql-connector 。

import mysql.connector as mc					#不要把文件名命名为mysql

md = mc.connect(
    host = "xx.xx.x.xx",						#服务器地址，就是单纯的ip地址
    port = "xxxx"								#不写的话默认为3306
    user = "xxxx",
    passwd = "xxxx",
    database = "xxxxx"						#数据库名
)
mycursor = md.cursor()

print("返回所有数据")
mycursor.execute("select * from table")
result = mycursor.fetchall()
for x in result:
    print(x)
    
print("返回一条数据")
mycursor.execute("select * from table")	#因为前面fetchall了，这里不重新执行的话，后面会为空
result = mycursor.fetchone()
print(result)

mycursor.close()
md.close()

oracle

．　这里使用的是cx_Oracle :

import cx_Oracle as co

str = 'user/password@xx.xx.xx.xx:xxxx/xxxx'
conn = co.connect(str)
c = conn.cursor()
x = c.execute(sql)
value = x.fetchone()

ORM（对象关系映射）

．　大部分数据库系统都包含Python接口，能使人更好的利用它们的功能，但是缺点是需要去了解SQL，如果更希望操纵Python对象而不是SQL查询，且仍然希望使用关系数据库作为数据后端，那么可以使用 ORM。

Python与ORM

．　 ORM 系统的作者将纯 SQL 语句进行了抽象化处理，将其实现为 Python 中的对象，数据库表被神奇地转化为 Python 类，其中的数据列作为属性，而数据库操作则会作为方法。
　　以下介绍一个知名的 Python ORM：SQLAlchemy。这不是标准库中的模块，需要手动下载。

from sqlalchemy import Column,String,create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()       #创建基类

class CurMode(Base):
    __tablename__ = 'curr_system_mode'

    #表结构
    station = Column(String(8),primary_key=True)
    mode_code = Column(String(4))

#初始化数据库连接
#'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
engine = create_engine('mysql+mysqlconnector://xx:xxx@xx.xx.xx.xx:3306/xx')
dbsession = sessionmaker(bind = engine)

#创建session对象，执行操作
session = dbsession()
user = session.query(CurMode).one()
print(user.station)
print(user.mode_code)

session.close()

．　以下是一些常用的用于查询的方法：
• filter_by()：将指定列的值作为关键字参数以获取查询结果。
• filter()：与filter_by()相似，不过更加灵活，还可以使用表达式。比如query.filter_by(userid=1)与query.filter(User.userid==1)相同。
• order_by()：与 SQL 的 ORDER BY 指令类似。默认情况下是升序的。需要导入
sqlalchemy.desc()使其降序排列。
• limit()：与 SQL 的 LIMIT 指令类似。
• offset()：与 SQL 的 OFFSET 指令类似。
• all()：返回匹配查询的所有对象。
• one()：返回匹配查询的唯一一个（下一个）对象。
• first()：返回匹配查询的第一个对象。
• join()：按照给定的 JOIN 条件创建 SQL JOIN 语句。
• update()：批量更新行。
• delete()：批量删除行。