【postgresql】数据表id自增与python sqlachemy结合实例

红太狼 2024-04-22 07:22 64阅读 0赞

需求：  
postgresql实现一个建表语句，表名：student,字段id,name,age，  
要求：每次添加一个数据id会自动增加1

在PostgreSQL中，您可以使用SERIAL或BIGSERIAL数据类型来自动生成主键ID。以下是一个创建名为student的表的SQL语句，该表包含id、name和age字段，其中id字段会自动递增：  
sql

CREATE TABLE student (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        age INT NOT NULL
    );

在这个建表语句中：

*  id字段被设置为SERIAL类型，这意味着每当您向表中插入新行时，id字段的值会自动增加1。  
    PRIMARY KEY约束表示id字段是表的主键，它在表中是唯一的，并且不能为NULL。
 *  name字段被设置为VARCHAR(255)类型，这是一个可变长度的字符串，最大长度为255个字符。
 *  age字段被设置为INT类型，用于存储整数值。

请注意，SERIAL类型是一个整数类型，它会在插入新行时自动增加，但它的值有上限。如果您需要一个更大的数字范围，可以使用BIGSERIAL类型，它与SERIAL类型类似，但可以存储更大的数字。  
要向student表中插入数据，您可以使用以下INSERT语句：

INSERT INTO student (name, age) VALUES ('John Doe', 20);
    INSERT INTO student (name, age) VALUES ('Tom', 20);

在这个INSERT语句中，您不需要指定id字段，因为它会自动生成。  
![在这里插入图片描述][2140d9d52f3d4f28b57a00983b7e02a7.png]

### ORM操作 ###

# -- coding:utf-8 --
    from sqlalchemy import Column, Integer, String, create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker
    
    Base = declarative_base()
    
    class User(Base):
        __tablename__ = 'student'
        id = Column(Integer, primary_key=True, autoincrement=True)
        name = Column(String(50), nullable=False)
        age = Column(Integer, nullable=False)
    
    # 创建引擎和会话
    engine = create_engine('postgresql://admin:admin@192.168.1.1/post')
    Session = sessionmaker(bind=engine)
    session = Session()
    
    # 创建一个新用户
    new_user = User(name='johndoe', age=18)
    session.add(new_user)
    session.commit()

数据插入成功：  
![在这里插入图片描述][89a7b15a9ac1466a8c355a08ae91f34d.png]

### SERIAL数据类型 ###

如果您的表已经创建，但使用的是`INT`类型作为主键，并且您想要修改它成为一个自动递增的`SERIAL`类型，您可以使用`ALTER TABLE`语句来修改列的数据类型并将其设置为主键。以下是一个例子，展示了如何将现有表`users`中的`id`列修改为`SERIAL`类型：

ALTER TABLE users
    MODIFY id SERIAL PRIMARY KEY;

这个命令会改变`users`表中`id`列的数据类型为`SERIAL`，并且将其设置为表的主键。如果`id`列已经包含数据，这个操作会自动为现有的每个`id`值分配一个唯一的序列值。  
如果您想要保留`id`列的现有数据并仅为新插入的行生成序列值，您可以使用`ALTER TABLE ... ALTER COLUMN ... RESTART`命令：

ALTER TABLE users
    ALTER COLUMN id RESTART WITH 1;

这个命令会将`id`序列重新启动，设置下一个插入的行的`id`值为1。这将不会影响表中已经存在的行。  
请注意，这些命令应该在数据库服务器上直接执行，而不是通过应用程序。如果您正在使用SQLAlchemy或其他ORM框架，您可能需要先迁移现有的数据，然后再修改表结构。

案例：  
如果之前建表语句中id 用的int类型，怎么修改成SERIAL类型：  
如果您的表已经创建，但使用的是`INT`类型作为主键，并且您想要修改它成为一个自动递增的`SERIAL`类型，您可以使用`ALTER TABLE`语句来修改列的数据类型并将其设置为主键。以下是一个例子，展示了如何将现有表`users`中的`id`列修改为`SERIAL`类型：

ALTER TABLE users
    MODIFY id SERIAL PRIMARY KEY;

ALTER TABLE users
    ALTER COLUMN id RESTART WITH 1;

### SERIAL 与BIGSERIAL的区别 ###

`SERIAL`和`BIGSERIAL`是PostgreSQL中用于自动递增整数键的数据类型，它们之间的主要区别在于可以存储的数值范围：

1.  `SERIAL`：它是`INTEGER`类型的一个别名，用于自动递增的整数。`SERIAL`类型的取值范围与`INTEGER`类型相同，理论上限是2^31 - 1（2147483647），但实际上由于系统限制，可能会有所不同。
2.  `BIGSERIAL`：它是`BIGINT`类型的一个别名，用于自动递增的更大范围的整数。`BIGSERIAL`类型的取值范围与`BIGINT`类型相同，理论上限是2^63 - 1（9223372036854775807）。  
    在选择`SERIAL`和`BIGSERIAL`时，您应该根据您的应用程序所需的ID范围来决定。如果您预计ID值会非常大，或者有可能达到`INTEGER`类型的上限，那么应该使用`BIGSERIAL`。如果您确信ID值会在`INTEGER`类型的范围内，那么`SERIAL`将是更高效的选择，因为它在存储和处理上消耗的资源更少。  
    例如，如果您正在构建一个可能会有数百万或更多用户的大规模应用程序，并且用户ID需要有很大的范围，那么使用`BIGSERIAL`会是更合适的选择。如果您构建的是一个小型应用程序，用户数量有限，那么`SERIAL`可能就足够了。  
    在创建表时，您可以这样定义这两个类型：

CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        -- ...
    );
    CREATE TABLE large_users (
        id BIGSERIAL PRIMARY KEY,
        -- ...
    );

在SQLAlchemy中，您可以这样指定这些类型：

from sqlalchemy import Column, Integer, BigInteger
    class SmallUser(Base):
        __tablename__ = 'users'
        id = Column(Integer, primary_key=True)
        # ...
    class LargeUser(Base):
        __tablename__ = 'large_users'
        id = Column(BigInteger, primary_key=True)
        # ...

在这个例子中，`SmallUser`类使用`Integer`类型，而`LargeUser`类使用`BigInteger`类型，后者可以存储更大的数值。在SQLAlchemy中，`BigInteger`类型对应于PostgreSQL中的`BIGINT`类型。

[2140d9d52f3d4f28b57a00983b7e02a7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/2a9b5376373547bb82b39900a4eae00c.png
[89a7b15a9ac1466a8c355a08ae91f34d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/bdc613b41cdf41c3935a5096e06a7903.png