Update update_db.py

Sebastian Raschka · Sebastian Raschka · commit 35fce5c50203 · 2013-12-02T18:26:19.000-05:00
diff --git a/sqlite3/update_db.py b/sqlite3/update_db.py
@@ -7,11 +7,23 @@
 conn = sqlite3.connect('zinc_db1.db')
 c = conn.cursor()
 
-# update field
+# update field (no insert if id doesn't exist)
 t = ('NO', 'ZINC00895033', )
 c.execute("UPDATE zinc_db1 SET purchasable=? WHERE zinc_id=?", t)
 print "Total number of rows changed:", conn.total_changes
 
+
+# update, or insert when id does not exist
+# here: updates rotatable bonds if record with primary key zinc_id exists,<br>  
+#     else inserts new record an sets purchasable to 0
+c.execute("""INSERT OR REPLACE INTO zinc_db1 (zinc_id, rotatable_bonds, purchasable)
+              VALUES ( 'ZINC123456798', 
+                3, 
+                COALESCE((SELECT purchasable from zinc_db1 WHERE zinc_id = 'ZINC123456798'), 0)
+              )"""
+
+
+
 # delete rows
 t = ('NO', )
 c.execute("DELETE FROM zinc_db1 WHERE purchasable=?", t)
