CODEWITHSUNDEEP
pythonsqlite
Hazel
Hazel

Reputation: 18

python sqlite - Assign the same value to multiple ? parameter substitutions in UPSERT

I have an SQLite database which I'm running the new UPSERT on. (Also, yes, you read that right. UPSERT's! In SQLite!) It has columns: name (UNIQUE), descr, and author_id. I know I can use

db.execute('INSERT INTO post (name, descr, author_id)'
  ' VALUES (?, ?, ?)'
  ' ON CONFLICT(name)'
  ' DO UPDATE SET descr = ?, author_id = ?',
  (self.name, self.descr, self.author_id, self.descr, self.author_id)
)

But that seems so repetitive in the last line. Is there a clean way I can map the same value (eg: self.descr) to multiple question marks (eg: the second and fourth)? Or is this simply the best possible approach?

Upvotes: 0

Views: 212

Answers (1)

Andrej Kesely
Andrej Kesely

Reputation: 195553

Yes, you can use named parameters:

db.execute('INSERT INTO post (name, descr, author_id)'
  ' VALUES (:name, :descr, :author_id)'
  ' ON CONFLICT(name)'
  ' DO UPDATE SET descr = :descr, author_id = :author_id',
  {'name': self.name, 'descr': self.descr, 'author_id': self.author_id}
)

Upvotes: 2

Related Questions