跳转至

SQL 表达式语言

原文： http://zetcode.com/db/sqlalchemy/exprlang/

在 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 表达式语言。

回到顶部