python alchemy_爱分享/python-sqlalchemy

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