python - Shorten SQLite3 insert statement for efficiency and readability

Question

From this answer:

cursor.execute("INSERT INTO booking_meeting (room_name,from_date,to_date,no_seat,projector,video,created_date,location_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (rname, from_date, to_date, seat, projector, video, now, location_name ))

I'd like to shorten it to something like:

simple_insert(booking_meeting, rname, from_date, to_date, seat, projector, video, now, location_name)

The first parameter is the table name which can be read to get list of column names to format the first section of the SQLite3 statement:

cursor.execute("INSERT INTO booking_meeting (room_name,from_date,to_date,no_seat,projector,video,created_date,location_name)

Then the values clause (second part of the insert statement):

VALUES (?, ?, ?, ?, ?, ?, ?, ?)"

can be formatted by counting the number of column names in the table.

I hope I explained the question properly and you can appreciate the time savings of such a function. How to write this function in python? ...is my question.

There may already a simple_insert() function in SQLite3 but I just haven't stumbled across it yet.

question from:https://stackoverflow.com/questions/66056368/shorten-sqlite3-insert-statement-for-efficiency-and-readability

Answer 1

If you're inserting into all the columns, then you don't need to specify the column names in the INSERT query. For that scenario, you could write a function like this:

def simple_insert(cursor, table, *args):
    query = f'INSERT INTO {table} VALUES (' + '?, ' * (len(args)-1) + '?)'
    cursor.execute(query, args)

For your example, you would call it as:

simple_insert(cursor, 'booking_meeting', rname, from_date, to_date, seat, projector, video, now, location_name)

Note I've chosen to pass cursor to the function, you could choose to just rely on it as a global variable instead.