[Basic] Condensed sqlite tutorial (riff on sebastian raschka's) - part 1

[mepyyeti]

This is part 1 of a tutorial on sqlite3 (part of the standard library and widely used). Sebastian Saschka wrote the original in 2014 and can be found here.

For this 'series' I've incorporated python3's f string and condensed the code to demystify it a bit for us noobs.

It emphasizes operations 'create table', 'alter table', 'insert into', 'update...SET', and uses 'primary key' and 'default' keywords.

I've limited this part to the simplest basics - creating the db, adding new columns, and inserting & updating rows. Each of these topics is chopped up into a snippet. It amounts to ~40% of the original.

for the db to be functional you have to run each snippet in order... Otherwise you run risk of duplication errors (especially when altering tables).

1 last word...I've relied on the open source sqlbrowser that can be found here. Raschka's guide relies on SQLite Manager . Unfortunately, it is incompatible with my firefox...

snippet 1 -- creating db

#!usr/bin/env python3

import sqlite3

db_file = 'sql3_db.sqlite'
table1 = 'my_1st_table'
id_column = 'my_id_column'
id_column_type = 'integer'

#variables below are used for parts snippets 2 and 3
'''
column1 = 'my_new_column1'
column1_type = 'text'
defval = 'hello world'

column2 = 'my_new_column2'
column2_type = 'text'
'''
con = sqlite3.connect(db_file)
c = con.cursor()

#create the database table
#CAUTION: DO NOT FORGET the COMMA btwn {id_column} and {id_column_type}
c.execute(f'create table {table1} ({id_column},{id_column_type} primary key)')

#commit() is necessary for any changes to be effected.
con.commit()
con.close()

snippet 2 -- adding new columns

#be sure these variables are not commented out
'''
column1 = 'my_new_column1'
column1_type = 'text'
defval = 'hello world'

column2 = 'my_new_column2'
column2_type = 'text'
'''
#it may be easiest to copy/paste this snippet just before con.commit() line in snippet 1

#alter table1 by adding column1 WITH NO DEFAULT value
#NOTE variable following COLUMN keyword requires quotes
c.execute(f"alter table {table1} add column '{column1}' {column1_type}")

#alter table1 by adding column2 WITH SINGLE DEFAULT value
#NOTE variable following DEFAULT keyword requires quotes
c.execute(f"alter table {table1} add column '{column2}' {column2_type} default '{defval}'")

#if code is running as separate file:
con.commit()
con.close()

snippet 3 -- inserting and updating rows

#again, these variables are needed
'''
column1 = 'my_new_column1'
column1_type = 'text'
defval = 'hello world'

column2 = 'my_new_column2'
column2_type = 'text'
'''
#the try/except form below is not necessary but can be helpful
'''
try:
	c.execute(f"insert into {table1} ({id_column}, {column2}) values (123, 'test')")
except sqlite3.IntegrityError:
	print(f'ERROR: {id_column} already contains the inputed value. ')
'''

#insert a UNIQUE id value if one doesn't exist and insert a SPECIFIC value into column1
#NOTICE insert or ignore keyword 'pattern'
c.execute(f"insert or ignore into {table1} ({id_column},{column1}) values (123, 'test')")

c.execute(f"update {table1} set {column2}='foo bar' where {id_column}=(123)")

con.commit()
con.close()

I hope you find it a bit helpful