SQLite Database

This is a simple wrapper for Python’s built-in sqlite package. It uses simple API to interact with the database in a NoSQL-like fashion.

Table of content.

• SQLite Database

  • Table of content

  • In Depth of Available Features

    • Available Filters

    • Available Constraints

    • Table-related operation

      • Select

        • Select Common Params

        • Select Methods

      • Update

      • Delete

      • Insert

      • Functions

    • Database-related operation

      • Create Table

      • Delete table

      • Accessing table

      • Reset table

      • Rename table

      • Check table

      • List tables that have been created

    • Export

      • Export Table

      • Export Database

In Depth of Available Features

Note: To test these features, make sure you copy and paste this snippet:

from sqlite_database import op
from sqlite_database.operators import eq, like, between
value = 0 # You can use almost anything.

For table and database, you can create any dummy database, but here’s one (assuming you have pasted the above snippet):

from sqlite_database import Database, integer, text
from sqlite_database import this
db = Database(":memory:")
my_table = db.create_table('my_table', [
    integer('row'),
    text('row2')
])

Available Filters

this:

data = table.select({
    "row": this == value
})

Key-Value:

data = table.select({
    'row': value
})

List of functions:

data = table.select([
    eq('row', value)
])

Any other variations are just math operators. For the list, operations can be obtained from the operators module.

Available Constraints

Like constraint:

data = table.select([
    like('row2', 'te%t')
])

Between constraints:

data = table.select([
    between('row', 0, 10)
])

Table-related operation

Select

To retrieve contents of a table, you can use .select* methods such as .paginate_select, .select, and .select_one. See Select Methods.

Select Common Params

offset: returns at specific position

only: you can use only to select which columns you want to return and use it as usual. However, only can also be used on functions too.

So, for example:

person = table.select(only=('name', 'age'))

is the same as

select name, age from table

On the other hand, a function can also be used in only:

table.select(only=count('*'))

which achieves the same as

select COUNT(*) from table

order: you can change the return order from asc and desc.

squash: The result of the select operation is usually a list/tuples of Row however, a squash option can be used to ‘inverse’ it into returning an Row where each value are list.

table.select(squash=True)

The example will do the trick.

Select Methods

.paginate_select: yields a selection page, useful when there’s a ton of data and you want to limit how much it returns at a time.

As it stands, it only covers length parameters for an iteration turn. This means it loops, selects (based on offset and limit), checks if the length is equal to the length parameter, yields, and increments the offset by the length.

Example:

several_people = table.paginate_select(
    {"age": this >= 20}, # Select people with age more or equal than 20
    length=20, # Each page has 20 columns
)

for page in several_people:
    print(page)

There’s also page param so you can skip directly to which page to return.

.select: selects, described in Select

Example:

persons = table.select() # This returns everything
persons_age = table.select(only=('age',)) # This returns everything but only contains the 'age' column.

See Select Common Params

.select_one: Like .select() but returns one item (Row)

Update

To change columns on matching conditions, use either update() or update_one. update_on updates specifically one item.

table.update({
    'row': 1 # What data should change, param name: condition
}, {
    'row': 2 # New data
})

Delete

You can delete row(s).

data = table.delete([
    eq('row', 0)
])

Most of the parameters are conditions, limit, and order. .delete also has .delete_one

Insert

To push data, you can use .insert

table.insert({
    'row': 2
})

use .insert_multiple if your data has many things.

Functions

You can now use functions. However, for now, it’s limited to only .select() queries. However, practically, you can use any functions defined.

from sqlite_database.functions import Function
count = Function('COUNT')
person.select(only=count('*'))

Database-related operation

You can use the cursor method to use raw SQLite Connection or access the sql property to access the connection.

For connecting to the database, you can pass any regular keyword args or args for connect (see sqlite3.connect)

Create Table

You can create a table, yes. By default, available types defined are real, text, integer, and blob

table = db.create_table('table', [
    integer('row'),
    text('row2')
])

Delete table

db.delete_table('table')

Accessing table

table = db.table('table')

Reset table

table = db.reset_table('table',[
    integer('row'),
    text('row2')
])

Rename table

table = db.rename_table('table', "some_table")

Check table

if db.check_table('table'): # Table exists
    # do something

List tables that have been created

db.tables

Export

You can export the database/table to CSV. For now, import functionality will not be added.

Make sure to add this line

from sqlite_database.csv import to_csv_file, to_csv_string

You can use to_csv_string rather than to_csv_file, all you need is to pass the table or database.

Note: The return type for the database is a tuple indicating (name, csv), and by that, to_csv_file would make sure that filename passed is a directory and the content will be all table files. E.g: /path/database/table.csv

Export Table

# To String
csv = to_csv_string(db.table("table"))

# To file
to_csv_file(db.table("table"), "table.csv")

Export Database

# To String
csv = to_csv_string(db) # list[(name, csv)]

# To directory
to_csv_file(db.table("table"), "DatabasePath")