sqlalchemy如何使用

这篇文章主要介绍了sqlalchemy如何使用的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇sqlalchemy如何使用文章都会有所收获，下面我们一起来看看吧。

成都创新互联公司是由多位在大型网络公司、广告设计公司的优秀设计人员和策划人员组成的一个具有丰富经验的团队，其中包括网站策划、网页美工、网站程序员、网页设计师、平面广告设计师、网络营销人员及形象策划。承接：成都做网站、成都网站制作、成都外贸网站建设、网站改版、网页设计制作、网站建设与维护、网络推广、数据库开发,以高性价比制作企业网站、行业门户平台等全方位的服务。

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://:@[:port]/

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

pymysql的连接：

mysql+pymysql://:@:/[?]，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}

########

例，改，错误示例：

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}

$$$$$$$

#######

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 '' 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}

$$$$$$$$$$$$

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

##########

例，删：

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

关于“sqlalchemy如何使用”这篇文章的内容就介绍到这里，感谢各位的阅读！相信大家对“sqlalchemy如何使用”知识都有一定的了解，大家如果还想学习更多知识，欢迎关注创新互联行业资讯频道。