SQL 表达式语言

最后修改于 2020 年 7 月 6 日

在本 SQLAlchemy 教程中，我们使用 SQLAlchemy 的 SQL 表达式语言。

SQLAlchemy 表达式语言使用 Python 构造来表示关系数据库结构和表达式。表达式语言通过隐藏 SQL 语言来提高代码的可维护性，从而避免混合 Python 代码和 SQL 代码。

对象关系映射器 (ORM) 构建在表达式语言之上。

选择所有行

在第一个示例中，我们使用表达式语言从表中选择所有行。

exp_select_all.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    stm = select([cars])
    rs = con.execute(stm) 

    print rs.fetchall()

该示例使用 select 方法从 Cars 表中检索所有行。

meta = MetaData(eng)
cars = Table('Cars', meta, autoload=True)

我们加载 Cars 表的定义。

stm = select([cars])

使用 select 方法，我们创建一个 SQL SELECT 语句。这个特定的表达式从提供的表中选择所有列和行。

rs = con.execute(stm)

该语句被执行。

print rs.fetchall()

使用 fetchall 方法，我们打印所有返回的数据。

$ ./exp_select_all.py 
[(1, u'Audi', 52642), (2, u'Mercedes', 57127), (3, u'Skoda', 9000), (4, u'Volvo', 29000), 
(5, u'Bentley', 350000), (6, u'Citroen', 21000), (7, u'Hummer', 41400), (8, u'Volkswagen', 21600)]

这是示例的输出。

限制选定的输出

在第二个示例中，我们限制从表中检索的数据。

exp_select_limit.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    stm = select([cars.c.Name, cars.c.Price]).limit(3)
    rs = con.execute(stm) 

    print rs.fetchall()

该示例从 Cars 表中打印三行的两列。

stm = select([cars.c.Name, cars.c.Price]).limit(3)

在方括号之间，我们提供要显示的列。 limit 方法将结果集限制为三行。

$ ./exp_select_limit.py 
[(u'Audi', 52642), (u'Mercedes', 57127), (u'Skoda', 9000)]

这是 exp_select_limit.py 程序的输出。

where() 方法

where 方法将 WHERE 子句添加到 select 方法生成的语句中。

exp_select_where.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select, and_    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    stm = select([cars]).where(and_(cars.c.Price > 10000, 
                                  cars.c.Price < 40000))
    rs = con.execute(stm) 

    print rs.fetchall()

该示例选择价格在 10000 到 40000 之间的所有汽车。

stm = select([cars]).where(and_(cars.c.Price > 10000, 
                                cars.c.Price < 40000))

为了构建预期的 SQL 语句，我们使用 select 和 where 方法以及 and_ 运算符。

$ ./exp_select_where.py 
[(4, u'Volvo', 29000), (6, u'Citroen', 21000), (8, u'Volkswagen', 21600)]

代码示例打印价格在 10000 到 40000 之间的所有汽车。

like() 方法

like 方法将 LIKE 子句添加到 select 方法生成的语句中。

exp_select_like.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    stm = select([cars]).where(cars.c.Name.like('%en'))
    rs = con.execute(stm) 

    print rs.fetchall()

使用 like 方法，我们选择名称以“en”结尾的所有汽车。

stm = select([cars]).where(cars.c.Name.like('%en'))

like 方法应用于列名。

$ ./exp_select_like.py 
[(6, u'Citroen', 21000), (8, u'Volkswagen', 21600)]

有两辆汽车的名称以“en”结尾。

排序行

order_by 方法将 ORDER BY 子句添加到 select 方法生成的语句中。

exp_select_order.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select, asc
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    metadata = MetaData(eng)
    cars = Table('Cars', metadata, autoload=True)  

    s = select([cars]).order_by(asc(cars.c.Name))
    rs = con.execute(s) 

    for row in rs:
        print row['Id'], row['Name'], row['Price']

该示例打印所有按汽车名称排序的行。

s = select([cars]).order_by(asc(cars.c.Name))

order_by 方法被赋予 asc 运算符，该运算符使排序以升序进行。

$ ./exp_select_order.py
1 Audi 52642
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
2 Mercedes 57127
3 Skoda 9000
8 Volkswagen 21600
4 Volvo 29000

该示例打印所有行。行按汽车名称的升序排列。

in_() 运算符

in_ 运算符用于将 IN 子句添加到生成的 SELECT 语句中。

exp_select_in.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData, tuple_
from sqlalchemy.sql import select
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    k = [(2,), (4,), (6,), (8,)]
    stm = select([cars]).where(tuple_(cars.c.Id).in_(k))
    rs = con.execute(stm) 

    for row in rs:
        print row['Id'], row['Name'], row['Price']

该示例打印表中的四行，由 in_ 运算符指定。

stm = select([cars]).where(tuple_(cars.c.Id).in_(k))

在 tuple_ 和 in_ 运算符的帮助下，我们构建了包含 IN 子句的语句。

$ ./exp_select_in.py 
2 Mercedes 57127
4 Volvo 29000
6 Citroen 21000
8 Volkswagen 21600

这是示例的输出。

创建表

下一个示例使用表达式语言在内存中创建一个表。

exp_create_table.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import (create_engine, Table, Column, Integer, 
    String, MetaData)
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///:memory:')

with eng.connect() as con:
    
    meta = MetaData(eng)
    cars = Table('Cars', meta,
         Column('Id', Integer, primary_key=True),
         Column('Name', String),
         Column('Price', Integer)
    )

    cars.create()

    ins1 = cars.insert().values(Id=1, Name='Audi', Price=52642)
    con.execute(ins1)

    ins2 = cars.insert().values(Id=2, Name='Mercedes', Price=57127)
    con.execute(ins2)
    
    ins3 = cars.insert().values(Id=3, Name='Skoda', Price=6000)
    con.execute(ins3)    

    s = select([cars])
    rs = con.execute(s) 

    for row in rs:
        print row['Id'], row['Name'], row['Price']

该示例在内存中创建一个新表，用数据填充它，并在该表上执行一个 SELECT 语句。

eng = create_engine('sqlite:///:memory:')

创建的表将是 SQLite 的内存中表。

meta = MetaData(eng)
cars = Table('Cars', meta,
    Column('Id', Integer, primary_key=True),
    Column('Name', String),
    Column('Price', Integer)
)

我们提供表的定义。

cars.create()

使用 create 方法创建表。

ins1 = cars.insert().values(Id=1, Name='Audi', Price=52642)
con.execute(ins1)

使用 insert 方法，我们将新行插入表中。

s = select([cars])
rs = con.execute(s) 

for row in rs:
    print row['Id'], row['Name'], row['Price']

在最后一步，我们执行一个 SELECT 语句并将所有返回的数据打印到控制台。

$ ./exp_create_table.py 
1 Audi 52642
2 Mercedes 57127
3 Skoda 6000

这是示例的输出。

连接表

在下一个示例中，我们连接两个表中的字段。我们使用 join 方法。

exp_join_tables.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import (create_engine, Table, Column, Integer, 
    String, ForeignKey, MetaData)
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:
    
    meta = MetaData(eng)
    
    authors = Table('Authors', meta, autoload=True)
    books = Table('Books', meta, autoload=True)

    stm = select([authors.join(books)])
    rs = con.execute(stm) 

    for row in rs:
        print row['Name'], row['Title']

该示例在两个表上执行内连接。我们获取作者及其对应的标题。

authors = Table('Authors', meta, autoload=True)
books = Table('Books', meta, autoload=True)

这两个表是从数据库中加载的。

stm = select([authors.join(books)])

我们使用 JOIN 子句创建一个 SELECT 语句。

$ ./exp_join_tables.py 
Jane Austen Emma
Leo Tolstoy War and Peace
Joseph Heller Catch XII
Charles Dickens David Copperfield
Joseph Heller Good as Gold
Leo Tolstoy Anna Karenia

这是示例的输出。

在本 SQLAlchemy 教程中，我们使用了 SQL 表达式语言。

电子书

SQL 表达式语言

选择所有行

限制选定的输出

where() 方法

like() 方法

排序行

in_() 运算符

创建表

连接表