How can I use column names to specify what to write to using sqlite3?

Question

For example, I want to create a function where I add a user to my 'users' table:

def add_user(name, id):
    cursor.execute("INSERT INTO users VALUES (?, ?, ?)", (name, id))
    conn.commit()

(It should be noted that the 'name' column (not the variable) is the first column, and the 'id' column is the third column)

This code writes the 'name' variable to the first column, which is the name column, and writes the 'id' variable to the second column (which is not the id column).

Is there a way I can use the names of the columns to specify what to write to? I want to avoid using solutions like writing the current value of the second column to itself, or adding 0 to it, or something like that.

forpas · Accepted Answer · 2021-06-03 15:41:06Z

1

First, you are using 3 ? placeholders and pass only 2 values: name and id, so your code would not even run.

In the INSERT statement, you can specify the columns that will receive the values that you supply as a tuple:

cursor.execute("INSERT INTO users(name, id) VALUES (?, ?)", (name, id))

or:

cursor.execute("INSERT INTO users(id, name) VALUES (?, ?)", (id, name))

In both cases the result is the same.

With INSERT INTO users(id, name)... the 1st ? corresponds to the value of the column id and the 2nd ? corresponds to the value of the column name, so you must pass their values inside the tuple in the same order.

answered Jun 3, 2021 at 15:41

forpas

165k10 gold badges51 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

How can I use column names to specify what to write to using sqlite3?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related