Parsing and Inserting data from a python dictionary into a database table using DAL

[Rahul]

Hi All, Massimo
     I am receiving a dictionary of records from a webservice like below (sample single record with just a few fields here) . Now I want to parse all the records and insert the data in my table (Currently SQLite and later it would be POSTGRE) . What is the best way to insert data into SQLite or other databases using DAL from following code. I am assuming that since we would utilize DAL it  might work irrespective of underlying DB. my table has similar fields but fieldnames and their order is changed a bit.

{u'status': None, u'tax': 3.6, u'entity': u'payment', u'currency': u'INR', u'id': u'128934802340', }

Thanks in advance for all the help,  :-)

Rahul

[Anthony]

If you can make the field names in the DAL model match exactly, you can simply do:

db.mytable.insert(**db.mytable._filter_fields(record_dict))

If you can't change the field names in the database itself but are comfortable changing the names within your DAL models, you can use the "rname" option when defining the fields that have mismatched names:

Field('tax', rname='sales_tax')

The above would allow you to do inserts using the name "tax", even though the real field name in the database is "sales_tax".

Otherwise, I suppose you would just do something like:

db.mytable.insert(field1=record['field1'], field2=record['field2_alt_spelling'], ...)

Anthony

[Rahul]

Thanks! Anthony for guiding me on this. Always #1 for helping us out.
Now there is one last thing and it may be trivial but can you point me to the right direction. I used below notation. This works fine if I have a single record

db.mytable.insert(field1=record['field1'], field2=record['field2_alt_spelling'], ...)

However, if I have multiple records, it fails with a type error given below

TypeError: string indices must be integers

Data is in this format -
{u'count': 6, u'items': [{u'refund_status': None, u'tax': None, u'entity': u'payment', u'currency': u'INR', u'id': u'Tq99121U7', u'captured': False, u'fee': None, u'international': False, u'email': u'hel...@gmail.com', u'status': u'failed', u'amount_refunded': 0, u'description': u'Purchase Description', u'order_id': None, u'vpa': None, u'bank': None, u'invoice_id': None, u'notes': [], u'card_id': u'311645', u'method': u'card', u'wallet': None, u'amount': 100, u'contact': u'7001394', u'error_description': u'Payment failed', u'error_code': u'BAD_REQUEST_ERROR', u'created_at': 15138345825},
 {u'refund_status': ......},
 {u'refund_status': None, .... 1513940199} ,
 {u'refund_status': None, u'created_at': 1513738859},

It is probably expecting integers here. How do I pass values in this case ?

Thanks,

Rahul.

[Anthony]

On Thursday, January 11, 2018 at 6:39:38 AM UTC-5, Rahul wrote:

Thanks! Anthony for guiding me on this. Always #1 for helping us out.
Now there is one last thing and it may be trivial but can you point me to the right direction. I used below notation. This works fine if I have a single record

db.mytable.insert(field1=record['field1'], field2=record['field2_alt_spelling'], ...)

However, if I have multiple records, it fails with a type error given below

TypeError: string indices must be integers

Data is in this format -
{u'count': 6, u'items': [{u'refund_status': None, u'tax': None, u'entity': u'payment', u'currency': u'INR', u'id': u'Tq99121U7', u'captured': False, u'fee': None, u'international': False, u'email': u'hel...@gmail.com', u'status': u'failed', u'amount_refunded': 0, u'description': u'Purchase Description', u'order_id': None, u'vpa': None, u'bank': None, u'invoice_id': None, u'notes': [], u'card_id': u'311645', u'method': u'card', u'wallet': None, u'amount': 100, u'contact': u'7001394', u'error_description': u'Payment failed', u'error_code': u'BAD_REQUEST_ERROR', u'created_at': 15138345825},
 {u'refund_status': ......},
 {u'refund_status': None, .... 1513940199} ,
 {u'refund_status': None, u'created_at': 1513738859},

Hard to say what the problem is without seeing your code, but your records are a list embedded in a dictionary, so you need to extract the list and loop over it:

for record in data['items']:
    db.mytable.insert(field1=record['field1'], ...)

Anthony

[Rahul]

Thank you! very much Anthony. That hint saved me a lot of time - The following code worked fine.

 allrecords = c.payment.all() # Fetches all the records
 
    for myrecord in allrecords['items']:
        webdbase.database.insert(.....

Just a few words about the project. I am working with integrating a new payment gateway in web2py app and hope to publish a new slice once it is successfully completed. Thanks! again

Rahul.

On Friday, January 12, 2018 at 7:38:51 AM UTC+5:30, Anthony wrote:

On Thursday, January 11, 2018 at 6:39:38 AM UTC-5, Rahul wrote:

Thanks! Anthony for guiding me on this. Always #1 for helping us out.
Now there is one last thing and it may be trivial but can you point me to the right direction. I used below notation. This works fine if I have a single record

db.mytable.insert(field1=record['field1'], field2=record['field2_alt_spelling'], ...)

However, if I have multiple records, it fails with a type error given below

TypeError: string indices must be integers

Data is in this format -

{u'count': 6, u'items': [{u'refund_status': None, u'tax': None, u'entity': u'payment', u'currency': u'INR', u'id': u'Tq99121U7', u'captured': False, u'fee': None, u'international': False, u'email': u'he...@gmail.com', u'status': u'failed', u'amount_refunded': 0, u'description': u'Purchase Description', u'order_id': None, u'vpa': None, u'bank': None, u'invoice_id': None, u'notes': [], u'card_id': u'311645', u'method': u'card', u'wallet': None, u'amount': 100, u'contact': u'7001394', u'error_description': u'Payment failed', u'error_code': u'BAD_REQUEST_ERROR', u'created_at': 15138345825},

 {u'refund_status': ......},
 {u'refund_status': None, .... 1513940199} ,
 {u'refund_status': None, u'created_at': 1513738859},