Sqlalchemy 简明教程

SQLAlchemy Core - Using Multiple Tables

RDBMS 的重要特性之一是在表之间建立关系。SQL 运算（例如 SELECT、UPDATE 和 DELETE）可以在相关表上执行。本节使用 SQLAlchemy 描述了这些运算。

One of the important features of RDBMS is establishing relation between tables. SQL operations like SELECT, UPDATE and DELETE can be performed on related tables. This section describes these operations using SQLAlchemy.

为此，在我们的 SQLite 数据库 (college.db) 中创建两个表。学生表具有与前一节中给出的相同的结构；而地址表具有使用外键约束映射到 id column in students table 的 st_id 列。

For this purpose, two tables are created in our SQLite database (college.db). The students table has the same structure as given in the previous section; whereas the addresses table has st_id column which is mapped to id column in students table using foreign key constraint.

以下代码将在 college.db 中创建两个表：

The following code will create two tables in college.db −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('sqlite:///college.db', echo=True)
meta = MetaData()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

addresses = Table(
   'addresses', meta,
   Column('id', Integer, primary_key = True),
   Column('st_id', Integer, ForeignKey('students.id')),
   Column('postal_add', String),
   Column('email_add', String))

meta.create_all(engine)

上面的代码将转换为下面显示的学生和地址表的 CREATE TABLE 查询：

Above code will translate to CREATE TABLE queries for students and addresses table as below −

CREATE TABLE students (
   id INTEGER NOT NULL,
   name VARCHAR,
   lastname VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE addresses (
   id INTEGER NOT NULL,
   st_id INTEGER,
   postal_add VARCHAR,
   email_add VARCHAR,
   PRIMARY KEY (id),
   FOREIGN KEY(st_id) REFERENCES students (id)
)

以下屏幕截图清晰地展示了上述代码：

The following screenshots present the above code very clearly −

create table queries
addresses table queries

通过执行表对象 insert() method 填充表中的数据。要在学生表中插入 5 行，可以使用下面给出的代码：

These tables are populated with data by executing insert() method of table objects. To insert 5 rows in students table, you can use the code given below −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

conn = engine.connect()
students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

conn.execute(students.insert(), [
   {'name':'Ravi', 'lastname':'Kapoor'},
   {'name':'Rajiv', 'lastname' : 'Khanna'},
   {'name':'Komal','lastname' : 'Bhandari'},
   {'name':'Abdul','lastname' : 'Sattar'},
   {'name':'Priya','lastname' : 'Rajhans'},
])

Rows 在地址表中使用以下代码添加：

Rows are added in addresses table with the help of the following code −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

addresses = Table(
   'addresses', meta,
   Column('id', Integer, primary_key = True),
   Column('st_id', Integer),
   Column('postal_add', String),
   Column('email_add', String)
)

conn.execute(addresses.insert(), [
   {'st_id':1, 'postal_add':'Shivajinagar Pune', 'email_add':'ravi@gmail.com'},
   {'st_id':1, 'postal_add':'ChurchGate Mumbai', 'email_add':'kapoor@gmail.com'},
   {'st_id':3, 'postal_add':'Jubilee Hills Hyderabad', 'email_add':'komal@gmail.com'},
   {'st_id':5, 'postal_add':'MG Road Bangaluru', 'email_add':'as@yahoo.com'},
   {'st_id':2, 'postal_add':'Cannought Place new Delhi', 'email_add':'admin@khanna.com'},
])

请注意，地址表中的 st_id 列引用学生表中的 id 列。我们现在可以使用此关系从这两个表中提取数据。我们希望从与地址表中 st_id 相对应的学生表中提取 name 和 lastname 。

Note that the st_id column in addresses table refers to id column in students table. We can now use this relation to fetch data from both the tables. We want to fetch name and lastname from students table corresponding to st_id in the addresses table.

from sqlalchemy.sql import select
s = select([students, addresses]).where(students.c.id == addresses.c.st_id)
result = conn.execute(s)

for row in result:
   print (row)

选择对象将有效地转换成以下 SQL 表达式，该表达式在公共关系上联接两个表：

The select objects will effectively translate into following SQL expression joining two tables on common relation −

SELECT students.id,
   students.name,
   students.lastname,
   addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM students, addresses
WHERE students.id = addresses.st_id

这将产生从两个表中提取相应数据的输出，如下所示：

This will produce output extracting corresponding data from both tables as follows −

(1, 'Ravi', 'Kapoor', 1, 1, 'Shivajinagar Pune', 'ravi@gmail.com')
(1, 'Ravi', 'Kapoor', 2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')
(3, 'Komal', 'Bhandari', 3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')
(5, 'Priya', 'Rajhans', 4, 5, 'MG Road Bangaluru', 'as@yahoo.com')
(2, 'Rajiv', 'Khanna', 5, 2, 'Cannought Place new Delhi', 'admin@khanna.com')