• Stars
    star
    178
  • Rank 214,989 (Top 5 %)
  • Language
    Python
  • License
    MIT License
  • Created over 8 years ago
  • Updated 10 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

The missing SQLAlchemy ORM interface.

sqlservice

version build coveralls license

The missing SQLAlchemy ORM interface.

Links

Introduction

So what exactly is sqlservice and what does "the missing SQLAlchemy ORM interface" even mean? SQLAlchemy is a fantastic library and features a superb ORM layer. However, one thing SQLAlchemy lacks is a unified interface for easily interacting with your database through your ORM models. This is where sqlservice comes in. It's interface layer on top of SQLAlchemy's session manager and ORM layer that provides a single point to manage your database connection/session, create/reflect/drop your database objects, and easily persist/destroy model objects.

Features

This library is meant to enhance your usage of SQLAlchemy. SQLAlchemy is great and this library tries to build upon that by providing useful abstractions on top of it.

  • Sync and asyncio database clients to manage ORM sessions with enhanced session classes.
  • Base class for a declarative ORM Model that makes updating model columns and relationships easier and converting to a dictionary a breeze.
  • Decorator-based event register for SQLAlchemy ORM events that can be used at the model class level. No need to register the event handler outside of the class definition.
  • And more!

Requirements

Quickstart

First, install using pip:

pip install sqlservice

Then, define some ORM models:

import re

from sqlalchemy import Column, ForeignKey, orm, types

from sqlservice import declarative_base, event


Model = declarative_base()

class User(Model):
    __tablename__ = "user"

    id = Column(types.Integer(), primary_key=True)
    name = Column(types.String(100))
    email = Column(types.String(100))
    phone = Column(types.String(10))

    roles = orm.relation("UserRole")

    @event.on_set("phone", retval=True)
    def on_set_phone(self, value):
        # Strip non-numeric characters from phone number.
        return re.sub("[^0-9]", "", value)

class UserRole(Model):
    __tablename__ = "user_role"

    id = Column(types.Integer(), primary_key=True)
    user_id = Column(types.Integer(), ForeignKey("user.id"), nullable=False)
    role = Column(types.String(25), nullable=False)

Next, configure the database client:

from sqlservice import AsyncDatabase, Database

db = Database(
    "sqlite:///db.sql",
    model_class=Model,
    isolation_level="SERIALIZABLE",
    echo=True,
    echo_pool=False,
    pool_size=5,
    pool_timeout=30,
    pool_recycle=3600,
    max_overflow=10,
    autoflush=True,
)

# Same options as above are supported but will default to compatibility with SQLAlchemy asyncio mode.
async_db = AsyncDatabase("sqlite:///db.sql", model_class=Model)

Prepare the database by creating all tables:

db.create_all()
await async_db.create_all()

Finally (whew!), start interacting with the database.

Insert a new record in the database:

user = User(name='Jenny', email=jenny@example.com, phone='555-867-5309')
with db.begin() as session:
    session.save(user)

async with db.begin() as session:
    await session.save(user)

Fetch records:

session = db.session()
assert user is session.get(User, user.id)
assert user is session.first(User.select())
assert user is session.all(User.select().where(User.id == user.id)[0]

Serialize to a dict:

assert user.to_dict() == {
    "id": 1,
    "name": "Jenny",
    "email": "[email protected]",
    "phone": "5558675309"
}

assert dict(user) == user.to_dict()

Update the record and save:

user.phone = '222-867-5309'
with db.begin() as session:
    session.save(user)

async with async_db.begin() as session:
    await session.save(user)

Upsert on primary key automatically:

other_user = User(id=1, name="Jenny", email="[email protected]", phone="5558675309")
with db.begin() as session:
    session.save(other_user)
assert user is other_user

For more details, please see the full documentation at http://sqlservice.readthedocs.io.

More Repositories

1

pydash

The kitchen sink of Python utility libraries for doing "stuff" in a functional way. Based on the Lo-Dash Javascript library.
Python
1,291
star
2

cacheout

A caching library for Python
Python
415
star
3

fnc

Functional programming in Python with generators and other utilities.
Python
247
star
4

hashfs

A content-addressable file management system for Python.
Python
212
star
5

pushjack

Push notifications for APNS (iOS) and GCM (Android).
Python
129
star
6

omdb.py

Python wrapper around OMDb API (Open Movie Database): http://omdbapi.com
Python
98
star
7

alchy

The declarative companion to SQLAlchemy
Python
75
star
8

shelmet

A shell power-up for working with the file system and running subprocess commands
Python
74
star
9

flask-pushjack

Flask extension for push notifications on APNS (iOS) and GCM (Android)
Python
70
star
10

verify

A painless assertion and validation library for Python.
Python
66
star
11

zulu

A drop-in replacement for native Python datetimes that embraces UTC.
Python
60
star
12

yummly.py

Python library for Yummly API: https://developer.yummly.com
Python
28
star
13

flask-logconfig

Flask extension for configuring Python logging module
Python
23
star
14

flask-alchy

Flask extension for alchy, the SQLAlchemy enhancement library
Python
17
star
15

logconfig

Simple helper moudle for configuring Python logging
Python
10
star
16

flask-hashfs

Flask extension for HashFS, a content-addressable file management system
Python
7
star
17

pixif

Python script for moving/copying photos from a directory and saving them based on EXIF tag data.
Python
6
star
18

ladder

HTTP client wrapper with URL generation via object notation and argument passing
Python
6
star
19

schemable

Schema validation and parsing library
Python
3
star
20

blog

Source code for my blog
Python
3
star
21

carafe

Flask application factory with extensions geared towards JSON APIs
Python
2
star