inserting multiple json files with different key order into postgres table-CodePudding

I have a postgresql table with four columns in this order: created_at, id, author_id, text

I use twitters API to get tweets matching my criteria (with Python). Twitter API only allows 500 tweets per request, meaning I have to request multiple times if my search query returns more than 500 - which is the case. I want to insert json entries into my postgres table

# get column names from json object
columns=[list(x.keys()) for x in response['data']][0] # creates ['created_at','id','author_id','text']

# get the data from the response
data = json_response['data']

# write entries into postgres table
for item in data:
    my_data = [item[column] for column in columns]
    cur.execute("INSERT INTO tweets VALUES(%s, %s, %s, %s)", tuple(my_data))

Now, this works fine if the response returned has the same order as my sql table. However, a json response might have a different ordering of the keys. It might now be 'id','author_id', 'text', 'created_at'.

And then I get an error 'invalid input syntax for type bigint: ...'

Is there any way I can preserve the same order when inserting, even though the json object has a different order every time?

CodePudding user response：

You could use named variables, dict instead of tuple:

my_data = {'created_at': '', 'id': '', 'author_id': '...', 'text': '...'}
cur.execute(
    "INSERT INTO tweets VALUES(%(created_at)s, %(id)s, %(author_id)s, %(text)s)",
    my_data
)

In case all keys exist in item, you could simply pass item to execute, no need to build my_data.