python - 用 pandas(dataframe) 中的另一数据覆盖一个数据

Question

定期(每 120 秒)获取数据，但最近的数据会覆盖 SQL DB 中以前的数据。我想要保存所有数据。另外，计时器是否正确？

import sqlalchemy as sa
import psycopg2
import requests as rq
import pandas as pd
import json
import time

start_time = time.time()
while True:
    temp = pd.DataFrame()
    df = pd.DataFrame()
    vehicleList = {"SN63NBK", "YY67UTP"}
    for ids in vehicleList:
        r = rq.get('https://api.tfl.gov.uk/Vehicle/' + ids + '/Arrivals')

        r = r.text
        temp = pd.read_json(r)
        temp['Type'] = 'ids'
        df = pd.concat([df, temp], sort=False).reset_index(drop=True)

    engine = sa.create_engine('postgresql+psycopg2://postgres:3434@127.0.0.1/postgres')
    df['timing'] = list(map(lambda x: json.dumps(x), df['timing']))
    df.to_sql('tfl_bus_pg6', engine, if_exists='replace', index=False)
time.sleep(120.0 - ((time.time() - start_time) % 120.0))

Answer 1

我稍微更改了您的代码，但我认为主要问题在于 if_exists 参数，您应该将其设置为 append，正如 @K753 在评论中提到的那样。

此外，YY67UTP id 不会返回任何内容，因此我将其替换为站点中的另一个随机 id，以说明代码的工作原理。

def _data_gen(vehicles):
    """ Yields a dataframe for each request """
    for ids in vehicles:
        time.sleep(1)
        r = rq.get('https://api.tfl.gov.uk/Vehicle/' + ids + '/Arrivals')
        temp = pd.read_json(r.text)
        temp['Type'] = ids
        yield temp

while True:
    # how do you break from while loop if you need to?
    vehicleList = {"SN63NBK", "YY67UTP"}
    df = pd.concat(_data_gen(vehicleList), sort=False, ignore_index=True)

    engine = sa.create_engine('postgresql+psycopg2://postgres:3434@127.0.0.1/postgres')
    df['timing'] = list(map(lambda x: json.dumps(x), df['timing']))
    df.to_sql('tfl_bus_pg6', engine, if_exists='append', index=False)
    time.sleep(120)