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()
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), ...
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.
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.
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.
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With