Light python wrapper classes for some popular databases.
They provide a simple interface for building, reading, deleting and updating databases.
Currently, supported databases are sqlite3
, firebase
and mongodb
.
From root of this directory:
pip install .
will install pydatabase
package and its dependency packages: firebase_admin
and pymongo[srv]
The example scripts also use tqdm
, so you will need to install it in order to run them.
-
SQLite3: You must have an existing sqlite3 database (it can be empty).
-
Firebase: You must have a firebase account and database setup, as well as a service key json that links to it. The database can have zero collections in it.
-
MongoDB: You will need to have a mongodb server running, either on Atlas or your local machine. If Atlas, you will require the connection string.
Database | Type | Multi-field queries | Host | Mobile-friendly |
---|---|---|---|---|
SQLite3 | relational | ✔️ | local + cloud | |
MongoDB | document | ✔️ | local + cloud | |
Firebase | document | cloud only | ✔️ |
- SQLite3 examples
- Firebase examples
- MongoDB examples
- Populate a MongoDB database from an SQLite3 database
Import the library and initialise the db object:
from pydatabase.sqlite_interface import SqliteInterface
db = SqliteInterface('../data/shop.db')
db.insert_row(entry={"name":ball,"cost":20.0},table="items",field_map={"name":"name","price":"cost"})
Display all item names in db:
queries = db.query(table='items',display_fields=('name',) )
Display all item names and prices with price less than 50:
queries = db.query(table='items',display_fields=('name','price'),query='price<50')
Display items with a matching category:
queries = db.query(table='items',display_fields=('name','category'),query='category="Sporting Goods"')
Display items with a double criteria for query:
queries = db.query(table='items',display_fields=('name','category'),query='(category="Sporting Goods")&(price>30)')
Display all data in the database:
queries = db.query(table='items')
Display unique categories:
queries = db.get_distinct_values(table='items', field='categories')
List fields in the items
table:
db.fields['items']
Return the primary key for the items
table:
db.primary_key['items']
delete all sporting goods:
db.delete_rows(table='items',query='categories="Sporting Goods"')
Clear all rows from a table:
db.clear_table(table="items")
Delete a table from the db:
db.clear_table(table="items")
Update the price and stocked status of the basketball
item:
update = {"price": 5.0, "stocked": 1}
db.update_fields(table="items", update=update, query='(name="Basketball")')
Import the library and initialise the db object:
from pydatabase.firebase_interface import FirebaseInterface
db = FirebaseInterface('path/to/your/service-key.json')
A collection will be created after the first new document is added.
Assume a toy dataset:
[{"category": "Sporting Goods", "price": 49.99, "stocked": true, "name": "Football"}, {"category": "Sporting Goods", "price": 9.99, "stocked": true, "name": "Baseball"}, {"category": "Sporting Goods", "price": 29.99, "stocked": false, "name": "Basketball"}, {"category": "Electronics", "price": 99.99, "stocked": true, "name": "iPod Touch"}, {"category": "Electronics", "price": 399.99, "stocked": false, "name": "iPhone 5"}, {"category": "Electronics", "price": 199.99, "stocked": true, "name": "Nexus 7"}]
Upload a collection from a json:
db.upload_collection( data, collection='items' )
Upload a collection, using the contents of the name
field as the document id:
db.upload_collection( data, collection='items', id='name' )
Only upload name and price fields for each document, and map the field name
in the input data to item_name
in the db:
db.upload_collection( data, collection='items', id='name' , {"name":"item_name","price":"price"} )
Upload a single document using the name
"ball" as the index:
db.upload_document( {"name":"ball","price",5}, collection='items', id='name' )
Upload a single document, dropping the price
field:
db.upload_document( {"name":"ball","price",5}, collection='items', id='name', field_map={"name":"name"} )
Download an entire collection:
docs = db.download_collection(collection='items')
Download documents by id:
docs = db.download_documents_by_id(collection='items',doc_ids=['Baseball','Basketball'])
Read all documents with a rating greater than 3.9:
docs = db.query(collection='items', query=('rating','>',3.9))
Read names only of all documents with a rating greater than 3.9:
docs = db.query(collection='items', query=('rating','>',3.9), display_fields=['name'])
Output queries as a list instead of a dictionary:
docs = db.query(collection='items', query=('rating','>',3.9), output_dict=False)
Delete the items
collection:
db.delete_collection(collection='items')
Delete all documents that have the category
"Sporting Goods":
db.delete_fields_by_query(collection='items', query_tuple=('category','==','Sporting Goods'))
Delete the documents with id 'Baseball' and 'Basketball':
db.delete_documents_by_id(collection='items',doc_ids=['Baseball','Basketball'])
Delete the rating
and price
fields (keep the documents) from all documents that have the category "Sporting Goods":
db.delete_fields_by_query(collection='items', fields=['rating','price'], query_tuple=('category','==','Sporting Goods'))
Delete the price
and category
fields from all documents (keep the documents):
db.delete_fields_all_docs(collection='places',fields=['price','category'])
Update the stock
and price
fields of all documents that have the category
"Sporting Goods":
db.update_fields_by_query(collection='items',query_tuple=('category','==','Sporting Goods'),update_dict={'stock':False,'price':5.0})
Update the price of specific id's, e.g. basketball's and baseball's, to be $7:
db.update_fields_by_id(collection='items', doc_ids=['basketball','baseball'],update_dict={'price': 7})
Import the library and initialise the db object:
from pydatabase.mongo_interface import MongoInterface
db = MongoInterface('shopdb')
A collection will be created after the first new document is added.
Upload a collection from a json, using position in list or dictionary key as primary index:
db.upload_collection( data, collection='items' )
Only upload name
and price
fields for each document, and map name
in the input data to item_name
in the db:
db.upload_collection( data, collection='items' , {"name":"item_name","price":"price"} )
Use autogenerated ObjectId
's or _id
keys in the json as the primary index:
db.upload_collection( data, collection='items',use_index_as_id=False )
Upload a single document (with auto-generate ObjectId
as primary index):
db.upload_document( {"name":"ball","price",5}, collection='items' )
Upload a single document with a custom primary index (e.g. 20
):
db.upload_document( {"_id":20,"name":"ball","price",5}, collection='items' )
Map the field name
to item_name
, and drop the price
field:
db.upload_document( {"name":"ball","price",5}, collection='items', field_map={"name":"item_name"} )
Download a collection:
docs = db.download_collection(collection='items')
Download the names
and prices
only from a collection:
docs = db.download_collection(collection='items',display_fields=['name','price'] )
Sort by increasing price
and download as list:
docs = db.download_collection(collection='items',sort_by=['price'],output_dict=False)
Download documents that have ObjectId
's as primary index, using strings:
docs = db.download_documents_by_id('user', ["6129b5ff77","6129b7e377"], convertObjectId=True)
Download documents by ObjectId
:
from bson.objectid import ObjectId
docs = db.download_documents_by_id('user', [ObjectId("6129b5ff77"),ObjectId("6129b7e377")], convertObjectId=False)
Download all users who's age
is greater than 29, and display their name
and age
:
db.query(collection='user',query_dict= {"age": {"$gt": 29}},display_fields=['name','age'])
Sort by decreasing age:
db.query(collection='user',query_dict= {"age": {"$gt": 29}},display_fields=['name','age'],sort_field='age',ascend=False)
Delete collection:
db.delete_collection(collection='users')
Delete all documents in "user" collection that have an age
greater than 29:
db.update_fields_by_query(collection='users',query_dict={"age":{"$gt":29}})
Delete documents by their id (i.e. primary index):
db.delete_documents_by_id(collection='user',doc_ids=["6129b7e3774460dccb16f7ff"],convertObjectId=True)
Update the employed status of a user document by id:
db.update_fields_by_id(collection='user',doc_ids=["61274b65","612752"],update_dict={"$set":{"employed":True}},convertObjectId=True)
Update the employed
status of users aged over 29 to be True:
db.update_fields_by_query(collection='user',query_dict={"age":{"$gt":29}}, update_dict={"$set":{"employed":True}})
# import libraries for mongo and sqlite
from pydatabase.mongo_interface import MongoInterface
from pydatabase.sqlite_interface import SqliteInterface
# initialise db object's
db_mongo = MongoInterface('shopdb')
db_sqlite = SqliteInterface('../data/shop.db')
# read data from sqlite database
data = db_sqlite.query(table='items',output_json=True)
# upload as collection to mongo database
db_mongo.upload_collection(data, collection='items2', use_index_as_id=False)