sqlite3 INSERT OR REPLACE overwrite fields?

Question

I have a gradebook that I've used for a couple years. I'm trying to clean it up a little because a colleague wants to use it, but it's not really user ready, just personally ready. Anyway, I was trying to automate updating my student list. I tried the following:

import csv
import sqlite3
f = csv.reader(open('students.csv'))

db = sqlite3.connect('gradebook.db')
cursor = db.cursor()

for row in f:
    print row
    cursor.execute('INSERT OR REPLACE INTO students (id, name, class, gender, birthday, something, period, section_number, subject, begin_date, end_date, status, absents, tardies) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)', row)

db.commit()
db.close()

This works for new students, BUT, I also have a couple columns in my database that aren't referenced by the CSV dump from school (notes, current chapter, etc). When I run the above code, anything I have in the non-referenced columns gets set to NULL. Is there a way to preserve columns not referenced in the execute command?

EDIT: Tried a bunch of different things, through my incompetence, nothing worked. Ended up with the following in a loop, just got an error:

cursor.execute('INSERT OR REPLACE INTO students (notes, id, name, class, gender, 
    birthday, something, period, section_number, subject, begin_date, end_date, 
    status, absents, tardies) VALUES ((select notes from students where id = 
    row[0]),?,?,?,?,?,?,?,?,?,?,?,?,?,?)', row)

It's the notes that I'm trying to save. All the other fields can be updated, based on the student's ID. I'm giving up for a little while. Getting too frustrated.

Create an ON UPDATE trigger: if NEW.xx IS NULL then set NEW.xx = OLD.xx — lc., Aug 31 '12 at 01:01
@pst That's fine, that's what I want to happen. I don't like the fields not specified (IE 'notes' — Benjamin Compson, Aug 31 '12 at 01:01
possible duplicate of [SQLite - UPSERT *not* INSERT or REPLACE](http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace) — lc., Aug 31 '12 at 01:03
@lc Certainly close enough for a competent programmer to figure it out. I stared at that one for awhile and tried a couple variations from my terminal and I just don't see how to make it work when I have ?,?,?,? stuff in a loop. I'll fumble around a little more. — Benjamin Compson, Aug 31 '12 at 01:13
Don't think so hard. Just add ALL columns, use the ? for parameters you're specifying and use subselects for columns you are not specifying. — lc., Aug 31 '12 at 01:15
@lc I'm out of my depth. I'm going to eat and cool down. Don't know how people program for a living. It's fun to try every once in awhile, but I don't know if I could handle it all day, every day. — Benjamin Compson, Aug 31 '12 at 01:37
Ahh, right right, sorry...You'll have to specify your parameters by name because you're re-using the ID one. — lc., Aug 31 '12 at 01:40

score 0 · Answer 1 · answered Aug 31 '12 at 03:55

Well, never could figure out the INSERT OR REPLACE format. Ended up just running and UPDATE query followed by an INSERT (catching the error on the primary key). Works for what I'm doing. Really seems like there should be an elegant way. UPSERT. Whatever.

sqlite3 INSERT OR REPLACE overwrite fields?

1 Answers1