python - 将 SQLAlchemy 设置为使用 PostgreSQL SERIAL 来生成身份

Question

背景:

我目前正在开发的应用程序正在从 SQLite3 过渡到 PostgreSQL。所有数据已成功迁移，使用当前数据库中的.dump，更改该类型的所有表

CREATE TABLE foo (
    id INTEGER NOT NULL, 
    bar INTEGER,
    ...
    PRIMARY KEY (id),
    FOREIGN KEY(bar) REFERENCES foobar (id),
    ...
);

到

CREATE TABLE foo (
    id SERIAL NOT NULL, 
    bar INTEGER,
    ...
    PRIMARY KEY (id),
    FOREIGN KEY(bar) REFERENCES foobar (id) DEFERRABLE,
    ...
);

和SET CONSTRAINTS ALL DEFERRED;。

自从我使用 SQLAlchemy 以来，我希望从那时起事情能顺利进行，当然是在更改引擎之后。但问题似乎出在 INSERT 上主键自动递增到唯一值。

表格，比如 foo，我目前有 7500 多行的问题，但序列 foo_id_seq 的 当前值 已设置在 5 上(因为我已经尝试了五次插入，但都失败了)。

问题:

所以现在我的问题是，如果没有在 INSERT 语句中显式提供 id，我怎样才能让 Postgres 自动为 id 分配一个唯一值 字段如果 foo?或者更具体地说，序列是否为其返回一个唯一值？

糖:

通过 SQLAlchemy 界面实现所有这些。

环境细节:

• python 2.6
• SQLAlchemy 8.2
• PostgreSQL 9.2
• psycopg2 - 2.5.1 (dt dec pq3 ext)

PS:如果有人为这个问题找到更合适的标题，请编辑它。

Answer 1

您的 PRIMARY KEY 应该被定义为使用 SEQUENCE 作为 DEFAULT，或者通过 SERIAL 方便伪类型:

CREATE TABLE blah (
    id serial primary key,
    ...
);

或显式SEQUENCE:

CREATE SEQUENCE blah_id_seq;

CREATE TABLE blah (
    id integer primary key default nextval('blah_id_seq'),
    ...
);

ALTER SEQUENCE blah_id_seq OWNED BY blah.id;

这是 discussed in the SQLAlchemy documentation .

您可以将其添加到现有表中:

CREATE SEQUENCE blah_id_seq OWNED BY blah.id;

ALTER TABLE blah ALTER COLUMN id SET DEFAULT nextval('blah_id_seq');

如果您更喜欢恢复转储，则手动添加序列。

如果您使用COPY 或类似方法将现有数据直接加载到表中，则需要设置序列起点:

SELECT setval('blah_id_seq', max(id)+1) FROM blah;

我想说这个问题可能与您在 SQLite 中进行开发有关，然后进行转储并将该转储恢复到 PostgreSQL。 SQLAlchemy 期望使用适当的默认值和序列自行创建模式。

我建议您改为让 SQLAlchemy 创建一个新的空数据库。将每个表的数据从 SQLite 数据库转储到 CSV，然后将该数据COPY 到 PostgreSQL 表中。最后，使用 setval 更新序列，以便它们生成适当的值。

不管怎样，您将需要确保创建适当的序列。您可以通过SERIAL 伪列类型，或通过手动SEQUENCE 创建和DEFAULT 设置来完成，但您必须这样做。否则，无法以高效、并发安全的方式将生成的 ID 分配给表。