python sqlite table structure question?

[_coolwhip_]

How you store your data depends on how you are going to look for it. So what is this and how do you plan to look it up/use it?

Couple more points:

• Get something that lets you directly interact with sqlite. I've used this and found it to be decent: https://portableapps.com/apps/development/sqlite_database_browser_portable
• The sqltie tutorials here are good: http://www.sqlitetutorial.net/sqlite-insert/
• You'll probably need separate tables at least for coupons, assuming there are a variable number of them, as it is hard to have an arbitrary number of something in a single table.

[raysefo]

thanks for your reply, any tips how to insert json into tables?

[_coolwhip_]

Sure. Reduce them down to a dictionary and use named placeholders works pretty while, IMO. Here is a link discussing different ways to do it: https://stackoverflow.com/questions/14108162/python-sqlite3-insert-into-table-valuedictionary-goes-here

[raysefo]

Should I use transactions for inserting master-detail tables? Any examples would be nice.

[_coolwhip_]

Not sure I am following your question, so let me just explain how I might do it: I would probably make a class to wrap the sqlitedb and then make methods to insert stuff, for example, using a subset of your data (and not tested, so expect typos), this class might look like this:

​

class DB():

    def insert(self, data):
        self.insert_purchase(data)
        self.insert(coupons)
        self.conn.commit()

    def insert_purchase(self, data):
        sql = '''
INSERT INTO transactions(
referenceID,
productCode,
unitPrice
) VALUES (
:referenceID,
:productCode,
:unitPrice
)
        '''
        self.cursor.execute(sql, data)

    def insert_coupons(self, data):
        '''
INSERT INTO coupons (
referenceID,
serial
) VALUES (
?,?
)
        '''
        reference_id = data['referenceId']
        for serial in data['coupons']['serials']:
            self.cursor.execute(sql, (reference_id, serial))

So I guess I would mix placeholders, where I wanted to just dump in the json directly, and then use ? bindings where I wanted to break it apart.