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
– PChemGuy, Commented Dec 4, 2022 at 9:24

Andrei Koval · Accepted Answer · 2022-12-19 23:52:41Z

0

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

answered Dec 19, 2022 at 23:52

Andrei Koval

31 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

OperationalError: near ")": syntax error in sqlite

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related