Htykuut

I am trying to implement Python Flask rest API. I have two tables (master-detail) in order to insert values from a parsed response of another rest API.

Here are my master-detail tables;

sql_create_purchase_confirmation_response_table = """ CREATE TABLE IF NOT EXISTS purchase_confirmation (

                                        id integer PRIMARY KEY,

                                        responseDatetime DATETIME NOT NULL,

                                        applicationCode text NOT NULL,

                                        version text NOT NULL,

                                        referenceId text NOT NULL,

                                        paymentId text NOT NULL,

                                        productCode text NOT NULL,

                                        quantity integer NOT NULL,

                                        currency text NOT NULL,

                                        unitPrice integer NOT NULL,

                                        totalPrice integer NOT NULL,

                                        merchantProductCode text NOT NULL,

                                        signature text NOT NULL,

                                        purchaseStatusCode text NOT NULL,

                                        purchaseStatusDate DATETIME NOT NULL                                        

                                        ); """



sql_create_purchase_confirmation_detail_response_table = """ CREATE TABLE IF NOT EXISTS purchase_confirmation_detail (

                                            referenceId text NOT NULL,

                                            serials text NULL,

                                            pins text NULL

                                            ); """

And here is my functions to insert into tables separately.

def add_purchase_confirmation_response(database_file, response):

query = "INSERT INTO purchase_confirmation (responseDatetime, applicationCode, version, referenceId," 

        "paymentId, productCode, quantity, currency, unitPrice, totalPrice, merchantProductCode," 

        "purchaseStatusCode, purchaseStatusDate, signature) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"



con = ''

try:



    with sql.connect(database_file, isolation_level=None) as con:

        con.execute('pragma journal_mode=wal')

        cur = con.cursor()

        cur.execute(query, [str(datetime.now()),

                            response['applicationCode'], response['version'], response['referenceId'],

                            response['paymentId'], response['productCode'], response['quantity'],

                            response['currency'], response['unitPrice'], response['totalPrice'],

                            response['merchantProductCode'], response['purchaseStatusCode'],

                            response['purchaseStatusDate'], response['signature']])



        con.commit()



except sql as e:

    con.rollback()

    print e.message

except:

    print("Unexpected error:", sys.exc_info()[0])

    raise



finally:

    con.close()



def add_purchase_confirmation_detail_response(database_file, response):

query = "INSERT INTO purchase_confirmation ( referenceId," 

        "serials, pins) VALUES (?, ?, ?)"



con = ''

try:



    pins = ''

    # parse response coupons

    for item in response['coupons']:

        for itemS in item['serials']:

            serials = itemS



            for itemP in item['pins']:

                pins = pins + itemP + ','



            print serials.rstrip(',')

            print pins.rstrip(',')



            # insert into table here

            with sql.connect(database_file, isolation_level=None) as con:

                con.execute('pragma journal_mode=wal')

                cur = con.cursor()

                cur.execute(query, [response['referenceId'], serials, pins])



                con.commit()

            pins = ''



except sql as e:

    con.rollback()

    print e.message

except:

    print("Unexpected error:", sys.exc_info()[0])

    raise



finally:

    con.close()

**In order to provide data consistency, is there a way to use transaction? I am new to Python, I would be glad if you can guide me. **

def confirmation():

try:

    uri = 'https://teststore.com/purchaseconfirmation'

    r = requests.post(uri, data=request_params_confirmation)



    add_purchase_confirmation_response('test.db', r.json())

    add_purchase_confirmation_detail_response('test.db', r.json())

    return jsonify(r.text)

except Exception as e:

    return e.message

except:

    print("Unexpected error:", sys.exc_info()[0])

    raise

SQLite has "atomic" transactions if you use BEGIN TRANSACTION. Probably in confirmation function, call a BEGIN TRANSACTION query before add_purchase... calls and then execute a commit or rollback depending on success or failure. You may also find this doc on busy timeout salient.