python - peewee: ValueError('Missing value for %s.' % column.name) on insert_many bulk insert

Question

更新

@coleifer 指出了我的错误：

.insert_many(data)是正确的，.insert_many([data])不是。

原来的

我不确定为什么insert_many认为缺少值。的输出data向我表明价值就在那里。

data是一个字典列表。我对文档进行了解释，认为这是insert_many.

我们可以通过使用 insert_many() 获得另一个巨大的提升。此方法接受元组或字典列表，并在单个查询中插入多行：

下面的代码是我正在使用的。我已经进行了更改以尝试使用float(ticker['v'])，因此它与表的定义一致。我还尝试weight用虚拟填充列，int以便填充所有字段，尽管weight列有null约束。在任何一种情况下，我仍然遇到同样的错误。这些更改没有反映在下面的代码中，因为它们没有更改错误或解决问题。

代码

def ticker_stream(stream):
    print(f"stream:\n{stream}")
    """ upsert Ticker data from stream """
    data = []
    for ticker in stream:
        for symbol in vars.symbol_ids:
            if symbol['name'] == ticker['s']:
                symbol_id = symbol['id']
                data.append({'symbol_id': symbol_id, 'weighted_average_price': ticker['w'], 'total_traded_base_asset_volume': ticker['v'], 'total_traded_quote_asset_volume': ticker['q']})
    try:
        print(f"data:\n{data}")
        insert_query = (c_db.Tickers
                            .insert_many([data])
                            .on_conflict(
                                conflict_target = [ c_db.Tickers.symbol ],
                                preserve = [ c_db.Tickers.symbol ],
                                update = [data]
                            )
                            .execute()
                        )

    except PeeweeException as e:
        logging.exception(f"{e}")

桌子

In [1]: from playhouse.reflection import print_model

In [2]: print_model(c_db.Tickers)
tickers
  id AUTO PK
  symbol INT FK: Symbols.id
  weighted_average_price FLOAT
  total_traded_base_asset_volume FLOAT
  total_traded_quote_asset_volume FLOAT
  weight INT

index(es)
  symbol_id

In [3]:

输出

stream:
[{'e': '24hrTicker', 'E': 1633019418650, 's': 'IOTXUSDT', 'p': '0.00216', 'P': '3.703', 'w': '0.06190', 'c': '0.06049', 'Q': '111', 'o': '0.05833', 'h': '0.06525', 'l': '0.05651', 'v': '1066120847', 'q': '65989958.82000', 'O': 1632933000000, 'C': 1633019418644, 'F': 11822513, 'L': 12020549, 'n': 198035}, {'e': '24hrTicker', 'E': 1633019419094, 's': 'BTCUSDT', 'p': '1361.38', 'P': '3.278', 'w': '42677.07', 'c': '42898.31', 'Q': '0.001', 'o': '41536.93', 'h': '43822.85', 'l': '40887.00', 'v': '365499.423', 'q': '15598442675.21', 'O': 1632933000000, 'C': 1633019419087, 'F': 1489633272, 'L': 1493071304, 'n': 3438004}, {'e': '24hrTicker', 'E': 1633019419046, 's': 'ETHUSDT', 'p': '116.83', 'P': '4.101', 'w': '2941.85', 'c': '2965.69', 'Q': '0.543', 'o': '2848.86', 'h': '3049.30', 'l': '2780.00', 'v': '2191345.813', 'q': '6446606858.84', 'O': 1632933000000, 'C': 1633019419039, 'F': 1103292619, 'L': 1106186143, 'n': 2893515}, {'e': '24hrTicker', 'E': 1633019418296, 's': 'BCHUSDT', 'p': '8.51', 'P': '1.754', 'w': '491.10', 'c': '493.77', 'Q': '0.074', 'o': '485.26', 'h': '506.13', 'l': '471.14', 'v': '280920.032', 'q': '137960347.52', 'O': 1632933000000, 'C': 1633019418289, 'F': 237467951, 'L': 237775138, 'n': 307187}, ... # omitted for brevity ... ]
data:
[{'symbol_id': 25930, 'weighted_average_price': '42677.07', 'total_traded_base_asset_volume': '365499.423', 'total_traded_quote_asset_volume': '15598442675.21'}, {'symbol_id': 25931, 'weighted_average_price': '2941.85', 'total_traded_base_asset_volume': '2191345.813', 'total_traded_quote_asset_volume': '6446606858.84'}]
Exception in thread parser:
Traceback (most recent call last):
  File "/Users/j/Documents/Development/crypto/binance/binance.venv/lib/python3.8/site-packages/peewee.py", line 2673, in _generate_insert
    val = row[i]
IndexError: list index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Applications/Xcode.app/Contents/Developer/Library/Frameworks/Python3.framework/Versions/3.8/lib/python3.8/threading.py", line 932, in _bootstrap_inner
    self.run()
  File "/Applications/Xcode.app/Contents/Developer/Library/Frameworks/Python3.framework/Versions/3.8/lib/python3.8/threading.py", line 870, in run
    self._target(*self._args, **self._kwargs)
  File "/Users/j/Documents/Development/crypto/binance/c_subscribe.py", line 19, in print_fifo_stream_buffer_data
    parser(oldest_stream_data_from_stream_buffer)
  File "/Users/j/Documents/Development/crypto/binance/c_parse.py", line 20, in parser
    ticker_stream(stream)
  File "/Users/j/Documents/Development/crypto/binance/c_parse.py", line 167, in ticker_stream
    insert_query = (c_db.Tickers
  File "/Users/j/Documents/Development/crypto/binance/binance.venv/lib/python3.8/site-packages/peewee.py", line 1907, in inner
    return method(self, database, *args, **kwargs)
  File "/Users/j/Documents/Development/crypto/binance/binance.venv/lib/python3.8/site-packages/peewee.py", line 1978, in execute
    return self._execute(database)
  File "/Users/j/Documents/Development/crypto/binance/binance.venv/lib/python3.8/site-packages/peewee.py", line 2745, in _execute
    return super(Insert, self)._execute(database)
  File "/Users/j/Documents/Development/crypto/binance/binance.venv/lib/python3.8/site-packages/peewee.py", line 2474, in _execute
    cursor = database.execute(self)
  File "/Users/j/Documents/Development/crypto/binance/binance.venv/lib/python3.8/site-packages/peewee.py", line 3156, in execute
    sql, params = ctx.sql(query).query()
  File "/Users/j/Documents/Development/crypto/binance/binance.venv/lib/python3.8/site-packages/peewee.py", line 613, in sql
    return obj.__sql__(self)
  File "/Users/j/Documents/Development/crypto/binance/binance.venv/lib/python3.8/site-packages/peewee.py", line 2730, in __sql__
    self._generate_insert(self._insert, ctx)
  File "/Users/j/Documents/Development/crypto/binance/binance.venv/lib/python3.8/site-packages/peewee.py", line 2682, in _generate_insert
    raise ValueError('Missing value for %s.' % column.name)
ValueError: Missing value for total_traded_base_asset_volume.
In [1]:

标签： pythonpeewee