Insert dictionary into SQLlite3
<p>I want to insert data from a dictionary into a sqlite table, I am using slqalchemy to do that, the keys in the dictionary and the column names are the same, and I want to insert the values into the same column name in the table. So this is my code:</p>

<pre><code>#This is the class where I create a table from with sqlalchemy, and I want to
#insert my data into.
#I didn't write the __init__ for simplicity
class Sizecurve(Base):
     __tablename__ = 'sizecurve'
     XS = Column(String(5))
     S = Column(String(5))
     M = Column(String(5))
     L = Column(String(5))
     XL = Column(String(5))
     XXL = Column(String(5))

o = Mapping()   #This creates an object which is actually a dictionary
for eachitem in myitems:
    # Here I populate the dictionary with keys from another list
    # This gives me a dictionary looking like this: o={'S':None, 'M':None, 'L':None}
    o[eachitem] = None
for eachsize in mysizes:
    # Here I assign values to each key of the dictionary, if a value exists if not just None
    # product_row is a class and size and stock are its attributes
    if(product_row.size in o):
        o[product_row.size] = product_row.stock
    # I put the final object into a list
        simplelist.append(o)
</code></pre>

<p>Now I want to put each the values from the dictionaries saved in simplelist into the right column in the sizecurve table. But I am stuck I don't know how to do that? So for example I have an object like this:</p>

<pre><code>o= {'S':4, 'M':2, 'L':1}
</code></pre>

<p>And I want to see for the row for column S value 4, column M value 2 etc.</p>

<p>Yes, it's possible (though aren't you missing primary keys/foreign keys on this table?).</p>

<pre><code>session.add(Sizecurve(**o))
session.commit()
</code></pre>

<p>That should insert the row.</p>

<p><a href="http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements" rel="nofollow">http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements</a></p>

<p>EDIT: On second read it seems like you are trying to insert all those values into one column? If so, I would make use of pickle. </p>

<p><a href="https://docs.python.org/3.5/library/pickle.html" rel="nofollow">https://docs.python.org/3.5/library/pickle.html</a></p>

<p>If performance is an issue (pickle is pretty fast, but if your doing 10000 reads per second it'll be the bottleneck), you should either redesign the table or use a database like PostgreSQL that supports JSON objects.</p>

python sqlite dictionary sqlalchemy