When trying to convert a list of python dictionaries into a SQLite table I keep getting an error?-CodePudding

I'm attempting to convert a list of python dictionaries into an SQLite table, using the code below.

conn = sqlite3.connect('stockData.db')
c = conn.cursor()
c.executemany(f"INSERT INTO AAPL1M2021 (time,open,high,low,close,volume) VALUES (%(t)s,%(o)s,%(h)s,%(l)s,%(c)s,%(v)s)", data)

Whenever I run this code it returns the error below.

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-7-dce4eb1e6f6f> in <module>
      2 conn = sqlite3.connect('stockData.db')
      3 c = conn.cursor()
----> 4 c.executemany("INSERT INTO AAPL1M2021 (time,open,high,low,close,volume) VALUES (%(t)s,%(o)s,%(h)s,%(l)s,%(c)s,%(v)s)", data)

OperationalError: near "%": syntax error

I must be missing something because I can't seem to find a solution.

Here is a sample dictionary from the list of dictionaries.

{'v': 26666,
  'vw': 133.233,
  'o': 133.31,
  'c': 133.49,
  'h': 133.49,
  'l': 133.02,
  't': 1609750800000,
  'n': 87}

CodePudding user response：

Python's sqlite package doesn't use %s style formatting. It uses ? for positional placeholders or :name for keyword placeholders.

So your query might look like

c.execute(
    "INSERT INTO AAPL1M2021 (time,open,high,low,close,volume) VALUES (:t, :o, :h, :l, :c, :v)", 
    data
)

Note that executemany is used for lists of arguments for executing mulitple queries. For a single dict use execute; use executemany for a list of dicts.