sqlite python example

example.py

#!/usr/bin/env python3

# starting with https://docs.python.org/3.8/library/sqlite3.html
import sqlite3
conn = sqlite3.connect('example.db')

c = conn.cursor()

c.execute("DROP TABLE IF EXISTS stocks")
c.execute("DROP TABLE IF EXISTS stuff")

# Create table

c.execute('''CREATE TABLE if not exists stocks
             (fnummer integer, name text, qty real)''')
c.execute('''CREATE TABLE if not exists stuff
             (fnummer integer, name text, price real)''')

# Insert some data

stocks = [('2','hannes', 100),
          ('3','anna', 40),
          ('4','herbert', 20),
          ('6','holger', 22),
         ]
stuff = [('2','hans', 35.14),
         ('3','anne', 37.34),
         ('5','luise', 97.27),
         ('6','holger', 10.10),
        ]
c.executemany('INSERT INTO stocks VALUES (?,?,?)', stocks)
c.executemany('INSERT INTO stuff VALUES (?,?,?)', stuff)

# Save (commit) the changes
conn.commit()

# select the data with sqlite full outer join workaround
cursor = c.execute("""SELECT *
        FROM stocks LEFT JOIN stuff ON stocks.fnummer = stuff.fnummer
        UNION ALL
        SELECT *
        FROM stuff LEFT JOIN stocks ON stocks.fnummer = stuff.fnummer
        WHERE stocks.fnummer IS NULL;""")

for row in cursor:
    print(row)

print()

# … add some function to the projection
cursor = c.execute("""SELECT ifnull(stocks.fnummer, stuff.fnummer), ifnull(stocks.name, stuff.name), qty, price
        FROM stocks LEFT JOIN stuff ON stocks.fnummer = stuff.fnummer
        UNION ALL
        SELECT ifnull(stocks.fnummer, stuff.fnummer), ifnull(stocks.name, stuff.name), qty, price
        FROM stuff LEFT JOIN stocks ON stocks.fnummer = stuff.fnummer
        WHERE stocks.fnummer IS NULL;""")

for row in cursor:
    print(row)

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()