SQL 表达式语言 · ZetCode 中文系列教程

# SQL 表达式语言 > 原文： [http://zetcode.com/db/sqlalchemy/exprlang/](http://zetcode.com/db/sqlalchemy/exprlang/) 在 SQLAlchemy 教程的这一部分中，我们使用 SQLAlchemy 的 SQL 表达式语言。 SQLAlchemy 表达式语言使用 Python 构造表示关系数据库结构和表达式。表达式语言通过隐藏 SQL 语言来提高代码的可维护性，因此不允许混合使用 Python 代码和 SQL 代码。对象关系映射器（ORM）建立在表达式语言之上。 ## 选择所有行在第一个示例中，我们使用表达式语言从表中选择所有行。 `exp_select_all.py` ```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`表中检索所有行。 ```py meta = MetaData(eng) cars = Table('Cars', meta, autoload=True) ``` 我们加载`Cars`表的定义。 ```py stm = select([cars]) ``` 使用`select()`方法，我们创建了一条 SQL `SELECT`语句。该特定表达式从提供的表中选择所有列和行。 ```py rs = con.execute(stm) ``` 该语句被执行。 ```py print rs.fetchall() ``` 使用`fetchall()`方法，我们将打印所有返回的数据。 ```py $ ./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` ```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`表中打印三行的两列。 ```py stm = select([cars.c.Name, cars.c.Price]).limit(3) ``` 在方括号之间，我们提供了要显示的列。 `limit()`方法将结果集限制为三行。 ```py $ ./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` ```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 之间的所有汽车。 ```py stm = select([cars]).where(and_(cars.c.Price > 10000, cars.c.Price < 40000)) ``` 为了构建预期的 SQL 语句，我们使用`select()`和`where()`方法以及`and_()`运算符。 ```py $ ./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` ```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'结尾的所有汽车。 ```py stm = select([cars]).where(cars.c.Name.like('%en')) ``` `like()`方法应用于列名。 ```py $ ./exp_select_like.py [(6, u'Citroen', 21000), (8, u'Volkswagen', 21600)] ``` 有两辆汽车的名字以`"en"`结尾。 ## 排序行 `order_by()`方法将`ORDER BY`子句添加到`select()`方法生成的语句中。 `exp_select_order.py` ```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'] ``` 该示例打印按汽车名称排序的所有行。 ```py s = select([cars]).order_by(asc(cars.c.Name)) ``` `order_by()`方法具有`asc()`运算符，该运算符以升序方式进行排序。 ```py $ ./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` ```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_()`运算符指定的表的四行。 ```py stm = select([cars]).where(tuple_(cars.c.Id).in_(k)) ``` 借助`tuple_()`和`in_()`运算符，我们构建了包含`IN`子句的语句。 ```py $ ./exp_select_in.py 2 Mercedes 57127 4 Volvo 29000 6 Citroen 21000 8 Volkswagen 21600 ``` 这是示例的输出。 ## 建立表下一个示例使用表达式语言在内存中创建一个表。 `exp_create_table.py` ```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`语句。 ```py eng = create_engine('sqlite:///:memory:') ``` 创建的表将是 SQLite 的内存表。 ```py meta = MetaData(eng) cars = Table('Cars', meta, Column('Id', Integer, primary_key=True), Column('Name', String), Column('Price', Integer) ) ``` 我们提供表的定义。 ```py cars.create() ``` 该表是使用`create()`方法创建的。 ```py ins1 = cars.insert().values(Id=1, Name='Audi', Price=52642) con.execute(ins1) ``` 使用`insert()`方法，我们将新行插入表中。 ```py s = select([cars]) rs = con.execute(s) for row in rs: print row['Id'], row['Name'], row['Price'] ``` 在最后一步，我们执行`SELECT`语句并将所有返回的数据打印到控制台。 ```py $ ./exp_create_table.py 1 Audi 52642 2 Mercedes 57127 3 Skoda 6000 ``` 这是示例的输出。 ## 连接表在下面的示例中，我们连接了两个表中的字段。我们使用`join()`方法。 `exp_join_tables.py` ```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'] ``` 该示例在两个表上执行内连接。我们得到了作者及其相应的标题。 ```py authors = Table('Authors', meta, autoload=True) books = Table('Books', meta, autoload=True) ``` 这两个表是从数据库加载的。 ```py stm = select([authors.join(books)]) ``` 我们使用`JOIN`子句创建`SELECT`语句。 ```py $ ./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 表达式语言。