• sqlite3

sqlite3

https://www.pythoncentral.io/introduction-to-sqlite-in-python/

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(path_to_file)) as conn:
    with closing(conn.cursor()) as cur:
        with conn: # auto-commits
            cur.execute(statement)

c.execute('select * from stocks where symbol=?', t)

conn = sqlite3.connect(":memory:")
conn = sqlite3.connect('file:path/to/database?mode=ro', uri=True)

# to access fields by name instead of by index
conn.row_factory = sqlite3.Row

# to have a timestamp type corresponding to datetime
sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
...
cur.execute('create table test(d date, ts timestamp)')
cur.execute('insert into test(d, ts) values (?, ?)', (today, now))
cur.execute('select d, ts from test')
cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')

TODO: importing csv into sqlite using .separator and .import sqlite special functions: https://cs.stanford.edu/people/widom/cs145/sqlite/SQLiteLoad.html TODO: read http://charlesleifer.com/blog/going-fast-with-sqlite-and-python/ TODO: types adapters and converters TODO: difference between PARSE_COLNAMES and PARSE_DECLTYPES

How to use sqlite with context managers?

src/sqlite.py:

import sqlite3
from contextlib import closing
import datetime

with closing(sqlite3.connect(":memory:",
                             detect_types = sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)) as conn:
    conn.row_factory = sqlite3.Row     # access fields by name
    with closing(conn.cursor()) as cur:
        with conn:    # auto-commit or -rollback
            cur.execute('''create table stocks (date text, trans text, symbol text, qty real, price real)''')
            purchases = [
                ('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
                ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
                ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
            cur.executemany('insert into stocks values (?,?,?,?,?)', purchases)
            rows = cur.execute('select * from stocks order by price')
            print(rows)
            for r in rows:
                print(r)
                
            cur.execute('select * from stocks where symbol=?', ('RHAT',))
            print(cur.fetchone())
            cur.execute('select * from stocks where symbol=?', ('IBM',))
            r = cur.fetchone()
            print(type(r))
            
            cur.execute("create table test(d date, ts timestamp)")

            today = datetime.date.today()
            now = datetime.datetime.now()

            cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
            cur.execute("select d, ts from test")
            row = cur.fetchone()
            print(today, "=>", row['d'], type(row['d']))
            print(now, "=>", row['ts'], type(row['ts']))

            
            cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')
            row = cur.fetchone()
            print("current_date", row['d'], type(row['d']))
            print("current_timestamp", row['ts'], type(row['ts']))