On 03/22/2016 05:37 AM, Andy S wrote:
I'm stuck with the need to generate a query of the form like:
|
SELECT
a.id,
b.name,
jr.*
FROM
a,b outer join jsonb_populate_recordset(b.jrs)asjr(bid
numeric,name varchar)on (TRUE)
WHERE
a.id =b.a_id
the alias involves building out a whole new construct. see below.
from sqlalchemy.sql import functions
from sqlalchemy.sql.selectable import FromClause, Alias
from sqlalchemy.sql.elements import ColumnClause
from sqlalchemy.ext.compiler import compiles
class FunctionColumn(ColumnClause):
def __init__(self, function, name, type_=None):
self.function = self.table = function
self.name = self.key = name
self.type = type_
self.is_literal = False
@property
def _from_objects(self):
return []
def _make_proxy(self, selectable, name=None, attach=True,
name_is_truncatable=False, **kw):
if self.name == self.function.name:
name = selectable.name
else:
name = self.name
co = ColumnClause(name, self.type)
co.key = self.name
co._proxies = [self]
if selectable._is_clone_of is not None:
co._is_clone_of = \
selectable._is_clone_of.columns.get(co.key)
co.table = selectable
co.named_with_table = False
if attach:
selectable._columns[co.key] = co
return co
@compiles(FunctionColumn)
def _compile_function_column(element, compiler, **kw):
if kw.get('asfrom', False):
return "(%s).%s" % (
compiler.process(element.function, **kw),
compiler.preparer.quote(element.name)
)
else:
return element.name
class PGAlias(Alias):
pass
@compiles(PGAlias)
def _compile_pg_alias(element, compiler, **kw):
text = compiler.visit_alias(element, **kw)
if kw['asfrom']:
text += "(%s)" % (
", ".join(
"%s %s" % (
col.name,
compiler.visit_typeclause(col)) for col in
element.element.c
)
)
return text
class ColumnFunction(functions.FunctionElement):
__visit_name__ = 'function'
@property
def columns(self):
return FromClause.columns.fget(self)
def _populate_column_collection(self):
for name, type_ in self.column_names:
self._columns[name] = FunctionColumn(self, name, type_)
def alias(self, name):
return PGAlias(self, name)
from sqlalchemy import * # noqa
from sqlalchemy.orm import * # noqa
from sqlalchemy.ext.declarative import declarative_base # noqa
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.dialects import postgresql
class jsonb_to_recordset_func(ColumnFunction): # noqa
name = 'jsonb_to_recordset'
column_names = [('goods_id', Integer()), ('quantity', Integer())]
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
supplemental_items = Column(JSONB)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
s.add(A(supplemental_items=[{"goods_id": 5, "quantity": 10}]))
s.commit()
jr = jsonb_to_recordset_func(A.supplemental_items).alias('j')
q = s.query(A.id, jr.c.goods_id, jr.c.quantity)
print q.all()
output works:
SELECT a.id AS a_id, j.goods_id AS j_goods_id, j.quantity AS j_quantity
FROM a, jsonb_to_recordset(a.supplemental_items) AS j(goods_id INTEGER,
quantity INTEGER)
2016-03-22 11:29:49,946 INFO sqlalchemy.engine.base.Engine {}
[(1, 5, 10)]
|
First, it's not clear to me how to construct a dynamically defined
RECORD as 'jr(bid numeric, name varchar)' so 'jr' becomes a table
name that could be referenced later on
Then it's not clear to me how to construct a table-like object of a
function call that one can reference in query() by it's name ('jr'
in this case).
Is it possible with SQLAlchemy?
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
