0

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.

Improve this question
5
  • If you're inserting into all the columns you can leave out the (room_name,from_date,to_date,no_seat,projector,video,created_date,location_name) part of the query; then you could just count the number of parameters to your simple_insert function and build the VALUES clause with one less ? (to account for the parameter which is the table name) Commented Feb 5, 2021 at 1:41
  • @Nick Yes I will always be inserting all the columns into the row. I didn't know you could actually insert a row with missing columns. That seems counterintutitive. Commented Feb 5, 2021 at 1:50
  • As long as there are default values for the missing columns, or they can be NULL, you can insert a row with missing columns. Commented Feb 5, 2021 at 1:51
  • @Nick I could always have default value and NULL inserted. Don't see the need to define them globally. Besides my table is dynamic and there are no static fields. Commented Feb 5, 2021 at 1:53
  • @Nick If you have time to write a function and post it I would like to use it and accept your answer. Commented Feb 5, 2021 at 1:55

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Brilliant three-liner! I'm just learning python and SQL so *args, query and your VALUES extrapolation is above my pay grade. Quick question though since there are already two question marks shouldn't (len(args)-1) be (len(args)-2) instead?
@WinEunuuchs2Unix There needs to be len(args) question marks; the first part '?, ' * (len(args)-1) generates len(args)-1 of them, and the '?)' adds the last one.
You could also use something like ', '.join('?' for _ in args) to generate the list of question marks.
I've used the join command a couple of times in my program. I guess it might be more readable. I'm still back in Python 2.7.12 days if that makes any difference?
@WinEunuuchs2Unix you really should upgrade, python 2 hasn't been supported for over a year. But either code will work fine in 2.7
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.