61_sqlalchemy基本使用

目录​

​​1、declare a mapping： 2​​​

​​2、connecting： 2​​​

​​3、create a schema： 3​​​

​​4、creating a session： 4​​​

​​5、create an instance of the mapped class： 4​​​

​​6、adding and updating： 6​​​

SQLAlchemy：​

是一个ORM框架；​

大量使用元编程；​

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

​​https://www.sqlalchemy.org/​​​

​​https://docs.sqlalchemy.org/en/latest/​​ #Read this first​

​​https://docs.sqlalchemy.org/en/latest/orm/tutorial.html​​​

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')​

​​https://docs.sqlalchemy.org/en/latest/core/engines.html​​​

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数据库中存在​