SQLALCHEMY的简单使用

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数据库字段中的映射关系

SQLAlchemyMYSQL
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'))

常用的筛选器

作用操作
equalsfilter(User.name == ‘ed’)
not equalsfilter(User.name != ‘ed’)
LIKEfilter(User.name.like(’%ed%’))
ILIKE(不区分大小写)filter(User.name.ilike(’%ed%’))
INfilter(User.name.in_([‘ed’, ‘wendy’, ‘jack’]))
NOT INfilter(~User.name.in_([‘ed’, ‘wendy’, ‘jack’]))
IS NULLfilter(User.name.is_(None))
ANDfilter(User.name == ‘ad’, User.nickname == “mark”)
ORfrom sqlalchemy import or_; filter(or_(User.name == ‘ed’, User.name == ‘wendy’))
MATCHfilter(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获取关联的数据。

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页