sqlitewrapper

sqlitewrapper

A python object-oriented wrapper for sqlite, based on sqlite3

Key features

sqlitewrapper covers a wide range of queries, with a few additional features

• Easily create and run queries
• Easily intergrate queries into programs
• Database and table methods
• Thread safe

Installation

pip install sqlitewrapper

Examples

Creating a table

import sqlitewrapper
from sqlitewrapper import types

db = sqlitewrapper.open("my_database")
table = db.create("my_table", {
    "id": types.primary(autoincrement=True),
    "token": types.unique(str),
    "username": types.notnull(str),
    "tag": types.default(0),
    "desc": types.blob, # types.null(types.blob) also works
    "status": str, # types.null(str) also works
    #"joined": types.primary # types.primary() and types.primary(int) also work, but you can't have multiple primary keys if one of them is autoincrementing
})

Adding and removing columns

db = sqlitewrapper.open("my_database")
table = db.table("my_table")

table.removeColumn("desc")
table.addColumn(bio=blob)

Adding and removing rows

table.add(token="arZrJFbECw", username="user1") # id will be 1, and tag will be 0
table.add(token="xpReueejDK", username="user2") # id will be 2, and tag will be 0
table.add(token="JwVSFbpRnI", username="user3")

# can use logical queries so must be run
table.remove().where("id").eq(2).run()
table.remove().where("id").eq(2).run()

Getting data from the table

# gets all the values in the first row where the token equals 'arZrJFbECw'
result = table.get().where("token").eq("arZrJFbECw").run()

# gets all the usernames in every row
result = table.getAll("username").run()

Updating data in the table

# sets desc to null where tokens equals 'arZrJFbECw'
table.set(desc=types.null).where("token").eq("arZrJFbECw").run()

# increase tag by 1000 if tag is equal to 0 or 1
table.set(tag=types.increment(1000)).IN(0, 1).run()

Within a class

from sqlitewrapper import DatabaseObject, types

class MyDB(DatabaseObject):

    def __init__(self):
        super().__init__("my_database")

        self.create("users", {
            "user_id": types.primary(autoincrement=True),
            "username": types.notnull(str)
        })

        self.create("user_items", {
            "item_id": types.primary(autoincrement=True),
            "user_id": types.foreign('users'),
            "username": types.notnull(str)
        })

Don't await completion

This is to allow sqlitewrapper to run write queries without the main program having to wait for it's completion

NOTE: The query execution rotates attempting to execute a read query and then a write query, this is so the queue is not stacked up preventing one type of query from being executed

NOTE: When doing cleanup you can use db.waitForQueue(), which will run until the queue is empty

db = sqlitewrapper.open("my_database", await_completion=True)
table = db.table("my_table")

table.add(token="xrDIltpbBQ", username="user4")
result = table.getAll("username").run() # in most cases 'user4' won't show up