[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] Support for pysqlcipher3
Alright, thanks! I would try to submit the PR. But it does look like a transitional package to me. How do we generally handle such upstream changes anyway? On Monday, June 1, 2015 at 9:06:20 AM UTC+5:30, Michael Bayer wrote: On 5/31/15 8:58 PM, Fayaz Yusuf Khan wrote: On Sunday 31 May 2015 11:06:32 AM Mike Bayer wrote: pysqlcipher is right here: http://docs.sqlalchemy.org/en/rel_1_0/dialects/sqlite.html#module-sqlalchemy .dialects.sqlite.pysqlcipher SQLAlchemy supports Python 2K and Python 3K in place though the above dialect has probably not been tested on Py3K. pysqlcipher does not support Py3K. https://github.com/leapcode/pysqlcipher/issues/3 It's a known issue. you can send in pysqlcipher3 to create_engine() using the dbapi argument: import pysqlcipher3 e = create_engine(sqlite+pysqlcipher:///file.db, dbapi=pysqlcipher3) feel free to submit a PR that returns this DBAPI from the dbapi method under py3k: https://bitbucket.org/zzzeek/sqlalchemy/src/0766c80b9c02fdbad3203835ab850ad690f4c03b/lib/sqlalchemy/dialects/sqlite/pysqlcipher.py?at=master#cl-82 -- 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.
[sqlalchemy] Inheritance and hydrid expression
Hello, I'm trying to do create a class with joined inheritance. Some have their name in their table and have to access it from another table using a join. I did this mixin and what though it creates an expression error because the SQL generated is not the good one. class NameMixin(object): @declared_attr.cascading def name(cls): if cls.__tablename__ != TABLE_NAME_THROUGH_RELATION: return Column(String) @hybrid_property def name(self): return self.basis.name @name.expression def name(cls): return select([OtherTable.__table__.c.name]).select_from( cls.__table__.join(OtherTable.__table__) ) @name.setter def name(self, new_name): self.other_table.name = new_name return name With this Mixin a Column name is created in the base which I don't want. I got this error when doing a query on the name: sqlalchemy.exc.ProgrammingError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (psycopg2.ProgrammingError) column name of relation subclass_with_name does not exist How can I tell sqlalchemy that the base class should not have the column and instead make the query using the subtable in different way. I you guys have any idea, it's welcome (even if it's not the method I'm trying to do). The simpler method would be to replicate the attribute and not do a join, though it's less elegant. Thanks for your help, Alexis. -- 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 create table from select statements in sqlalchemy
I would like create table as select statement http://www.postgresql.org/docs/8.1/static/sql-createtableas.html On 1 June 2015 at 20:48, Mike Bayer mike...@zzzcomputing.com wrote: On 6/1/15 9:39 AM, Ranjith Ramachandra wrote: I am using sqlalchemy's core features do write some abstraction layer. The layer itself needs to be able to create tables from select statements. Sample code: metadata = MetaData(bind=engine) table = Table(table_name, metadata, autoload=True, autoload_with=engine) s = select(table).where(table.c.column_1 10) Now what I want to be able to is create a new table from the select statement above. How can I do it? can you be more specific? What DDL would you like to emit ? -- 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. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/lKDkX68fOqI/unsubscribe. To unsubscribe from this group and all its topics, 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. -- 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.
[sqlalchemy] Re: ...and MVC
On Sunday, May 31, 2015 at 4:48:09 AM UTC-4, Fayaz Yusuf Khan wrote: I do the former in cases which involve very long transactions. (Batch uploads and processing. I'm counting batch and background tasks as part of the controller layer here.) I manage transactions manually in the controllers too. I used multiple transactions in the controller a for a while for long transactions, but then decided to offload all that stuff onto another process (via Celery). Now I only use multiple transactions for batch processing where not every component involved in the transaction supports savepoints. -- 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
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 create table from select statements in sqlalchemy
On 6/1/15 9:39 AM, Ranjith Ramachandra wrote: I am using sqlalchemy's core features do write some abstraction layer. The layer itself needs to be able to create tables from select statements. Sample code: metadata = MetaData(bind=engine) table = Table(table_name, metadata, autoload=True, autoload_with=engine) s = select(table).where(table.c.column_1 10) Now what I want to be able to is create a new table from the select statement above. How can I do it? can you be more specific? What DDL would you like to emit ? -- 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.
Re: [sqlalchemy] Inheritance and hydrid expression
On 6/1/15 5:56 AM, Alexis Benoist wrote: Hello, I'm trying to do create a class with joined inheritance. Some have their name in their table and have to access it from another table using a join. I did this mixin and what though it creates an expression error because the SQL generated is not the good one. class NameMixin(object): @declared_attr.cascading def name(cls): if cls.__tablename__ != TABLE_NAME_THROUGH_RELATION: return Column(String) @hybrid_property def name(self): return self.basis.name @name.expression def name(cls): return select([OtherTable.__table__.c.name]).select_from( cls.__table__.join(OtherTable.__table__) ) @name.setter def name(self, new_name): self.other_table.name = new_name return name With this Mixin a Column name is created in the base which I don't want. I got this error when doing a query on the name: sqlalchemy.exc.ProgrammingError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (psycopg2.ProgrammingError) column name of relation subclass_with_name does not exist How can I tell sqlalchemy that the base class should not have the column and instead make the query using the subtable in different way. you put one version of the def name on the base class and another on the subclass. but also, hybrids don't take care of the FROM clause of your SELECT. If the hybrid attribute assumes a set of tables are present then you need to say query.select_from() and/or query.join() to add the FROM expressions that you want. -- 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 create table from select statements in sqlalchemy
On 6/1/15 11:19 AM, Ranjith wrote: I would like create table as select statement http://www.postgresql.org/docs/8.1/static/sql-createtableas.html OK but also you want to use bound parameters, apparently. I've answered the question at http://stackoverflow.com/a/30577608/34549. On 1 June 2015 at 20:48, Mike Bayer mike...@zzzcomputing.com mailto:mike...@zzzcomputing.com wrote: On 6/1/15 9:39 AM, Ranjith Ramachandra wrote: I am using sqlalchemy's core features do write some abstraction layer. The layer itself needs to be able to create tables from select statements. Sample code: metadata = MetaData(bind=engine) table = Table(table_name, metadata, autoload=True, autoload_with=engine) s = select(table).where(table.c.column_1 10) Now what I want to be able to is create a new table from the select statement above. How can I do it? can you be more specific? What DDL would you like to emit ? -- 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 a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/lKDkX68fOqI/unsubscribe. To unsubscribe from this group and all its topics, 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.
[sqlalchemy] How to create table from select statements in sqlalchemy
I am using sqlalchemy's core features do write some abstraction layer. The layer itself needs to be able to create tables from select statements. Sample code: metadata = MetaData(bind=engine) table = Table(table_name, metadata, autoload=True, autoload_with=engine) s = select(table).where(table.c.column_1 10) Now what I want to be able to is create a new table from the select statement above. How can I do it? -- 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
[sqlalchemy] Re: How to create table from select statements in sqlalchemy
I have also asked the question at http://stackoverflow.com/questions/30575111/how-to-create-a-new-table-from-select-statement-in-sqlalchemy On Monday, 1 June 2015 19:09:13 UTC+5:30, Ranjith Ramachandra wrote: I am using sqlalchemy's core features do write some abstraction layer. The layer itself needs to be able to create tables from select statements. Sample code: metadata = MetaData(bind=engine) table = Table(table_name, metadata, autoload=True, autoload_with=engine) s = select(table).where(table.c.column_1 10) Now what I want to be able to is create a new table from the select statement above. How can I do it? -- 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] SQLAlchemy quoting of table names - Can't redefine 'quote' or 'quote_schema' arguments
Nailed it, removing quote;False and setting the quoting characters to []
did the job. Huge thanks for the help,
On Friday, 29 May 2015 09:43:27 UTC+10, Michael Bayer wrote:
On 5/28/15 7:13 PM, Adam Darwin wrote:
Thanks for the response Michael as far as I can tell there is no
reflective step in created the declarative_base the code is as posted.
OK then you do not need the extend_existing flag, below is a test case
which illustrates that even with this flag, the quote flag is honored, as
long as nothing else in the program already made a Table object with that
same name on the same MetaData. If you can modify this test to show what
you're doing, that will help me to understand where to go with this.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import CreateTable
Base = declarative_base()
class RiskAggregationGroup(Base):
__tablename__ = 'RISK_AGGREGATION_GROUP'
__table_args__ = {'quote': False, 'extend_existing': True}
id = Column(Integer, name='id_risk_agg', primary_key=True)
name = Column(String(50), name='nm_risk_agg')
description = Column(String(100), name='tx_desc')
e = create_engine(sybase://)
# I don't have a sybase database handy, so here's a print
print(CreateTable(RiskAggregationGroup.__table__).compile(e))
If I try enabling quotes it fails:
ProgrammingError: (pyodbc.ProgrammingError) ('42000', [42000]
[Sybase][ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near
'RISK_AGGREGATION_GROUP.'.\n (102) (SQLExecDirectW)) [SQL: u'SELECT
RISK_AGGREGATION_GROUP.id_risk_agg AS RISK_AGGREGATION_GROUP_i_1,
RISK_AGGREGATION_GROUP.nm_risk_agg AS RISK_AGGREGATION_GROUP_n_2,
RISK_AGGREGATION_GROUP.tx_desc AS RISK_AGGREGATION_GROUP_t_3,
RISK_AGGREGATION_GROUP.saves_pl AS RISK_AGGREGATION_GROUP_s_4,
RISK_AGGREGATION_GROUP.id_market_making AS RISK_AGGREGATION_GROUP_i_5,
RISK_AGGREGATION_GROUP.id_bond_trading AS RISK_AGGREGATION_GROUP_i_6,
RISK_AGGREGATION_GROUP.id_trader AS RISK_AGGREGATION_GROUP_i_7 \nFROM
RISK_AGGREGATION_GROUP \nWHERE RISK_AGGREGATION_GROUP.id_market_making
= 1']
that suggests we might be using the wrong quoting character on sybase;
it's not failing to locate the table, it's raising a syntax error. not
really sure, I don't have a sybase handy to test with. It's possible this
quoting character should be a bracket. You can set that like this:
e = create_engine(sybase://)
# if not using quote=False, this will change the quoting character
e.dialect.identifier_preparer.initial_quote = '['
e.dialect.identifier_preparer.final_quote = ']'
if that confirms the issue, file a bug because we should fix that.
I'm struggling to understand the code in sqlalchemy, but from what I can
guess the table gets created and then _init_existing is called with the
__table_args__ (although I can't find where).
Would I be correct in saying that what you're expecting is that in
_setup_table the table should not already exist in the dict? I've no idea
why it would
On Friday, 29 May 2015 00:35:56 UTC+10, Michael Bayer wrote:
On 5/27/15 10:34 PM, Adam Darwin wrote:
Whilst upgrading from sqlalchemy 0.8 to 1.0.4 my ORM has broken with
the error Can't redefine 'quote' or 'quote_schema' arguments
I connect to a sybase db, and use a declarative_base
Base = declarative_base()
Using a standard method to create the mapping below
class RiskAggregationGroup(Base):
__tablename__ = 'RISK_AGGREGATION_GROUP'
__table_args__ = {'quote':False,'extend_existing':True}
id = Column(Integer, name='id_risk_agg', primary_key=True)
name = Column(String(50), name='nm_risk_agg')
description = Column(String(100), name='tx_desc')
This worked fine in sqlalchemy 0.8 but breaks in 1.0.4 as it doesn't like
me specifying quote as a table arg. I've tried a whole host of things to
get around this, setting it in the base, e.g.
The message you are getting refers to when the table metadata has already
been reflected, and the correct identifiers have already been loaded from
the database. This is not illustrated here but it seems likely that you
are also running a reflection step before you create this declarative base;
no such error is emitted otherwise. I would need clarification on that.
throws the same error. If I change it to use the @declared_attr the
quoting is not turned off. I'm unable to change the sybase settings and my
table names are all caps (which is the cause of the quoting). I've got
about 20 tables defined here, so am loathe to change them all to Table
creations, such as:
If your table is named in ALL_CAPS (on the database side) and Sybase is
considering this in a case-sensitive manner, then you need the quotes.
Quoting means, this name is in exactly this case, so if your statement
my table names are all
