插入时创建一条记录

Question

当在用户表中创建用户时，我如何将用户添加到钱包表中？我正在解释在用户表上使用事件侦听，但它似乎根本不起作用。

def User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String)
sponsor_referral_id = db.Column(db.String)

def Wallet(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String)
wallet_1 = db.Column(db.Float, default=0.0)
wallet_2 = db.Column(db.Float, default=0.0)

我试过这个：

@event.listens_for(User, "after_insert")
def create_wallet(mapper, connection, target):
target.session.add(Wallet(username=target.username))

当一个用户i被添加到user表中时，它没有产生任何结果。

我需要关于如何处理这件事的帮助。

Answer 1

也许我错了。。。但当您使用after_insert+session.add()时，结果可能不一致。我认为在你的情况下最好使用after_flush。因为flush()在一个事务中传递一系列操作(插入、更新和删除)。因此，您可以在flush之后但在commit之前添加一个新操作：

from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import event

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://...blablalba...'
app.config['SQLALCHEMY_ECHO'] = True  # let's check queries
db = SQLAlchemy(app)

class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String)
sponsor_referral_id = db.Column(db.String)

class Wallet(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String)
wallet_1 = db.Column(db.Float, default=0.0)
wallet_2 = db.Column(db.Float, default=0.0)

@event.listens_for(db.session, 'after_flush')
def example(session, instance):
# save new Wallet record if new User
for rec in session.new:
if isinstance(rec, User):
session.add(Wallet(username=rec.username))

@app.route('/')
def test():
# create a user and check for updates
db.session.add(User(username='test', sponsor_referral_id='test'))
db.session.commit()
user1 = User.query.first()
user1.username = 'new name'
db.session.commit()
# create one more user
db.session.add(User(username='test2', sponsor_referral_id='test2'))
db.session.commit()
return jsonify({'wallets': len(Wallet.query.all()), 'users': len(User.query.all())})

db.create_all()

运行服务器，打开http://localhost:5000/，让我们看看日志：

2021-07-16 17:48:33,992 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-07-16 17:48:33,994 INFO sqlalchemy.engine.Engine INSERT INTO "user" (username, sponsor_referral_id) VALUES (%(username)s, %(sponsor_referral_id)s) RETURNING "user".id
2021-07-16 17:48:33,994 INFO sqlalchemy.engine.Engine [generated in 0.00023s] {'username': 'test', 'sponsor_referral_id': 'test'}
2021-07-16 17:48:33,997 INFO sqlalchemy.engine.Engine INSERT INTO wallet (username, wallet_1, wallet_2) VALUES (%(username)s, %(wallet_1)s, %(wallet_2)s) RETURNING wallet.id
2021-07-16 17:48:33,998 INFO sqlalchemy.engine.Engine [generated in 0.00030s] {'username': 'test', 'wallet_1': 0.0, 'wallet_2': 0.0}
2021-07-16 17:48:33,999 INFO sqlalchemy.engine.Engine COMMIT

2021-07-16 17:48:34,004 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-07-16 17:48:34,006 INFO sqlalchemy.engine.Engine SELECT "user".id AS user_id, "user".username AS user_username, "user".sponsor_referral_id AS user_sponsor_referral_id 
FROM "user" 
LIMIT %(param_1)s
2021-07-16 17:48:34,007 INFO sqlalchemy.engine.Engine [generated in 0.00026s] {'param_1': 1}
2021-07-16 17:48:34,010 INFO sqlalchemy.engine.Engine UPDATE "user" SET username=%(username)s WHERE "user".id = %(user_id)s
2021-07-16 17:48:34,010 INFO sqlalchemy.engine.Engine [generated in 0.00020s] {'username': 'new name', 'user_id': 1}
2021-07-16 17:48:34,010 INFO sqlalchemy.engine.Engine COMMIT

2021-07-16 17:48:34,012 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-07-16 17:48:34,012 INFO sqlalchemy.engine.Engine INSERT INTO "user" (username, sponsor_referral_id) VALUES (%(username)s, %(sponsor_referral_id)s) RETURNING "user".id
2021-07-16 17:48:34,013 INFO sqlalchemy.engine.Engine [cached since 0.01892s ago] {'username': 'test2', 'sponsor_referral_id': 'test2'}
2021-07-16 17:48:34,014 INFO sqlalchemy.engine.Engine INSERT INTO wallet (username, wallet_1, wallet_2) VALUES (%(username)s, %(wallet_1)s, %(wallet_2)s) RETURNING wallet.id
2021-07-16 17:48:34,014 INFO sqlalchemy.engine.Engine [cached since 0.01685s ago] {'username': 'test2', 'wallet_1': 0.0, 'wallet_2': 0.0}
2021-07-16 17:48:34,015 INFO sqlalchemy.engine.Engine COMMIT

看起来像你需要的。