Python3-访问数据库

为了便于程序保存和读取数据，而且，能直接通过条件快速查询到指定 的数据，就出现了数据库（Database）这种专门用于集中存储和查询的软件。

数据库类别

　　 付费的商用数据库：

• Oracle，典型的高富帅；
• SQL Server，微软自家产品，Windows 定制专款；
• DB2，IBM的产品，听起来挺高端；
• Sybase，曾经跟微软是好基友，后来关系破裂，现在家境惨淡。

这些数据库都是不开源而且付费的，最大的好处是花了钱出了问题可以 找厂家解决，不过在Web 的世界里，常常需要部署成千上万的数据库 服务器，当然不能把大把大把的银子扔给厂家，所以，无论是Google、 Facebook，还是国内的 BAT，无一例外都选择了免费的开源数据库：

• MySQL，大家都在用，一般错不了；
• PostgreSQL，学术气息有点重，其实挺不错，但知名度没有MySQL 高；
• sqlite，嵌入式数据库，适合桌面和移动应用。

使用SQLite

SQLite 是一种嵌入式数据库，它的数据库就是一个文件。由于 SQLite 本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中， 甚至在 iOS 和Android 的App 中都可以集成。
　　Python 就内置了 SQLite3，所以，在 Python 中使用 SQLite，不需要安装 任何东西，直接使用。

在使用 SQLite 前，我们先要搞清楚几个概念：

• 表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个 表，比如学生的表，班级的表，学校的表，等等。
• 表和表之间通过外键 关联。
• 要操作关系数据库，首先需要连接到数据库，一个数据库连接称为 Connection；
• 连接到数据库后，需要打开游标，称之为Cursor，通过Cursor 执行 SQL 语句，然后获得执行结果。

Python 定义了一套操作数据库的API 接口，任何数据库要连接到 Python，只需要提供符合 Python 标准的数据库驱动即可。 由于 SQLite 的驱动内置在 Python 标准库中，所以我们可以直接来操作 SQLite 数据库。 我们在 Python 交互式命令行实践一下：

# 导入 SQLite 驱动:
>>> import sqlite3
# 连接到 SQLite 数据库
# 数据库文件是 test.db
# 如果文件不存在，会自动在当前目录创建:
>>> conn = sqlite3.connect(‘test.db‘)
# 创建一个 Cursor:
>>> cursor = conn.cursor()
# 执行一条 SQL 语句，创建 user 表:
>>> cursor.execute(‘create table user (id varchar(20) primary key, name
varchar(20))‘)
<sqlite3.Cursor object at 0x10f8aa260>
# 继续执行一条 SQL 语句，插入一条记录:
>>> cursor.execute(‘insert into user (id, name) values (\‘1\‘,\‘Michael\‘)‘)
<sqlite3.Cursor object at 0x10f8aa260>
# 通过 rowcount 获得插入的行数:
>>> cursor.rowcount
1
# 关闭 Cursor:
>>> cursor.close()
# 提交事务:
>>> conn.commit()
# 关闭 Connection:
>>> conn.close()
我们再试试查询记录：
>>> conn = sqlite3.connect(‘test.db‘)
>>> cursor = conn.cursor()
# 执行查询语句:
>>> cursor.execute(‘select * from user where id=?‘, ‘1‘)
<sqlite3.Cursor object at 0x10f8aa340>
# 获得查询结果集:
>>> values = cursor.fetchall()
>>> values
[(‘1‘, ‘Michael‘)]
>>> cursor.close()
>>> conn.close()

注意：

1. 使用 Python 的DB-API 时，只要搞清楚 Connection 和 Cursor 对象，打开后记得关闭
2. 使用Cursor对象执行insert，update，delete语句时，rowcount返回影响的行数，就可以拿到执行结果。
3. 使用 Cursor 对象执行 select 语句时，通过 featchall()可以拿到结果集。结果集是一个 list，每个元素都是一个 tuple，对应一行记录。
4. 如果 SQL语句带有参数，那么需要把参数按照位置传递给 execute()方法，有几个?占位符就必须对应几个参数，例如：　　cursor.execute(‘select * from user where id=?‘, ‘1‘)
5. SQLite 支持常见的标准 SQL语句以及几种常见的数据类型。
6. 具体文档在 Python 中操作数据库时，要先导入数据库对应的驱动，然后，通过Connection 对象和 Cursor 对象操作数据。
7. 要确保打开的 Connection 对象和 Cursor对象都正确地被关闭，否则，资源就会泄露。（ try:...except:...finally:...）

练习

#请编写函数，在 Sqlite 中根据分数段查找指定的名字：

# -*- coding: utf-8 -*-
import os, sqlite3
db_file = os.path.join(os.path.dirname(__file__), ‘test.db‘)
if os.path.isfile(db_file):
    os.remove(db_file)
# 初始数据:
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute(‘create table user(id varchar(20) primary key, name varchar(20), score int)‘)
cursor.execute(r"insert into user values (‘A-001‘, ‘Adam‘, 95)")
cursor.execute(r"insert into user values (‘A-002‘, ‘Bart‘, 62)")
cursor.execute(r"insert into user values (‘A-003‘, ‘Lisa‘, 78)")
cursor.close()
conn.commit()
conn.close()
def get_score_in(low, high):
    #返回指定分数区间的名字，按分数从低到高排序 ‘
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()
    cursor.execute(‘select name from user where score between ? and ? order by score asc‘,(low,high))
    values = cursor.fetchall()
    name= [x[0]for x in values]
    cursor.close()
    conn.close()
    #print(name)
    return name


# 测试:
assert get_score_in(80, 95) == [‘Adam‘], get_score_in(80, 95)
assert get_score_in(60, 80) == [‘Bart‘, ‘Lisa‘], get_score_in(60, 80)
assert get_score_in(60, 100) == [‘Bart‘, ‘Lisa‘, ‘Adam‘],get_score_in(60, 100)
print(‘Pass‘)

使用 MySQL

MySQL 是Web 世界中使用最广泛的数据库服务器。

SQLite 的特点是量级、可嵌入，但不能承受高并发访问，适合桌面和移动应用。

而MySQL是为服务器端设计的数据库，能承受高并发访问，同时占用的内存也远 远大于 SQLite。

此外，MySQL 内部有多种数据库引擎，最常用的引擎是支持数据库事务的 InnoDB。

MySQL安装：

https://www.cnblogs.com/jpga/p/12566697.html

在Mysql的官网上https://dev.mysql.com/doc/connector-python/en/connector-python-versions.html查看对应关系：

安装驱动：（我用的第二个，第一个报错说没有--allow-external操作）

$ pip install mysql-connector-python --allow-external mysql-connector-python 或 <span>$ pip install mysql-connector</span>

　　第二个驱动装好后使用过程中还是报错，重装了一个pymysql,用法改一下就行

#书中源码

# 导入 MySQL 驱动:
>>> import mysql.connector 
# 注意把 password 设为你的 root 口令: 
>>> conn = mysql.connector.connect(user=‘root‘, password=‘password‘, database=‘test‘)
>>> cursor = conn.cursor() 
# 创建 user 表: 
>>> cursor.execute(‘create table user (id varchar(20) primary key, name varchar(20))‘) 
# 插入一行记录，注意 MySQL 的占位符是%s:
>>> cursor.execute(‘insert into user (id, name) values (%s, %s)‘, [‘1‘, ‘Michael‘])
>>> cursor.rowcount 1 
# 提交事务:
>>> conn.commit() 
>>> cursor.close() 
# 运行查询:
>>> cursor = conn.cursor()
>>> cursor.execute(‘select * from user where id = %s‘, [‘1‘])
>>> values = cursor.fetchall() 
>>> values
[(‘1‘, ‘Michael‘)] 
# 关闭 Cursor 和 Connection:
>>> cursor.close() True
>>> conn.close() 


#由于 Python 的DB-API 定义都是通用的，所以，操作MySQL的数据库 代码和 SQLite 类似。

#修改后

import pymysql
conn = pymysql.connect(‘localhost‘,‘root‘, ‘password‘, ‘test‘)
cursor = conn.cursor()
#创建 user 表:
cursor.execute(‘create table user1 (id varchar(20) primary key, name varchar(20))‘)
#插入一行记录，注意 MySQL 的占位符是%s:
cursor.execute(‘insert into user1 (id, name) values (%s, %s)‘, [‘1‘, ‘Michael‘])
print(cursor.rowcount)
conn.commit()
cursor.close()

#运行查询
cursor = conn.cursor()
cursor.execute(‘select * from user1 where id = %s‘, [‘1‘])
values = cursor.fetchall()#[(‘1‘, ‘Michael‘)]
# 关闭 Cursor 和 Connection: >>> cursor.close() True
conn.close()

连接的test数据库需要事先在mysql中创建

 其他常用操作：

show databases;    　　　　# 显示所有数据库
create database t1; 　　　　# 创建数据库t1
use database t1;   　　　　#指定当前操作的数据库为t1
drop database t1;  　　　　#删除数据库t1

mysql-connector使用案例：

https://www.cnblogs.com/vathe/p/6964086.html

使用 SQL Alchemy

数据库表是一个二维表，包含多行多列。

按 Python 的 数据结构：list 表示多行，list 的每一个元素是 tuple，表示一行记录，例如包含 id 和 name 的 user 表：[(‘1‘, ‘Michael‘), (‘2‘, ‘Bob‘), (‘3‘, ‘Adam‘)]

但是用 tuple 表示一行表的结构不明显，用 class 实例来表示，会更清晰：

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

[User(‘1‘, ‘Michael‘), User(‘2‘, ‘Bob‘), User(‘3‘, ‘Adam‘)]

ORM技术：Object-Relational Mapping，把关系数据库的表结构映射到对象上。

ORM框架可完成此转换。在 Python 中，最有名的ORM框架是 SQLAlchemy。

SQLAlchemy 的用法如下。

1. 首先通过 pip 安装 SQLAlchemy： $ pip install sqlalchemy
2. 然后，利用上次我们在MySQL的test 数据库中创建的 user表，用 SQLAlchemy 来试试：

以下代码完成 SQLAlchemy 的初始化和具体每个表的 class 定义。

# 导入:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker 
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类: 
Base = declarative_base()

# 定义 User 对象: 
class User(Base): 
    # 表的名字:
    __tablename__ = ‘user‘
   
     # 表的结构: 
    id = Column(String(20), primary_key=True) 
    name = Column(String(20))

# 初始化数据库连接:
engine =
create_engine(‘mysql+mysqlconnector://root::3306/test‘) # 创建 DBSession 类型:
DBSession = sessionmaker(bind=engine)

如果 有多个表，就继续定义其他 class，例如 School：

class School(Base):
　　__tablename__ = ‘school‘

id = ...

name = ...

create_engine()用来初始化数据库连接。

SQLAlchemy 用一个字符串表示连接信息：

‘数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名‘

你只需要根据需要替换掉用户名、口令等信息即可。 下面，我们看看如何向数据库表中添加一行记录。 由于有了ORM，我们向数据库表中添加一行记录，可以视为添加一个 User 对象：

# 创建 session 对象:
session = DBSession() 
# 创建新 User 对象:
new_user = User(id=‘5‘, name=‘Bob‘) 
# 添加到 session: 
session.add(new_user) 
# 提交即保存到数据库: 
session.commit() 
# 关闭 session: 
session.close()

可见，关键是获取 session，然后把对象添加到 session，最后提交并关 闭。DBSession 对象可视为当前数据库连接。
如何从数据库表中查询数据呢？有了ORM，查询出来的可以不再是 tuple，而是 User对象。SQLAlchemy 提供的查询接口如下：

# 创建 Session:
session = DBSession() 
# 创建 Query 查询，filter 是 where 条件，最后调用 one()返回唯一行，如果调用 all()则返回所有行:
user = session.query(User).filter(User.id==‘5‘).one()
# 打印类型和对象的 name 属性:
print(‘type:‘, type(user)) 
print(‘name:‘, user.name) 
# 关闭 Session: 
session.close()

运行结果如下：
type: <class ‘__main__.User‘> 
name: Bob

session = DBSession()
# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
user = session.query(User).filter(User.name==‘Bob‘).all()
# 打印类型和对象的name属性:
print(‘type:‘, type(user))
for row in user:
    print(‘id: %s , name: %s‘%(row.id, row.name))

type: <class ‘list‘>
id: 10 , name: Bob
id: 111 , name: Bob
id: 112 , name: Bob
id: 132 , name: Bob
id: 138 , name: Bob
id: 14 , name: Bob
id: 5 , name: Bob
id: 6 , name: Bob
id: 8 , name: Bob
id: 9 , name: Bob

基本使用：

https://www.jianshu.com/p/cf77beef7a25

可见，ORM就是把数据库表的行与相应的对象建立关联，互相转换。
由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相 应地，ORM框架也可以提供两个对象之间的一对多、多对多等功能。 例如，如果一个User 拥有多个Book，就可以定义一对多关系如下：

#例如，如果一个User 拥有多个Book，就可以定义一对多关系如下：
class User(Base):
    __tablename__ = ‘user‘
    id = Column(String(20), primary_key=True) 
    name = Column(String(20)) 
    # 一对多:
    books = relationship(‘Book‘) 

class Book(Base): 
    __tablename__ = ‘book‘
    id = Column(String(20), primary_key=True) 
    name = Column(String(20)) 
    # “多”的一方的 book 表是通过外键关联到 user 表的: 
    user_id = Column(String(20), ForeignKey(‘user.id‘))

当我们查询一个User 对象时，该对象的 books 属性将返回一个包含若 干个Book 对象的 list。 小结 ORM框架的作用就是把数据库表的一行记录与一个对象互相做自动转 换。
正确使用ORM的前提是了解关系数据库的原理。