SQLtills
Simple CRUD utilities for sqlalchemy
Finding myself having to duplicate code for my projects for CRUD even when using SQLAlchemy was very annoying. Thus I created some utilities which I then found annoying to have to copy paste and maintain between different projects. So in all my selfish glory I present to you SQLtills, a simple CRUD tool for sqlalchemy. Why is it called SQLtills because I am extremly horrible at naming things.
SQLtills is available on PyPi so to install simply:
pip install -U SQLtillsHow to use sqltills
For this tutorial we will be using an in-memory sqlite database as is typical for an sqlalchemy tutorial however, and this probably doesn't need to be said, we're good with whatever sqlalchemy is good with.
Imagine you have a database with two tables: authors and blogs
your sqlalchemy models would then be
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref, sessionmaker
Base = declarative_base()
class Blog(Base):
__tablename__ = "blogs"
id = Column(Integer, primary_key = True)
author_id = Column(Integer, ForeignKey("authors.id"))
title = Column(String)
blog_text = Column(String)
class Author(Base):
__tablename__ = "authors"
id = Column(Integer, primary_key = True)
name = Column(String)
blogs = relationship(Blog, backref = backref("author", uselist = False))
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.bind = engine
Base.metadata.create_all()Awesome we now have the two tables in the database and the models defined. In the same script lets write some basic CRUD you would do in an app using sqltills utilities
from sqltills import create_rows
session_maker = sessionmaker(bind = engine)
session = session_maker()
new_author = Author()
new_author.name = "Omar"
new_blog = Blog()
new_blog.title = "How SQLAlchemy is Totally Awesome"
new_blog.blog_text = "too much stuff to put into one blog so the short answer is because"
new_author.blogs.append(new_blog)
create_rows(sesion, new_author, new_blog)Easy right ! Okay imagine my application explodes and becomes medium.com now I have thousands of blogs and thousands of authors. I want to get all blogs which have the author being either Omar or Nancy
from sqltills import read_rows
results = read_rows(session, Author, filters = [
{
'name': {
'comparitor': '==',
'data': 'Omar'
},
'join': 'or'
},
{
'name': {
'comparitor':'==',
'data': 'Nancy'
}
}
])
results = results.all()
for author in results:
for blog in author.blogs:
print(blog.title)The filters argument is optional, if excluded the whole table will be included in the SQLAlchemy Query object. A parse-(like)-tree is used to build the filter expressions used in the session.query().filter() method
This filter parser can be used as well.
from sqltills import ParseTree
filters = [
{
'name': {
'comparitor': '==',
'data': 'Omar'
},
'join': 'or'
},
{
'name': {
'comparitor':'==',
'data': 'Nancy'
}
}
]
parser = ParseTree(Author, filters)
results = parser.query(session)This is a really simple and almost useless package but it allows me to have a centralized set of tools to do all my CRUD and makes my code clean(er) and easier to maintain.
