Sqlalchemy 简明教程

SQLAlchemy Core - Creating Table

现在，让我们讨论如何使用创建表函数。

Let us now discuss how to use the create table function.

SQL 表达式语言针对表列构建其表达式。SQLAlchemy Column 对象表示数据库表中的 column ，数据库表反过来表示为 Tableobject 。Metadata 包含表定义和关联的对象，例如索引、视图、触发器等。

The SQL Expression Language constructs its expressions against table columns. SQLAlchemy Column object represents a column in a database table which is in turn represented by a Tableobject. Metadata contains definitions of tables and associated objects such as index, view, triggers, etc.

因此，SQLAlchemy Metadata 中 MetaData 类的对象是 Table 对象及它们关联的架构构造的集合。它包含一组 Table 对象，以及与引擎或连接可选绑定。

Hence an object of MetaData class from SQLAlchemy Metadata is a collection of Table objects and their associated schema constructs. It holds a collection of Table objects as well as an optional binding to an Engine or Connection.

from sqlalchemy import MetaData
meta = MetaData()

MetaData 类的构造函数可以具有默认情况下为 None 的 bind 和 schema 参数。

Constructor of MetaData class can have bind and schema parameters which are by default None.

接下来，我们在上述元数据目录中使用 the Table construct 定义所有表，该对象类似于常规的 SQL CREATE TABLE 语句。

Next, we define our tables all within above metadata catalog, using the Table construct, which resembles regular SQL CREATE TABLE statement.

Table 类的对象表示数据库中某个对应的表。它的构造函数接受以下参数：

An object of Table class represents corresponding table in a database. The constructor takes the following parameters −

Name

Name of the table

Metadata

MetaData object that will hold this table

Column(s)

One or more objects of column class

Column 对象表示 database table 中的 column 。构造函数接受名称、类型和其他参数，例如 primary_key、autoincrement 及其他约束。

Column object represents a column in a database table. Constructor takes name, type and other parameters such as primary_key, autoincrement and other constraints.

SQLAlchemy 将 Python 数据匹配到其中定义的最佳通用列数据类型。其中一些通用数据类型如下：

SQLAlchemy matches Python data to the best possible generic column data types defined in it. Some of the generic data types are −

  1. BigInteger

  2. Boolean

  3. Date

  4. DateTime

  5. Float

  6. Integer

  7. Numeric

  8. SmallInteger

  9. String

  10. Text

  11. Time

要在 college 数据库中创建一个 students table ，请使用以下代码段：

To create a students table in college database, use the following snippet −

from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()

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

create_all() 函数使用此引擎对象创建所有定义的表对象，并将信息存储在元数据中。

The create_all() function uses the engine object to create all the defined table objects and stores the information in metadata.

meta.create_all(engine)

下面给出了完成的代码，它将在其中创建一张 students 表，生成 SQLite 数据库 college.db。

Complete code is given below which will create a SQLite database college.db with a students table in it.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
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),
)
meta.create_all(engine)

由于 create_engine() 函数的 echo 属性设置为 True ，因此控制台将显示表的实际 SQL 查询，如下所示：

Because echo attribute of create_engine() function is set to True, the console will display the actual SQL query for table creation as follows −

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

college.db 将在当前工作目录中创建。要检查 students 表是否创建，你可以使用任何 SQLite GUI 工具（如 SQLiteStudio ）来打开该数据库。

The college.db will be created in current working directory. To check if the students table is created, you can open the database using any SQLite GUI tool such as SQLiteStudio.

下图显示了在数据库中创建的 students 表：

The below image shows the students table that is created in the database −

students table