本文介绍了SQLAlchemy的基本使用，包括创建数据库连接、映射关系、对象操作（添加、修改）、查询技巧（分组、过滤）、外键处理以及懒加载。示例涵盖了PostgreSQL、MySQL等数据库，详细解析了default与server_default的区别，并提供了查询多张表的方法。
SQLAlchemy
声明
写这篇文章的目的只是为了自己在遇到问题时方便查找，文中会有SqlAlchemy官方文档中的内容。
sqlalchemy是一个对象关系映射器， 提供了一种将用户定义的Python类与数据库表以及这些类（对象）的实例与相应表中的行关联起来的方法。ORM即Object - Relation - Mapping
创建连接
使用SQLAlchemy的第一步是创建一个数据库连接，这里使用的是mysql数据库
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://username:password@ip/dbname')
这个 create_engine() 函数生成 Engine 基于URL的对象。以下是这些URL RFC-1738 ，通常可以包括用户名、密码、主机名、数据库名称以及用于附加配置的可选关键字参数。在某些情况下，文件路径被接受，而在其他情况下，“数据源名称”将替换“主机”和“数据库”部分。数据库URL的典型形式是：
dialect+driver://username:password@host:port/database
PostgreSQL
# default
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')
# psycopg2
engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
# pg8000
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
MySQL
# default
engine = create_engine('mysql://scott:tiger@localhost/foo')
# mysqlclient (a maintained fork of MySQL-Python)
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
# PyMySQL
engine = create_engine('mysql+pymysql://scott:tiger@localhost/foo')
Oracle
engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')
Microsoft SQL
# pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')
# pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')
SQLite
sqlite使用python内置模块连接到基于文件的数据库 sqlite3 默认情况下。当sqlite连接到本地文件时，URL格式略有不同。URL的“文件”部分是数据库的文件名。对于相对文件路径，这需要三个斜杠：：
# Unix/Mac - 4 initial slashes in total
engine = create_engine('sqlite:////absolute/path/to/foo.db')
# Windows
engine = create_engine('sqlite:///C:\\path\\to\\foo.db')
# Windows alternative using raw string
engine = create_engine(r'sqlite:///C:\path\to\foo.db')
创建映射
所有的映射应该基于declarative_base,且需要声明__tablename__ 属性
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column, Integer, String
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
nickname = Column(String)
def __repr__(self):
return "<User(name='%s', fullname='%s', nickname='%s')>" % (self.name, self.fullname, self.nickname)
Column类中常用使用的属性
| 属性 | 作用 |
|---|---|
| default | 默认值，当未指定此列时自动使用的值 |
| nullable | 是否可为空 |
| primary_key | 是否为主键 |
| unique | 是否唯一 |
| autoincrement | 是否自增 |
| onupdate | 更新的时候执行的函数 |
| name | 该列在数据库中的名称 |
| Index | 是否在该字段建立索引 |
暂时只使用到了这么多
SQLAlchemy中的常用字段与mysql数据库字段中的映射关系
| SQLAlchemy | MYSQL |
|---|---|
| Integer | |
| String | |
| Text | |
| Boolean | |
| Date | |
| DateTime | |
| Float |
default与server_default的区别
default作用在操作层次，即不指定该字段的值时，ORM会自动填充该值，并不会影响实际数据库中该字段的属性。
server_default是直接作用在数据库字段。且该属性应该填的是一个函数
from sqlalchemy import text
from sqlalchemy import Column, TIMESTAMP
updated_time = Column(TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
开始使用
对数据库的操作是基于Session的。首先要创建一个Session对象,操作Session应该是相当于操作事务吧。
这里对Session不做深入介绍，会在另一篇文章介绍
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
添加一个对象
ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
session.add(ed_user)
这个时候，数据还未被添加到数据库中，因为我们所做的修改还没有提交。要想将所做的修改提交到数据库中，要使用commit()
session.commmit()
一次性添加多个对象可以使用add_all()
修改一个对象
首先要知道修改的对象是哪个，比如，将name为’ed’的用户的nickname修改为mark
ed_user = session.query(User).filter(User.name == 'ed').first()
ed_user.nickname="mark"
session.add(ed_user)
session.commit()
简单查询
有两种查询的方法，一种是使用filter,一种是使用filter_by，这两种有略微差异
session.query(User).filter(User.name == "ed")
session.query(User).filter_by(name="ed")
分组查询order_by
有时候我们会按照某一列分组进行查询
session.query(User).order_by(User.id)
and查询
在filter或filter_by中写多个筛选条件则代表and查询
session.query(User).filter(User.name == 'ad', User.nickname == "mark")
or查询
from sqlalchemy import or_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))
常用的筛选器
| 作用 | 操作 |
|---|---|
| equals | filter(User.name == ‘ed’) |
| not equals | filter(User.name != ‘ed’) |
| LIKE | filter(User.name.like(’%ed%’)) |
| ILIKE(不区分大小写) | filter(User.name.ilike(’%ed%’)) |
| IN | filter(User.name.in_([‘ed’, ‘wendy’, ‘jack’])) |
| NOT IN | filter(~User.name.in_([‘ed’, ‘wendy’, ‘jack’])) |
| IS NULL | filter(User.name.is_(None)) |
| AND | filter(User.name == ‘ad’, User.nickname == “mark”) |
| OR | from sqlalchemy import or_; filter(or_(User.name == ‘ed’, User.name == ‘wendy’)) |
| MATCH | filter(User.name.match(‘wendy’)) |
外键
sqlalchemy中，建立一个外键是使用relationship和ForeignKey
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")
relationshop中，要告诉orm，外键关联的是哪个model。样例中，关联的是User表，back_populates的作用是为了可以在User表中直接查询Address实例。
即可以用Address.user表示一个User实例，也可以使用User.addresses表示Address实例。也可以将back_populates换成backref,这个时候，只需要在User表中添加relationship字段。
同时查询两张表
可以直接使用连表查询
session.query(User, Address).filter(User.id==Address.user_id).filter(Address.email_address=='jack@google.com').all()
如果存在外键可以使用join查询
session.query(User).join(Address).filter(Address.email_address=="wika.jia@qq.com").first()
如果没有外键，也可以使用下面这种方式
session.query.join(Address, User.id==Address.user_id)
懒加载
在建立外键时，relationship允许我们传入lazy参数。
lazy="dynamic"时，在访问User.addresses时，获取的是一个查询对象，并不是直接可以获取关联的数据。
需要通过User.addresses.all()查询一次，才可以获取。
lazy="select"时，就可以直接使用User.addresses.email_address获取关联的数据。
扫一扫
921
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
