Note: This usage is copied from the project's Features.md
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
db = Database(":memory:")
table = db.create_table('table', [
integer('row'),
text('row2')
])
Basic
data = table.select({
"row": op == value
})
Basic (new equal) (v0.1.2)
data = table.select({
'row': value
})
List (v0.1.2)
data = table.select([
eq('row', value)
])
Any other variations are just math operators, for the list, operations can be obtained from the operators
module.
Like constraint
data = table.select([
like('row2', 'te%t')
])
Between constraint
data = table.select([
between('row', 0, 10)
])
data = table.select({'row': 0})
paginate_select
has the same effect as select
except that it's paginate.
As it stands, it only covers at-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.
offset
isn't available as a parameter yet but exists for the select
method.
New in v0.4.0: parameter only
.
only
is introduced so that you can select what column you want on a select operation, 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
The result of the select operation is usually a list/tuples of AttrDict
however, a crunch
option can be used to 'inverse' it into returning an AttrDict
where each value are list.
table.select(squash=True)
The example will do the trick.
table.update({
'row': 1 # What data should change, param name: condition
}, {
'row': 2 # New data
})
You can use update_one
and update
. The only difference is that update_one
has 3 parameters. (yes, excluding the limit
param.)
data = table.delete([
eq('row', 0)
])
table.insert({
'row': 2
})
You can now use functions on the latest version (from 0.4.0
) 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('*'))
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
)
table = db.create_table('table', [
integer('row'),
text('row2')
])
db.delete_table('table')
table = db.table('table')
table = db.reset_table('table',[
integer('row'),
text('row2')
])
table = db.rename_table('table', "some_table")
exists = db.check_table('table')
db.tables
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
# To String
csv = to_csv_string(db.table("table"))
# To file
to_csv_file(db.table("table"), "table.csv")
# To String
csv = to_csv_string(db) # list[(name, csv)]
# To directory
to_csv_file(db.table("table"), "DatabasePath")