How to perform insert statement using arrays in python 3 flask-restful

Question

I want to make multiple inputs of json using the array list. The code below shows a single post only.

parser = reqparse.RequestParser()
parser.add_argument('tblskuid', type = str)
parser.add_argument('tbluserid', type = str)
args = parser.parse_args()

tblskuid = args['tblskuid']
tbluserid = args['tbluserid']

tbl_bo = [
         (tblskuid, tbluserid, )
            ]

conn = psycopg2.connect(database='mobiletracker', user='', host='', password='')
cursor = conn.cursor()
args_str = ','.join(['%s'] * len(tbl_bo))
cursor.execute(cursor.mogrify('INSERT INTO tbl_bad_orders_product(tblskuid, tbluserid) VALUES {}'.format(args_str), tbl_bo).decode('utf8'))
conn.commit()
cursor.close()

What i want is to insert a list of data in array like this one.

tbl_bo = [
      {
        "tbl_skuid":"1",
        "tbluserid":"1",

      },
      {
        "tbl_skuid":"message",
        "tbluserid":"employeeid",

      }
    ]

and how do i suppose to perform the posting response with the use of postman? any help would really appreciated. Thanks in advance.

why you don't use something like [sqlalchemy](https://docs.sqlalchemy.org/en/latest/orm/examples.html) or [peewee](http://docs.peewee-orm.com/en/latest/peewee/example.html)? — Danila Ganchar, Dec 14 '18 at 14:39
we're handling 20k of data in just 1 post. sqlalchemy is very slow in processing. cursor is 100x faster than sql alchemy. — itTech, Dec 16 '18 at 14:05
[possible what you need](https://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query/30721460) — Danila Ganchar, Dec 17 '18 at 08:07
i already have that structure sir. :) look above. that's the way i construct my cursor :) thanks anyway :) — itTech, Dec 19 '18 at 07:07
Not understand the main problem. Do you want just insert 20k of data? What problem should the postman solve? — Danila Ganchar, Dec 19 '18 at 07:22
in making a keys in kotlin we need the urlencoded type of response. meaning to say the keys will send same as how we use to send data using x-www-form-urlencoded well anyways, we already did it in json response. too much to code but its the only way thanks. — itTech, Dec 21 '18 at 10:23

itTech · Accepted Answer · 2018-12-21T21:19:03.537

I am going to answer my own question because I solved it by myself. We're going to use json response using cursor function. I don't want to use sqlAlchemy for its being complicated and has a speed issue in heavy data posting. I think RequestParser() is not an efficient way for multiple post using arrays. Now here is the code.

class Logs_api(Resource):
    @jwt_required
    def post(self):
        try: 
          conn = None
          #getting the json request of post data 
          json_dict = request.get_json(force=True, silent=True)

          #count the length of json dictionary
          x = len(json_dict)


          tbl_logs = []
          for i in chain(range(0, x)):

            tbl_logs.append((json_dict[i]['tbluserid'],json_dict[i]['event'],json_dict[i]['current_longitude'],json_dict[i]['current_latitude'],json_dict[i]['end_longitude'],json_dict[i]['end_latitude'],json_dict[i]['gps_accuracy'],json_dict[i]['gps_provider'],json_dict[i]['battery'],json_dict[i]['datetime_log'], ))

          conn = psycopg2.connect(database='yourdb', user='dbuser', host='dbhost', password='dbpass', options='-c statement_timeout=1000')  
          cursor = conn.cursor()
          args_str = ','.join(['%s'] * len(tbl_logs))
        #   print(cursor.mogrify('INSERT INTO tbl_location_logs(tbluserid, event, current_longitude, current_latitude, end_longitude, end_latitude, gps_accuracy, gps_provider, battery, datetime_log) VALUES {}'.format(args_str), tbl_logs).decode('utf8'))
          cursor.execute(cursor.mogrify('INSERT INTO tbl_location_logs(tbluserid, event, current_longitude, current_latitude, end_longitude, end_latitude, gps_accuracy, gps_provider, battery, datetime_log) VALUES {}'.format(args_str), tbl_logs).decode('utf8'))
          conn.commit()
          cursor.close()

          return {'status' : 'success', 'message' : 'success'}
        except Exception as e:
            x = str(e)
            x.replace('\n', '')

            return {'status' : 'failed', 'message' : str(x)}
        finally:
            if conn is not None:
                conn.close()

in postman, use raw not x-www-form-urlencoded and type your json there. This is my sample format.

[ 
  {
        "tbluserid": "2",
        "event": "sample",
        "current_longitude": "20",
        "current_latitude": "200",
        "end_longitude": "20",
        "end_latitude": "200",
        "gps_accuracy": "200",
        "gps_provider": "google maps pangett",
        "battery": "200",
        "datetime_log": "2018-02-02 23:00:00"
    }
]

with the help of the code structure above, i posted a total of 5800+ of data in just 1.2 seconds and the proof are the 2 pictures below:

How to perform insert statement using arrays in python 3 flask-restful

1 Answers1