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.