OperationalError: near ")": syntax error in sqlite

Question

Below is my code:

# export data to sqlite
    with sqlite3.connect('realtime_crypto.sqlite') as conn:
        df.to_sql('data', con=conn, if_exists='append', index=False)

Below is the error showing

OperationalError                          Traceback (most recent call last)
Input In [5], in <cell line: 218>()
    232 # export data to sqlite
    233 with sqlite3.connect('realtime_crypto.sqlite') as conn:
--> 234     df.to_sql('data', con=conn, if_exists='append', index=False)

OperationalError: near ")": syntax error

I tried changing the format still not working.

Why are you using sqlite3 connection context manager with pandas? Do you understand what it does and when it should be used? — PChemGuy, Dec 04 '22 at 09:24

score 0 · Answer 1 · answered Dec 19 '22 at 23:52

Most probably you have single quotes in your dataframe.

I would recommend to use pyathena.pandas.util.to_sql function so if the value contains single quotes, it will be automatically escaped:

import pandas as pd
from pyathena.pandas.util import to_sql

df = pd.DataFrame({"a": ["foo", "bar", "jim o'rourke"]})
to_sql(df, "test", conn)

How do I convert a string into safe SQL String?

https://github.com/laughingman7743/PyAthena/issues/238

OperationalError: near ")": syntax error in sqlite

1 Answers1