python sqlite example

sqlite.py

import sqlite3 as db


connection = db.connect(":memory:")

# Use the built-in row factory class and auto-commit
# all data modifications.
connection.row_factory = db.Row
connection.isolation_level = None

cursor = connection.cursor()
cursor.execute("""
    create table `account`
    (
        `uid` integer primary key autoincrement,
        `name` text
    )
""")


class Account(object):
    def __init__(self, uid=None, name=None):
        self.uid = uid
        self.name = name


def fetch_all():
    result = []
    cursor.execute("select * from `account`")

    for row in cursor:
        result.append(Account(row['uid'],
                              row['name']))

    return result


def fetch(uid):
    cursor.execute("select * from `account` where `uid` = ?", [uid])
    result = cursor.fetchone()
    
    if not result:
        return None
    
    return Account(result['uid'],
                   result['name'])


def save(account):
    cursor.execute("insert into `account` (`name`) values (?)", [account.name])


if __name__ == "__main__":
    account = fetch(1)
    accounts = fetch_all()

    assert account == None
    assert accounts == []

    account = Account()
    account.name = "Example Account"

    save(account)
    account = fetch(1)

    assert account.uid == 1
    assert account.name == "Example Account"

    accounts = fetch_all()

    assert len(accounts) == 1
    assert accounts[0].uid == 1
    assert accounts[0].name == "Example Account"