flask 怎么在db.session.add后获取自增id

Question

@v1.route('/users/register', methods=('POST',))
def users_register():
    form = RegisterForm()

    if not form.validate_on_submit():
        return formate_validate_error(form.errors)

    try:
        #
        user = User()
        user.name = form.name.data
        user.password = password_encode(form.password.data)
        user.email = form.email.data
        db.session.add(user)

        print(user.id) ## 获取到的是None 这个ID要在这个事物里使用的。

        #login_result = login_user(user)

        db.session.commit()
        # return return_success({
        #     'token': login_result['token']
        # })
        return 'e'
    except Exception as e:
        db.session.rollback()
        return return_err(str(e), status='exception')

执行记录：

[2018-07-24 10:32:39 +0800] [43241] [DEBUG] POST /v1/users/register
2018-07-24 10:32:39,622 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-07-24 10:32:39,624 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT users.id AS users_id, users.name AS users_name, users.email AS users_email, users.mobile AS users_mobile, users.password AS users_password, users._2fa AS users__2fa, users.created_at AS users_created_at 
FROM users 
WHERE lower(users.name) = lower(%(lower_1)s)) AS anon_1
2018-07-24 10:32:39,624 INFO sqlalchemy.engine.base.Engine {'lower_1': 'test1'}
2018-07-24 10:32:39,632 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT users.id AS users_id, users.name AS users_name, users.email AS users_email, users.mobile AS users_mobile, users.password AS users_password, users._2fa AS users__2fa, users.created_at AS users_created_at 
FROM users 
WHERE lower(users.email) = lower(%(lower_1)s)) AS anon_1
2018-07-24 10:32:39,633 INFO sqlalchemy.engine.base.Engine {'lower_1': '[e-mail]'}
None
2018-07-24 10:32:39,744 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, email, password, _2fa, created_at) VALUES (%(name)s, %(email)s, %(password)s, %(_2fa)s, %(created_at)s) RETURNING users.id
2018-07-24 10:32:39,744 INFO sqlalchemy.engine.base.Engine {'name': 'test1', 'email': '[e-mail]', 'password': '$2b$10$SDhD7crcXoT6Gq/bA6WN2.6cvn3GUyPJxUWa1W37wpy99n1pgoiNW', '_2fa': None, 'created_at': '2018-07-24 02:32:39+00:00'}
2018-07-24 10:32:39,745 INFO sqlalchemy.engine.base.Engine ROLLBACK

获取到个异常：

(psycopg2.IntegrityError) duplicate key value violates unique constraint \"idx_users_email\"\nDETAIL:  Key (lower('email'::text))=(email) already exists.\n [SQL: 'INSERT INTO users (name, email, password, _2fa, created_at) VALUES (%(name)s, %(email)s, %(password)s, %(_2fa)s, %(created_at)s) RETURNING users.id'] [parameters: {'name': 'test1', 'email': '[e-mail]', 'password': '$2b$10$SDhD7crcXoT6Gq/bA6WN2.6cvn3GUyPJxUWa1W37wpy99n1pgoiNW', '_2fa': None, 'created_at': '2018-07-24 02:32:39+00:00'}] (Background on this error at: http://sqlalche.me/e/gkpj)

# models
class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.BigInteger, primary_key=True)
    name = db.Column(db.VARCHAR(50), nullable=False)
    email = db.Column(db.VARCHAR(200), nullable=False)
    # 用户手机 默认为NULL
    mobile = db.Column(db.VARCHAR(20), server_default=text('NULL'))

    # 登录密码
    password = db.Column(db.VARCHAR, nullable=False)

    # 两步验证秘钥
    _2fa = db.Column(db.VARCHAR(32), nullable=True)

    # 注册时间
    created_at = db.Column(db.TIMESTAMP(False), nullable=False, server_default=text('now()'))

    __table_args__ = (
        Index('idx_users_name', func.lower('name'), unique=True),
        Index('idx_users_email', func.lower('email'), unique=True)
    )

    def __init__(self):
        if not self.created_at:
            self.created_at = now().format()

    def __repr__(self):
        return '<User %s>' % self.name

但是数据库里确实没有name和email重复的

Answer 1

在网上搜到的，可以看看https://www.crifan.com/sqlalc...

在commit提交之前db.session.flush()
再打印对象的id可以获得新增的ID
choice = Choice(name=name, right=right)

    db.session.add(choice)
    db.session.flush()
    print("choice: ", choice.id)