I'm having some trouble with the Postgresql function row_to_json. It's
possible to use it on an entire table to turn each row from the table into
a JSON object, like select row_to_json(t) as j from some_table as t. You
can also use a subselect, and that's where the trouble lies.
I want to generate this SQL, or the equivalent:
update sample set query_args=(select row_to_json(s) from (select
ARRAY[campaign] as campaign, ARRAY[keyword] as keyword from sample as
inner_sample where inner_sample.id = sample.id) as s);
Here's my table definition and attempt:
import sqlalchemy as sa
from sqlalchemy.dialects.postgres import array, JSON
engine = sa.create_engine("postgres://foo") # let engine be a valid postgres
engine
meta = sa.MetaData()
sample_table = sa.Table('sample', meta,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('campaign', sa.Unicode),
sa.Column('keyword', sa.Unicode),
sa.Column('query_args', JSON))
sample_table.create(engine)
# put in some sample rows
engine.execute(sample_table.insert(), [{'campaign': 'one', 'keyword': 'foo'},
{'campaign': 'two', 'keyword': 'bar'}])
inner_sample = sample_table.alias('inner_sample')
s = sa.select([array([inner_sample.c.campaign]).label('campaign'),
array([inner_sample.c.keyword]).label('keyword')]).where(inner_sample.c.id ==
sample_table.c.id).correlate(sample_table).alias('s')
print s.compile(engine)
# SELECT ARRAY[inner_sample.campaign] AS campaign, ARRAY[inner_sample.keyword]
AS keyword
# FROM sample AS inner_sample, sample
# WHERE inner_sample.id = sample.id
The FROM clause isn't quite right, but that will take care of itself later
thanks to the correlate call. This is the subselect that I want to turn
into JSON using row_to_json, and then store into the query_args column.
j = sa.func.row_to_json(s).select().select_from(s).as_scalar()
print sample_table.update().values(query_args=j).compile(db_session.bind)
# UPDATE sample SET query_args=(SELECT row_to_json(SELECT
ARRAY[inner_sample.campaign] AS campaign, ARRAY[inner_sample.keyword] AS keyword
# FROM sample AS inner_sample
# WHERE inner_sample.id = sample.id) AS row_to_json_1
# FROM (SELECT ARRAY[inner_sample.campaign] AS campaign,
ARRAY[inner_sample.keyword] AS keyword
# FROM sample AS inner_sample
# WHERE inner_sample.id = sample.id) AS s)
Now the FROM clause in the subselect is right, but the subselect gets
repeated inside the row_to_json call. The alias 's' does correctly get
rendered; how can I tell SQLAlchemy that row_to_json is a function that can
accept a selectable? It's basically right except the row_to_json call
should just be row_to_json(s).
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
