Python数据库编程

• 简介

  创新互联长期为成百上千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为亚东企业提供专业的做网站、网站制作，亚东网站改版等技术服务。拥有十载丰富建站经验和众多成功案例,为您定制开发。

  在任何应用中，都需要持久化存储，一般有3种基础的存储机制：文件、数据库系统以及一些混合类型。这种混合类型包括现有系统上的API、ORM、文件管理器、电子表格、配置文件等。在了解数据库以及如何在Python中使用他们之前，首先需要知道数据库概念以及SQL语句。

底层存储

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

用户接口

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

数据库

  一个关系数据库管理系统(RDBMS)通常可以管理多个数据库，比如销售、市场、用户支持等，都可以在同一个服务端。

组件

  数据库存储可以抽象为一张表。每行数据都有一些字段对应于数据库的列。每一行的表定义的集合以及每个表的数据类型放到一起定义了数据库的模式(schema)。数据库可以创建(create)和删除(drop)，表也一样。往数据库里添加新行叫做插入(insert)，修改表中已存在的行叫做更新(update)，而移除表中已存在的行叫做删除(delete)、这些动作通常称为数据库命令或操作。使用可选条件请求获取数据库中的行称为查询(query)。

SQL

   数据库命令和查询操作是通过SQL语句提交给数据库的。虽然并非所有数据库都是用SQL语句，但是大多数关系数据库使用。下面是一些SQL命令示例，大部分数据库不区分大小写，但是对数据库关键字使用大写字母是最为广泛接受的风格。大多数命令需要结尾的分号(;)来结束这条语句。

创建数据库

MySQL> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

使用数据库与删除数据库

mysql> USE test;
Database changed
mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.00 sec)

创建表

mysql> CREATE TABLE users (login VARCHAR(8),userid INT,projid INT);
Query OK, 0 rows affected (0.02 sec)

插入行

mysql> INSERT INTO users VALUES('lena',211,1);
Query OK, 1 row affected (0.00 sec)

更新行

mysql> UPDATE users SET userid=311 WHERE projid=1;        
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

删除行

mysql> DELETE FROM users WHERE projid=1;
Query OK, 1 row affected (0.00 sec)

删除表并清空数据

mysql> DROP TABLE users;
Query OK, 0 rows affected (0.00 sec)

   在Python中数据库是通过适配器的方式进行访问。适配器是一个Python模块，使用它可以与关系型数据库的客户端接口相连。如图所示为编写Python数据库应用的结构，包括使用和没有使用ORM的情况。从图中可以看出DB-API是连接到数据库客户端的C语言的接口。

• Python的DB-API

   DB-API是阐明一系列所需对象和数据库访问机制的标准，它可以为不同的数据库适配器和底层数据库提供一致性的访问。DB-API标准要求必须提供下表的功能和属性。

数据属性

  apilevel，该字符串致命模块需要兼容的DB-API最高版本

  threadsafety，指明模块线程的安全级别

    0：不支持线程安全，线程间不能共享模块。

    1：最小化线程安全支持，线程间可以共享模块，但不能共享连接。

    2：适度的线程安全支持，线程间可以共享模块和连接，但不能共享游标。

    3：完全的线程安全支持，线程可以共享模块，连接和游标。

参数风格 

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

函数属性

  connect()函数通过Connection对象访问数据库。兼容模块继续实现connect()函数，该函数创建并返回一个Connection对象。connect()函数可以使用包含多个参数的字符串来传递数据库连接信息，也可以按照位置传递每个参数，或者时使用关键字参数的形式传递。

connect(host ='localhost', user = 'root', passwd ='123456',db='movie',charset='utf8')

异常

Connection对象

  应用与数据之间进行通信需要建立数据库连接。它是最基本的机制，只有通过数据库连接才能把命令传递到服务器，并得到返回的结果。当一个连接建立后，可以创建一个游标，向数据库发送请求，然后从数据库中接收回应。

  Connection对象不需要包含任何数据，不过应当定义下标的几个方法：

Cursor对象

   当建立连接后，就可以和数据库进行通信。游标可以让用户提交数据库命令，并且获得查询结果行。Python DB-API游标对象总能提供游标的功能，游标对象最重要的属性是execute()和fetch()方法，所有针对数据库的服务请求都是通过它们执行的。

• ORM与SQLAlchemy

  ORM(Object-Relational Mapping,对象关系映射)的作用实在关系型数据库和业务实体对象之间做一个映射，这样开发者在操作数据库的数据时，就不需要再去和复杂的SQL语句打交道，只需要简单的操作对象的属性和方法。所有ORM必须具备3个方面的基本能力：映射技术、CURD操作和缓存技术。

   ORM在卡发者和数据库之间建立了中间层，把数据库中的数据转换成了Python中的对象实体，这样即屏蔽不同数据库之间的差异性，又使开发者可以非常方便的操作数据库中的数据。当前SQLAlchemy是Python中最成熟的ORM框架，资源和文档丰富。大多数Python Web框架对其都有很好的支持。

  Dialect用于和数据API进行连接，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作：

连接数据库：

In [1]: from sqlalchemy import create_engine
In [2]: engine = create_engine('mysql+mysqlconnector://root@127.0.0.1:3306
   ...: /test',echo=True)

创建表:

In [3]: from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey,Seque   ...:nce
In [4]: metadata = MetaData()
In [5]: users = Table('users', metadata,
   ...:     Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
   ...:     Column('name', String(50)),
   ...:     Column('fullname', String(50)),
   ...:     Column('password', String(12))
   ...: )
In [6]: addresses = Table('addresses', metadata,
   ...:     Column('id', Integer, primary_key=True),
   ...:     Column('user_id', None, ForeignKey('users.id')),
   ...:     Column('email_address', String(50), nullable=False)
   ...: )
In [7]: metadata.create_all(engine)
2017-05-19 17:59:46,958 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2017-05-19 17:59:46,959 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,960 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2017-05-19 17:59:46,960 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,962 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2017-05-19 17:59:46,962 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,963 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2017-05-19 17:59:46,963 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,964 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2017-05-19 17:59:46,964 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,965 INFO sqlalchemy.engine.base.Engine DESCRIBE `addresses`
2017-05-19 17:59:46,965 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,966 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-05-19 17:59:46,967 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE addresses (
        id INTEGER NOT NULL AUTO_INCREMENT, 
        user_id INTEGER, 
        email_address VARCHAR(50) NOT NULL, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES users (id)
)

2017-05-19 17:59:46,967 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,994 INFO sqlalchemy.engine.base.Engine COMMIT

插入数据：

In [8]: ins = users.insert()
In [9]: str(ins)
Out[9]: 'INSERT INTO users (id, name, fullname, password) VALUES (:id, :name, :fullname, :password)'
In [10]: ins = users.insert().values(id=1,name='jack', fullname='Jack Jones')
In [11]: ins.compile().params
  Out[11]: {'fullname': 'Jack Jones', 'id': 1, 'name': 'jack'}
In [12]: conn = engine.connect()
In [13]: result = conn.execute(ins)
2017-05-19 18:04:29,982 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name, fullname) VALUES (%(id)s, %(name)s, %(fullname)s)
2017-05-19 18:04:29,982 INFO sqlalchemy.engine.base.Engine {'id': 1, 'name': 'jack', 'fullname': 'Jack Jones'}
2017-05-19 18:04:29,982 INFO sqlalchemy.engine.base.Engine COMMIT
In [16]: conn.execute(addresses.insert(), [                       #多条语句插入
    ...: ... {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
    ...: ... {'user_id': 1, 'email_address' : 'jack@msn.com'},
    ...: ... {'user_id': 2, 'email_address' : 'www@www.org'},
    ...: ... {'user_id': 2, 'email_address' : 'wendy@aol.com'},
    ...: ... ])
2017-05-19 18:07:29,203 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (user_id, email_address) VALUES (%(user_id)s, %(email_address)s)
2017-05-19 18:07:29,203 INFO sqlalchemy.engine.base.Engine ({'user_id': 1, 'email_address': 'jack@yahoo.com'}, {'user_id': 1, 'email_address': 'jack@msn.com'}, {'user_id': 2, 'email_address': 'www@www.org'}, {'user_id': 2, 'email_address': 'wendy@aol.com'})
2017-05-19 18:07:29,204 INFO sqlalchemy.engine.base.Engine COMMIT
  Out[16]: 

查询

In [17]: from sqlalchemy.sql import select
In [18]: s = select([users])
In [19]: result = conn.execute(s)
2017-05-19 18:08:59,639 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, users.password 
FROM users
2017-05-19 18:08:59,639 INFO sqlalchemy.engine.base.Engine {}
In [20]: for row in result:
    ...:   print(row)
    ...:   
(1, 'jack', 'Jack Jones', None)
In [22]:  for row in conn.execute(select([users, addresses])):   #多条查询
    ...:     print(row)
    ...:    
2017-05-19 18:11:41,681 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, users.password, addresses.id, addresses.user_id, addresses.email_address 
FROM users, addresses
2017-05-19 18:11:41,681 INFO sqlalchemy.engine.base.Engine {}
(1, 'jack', 'Jack Jones', None, 1, 1, 'jack@yahoo.com')
(1, 'jack', 'Jack Jones', None, 2, 1, 'jack@msn.com')
(1, 'jack', 'Jack Jones', None, 3, 2, 'www@www.org')
(1, 'jack', 'Jack Jones', None, 4, 2, 'wendy@aol.com')

更新

In [27]: stmt = users.update().values(fullname="Fullname: " + users.c.name)
In [28]: conn.execute(stmt)
2017-05-19 18:27:33,489 INFO sqlalchemy.engine.base.Engine UPDATE users SET fullname=(concat(%(name_1)s, users.name))
2017-05-19 18:27:33,489 INFO sqlalchemy.engine.base.Engine {'name_1': 'Fullname: '}
2017-05-19 18:27:33,490 INFO sqlalchemy.engine.base.Engine COMMIT
Out[28]: 

删除

In [31]: conn.execute(addresses.delete())
2017-05-19 18:30:02,296 INFO sqlalchemy.engine.base.Engine DELETE FROM addresses
2017-05-19 18:30:02,296 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 18:30:02,297 INFO sqlalchemy.engine.base.Engine COMMIT
  Out[31]: 
In [32]:  conn.execute(users.delete().where(users.c.name > 'm'))
2017-05-19 18:30:12,159 INFO sqlalchemy.engine.base.Engine DELETE FROM users WHERE users.name > %(name_1)s
2017-05-19 18:30:12,159 INFO sqlalchemy.engine.base.Engine {'name_1': 'm'}
2017-05-19 18:30:12,159 INFO sqlalchemy.engine.base.Engine COMMIT
  Out[32]: