sqlite3.OperationalError: near ")": syntax error

[rhpierson4]

When I try to run cpi.update(), I get an error: 'sqlite3.OperationalError: near ")": syntax error'. I'm unsure how to update the underlying cpi tables. Even after I uninstalled and reinstalled, I only have data up until 2023-03-31.

[palewire]

Can you share your version of the program and the full traceback?

[rhpierson4]

Yes, I'm on cpi-1.0.18. Here's the full traceback:

Traceback (most recent call last):
  File "/Users/rp/Library/Caches/pypoetry/virtualenvs/practice-py3.10/lib/python3.10/site-packages/IPython/core/interactiveshell.py", line 3460, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-44-dc1e66f7ee2a>", line 1, in <module>
    cpi.update()
  File "/Users/rp/Library/Caches/pypoetry/virtualenvs/practice-py3.10/lib/python3.10/site-packages/cpi/__init__.py", line 163, in update
    Downloader().update()
  File "/Users/rp/Library/Caches/pypoetry/virtualenvs/practice-py3.10/lib/python3.10/site-packages/cpi/download.py", line 79, in update
    [self.insert_tsv(file) for file in self.FILE_LIST]
  File "/Users/rp/Library/Caches/pypoetry/virtualenvs/practice-py3.10/lib/python3.10/site-packages/cpi/download.py", line 79, in <listcomp>
    [self.insert_tsv(file) for file in self.FILE_LIST]
  File "/Users/rp/Library/Caches/pypoetry/virtualenvs/practice-py3.10/lib/python3.10/site-packages/cpi/download.py", line 97, in insert_tsv
    df.to_sql(file, conn, if_exists="replace", index=False)
  File "/Users/rp/Library/Caches/pypoetry/virtualenvs/practice-py3.10/lib/python3.10/site-packages/pandas/core/generic.py", line 2987, in to_sql
    return sql.to_sql(
  File "/Users/rp/Library/Caches/pypoetry/virtualenvs/practice-py3.10/lib/python3.10/site-packages/pandas/io/sql.py", line 695, in to_sql
    return pandas_sql.to_sql(
  File "/Users/rp/Library/Caches/pypoetry/virtualenvs/practice-py3.10/lib/python3.10/site-packages/pandas/io/sql.py", line 2187, in to_sql
    table.create()
  File "/Users/rp/Library/Caches/pypoetry/virtualenvs/practice-py3.10/lib/python3.10/site-packages/pandas/io/sql.py", line 838, in create
    self._execute_create()
  File "/Users/rp/Library/Caches/pypoetry/virtualenvs/practice-py3.10/lib/python3.10/site-packages/pandas/io/sql.py", line 1871, in _execute_create
    conn.execute(stmt)
sqlite3.OperationalError: near ")": syntax error

[palewire]

I just ran the process myself on the same version and didn't get the error. Here's what I see:

U6122976@TR-WT3HM659RF cpi % pipenv run python
Python 3.9.16 (main, Feb 18 2023, 06:08:01) 
[Clang 14.0.0 (clang-1400.0.29.202)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import cpi
/Users/U6122976/Code/cpi/cpi/__init__.py:42: StaleDataWarning: CPI data is out of date. To accurately inflate to today's dollars, you must run `cpi.update()`.
  warnings.warn(StaleDataWarning())
>>> cpi.update()
>>>

Any ideas what's up?

[palewire]

This doesn't directly address the issue you've raised but I did just update the package with fresh data and released it on PyPI. So you can also upgrade that way with pip if you are stuck.

[rhpierson4]

Thanks for checking. I'm using an M1 mac, could that be part of the problem?

[palewire]

Potentially. I am using the pandas utilities for writing to Sqlite. I wonder if there's some crossplatform bug there.

[palewire]

The sqlite3 database access has been refactored almost entirely. I'm going to assume it resolved whatever this bug was. If not, just shout and we can reopen and test with the trunk code.