How do I fill in the column names of an insert statement in python

Question

I want to use Python to insert some data into a db. Skipping a ton of code, I use something like this:

cursor.execute('insert into table_name (column1, column2) values (%s, %s)', variable1, variable2)

All well and good. I am fetching data from multiple endpoints of an API which all return data in a fairly flat format, for example:

[
    {
        "LearnerId": 123456,
        "LearnerCode": "654321",
        "Date": "2018-07-02",
        "Session": "AM",
        "AttendanceCode": "/\\",
        "AttendanceCodeId": 1,
        "MinutesLate": null,
        "Notes": null,
        "ClassDateRollId": 347514,
        "GroupCode": "7A",
        "EnteredDate": "2018-07-02 10:10:24"
    },
    {
        "LearnerId": 778832,
        "LearnerCode": "837478",
        "Date": "2018-07-02",
        "Session": "AM",
        "AttendanceCode": "/\\",
        "AttendanceCodeId": 1,
        "MinutesLate": null,
        "Notes": null,
        "ClassDateRollId": 347514,
        "GroupCode": "9E",
        "EnteredDate": "2018-07-02 10:12:34"
    }
]

The actual data and key:value pairs differ from endpoint to endpoint, but the basic structure (I.E. a single 'level' of a dictionary per item in the list) is the same for each endpoint.

Each endpoint is basically dropping data into a table where the column names exactly replicate the Keys in the JSON, so I would really like a single insert function that simply iterates over the json and builds the insert statement from that, filling in the column names using the keys and values using the values. I can do that for the values like so:

a = [
    {
        'foo':'one',
        'bar':'two'
    }
]

cursor.execute('insert into table_name (foo, bar) values (%s, %s)', tuple(a[0].values()))

But I can't manage to programatically fill in the '(foo, bar)' bit so that I don't have to type out the column names manually. How do I do that bit, please?

Answer 1

For some reason whenever I post on SO my mind immediately realises I'm being daft and the answer is easy. A combination of str.join() and string multiplication to the rescue!

s = len(a[0].values()) * '%s,'
s = s[:-1]
cursor.execute('insert into table_name (' + str.join(',', a[0].keys()) + ') values (' + s + ');'