psycopg2插入嵌套词典列表中的多个表格

Question

因此，我有嵌套词典的列表，这些字典从我想获取值并将其插入我的PostgreSQL 14数据库中的表中。我正在与Psycopg2一起使用Python。

列表看起来像这样...

嵌套词典列表

[
 {
  "trainNumber": 1,
  "departureDate": "2020-12-08",
  "cancelled": false,
  "trainType": "IC",
  "0": {
   "type": "DEPARTURE",
   "stationShortCode": "HKI",
   "scheduledTime": "2020-12-08T04:57:00.000Z"
  },
  "1": {
   "type": "ARRIVAL",
   "stationShortCode": "PSL",
   "scheduledTime": "2020-12-08T05:02:00.000Z"
  },
  "2": {
   "type": "DEPARTURE",
   "stationShortCode": "PSL",
   "scheduledTime": "2020-12-08T05:03:00.000Z"
  },
{
  "trainNumber": 2,
  "departureDate": "2020-12-08",
  "cancelled": false,
  "trainType": "S",
  "0": {
   "type": "DEPARTURE",
   "stationShortCode": "JNS",
   "scheduledTime": "2020-12-08T03:12:00.000Z"
  },
  "1": {
   "type": "ARRIVAL",
   "stationShortCode": "PLT",
   "scheduledTime": "2020-12-08T03:12:34.000Z"
  },
  "2": {
   "type": "DEPARTURE",
   "stationShortCode": "PLT",
   "scheduledTime": "2020-12-08T03:12:34.000Z"
  },

psycopg2 的查询

insert_query = WITH test AS (
    INSERT INTO trains_data.trains (train_number, train_type)
    VALUES (%(trainNumber)s, %(trainType)s)
    RETURNING id
), test2 as (
    INSERT INTO trains_data.train_routes (train_id, date)
    VALUES ( (SELECT id FROM test), %(departureDate)s)
    RETURNING id
), test3 as (
    INSERT INTO trains_data.train_timetables (route_id, scheduled_time, station_id, route_type, cancelled)
    VALUES ( (SELECT id FROM test2), %(scheduledTime)s, (SELECT station_id FROM trains_data.train_stations WHERE short_name = %(stationShortCode)s), %(type)s, %(cancelled)s)
);

问题是，当我运行此查询时，我会得到keyError：'scheduledtime'> 因此，我的问题是：

1. 如何访问需要获得时间，类型和站点值值的嵌套词典，还是有可能？
2. 我应该考虑一些不同的方法来插入我的数据吗？
3. 我的SQL代码正确插入数据有些正确吗？

任何有点帮助都将不胜感激。