Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlalchemy creating VIEW with ORM

Tags:

I created the following ORM:

from sqlalchemy import Column, Integer, String, UniqueConstraint
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


class TableA(Base):
    __tablename__ = 'table_a'

    id = Column(Integer, primary_key=True, nullable=False)
    identifier = Column(String(320))
    internal_id = Column(Integer)
    type = Column(String(32))
    time = Column(DateTime(timezone=True))
    success = Column(Boolean())
    parameters = Column(JSONB())



class TableB(Base):
    __tablename__ = 'table_b'
    __table_args__ = (UniqueConstraint('generate_action',
                                       'print_action',
                                        name='my_action_key'),)

    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    generate_action = Column(Integer)
    print_action = Column(Integer)
    generate_action = Column(Integer)
    coupon_code = Column(String(300))
    number_of_rebought_items = Column(Integer)
    seconds_between_rebuy = Column(Integer)

I'm trying to figure out how to convert the following raw SQL view to ORM syntax with sqlalchemy.

CREATE VIEW my_view AS
    SELECT table_b.id as table_b_id,
        tb.coupon_code as coupon_code,
        tb.number_of_rebought_items as number_of_rebought_items,
        ta.id as table_a_action_id,
        ta.time as time,
        ta.parameters as parameters,
    FROM table_b tb
    LEFT JOIN table_a ta on
        ta.id = tb.generate_action;  

Couldn't find any good examples out there of how to do it with ORM.
So far, my solution is to just run raw sql to create this view.

can anyone point me to the right direction, or give an example of how to create views with sqlalchemy orm?

Is it possible to create the views with metadata.create_all()

like image 714
Urban48 Avatar asked Oct 17 '16 10:10

Urban48


People also ask

How do I create a view in SQLAlchemy?

So something like this should work: >>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey >>> from sqlalchemy. sql import select >>> from sqlalchemy_views import CreateView, DropView >>> metadata = MetaData() >>> users = Table('users', metadata, ... Column('id', Integer, primary_key=True), ...

Is SQLAlchemy an ORM?

SQLAlchemy is a library that facilitates the communication between Python programs and databases. Most of the times, this library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements.

Should I use SQLAlchemy core or ORM?

If you want to view your data in a more schema-centric view (as used in SQL), use Core. If you have data for which business objects are not needed, use Core. If you view your data as business objects, use ORM. If you are building a quick prototype, use ORM.

Is SQLAlchemy better than Django ORM?

Community Size. Without any doubt, SQLAlchemy has the largest community among Python ORM frameworks. If community is important to you (and I think it should be), SQL Alchemy should be your choice. It doesn't mean though that you won't find any help for other frameworks such as Django.


1 Answers

the library sqlalchemy-utils now includes functionality for creating views, and it associates the view with sqlalchemy's metadata so that it is possible to create the view using Base.metadata.create_all

example:

# installation: pip install sqlalchemy-utils
from sqlalchemy_utils import create_view
from sqlalchemy import select, func

# engine Base & Table declaration elided for brevity

stmt = select([
    TableB.id.label('table_b_id'),
    TableB.coupon_code,
    TableB.number_of_rebought_items,
    TableA.id.label('table_a_action_id'),
    TableA.time,
    TableA.parameters
]).select_from(TableB.__table__.outerjoin(TableA, TableB.generate_action == TableA.id))

# attaches the view to the metadata using the select statement
view = create_view('my_view', stmt, Base.metadata)

# provides an ORM interface to the view
class MyView(Base):
    __table__ = view

# will create all tables & views defined with ``create_view``
Base.metadata.create_all()

# At this point running the following yields 0, as expected,
# indicating that the view has been constructed on the server 
engine.execute(select([func.count('*')], from_obj=MyView)).scalar() 
like image 51
Haleemur Ali Avatar answered Sep 19 '22 14:09

Haleemur Ali