GitLocker: The Coding Marketplace

Description:

knexpy 0.1.3

Knexpy

A query builder for SQLite3 based on Knexjs
Features

Transactions
Type Checking
Bulk Insert
JSON mapping

You can report bugs and discuss feature on the GitHub issues page.
For more detailed information check Readthedocs
Examples
Creating a Table
from Knexpy import Knex, Field

db = Knex("<Database File Name/Path>")

db.table(
"c",
[
Field.integer("field"),
],
not_exists=False,
)

db.table(
"t",
[
Field.varchar("field"), # Default size: 255
Field.varchar("field2"),
Field.varchar("field3"),
Field.foreign_key("field4", "c", "id"),
],
not_exists=False, # IF NOT EXISTS clause. Defaults to True
)

When creating a table the fields id, created_at, modified_at are automatically generated.
The id field is a hash based on the information of the Row

Basic Select
from Knexpy import Knex

db = Knex("<Database File Name/Path>")

query = (
db.select("id", "field", "field2", ["field3", "test"],...) # List type on select acts as an alias
.from_("t")
.where("field", "=", "12345")
.order_by("id")
)

query.query() # Returns data as JSON
query.query(False) # Returns data as tuples

Select with Subquery
from Knexpy import Knex

db = Knex("<Database File Name/Path>")

query = (
db.select("id", "field", "field2", ["field3", "test"],...)
.from_("t")
.where("field", "=", "12345")
.where(
"field4",
"=",
db.subquery().select("id").from_("c").where("field", "=", 12345),
join_type="OR", # If attribute not present defaults to "AND".
)
.order_by("id")
)

query.query() # Returns data as JSON

Insert JSON Data
from Knexpy import Knex

db = Knex("<Database File Name/Path>", type_check=True) # type_check enables type checking (duh) when inserting/updating data

db.insert_json("<table>", {
"field": "1",
"field2": "2",
"field3": "3"
})