[PYTHON] Modified SQL Alchemy

SQL Alchemy, the standard O / R mapper for Pyramid and Flask, has been modified.

SQL Alchemy syntax problem

Demon remodeled

I tried to remodel it so that it can be described simply. In terms of speed, the DB connection is connected pooled using ThreadLocalStorage, so it operates at about 8 times the speed of unused. In the local environment, the speed of one select query has been increased from 8ms to 1ms.

Usage example after SQLAlchemy magic modification

# -*- coding: utf-8 -*-
from module.book import Book

# select
book1 = Book.get(1)
books = Book.objects().filter(Book.price==2160).all()

# insert
book_rye = Book(pk=None,
                title="The catcher in the rye",
                price=1000,
                publish="J. D. Salinger",
                published="")
book_rye = Book.insert(book_rye)
print(book_rye.id)

# update
book_rye.price = 1200
book_rye.save()

# delete
book_rye.delete()

book.py

# -*- coding: utf-8 -*-
from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
from module.db.base import DBBaseMixin

Base = declarative_base()


class Book(DBBaseMixin, Base):
    title = Column('title', String(200))
    price = Column('price', Integer)
    publish = Column('publish', String(200))
    published = Column('published', String(200))

    def __init__(self, pk, title, price, publish, published):
        self.pk = pk
        self.title = title
        self.price = price
        self.publish = publish
        self.published = published

base_mixin.py

# -*- coding: utf-8 -*-
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base, declared_attr
import re
from utils.db import get_db_session

Base = declarative_base()


def camel_to_snake(s):
    """
    >>> convert('CamelCase')
    'camel_case'
    >>> convert('CamelCamelCase')
    'camel_camel_case'
    >>> convert('Camel2Camel2Case')
    'camel2_camel2_case'
    >>> convert('getHTTPResponseCode')
    'get_http_response_code'
    >>> convert('get2HTTPResponseCode')
    'get2_http_response_code'
    >>> convert('HTTPResponseCode')
    'http_response_code'
    >>> convert('HTTPResponseCodeXYZ')
    'http_response_code_xyz'
    :param s: str
    :return: str
    """
    s1 = re.sub('(.)([A-Z][a-z]+)', r'\1_\2', s)
    return re.sub('([a-z0-9])([A-Z])', r'\1_\2', s1).lower()


class DBBaseMixin(object):
    id = Column('id', Integer, primary_key=True)

    def __repr__(self):
        return '<{0}.{1} object at {2}>'.format(
            self.__module__, type(self).__name__, hex(id(self)))

    @declared_attr
    def __tablename__(cls):
        return camel_to_snake(cls.__name__)

    @classmethod
    def objects(cls):
        """
        :rtype : sqlalchemy.orm.query.Query
        """
        return get_db_session().query(cls)

    @classmethod
    def session(cls):
        return get_db_session()

    @classmethod
    def get(cls, pk):
        """
        :param pk: int
        :rtype: cls
        """
        return cls.objects().get(pk)

    @classmethod
    def insert(cls, obj):
        """
        :param obj: cls
        :rtype: cls
        """
        cls.session().add(obj)
        cls.session().commit()
        return obj

    @classmethod
    def bulk_insert(cls, objs):
        """
        :param objs: list[cls]
        :rtype: list[cls]
        """
        cls.session().add_all(objs)
        cls.session().commit()
        return objs

    def delete(self):
        session = self.__class__.session()
        session.query(self.__class__).filter(self.__class__.id==self.id).delete()
        session.commit()

    def save(self):
        """
        :return:self
        """
        session = self.__class__.session()
        session.add(self)
        session.commit()
        return self

db.py

# -*- coding: utf-8 -*-
import threading
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

tls = threading.local()


def get_db_session():
    """
Generate and reuse SQL Alchemy DB sessions
    :rtype : scoped_session
    """
    if hasattr(tls, "db_session"):
        return tls.db_session

    #DB session generation
    engine = get_db_engine()
    db_session = scoped_session(sessionmaker(autocommit=False,
                                             autoflush=False,
                                             bind=engine))
    tls.db_session = db_session
    return db_session


def get_db_engine():
    db_user = 'root'
    db_host = '127.0.0.1'
    db_name = 'Flask'
    db_path = 'mysql://{}@{}/{}'.format(db_user, db_host, db_name)
    engine = create_engine(db_path, encoding='utf-8', pool_size=5)
    return engine

A pretty fatal problem with this code

It does not support updating by DB transaction processing. I think that it can be handled if session is generated with the with clause and save (transaction_session = session) can be done. Of course, commit is executed with __exit__ in the with clause.

Pseudocode for DB transactions

#Exchange prices
with commit_on_success as session:
  # select for update
  book1 = Book.get(1, transaction_session=session, for_update=True)
  book2 = Book.get(2, transaction_session=session, for_update=True)

  # exchange price
  _tmp_price = book1.price
  book1.price = book2.price
  book2.price = _tmp_price
  
  # update
  book1.save(transaction_session=session)
  book2.save(transaction_session=session)