SQL Alchemy，如何将数据插入两个表并引用外键? [英] SQL Alchemy, how to insert data into two tables and reference foreign key?

问题描述

我正在使用SQL Alchemy(通过Flask_sqlalchemy)将python词典列表插入Postgres数据库.

I am inserting a list of python dictionaries into a Postgres database using SQL Alchemy (via Flask_sqlalchemy).

其中一个表是所有唯一项的列表(表1)，而第二个表是与某项相关的数据时间序列(表2).

One of the tables is a list of all unique items (table 1), while the second is a time series of data related to an item (table2).

本质上，我想在表1中插入任何新行(具有唯一的哈希)，然后将其数据插入表2中.如果表1中已经存在该行，只需在表2中插入引用表1中的条目.

In essence, I want to insert any new row (with unique hash) in to table 1, then insert it's data to table 2. If it already exists in table 1, just insert the "child" in table 2 referencing the entry in table 1.

这是列表中的一项，列表中有几百项.

This is one item in the list, the list has a few hundred of these.

{'placement': '5662448s608653114', 't1': datetime.datetime(2018, 4, 15, 17, 47, 7, 434982), 't2': datetime.datetime(2018, 4, 25, 17, 47, 7, 434994), 'camp_id': 1, 'clicks': '0', 'visits': '3', 'conversions': '0', 'revenue': '0'}

我想将5662448s608653114插入到 table1 中，然后将所有其他数据插入到 table2 中，在这里我不是通过5662448s608653114引用该项目，而是通过它的ID表1

I would like to insert 5662448s608653114 into table1, and then insert all the other data into table2, where i reference the item not by 5662448s608653114, but by it's id in table 1

所以我会得到:

表1:

____________________
1| 5662448s608653114
2| 5520103

表2:

ID | Pl id | T1 | T2 | cost | revenue | clicks
_______________________________________________
499| 1     |
500| 2     |

我对此进行了测试，但无效:

I tested this, which does not work:

    def write_tracker_data(self):

    for item in self.data:
        ts = Placements(placement_ts_hash=item["placement"])
        pl = TrackerPlacementData(placement_id=ts.id, t1=item["t1"], t2=item["t2"], camp_id=1,  revenue=item["revenue"], clicks=item["clicks"], conversions=item["conversions"])
        db.session.add(pl)

    db.session.commit()

上面的代码插入了数据，但是没有数据，而是插入了表1中的id.它似乎也不是很有效，您知道什么时候可以做某些更好的事情……

The above code inserts the data, but with none instead of the id from the Table 1. It also doesn't seem very efficient, you know that feeling when something can definitely be done a better way ...

以下是可供参考的模型类:

Here's the model classes for reference:

class Placements(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    traffic_source = db.Column(db.Integer, db.ForeignKey('ts_types.id'))
    placement_ts_hash = db.Column(db.String, index=True)
    placement_url = db.Column(db.String)
    placement_type = db.Column(db.String)

    # Relationship betwwen unique placement table and tracker_placeemnt_data
    tracker_data = db.relationship("TrackerPlacementData", backref="placement_hash")

class TrackerPlacementData(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    t1 = db.Column(db.DateTime(timezone=True))
    t2 = db.Column(db.DateTime(timezone=True), index=True)
    camp_id = db.Column(db.Integer, db.ForeignKey('campaigns.id'), nullable=False)
    placement_id = db.Column(db.Integer, db.ForeignKey('placements.id'), nullable=True, index=True)
    revenue = db.Column(db.Float)
    clicks = db.Column(db.Integer)
    conversions = db.Column(db.Integer)

谢谢.

这可行，但是由于循环中每个项目都有一个新的会话，所以它看起来不是很好::/

This works, but it doesn't seem very good due to a new session for every item in the loop :/

def write_tracker_data(self):

def write_tracker_data(self):

for item in self.data:
    ts = Placements(placement_ts_hash=item["placement"])
    db.session.add(ts)
    db.session.commit()

    pl = TrackerPlacementData(placement_hash=ts, t1=item["t1"], t2=item["t2"], camp_id=1,
                              revenue=item["revenue"], clicks=item["clicks"], conversions=item["conversions"])
    db.session.add(pl)

    db.session.commit()

推荐答案

您的 Placement 实例在提交之前没有ID.这是 tracker_data 关系可以帮助您的地方...

Your Placement instance won't have an id until it is committed. This is where the tracker_data relationship can help you...

for item in self.data:
    ts = Placements(placement_ts_hash=item["placement"])
    pl = TrackerPlacementData(
        t1=item["t1"], 
        t2=item["t2"], 
        camp_id=1, 
        revenue=item["revenue"], 
        clicks=item["clicks"], 
        conversions=item["conversions"]
    )
    ts.tracker_data.append(pl)
    db.session.add(ts)
db.session.commit()

请注意， pl.placement_id 未设置为任何内容.取而代之的是，将 pl 附加到 ts.tracker_data 上，并且在调用commit时应该为您提供一切照顾.

Notice that pl.placement_id is not set to anything. Instead pl is appended to ts.tracker_data and everything should be looked after for you when you call commit.

这篇关于SQL Alchemy，如何将数据插入两个表并引用外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！