| pip install mysql-connector
# 如果数据库已经存在，可以直接指定 db='dbname' 参数
conn = mysql.connector.connect(host='127.0.0.1', port=3306, user='root',
password='password', charset='utf8')
cursor = conn.cursor()
cursor.execute('show databases') # 查询数据库
print(cursor.fetchall()) # 查询命令后必须进行 fetch 操作
# 创建 test 数据库
cursor.execute('create database if not exists test')
cursor.execute('use test')
try:
cursor.execute('''create table test
(id char(32) primary key not null,
name test not null,
age int not null);''')
except:
cursor.execute('''insert into test (id, name, age) values ('000', 'John', '23')''')
cursor.execute('''insert into test (id, name, age) values ('001', 'Tom', '25')''')
cursor.execute('''insert into test (id, name, age) values ('002', 'Jack', '29')''')
cursor.execute('''select * from test''')
# 查询一个结果
print(cursor.fetchone())
print(cursor.fetchall())
conn.commit()
cursor.close()
conn.close()
要注意的是查询命令后必须进行 fetch 操作，并取完所有结果，否则会报 Unread result found 错误。
18.3. ORM 框架
如果我们要使用多套数据库，那么就要实现多套数据库SQL接口，例如查询，删除等等，这导致代码重复繁琐，是否可以提供一个抽象层，把对数据库的SQL操作转化为对象操作呢？
对象关系映射（ORM，Object Relational Mapping）通过使用描述对象和数据库之间映射的元数据，将程序中的对象操作自动关联到关系数据库中。
ORM 是一种技术解决方案， Python 下提供了很多 ORM 的模块实现，如 peewee，Storm，SQLObject 和 SQLAlchemy。
使用 ORM 操作数据库相当于增加了一个封装转换层，性能上会打折扣，要根据实际情况选择使用。
18.3.1. peewee
peewee 是一个非常轻量级的 Python ORM 实现，它提供类似著名的 Django Web 框架 API，非常易于上手。
peewee 中定义了 Model 类，Field 和 Nodel 实例与数据库的映射关系如下：
# Person() 实例对应行
grandma = Person.create(name='Grandma', age = 60)
grandpa = Person.create(name='Grandpa', age = 62)
print_person(grandma)
print_person(grandpa)
1 Grandma 60
2 Grandpa 62
如果我们定义 Person 类时指定主键，将不再自动生成 id，例如：
2 | class Person(Model):
name = TextField(primary_key=True) # 指定 name 为主键
age = IntegerField()
可以批量添加数据，只需要将参数集中在一个字典里：
6 | persons = {{name='Grandma', age=60}, {name='Grandpa', age=62}}
for args in persons:
Person.create(**args)
for args in persons:
Person(
**args).save()
采用 sqlite 查看建表语句和 person 表中的数据：
6 | sqlite> .schema person
CREATE TABLE "person" ("id" INTEGER NOT NULL PRIMARY KEY,
"name" DATE NOT NULL, "age" INTEGER NOT NULL);
sqlite> select * from person;
1|Grandma|60
2|Grandpa|62
如果不指定主键，会自动生成 id ，我们在插入前需要判断当需要插入的数据是否存在。
18.3.1.3. 查询
使用 Model.select() 或者 Model.get() 类函数可以查询特定行，或者所有行：
19 | person = Person.select().where(Person.name == 'Grandma').get()
print_person(person)
1 Grandma 60
# 简化的查询方法
person = Person.get(Person.name == 'Grandma')
print_person(person)
1 Grandma
# 查询所有行
for person in Person.select():
print_person(person)
1 Grandma 60
2 Grandpa 62
也可以指定条件查询：
1 | for person in Person.filter(Person.id > 1):
print_person(person)
在查询是可以使用 Model.order_by() 方法进行排序：
1 | for person in Person.select().order_by(Person.name):
print_person(person)
# 更新 Father 行的 age 信息
father = Person.get(Person.name == 'Father')
father.age = 32
id = father.save()
print(id)
Model.delete() 类函数可以清空表或某个表项，instance.delete_instance() 用于删除一个特定表项：
12 | # 选择删除，成功返回 1，否则返回 0
Person.delete().where(Person.name == 'father').execute()
# 清空 Person 表项
Person.delete().execute()
# 已实例化的数据删除
father = Person.get(Person.id == 3)
id = father.delete_instance()
print(id)
12 | # SQLite 数据库，支持外键，启用 WAL 日志模式，缓存64MB
sqlite_db = SqliteDatabase('test.db', pragmas={
'foreign_keys': 1,
'journal_mode': 'wal',
'cache_size': -1024 * 64})
# 连接 MySQL 数据库 dbname
mysql_db = MySQLDatabase('dbname', user='username', password='password',
host='127.0.0.1', port=3306)
# 连接 Postgres 数据库
pg_db = PostgresqlDatabase('dbname', user='username', password='password',
host='127.0.0.1', port=5432)
peewee 使用 pymysql 或 MySQLdb 作为 MySQL 驱动模块，如果没有安装会提示错误。
18.3.1.6. Foreign Keys
如果一个表中一列要引用另一个表中的表项，例如一个家庭成员的表和一个宠物表，每个宠物都有它的主人，那么在主人这一项里面就可以引用家庭成员表中某个成员的 id，这种引用被称为外键。
20 | db = SqliteDatabase('people.db')
class Person(Model):
name = CharField()
birthday = DateField()
class Meta:
database = db
class Pet(Model):
name = CharField()
animal_type = CharField()
# 定义外键
owner = ForeignKeyField(Person, backref='pets')
class Meta:
database = db
# 创建 person 和 pet 表
db.create_tables([Person, Pet])
上面定义了个两个类，一个用于定义家庭成员，一个用于定义宠物。它们对应数据库中的两张表 person 和 pet，pet 中的主人一栏引用 person 中的 id。
为两个表插入一些表项：
13 | bob = Person.create(name='Bob', age=60)
herb = Person.create(name='Herb', age=30)
bob_kitty = Pet.create(owner=bob, name='Kitty', animal_type='cat')
herb_fido = Pet.create(owner=herb, name='Fido', animal_type='dog')
herb_mittens = Pet.create(owner=herb, name='Mittens', animal_type='cat')
sqlite> select * from person;
1|Bob|60
2|Herb|30
sqlite> select * from pet;
1|1|Kitty|cat
2|2|Fido|dog
3|2|Mittens|cat
通过 sqlite 可以查询到每个 pet 的 owner 一项都对应它主人的 id 。
6 | query = Pet.select().where(Pet.animal_type == 'cat')
for pet in query:
print(pet.name, pet.owner.name)
Kitty Bob
Mittens Herb
18.3.2. SQLAlchemy
SQLAlchemy 是另一个著名的 Python ORM 模块，专为高效率和高性能的数据库访问设计的，代码比较复杂。Python 另一著名的轻量级 Web 框架 Flask 就基于 SQLAlchemy 实现了 flask-sqlaichemy ORM 模型。
SQLAlchemy 的一大特点在于所有的数据库操作通过一个数据库 session 进行，在该session中控制每个对象的生命周期 。
18.3.2.1. sqlalchemy数据类型
sqlalchemy 常用类型 和 Python 数据类型对照表：
18.3.2.2. 数据库操作
相对于 peewee，SQLAlchemy 操作数据库需要创建一个进行数据库操作的 session，它被称为工作单元，一个数据库对应一个 session，如果要进行多个数据库交互，就要创建多个 session，它实现了数据库之间的隔离。
34 | from sqlalchemy import create_engine,Column,String,Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
# 创建对象的基类:
Base = declarative_base()
# 定义Person对象
class Person(Base):
__tablename__ = 'person'
# 表结构
id = Column(Integer, primary_key=True , autoincrement=True)
name = Column(String(20))
age = Column(Integer)
# 初始化数据库连接:
engine = create_engine('sqlite:///person.db')
# 创建session对象:
session = sessionmaker(bind=engine)()
# 创建表结构
Base.metadata.create_all(engine)
# 将实例对象添加到 session
session.add(Person(name='Grandma', age=60))
session.add(Person(name='Grandpa', age=62))
# 提交到数据库
session.commit()
# 关闭 session
session.close()
这里我们指定自动生成 id，通过 sqlite 查看插入的数据：
2 | sqlite> select * from person;
1|Grandma|60
2|Grandpa|62
|
