61数据库6_SQLAlchemy

最新推荐文章于 2024-01-25 23:02:34 发布

weixin_34255055

最新推荐文章于 2024-01-25 23:02:34 发布

阅读量249

点赞数

文章标签：数据库前端 python ViewUI

原文链接：http://blog.51cto.com/jowin/2394534

版权

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

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()

# 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

转载于:https://blog.51cto.com/jowin/2394534

weixin_34255055

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫