SQLite simple ORM
It is a simple and small ORM for SQLite database. An object-relational mapper (ORM) is a code library that automates the transfer of data stored in relational databases tables into objects that are more commonly used in application code.
- python 3+ (developed with 3.7)
- supports SQLite only
Install
pip install py-sqlite-orm-danidr
Usage
from sqlite_orm.database import Database
from sqlite_orm.field import IntegerField, BooleanField, TextField
from sqlite_orm.table import BaseTable
import logging
class User(BaseTable):
__table_name__ = 'users'
id = IntegerField(primary_key=True, auto_increment=True) #autoincrement int field
name = TextField(not_null=True)
active = BooleanField(not_null=True, default_value=1)
class Post(BaseTable):
__table_name__ = 'posts'
id = IntegerField(primary_key=True)
name = TextField(not_null=True)
id_user = IntegerField(foreign_key=User.id)
if __name__ == '__main__':
#logger configure:
logging.basicConfig(filename="sample.log", level=logging.DEBUG, format=('%(asctime)s: '
'%(filename)s: '
'%(levelname)s: '
'%(funcName)s(): '
'%(lineno)d: '
'%(message)s'), datefmt="%Y-%m-%d %H:%M:%S")
with Database("test.db") as db:
# create table
db.query(Post, User).create().execute()
user1 = User(id=1, name='User1')
user2 = User(id=2, name='User2')
user3 = User(id=3, name='User3')
post1 = Post(id=1, name='Post1', id_user=user1.id)
post2 = Post(id=2, name='Post2', id_user=user2.id)
post3 = Post(id=3, name='Post3', id_user=user3.id)
#insert data
db.query().insert(user1, user2, user3, post1, post2, post3).execute()
# select with columns + autojoin with fk;
print('\n=======SELECT + Auto Join=======')
for row in db.query(User, Post.name).select().join(Post).execute():
print(row)
# update
db.query(User).update(name='User3_UPDATED').filter(User.name == 'User3').execute()
print('\n=======SELECT after update=======')
for row in db.query(User, Post.name).select().join(Post).execute():
print(row)
db.query(User).delete().filter(User.name == 'User3_UPDATED').execute()
print('\n=======SELECT after delete=======')
for row in db.query(User, Post.name).select().join(Post).execute():
print(row)
# delete
db.query(User, Post).drop().execute()