SQLAlchemy介绍
SQLAlchemy 的一个目标是提供能兼容众多数据库(如 SQLite、MySQL、Postgres、Oracle、MS-SQL、SQLServer 和 Firebird)的企业级持久性模型。
目前正处于积极开发阶段,已建立了丰富的社区。针对 SQLAlchemy 的扩展和插件包括:declarative、Migrate、Elixir、SQLSoup、django-sqlalchemy、DBSprockets、FormAlchemy 和 z3c.sqlalchemy
该框架建立在 DB API之上,使用关系对象映射进行数据库操作,简言之便是:将类和对象转换成SQL,然后使用数据API执行SQL并获取执行结果。
结构如下
Engine,框架的引擎
Connection Pooling ,数据库连接池
Dialect,选择连接数据库的DB API种类
Schema/Types,架构和类型
SQL Exprression Language,SQL表达式语言
简单增删改查
import uuid
import datetime
import sqlalchemy
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class TDog(Base):
__tablename__ = "dog"
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
name = sqlalchemy.Column(sqlalchemy.String(256), nullable=True)
uuid = sqlalchemy.Column(sqlalchemy.String(64), unique=True)
addtime = sqlalchemy.Column(sqlalchemy.DateTime())
data = sqlalchemy.Column(sqlalchemy.Text(), default='{}')
def __init__(self, name, uuid, data='{}', addtime=None):
self.name = name
self.uuid = uuid
self.data = data
self.addtime = addtime if None != addtime else datetime.datetime.now()
url = 'postgresql://dbuser:dbpassword@127.0.0.1:5432/zoodb'
engine = sqlalchemy.create_engine(url)
metadata = sqlalchemy.schema.MetaData(bind=engine)
Base.metadata.create_all(engine) # 创建表结构
Session = orm.sessionmaker(bind=engine)
def admq():
sess = Session()
# 增加一只狗
uuidstr = str(uuid.uuid4())
adog = TDog("doge", uuidstr)
sess.add(adog)
sess.commit()
# 根据 uuid 字段,查询这只狗
qdog = sess.query(TDog).filter(TDog.uuid == uuidstr).first()
print("查询结果:", qdog.name, qdog.uuid, qdog.addtime)
# 根据 uuid 字段修改这只狗
data = {"name": "newdoge", "addtime": datetime.datetime.now()}
sess.query(TDog).filter(TDog.uuid == uuidstr).update(data)
sess.commit()
# 查询一下修改后的结果
qdog = sess.query(TDog).filter(TDog.uuid == uuidstr).first()
print("修改后的查询结果:", qdog.name, qdog.uuid, qdog.addtime)
# 根据 uuid 字段删除这只狗
sess.query(TDog).filter(TDog.uuid == uuidstr).delete()
sess.commit()
# 查询一下修改后的结果
qdog = sess.query(TDog).filter(TDog.uuid == uuidstr).first()
if None == qdog:
print("删除后:dog with uuid [{0}] is not exists".format(uuidstr))
else:
print("删除后:", qdog.name, qdog.uuid, qdog.addtime)
if "__main__" ==__name__:
admq()
1、带条件查询
def get_session(db_url: str, debug: bool = False):
engine = sqlalchemy.create_engine(db_url, echo=debug)
return sessionmaker(engine)
session = get_session()
session.query(User).filter(User.username=='xx').all()
session.query(User).filter(User.username=='xx').first()
#结果为一个时正常,多了就报错
session.query(User).filter(User.username=='xx').one()
#通过id查询
session.query(User).get(2)
2、多表关联查询
def get_session(db_url: str, debug: bool = False):
engine = sqlalchemy.create_engine(db_url, echo=debug)
return sessionmaker(engine)
session = get_session()
#cross join
session.query(UserDetails,User).all()
#加上了where条件
session.query(UserDetails,User).filter(User.id==UserDetails.id).all()
#inner join
session.query(User.username,UserDetails.lost_login).join(UserDetails,UserDetails.id==User.id).all()
#左连接,sqlalchemy没有右连接
session.query(User.username,UserDetails.lost_login).outerjoin(UserDetails,UserDetails.id==User.id).all() )
#union关联
q1 = session.query(User.id)
q2 = session.query(UserDetails.id)
print(q1.union(q2).all())
3、原生SQL查询
sql_1 = """
select * from `user`
"""
row = session.execute(sql_1)
print(row,dir(row))
print( row.fetchone() )
print( row.fetchmany() )
print( row.fetchall() )