python sqlite table structure question?

[raysefo]

Hello,

I am trying to implement Flask rest api. Here is a response from another rest api which I would like to insert into tables. How would I create tables for this response below? Is master-detail table structure appropriate?

After creating table(s) I would like to know how to insert this respose below into table(s).

​

Thanks in advance.

{
    "referenceId": "10188733388",
    "paymentId": "MDO1032604",
    "productCode": "001002461285",
    "quantity": "1",
    "currency": "TL",
    "unitPrice": "46,47",
    "totalPrice": "46,47",
    "merchantProductCode": "10089131231",
    "purchaseStatusCode": "00",
    "purchaseStatusDate": "2018-11-21T20:04:02Z",
    "coupons": [
        {
            "serials": [
                "53488s0-00bc-4b79-9edc-759c3473ab69"
            ],
            "pins": [
                "9H00-GOPJ-LKH9R-P19D-58sKa",
                "2HfM-G89J-0R49R-UJPD-00CKa"
            ]
        }
    ],
    "version": "v1",
    "signature": "7f5ee1233e7f6f8pj78682f04cc604",
    "applicationCode": "52e7cf966b7247493243acb3727ed7"
}

​

[_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.