Skip to content

Instantly share code, notes, and snippets.

@palevell

palevell/sql-ins-upd-ignore.py

Last active October 17, 2018 23:04
Show Gist options
  • Save palevell/f57d3ce7122d70c5406afe73dcc73e4b to your computer and use it in GitHub Desktop.
Save palevell/f57d3ce7122d70c5406afe73dcc73e4b to your computer and use it in GitHub Desktop.
Download ZIP
This code snippet demonstrate's SQLite's new UPSERT command.
Raw
sql-ins-upd-ignore.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# sql-ins-upd-ignore.py v1.0.0 - Tuesday, October 16, 2018
# Ref1: https://stackoverflow.com/a/50718957/2719754
# Ref2: https://www.sqlite.org/lang_UPSERT.html
"""
The UPSERT command is implemented by way of an ON CONFLICT clause
on the INSERT statement.
In this snippet, the HITS column is incremented, to indicate the
number of times that random names were generated by the faker module.
The DELAY flag is merely to make the UPDATED column have a different
timestamp than the CREATED colum.
There is also a bit of code to "pretty print" the output by querying
the database to determine column widths. There may be a more "Pythonic" way to do this.
Enjoy!
"""
from faker import Faker
from time import sleep
import os
import sqlite3 as lite
DELAY = True
def main():
fake = Faker()
db = lite.connect(':memory:')
sql = """
CREATE TABLE dt_example (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name STRING(50) NOT NULL,
hits INTEGER NOT NULL DEFAULT 0,
created DATETIME NOT NULL DEFAULT (DATETIME ('now', 'localtime')),
updated DATETIME
);
CREATE UNIQUE INDEX idx_example_name ON dt_example (name);
CREATE TRIGGER trb_row_updated
BEFORE UPDATE ON dt_example
BEGIN
UPDATE dt_example
SET updated = DATETIME('now', 'localtime')
WHERE id = NEW.id;
END;
INSERT INTO dt_example (name) VALUES ('Bob');
"""
with db:
cur = db.cursor()
# Create table(s)
cur.executescript(sql)
cur.execute("SELECT * FROM dt_example LIMIT 1;")
headings = [description[0] for description in cur.description]
print("Headings:", headings)
# Populate table(s)
for i in range(25):
names = []
for j in range(25):
names.append([fake.first_name(),])
sql = """
INSERT INTO dt_example(name) VALUES(?)
ON CONFLICT(name) DO UPDATE SET hits = hits + 1;
"""
cur.executemany(sql, names)
if DELAY:
sleep(1.01)
# Get Summary Info (ie. column widths)
sql = """
SELECT length(max(id)), max(length(name)), length(max(hits))
FROM dt_example;
"""
cur.execute(sql)
id_width, name_width, hits_width = cur.fetchone()
print(id_width, name_width, hits_width)
hits_fmt = "%%%dd" % hits_width
id_fmt = "%%%dd)" % id_width
name_fmt = "%%-%ds" % name_width
date_fmt = "%s"
fmt = "%s %s %s %s %s" % (id_fmt, name_fmt, hits_fmt, date_fmt, date_fmt)
print("fmt:", fmt)
# View Results
cur.execute("SELECT * FROM dt_example;")
rows = cur.fetchall()
# Cols: id, name, hits, created, updated
for row in rows:
print(fmt % row)
print()
return
def DebugBreakpoint():
do_nothing = True
return
def init():
if lite.sqlite_version_info < (3,24,0):
print("This version of SQLite does not support UPSERT. Aborting.")
exit()
return
def eoj():
print("Done.")
return
if __name__ == '__main__':
init()
main()
eoj()
""" Sample Output:
Headings: ['id', 'name', 'hits', 'created', 'updated']
3 11 2
fmt: %3d) %-11s %2d %s %s
1) Bob 0 2018-10-17 17:02:56 None
2) Kenneth 4 2018-10-17 17:02:56 2018-10-17 17:03:20
3) James 8 2018-10-17 17:02:56 2018-10-17 17:03:21
4) Jennifer 7 2018-10-17 17:02:56 2018-10-17 17:03:13
5) Ashley 5 2018-10-17 17:02:56 2018-10-17 17:03:21
6) Nancy 3 2018-10-17 17:02:56 2018-10-17 17:03:17
8) Matthew 2 2018-10-17 17:02:56 2018-10-17 17:03:19
9) Kelli 1 2018-10-17 17:02:56 2018-10-17 17:03:13
10) Katherine 2 2018-10-17 17:02:56 2018-10-17 17:03:15
11) Richard 4 2018-10-17 17:02:56 2018-10-17 17:03:17
12) Paul 4 2018-10-17 17:02:56 2018-10-17 17:03:21
14) Shawn 0 2018-10-17 17:02:56 None
15) Janet 0 2018-10-17 17:02:56 None
16) Roger 0 2018-10-17 17:02:56 None
17) Mark 8 2018-10-17 17:02:56 2018-10-17 17:03:21
18) Brittany 3 2018-10-17 17:02:56 2018-10-17 17:03:13
19) Sara 1 2018-10-17 17:02:56 2018-10-17 17:03:10
20) Robin 0 2018-10-17 17:02:56 None
21) John 9 2018-10-17 17:02:56 2018-10-17 17:03:16
22) Brandy 0 2018-10-17 17:02:56 None
23) Alexandra 0 2018-10-17 17:02:56 None
24) Carolyn 2 2018-10-17 17:02:56 2018-10-17 17:03:16
25) David 14 2018-10-17 17:02:56 2018-10-17 17:03:21
26) Laura 2 2018-10-17 17:02:56 2018-10-17 17:03:11
27) Debra 1 2018-10-17 17:02:57 2018-10-17 17:03:18
28) Steven 1 2018-10-17 17:02:57 2018-10-17 17:03:09
29) Sean 4 2018-10-17 17:02:57 2018-10-17 17:03:14
30) Stephen 1 2018-10-17 17:02:57 2018-10-17 17:03:18
31) Crystal 0 2018-10-17 17:02:57 None
32) Holly 2 2018-10-17 17:02:57 2018-10-17 17:03:18
34) Jorge 1 2018-10-17 17:02:57 2018-10-17 17:03:20
36) Heather 3 2018-10-17 17:02:57 2018-10-17 17:03:17
37) Heidi 0 2018-10-17 17:02:57 None
38) Hannah 1 2018-10-17 17:02:57 2018-10-17 17:03:17
39) Lisa 2 2018-10-17 17:02:57 2018-10-17 17:03:16
40) Joseph 4 2018-10-17 17:02:57 2018-10-17 17:03:20
41) Phillip 0 2018-10-17 17:02:57 None
42) Tammy 1 2018-10-17 17:02:57 2018-10-17 17:03:17
43) Sierra 0 2018-10-17 17:02:57 None
44) Brenda 6 2018-10-17 17:02:57 2018-10-17 17:03:19
45) Melissa 1 2018-10-17 17:02:57 2018-10-17 17:02:58
46) Michele 1 2018-10-17 17:02:57 2018-10-17 17:03:18
47) Tamara 1 2018-10-17 17:02:57 2018-10-17 17:03:16
48) Danielle 3 2018-10-17 17:02:57 2018-10-17 17:03:11
49) Jon 0 2018-10-17 17:02:57 None
50) Thomas 2 2018-10-17 17:02:57 2018-10-17 17:03:09
51) Arthur 1 2018-10-17 17:02:57 2018-10-17 17:03:02
52) Nicole 4 2018-10-17 17:02:58 2018-10-17 17:03:10
53) Carol 1 2018-10-17 17:02:58 2018-10-17 17:03:01
54) Martin 0 2018-10-17 17:02:58 None
55) Donald 2 2018-10-17 17:02:58 2018-10-17 17:03:10
56) Lynn 1 2018-10-17 17:02:58 2018-10-17 17:03:10
57) Stacey 2 2018-10-17 17:02:58 2018-10-17 17:03:07
58) Anne 2 2018-10-17 17:02:58 2018-10-17 17:03:18
60) Julie 2 2018-10-17 17:02:58 2018-10-17 17:03:00
61) Margaret 3 2018-10-17 17:02:58 2018-10-17 17:03:20
62) Stacy 0 2018-10-17 17:02:58 None
63) Tonya 0 2018-10-17 17:02:58 None
64) Travis 5 2018-10-17 17:02:58 2018-10-17 17:03:16
65) Jessica 4 2018-10-17 17:02:58 2018-10-17 17:03:16
66) Susan 3 2018-10-17 17:02:58 2018-10-17 17:03:17
67) Emily 6 2018-10-17 17:02:58 2018-10-17 17:03:17
69) Wendy 0 2018-10-17 17:02:58 None
70) Andrea 2 2018-10-17 17:02:58 2018-10-17 17:03:10
71) Rhonda 0 2018-10-17 17:02:58 None
72) Rachel 1 2018-10-17 17:02:58 2018-10-17 17:03:05
74) Mary 9 2018-10-17 17:02:58 2018-10-17 17:03:19
75) Edward 1 2018-10-17 17:02:58 2018-10-17 17:03:14
76) Andrew 2 2018-10-17 17:02:58 2018-10-17 17:03:21
77) Keith 0 2018-10-17 17:02:59 None
78) Todd 2 2018-10-17 17:02:59 2018-10-17 17:03:09
79) Pamela 5 2018-10-17 17:02:59 2018-10-17 17:03:19
80) Michelle 5 2018-10-17 17:02:59 2018-10-17 17:03:21
81) Evan 0 2018-10-17 17:02:59 None
84) Joanna 0 2018-10-17 17:02:59 None
87) Anita 0 2018-10-17 17:02:59 None
88) Ryan 5 2018-10-17 17:02:59 2018-10-17 17:03:20
89) Toni 0 2018-10-17 17:02:59 None
90) Robert 8 2018-10-17 17:02:59 2018-10-17 17:03:21
92) Nicholas 2 2018-10-17 17:02:59 2018-10-17 17:03:08
93) Amanda 4 2018-10-17 17:02:59 2018-10-17 17:03:15
95) Natalie 0 2018-10-17 17:02:59 None
96) Alan 1 2018-10-17 17:02:59 2018-10-17 17:03:17
98) Christopher 3 2018-10-17 17:02:59 2018-10-17 17:03:19
99) Sydney 0 2018-10-17 17:02:59 None
100) Amy 5 2018-10-17 17:02:59 2018-10-17 17:03:19
101) Jack 0 2018-10-17 17:02:59 None
103) Kimberly 3 2018-10-17 17:03:00 2018-10-17 17:03:18
105) Jeffrey 3 2018-10-17 17:03:00 2018-10-17 17:03:15
107) Erica 1 2018-10-17 17:03:00 2018-10-17 17:03:07
108) Ronald 2 2018-10-17 17:03:00 2018-10-17 17:03:19
109) Derrick 2 2018-10-17 17:03:00 2018-10-17 17:03:18
111) Linda 0 2018-10-17 17:03:00 None
114) Marcus 1 2018-10-17 17:03:00 2018-10-17 17:03:07
116) Brandi 0 2018-10-17 17:03:00 None
117) Curtis 0 2018-10-17 17:03:00 None
118) Joshua 1 2018-10-17 17:03:00 2018-10-17 17:03:19
119) Tara 0 2018-10-17 17:03:00 None
120) Micheal 0 2018-10-17 17:03:00 None
122) Michael 14 2018-10-17 17:03:00 2018-10-17 17:03:21
124) Dylan 1 2018-10-17 17:03:00 2018-10-17 17:03:16
127) Kyle 0 2018-10-17 17:03:01 None
128) Tyler 2 2018-10-17 17:03:01 2018-10-17 17:03:18
130) Stephanie 6 2018-10-17 17:03:01 2018-10-17 17:03:18
131) Judy 1 2018-10-17 17:03:01 2018-10-17 17:03:09
133) Marie 0 2018-10-17 17:03:01 None
134) Dustin 0 2018-10-17 17:03:01 None
135) Zachary 1 2018-10-17 17:03:01 2018-10-17 17:03:16
136) Joe 0 2018-10-17 17:03:01 None
141) Kim 0 2018-10-17 17:03:01 None
143) Bradley 0 2018-10-17 17:03:01 None
146) Raymond 0 2018-10-17 17:03:01 None
147) Dwayne 1 2018-10-17 17:03:01 2018-10-17 17:03:07
150) Scott 5 2018-10-17 17:03:01 2018-10-17 17:03:21
151) Christian 2 2018-10-17 17:03:01 2018-10-17 17:03:14
152) Justin 5 2018-10-17 17:03:02 2018-10-17 17:03:20
155) Eric 7 2018-10-17 17:03:02 2018-10-17 17:03:21
156) Lauren 3 2018-10-17 17:03:02 2018-10-17 17:03:13
158) Sarah 7 2018-10-17 17:03:02 2018-10-17 17:03:21
159) Carla 0 2018-10-17 17:03:02 None
161) Patricia 0 2018-10-17 17:03:02 None
164) Brian 4 2018-10-17 17:03:02 2018-10-17 17:03:21
168) Gina 1 2018-10-17 17:03:02 2018-10-17 17:03:16
169) Jesse 0 2018-10-17 17:03:02 None
173) Marissa 3 2018-10-17 17:03:02 2018-10-17 17:03:20
176) Vincent 0 2018-10-17 17:03:02 None
177) Angela 4 2018-10-17 17:03:03 2018-10-17 17:03:21
179) Louis 1 2018-10-17 17:03:03 2018-10-17 17:03:11
180) William 5 2018-10-17 17:03:03 2018-10-17 17:03:19
181) Renee 1 2018-10-17 17:03:03 2018-10-17 17:03:16
183) Nathaniel 0 2018-10-17 17:03:03 None
184) Alejandro 0 2018-10-17 17:03:03 None
186) Corey 1 2018-10-17 17:03:03 2018-10-17 17:03:18
188) Felicia 0 2018-10-17 17:03:03 None
191) Amber 0 2018-10-17 17:03:03 None
192) Steve 0 2018-10-17 17:03:03 None
196) Pedro 0 2018-10-17 17:03:03 None
197) Caitlin 0 2018-10-17 17:03:03 None
198) Joyce 0 2018-10-17 17:03:03 None
199) Beth 0 2018-10-17 17:03:03 None
203) Marco 0 2018-10-17 17:03:05 None
206) Douglas 0 2018-10-17 17:03:05 None
208) Karen 1 2018-10-17 17:03:05 2018-10-17 17:03:21
214) Catherine 0 2018-10-17 17:03:05 None
216) Darrell 0 2018-10-17 17:03:05 None
219) Kevin 3 2018-10-17 17:03:05 2018-10-17 17:03:21
220) Sheri 1 2018-10-17 17:03:05 2018-10-17 17:03:14
221) Tony 1 2018-10-17 17:03:05 2018-10-17 17:03:18
222) Elizabeth 4 2018-10-17 17:03:05 2018-10-17 17:03:14
225) Brandon 2 2018-10-17 17:03:05 2018-10-17 17:03:20
231) Alison 0 2018-10-17 17:03:06 None
234) Katie 0 2018-10-17 17:03:06 None
236) Jason 1 2018-10-17 17:03:06 2018-10-17 17:03:21
239) Adam 1 2018-10-17 17:03:06 2018-10-17 17:03:06
242) Jay 1 2018-10-17 17:03:06 2018-10-17 17:03:19
253) Cameron 0 2018-10-17 17:03:07 None
255) Isaac 0 2018-10-17 17:03:07 None
258) Kiara 0 2018-10-17 17:03:07 None
260) Megan 1 2018-10-17 17:03:07 2018-10-17 17:03:16
261) Katelyn 1 2018-10-17 17:03:07 2018-10-17 17:03:15
263) Dennis 0 2018-10-17 17:03:07 None
265) Chad 0 2018-10-17 17:03:07 None
267) Daryl 1 2018-10-17 17:03:07 2018-10-17 17:03:18
268) Kelly 2 2018-10-17 17:03:07 2018-10-17 17:03:12
271) Jade 0 2018-10-17 17:03:07 None
276) Erin 0 2018-10-17 17:03:07 None
279) Karl 0 2018-10-17 17:03:08 None
280) Kirsten 0 2018-10-17 17:03:08 None
281) Dawn 1 2018-10-17 17:03:08 2018-10-17 17:03:10
283) Nathan 1 2018-10-17 17:03:08 2018-10-17 17:03:08
288) Jacob 1 2018-10-17 17:03:08 2018-10-17 17:03:11
290) Theresa 1 2018-10-17 17:03:08 2018-10-17 17:03:15
291) Vanessa 0 2018-10-17 17:03:08 None
295) Tracey 0 2018-10-17 17:03:08 None
296) Anna 2 2018-10-17 17:03:08 2018-10-17 17:03:21
301) Darren 2 2018-10-17 17:03:08 2018-10-17 17:03:20
302) Rebecca 0 2018-10-17 17:03:09 None
303) Roberto 0 2018-10-17 17:03:09 None
307) Colleen 0 2018-10-17 17:03:09 None
308) Juan 0 2018-10-17 17:03:09 None
309) Brianna 0 2018-10-17 17:03:09 None
310) Jeremy 0 2018-10-17 17:03:09 None
311) Miranda 0 2018-10-17 17:03:09 None
314) Garrett 0 2018-10-17 17:03:09 None
315) Timothy 4 2018-10-17 17:03:09 2018-10-17 17:03:13
316) Yolanda 1 2018-10-17 17:03:09 2018-10-17 17:03:11
324) Ann 0 2018-10-17 17:03:09 None
325) Sharon 1 2018-10-17 17:03:09 2018-10-17 17:03:14
328) Jamie 0 2018-10-17 17:03:10 None
331) Caleb 1 2018-10-17 17:03:10 2018-10-17 17:03:14
335) Donna 0 2018-10-17 17:03:10 None
337) Johnny 0 2018-10-17 17:03:10 None
340) Betty 1 2018-10-17 17:03:10 2018-10-17 17:03:21
346) Jerry 0 2018-10-17 17:03:10 None
347) Colton 0 2018-10-17 17:03:10 None
348) Anthony 1 2018-10-17 17:03:10 2018-10-17 17:03:14
350) Colin 0 2018-10-17 17:03:10 None
351) Andre 0 2018-10-17 17:03:10 None
354) Janice 0 2018-10-17 17:03:11 None
358) Trevor 0 2018-10-17 17:03:11 None
360) Chelsey 0 2018-10-17 17:03:11 None
364) Hunter 0 2018-10-17 17:03:11 None
369) Gregory 1 2018-10-17 17:03:11 2018-10-17 17:03:12
370) Connie 0 2018-10-17 17:03:11 None
373) Javier 0 2018-10-17 17:03:11 None
374) Brett 0 2018-10-17 17:03:11 None
377) Yvonne 0 2018-10-17 17:03:12 None
380) Tiffany 1 2018-10-17 17:03:12 2018-10-17 17:03:18
382) Jimmy 0 2018-10-17 17:03:12 None
383) Maria 2 2018-10-17 17:03:12 2018-10-17 17:03:20
384) Tanya 0 2018-10-17 17:03:12 None
385) Kristina 1 2018-10-17 17:03:12 2018-10-17 17:03:19
386) Teresa 1 2018-10-17 17:03:12 2018-10-17 17:03:20
391) Krystal 0 2018-10-17 17:03:12 None
398) Ethan 0 2018-10-17 17:03:12 None
399) Emma 0 2018-10-17 17:03:12 None
400) Peter 0 2018-10-17 17:03:12 None
402) Samantha 2 2018-10-17 17:03:13 2018-10-17 17:03:20
406) Jose 2 2018-10-17 17:03:13 2018-10-17 17:03:19
413) Melanie 0 2018-10-17 17:03:13 None
414) Gary 0 2018-10-17 17:03:13 None
415) Shelby 0 2018-10-17 17:03:13 None
418) Frank 0 2018-10-17 17:03:13 None
421) Melinda 0 2018-10-17 17:03:13 None
427) Jackie 0 2018-10-17 17:03:14 None
429) Jodi 0 2018-10-17 17:03:14 None
430) Erika 0 2018-10-17 17:03:14 None
431) Deborah 1 2018-10-17 17:03:14 2018-10-17 17:03:14
443) Bryan 0 2018-10-17 17:03:14 None
454) Cathy 0 2018-10-17 17:03:15 None
458) Kathleen 1 2018-10-17 17:03:15 2018-10-17 17:03:19
461) Jeff 0 2018-10-17 17:03:15 None
464) Rachael 0 2018-10-17 17:03:15 None
466) Wesley 0 2018-10-17 17:03:15 None
467) Samuel 0 2018-10-17 17:03:15 None
468) Cheryl 0 2018-10-17 17:03:15 None
474) Kendra 0 2018-10-17 17:03:15 None
481) Cynthia 0 2018-10-17 17:03:16 None
483) Olivia 0 2018-10-17 17:03:16 None
485) Ruben 0 2018-10-17 17:03:16 None
489) Loretta 0 2018-10-17 17:03:16 None
492) Gabrielle 0 2018-10-17 17:03:16 None
493) Jonathan 0 2018-10-17 17:03:16 None
494) Julia 0 2018-10-17 17:03:16 None
495) Kristen 0 2018-10-17 17:03:16 None
496) Deanna 0 2018-10-17 17:03:16 None
499) Aaron 2 2018-10-17 17:03:16 2018-10-17 17:03:20
502) Kelsey 0 2018-10-17 17:03:17 None
504) Alexander 1 2018-10-17 17:03:17 2018-10-17 17:03:20
505) Charles 1 2018-10-17 17:03:17 2018-10-17 17:03:19
508) Leslie 0 2018-10-17 17:03:17 None
509) Jeffery 0 2018-10-17 17:03:17 None
514) Bobby 0 2018-10-17 17:03:17 None
518) Christine 0 2018-10-17 17:03:17 None
524) Mitchell 0 2018-10-17 17:03:17 None
526) Randy 0 2018-10-17 17:03:17 None
531) Kellie 0 2018-10-17 17:03:18 None
533) Jacqueline 2 2018-10-17 17:03:18 2018-10-17 17:03:20
534) Wanda 0 2018-10-17 17:03:18 None
537) Mackenzie 0 2018-10-17 17:03:18 None
539) Brad 0 2018-10-17 17:03:18 None
541) Sandra 0 2018-10-17 17:03:18 None
542) Denise 1 2018-10-17 17:03:18 2018-10-17 17:03:19
548) Lawrence 0 2018-10-17 17:03:18 None
565) Patrick 0 2018-10-17 17:03:19 None
569) Debbie 0 2018-10-17 17:03:19 None
571) Alex 0 2018-10-17 17:03:19 None
575) Daniel 1 2018-10-17 17:03:19 2018-10-17 17:03:21
576) April 0 2018-10-17 17:03:19 None
584) Monique 0 2018-10-17 17:03:20 None
590) Shelly 0 2018-10-17 17:03:20 None
595) Christina 0 2018-10-17 17:03:20 None
599) Larry 0 2018-10-17 17:03:20 None
611) Madison 0 2018-10-17 17:03:21 None
613) Jeanne 0 2018-10-17 17:03:21 None
622) Alicia 0 2018-10-17 17:03:21 None
Done.
"""
""" INSERT INTO players (user_name, age)
VALUES('steven', 32)
ON CONFLICT(user_name)
DO UPDATE SET age=excluded.age; # RESEARCH excluded table
-----
CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
ON CONFLICT(word) DO UPDATE SET count=count+1;
-----
CREATE TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);
INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;
-----
CREATE TABLE phonebook2(
name TEXT PRIMARY KEY,
phonenumber TEXT,
validDate DATE
);
INSERT INTO phonebook2(name,phonenumber,validDate)
VALUES('Alice','704-555-1212','2018-05-08')
ON CONFLICT(name) DO UPDATE SET
phonenumber=excluded.phonenumber,
validDate=excluded.validDate
WHERE excluded.validDate>phonebook2.validDate;
"""
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment