search

atviriduomenys / spinta

Spinta is a framework to describe, extract and publish data (a DEP Framework).
MIT License
10 stars 4 forks source link

Error with keymap sync #518

Closed sirex closed 9 months ago

sirex commented 9 months ago

When running spinta push command, I got following error:

╭─────────────────────────────── Traceback (most recent call last) ────────────────────────────────╮
│ spinta/cli/push.py:204 in push                                                                   │
│                                                                                                  │
│    201 │   │   │   │   synchronize = True                                                        │
│    202 │   │   │                                                                                 │
│    203 │   │   │   dependant_models = extract_dependant_nodes(models, not synchronize)           │
│ ❱  204 │   │   │   sync_keymap(                                                                  │
│    205 │   │   │   │   context=context,                                                          │
│    206 │   │   │   │   keymap=km,                                                                │
│    207 │   │   │   │   client=client,                                                            │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │            dataset = 'datasets/gov/vpt/sutartys'                                             │ │
│ │         first_time = True                                                                    │ │
│ │        incremental = False                                                                   │ │
│ │              limit = None                                                                    │ │
│ │               mode = <Mode.external: 'external'>                                             │ │
│ │    no_progress_bar = True                                                                    │ │
│ │             output = 'get.data.gov.lt'                                                       │ │
│ │               page = []                                                                      │ │
│ │         page_model = None                                                                    │ │
│ │        retry_count = 5                                                                       │ │
│ │      stop_on_error = False                                                                   │ │
│ │           stop_row = None                                                                    │ │
│ │          stop_time = None                                                                    │ │
│ │        synchronize = True                                                                    │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
│                                                                                                  │
│ spinta/datasets/keymaps/synchronize.py:74 in                                                     │
│ sync_keymap                                                                                      │
│                                                                                                  │
│    71 │   │   │   │   sync_cid = row['_cid']                                                     │
│    72 │   │   │                                                                                  │
│    73 │   │   │   for keymap_name in model_keymaps:                                              │
│ ❱  74 │   │   │   │   keymap.update_sync_data(keymap_name, sync_cid, datetime.datetime.now())    │
│    75 │   │   │                                                                                  │
│    76 │   │   │   if model.model_type() in counters:                                             │
│    77 │   │   │   │   for counter in counters[model.model_type()].values():                      │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │             row = {                                                                          │ │
│ │                   │   '_cid': 16190,                                                         │ │
│ │                   │   '_created': '2023-05-22T15:36:07.988145',                              │ │
│ │                   │   '_op': 'insert',                                                       │ │
│ │                   │   '_id': '387cc596-b883-4497-a67f-393247861b4d',                         │ │
│ │                   │   '_txn': '97c8f95a-32b0-4898-a7d7-dedb6ccfd9c3',                        │ │
│ │                   │   '_revision': '4d2820e1-ca45-4553-b01c-dd4164031ded',                   │ │
│ │                   │   'dok_id': 2002897085,                                                  │ │
│ │                   │   'dok_atask_id': None,                                                  │ │
│ │                   │   'dok_formos_tipas': 1,                                                 │ │
│ │                   │   'dok_reg_data': None,                                                  │ │
│ │                   │   ... +23                                                                │ │
│ │                   }                                                                          │ │
│ │          server = 'https://get.data.gov.lt'                                                  │ │
│ │     status_code = 200                                                                        │ │
│ │        sync_cid = 16190                                                                      │ │
│ │             url = 'https://get.data.gov.lt/datasets/gov/vpt/sutartys/VptNSutartys/:changes'  │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
│                                                                                                  │
│ spinta/datasets/keymaps/sqlalchemy.py:119 in                                                     │
│ update_sync_data                                                                                 │
│                                                                                                  │
│   116 │   │   │   index_elements=[table.c.model],                                                │
│   117 │   │   │   set_=dict(cid=cid, updated=time)                                               │
│   118 │   │   )                                                                                  │
│ ❱ 119 │   │   self.conn.execute(query)                                                           │
│   120 │                                                                                          │
│   121 │   def synchronize(self, name: str, value: Any, primary_key: str):                        │
│   122 │   │   table = self.get_table(name)                                                       │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │   cid = 16190                                                                                │ │
│ │  name = 'datasets/gov/vpt/sutartys/VptNSutartys'                                             │ │
│ │ table = Table('_synchronize', ...)                                                           │ │
│ │  time = datetime.datetime(2023, 10, 4, 6, 11, 34, 740914)                                    │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
╰──────────────────────────────────────────────────────────────────────────────────────────────────╯
OperationalError: (sqlite3.OperationalError) near "ON": syntax error
SQL:
    INSERT INTO _synchronize (model, cid, updated)
    VALUES (?, ?, ?)
    ON CONFLICT (model)
    DO UPDATE SET cid = ?, updated = ?
parameters:
    'datasets/gov/vpt/sutartys/VptNSutartys',
    16190,
    '2023-10-04 06:11:34.740914',
    16190,
    '2023-10-04 06:11:34.740914'

I think, this might be related to SQLite version:

$ python -c "import sqlite3; print(sqlite3.version)"
2.6.0

Most servers run very old SQLite version and we don't have any requirements, for SQLite version. Maybe it is possible to use less fancy SQL query?

SQLite documentation says:

UPSERT syntax was added to SQLite with version 3.24.0 (2018-06-04).

Since this is not a first time, when we get errors because of old SQLite version, it would be nice, to add 2.6.0 SQLite version to the CI, so that we would not miss such errors next time.

Another option, would be to use pysqlite-binary, which comes with a newer SQLite version:

https://github.com/pysqlite3/pysqlite3

Related