[sqlalchemy] how to return an array of dicts
hello all! probably this was asked before, as I already grabbed some answers already from here and stackoverflow, but I don't really feel happy about it. problem: i have a query that it's result must go directly as a json (web / rpc usage), and I wonder if I must go from the cycle . class A(Base): attr_one = Column(..) attr_two = Column(..) attr_three = Column(..) attr_four = Column(..) attr_five = Column(..) data = session.query(A.attr_one, A.attr_four) # ok, got a query object result = data.all() # got a result proxy wanted_result = map(lambda r: r._as_dict(), data.all()) # iterate through the result proxy, calling the _as_dict() method from row_proxy. it does the job, but is that really necessary? my question is if there's any way of simplifying the query just for json purposes, since imho the need of some proxies here may be an overkill. but ... i might be wrong :) best regards, richard. -- 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. attachment: richard.vcf
Re: [sqlalchemy] how to return an array of dicts
thanks Simon! yes, i'm already using hooks so I can pass datetime, decimal, enums and so on; of course, it can help if I have to go with the result proxy. i just wonder if there's another way of doing this without having sqlalchemy to provide me helpers of proxy objects. i'm thinking about straight, performance-wize optimization :) best regards, richard. On 06/01/2015 10:00 AM, Simon King wrote: On Mon, Jun 1, 2015 at 1:51 PM, Richard Gerd Kuesters rich...@pollux.com.br wrote: hello all! probably this was asked before, as I already grabbed some answers already from here and stackoverflow, but I don't really feel happy about it. problem: i have a query that it's result must go directly as a json (web / rpc usage), and I wonder if I must go from the cycle . class A(Base): attr_one = Column(..) attr_two = Column(..) attr_three = Column(..) attr_four = Column(..) attr_five = Column(..) data = session.query(A.attr_one, A.attr_four) # ok, got a query object result = data.all() # got a result proxy wanted_result = map(lambda r: r._as_dict(), data.all()) # iterate through the result proxy, calling the _as_dict() method from row_proxy. it does the job, but is that really necessary? my question is if there's any way of simplifying the query just for json purposes, since imho the need of some proxies here may be an overkill. but ... i might be wrong :) I'm not aware of any way of getting SQLAlchemy to return plain old dicts. How are you converting your Python dicts to JSON? JSON converters often have a hook point where you can define how custom objects are converted to JSON. Here's how you could do it in Pyramid: http://pyramid.readthedocs.org/en/latest/narr/renderers.html#using-the-add-adapter-method-of-a-custom-json-renderer And the Python json.dump function accepts a default parameter that could do custom serialization. Hope that helps, Simon -- 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. attachment: richard.vcf
Re: [sqlalchemy] how to return an array of dicts
On Mon, Jun 1, 2015 at 1:51 PM, Richard Gerd Kuesters rich...@pollux.com.br wrote: hello all! probably this was asked before, as I already grabbed some answers already from here and stackoverflow, but I don't really feel happy about it. problem: i have a query that it's result must go directly as a json (web / rpc usage), and I wonder if I must go from the cycle . class A(Base): attr_one = Column(..) attr_two = Column(..) attr_three = Column(..) attr_four = Column(..) attr_five = Column(..) data = session.query(A.attr_one, A.attr_four) # ok, got a query object result = data.all() # got a result proxy wanted_result = map(lambda r: r._as_dict(), data.all()) # iterate through the result proxy, calling the _as_dict() method from row_proxy. it does the job, but is that really necessary? my question is if there's any way of simplifying the query just for json purposes, since imho the need of some proxies here may be an overkill. but ... i might be wrong :) I'm not aware of any way of getting SQLAlchemy to return plain old dicts. How are you converting your Python dicts to JSON? JSON converters often have a hook point where you can define how custom objects are converted to JSON. Here's how you could do it in Pyramid: http://pyramid.readthedocs.org/en/latest/narr/renderers.html#using-the-add-adapter-method-of-a-custom-json-renderer And the Python json.dump function accepts a default parameter that could do custom serialization. Hope that helps, Simon -- 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.
Re: [sqlalchemy] how to return an array of dicts
Thanks Jonathan, I agree with you, 100%. I have methods for that also, when I have to deal with the real objects and queries and stuff. The point, in my question, is that I have some services that are not vital to my application, but are used constantly -- and it just spits out data. I'm just trying to work on some edges that might help me (saving memory, I/O, etc). I can even put a plain old select :) This question is, kind of, general and not specific to JSON, in my point of view. best regards, richard. On 06/01/2015 01:28 PM, Jonathan Vanasco wrote: All my models inherit from an additional base class with this method: def columns_as_dict(self): return a dict of the columns; does not handle relationships return dict((col.name, getattr(self, col.name)) for col in sqlalchemy_orm.class_mapper(self.__class__).mapped_table.c) so when returning a JSON array, I just do something like; return [i.columns_as_dict() for i in results] I prefer this for several reasons: - I don't think (anymore) that sqlalchemy should return raw data. I'm not fine with it's internal constructs after departing from the recommended usage a few times and finding myself creating more problems than I solved. - I easily can override columns_as_dict() on classes to define only those columns that I want returned. - IIRC, The result_proxy/row_proxy aren't always fetched from the database, there could still be data on the connection - or you could be on an unloaded lazy-loaded relation. Running a list comprehension lets me slurp all that data, and close up the DB resources sooner. This has made pinpointing bugs a lot easier than having unloaded data accessed in a template (which often produces hard-to-figure out tracebacks as the db is nestled in the template, which is nestled in your app). There are probably a dozen better reasons for why I prefer this method , these just popped up in my head. -- 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 http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
Re: [sqlalchemy] how to return an array of dicts
argh! results = map(lambda r: dict(r.items()), session.execute(my_select).fetchall()) much simplier :) but, the question persists: is this the best approach for a raw data dictionary result query? best regards, richard. On 06/01/2015 10:22 AM, Richard Gerd Kuesters wrote: well, i can use select and zip ... don't know if this is the best approach: foo = session.execute(my_select) # my_select have the same rules as the session.query(A..., A).filter(...).order_by(...).offset(...).limit() results = map(lambda r: dict(foo.keys(), r), foo.fetchall()) any thoughts? :) On 06/01/2015 10:08 AM, Richard Gerd Kuesters wrote: thanks Simon! yes, i'm already using hooks so I can pass datetime, decimal, enums and so on; of course, it can help if I have to go with the result proxy. i just wonder if there's another way of doing this without having sqlalchemy to provide me helpers of proxy objects. i'm thinking about straight, performance-wize optimization :) best regards, richard. On 06/01/2015 10:00 AM, Simon King wrote: On Mon, Jun 1, 2015 at 1:51 PM, Richard Gerd Kuesters rich...@pollux.com.br wrote: hello all! probably this was asked before, as I already grabbed some answers already from here and stackoverflow, but I don't really feel happy about it. problem: i have a query that it's result must go directly as a json (web / rpc usage), and I wonder if I must go from the cycle . class A(Base): attr_one = Column(..) attr_two = Column(..) attr_three = Column(..) attr_four = Column(..) attr_five = Column(..) data = session.query(A.attr_one, A.attr_four) # ok, got a query object result = data.all() # got a result proxy wanted_result = map(lambda r: r._as_dict(), data.all()) # iterate through the result proxy, calling the _as_dict() method from row_proxy. it does the job, but is that really necessary? my question is if there's any way of simplifying the query just for json purposes, since imho the need of some proxies here may be an overkill. but ... i might be wrong :) I'm not aware of any way of getting SQLAlchemy to return plain old dicts. How are you converting your Python dicts to JSON? JSON converters often have a hook point where you can define how custom objects are converted to JSON. Here's how you could do it in Pyramid: http://pyramid.readthedocs.org/en/latest/narr/renderers.html#using-the-add-adapter-method-of-a-custom-json-renderer And the Python json.dump function accepts a default parameter that could do custom serialization. Hope that helps, Simon -- 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 http://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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
Re: [sqlalchemy] how to return an array of dicts
All my models inherit from an additional base class with this method: def columns_as_dict(self): return a dict of the columns; does not handle relationships return dict((col.name, getattr(self, col.name)) for col in sqlalchemy_orm.class_mapper(self.__class__).mapped_table.c) so when returning a JSON array, I just do something like; return [i.columns_as_dict() for i in results] I prefer this for several reasons: - I don't think (anymore) that sqlalchemy should return raw data. I'm not fine with it's internal constructs after departing from the recommended usage a few times and finding myself creating more problems than I solved. - I easily can override columns_as_dict() on classes to define only those columns that I want returned. - IIRC, The result_proxy/row_proxy aren't always fetched from the database, there could still be data on the connection - or you could be on an unloaded lazy-loaded relation. Running a list comprehension lets me slurp all that data, and close up the DB resources sooner. This has made pinpointing bugs a lot easier than having unloaded data accessed in a template (which often produces hard-to-figure out tracebacks as the db is nestled in the template, which is nestled in your app). There are probably a dozen better reasons for why I prefer this method , these just popped up in my head. -- 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.
Re: [sqlalchemy] how to return an array of dicts
well, i can use select and zip ... don't know if this is the best approach: foo = session.execute(my_select) # my_select have the same rules as the session.query(A..., A).filter(...).order_by(...).offset(...).limit() results = map(lambda r: dict(foo.keys(), r), foo.fetchall()) any thoughts? :) On 06/01/2015 10:08 AM, Richard Gerd Kuesters wrote: thanks Simon! yes, i'm already using hooks so I can pass datetime, decimal, enums and so on; of course, it can help if I have to go with the result proxy. i just wonder if there's another way of doing this without having sqlalchemy to provide me helpers of proxy objects. i'm thinking about straight, performance-wize optimization :) best regards, richard. On 06/01/2015 10:00 AM, Simon King wrote: On Mon, Jun 1, 2015 at 1:51 PM, Richard Gerd Kuesters rich...@pollux.com.br wrote: hello all! probably this was asked before, as I already grabbed some answers already from here and stackoverflow, but I don't really feel happy about it. problem: i have a query that it's result must go directly as a json (web / rpc usage), and I wonder if I must go from the cycle . class A(Base): attr_one = Column(..) attr_two = Column(..) attr_three = Column(..) attr_four = Column(..) attr_five = Column(..) data = session.query(A.attr_one, A.attr_four) # ok, got a query object result = data.all() # got a result proxy wanted_result = map(lambda r: r._as_dict(), data.all()) # iterate through the result proxy, calling the _as_dict() method from row_proxy. it does the job, but is that really necessary? my question is if there's any way of simplifying the query just for json purposes, since imho the need of some proxies here may be an overkill. but ... i might be wrong :) I'm not aware of any way of getting SQLAlchemy to return plain old dicts. How are you converting your Python dicts to JSON? JSON converters often have a hook point where you can define how custom objects are converted to JSON. Here's how you could do it in Pyramid: http://pyramid.readthedocs.org/en/latest/narr/renderers.html#using-the-add-adapter-method-of-a-custom-json-renderer And the Python json.dump function accepts a default parameter that could do custom serialization. Hope that helps, Simon -- 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 http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
