insert a list of Ordered Dictionaries to SQL table

Question

I have list of OrderedDicts the order of keys is correct, there is 72 keys in each dictionary,How can I write them to SQL table,

from collections import OrderedDict
import sqlite3
from sqlite3 import OperationalError
#code here
#my_list=list of OrderedDicts

conn = sqlite3.connect('test3.db')
cursor = conn.cursor()

I checked Insert a list of dictionaries into an SQL table using python,

cursor.executemany("""
    INSERT INTO 
        mytable
        (id, price, type)
    VALUES
        (%(key1)s, %(key2)s, %(key3)s, ...(key72))
""", lst) # there must be a more pythonic way of performing this.

What I want is way of writing my_list into table.

Answer 1

you can use pandas to write directly, refer to pandas.DataFrame.to_sql documentation.

import pandas
#your code
conn = sqlite3.connect('test3.db')
cursor = conn.cursor()
df=pandas.DataFrame(my_list) # list of dictionaries
df.to_sql("name_of_table",conn)
conn.close()