sqlalchemy
环境
Ubuntu 18.0.4
Python 3.6
pip
pipenv
快速安装 Docker
安装 docker
sudoapt update
sudoapt installdocker.io -y
解决 docker 命令执行权限问题
sudogroupadd docker
sudogpasswd -a ${USER} docker
sudoservice docker restart
newgrp - docker
国内镜像加速
curl -sSL https://get.daocloud.io/daotools/set_mirror.sh | sh -s http://f1361db2.m.daocloud.io
MySQL 8.0
下载镜像
docker pull mysql:8.0
创建容器
docker create --name=mysql8.0-sqlalchemy -p 33060:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.0
启动容器
docker start mysql8.0-sqlalchemy
进入容器
docker exec -it mysql8.0-sqlalchemy /bin/bash
创建数据库
create database sqlalchemy character set utf8mb4;
创建用户
create user 'sqlalchemy'@'%' identified with mysql_native_password by 'sqlalchemy';
用户分配权限
grant all on sqlalchemy.* to 'sqlalchemy'@'%' with grant option;
退出数据库
exit;
退出容器
exit
安装
初始化环境
新建目录 sqlalchemy
mkdirsqlalchemy
初始化环境
cdsqlalchemy
pipenv shell
安装 sqlalchemy
pipenv installsqlalchemy
查看安装版本
import sqlalchemy
print(sqlalchemy.__version__)
注: sqlalchemy 不能直接操作数据库, 可以需要通过 pymysql 操作数据库
安装 pymysql
pipenv installpymysql
使用
连接MySQL
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://sqlalchemy:sqlalchemy@127.0.0.1:33060/t1", max_overflow=5)
定义 User 模型
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(50))
通过模型创建数据库
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine("mysql+pymysql://sqlalchemy:sqlalchemy@127.0.0.1:33060/sqlalchemy", max_overflow=5)
Base.metadata.create_all(engine)
增删改查
session
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
Session = sessionmaker()
Session.configure(bind=engine) # once engine is available
提交事务
session.commit()
回滚事务
session.rollback()
添加数据
通过模型赋值
ed_user = User(name='ed')
添加数据
session.add(ed_user)
提交事务
session.commit()
添加多条数据
session.add_all([
User(name='wendy', fullname='Wendy Williams', nickname='windy'),
User(name='mary', fullname='Mary Contrary', nickname='mary'),
User(name='fred', fullname='Fred Flintstone', nickname='freddy')
])
查询数据
our_user = session.query(User).filter_by(name='ed').first()
查询条件
=
query.filter(User.name == 'ed')
!=
query.filter(User.name != 'ed')
LIKE
query.filter(User.name.like('%ed%'))
LIKE
区分大小写
query.filter(User.name.like('%ed%'))
不区分大小写
query.filter(User.name.ilike('%ed%'))
IN
query.filter(User.name.in_(['ed', 'wendy', 'jack']))
子查询
query.filter(User.name.in_(
session.query(User.name).filter(User.name.like('%ed%'))
))
NOT IN
query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
query.filter(User.name.notin_(['ed', 'wendy', 'jack']))
NULL
NULL
query.filter(User.name == None)
query.filter(User.name.is_(None))
NOT NULL
query.filter(User.name != None)
query.filter(User.name.isnot(None))
AND
from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
or
from sqlalchemy import or_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))
MATCH
query.filter(User.name.match('wendy'))
返回值
all
all() 返回 list
first
first() 返回 scalar
one
one
one()完全提取所有行,如果结果中不存在一个对象标识或复合行,则会引发错误。找到多行:
one()方法适用于希望处理“找不到任何项目”而不是“找到多个项目”的系统;例如RESTful Web服务,可能希望在找不到结果时引发“未找到404”,但在找到多个结果时引发应用程序错误。
one_or_none
one_or_none()就像one(),除非没有找到结果,它不会引发错误;它只返回无。但是,与one()类似,如果找到多个结果,它确实会引发错误。
scalar()调用one()方法,成功后返回行的第一列:
query = session.query(User.id).filter(User.name == 'ed').order_by(User.id)
query.scalar()
原生SQL
from sqlalchemy import text
session.query(User).filter(text("id<224")).order_by(text("id")).all():
数据绑定
可以使用冒号使用基于字符串的SQL指定绑定参数。要指定值,请使用params()方法:
session.query(User).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()
模型关联
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship("User", back_populates="addresses")
User.addresses = relationship("Address", order_by=Address.id, back_populates="user")
JOIN
query.join(Address, User.id==Address.user_id) # explicit condition
query.join(User.addresses) # specify relationship from left to right
query.join(Address, User.addresses) # same, with explicit target
query.join('addresses') # same, using a string
正如您所期望的那样,使用outerjoin()函数将相同的想法用于“外部”连接:
query.outerjoin(User.addresses) # LEFT OUTER JOIN
使用别名
>>> from sqlalchemy.orm import aliased
>>> adalias1 = aliased(Address)
>>> adalias2 = aliased(Address)
SQL>>> for username, email1, email2 in \
... session.query(User.name, adalias1.email_address, adalias2.email_address).\
... join(adalias1, User.addresses).\
... join(adalias2, User.addresses).\
... filter(adalias1.email_address=='jack@google.com').\
... filter(adalias2.email_address=='j25@yahoo.com'):
... print(username, email1, email2)
jack jack@google.com j25@yahoo.com