SQLAlchemy：

是一个ORM框架；

大量使用元编程；

编程时，先对象&关系映射，才能操作DB，已成为工业标准；

pip install sqlalchemy pymysql

pip show sqlalchemy

> import sqlalchemy

> sqlalchemy.__version__ #version check

开发中，一般都采用ORM框架，这样就可使用对象操作表了；

定义表映射的类，使用Column的描述器定义类属性，使用ForeignKey定义外键约束；

如果在一个对象中，想查看其它表对象的内容，就要使用relationship来定义关系；

是否使用FK？

支持，力挺派，能使数据保证完整性、一致性；

不支持，嫌弃派，开发难度增加，大量数据时影响插入、修改、删除的效率；

通常要在业务层保证数据一致性（事务）；

注：

账号密码授权，若为前端用户，仅用来查数据，用grant select即可，不要grant all；

UML，统一建模语言；

navicat mysql，右键库或表，转储SQL文件，结构和数据；若仅导出结构，导出前要删除相关表中数据；

oralce中没有自增，用到sequence，from sqlalchemy import Sequence

1、declare a mapping：

创建映射：

创建基类Base，便于实体类继承；

创建实体类，Student表；

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base() #基类，创建基类，一次性的

from sqlalchemy import Column, Integer, String

class Student(Base): #实体类，declare a mapping

__tablename__ = 'student' #指定表名，必须写，防止忘记对应的表

id = Column('id', Integer, primary_key=True, autoincrement=True)) #定义属性对应字段，第1参数是字段名，如果和属性名一致可省，如果和属性名不一致要指定；Column类指定对应的字段，必须指定，Column即上例的Field；此处'id'可省，Integer为type不能省

name = Column(String(64), nullable=False)

age = Column(Integer)

def __repr__(self):

return '<{} id:{} name:{} age:{}>'.format(self.__class__.__name__, self.id, self.name, self.age)

__str__ = __repr__

2、connecting：

数据库连接的事情，交给引擎；

echo=True，引擎是否打印执行的语句，调试时打开很方便；

mysqldb的连接：

mysql+mysqldb://<user>:<password>@<host>[:port]/<dbname>

engine = sqlalchemy.create_engine('mysql+mysqldb://root:rootqazwsx@10.113.129.2:3306/test1')

pymysql的连接：

mysql+pymysql://<username>:<password>@<host>:<port>/<dbname>[?<options>]，options为与DB连接相关的选项

engine = sqlalchemy.create_engine('mysql+pymysql://root:rootqazwsx@10.113.129.2:3306/test1')

engine-configuration：

注：

内部使用了连接池；

dialect，方言，sql差异；

from sqlalchemy import create_engine

host = '10.113.129.2'

port = 3306

user = 'root'

password = 'rootqazwsx'

database = 'test1'

conn_str = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(user, password, host, port, database)

# engine = create_engine('mysql+pymysql://root:rootqazwsx@10.113.129.2:3306/test1', echo=True) #

engine = create_engine(conn_str, echo=True) #引擎，管理连接池，connecting；echo=True，执行的语句是否打印，可在配置文件中全局设置，调试时打开

3、create a schema：

Base.metadata.drop_all(engine) #删除继承自Base的所有表

Base.metadata.create_all(engine) #create a schema，创建继承自Base的所有表；Base.metadata中有一张表记录着所有用Base创建的实体类（实体类继承自Base)，遍历所有实体类，将查到的定义信息填到创建表的语句中；engine的echo=True，打开，执行后会有建表语句；创建表，共用的功能，而子类上是个性化的功能

注：

生产很少这样创建表，都是系统上线时由脚本生成，如用navicat mysql在测试里右键库或表，转储SQL文件，再导入到生产里；

生产很少删除表，废弃都不能删除；

4、creating a session：

在一个会话中操作数据库，会话建立在连接上，连接被引擎管理；

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine) #方式一；返回类；另，autoflush=False，autocommit=False

session = Session()实例化，session.add()，session.add_all()，session.commit()，session.rollback()，session.query()，session.cursor，session.execute()执行原生sql

# Session = sessionmaker() #方式二

# session = Session(bind=engine)

注：

class sessionmaker(_SessionClassMethods):

def __init__(self, bind=None, class_=Session, autoflush=True,

autocommit=False,

expire_on_commit=True,

info=None, **kw):

5、create an instance of the mapped class：

例，增：

try:

stu1 = Student()

stu1.name = 'tom' #属性赋值

stu1.age = 20

# student.id = 100 #有自增字段和有默认值的可不加

# session.add(stu1)状态为pending

stu2 = Student(name='jerry', age=18) #构造的时候传入

session.add_all([stu1, stu2])状态为pending

# lst = []

# for i in range(10):

# stu = Student()

# stu.name = 'jessica' + str(i)

# stu.age = 20 + i

# lst.append(stu)

# session.add_all(lst)

session.commit()

except Exception as e:

print(e)

session.rollback()

finally:

pass

输出：

2018-10-10 17:04:18,319 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

2018-10-10 17:04:18,320 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,333 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()

2018-10-10 17:04:18,333 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,355 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'

2018-10-10 17:04:18,355 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,371 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1

2018-10-10 17:04:18,371 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,382 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1

2018-10-10 17:04:18,382 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,393 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1

2018-10-10 17:04:18,393 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,414 INFO sqlalchemy.engine.base.Engine DESCRIBE `student`

2018-10-10 17:04:18,414 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,430 INFO sqlalchemy.engine.base.Engine

DROP TABLE student

2018-10-10 17:04:18,430 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,447 INFO sqlalchemy.engine.base.Engine COMMIT

2018-10-10 17:04:18,468 INFO sqlalchemy.engine.base.Engine DESCRIBE `student`

2018-10-10 17:04:18,468 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,482 INFO sqlalchemy.engine.base.Engine ROLLBACK

2018-10-10 17:04:18,494 INFO sqlalchemy.engine.base.Engine

CREATE TABLE student (

id INTEGER NOT NULL AUTO_INCREMENT,

name VARCHAR(64) NOT NULL,

age INTEGER,

PRIMARY KEY (id)

)

2018-10-10 17:04:18,494 INFO sqlalchemy.engine.base.Engine {}

2018-10-10 17:04:18,537 INFO sqlalchemy.engine.base.Engine COMMIT

2018-10-10 17:04:18,562 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2018-10-10 17:04:18,563 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, age) VALUES (%(name)s, %(age)s)

2018-10-10 17:04:18,563 INFO sqlalchemy.engine.base.Engine {'age': 20, 'name': 'tom'}

2018-10-10 17:04:18,574 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, age) VALUES (%(name)s, %(age)s) #用了参数化查询

2018-10-10 17:04:18,574 INFO sqlalchemy.engine.base.Engine {'age': 18, 'name': 'jerry'}

2018-10-10 17:04:18,586 INFO sqlalchemy.engine.base.Engine COMMIT

6、adding and updating：

CRUD操作；

每一个实体都有一个状态属性_sa_instance_state，其类型是sqlalchemy.orm.state.InstanceState，可使用sqlalchemy.inspect(entity)函数查看状态；

常见的状态有：

transient（短暂的，路过的），实体类尚未加入到session中，同时并没有保存到数据库中；

pending（未决定的，行将发生的），transient的实体被add()到session中，状态切换为pending，但还未flush到DB中；

persistent（持久稳固的，坚持的，固执的），session中的实体对象对应着DB中的真实记录，pending状态在提交成功后变为persistent状态，或查询成功返回的实体也是persistent状态；

deleted（已删除的），实体被删除且已flush但未commit完成，事务提交成功了，实体变成detached，事务失败返回persistent状态；

detached（单独的，冷漠的，超然而客观的），删除成功的实体进入这个状态；

新建一个实体，状态是transient临时的；

一旦add()后，由transient-->pending；

成功commit()后，由pending-->persistent；

成功查询返回的实体对象，也是persistent；

persistent状态的实体，依然是persistent状态；

persistent状态的实体，删除后，已flush但没commit()，转为deleted，事务成功提交，转为detached，事务提交失败，转为persistent；

只有在persistent状态的实体，才能delete和update，即删除、修改操作，；

例，commit()后的增：

try:

stu1 = Student()

stu1.name = 'tom'

stu1.age = 20

# student.id = 100

print(stu1.id)

session.add(stu1)

# stu2 = Student(name='jerry', age=18)

# session.add_all([stu1, stu2])

session.commit()

print('~~~~~~~~~~~~~~~~~~~~~~~~~~~')

print('@@@@@@@', stu1.id)

stu1.age = 22 #session.commit()后再改，会先查询

session.add(stu1) #再次session.add()和session.commit()，由于id为PK且自增，无论有无stu1.age=22都会新增一条记录；若id不是自增，有stu1.age=22则是update一条记录

session.commit() #始终与状态有关，感知到stu1有变化才会提交，能否提交成功看stu1有无变化，有变化了才提交（id为autoincrement，此例只要执行就会新增一条记录）；stu1主键没有值，就是新增，主键有值，就是找到对应的记录修改

except Exception as e:

print(e)

session.rollback()

finally:

pass

输出：

……

2018-10-10 21:20:13,812 INFO sqlalchemy.engine.base.Engine {}

None

2018-10-10 21:20:13,839 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2018-10-10 21:20:13,840 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, age) VALUES (%(name)s, %(age)s)

2018-10-10 21:20:13,841 INFO sqlalchemy.engine.base.Engine {'age': 20, 'name': 'tom'}

2018-10-10 21:20:13,852 INFO sqlalchemy.engine.base.Engine COMMIT

~~~~~~~~~~~~~~~~~~~~~~~~~~~

2018-10-10 21:20:13,881 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2018-10-10 21:20:13,881 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age

FROM student

WHERE student.id = %(param_1)s

2018-10-10 21:20:13,881 INFO sqlalchemy.engine.base.Engine {'param_1': 16}

@@@@@@@ 16

2018-10-10 21:20:13,894 INFO sqlalchemy.engine.base.Engine UPDATE student SET age=%(age)s WHERE student.id = %(student_id)s

2018-10-10 21:20:13,894 INFO sqlalchemy.engine.base.Engine {'age': 22, 'student_id': 16}

2018-10-10 21:20:13,909 INFO sqlalchemy.engine.base.Engine COMMIT

例，简单查询：

try:

queryobj = session.query(Student).filter(Student.id==8) #query()方法将实体类传入，返回类对象（是可迭代对象，查看源码有__iter__()），这时候并不查询，迭代它就执行sql来查询数据库，封装数据到指定类的实例；get()方法使用主键查询，返回一条传入类的一个实例

# queryobj = session.query(Student) #无条件

for i in queryobj:

print('########', i)

except Exception as e:

print(e)

输出：

……

2018-10-10 22:42:32,931 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age

FROM student

WHERE student.id = %(id_1)s

2018-10-10 22:42:32,931 INFO sqlalchemy.engine.base.Engine {'id_1': 8}

######## <Student id:8 name:tom age:26>

例，改，错误示例：

try:

stu1 = Student()

stu1.id = 2 #这种不是改，而是是一个全新的stu1，如果该id已有，会PK冲突；正确改的做法，先查再改，得到PK才能改

stu1.name = 'jerry'

stu1.age = 28

session.add(stu1)

session.commit()

except Exception as e:

print(e)

session.rollback()

finally:

pass

输出：

2018-10-11 08:07:21,772 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2018-10-11 08:07:21,773 INFO sqlalchemy.engine.base.Engine INSERT INTO student (id, name, age) VALUES (%(id)s, %(name)s, %(age)s)

2018-10-11 08:07:21,773 INFO sqlalchemy.engine.base.Engine {'age': 28, 'id': 2, 'name': 'jerry'}

2018-10-11 08:07:21,785 INFO sqlalchemy.engine.base.Engine ROLLBACK

(pymysql.err.IntegrityError) (1062, "Duplicate entry '2' for key 'PRIMARY'") [SQL: 'INSERT INTO student (id, name, age) VALUES (%(id)s, %(name)s, %(age)s)'] [parameters: {'age': 28, 'id': 2, 'name': 'jerry'}]

例，改：

先查回来，修改后，再提交；

改不能改PK字段；

先SELECT再UPDATE；

try:

stu1 = session.query(Student).get(2)

print('$$$$$$$', stu1)

stu1.name = 'jowin'

stu1.age = 28

print('#######', stu1)

session.add(stu1)

session.commit()

except Exception as e:

print(e)

session.rollback()

finally:

pass

输出：

2018-10-11 08:39:56,595 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age

FROM student

WHERE student.id = %(param_1)s

2018-10-11 08:39:56,595 INFO sqlalchemy.engine.base.Engine {'param_1': 2}

$$$$$$$ <Student id:2 name:tom age:24>

####### <Student id:2 name:jowin age:28>

2018-10-11 08:39:56,607 INFO sqlalchemy.engine.base.Engine UPDATE student SET name=%(name)s, age=%(age)s WHERE student.id = %(student_id)s

2018-10-11 08:39:56,608 INFO sqlalchemy.engine.base.Engine {'age': 28, 'student_id': 2, 'name': 'jowin'}

2018-10-11 08:39:56,619 INFO sqlalchemy.engine.base.Engine COMMIT

例，删，错误示例：

try:

stu1 = Student(id=2, name='sam', age=26)

session.delete(stu1)

session.commit()

except Exception as e:

print(e)

session.rollback()

finally:

pass

输出：

Instance '<Student at 0xa59438>' is not persisted #未持久的异常

例，删：

正确做法，先查再删；

from sqlalchemy import inspect

try:

stu1 = session.query(Student).get(2)

session.delete(stu1)

print('$$$$$$$$$$$$', inspect(stu1))

session.commit()

print('##########', inspect(stu1))

except Exception as e:

print(e)

session.rollabck()

finally:

pass

输出：

2018-10-11 08:52:12,317 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age

FROM student

WHERE student.id = %(param_1)s

2018-10-11 08:52:12,318 INFO sqlalchemy.engine.base.Engine {'param_1': 2}

$$$$$$$$$$$$ <sqlalchemy.orm.state.InstanceState object at 0x000000000401B400>

2018-10-11 08:52:12,330 INFO sqlalchemy.engine.base.Engine DELETE FROM student WHERE student.id = %(id)s

2018-10-11 08:52:12,330 INFO sqlalchemy.engine.base.Engine {'id': 2}

2018-10-11 08:52:12,342 INFO sqlalchemy.engine.base.Engine COMMIT

########## <sqlalchemy.orm.state.InstanceState object at 0x000000000401B400>

例，删：

from sqlalchemy import inspect

def show(entity):

ins = inspect(entity)

print('~~~~~~~~~~~~~~~', ins.transient, ins.pending, ins.persistent, ins.detached)

try:

# print('~~~~~~~~~~~~~', Student.__dict__)

stu1 = session.query(Student).get(4)

session.delete(stu1)

# ins = inspect(stu1)

# print('$$$$$$$$$$$$', ins)

show(stu1)

session.commit()

# ins = inspect(stu1)

# print('##########', ins)

show(stu1)

except Exception as e:

print(e)

session.rollabck()

finally:

pass

输出：

018-10-11 14:40:28,111 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age

FROM student

WHERE student.id = %(param_1)s

2018-10-11 14:40:28,111 INFO sqlalchemy.engine.base.Engine {'param_1': 4}

~~~~~~~~~~~~~~~ False False True False

2018-10-11 14:40:28,126 INFO sqlalchemy.engine.base.Engine DELETE FROM student WHERE student.id = %(id)s

2018-10-11 14:40:28,126 INFO sqlalchemy.engine.base.Engine {'id': 4}

2018-10-11 14:40:28,152 INFO sqlalchemy.engine.base.Engine COMMIT

~~~~~~~~~~~~~~~ False False False True

总结：

config.py

USERNAME = 'blog'

PASSWD = 'blog'

IP = '10.10.103.8'

PORT = '3306'

DBNAME = 'blog'

PARAMS = 'charset=utf8mb4'

URL = 'mysql+pymysql://{}:{}@{}:{}/{}?{}'.format(USERNAME, PASSWD, IP, PORT, DBNAME, PARAMS)

DB_DEBUG = True

models.py

from . import config

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, BigInteger, DateTime

from sqlalchemy import ForeignKey, UniqueConstraint, PrimaryKeyConstraint

from sqlalchemy.orm import relationship, sessionmaker

from sqlalchemy.dialects.mysql import LONGTEXT, TINYINT

Base = declarative_base()

class User(Base):创建表

__tablename__ = 'user'

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(48), nullable=False)

password = Column(String(128), nullable=False)

email = Column(String(64), nullable=False, unique=True)

def __repr__(self):

return '<User (id={}, name={}, email={})>'.format(self.id, self.name, self.email)

engine = create_engine(config.URL, echo=config.DB_DEBUG)

def create_all():

Base.metadata.create_all(engine)一旦使用该方法将模型映射到数据库后，即使改变了模型的字段，也不会重新映射了

def drop_all():

Base.metadata.drop_all(engine)

Session = sessionmaker(bind=engine)

session = Session()使用orm对DB操作必须通过session对象实现

注：

conn = engine.connect() #调用引擎的connect()得到一个对象

result = conn.execute('select version()') #通过conn对象就可对DB进行操作

print(result.fetchone())

Column常用属性：

default: 默认值

nullable: 是否可空

primary_key: 是否为主键

unique: 是否唯一

autoincrement: 是否自增长

name: 该属性再数据库中的字段映射

onupdate: 当数据更新时会自动使用这个属性，比如update_time = Colum(DateTime, notallow=datetime.now, default=datetime.now)

常用数据类型：

Integer: 整型

Float: 浮点型，后面只会保留4位小数，会有精度丢失问题，占据32位

Double: 双精度浮点类型，占据64位，也会存在精度丢失问题

DECIMAL: 定点类型，解决浮点类型精度丢失问题；如果精度要求高，比如金钱，则适合用此类型

Boolean: 传递True/False进行

enum: 枚举类型

Date: 传递datetime.date()进去

Datetime: 传递datetime.datetme()进去

Time: 传递datetime.time()进去

String: 字符类型，使用时需要指定长度，区别于Text类型

Text: 文本类型，一般可以存储6w多个字符

LONGTEXT: 长文本类型

from sqlalchemy.dialects.mysql import LONGTEXT

因为LONGTEXT只在MySQL数据库中存在