Reading/writing SQLite3

The PyFunctional library is surprisingly good at working with SQLite3 and features a multitude of different operators, which turn a traditionally more complex task of querying DBS and writing to them somewhat more palatable.

For instance, when querying a sqlite3 database, we can leverage seq.sqlite3(db, query). to_list() in order to query a number of rows from a database and transform them into a list. This is just pure syntactic sugar and makes your code bases more succinct and ultimately more readable.

The official example they give in their documentation is when querying all the users from a database. They pass in the relative database path to their sqlite3 database and then call select * from user in order to return all users. The to_list() operator then transforms the rows returned by the SQL query back into your standard list in Python that you can manipulate and bend to your will:

db_path = 'examples/users.db'
users = seq.sqlite3(db_path, 'select * from user').to_list()
# [(1, 'Tom'), (2, 'Jack'), (3, 'Jane'), (4, 'Stephan')]]

sorted_users = seq.sqlite3(db_path, 'select * from user order by name').to_list()
# [(2, 'Jack'), (3, 'Jane'), (4, 'Stephan'), (1, 'Tom')]

When it comes to the all important task of writing things back to a database in sqlite3, we can have a range of different options:

import sqlite3
from collections import namedtuple

with sqlite3.connect(':memory:') as conn:
    conn.execute('CREATE TABLE user (id INT, name TEXT)')
    conn.commit()
    User = namedtuple('User', 'id name')
    
    # Write using a specific query
    seq([(1, 'pedro'), (2, 'fritz')]).to_sqlite3(conn, 'INSERT INTO user (id, name) VALUES (?, ?)')
    
    # Write by inserting values positionally from a tuple/list into named table
    seq([(3, 'sam'), (4, 'stan')]).to_sqlite3(conn, 'user')
    
    # Write by inferring schema from namedtuple
    seq([User(name='tom', id=5), User(name='keiga', id=6)]).to_sqlite3(conn, 'user')
    
    # Write by inferring schema from dict
    seq([dict(name='david', id=7), dict(name='jordan', id=8)]).to_sqlite3(conn, 'user')
    
    # Read everything back to make sure it wrote correctly
    print(list(conn.execute('SELECT * FROM user')))
    
    # [(1, 'pedro'), (2, 'fritz'), (3, 'sam'), (4, 'stan'), (5, 'tom'), (6, 'keiga'), (7, 'david'), (8, 'jordan')]

These all represent distinct ways of writing a list back to our metaphorical user table for this section.