Lazy reflecting Tables to SQLAlchemy ORM

SQLAlchemy has been in market since a long time now and is one of the best ORM’s available so far. When working on backend Frameworks such as Flask or FastAPI, we usually come across this ORM.

There are two approaches, we can use SQLAlchemy:

• Creating Schema, Tables manually using declarative_base objects and migrating them.
• Reflecting existing objects in the database using metadata.

The issue with later approach is that when there are a lot of tables to work on, initial reflect to all the tables would take a lot of time and increase your application boot time. I came up with an approach to tackle such situations wherein you need to reflect existing tables without degrading application performance.

Idea is to reflect tables and views lazily on requirements rather than loading everything at once. We do not require all the tables at once, do we ?

Since, API’s would only be querying or performing CRUD operations on a subset of tables, we have room to skip loading other tables, but also keep already reflected tables persistent.

I have created a lazy wrapper for this purpose which reflects tables on requirements only once and persist them so that you can use same objects going forward.

<span>class</span> <span>LazyDBProp</span><span>(</span><span>object</span><span>):</span>
    <span>"""This descriptor returns sqlalchemy Table class which can be used to query table from the schema """</span>
    <span>def</span> <span>__init__</span><span>(</span><span>self</span><span>)</span> <span>-></span> <span>None</span><span>:</span>
        <span>self</span><span>.</span><span>_table</span> <span>=</span> <span>None</span>
        <span>self</span><span>.</span><span>_name</span> <span>=</span> <span>None</span>
    <span>def</span> <span>__set_name__</span><span>(</span><span>self</span><span>,</span> <span>_</span><span>,</span> <span>name</span><span>):</span>
        <span>self</span><span>.</span><span>_name</span> <span>=</span> <span>name</span>
    <span>def</span> <span>__set__</span><span>(</span><span>self</span><span>,</span> <span>instance</span><span>,</span> <span>value</span><span>):</span>
        <span>if</span> <span>isinstance</span><span>(</span><span>value</span><span>,</span> <span>(</span><span>CustomTable</span><span>,</span> <span>Table</span><span>)):</span>
            <span>self</span><span>.</span><span>_table</span> <span>=</span> <span>value</span>
    <span>def</span> <span>__get__</span><span>(</span><span>self</span><span>,</span> <span>instance</span><span>,</span> <span>_</span><span>):</span>
        <span>if</span> <span>self</span><span>.</span><span>_table</span> <span>is</span> <span>None</span><span>:</span>
            <span>self</span><span>.</span><span>_table</span> <span>=</span> <span>CustomTable</span><span>(</span>
                <span>self</span><span>.</span><span>_name</span><span>,</span> <span>instance</span><span>.</span><span>metadata</span><span>,</span> <span>autoload</span><span>=</span><span>True</span><span>)</span>
        <span>return</span> <span>self</span><span>.</span><span>_table</span>
<span>class</span> <span>LazyDBProp</span><span>(</span><span>object</span><span>):</span>
    <span>"""This descriptor returns sqlalchemy Table class which can be used to query table from the schema """</span>

    <span>def</span> <span>__init__</span><span>(</span><span>self</span><span>)</span> <span>-></span> <span>None</span><span>:</span>
        <span>self</span><span>.</span><span>_table</span> <span>=</span> <span>None</span>
        <span>self</span><span>.</span><span>_name</span> <span>=</span> <span>None</span>

    <span>def</span> <span>__set_name__</span><span>(</span><span>self</span><span>,</span> <span>_</span><span>,</span> <span>name</span><span>):</span>
        <span>self</span><span>.</span><span>_name</span> <span>=</span> <span>name</span>

    <span>def</span> <span>__set__</span><span>(</span><span>self</span><span>,</span> <span>instance</span><span>,</span> <span>value</span><span>):</span>
        <span>if</span> <span>isinstance</span><span>(</span><span>value</span><span>,</span> <span>(</span><span>CustomTable</span><span>,</span> <span>Table</span><span>)):</span>
            <span>self</span><span>.</span><span>_table</span> <span>=</span> <span>value</span>

    <span>def</span> <span>__get__</span><span>(</span><span>self</span><span>,</span> <span>instance</span><span>,</span> <span>_</span><span>):</span>
        <span>if</span> <span>self</span><span>.</span><span>_table</span> <span>is</span> <span>None</span><span>:</span>
            <span>self</span><span>.</span><span>_table</span> <span>=</span> <span>CustomTable</span><span>(</span>
                <span>self</span><span>.</span><span>_name</span><span>,</span> <span>instance</span><span>.</span><span>metadata</span><span>,</span> <span>autoload</span><span>=</span><span>True</span><span>)</span>
        <span>return</span> <span>self</span><span>.</span><span>_table</span>
class LazyDBProp(object):
    """This descriptor returns sqlalchemy Table class which can be used to query table from the schema """

    def __init__(self) -> None:
        self._table = None
        self._name = None

    def __set_name__(self, _, name):
        self._name = name

    def __set__(self, instance, value):
        if isinstance(value, (CustomTable, Table)):
            self._table = value

    def __get__(self, instance, _):
        if self._table is None:
            self._table = CustomTable(
                self._name, instance.metadata, autoload=True)
        return self._table

Enter fullscreen mode Exit fullscreen mode

This class uses descriptors under the hood to persist table or view objects. I have also created a wrapper to generate dynamic class to hold these descriptor based table objects.

<span>def</span> <span>get_lazy_class</span><span>(</span><span>engine</span><span>:</span> <span>Engine</span><span>)</span> <span>-></span> <span>object</span><span>:</span>
    <span>""" Function to create Lazy class for pulling table object using SQLalchemy metadata """</span>
    <span>def</span> <span>__init__</span><span>(</span><span>self</span><span>,</span> <span>engine</span><span>:</span> <span>Engine</span><span>):</span>
        <span>self</span><span>.</span><span>metadata</span> <span>=</span> <span>MetaData</span><span>(</span><span>engine</span><span>)</span>
        <span>self</span><span>.</span><span>engine</span> <span>=</span> <span>engine</span>
    <span>def</span> <span>__getattr__</span><span>(</span><span>self</span><span>,</span> <span>attr</span><span>):</span>
        <span>if</span> <span>attr</span> <span>not</span> <span>in</span> <span>self</span><span>.</span><span>__dict__</span><span>:</span>
            <span>obj</span> <span>=</span> <span>self</span><span>.</span><span>__patch</span><span>(</span><span>attr</span><span>)</span>
        <span>return</span> <span>obj</span><span>.</span><span>__get__</span><span>(</span><span>self</span><span>,</span> <span>type</span><span>(</span><span>self</span><span>))</span>
    <span>def</span> <span>__patch</span><span>(</span><span>self</span><span>,</span> <span>attribute</span><span>):</span>
        <span>obj</span> <span>=</span> <span>LazyDBProp</span><span>()</span>
        <span>obj</span><span>.</span><span>__set_name__</span><span>(</span><span>self</span><span>,</span> <span>attribute</span><span>)</span>
        <span>setattr</span><span>(</span><span>type</span><span>(</span><span>self</span><span>),</span> <span>attribute</span><span>,</span> <span>obj</span><span>)</span>
        <span>return</span> <span>obj</span>
    <span># naming classes uniquely for different schema's </span>    <span># to avoid cross referencing </span>    <span>LazyClass</span> <span>=</span> <span>type</span><span>(</span><span>f</span><span>"LazyClass_</span><span>{</span><span>engine</span><span>.</span><span>url</span><span>.</span><span>database</span><span>}</span><span>"</span><span>,</span> <span>(),</span> <span>{})</span>
    <span>LazyClass</span><span>.</span><span>__init__</span> <span>=</span> <span>__init__</span>
    <span>LazyClass</span><span>.</span><span>__getattr__</span> <span>=</span> <span>__getattr__</span>
    <span>LazyClass</span><span>.</span><span>__patch</span> <span>=</span> <span>__patch</span>
    <span>return</span> <span>LazyClass</span><span>(</span><span>engine</span><span>)</span>
<span>def</span> <span>get_lazy_class</span><span>(</span><span>engine</span><span>:</span> <span>Engine</span><span>)</span> <span>-></span> <span>object</span><span>:</span>
    <span>""" Function to create Lazy class for pulling table object using SQLalchemy metadata """</span>

    <span>def</span> <span>__init__</span><span>(</span><span>self</span><span>,</span> <span>engine</span><span>:</span> <span>Engine</span><span>):</span>
        <span>self</span><span>.</span><span>metadata</span> <span>=</span> <span>MetaData</span><span>(</span><span>engine</span><span>)</span>
        <span>self</span><span>.</span><span>engine</span> <span>=</span> <span>engine</span>

    <span>def</span> <span>__getattr__</span><span>(</span><span>self</span><span>,</span> <span>attr</span><span>):</span>
        <span>if</span> <span>attr</span> <span>not</span> <span>in</span> <span>self</span><span>.</span><span>__dict__</span><span>:</span>
            <span>obj</span> <span>=</span> <span>self</span><span>.</span><span>__patch</span><span>(</span><span>attr</span><span>)</span>
        <span>return</span> <span>obj</span><span>.</span><span>__get__</span><span>(</span><span>self</span><span>,</span> <span>type</span><span>(</span><span>self</span><span>))</span>

    <span>def</span> <span>__patch</span><span>(</span><span>self</span><span>,</span> <span>attribute</span><span>):</span>
        <span>obj</span> <span>=</span> <span>LazyDBProp</span><span>()</span>
        <span>obj</span><span>.</span><span>__set_name__</span><span>(</span><span>self</span><span>,</span> <span>attribute</span><span>)</span>
        <span>setattr</span><span>(</span><span>type</span><span>(</span><span>self</span><span>),</span> <span>attribute</span><span>,</span> <span>obj</span><span>)</span>
        <span>return</span> <span>obj</span>

    <span># naming classes uniquely for different schema's </span>    <span># to avoid cross referencing </span>    <span>LazyClass</span> <span>=</span> <span>type</span><span>(</span><span>f</span><span>"LazyClass_</span><span>{</span><span>engine</span><span>.</span><span>url</span><span>.</span><span>database</span><span>}</span><span>"</span><span>,</span> <span>(),</span> <span>{})</span>
    <span>LazyClass</span><span>.</span><span>__init__</span> <span>=</span> <span>__init__</span>
    <span>LazyClass</span><span>.</span><span>__getattr__</span> <span>=</span> <span>__getattr__</span>
    <span>LazyClass</span><span>.</span><span>__patch</span> <span>=</span> <span>__patch</span>
    <span>return</span> <span>LazyClass</span><span>(</span><span>engine</span><span>)</span>
def get_lazy_class(engine: Engine) -> object:
    """ Function to create Lazy class for pulling table object using SQLalchemy metadata """

    def __init__(self, engine: Engine):
        self.metadata = MetaData(engine)
        self.engine = engine

    def __getattr__(self, attr):
        if attr not in self.__dict__:
            obj = self.__patch(attr)
        return obj.__get__(self, type(self))

    def __patch(self, attribute):
        obj = LazyDBProp()
        obj.__set_name__(self, attribute)
        setattr(type(self), attribute, obj)
        return obj

    # naming classes uniquely for different schema's     # to avoid cross referencing     LazyClass = type(f"LazyClass_{engine.url.database}", (), {})
    LazyClass.__init__ = __init__
    LazyClass.__getattr__ = __getattr__
    LazyClass.__patch = __patch
    return LazyClass(engine)

Enter fullscreen mode Exit fullscreen mode

Above class can be simply used as below:

<span>from</span> <span>lazy_alchemy</span> <span>import</span> <span>get_lazy_class</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span>
<span>db_engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>DB_CONNECT_STRING</span><span>)</span>
<span>lazy_db</span> <span>=</span> <span>get_lazy_class</span><span>(</span><span>db_engine</span><span>)</span>
<span>db_model</span> <span>=</span> <span>lazy_db</span><span>.</span><span>my_db_table_foo</span>
<span>query</span> <span>=</span> <span>session</span><span>.</span><span>query</span><span>(</span><span>db_model</span><span>).</span><span>filter</span><span>(</span><span>db_model</span><span>.</span><span>foo</span> <span>==</span> <span>"bar"</span><span>).</span><span>all</span><span>()</span>
<span>from</span> <span>lazy_alchemy</span> <span>import</span> <span>get_lazy_class</span>
<span>from</span> <span>sqlalchemy</span> <span>import</span> <span>create_engine</span>

<span>db_engine</span> <span>=</span> <span>create_engine</span><span>(</span><span>DB_CONNECT_STRING</span><span>)</span>
<span>lazy_db</span> <span>=</span> <span>get_lazy_class</span><span>(</span><span>db_engine</span><span>)</span>

<span>db_model</span> <span>=</span> <span>lazy_db</span><span>.</span><span>my_db_table_foo</span>
<span>query</span> <span>=</span> <span>session</span><span>.</span><span>query</span><span>(</span><span>db_model</span><span>).</span><span>filter</span><span>(</span><span>db_model</span><span>.</span><span>foo</span> <span>==</span> <span>"bar"</span><span>).</span><span>all</span><span>()</span>
from lazy_alchemy import get_lazy_class
from sqlalchemy import create_engine

db_engine = create_engine(DB_CONNECT_STRING)
lazy_db = get_lazy_class(db_engine)

db_model = lazy_db.my_db_table_foo
query = session.query(db_model).filter(db_model.foo == "bar").all()

Enter fullscreen mode Exit fullscreen mode

Once reflected, these objects can be referenced repeatedly. Reflecting only required object enhances application performance with minimal overhead.

This had me cut down application boot time from more than a minute to couple of seconds :).

If you want to implement above in your project, you can simply use my pypi package Lazy Alchemy.

I would be happy to hear you views and alternatives to this approach.

Thank you for reading this, I hope you found some helpful tips.

原文链接：Lazy reflecting Tables to SQLAlchemy ORM