[sqlalchemy] How to set Row instance attribute
Hello, I get some data from DB and have a sequence of Row objects, how it is possible to modify data in those objects attrs? Something like result = await dbsession.execute(query) services = result.all() for i, service in enumerate(services): services[i].title = f"some text here {service.title}" but I get AttributeError("can't set attribute") Thanks -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a2552b95-e41b-4fa6-b655-3c16cf5f844en%40googlegroups.com.
Re: [sqlalchemy] "flatten" model columns in Row
Thank you, Mike! воскресенье, 20 ноября 2022 г. в 16:31:09 UTC+2, Mike Bayer: > the quickest way at the moment is to use select(X.__table__, y_col), or, > if you use select(X), you can execute from session.connection().execute(). > > > > On Sat, Nov 19, 2022, at 5:31 PM, sector119 wrote: > > Hello > > For example I have two models X and Y, I want to get all columns from X > model and only one or two cols from Y model in the same Row as > [(x_col1, x_col2, x_col3, y_col1), ...] but not [(X, y_col1)] as I get > when I perform > select(X, Y.col1).join(X.y) > > I just don't want to write all X model cols down at the select statement > and just one from Y model like select(X.col1, X.col2, X.col3, Y.col1), X > model has plenty of cols, and I'm lazy ) > > Thanks > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/e3681861-a4ac-4502-b1a4-2b36ca8648aan%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/e3681861-a4ac-4502-b1a4-2b36ca8648aan%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a2524455-4782-471e-9427-8470ee9132fcn%40googlegroups.com.
[sqlalchemy] "flatten" model columns in Row
Hello For example I have two models X and Y, I want to get all columns from X model and only one or two cols from Y model in the same Row as [(x_col1, x_col2, x_col3, y_col1), ...] but not [(X, y_col1)] as I get when I perform select(X, Y.col1).join(X.y) I just don't want to write all X model cols down at the select statement and just one from Y model like select(X.col1, X.col2, X.col3, Y.col1), X model has plenty of cols, and I'm lazy ) Thanks -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e3681861-a4ac-4502-b1a4-2b36ca8648aan%40googlegroups.com.
Re: [sqlalchemy] SQLAlchemy 2. psycopg3 type adapt
Thank You, Mike, I thought that I can adapt types with sqlalchemy, not with db adapter like psycopg, to make it not db adapter dependent solution. воскресенье, 11 сентября 2022 г. в 04:14:02 UTC+3, Mike Bayer: > maybe email on the psycopg2 list / github tracker > > On Sat, Sep 10, 2022, at 4:15 PM, sector119 wrote: > > Hello, > > I got exception when I try to insert numpy.int8 type data. > sqlalchemy.exc.ProgrammingError: (psycopg.ProgrammingError) cannot adapt > type 'int8' using placeholder '%s' (format: AUTO) > > I'm trying to adapt it with code below, but it looks like a bit > comprehensive. > > Maybe you can suggest another solution? And what is a proper place to call > connection.adapters.register_dumper, is it ok to call it in that listener? > > *models/__init__.py* > > @listens_for(Pool, "connect") > def pool_on_connect(connection, _): > connection.adapters.register_dumper(np.integer, NumPyIntDumper) > > *models/adapters.py * > > from typing import Any > > import numpy as np > from psycopg import errors as e, postgres > from psycopg._wrappers import Int2, Int4, Int8, IntNumeric > from psycopg.adapt import Dumper, PyFormat > > > class _NumPyIntDumper(Dumper): > def dump(self, obj: Any) -> bytes: > t = type(obj) > > if not issubclass(t, np.integer): > raise e.DataError(f"integer expected, got > {type(obj).__name__!r}") > > return str(obj).encode() > > def quote(self, obj: Any) -> bytes: > value = self.dump(obj) > return value if obj >= 0 else b" " + value > > > class Int2Dumper(_NumPyIntDumper): > oid = postgres.types["int2"].oid > > > class Int4Dumper(_NumPyIntDumper): > oid = postgres.types["int4"].oid > > > class Int8Dumper(_NumPyIntDumper): > oid = postgres.types["int8"].oid > > > class IntNumericDumper(_NumPyIntDumper): > oid = postgres.types["numeric"].oid > > > class NumPyIntDumper(Dumper): > def dump(self, obj: Any) -> bytes: > raise TypeError( > f"{type(self).__name__} is a dispatcher to other dumpers:" > " dump() is not supposed to be called" > ) > > def get_key(self, obj: int, format: PyFormat) -> type: > return self.upgrade(obj, format).cls > > _int2_dumper = Int2Dumper(Int2) # smallint > _int4_dumper = Int4Dumper(Int4) # integer > _int8_dumper = Int8Dumper(Int8) # bigint > _int_numeric_dumper = IntNumericDumper(IntNumeric) # numeric > > def upgrade(self, obj: int, format: PyFormat) -> Dumper: > if isinstance(obj, (np.int8, np.int16, np.uint8)): > return self._int2_dumper > elif isinstance(obj, (np.int32, np.uint16)): > return self._int4_dumper > elif isinstance(obj, (np.int64, np.uint32)): > return self._int8_dumper > elif isinstance(obj, np.uint64): > return self._int_numeric_dumper > > raise e.DataError(f"{type(obj).__name__!r} not supported by > NumPyIntDumper") > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/db4fd851-bf4f-4a1e-a984-2398c027ce83n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/db4fd851-bf4f-4a1e-a984-2398c027ce83n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/25a58769-42e5-4c43-87d3-0cfeefa8f192n%40googlegroups.com.
[sqlalchemy] SQLAlchemy 2. psycopg3 type adapt
Hello, I got exception when I try to insert numpy.int8 type data. sqlalchemy.exc.ProgrammingError: (psycopg.ProgrammingError) cannot adapt type 'int8' using placeholder '%s' (format: AUTO) I'm trying to adapt it with code below, but it looks like a bit comprehensive. Maybe you can suggest another solution? And what is a proper place to call connection.adapters.register_dumper, is it ok to call it in that listener? *models/__init__.py* @listens_for(Pool, "connect") def pool_on_connect(connection, _): connection.adapters.register_dumper(np.integer, NumPyIntDumper) *models/adapters.py * from typing import Any import numpy as np from psycopg import errors as e, postgres from psycopg._wrappers import Int2, Int4, Int8, IntNumeric from psycopg.adapt import Dumper, PyFormat class _NumPyIntDumper(Dumper): def dump(self, obj: Any) -> bytes: t = type(obj) if not issubclass(t, np.integer): raise e.DataError(f"integer expected, got {type(obj).__name__!r}") return str(obj).encode() def quote(self, obj: Any) -> bytes: value = self.dump(obj) return value if obj >= 0 else b" " + value class Int2Dumper(_NumPyIntDumper): oid = postgres.types["int2"].oid class Int4Dumper(_NumPyIntDumper): oid = postgres.types["int4"].oid class Int8Dumper(_NumPyIntDumper): oid = postgres.types["int8"].oid class IntNumericDumper(_NumPyIntDumper): oid = postgres.types["numeric"].oid class NumPyIntDumper(Dumper): def dump(self, obj: Any) -> bytes: raise TypeError( f"{type(self).__name__} is a dispatcher to other dumpers:" " dump() is not supposed to be called" ) def get_key(self, obj: int, format: PyFormat) -> type: return self.upgrade(obj, format).cls _int2_dumper = Int2Dumper(Int2) # smallint _int4_dumper = Int4Dumper(Int4) # integer _int8_dumper = Int8Dumper(Int8) # bigint _int_numeric_dumper = IntNumericDumper(IntNumeric) # numeric def upgrade(self, obj: int, format: PyFormat) -> Dumper: if isinstance(obj, (np.int8, np.int16, np.uint8)): return self._int2_dumper elif isinstance(obj, (np.int32, np.uint16)): return self._int4_dumper elif isinstance(obj, (np.int64, np.uint32)): return self._int8_dumper elif isinstance(obj, np.uint64): return self._int_numeric_dumper raise e.DataError(f"{type(obj).__name__!r} not supported by NumPyIntDumper") -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/db4fd851-bf4f-4a1e-a984-2398c027ce83n%40googlegroups.com.
Re: [sqlalchemy] async mode and relationship more than one level deep
Thank You, works like a charm четверг, 22 июля 2021 г. в 00:26:57 UTC+3, Mike Bayer: > you need to chain those like: > > selectinload(Database.person).selectinload(Person.city) > > docs: > > > https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#relationship-loading-with-loader-options > > > > On Wed, Jul 21, 2021, at 4:25 PM, sector119 wrote: > > Hello, Mike > > When I want to use some relationship I just set selectinload option on it > like > > s = select(Database).options(selectinload(Database.person)) > > Here Database.person is relationship with Person model > > But what to do if I want to access some relationship of Person model? For > example Person.city, > > I got errors when I set selectinload(Database.person.city) or > selectinload(Person.city) > > Thank You > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/4a389836-c41c-4db5-a427-75e87a39ff7bn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/4a389836-c41c-4db5-a427-75e87a39ff7bn%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a7842e68-c737-4989-b033-8ee1543bc8ben%40googlegroups.com.
[sqlalchemy] Re: async mode and relationship more than one level deep
s = select( Database ).options( selectinload( Database.person ).options( joinedload(Person.city) ) ) среда, 21 июля 2021 г. в 23:25:05 UTC+3, sector119: > Hello, Mike > > When I want to use some relationship I just set selectinload option on it > like > > s = select(Database).options(selectinload(Database.person)) > > Here Database.person is relationship with Person model > > But what to do if I want to access some relationship of Person model? For > example Person.city, > > I got errors when I set selectinload(Database.person.city) or > selectinload(Person.city) > > Thank You > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ee1aa492-29e0-4abe-afca-c34356fb7a24n%40googlegroups.com.
[sqlalchemy] async mode and relationship more than one level deep
Hello, Mike When I want to use some relationship I just set selectinload option on it like s = select(Database).options(selectinload(Database.person)) Here Database.person is relationship with Person model But what to do if I want to access some relationship of Person model? For example Person.city, I got errors when I set selectinload(Database.person.city) or selectinload(Person.city) Thank You -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4a389836-c41c-4db5-a427-75e87a39ff7bn%40googlegroups.com.
[sqlalchemy] No NOWAIT for FOR UPDATE
Hello! On a PostgreSQL database can't get NOWAIT with FOR UPDATE clause ( sqlalchemy 1.4.18 from sqlalchemy.future import select from unity.models.unity import Internal sql = select( Internal.person_id_internal ).with_for_update(nowait=True) str(sql) Out[12]: 'SELECT unity.internals.person_id_internal \nFROM unity.internals FOR UPDATE' -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/587b5317-e275-4c22-a78e-460eeafff1c1n%40googlegroups.com.
[sqlalchemy] Set PostgreSQL default index tablespace
Hello Is it possible to set default tablespace for all indexes? I know that I cat set it with Index('my_index', my_table.c.data, postgresql_tablespace='my_tablespace') But I want to set set it by default somehow, that when I just put "index=True" on column, I get index created at some tablespace. Thank You -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2d9fca7c-6df1-4632-a97f-43a60cdae4ecn%40googlegroups.com.
Re: [sqlalchemy] Overlapping FK problem
Thanks! service = relationship('Service', primaryjoin='and_(Service.schema == Transaction.schema, Service.id == foreign(Transaction.service_id))') eliminates the error But why I have no need to do the same with organization = relationship(...) ? Because Service model references Organization one ? понедельник, 28 декабря 2020 г. в 19:53:24 UTC+2, Mike Bayer: > > > On Mon, Dec 28, 2020, at 12:37 PM, sector119 wrote: > > Thank You, Mike, > > Do you mean that I have to remove all foreign() annotations from all my > relationships like this? > service = relationship(*'Service'*, primaryjoin=*'and_(Service.schema == > Transaction.schema, Service.id == Transaction.service_id)'*) > organization = relationship(*'Organization'*, > primaryjoin=*'and_(Organization.schema > == Transaction.schema, Organization.id == Transaction.organization_id)'*) > person = relationship(*'Person'*, primaryjoin=*'and_(Person.schema == > Transaction.schema, Person.id == Transaction.person_id)'*) > > > > no, only the foreign() annotation that surrounds "Transaction.schema". > leave the one that surrounds "Transaction.service_id". that should > eliminate the error. > > > > if so, I still get the same warning.. > > And must I set primaryjoin for service and organization relationships at > all if I've set up FK for them > ForeignKeyConstraint( > (schema, service_id), > (Service.schema, Service.id) > ) > and > ForeignKeyConstraint( > (schema, organization_id), > (Organization.schema, Organization.id) > ), > > > понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer: > > > by having foreign() on the Transaction.schema column, that means when you > do this: > > t1 = Transaction() > > t1.service = some_service() > > the ORM is being instructed to copy some_service.schema over to > t1.schema. Because "foreign" means "this is the column that mirrors the > value of a canonical value on the related row". > > so you have to decide what should happen if you did this: > > t1.service = Service(schema="a", ...) > t1.organiazation = Organization(schema="b", ...) > t1.person = Person(schema="c", ...) > > is the above possible? or an error condition? > > overall, if the plan is that "schema" will match across all the objects > involved, and your application will make sure those are all set as needed, > just remove the foreign() annotation from the Transaction.service column. > the primary joins already have enough information based on the service_id, > organization_id and person_id columns. > > > On Mon, Dec 28, 2020, at 6:04 AM, sector119 wrote: > > I get following warning with my model: > > SAWarning: relationship 'Transaction.organization' will copy column > organizations.schema to column transactions.schema, which conflicts with > relationship(s): 'Transaction.service' (copies services.schema to > transactions.schema). If this is not the intention, consider if these > relationships should be linked with back_populates, or if viewonly=True > should be applied to one or more if they are read-only. For the less common > case that foreign key constraints are partially overlapping, the > orm.foreign() annotation can be used to isolate the columns that should be > written towards. The 'overlaps' parameter may be used to remove this > warning. > > I have all my tables partitioned by "schema" column so I have to put it to > every PK and FK > I add "primaryjoin" to my relations as described at > https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys > but still get that warning. How can I fix it? > > Thank You > > class Transaction(Base): > __tablename__ = *'transactions'* > > schema = Column(String(63), nullable=False, index=True) > id = Column(BigInteger, nullable=False, index=True) > > user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.users.id > <http://users.id>'*), nullable=False, index=True) > office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id > <http://offices.id>'*), nullable=False, index=True) > service_id = Column(Integer, nullable=False, index=True) > organization_id = Column(Integer, nullable=False, index=True) > > ... > > service = relationship(*'Service'*, > primaryjoin=*'and_(Service.schema == foreign(Transaction.schema), > Service.id == foreign(Transaction.service_id))'*) > organization = relationship(*'Organization'*,
Re: [sqlalchemy] Overlapping FK problem
>>> is the above possible? or an error condition? I don't want to restrict that case >>> overall, if the plan is that "schema" will match across all the objects involved, and your application will make sure those are all set as needed, just >>> remove the foreign() annotation from the Transaction.service column. the primary joins already have enough information based on the >>> service_id, organization_id and person_id columns. "schema" will match across all the objects - 100% понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer: > by having foreign() on the Transaction.schema column, that means when you > do this: > > t1 = Transaction() > > t1.service = some_service() > > the ORM is being instructed to copy some_service.schema over to > t1.schema. Because "foreign" means "this is the column that mirrors the > value of a canonical value on the related row". > > so you have to decide what should happen if you did this: > > t1.service = Service(schema="a", ...) > t1.organiazation = Organization(schema="b", ...) > t1.person = Person(schema="c", ...) > > is the above possible? or an error condition? > > overall, if the plan is that "schema" will match across all the objects > involved, and your application will make sure those are all set as needed, > just remove the foreign() annotation from the Transaction.service column. > the primary joins already have enough information based on the service_id, > organization_id and person_id columns. > > > On Mon, Dec 28, 2020, at 6:04 AM, sector119 wrote: > > I get following warning with my model: > > SAWarning: relationship 'Transaction.organization' will copy column > organizations.schema to column transactions.schema, which conflicts with > relationship(s): 'Transaction.service' (copies services.schema to > transactions.schema). If this is not the intention, consider if these > relationships should be linked with back_populates, or if viewonly=True > should be applied to one or more if they are read-only. For the less common > case that foreign key constraints are partially overlapping, the > orm.foreign() annotation can be used to isolate the columns that should be > written towards. The 'overlaps' parameter may be used to remove this > warning. > > I have all my tables partitioned by "schema" column so I have to put it to > every PK and FK > I add "primaryjoin" to my relations as described at > https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys > but still get that warning. How can I fix it? > > Thank You > > class Transaction(Base): > __tablename__ = *'transactions'* > > schema = Column(String(63), nullable=False, index=True) > id = Column(BigInteger, nullable=False, index=True) > > user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.users.id > <http://users.id>'*), nullable=False, index=True) > office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id > <http://offices.id>'*), nullable=False, index=True) > service_id = Column(Integer, nullable=False, index=True) > organization_id = Column(Integer, nullable=False, index=True) > > ... > > service = relationship(*'Service'*, > primaryjoin=*'and_(Service.schema == foreign(Transaction.schema), > Service.id == foreign(Transaction.service_id))'*) > organization = relationship(*'Organization'*, > primaryjoin=*'and_(Organization.schema == foreign(Transaction.schema), > Organization.id == foreign(Transaction.organization_id))'*) > person = relationship(*'Person'*, > primaryjoin=*'and_(Person.schema == foreign(Transaction.schema), > Person.id == foreign(Transaction.person_id))'*) > rollback_user = relationship(*'User'*, primaryjoin=*'User.id == > Transaction.rollback_user_id'*) > > __table_args__ = ( > PrimaryKeyConstraint(*'schema'*, *'id'*), > ForeignKeyConstraint( > (schema, service_id), > (Service.schema, Service.id) > ), > ForeignKeyConstraint( > (schema, organization_id), > (Organization.schema, Organization.id) > ), > { > *'postgresql_partition_by'*: > *'LIST (schema)'* } > ) > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this messa
Re: [sqlalchemy] Overlapping FK problem
Thank You, Mike, Do you mean that I have to remove all foreign() annotations from all my relationships like this? service = relationship('Service', primaryjoin='and_(Service.schema == Transaction.schema, Service.id == Transaction.service_id)') organization = relationship('Organization', primaryjoin='and_(Organization.schema == Transaction.schema, Organization.id == Transaction.organization_id)') person = relationship('Person', primaryjoin='and_(Person.schema == Transaction.schema, Person.id == Transaction.person_id)') if so, I still get the same warning.. And must I set primaryjoin for service and organization relationships at all if I've set up FK for them ForeignKeyConstraint( (schema, service_id), (Service.schema, Service.id) ) and ForeignKeyConstraint( (schema, organization_id), (Organization.schema, Organization.id) ), понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer: > by having foreign() on the Transaction.schema column, that means when you > do this: > > t1 = Transaction() > > t1.service = some_service() > > the ORM is being instructed to copy some_service.schema over to > t1.schema. Because "foreign" means "this is the column that mirrors the > value of a canonical value on the related row". > > so you have to decide what should happen if you did this: > > t1.service = Service(schema="a", ...) > t1.organiazation = Organization(schema="b", ...) > t1.person = Person(schema="c", ...) > > is the above possible? or an error condition? > > overall, if the plan is that "schema" will match across all the objects > involved, and your application will make sure those are all set as needed, > just remove the foreign() annotation from the Transaction.service column. > the primary joins already have enough information based on the service_id, > organization_id and person_id columns. > > > On Mon, Dec 28, 2020, at 6:04 AM, sector119 wrote: > > I get following warning with my model: > > SAWarning: relationship 'Transaction.organization' will copy column > organizations.schema to column transactions.schema, which conflicts with > relationship(s): 'Transaction.service' (copies services.schema to > transactions.schema). If this is not the intention, consider if these > relationships should be linked with back_populates, or if viewonly=True > should be applied to one or more if they are read-only. For the less common > case that foreign key constraints are partially overlapping, the > orm.foreign() annotation can be used to isolate the columns that should be > written towards. The 'overlaps' parameter may be used to remove this > warning. > > I have all my tables partitioned by "schema" column so I have to put it to > every PK and FK > I add "primaryjoin" to my relations as described at > https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys > but still get that warning. How can I fix it? > > Thank You > > class Transaction(Base): > __tablename__ = *'transactions'* > > schema = Column(String(63), nullable=False, index=True) > id = Column(BigInteger, nullable=False, index=True) > > user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.users.id > <http://users.id>'*), nullable=False, index=True) > office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id > <http://offices.id>'*), nullable=False, index=True) > service_id = Column(Integer, nullable=False, index=True) > organization_id = Column(Integer, nullable=False, index=True) > > ... > > service = relationship(*'Service'*, > primaryjoin=*'and_(Service.schema == foreign(Transaction.schema), > Service.id == foreign(Transaction.service_id))'*) > organization = relationship(*'Organization'*, > primaryjoin=*'and_(Organization.schema == foreign(Transaction.schema), > Organization.id == foreign(Transaction.organization_id))'*) > person = relationship(*'Person'*, > primaryjoin=*'and_(Person.schema == foreign(Transaction.schema), > Person.id == foreign(Transaction.person_id))'*) > rollback_user = relationship(*'User'*, primaryjoin=*'User.id == > Transaction.rollback_user_id'*) > > __table_args__ = ( > PrimaryKeyConstraint(*'schema'*, *'id'*), > ForeignKeyConstraint( > (schema, service_id), > (Service.schema, Service.id) > ), > ForeignKeyConstraint( > (schema, organization_id), > (Organization.schema, Organization.id) > ), > { > *'postgresql_partition_by'*: > *'LIST (schema)'* } >
[sqlalchemy] Overlapping FK problem
I get following warning with my model: SAWarning: relationship 'Transaction.organization' will copy column organizations.schema to column transactions.schema, which conflicts with relationship(s): 'Transaction.service' (copies services.schema to transactions.schema). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards. The 'overlaps' parameter may be used to remove this warning. I have all my tables partitioned by "schema" column so I have to put it to every PK and FK I add "primaryjoin" to my relations as described at https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys but still get that warning. How can I fix it? Thank You class Transaction(Base): __tablename__ = 'transactions' schema = Column(String(63), nullable=False, index=True) id = Column(BigInteger, nullable=False, index=True) user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + '.users.id'), nullable=False, index=True) office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + '.offices.id'), nullable=False, index=True) service_id = Column(Integer, nullable=False, index=True) organization_id = Column(Integer, nullable=False, index=True) ... service = relationship('Service', primaryjoin='and_(Service.schema == foreign(Transaction.schema), Service.id == foreign(Transaction.service_id))') organization = relationship('Organization', primaryjoin='and_(Organization.schema == foreign(Transaction.schema), Organization.id == foreign(Transaction.organization_id))') person = relationship('Person', primaryjoin='and_(Person.schema == foreign(Transaction.schema), Person.id == foreign(Transaction.person_id))') rollback_user = relationship('User', primaryjoin='User.id == Transaction.rollback_user_id') __table_args__ = ( PrimaryKeyConstraint('schema', 'id'), ForeignKeyConstraint( (schema, service_id), (Service.schema, Service.id) ), ForeignKeyConstraint( (schema, organization_id), (Organization.schema, Organization.id) ), { 'postgresql_partition_by': 'LIST (schema)' } ) -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0d422feb-956f-4efc-b3c0-17473652c603n%40googlegroups.com.
Re: [sqlalchemy] from_statement and cte problem
For example I have a property 'path' with CTE like this and it wouldn't return set of rows, but scalar value as array[] of rows would it be possible to load this property as subquery() ? Of course I can include that CTE query in my query(Locality) using subquery(), but it would be cool if I'll be able to "undefer" that property somehow like relation ) @property def path(self): session = object_session(self) def get_locality_path_q(locality_id): parent_q = session.query(Locality).filter(Locality.id == locality_id).cte(recursive=True) parents = aliased(parent_q) locality_alias = aliased(Locality) q = parent_q.union_all( session.query(locality_alias).join(parents, locality_alias.id == parents.c.parent_id) ) cte = aliased(Locality, q) return session.query(cte).order_by(cte.id) return get_locality_path_q(self.id) вторник, 2 июля 2019 г., 0:03:20 UTC+3 пользователь Mike Bayer написал: > > I don't know what you mean. is there SQL you know you want or is that the > part you want to figure out? > > > On Mon, Jul 1, 2019, at 4:21 PM, sector119 wrote: > > Mike, is it possible to load "@property" as subquery? Maybe as > ARRAY[path_item, path_item, ...] > > воскресенье, 30 июня 2019 г., 20:26:42 UTC+3 пользователь Mike Bayer > написал: > > > > On Sun, Jun 30, 2019, at 5:37 AM, sector119 wrote: > > Nice, thanks a lot, Mike, now it works as expected > > > that's great. the docs are not good here, there's not enough discussion > of "aliased()" , from_statement() and what they do, also I'm changing some > details of how they do their work for 1.4 in any case so documentation > efforts will be needed. > > > > > @property > *def *path(self): > session = object_session(self) > > *def *get_locality_path_q(locality_id): > parent_q = session.query(Locality).filter(Locality.id == > locality_id).cte(recursive=*True*) > > parents = aliased(parent_q) > > locality_alias = aliased(Locality) > > q = parent_q.union_all( > session.query(locality_alias).join(parents, locality_alias.id == > parents.c.parent_id) > ) > > cte = aliased(Locality, q) > > *return *session.query(cte).order_by(cte.id) > > *return *get_locality_path_q(self.id) > > > воскресенье, 30 июня 2019 г., 1:11:21 UTC+3 пользователь Mike Bayer > написал: > > > > On Sat, Jun 29, 2019, at 11:24 AM, sector119 wrote: > > Hello, > > > I have Locality model with 'path' property to get path from 'root' of tree to > current item, everything works ok, but > > I can't get result as Locality instance list.. > > When I use > *'*object_session(self).query(Locality).from_statement(q).order_by(Locality.id)' > I get > > sqlalchemy.exc.ArgumentError: from_statement accepts text(), select(), and > union() objects only. > > > How can I adopt results to Locality model? > > > > *class *Locality(Base): > __tablename__ = > *'localities'* > __table_args__ = {*'schema'*: SYSTEM_SCHEMA} > > id = Column(Integer, primary_key=*True*) > parent_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.localities.id > <http://localities.id>'*)) > name = Column(UnicodeText, nullable=*False*) > type = Column(Integer, nullable=*False*) > > @property > *def *path(self): > *def *get_locality_path_q(locality_id): > top_q = select([ > Locality.id, > Locality.parent_id, > Locality.name, > Locality.type, > ]).\ > where(Locality.id == locality_id).\ > cte(recursive=*True*) > > parents = aliased(top_q) > > locality_alias = aliased(Locality) > > q = top_q.union_all( > select([ > locality_alias.id, > locality_alias.parent_id, > locality_alias.name, > locality_alias.type > ]).select_from(join(locality_alias, parents, > locality_alias.id == parents.c.parent_id)) > ) > > > *# return object_session(self).query(q).order_by(q.c.id <http://q.c.id>)* >*return > *object_session(self).query(Locality).from_statement(q).order_by(Locality.id) > > *return *get_locality_path_q(self.id) > > > above, "q" is a CTE, not a SELECT, meaning it's something you can select >
Re: [sqlalchemy] from_statement and cte problem
Mike, is it possible to load "@property" as subquery? Maybe as ARRAY[path_item, path_item, ...] воскресенье, 30 июня 2019 г., 20:26:42 UTC+3 пользователь Mike Bayer написал: > > > > On Sun, Jun 30, 2019, at 5:37 AM, sector119 wrote: > > Nice, thanks a lot, Mike, now it works as expected > > > that's great. the docs are not good here, there's not enough discussion > of "aliased()" , from_statement() and what they do, also I'm changing some > details of how they do their work for 1.4 in any case so documentation > efforts will be needed. > > > > > @property > *def *path(self): > session = object_session(self) > > *def *get_locality_path_q(locality_id): > parent_q = session.query(Locality).filter(Locality.id == > locality_id).cte(recursive=*True*) > > parents = aliased(parent_q) > > locality_alias = aliased(Locality) > > q = parent_q.union_all( > session.query(locality_alias).join(parents, locality_alias.id == > parents.c.parent_id) > ) > > cte = aliased(Locality, q) > > *return *session.query(cte).order_by(cte.id) > > *return *get_locality_path_q(self.id) > > > воскресенье, 30 июня 2019 г., 1:11:21 UTC+3 пользователь Mike Bayer > написал: > > > > On Sat, Jun 29, 2019, at 11:24 AM, sector119 wrote: > > Hello, > > > I have Locality model with 'path' property to get path from 'root' of tree to > current item, everything works ok, but > > I can't get result as Locality instance list.. > > When I use > *'*object_session(self).query(Locality).from_statement(q).order_by(Locality.id)' > I get > > sqlalchemy.exc.ArgumentError: from_statement accepts text(), select(), and > union() objects only. > > > How can I adopt results to Locality model? > > > > *class *Locality(Base): > __tablename__ = > *'localities'* > __table_args__ = {*'schema'*: SYSTEM_SCHEMA} > > id = Column(Integer, primary_key=*True*) > parent_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.localities.id > <http://localities.id>'*)) > name = Column(UnicodeText, nullable=*False*) > type = Column(Integer, nullable=*False*) > > @property > *def *path(self): > *def *get_locality_path_q(locality_id): > top_q = select([ > Locality.id, > Locality.parent_id, > Locality.name, > Locality.type, > ]).\ > where(Locality.id == locality_id).\ > cte(recursive=*True*) > > parents = aliased(top_q) > > locality_alias = aliased(Locality) > > q = top_q.union_all( > select([ > locality_alias.id, > locality_alias.parent_id, > locality_alias.name, > locality_alias.type > ]).select_from(join(locality_alias, parents, > locality_alias.id == parents.c.parent_id)) > ) > > > *# return object_session(self).query(q).order_by(q.c.id <http://q.c.id>)* >*return > *object_session(self).query(Locality).from_statement(q).order_by(Locality.id) > > *return *get_locality_path_q(self.id) > > > above, "q" is a CTE, not a SELECT, meaning it's something you can select > FROM, like a table. Call select() to SELECT from it: > > return ( > object_session(self) > .query(Locality) > .from_statement(q.select().order_by(q.c.id)) > ) > > > additionally, from_statement() does not allow further changes to the > statement and the ORDER BY must be in terms of thing you are selecting > from, in this case q.c.id > > your code would be easier if you made use of top_q as a FROM object rather > than a statement: > > lcte = aliased(Locality, q) > > return ( > object_session(self) > .query(lcte) > .order_by(lcte.id) > ) > > > There's not too many doc examples of how aliased() works with FROM clauses > but the basic idea is at: > > > https://docs.sqlalchemy.org/en/13/orm/tutorial.html#selecting-entities-from-subqueries > > https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.aliased > > A little more on aliased() with CTE is written more for query.cte() which > you could also be using here: > > > https://docs.sqlalchemy.or
Re: [sqlalchemy] from_statement and cte problem
Nice, thanks a lot, Mike, now it works as expected @property def path(self): session = object_session(self) def get_locality_path_q(locality_id): parent_q = session.query(Locality).filter(Locality.id == locality_id).cte(recursive=True) parents = aliased(parent_q) locality_alias = aliased(Locality) q = parent_q.union_all( session.query(locality_alias).join(parents, locality_alias.id == parents.c.parent_id) ) cte = aliased(Locality, q) return session.query(cte).order_by(cte.id) return get_locality_path_q(self.id) воскресенье, 30 июня 2019 г., 1:11:21 UTC+3 пользователь Mike Bayer написал: > > > > On Sat, Jun 29, 2019, at 11:24 AM, sector119 wrote: > > Hello, > > > I have Locality model with 'path' property to get path from 'root' of tree to > current item, everything works ok, but > > I can't get result as Locality instance list.. > > When I use > *'*object_session(self).query(Locality).from_statement(q).order_by(Locality.id)' > I get > > sqlalchemy.exc.ArgumentError: from_statement accepts text(), select(), and > union() objects only. > > > How can I adopt results to Locality model? > > > > *class *Locality(Base): > __tablename__ = > *'localities'* > __table_args__ = {*'schema'*: SYSTEM_SCHEMA} > > id = Column(Integer, primary_key=*True*) > parent_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.localities.id > <http://localities.id>'*)) > name = Column(UnicodeText, nullable=*False*) > type = Column(Integer, nullable=*False*) > > @property > *def *path(self): > *def *get_locality_path_q(locality_id): > top_q = select([ > Locality.id, > Locality.parent_id, > Locality.name, > Locality.type, > ]).\ > where(Locality.id == locality_id).\ > cte(recursive=*True*) > > parents = aliased(top_q) > > locality_alias = aliased(Locality) > > q = top_q.union_all( > select([ > locality_alias.id, > locality_alias.parent_id, > locality_alias.name, > locality_alias.type > ]).select_from(join(locality_alias, parents, > locality_alias.id == parents.c.parent_id)) > ) > > > *# return object_session(self).query(q).order_by(q.c.id <http://q.c.id>)* >*return > *object_session(self).query(Locality).from_statement(q).order_by(Locality.id) > > *return *get_locality_path_q(self.id) > > > above, "q" is a CTE, not a SELECT, meaning it's something you can select > FROM, like a table. Call select() to SELECT from it: > > return ( > object_session(self) > .query(Locality) > .from_statement(q.select().order_by(q.c.id)) > ) > > > additionally, from_statement() does not allow further changes to the > statement and the ORDER BY must be in terms of thing you are selecting > from, in this case q.c.id > > your code would be easier if you made use of top_q as a FROM object rather > than a statement: > > lcte = aliased(Locality, q) > > return ( > object_session(self) > .query(lcte) > .order_by(lcte.id) > ) > > > There's not too many doc examples of how aliased() works with FROM clauses > but the basic idea is at: > > > https://docs.sqlalchemy.org/en/13/orm/tutorial.html#selecting-entities-from-subqueries > > https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.aliased > > A little more on aliased() with CTE is written more for query.cte() which > you could also be using here: > > > https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=cte#sqlalchemy.orm.query.Query.cte > > > > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 sqlal...@googlegroups.com . > To post to this group, send email to sqlal...@googlegroups.com > .
[sqlalchemy] from_statement and cte problem
Hello, I have Locality model with 'path' property to get path from 'root' of tree to current item, everything works ok, but I can't get result as Locality instance list.. When I use *'*object_session(self).query(Locality).from_statement(q).order_by(Locality.id)' I get sqlalchemy.exc.ArgumentError: from_statement accepts text(), select(), and union() objects only. How can I adopt results to Locality model? class Locality(Base): __tablename__ = 'localities' __table_args__ = {'schema': SYSTEM_SCHEMA} id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + '.localities.id')) name = Column(UnicodeText, nullable=False) type = Column(Integer, nullable=False) @property def path(self): def get_locality_path_q(locality_id): top_q = select([ Locality.id, Locality.parent_id, Locality.name, Locality.type, ]).\ where(Locality.id == locality_id).\ cte(recursive=True) parents = aliased(top_q) locality_alias = aliased(Locality) q = top_q.union_all( select([ locality_alias.id, locality_alias.parent_id, locality_alias.name, locality_alias.type ]).select_from(join(locality_alias, parents, locality_alias.id == parents.c.parent_id)) ) # return object_session(self).query(q).order_by(q.c.id) return object_session(self).query(Locality).from_statement(q).order_by(Locality.id) return get_locality_path_q(self.id) -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/78205703-4631-4c1a-aa13-f89fc6f06feb%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Alembic and postgresql multiple schema question
> if you want individual migration sections for each schema, with or > without "schema" written in, there's ways to do all that also but that > doesn't seem necessary if you are sharing a single model with multiple > identical schemas. > The problem is that some one might alter some tables at some schemas with plain sql in psql (( So it would be perfect to have individual migration sections for each schema, _with_ "schema" written in. Thank you -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.
Re: [sqlalchemy] Alembic and postgresql multiple schema question
from __future__ import with_statement from alembic import context from sqlalchemy import engine_from_config, pool from logging.config import fileConfig # this is the Alembic Config object, which provides # access to the values within the .ini file in use. config = context.config # Interpret the config file for Python logging. # This line sets up loggers basically. fileConfig(config.config_file_name) # add your model's MetaData object here # for 'autogenerate' support # from myapp import mymodel # target_metadata = mymodel.Base.metadata from epsilon.models.meta import metadata target_metadata = metadata # other values from the config, defined by the needs of env.py, # can be acquired: # my_important_option = config.get_main_option("my_important_option") # ... etc. schema_names = config.get_main_option('schemas') def run_migrations_offline(): """Run migrations in 'offline' mode. This configures the context with just a URL and not an Engine, though an Engine is acceptable here as well. By skipping the Engine creation we don't even need a DBAPI to be available. Calls to context.execute() here emit the given string to the script output. """ url = config.get_main_option("sqlalchemy.url") context.configure( url=url, target_metadata=target_metadata, literal_binds=True) with context.begin_transaction(): context.run_migrations() def run_migrations_online(): """Run migrations in 'online' mode. In this scenario we need to create an Engine and associate a connection with the context. """ connectable = engine_from_config( config.get_section(config.config_ini_section), prefix='sqlalchemy.', poolclass=pool.NullPool) with connectable.connect() as connection: for schema_name in schema_names.split(): conn = connection.execution_options(schema_translate_map={None: schema_name}) print("Migrating schema %s" % schema_name) context.configure( connection=conn, target_metadata=target_metadata ) with context.begin_transaction(): context.run_migrations() if context.is_offline_mode(): run_migrations_offline() else: run_migrations_online() четверг, 30 августа 2018 г., 16:09:35 UTC+3 пользователь Mike Bayer написал: > > On Thu, Aug 30, 2018 at 7:11 AM, sector119 > wrote: > > Mike, but in run_migrations_online() I use conn = > > connection.execution_options(schema_translate_map={None: schema_name}) > > But I get no schemas at resulting alembic/versions/file.py > > can you share your env.py > > > > > > > среда, 29 августа 2018 г., 20:46:07 UTC+3 пользователь Mike Bayer > написал: > >> > >> On Wed, Aug 29, 2018 at 5:12 AM, sector119 wrote: > >> > Hello > >> > > >> > I have N schemas with the same set of tables, 1 system schema with > >> > users, > >> > groups, ... tables and 6 schemas with streets, organizations, > >> > transactions, > >> > ... tables. > >> > On those schemas tables I don't set __table_args__ = ({'schema': > >> > SCHEMA},) > >> > I just call dbsession.execute('SET search_path TO system, %s' % > SCHEMA) > >> > before sql queries. > >> > > >> > When I make some changes in my model structures I want to refactor > table > >> > in > >> > all schemas using Alembic, how can I do that? > >> > Maybe I can make some loop over my schemas somewhere? > >> > >> setting the search path is going to confuse SQLAlchemy's table > >> reflection process, such that it assumes a Table of a certain schema > >> does not require a "schema" argument, because it is already in the > >> search path. > >> > >> Keep the search path set to "public", see > >> > >> > http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path. > > > >> There is an option to change this behavior mentioned in that > >> section called postgresql_ignore_search_path, however it isn't > >> guaranteed to suit all use cases. if that makes your case work, then > >> that would be all you need. if not, then read on... > >> > >> For the officially supported way to do this, you want to have the > >> explicit schema name inside the SQL - but this can be automated for a > >> multi-tenancy
Re: [sqlalchemy] Alembic and postgresql multiple schema question
Mike, but in run_migrations_online() I use conn = connection.execution_options(schema_translate_map={None: schema_name}) But I get no schemas at resulting alembic/versions/file.py среда, 29 августа 2018 г., 20:46:07 UTC+3 пользователь Mike Bayer написал: > > On Wed, Aug 29, 2018 at 5:12 AM, sector119 > wrote: > > Hello > > > > I have N schemas with the same set of tables, 1 system schema with > users, > > groups, ... tables and 6 schemas with streets, organizations, > transactions, > > ... tables. > > On those schemas tables I don't set __table_args__ = ({'schema': > SCHEMA},) > > I just call dbsession.execute('SET search_path TO system, %s' % SCHEMA) > > before sql queries. > > > > When I make some changes in my model structures I want to refactor table > in > > all schemas using Alembic, how can I do that? > > Maybe I can make some loop over my schemas somewhere? > > setting the search path is going to confuse SQLAlchemy's table > reflection process, such that it assumes a Table of a certain schema > does not require a "schema" argument, because it is already in the > search path. > > Keep the search path set to "public", see > > http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path. > > > There is an option to change this behavior mentioned in that > section called postgresql_ignore_search_path, however it isn't > guaranteed to suit all use cases. if that makes your case work, then > that would be all you need. if not, then read on... > > For the officially supported way to do this, you want to have the > explicit schema name inside the SQL - but this can be automated for a > multi-tenancy application. Use the schema translation map feature: > > http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execution_options#schema-translating. > > > > > > > > > > Thanks > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > 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+...@googlegroups.com . > > To post to this group, send email to sqlal...@googlegroups.com > . > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.
[sqlalchemy] Re: Alembic and postgresql multiple schema question
I've found some example at https://stackoverflow.com/questions/21109218/alembic-support-for-multiple-postgres-schemas But when I run alembic revision --autogenerate -m "Initial upgrade" at alembic/versions/24648f118be9_initial_upgrade.py I've got no schema='myschema' keywords on table, indexes, columns items (( def run_migrations_online(): """Run migrations in 'online' mode. In this scenario we need to create an Engine and associate a connection with the context. """ connectable = engine_from_config( config.get_section(config.config_ini_section), prefix='sqlalchemy.', poolclass=pool.NullPool) with connectable.connect() as connection: for schema_name in schema_names.split(): conn = connection.execution_options(schema_translate_map={None: schema_name}) print("Migrating schema %s" % schema_name) context.configure( connection=conn, target_metadata=target_metadata ) with context.begin_transaction(): context.run_migrations() среда, 29 августа 2018 г., 12:12:19 UTC+3 пользователь sector119 написал: > > Hello > > I have N schemas with the same set of tables, 1 system schema with users, > groups, ... tables and 6 schemas with streets, organizations, transactions, > ... tables. > On those schemas tables I don't set __table_args__ = ({'schema': SCHEMA},) > I just call dbsession.execute('SET search_path TO system, %s' % SCHEMA) > before sql queries. > > When I make some changes in my model structures I want to refactor table > in all schemas using Alembic, how can I do that? > Maybe I can make some loop over my schemas somewhere? > > > Thanks > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.
[sqlalchemy] Alembic and postgresql multiple schema question
Hello I have N schemas with the same set of tables, 1 system schema with users, groups, ... tables and 6 schemas with streets, organizations, transactions, ... tables. On those schemas tables I don't set __table_args__ = ({'schema': SCHEMA},) I just call dbsession.execute('SET search_path TO system, %s' % SCHEMA) before sql queries. When I make some changes in my model structures I want to refactor table in all schemas using Alembic, how can I do that? Maybe I can make some loop over my schemas somewhere? Thanks -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.
Re: [sqlalchemy] Extra table name in FROM clause
Here is SQL that works as expected: SELECT m.*, d.* FROM ( SELECT person_id, service_id, person_id_internal, meters_readings, -- two-dimensional array meters_readings [generate_subscripts(meters_readings, 1)] [1] AS meter_id, meters_readings [generate_subscripts(meters_readings, 1)] [2] AS organization_reading, date FROM databases ) d LEFT OUTER JOIN LATERAL ( SELECT DISTINCT ON (service_id, person_id_internal, meter_id) user_id, reading FROM meter_readings WHERE person_id = d.person_id AND service_id = d.service_id AND meter_id = d.meter_id AND commit_date > d.date AND rollback_date IS NULL AND reading IS NOT NULL ORDER BY service_id, person_id_internal, meter_id, commit_date DESC, commit_time DESC ) m ON TRUE WHERE d.person_id = 2099000301470; And that is SA query that also works :) I managed to write it )) def get_person_meters_q(dbsession, person_id): database = dbsession.query( Database.person_id, Database.service_id, Database.person_id_internal, Database.date, ( type_coerce( Database.meters_readings[ func.generate_subscripts(Database.meters_readings, 1) ], type_=ARRAY(Text) )[1] ).label('meter_id'), ( type_coerce( Database.meters_readings[ func.generate_subscripts(Database.meters_readings, 1) ], type_=ARRAY(Text) )[2] ).label('organization_reading') ).subquery() meter_readings = dbsession.query( MeterReading.user_id, MeterReading.reading ). \ distinct( MeterReading.service_id, MeterReading.person_id_internal, MeterReading.meter_id). \ filter( and_( MeterReading.person_id == database.c.person_id, MeterReading.service_id == database.c.service_id, MeterReading.meter_id == database.c.meter_id, MeterReading.commit_date > database.c.date, MeterReading.rollback_date == None, MeterReading.reading != None ) ). \ order_by( MeterReading.service_id, MeterReading.person_id_internal, MeterReading.meter_id, MeterReading.commit_date.desc(), MeterReading.commit_time.desc() ).subquery().lateral() q = dbsession.query( meter_readings, database ). \ select_from( database.outerjoin(meter_readings, true()) ).filter(database.c.person_id == person_id) return q The problem with extra "database" table name in select was because I use filter(Database.person_id == person_id) but not filter(database.c.person_id == person_id) About LATERAL https://www.postgresql.org/docs/current/static/sql-select.html The LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to *refer to columns of FROM items **that appear before it* in the FROM list. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.) So I refer columns that are in database query (first) from meter_readings query (second select) Without LATERAL I get: ERROR: invalid reference to FROM-clause entry for table "d" Hint: There is an entry for table "d", but it cannot be referenced from this part of the query. And that is SQL query result: \N \N 2099000153759 75 952160 {{952160,1140,0}} 952160 11402017-11-30 \N \N 2099000153759 2 27852 {{219935,14768,0}} 219935 14768 2017-11-30 \N \N 2099000153759 4 206688 {{468805,57,0},{589164,73,0}} 468805 57 2017-11-30 \N \N 2099000153759 4 206688 {{468805,57,0},{589164,73,0}} 589164 73 2017-11-30 -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.
[sqlalchemy] Re: Extra table name in FROM clause
oops, .filter(database.c.person_id == person_id) fixed the problem. Thanks! четверг, 4 января 2018 г., 23:08:35 UTC+2 пользователь sector119 написал: > > Hello, > > In this query I get extra (unexpected) "databases" table name in FROM > clause > > q = dbsession.query( > meter_readings.c.reading, > database.c.service_id > ). \ > select_from( > database.outerjoin(meter_readings, true()) > ).filter(Database.person_id == person_id) > > > Here is the query: > SELECT ... > FROM *databases*, (SELECT ... FROM databases) AS anon_2 LEFT OUTER JOIN > LATERAL (SELECT ... FROM meter_readings) AS anon_1 ON true WHERE > databases.person_id = 123 > > But I expect to get it without *databases *table like > SELECT ... FROM (SELECT ... FROM databases) AS anon_2 LEFT OUTER JOIN > LATERAL (SELECT ... FROM meter_readings) AS anon_1 ON true WHERE > databases.person_id = 123 > > > What am I doing wrong? > > > > database = dbsession.query( > Database.person_id, > Database.service_id, > Database.person_id_internal, > Database.date, > ( > type_coerce( > Database.meters_readings[ > func.generate_subscripts(Database.meters_readings, 1) > ], > type_=ARRAY(Text) > )[1] > ).label('meter_id'), > ( > type_coerce( > Database.meters_readings[ > func.generate_subscripts(Database.meters_readings, 1) > ], > type_=ARRAY(Text) > )[2] > ).label('organization_reading') > ).subquery() > > meter_readings = dbsession.query( > MeterReading.user_id, > MeterReading.reading > ). \ > distinct( > MeterReading.service_id, > MeterReading.person_id_internal, > MeterReading.meter_id). \ > filter( > and_( > MeterReading.person_id == database.c.person_id, > MeterReading.service_id == database.c.service_id, > MeterReading.meter_id == database.c.meter_id, > MeterReading.commit_date > database.c.date, > MeterReading.rollback_date == None, > MeterReading.reading != None > ) > ). \ > order_by( > MeterReading.service_id, > MeterReading.person_id_internal, > MeterReading.meter_id, > MeterReading.commit_date.desc(), > MeterReading.commit_time.desc() > ).subquery().lateral() > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.
[sqlalchemy] Extra table name in FROM clause
Hello, In this query I get extra (unexpected) "databases" table name in FROM clause q = dbsession.query( meter_readings.c.reading, database.c.service_id ). \ select_from( database.outerjoin(meter_readings, true()) ).filter(Database.person_id == person_id) Here is the query: SELECT ... FROM *databases*, (SELECT ... FROM databases) AS anon_2 LEFT OUTER JOIN LATERAL (SELECT ... FROM meter_readings) AS anon_1 ON true WHERE databases.person_id = 123 But I expect to get it without *databases *table like SELECT ... FROM (SELECT ... FROM databases) AS anon_2 LEFT OUTER JOIN LATERAL (SELECT ... FROM meter_readings) AS anon_1 ON true WHERE databases.person_id = 123 What am I doing wrong? database = dbsession.query( Database.person_id, Database.service_id, Database.person_id_internal, Database.date, ( type_coerce( Database.meters_readings[ func.generate_subscripts(Database.meters_readings, 1) ], type_=ARRAY(Text) )[1] ).label('meter_id'), ( type_coerce( Database.meters_readings[ func.generate_subscripts(Database.meters_readings, 1) ], type_=ARRAY(Text) )[2] ).label('organization_reading') ).subquery() meter_readings = dbsession.query( MeterReading.user_id, MeterReading.reading ). \ distinct( MeterReading.service_id, MeterReading.person_id_internal, MeterReading.meter_id). \ filter( and_( MeterReading.person_id == database.c.person_id, MeterReading.service_id == database.c.service_id, MeterReading.meter_id == database.c.meter_id, MeterReading.commit_date > database.c.date, MeterReading.rollback_date == None, MeterReading.reading != None ) ). \ order_by( MeterReading.service_id, MeterReading.person_id_internal, MeterReading.meter_id, MeterReading.commit_date.desc(), MeterReading.commit_time.desc() ).subquery().lateral() -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.
Re: [sqlalchemy] NotImplementedError: Operator 'getitem' is not supported on this expression
Thank you, Mike, but I need something like this type_coerce( Database.meters_readings[ func.generate_subscripts(Database.meters_readings, 1) ], type_=ARRAY(Text) )[1] Database.meters_readings is two-dimensional array like [ ['1', '2', '3'], ['4', '5', '6'] ], so Database.meters_readings[func.generate_subscripts.] must return ['1', '2', '3'] or ['4', '5', '6'], and than I want to get 1-st or second element of this "sub-array" func.generate_subscripts have to return int and it is Ok, but why I can't get that "sub-array" without type_coerce? четверг, 4 января 2018 г., 17:48:56 UTC+2 пользователь Mike Bayer написал: > > On Thu, Jan 4, 2018 at 6:49 AM, sector119 > > wrote: > > Hello, > > > > > > I try to rewrite "meters_readings[generate_subscripts(meters_readings, > > 1)][1]" from plain sql query that works > > > > with sqlalchemy and > > > Database.meters_readings[func.generate_subscripts(Database.meters_readings, > > 1)][1] doesn't work > > > your func.generate_subscripts() function doesn't know that it needs to > return a datatype that fulfills the "Indexable" contract, that of > ARRAY, JSON and HSTORE (though PG documents this as returning "setof", > that supports indexing? shrugs). You need to give it the type you > need, which I guess in this case the closest is ARRAY: > > >>> f = func.generate_subscripts(column('q'), 1, type_=ARRAY(Integer)) > >>> print f[5] > (generate_subscripts(q, :generate_subscripts_1))[:generate_subscripts_2] > > > > > > > > I get NotImplementedError: Operator 'getitem' is not supported on this > > expression > > > > > > Is it possible? > > > > > > > > def get_person_meters_q(dbsession, person_id): > > database = dbsession.query( > > Database.person_id, > > Database.service_id, > > Database.person_id_internal, > > Database.meters_readings, > > > > > (Database.meters_readings[func.generate_subscripts(Database.meters_readings, > > > 1)][1]).label('meter_id'), > > > > > (Database.meters_readings[func.generate_subscripts(Database.meters_readings, > > > 1)][2]).label('organization_reading'), > > Database.date > > ).subquery() > > > > meter_readings = dbsession.query( > > MeterReading.user_id, > > MeterReading.reading > > ). \ > > distinct( > > MeterReading.service_id, > > MeterReading.person_id_internal, > > MeterReading.meter_id). \ > > filter( > > and_( > > MeterReading.person_id == database.c.person_id, > > MeterReading.service_id == database.c.service_id, > > MeterReading.meter_id == database.c.meter_id, > > MeterReading.commit_date > database.c.date, > > MeterReading.rollback_date == None, > > MeterReading.reading != None > > ) > > ). \ > > order_by( > > MeterReading.service_id, > > MeterReading.person_id_internal, > > MeterReading.meter_id, > > MeterReading.commit_date.desc(), > > MeterReading.commit_time.desc() > > ).subquery().lateral() > > > > q = dbsession.query( > > meter_readings, > > database > > ). \ > > select_from(database). \ > > outerjoin(meter_readings, true()). \ > > filter(Database.person_id == person_id) > > > > return q > > > > > > > > File > "/home/sector119/PycharmProjects/epsilon/epsilon/scripts/testdb.py", > > line 98, in get_person_meters_q > > > > > (Database.meters_readings[func.generate_subscripts(Database.meters_readings, > > > 1)][1]).label('meter_id'), > > File > > > "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/operators.py", > > > > line 411, in __getitem__ > > return self.operate(getitem, index) > > File > > > "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchem
[sqlalchemy] NotImplementedError: Operator 'getitem' is not supported on this expression
Hello, I try to rewrite "meters_readings[generate_subscripts(meters_readings, 1)][1]" from plain sql query that works with sqlalchemy and Database.meters_readings[func.generate_subscripts(Database.meters_readings, 1)][1] doesn't work I get NotImplementedError: Operator 'getitem' is not supported on this expression Is it possible? def get_person_meters_q(dbsession, person_id): database = dbsession.query( Database.person_id, Database.service_id, Database.person_id_internal, Database.meters_readings, (Database.meters_readings[func.generate_subscripts(Database.meters_readings, 1)][1]).label('meter_id'), (Database.meters_readings[func.generate_subscripts(Database.meters_readings, 1)][2]).label('organization_reading'), Database.date ).subquery() meter_readings = dbsession.query( MeterReading.user_id, MeterReading.reading ). \ distinct( MeterReading.service_id, MeterReading.person_id_internal, MeterReading.meter_id). \ filter( and_( MeterReading.person_id == database.c.person_id, MeterReading.service_id == database.c.service_id, MeterReading.meter_id == database.c.meter_id, MeterReading.commit_date > database.c.date, MeterReading.rollback_date == None, MeterReading.reading != None ) ). \ order_by( MeterReading.service_id, MeterReading.person_id_internal, MeterReading.meter_id, MeterReading.commit_date.desc(), MeterReading.commit_time.desc() ).subquery().lateral() q = dbsession.query( meter_readings, database ). \ select_from(database). \ outerjoin(meter_readings, true()). \ filter(Database.person_id == person_id) return q File "/home/sector119/PycharmProjects/epsilon/epsilon/scripts/testdb.py", line 98, in get_person_meters_q (Database.meters_readings[func.generate_subscripts(Database.meters_readings, 1)][1]).label('meter_id'), File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/operators.py", line 411, in __getitem__ return self.operate(getitem, index) File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 692, in operate return op(self.comparator, *other, **kwargs) File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/operators.py", line 411, in __getitem__ return self.operate(getitem, index) File "", line 1, in File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/type_api.py", line 63, in operate return o[0](self.expr, op, *(other + o[1:]), **kwargs) File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/default_comparator.py", line 192, in _getitem_impl _unsupported_impl(expr, op, other, **kw) File "/home/sector119/PythonVirtualEnv/epsilon/lib/python3.5/site-packages/sqlalchemy/sql/default_comparator.py", line 197, in _unsupported_impl "this expression" % op.__name__) NotImplementedError: Operator 'getitem' is not supported on this expression -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.
Re: [sqlalchemy] update instance.relation.attr in instance.attr "set event listener"
Going back to the subject )) I get error (and no data updated) while using no_autoflush /Users/sector119/PythonVirtualEnv/supplements/lib/python2.7/site-packages/sqlalchemy/orm/session.py:2122: SAWarning: Attribute history events accumulated on 1 previously clean instances within inner-flush event handlers have been reset, and will not result in database updates. Consider using set_committed_value() within inner-flush event handlers to avoid this warning. def reviews_after_insert_listener(mapper, connection, target): session = object_session(target) with session.no_autoflush: print repr(target.overview) target.overview.reviews_count += 1 target.overview.rating_sum += target.overview_rating target.overview.rating_count += 1 if target.overview_flavor_rating is not None: target.overview_flavor.rating_sum += target.overview_flavor_rating target.overview_flavor.rating_count += 1 event.listen(ProductOverviewReview, 'after_insert', reviews_after_insert_listener) четверг, 25 февраля 2016 г., 15:41:03 UTC+2 пользователь Simon King написал: > > Er, ok. There are simpler ways to avoid autoflush > > https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/DisableAutoflush > > for example: > > session = sqlalchemy.orm.object_session(mapper) > with session.no_autoflush: > target.product.quantity += (value - oldvalue) > > ...but that still doesn't fix your underlying problem, which is that you > are assigning an unexpected object to your "label" property. I guess > FieldStorage comes from your web framework, and you need to extract the > actual value from that before assigning it to your mapped object. > > Simon > > On Thu, Feb 25, 2016 at 1:24 PM, sector119 > wrote: > >> Thanks a lot, I add >> >> @event.listens_for(ProductFlavor, 'after_update') >> def quantity_before_update_listener(mapper, connection, target): >> quantity = >> select([func.coalesce(func.sum(ProductFlavor.__table__.c.quantity), >> 0)]).where( >> ProductFlavor.__table__.c.product_id == Product.__table__.c.id) >> connection.execute( >> Product.__table__.update().where(Product.__table__.c.id == >> target.product_id).values(quantity=quantity)) >> >> >> четверг, 25 февраля 2016 г., 11:52:50 UTC+2 пользователь Simon King >> написал: >>> >>> On Wed, Feb 24, 2016 at 9:51 PM, sector119 wrote: >>> >>>> Hello! >>>> >>>> I have two models, Product and ProductFlavor with one-to-many >>>> relationship >>>> And I have a listener, which I want to update Product.quantity on >>>> ProductFlavor.quantity change: >>>> >>>> @event.listens_for(ProductFlavor.quantity, 'set') >>>> def quantity_set(target, value, oldvalue, initiator): >>>> if value != oldvalue: >>>> target.product.quantity += (value - oldvalue) >>>> >>>> >>>> But I get the following error: >>>> >>>> >>>> ProgrammingError: (raised as a result of Query-invoked autoflush; >>>> consider using a session.no_autoflush block if this flush is occurring >>>> prematurely) >>>> >>>> (psycopg2.ProgrammingError) can't adapt type 'instance' [SQL: 'UPDATE >>>> product_flavor SET label=%(label)s WHERE product_flavor.id = >>>> %(product_flavor_id)s'] [parameters: {'product_flavor_id': 4, 'label': >>>> FieldStorage('label', u'42bbebd1f7ba46b58d3d4b794b4b890e.png')}] >>>> >>>> What I'm doing wrong? >>>> >>> >>> It looks like you're assigning a non-string to your "label" column. This >>> isn't directly related to your attribute listener - the error would happen >>> even without the attribute listener when you called session.flush() or >>> session.commit(). The attribute listener is just causing the flush to >>> happen earlier presumably because "target.product" has not yet been loaded >>> from the database. >>> >>> 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+...@googlegroups.com . >> To post to this group, send email to sqlal...@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.
Re: [sqlalchemy] need help with query
With that query I get: InvalidRequestError: Could not find a FROM clause to join from. Tried joining to , but got: Can't find any foreign key relationships between 'category' and 'product'. Product and category model has many to many relationship -- 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.
[sqlalchemy] Re: need help with query
It works, but it's not pretty )) brand = DBSession.query(Brand).filter_by(slug=brand_slug).one() return DBSession.query(Category).filter(Category.overviews.any(brand_id=brand.id)).order_by(Category.name) вторник, 1 марта 2016 г., 21:52:47 UTC+2 пользователь sector119 написал: > > Hello, > > I need your help with one query, I want to get Category items that some > Product's of some Brand have. > > I try DBSession.query(Category).filter(Category.overviews.any(Brand.slug > == brand_slug)).order_by(Category.name) > But it doesn't work as expected because there is no relation between Brand > and other tables > > > class Brand(Base): > __tablename__ = 'brand' > > id = Column(Integer, primary_key=True) > name = Column(UnicodeText, nullable=False) > slug = Column(UnicodeText, nullable=False, unique=True) > > > class Category(Base): > __tablename__ = 'category' > > id = Column(Integer, primary_key=True) > name = Column(UnicodeText, nullable=False) > > > class Product(Base): > __tablename__ = 'product' > > id = Column(Integer, primary_key=True) > brand_id = Column(Integer, ForeignKey('brand.id'), nullable=False) > > name = Column(UnicodeText, nullable=False) > > brand = relationship('Brand', backref='products') > categories = relationship('Category', backref='products', > secondary=product_category) # many-to-many relationship > > > > -- 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.
[sqlalchemy] need help with query
Hello, I need your help with one query, I want to get Category items that some Product's of some Brand have. I try DBSession.query(Category).filter(Category.overviews.any(Brand.slug == brand_slug)).order_by(Category.name) But it doesn't work as expected because there is no relation between Brand and other tables class Brand(Base): __tablename__ = 'brand' id = Column(Integer, primary_key=True) name = Column(UnicodeText, nullable=False) slug = Column(UnicodeText, nullable=False, unique=True) class Category(Base): __tablename__ = 'category' id = Column(Integer, primary_key=True) name = Column(UnicodeText, nullable=False) class Product(Base): __tablename__ = 'product' id = Column(Integer, primary_key=True) brand_id = Column(Integer, ForeignKey('brand.id'), nullable=False) name = Column(UnicodeText, nullable=False) brand = relationship('Brand', backref='products') categories = relationship('Category', backref='products', secondary=product_category) # many-to-many relationship -- 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.
[sqlalchemy] Re: Need help with update + select query
product_t = Product.__table__ product_flavor_t = ProductFlavor.__table__ product_t_a = product_t.alias() op.add_column(u'product', sa.Column('servings', sa.Integer(), nullable=True)) op.add_column(u'product', sa.Column('flavors_count', sa.Integer(), nullable=True)) servings = select([func.coalesce(func.avg(product_flavor_t.c.size), 0)]).\ select_from(product_t_a.outerjoin(product_flavor_t)).\ where(product_t.c.id == product_t_a.c.id) flavors_count = select([func.count(product_flavor_t.c.id)]).\ select_from(product_t_a.outerjoin(product_flavor_t)).\ where(product_t.c.id == product_t_a.c.id) op.execute(product_t.update().values(servings=servings, flavors_count=flavors_count)) op.alter_column(u'product', 'servings', nullable=False) op.alter_column(u'product', 'flavors_count', nullable=False) -- 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.
[sqlalchemy] Re: Need help with update + select query
Postgresql doesn't support this yet, so I have to use separate query all aggregates (( SET (servings, flavors_count) = ( SELECT coalesce(avg(f.size), 0), count(f.id) FROM product p LEFT OUTER JOIN product_flavor f ON p.id = f.product_id WHERE product.id = p.id четверг, 25 февраля 2016 г., 15:19:11 UTC+2 пользователь sector119 написал: > > Hello, > > Can some one help me with that query? I get AttributeError: servings > I expect that sqlalchemy use update from select for that query or it's not > possible and I must use select(...).as_scalar() for every updated column? > > s = > select([func.coalesce(func.avg(product_flavor_t.c.size).label('servings'), > 0), > func.count().label('flavors_count')]).\ > where(and_(product_flavor_t.c.product_id == product_t.c.id, > product_flavor_t.c.quantity > 0)) > > op.execute(product_t.update().values(servings=s.c.servings, > flavors_count=s.c.flavors_count)) > > Thanks! > -- 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.
Re: [sqlalchemy] Need help with update + select query
s = select([func.coalesce(func.avg(product_flavor_t.c.size), 0).label('f1'), func.count(product_flavor_t.c.id).label('f2')]).\ select_from(product_t.outerjoin(product_flavor_t)) op.execute(product_t.update().values(servings=s.c.f1, flavors_count=s.c.f2)) I tried that, but it doesn't work as expected (( I got UPDATE without SELECT : sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "f1" does not exist LINE 1: UPDATE product SET servings=f1, flavors_count=f2 And PostgreSQL log file contains: statement: ALTER TABLE product ADD COLUMN servings INTEGER statement: ALTER TABLE product ADD COLUMN flavors_count INTEGER statement: UPDATE product SET servings=f1, flavors_count=f2 ERROR: column "f1" does not exist at character 29 STATEMENT: UPDATE product SET servings=f1, flavors_count=f2 statement: ROLLBACK -- 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.
Re: [sqlalchemy] update instance.relation.attr in instance.attr "set event listener"
Thanks a lot, I add @event.listens_for(ProductFlavor, 'after_update') def quantity_before_update_listener(mapper, connection, target): quantity = select([func.coalesce(func.sum(ProductFlavor.__table__.c.quantity), 0)]).where( ProductFlavor.__table__.c.product_id == Product.__table__.c.id) connection.execute( Product.__table__.update().where(Product.__table__.c.id == target.product_id).values(quantity=quantity)) четверг, 25 февраля 2016 г., 11:52:50 UTC+2 пользователь Simon King написал: > > On Wed, Feb 24, 2016 at 9:51 PM, sector119 > wrote: > >> Hello! >> >> I have two models, Product and ProductFlavor with one-to-many relationship >> And I have a listener, which I want to update Product.quantity on >> ProductFlavor.quantity change: >> >> @event.listens_for(ProductFlavor.quantity, 'set') >> def quantity_set(target, value, oldvalue, initiator): >> if value != oldvalue: >> target.product.quantity += (value - oldvalue) >> >> >> But I get the following error: >> >> >> ProgrammingError: (raised as a result of Query-invoked autoflush; >> consider using a session.no_autoflush block if this flush is occurring >> prematurely) >> >> (psycopg2.ProgrammingError) can't adapt type 'instance' [SQL: 'UPDATE >> product_flavor SET label=%(label)s WHERE product_flavor.id = >> %(product_flavor_id)s'] [parameters: {'product_flavor_id': 4, 'label': >> FieldStorage('label', u'42bbebd1f7ba46b58d3d4b794b4b890e.png')}] >> >> What I'm doing wrong? >> > > It looks like you're assigning a non-string to your "label" column. This > isn't directly related to your attribute listener - the error would happen > even without the attribute listener when you called session.flush() or > session.commit(). The attribute listener is just causing the flush to > happen earlier presumably because "target.product" has not yet been loaded > from the database. > > 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Need help with update + select query
Hello, Can some one help me with that query? I get AttributeError: servings I expect that sqlalchemy use update from select for that query or it's not possible and I must use select(...).as_scalar() for every updated column? s = select([func.coalesce(func.avg(product_flavor_t.c.size).label('servings'), 0), func.count().label('flavors_count')]).\ where(and_(product_flavor_t.c.product_id == product_t.c.id, product_flavor_t.c.quantity > 0)) op.execute(product_t.update().values(servings=s.c.servings, flavors_count=s.c.flavors_count)) Thanks! -- 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.
[sqlalchemy] update instance.relation.attr in instance.attr "set event listener"
Hello! I have two models, Product and ProductFlavor with one-to-many relationship And I have a listener, which I want to update Product.quantity on ProductFlavor.quantity change: @event.listens_for(ProductFlavor.quantity, 'set') def quantity_set(target, value, oldvalue, initiator): if value != oldvalue: target.product.quantity += (value - oldvalue) But I get the following error: ProgrammingError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (psycopg2.ProgrammingError) can't adapt type 'instance' [SQL: 'UPDATE product_flavor SET label=%(label)s WHERE product_flavor.id = %(product_flavor_id)s'] [parameters: {'product_flavor_id': 4, 'label': FieldStorage('label', u'42bbebd1f7ba46b58d3d4b794b4b890e.png')}] What I'm doing wrong? Thank You! -- 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.
[sqlalchemy] Re: How to jsonify Query result?
Ok, that approach isn't cool.. I get another one from flask_jsontools and it does what I need! json.dumps(q, cls=DynamicJSONEncoder) Base = declarative_base(cls=JsonSerializableBase) import decimal from datetime import datetime, date from json import JSONEncoder from sqlalchemy import inspect from sqlalchemy.orm.state import InstanceState class DynamicJSONEncoder(JSONEncoder): def default(self, obj): if isinstance(obj, (datetime, date)): return obj.isoformat() elif isinstance(obj, decimal.Decimal): return float(obj) elif hasattr(obj, '__json__'): return obj.__json__() return super(DynamicJSONEncoder, self).default(obj) def get_entity_propnames(entity): ins = entity if isinstance(entity, InstanceState) else inspect(entity) return set(ins.mapper.column_attrs.keys() + ins.mapper.relationships.keys()) def get_entity_loaded_propnames(entity): """ Get entity property names that are loaded (e.g. won't produce new queries)""" ins = inspect(entity) keynames = get_entity_propnames(ins) # If the entity is not transient -- exclude unloaded keys # Transient entities won't load these anyway, so it's safe to include all columns and get defaults if not ins.transient: keynames -= ins.unloaded # If the entity is expired -- reload expired attributes as well # Expired attributes are usually unloaded as well! if ins.expired: keynames |= ins.expired_attributes return keynames class JsonSerializableBase(object): """ Declarative Base mixin to allow objects serialization""" __json_private__ = set() def __json__(self): return {name: getattr(self, name) for name in get_entity_loaded_propnames(self) - self.__json_private__} -- 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: How to jsonify Query result?
I create Serializer class and use it for all my models with: Base = declarative_base(cls=Serializer) But I have one problem! I have Organization model and Service model that has "organization = relationship('Organization', backref='services')" relationship and I get "RuntimeError: maximum recursion depth exceeded while calling a Python object" What can I do with that? Help me please! Traceback (most recent call last): File "s.py", line 10, in print repr(r.to_dict()) File "/Users/sector119/PycharmProjects/epsilon/epsilon/models/serializer.py", line 31, in to_dict value = self._serialize(key, field.value) File "/Users/sector119/PycharmProjects/epsilon/epsilon/models/serializer.py", line 48, in _serialize result.append(cls._serialize(key, v)) File "/Users/sector119/PycharmProjects/epsilon/epsilon/models/serializer.py", line 51, in _serialize result = value.to_dict() File "/Users/sector119/PycharmProjects/epsilon/epsilon/models/serializer.py", line 31, in to_dict ... ... ... File "/Users/sector119/PythonVirtualEnv/epsilon/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 671, in value self.state.obj(), self.state.class_) File "/Users/sector119/PythonVirtualEnv/epsilon/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 233, in __get__ dict_ = instance_dict(instance) RuntimeError: maximum recursion depth exceeded while calling a Python object from datetime import datetime, date from sqlalchemy.inspection import inspect class Serializer(object): __public__ = None __private__ = None def to_dict(self, exclude=(), extra=()): data = {} items = inspect(self).attrs.items() if self.__public__: public = self.__public__ + extra else: public = extra if self.__private__: private = self.__private__ + exclude else: private = exclude for key, field in items: if private and key in private: continue if public and key not in public: continue value = self._serialize(key, field.value) if value: data[key] = value return data @classmethod def _serialize(cls, key, value): print 'Field name: %s' % key if isinstance(value, (datetime, date)): print 'Type date' result = value.isoformat() elif hasattr(value, '__iter__'): print 'Type iterable' result = [] for v in value: result.append(cls._serialize(key, v)) elif isinstance(value, Serializer): print 'Type Serializer' result = value.to_dict() else: print 'Type other' result = value print 'Result %r\n' % result return result -- 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 jsonify Query result?
Hello, How can we jsonify the tuples returned by Query ? Thanks. -- 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] Can't create table with ENUM type column
Thanks a lot for your help, Michael ! -- 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: Can't create table with ENUM type column
Code below creates enum type, but server_default still doesn't work as I expect )) import_action = ENUM('copy', 'insert', 'update', name='import_action') class Import(Base): __tablename__ = 'import' id = Column(Integer, primary_key=True) name = Column(UnicodeText, nullable=False) actions = Column(ARRAY(import_action), server_default=array(['copy'], type_=import_action), nullable=False) @event.listens_for(Import.__table__, 'before_create') def receive_before_create(target, connection, **kwargs): import_action.create(connection) -- 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: Can't create table with ENUM type column
Column(ENUM('copy', 'insert', 'update', name='import_action')) *works* Column(ARRAY(ENUM('copy', 'insert', 'update', name='import_action'))) *doesn't create enum type * -- 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] Can't create table with ENUM type column
Hello, Help me create table with enum type column, SA do not create enum type before table :( And server_default doesn't set value that I expect, "actions import_action[] DEFAULT ARRAY['copy'] NOT NULL" it set array['copy'], not import_action['copy'] as default... Code and exception: from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column from sqlalchemy import Integer, UnicodeText from sqlalchemy.dialects.postgresql import ARRAY, array, ENUM engine = create_engine('postgresql+psycopg2://user:password@127.0.0.1:5432/epsilon', echo=True) Base = declarative_base() DBSession = sessionmaker(bind=engine)() Base.metadata.bind = engine import_action = ENUM('copy', 'insert', 'update', name='import_action', metadata=Base.metadata) class Import(Base): __tablename__ = 'import' id = Column(Integer, primary_key=True) name = Column(UnicodeText, nullable=False) actions = Column(ARRAY(import_action), server_default=array(['copy'], type_=import_action), nullable=False) Import.__table__.create(checkfirst=True) sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) type "import_action[]" does not exist LINE 5: actions import_action[] DEFAULT ARRAY['copy'] NOT NULL, ^ [SQL: "\nCREATE TABLE import (\n\tid SERIAL NOT NULL, \n\tname TEXT NOT NULL, \n\tactions import_action[] DEFAULT ARRAY['copy'] NOT NULL, \n\tPRIMARY KEY (id)\n)\n\n"] -- 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] Can't create table in just created schema
Hello! I can't create table in just created schema without commit() after CreateSchema statement, code and traceback posted below. Is it Ok? Or I make something wrong? PS. Can I call CreateSchema with ".execute_if(callable_=should_create)" ? from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.schema import CreateSchema from epsilon.models import Base, SYSTEM_SCHEMA, SYSTEM_MODELS engine = create_engine('postgresql+psycopg2://USER:PASSWORD@127.0.0.1:5432/epsilon', echo=True) DBSession = sessionmaker(bind=engine)() Base.metadata.bind = engine def create_schema(name): s = CreateSchema(name) DBSession.execute(s) #DBSession.commit() // IF I uncomment this line, everything work fine try: create_schema(SYSTEM_SCHEMA) DBSession.execute('SET search_path TO %s' % SYSTEM_SCHEMA) for m in SYSTEM_MODELS: m.__table__.create(checkfirst=True) DBSession.commit() except: DBSession.rollback() raise *2015-06-28 20:39:04,633 INFO sqlalchemy.engine.base.Engine select version() 2015-06-28 20:39:04,633 INFO sqlalchemy.engine.base.Engine {} 2015-06-28 20:39:04,634 INFO sqlalchemy.engine.base.Engine select current_schema() 2015-06-28 20:39:04,635 INFO sqlalchemy.engine.base.Engine {} 2015-06-28 20:39:04,636 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 2015-06-28 20:39:04,636 INFO sqlalchemy.engine.base.Engine {} 2015-06-28 20:39:04,637 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1 2015-06-28 20:39:04,638 INFO sqlalchemy.engine.base.Engine {} 2015-06-28 20:39:04,639 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings 2015-06-28 20:39:04,639 INFO sqlalchemy.engine.base.Engine {} 2015-06-28 20:39:04,640 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2015-06-28 20:39:04,640 INFO sqlalchemy.engine.base.Engine CREATE SCHEMA system 2015-06-28 20:39:04,640 INFO sqlalchemy.engine.base.Engine {} 2015-06-28 20:39:04,641 INFO sqlalchemy.engine.base.Engine SET search_path TO system 2015-06-28 20:39:04,641 INFO sqlalchemy.engine.base.Engine {} 2015-06-28 20:39:04,650 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=%(schema)s and relname=%(name)s 2015-06-28 20:39:04,650 INFO sqlalchemy.engine.base.Engine {'name': u'locality', 'schema': u'system'} 2015-06-28 20:39:04,654 INFO sqlalchemy.engine.base.Engine CREATE TABLE system.locality ( id SERIAL NOT NULL, parent_id INTEGER, name TEXT NOT NULL, type INTEGER NOT NULL, schema VARCHAR(63) NOT NULL, PRIMARY KEY (id), FOREIGN KEY(parent_id) REFERENCES system.locality (id), UNIQUE (schema) ) 2015-06-28 20:39:04,654 INFO sqlalchemy.engine.base.Engine {} 2015-06-28 20:39:04,654 INFO sqlalchemy.engine.base.Engine ROLLBACK 2015-06-28 20:39:04,655 INFO sqlalchemy.engine.base.Engine ROLLBACK Traceback (most recent call last):* *...* *...* *...* *sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) schema "system" does not exist* -- 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 model with dynamically set schema?
Michael, I want to set search_path to locality schema that depends on user settings. And I have some kind of admin utility where user can setup locality environment (schema) with set of tables and I have the list of models those should be created at some schema that admin-user would specify.. How can I create those list of Models at some table? -- 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 model with dynamically set schema?
Hello! I have some model: class Person(Base): __tablename__ = 'person' id = Column(BigInteger, primary_key=True, autoincrement=False) locality_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + '.locality.id'), nullable=False) street_id = Column(Integer, ForeignKey('street.id'), nullable=False) building = Column(Integer, nullable=False) apartment = Column(Integer, nullable=False) ... I want to create this model at different schemas: 'schema1', 'schema2', 'schema3' and model's FK street_id should point to 'street.id' at the same schema! How can I do that? -- 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: Can't access model object backref
I want to order_by(Category.parent.name, Category.name) is it possible? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Can't access model object backref
Hello. I have Category model: class Category(Base): __tablename__ = 'categories' id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey('categories.id')) name = Column(Unicode(255), nullable=False) description = Column(UnicodeText) position = Column(Integer) children = relationship('Category', backref=backref('parent', remote_side=[id]), lazy='joined', join_depth=1, order_by='Category.position') But I can't access it's 'parent' backref (I want to use it in order_by). Why? >>> from whs.models import Category >>> Category.parent Traceback (most recent call last): File "", line 1, in AttributeError: type object 'Category' has no attribute 'parent' -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: column_property and class Mixin problem
Thanks a lot, Michael! Works like a charm! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/W6s3M_bzrRAJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] column_property and class Mixin problem
Now I get File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 59, in _compiler_dispatch return getter(visitor)(self, **kw) File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 370, in visit_column raise exc.CompileError("Cannot compile Column object until " CompileError: Cannot compile Column object until it's 'name' is assigned. With case([(cls.last_read != None, cls.last_read <= func.now() - datetime.timedelta(minutes=30))], else_=literal(False)) case([(cls.last_read != None, literal(cls.last_read <= func.now() - datetime.timedelta(minutes=30)))], else_=literal(False)) case([(cls.last_read != None, literal(cls.last_read <= func.now() - datetime.timedelta(minutes=30)).label('foo'))], else_=literal(False)) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/vRfXbta7DoMJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] column_property and class Mixin problem
Hello. Can't get this to work, I want to get users who is online - users where last_read column <= now() - 30 minutes With DBSession.query(User).filter(User.is_online) query But get the following error: File "/home/eps/devel/tourclub/pbb/pbb/models/__init__.py", line 147, in class User(UserMixin, Base): File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py", line 1273, in __init__ _as_declarative(cls, classname, cls.__dict__) File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py", line 1078, in _as_declarative column_copies[obj] = getattr(cls, name) File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/ext/declarative.py", line 1480, in __get__ return desc.fget(cls) File "/home/eps/devel/tourclub/pbb/pbb/models/__init__.py", line 143, in is_online return column_property(case([(cls.last_read is not None, cls.last_read <= func.now() - datetime.timedelta(minutes=30))], else_=False)) File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py", line 607, in case return _Case(whens, value=value, else_=else_) File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py", line 3016, in __init__ _literal_as_binds(r)) for (c, r) in whens File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/sql/expression.py", line 1410, in _no_literals "bound value." % element) sqlalchemy.exc.ArgumentError: Ambiguous literal: True. Use the 'text()' function to indicate a SQL expression literal, or 'literal()' to indicate a bound value. I want to get something like this but in SA... SELECT * FROM "user" u WHERE (CASE WHEN u.last_read IS NULL THEN False ELSE u.last_read <= now() - 30 * INTERVAL '1 minute' END) IS True; I use next UserMixin class and User declarative model: class UserMixin(object): id = Column(Integer, primary_key=True) username = Column(String(255), unique=True, nullable=False) ... last_read = Column(DateTime) @declared_attr def is_online(cls): return column_property(case([(cls.last_read is not None, cls.last_read <= func.now() - datetime.timedelta(minutes=30))], else_=False)) class User(UserMixin, Base): __tablename__ = 'user' -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/KUr2Iqu__x0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: can't get delete-orphan work
Thanks a lot, Michael! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Vc5LM46bOiUJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] can't get delete-orphan work
Hi. I think that I'm doing somethig wrong, but I can't get delete-orphah work... No DELETES on static_page_urls are performed... 2011-11-12 12:31:14 EET LOG: statement: BEGIN 2011-11-12 12:31:14 EET LOG: statement: DELETE FROM static_pages WHERE static_pages.id = 1 2011-11-12 12:31:14 EET ERROR: update or delete on table "static_pages" violates foreign key constraint "static_page_urls_page_id_fkey" on table "static_page_urls" 2011-11-12 12:31:14 EET DETAIL: Key (id)=(1) is still referenced from table "static_page_urls". 2011-11-12 12:31:14 EET STATEMENT: DELETE FROM static_pages WHERE static_pages.id = 1 from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, ForeignKey from sqlalchemy import Integer, Unicode, UnicodeText from sqlalchemy.orm import relationship engine = create_engine('postgresql+psycopg2://USER:PASSWORD@127.0.0.1:5432/test', echo=True) Session = sessionmaker() Base = declarative_base() Session.configure(bind=engine) Base.metadata.bind = engine class StaticPageUrl(Base): __tablename__ = 'static_page_urls' id = Column(Integer, primary_key=True) title = Column(Unicode(255), nullable=False) url = Column(UnicodeText, nullable=False) page_id = Column(Integer, ForeignKey('static_pages.id'), nullable=False) class StaticPage(Base): __tablename__ = 'static_pages' id = Column(Integer, primary_key=True) title = Column(Unicode(255), nullable=False) urls = relationship(StaticPageUrl, cascade="all, delete, delete-orphan") Base.metadata.create_all(engine) session = Session() p = StaticPage(title='test page') p.urls.append(StaticPageUrl(title='testurl', url='www.url.org')) session.add(p) session.flush() session.commit() session.close() session = Session() user = session.query(StaticPage).filter_by(id=1).delete() session.add(report) session.flush() session.commit() session.close() -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/DOaTfEuV_bYJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: after_insert mapper event: can't populate target.BACKREF.attr
this works without any error, comment_after_insert_listener() prints that target.report.comments_count = 1, but this value isn't commited... Can't get why prev testcase fails, this works, but target.report.comments_count value isn't saved :/ def comment_after_insert_listener(mapper, connection, target): target.report.comments_count = target.report.comments_count + 1 print target.report.comments_count event.listen(Comment, 'after_insert', comment_after_insert_listener) session = Session() report = session.query(Report).get(1) comment = Comment(content=u'test comment', report=report) session.add(comment) session.commit() -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/7TouhhL3eSwJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: to many statements for collection.append?
In testcase above this works too: session = Session() user = session.query(User).filter_by(username='sector119').one() session.close() session = Session() report = session.query(Report).get(1) comment = Comment(content=u'test comment', user=user) report.comments.append(comment) session.add(report) session.flush() session.commit() session.close() 2011-11-09 13:43:28 EET LOG: statement: BEGIN 2011-11-09 13:43:28 EET LOG: statement: INSERT INTO users (username) VALUES ('sector119') RETURNING users.id 2011-11-09 13:43:28 EET LOG: statement: COMMIT 2011-11-09 13:43:28 EET LOG: statement: BEGIN 2011-11-09 13:43:28 EET LOG: statement: SELECT users.id AS users_id, users.username AS users_username FROM users WHERE users.username = 'sector119' 2011-11-09 13:43:28 EET LOG: statement: INSERT INTO report (content, user_id) VALUES ('test report', 1) RETURNING report.id 2011-11-09 13:43:28 EET LOG: statement: COMMIT 2011-11-09 13:43:28 EET LOG: statement: BEGIN 2011-11-09 13:43:28 EET LOG: statement: SELECT users.id AS users_id, users.username AS users_username FROM users WHERE users.username = 'sector119' 2011-11-09 13:43:28 EET LOG: statement: ROLLBACK 2011-11-09 13:43:28 EET LOG: statement: BEGIN 2011-11-09 13:43:28 EET LOG: statement: SELECT report.id AS report_id, report.content AS report_content, report.comments_count AS report_comments_count, report.user_id AS report_user_id FROM report WHERE report.id = 1 2011-11-09 13:43:28 EET LOG: statement: SELECT comment.id AS comment_id, comment.content AS comment_content, comment.report_id AS comment_report_id, comment.user_id AS comment_user_id FROM comment WHERE 1 = comment.report_id ORDER BY comment.id 2011-11-09 13:43:28 EET LOG: statement: INSERT INTO comment (content, report_id, user_id) VALUES ('test comment', 1, 1) RETURNING comment.id 2011-11-09 13:43:28 EET LOG: statement: COMMIT -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/cORJHpjqz2wJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: to many statements for collection.append?
Can't get why, but in Pyramid, the same code works, but generate on more UPDATE statement report = session.query(TripReport).get(id) user = session.query(User).filter_by(username='sector119').one() comment = TripReportComment(content=form.content.data, user=user) report.comments.append(comment) session.add(report) 2011-11-09 13:36:35 EET LOG: statement: BEGIN 2011-11-09 13:36:35 EET LOG: statement: SELECT trip_reports.id AS trip_reports_id, trip_reports.title AS trip_reports_title, trip_reports.content AS trip_reports_content, trip_reports.route AS trip_reports_route, trip_reports.date_start AS trip_reports_date_start, trip_reports.date_end AS trip_reports_date_end, trip_reports.type_id AS trip_reports_type_id, trip_reports.user_id AS trip_reports_user_id, trip_reports.schedule_id AS trip_reports_schedule_id, trip_reports.create_date AS trip_reports_create_date, trip_reports.comments_count AS trip_reports_comments_count FROM trip_reports WHERE trip_reports.id = '7' 2011-11-09 13:36:35 EET LOG: statement: SELECT users.password AS users_password, users.id AS users_id, users.username AS users_username, users.first_name AS users_first_name, users.last_name AS users_last_name, users.birthday AS users_birthday, users.sex AS users_sex, users.email AS users_email, users.organization AS users_organization, users.website AS users_website, users.signature AS users_signature, users.subscribed AS users_subscribed, users.create_date AS users_create_date, users.last_login AS users_last_login, users.disabled AS users_disabled FROM users WHERE users.username = 'sector119' 2011-11-09 13:36:35 EET LOG: statement: INSERT INTO trip_report_comments (content, user_id, report_id) VALUES ('test3', 6, NULL) RETURNING trip_report_comments.id 2011-11-09 13:36:35 EET LOG: duration: 1.014 ms 2011-11-09 13:36:35 EET LOG: statement: SELECT trip_report_comments.id AS trip_report_comments_id, trip_report_comments.content AS trip_report_comments_content, trip_report_comments.create_date AS trip_report_comments_create_date, trip_report_comments.user_id AS trip_report_comments_user_id, trip_report_comments.report_id AS trip_report_comments_report_id FROM trip_report_comments WHERE 7 = trip_report_comments.report_id ORDER BY trip_report_comments.id ### HERE IS UPDATE :/ 2011-11-09 13:36:35 EET LOG: statement: UPDATE trip_report_comments SET report_id=7 WHERE trip_report_comments.id = 18 2011-11-09 13:36:35 EET LOG: statement: COMMIT -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/yUYRzTS3JuMJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] to many statements for collection.append?
### TEST 1 STATEMENTS 2011-11-09 13:30:24 EET LOG: statement: BEGIN 2011-11-09 13:30:24 EET LOG: statement: SELECT report.id AS report_id, report.content AS report_content, report.comments_count AS report_comments_count, report.user_id AS report_user_id FROM report WHERE report.id = 1 2011-11-09 13:30:24 EET LOG: statement: SELECT users.id AS users_id, users.username AS users_username FROM users WHERE users.username = 'sector119' 2011-11-09 13:30:24 EET LOG: statement: SELECT comment.id AS comment_id, comment.content AS comment_content, comment.report_id AS comment_report_id, comment.user_id AS comment_user_id FROM comment WHERE 1 = comment.report_id ORDER BY comment.id 2011-11-09 13:30:24 EET LOG: statement: INSERT INTO comment (content, report_id, user_id) VALUES ('test comment', 1, 1) RETURNING comment.id 2011-11-09 13:30:24 EET LOG: statement: COMMIT ### TEST 2 STATEMENTS 2011-11-09 13:30:24 EET LOG: statement: BEGIN 2011-11-09 13:30:24 EET LOG: statement: SELECT report.id AS report_id, report.content AS report_content, report.comments_count AS report_comments_count, report.user_id AS report_user_id FROM report WHERE report.id = 1 2011-11-09 13:30:24 EET LOG: statement: SELECT users.id AS users_id, users.username AS users_username FROM users WHERE users.username = 'sector119' 2011-11-09 13:30:24 EET LOG: statement: INSERT INTO comment (content, report_id, user_id) VALUES ('test comment', NULL, 1) RETURNING comment.id 2011-11-09 13:30:24 EET ERROR: null value in column "report_id" violates not-null constraint 2011-11-09 13:30:24 EET STATEMENT: INSERT INTO comment (content, report_id, user_id) VALUES ('test comment', NULL, 1) RETURNING comment.id 2011-11-09 13:30:24 EET LOG: statement: ROLLBACK ### TEST CASE from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, ForeignKey from sqlalchemy import Integer, String, UnicodeText from sqlalchemy import event from sqlalchemy.orm import relationship engine = create_engine('postgresql+psycopg2://USER:PASSWORD@127.0.0.1:5432/test', echo=True) Session = sessionmaker() Base = declarative_base() Session.configure(bind=engine) Base.metadata.bind = engine class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) username = Column(String(255), unique=True, nullable=False) class Comment(Base): __tablename__ = 'comment' id = Column(Integer, primary_key=True) content = Column(UnicodeText, nullable=False) report_id = Column(Integer, ForeignKey('report.id'), nullable=False) user_id = Column(Integer, ForeignKey('users.id'), nullable=False) user = relationship('User', backref='comments') class Report(Base): __tablename__ = 'report' id = Column(Integer, primary_key=True) content = Column(UnicodeText, nullable=False) comments_count = Column(Integer, server_default='0', nullable=False) user_id = Column(Integer, ForeignKey('users.id'), nullable=False) user = relationship('User', backref='reports') comments = relationship('Comment', backref='report', cascade='all, delete-orphan', order_by='Comment.id') Base.metadata.create_all(engine) session = Session() user = User(username='sector119') session.add(user) session.flush() session.commit() session.close() session = Session() user = session.query(User).filter_by(username='sector119').one() report = Report(content=u'test report', user=user) session.add(report) session.flush() session.commit() session.close() session = Session() ### TEST 1 report = session.query(Report).get(1) user = session.query(User).filter_by(username='sector119').one() comment = Comment(content=u'test comment', user_id=user.id) report.comments.append(comment) session.add(report) session.flush() session.commit() session.close() ### TEST 2 # HERE I GOT ERROR if I uncomment "user = ..." line session = Session() report = session.query(Report).get(1) #user = session.query(User).filter_by(username='sector119').one() comment = Comment(content=u'test comment', user=user) report.comments.append(comment) session.add(report) session.flush() session.commit() session.close() 2011-11-09 13:22:10,270 INFO sqlalchemy.engine.base.Engine INSERT INTO comment (content, report_id, user_id) VALUES (%(content)s, %(report_id)s, %(user_id)s) RETURNING comment.id 2011-11-09 13:22:10,270 INFO sqlalchemy.engine.base.Engine {'content': u'test comment', 'user_id': 1, 'r
[sqlalchemy] Re: to many statements for collection.append?
Thanks, Michael, it was my mistake. I use sqlalchemy with pyramid and comment = ReportComment(content=form.content.data, user=self.request.user) where self.request.user is class RequestFactory(Request): @reify def user(self): db = DBSession() username = unauthenticated_userid(self) if username is not None: return db.query(User).filter_by(username=username).first() that is why I have 'broken' session :/ -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/TEhPf5UVzJcJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: after_insert mapper event: can't populate target.BACKREF.attr
# test_event.py from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, ForeignKey from sqlalchemy import Integer, UnicodeText from sqlalchemy import event from sqlalchemy.orm import relationship engine = create_engine('postgresql+psycopg2://USER:PASSWORD@127.0.0.1:5432/test', echo=True) Session = sessionmaker() Base = declarative_base() Session.configure(bind=engine) Base.metadata.bind = engine class Comment(Base): __tablename__ = 'comment' id = Column(Integer, primary_key=True) content = Column(UnicodeText, nullable=False) report_id = Column(Integer, ForeignKey('report.id'), nullable=False) def comment_after_insert_listener(mapper, connection, target): target.report.comments_count += 1 event.listen(Comment, 'after_insert', comment_after_insert_listener) class Report(Base): __tablename__ = 'report' id = Column(Integer, primary_key=True) content = Column(UnicodeText, nullable=False) comments_count = Column(Integer, server_default='0', nullable=False) comments = relationship('Comment', backref='report', cascade='all, delete-orphan', order_by='Comment.id') Base.metadata.create_all(engine) session = Session() report = Report(content=u'test report') session.add(report) session.flush() session.commit() comment = Comment(content=u'test comment', report_id=1) session.add(comment) session.flush() session.commit() % /home/eps/tourclub/bin/python test_event.py 2011-11-09 10:19:38,970 INFO sqlalchemy.engine.base.Engine select version() 2011-11-09 10:19:38,970 INFO sqlalchemy.engine.base.Engine {} 2011-11-09 10:19:38,972 INFO sqlalchemy.engine.base.Engine select current_schema() 2011-11-09 10:19:38,972 INFO sqlalchemy.engine.base.Engine {} 2011-11-09 10:19:38,974 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s 2011-11-09 10:19:38,974 INFO sqlalchemy.engine.base.Engine {'name': u'report'} 2011-11-09 10:19:38,976 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s 2011-11-09 10:19:38,976 INFO sqlalchemy.engine.base.Engine {'name': u'comment'} 2011-11-09 10:19:38,978 INFO sqlalchemy.engine.base.Engine CREATE TABLE report ( id SERIAL NOT NULL, content TEXT NOT NULL, comments_count INTEGER DEFAULT '0' NOT NULL, PRIMARY KEY (id) ) 2011-11-09 10:19:38,978 INFO sqlalchemy.engine.base.Engine {} 2011-11-09 10:19:39,041 INFO sqlalchemy.engine.base.Engine COMMIT 2011-11-09 10:19:39,051 INFO sqlalchemy.engine.base.Engine CREATE TABLE comment ( id SERIAL NOT NULL, content TEXT NOT NULL, report_id INTEGER NOT NULL, PRIMARY KEY (id), FOREIGN KEY(report_id) REFERENCES report (id) ) 2011-11-09 10:19:39,051 INFO sqlalchemy.engine.base.Engine {} 2011-11-09 10:19:39,134 INFO sqlalchemy.engine.base.Engine COMMIT 2011-11-09 10:19:39,145 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2011-11-09 10:19:39,146 INFO sqlalchemy.engine.base.Engine INSERT INTO report (content) VALUES (%(content)s) RETURNING report.id 2011-11-09 10:19:39,146 INFO sqlalchemy.engine.base.Engine {'content': u'test report'} 2011-11-09 10:19:39,147 INFO sqlalchemy.engine.base.Engine COMMIT 2011-11-09 10:19:39,151 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2011-11-09 10:19:39,152 INFO sqlalchemy.engine.base.Engine INSERT INTO comment (content, report_id) VALUES (%(content)s, %(report_id)s) RETURNING comment.id 2011-11-09 10:19:39,152 INFO sqlalchemy.engine.base.Engine {'content': u'test comment', 'report_id': 1} 2011-11-09 10:19:39,154 INFO sqlalchemy.engine.base.Engine ROLLBACK Traceback (most recent call last): File "test_event.py", line 54, in session.flush() File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1547, in flush self._flush(objects) File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1616, in _flush flush_context.execute() File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 328, in execute rec.execute(self) File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 472, in execute uow File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 2270, in _save_obj mapper.dispatch.after_insert(mapper, connection, state) File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/event.py", line 274, in __call__ fn(*args, **kw) File "/home/eps/devel/tourclub/sqlalchemy/lib/sqlalchemy/orm/events.py", line 360, in wrap wrapped_fn(*arg, **kw) File "tes
[sqlalchemy] to many statements for collection.append?
Hi. Why SA produce last UPDATE if I set report_id value? And why it produces those two SELECTs if I do not perform read access to report and report.comments? Should not it exec only one insert? report = session.query(TripReport).get(id) comment = TripReportComment(content=form.content.data, user=request.user, report_id=form.report_id.data) report.comments.append(comment) session.add(report) SELECT trip_reports.id AS trip_reports_id, trip_reports.title AS trip_reports_ti tle, trip_reports.content AS trip_reports_content, trip_reports.route AS trip_reports_route, trip_reports.date_start AS t rip_reports_date_start, trip_reports.date_end AS trip_reports_date_end, trip_reports.type_id AS trip_reports_type_id, tri p_reports.user_id AS trip_reports_user_id, trip_reports.schedule_id AS trip_reports_schedule_id, trip_reports.create_date AS trip_reports_create_date, trip_reports.comments_count AS trip_reports_comments_count FROM trip_reports WHERE trip_reports.id = '7' INSERT INTO trip_report_comments (content, user_id, report_id) VALUES ('test content', 6, '7') RETURNING trip_report_comments.id SELECT trip_report_comments.id AS trip_report_comments_id, trip_report_comments. content AS trip_report_comments_content, trip_report_comments.create_date AS trip_report_comments_create_date, trip_repor t_comments.user_id AS trip_report_comments_user_id, trip_report_comments.report_id AS trip_report_comments_report_id FROM trip_report_comments WHERE 7 = trip_report_comments.report_id ORDER BY trip_report_comments.id UPDATE trip_report_comments SET report_id=7 WHERE trip_report_comments.id = 5 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/YKVkfJu5Q80J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] after_insert mapper event: can't populate target.BACKREF.attr
Hello. Why comments_after_insert_listener return None for target.report - backref for TripReport.comments? class TripReportComment(Base): __tablename__ = 'trip_report_comments' id = Column(Integer, primary_key=True) content = Column(UnicodeText, nullable=False) report_id = Column(Integer, ForeignKey('trip_reports.id'), nullable=False) def comments_after_insert_listener(mapper, connection, target): print target.report event.listen(TripReportComment, 'after_insert', comments_after_insert_listener) class TripReport(Base): __tablename__ = 'trip_reports' id = Column(Integer, primary_key=True) ... comments = relationship('TripReportComment', backref='report', cascade='all, delete-orphan', order_by='TripReportComment.id') -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/xlOoyFWOAZUJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to replace selectable but not with original table alias?
Thanks a lot, Michael. Really, I can make t = t1 or t = t2 and then use t in select() On 12 Жов, 16:46, Michael Bayer wrote: > replace_selectable can only swap in another selectable that's derived from > the original. Else there's no way to correlate columns between each. > > you can try doing it directly, where the column correlation is by name > (something SQLAlchemy doesn't ever do): > > from sqlalchemy.sql import visitors > > def replace(x): > if x is t1: # replace the table > return t2 > elif t1.c.contains_column(x): # replace columns in the table > return t2.c[x.key] > > s = visitors.replacement_traverse(s, {}, replace) > > There also are compilation ways to do this, building a subclass of Alias > which compiles to the new name without the "AS", this would be a little hacky. > > I also might be looking to adjust my application to not require a pattern > like this. Here it would likely mean varying between "t1" and "t2" before > "s" is created. > > On Oct 12, 2011, at 8:32 AM, sector119 wrote: > > > > > > > > > Hello. > > > Is it possible to replace some table with defferent one? > > I want to get the same query, with the same columns, having, order_by, > > where clauses but with different table name. > > > s.replace_selectable(t1, t1_alias) - works > > s.replace_selectable(t1, t2) - doesn't > > > Thanks. > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] how to replace selectable but not with original table alias?
Hello. Is it possible to replace some table with defferent one? I want to get the same query, with the same columns, having, order_by, where clauses but with different table name. s.replace_selectable(t1, t1_alias) - works s.replace_selectable(t1, t2) - doesn't Thanks. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] why func.sum(some_table.c.bigintfield) returns Decimal ?
Hello. Why func.sum(some_table.c.bigintfield) returns Decimal ? documents_table.c.sum has BigInteger type (postgresql table field has bigint type) the same issue I got with postgresql date_part func, when I extract year or month I got Decimal result, not int >>> s = select([func.sum(documents_table.c.sum).label('payments_sum'), >>> func.sum(documents_table.c.payments).label('payments_count')]) >>> session.execute(s).fetchall() 2011-09-26 13:48:41,543 INFO sqlalchemy.engine.base.Engine SELECT sum(documents.sum) AS payments_sum, sum(documents.payments) AS payments_count FROM documents 2011-09-26 13:48:41,543 INFO sqlalchemy.engine.base.Engine {} [(Decimal('51788997139'), 8853396L)] >>> s = select([cast(func.sum(documents_table.c.sum), >>> BigInteger).label('payments_sum'), >>> func.sum(documents_table.c.payments).label('payments_count')]) >>> session.execute(s).fetchall() 2011-09-26 13:48:56,243 INFO sqlalchemy.engine.base.Engine SELECT CAST(sum(documents.sum) AS BIGINT) AS payments_sum, sum(documents.payments) AS payments_count FROM documents 2011-09-26 13:48:56,244 INFO sqlalchemy.engine.base.Engine {} [(51788997139L, 8853396L)] -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] how to make obj copy with all relations?
Hello. How to make a copy of object with all relations? But with new PK value and save? Thanks. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: something wrong with relationship caching at _trunk_
Thank you, Michael! On 22 Грд, 18:38, Michael Bayer wrote: > Thank you, please apply the change in r9327b3748997 to your > _params_from_query() function. > > On Dec 22, 2010, at 5:47 AM, sector119 wrote: > > > > > > > > > from beaker import cache > > > from sqlalchemy import * > > from sqlalchemy.orm import mapper, sessionmaker, scoped_session, > > relationship > > from sqlalchemy.types import * > > > # from examples/beaker_caching > > from eps.model import caching_query > > > ### INIT > > > cache_manager = cache.CacheManager() > > metadata = MetaData() > > engine = create_engine('postgresql+psycopg2:// > > LOGIN:passw...@127.0.0.1:5432/DBNAME', echo=False) > > Session = scoped_session(sessionmaker(autoflush=True, > > autocommit=False, > > > query_cls=caching_query.query_callable(cache_manager), bind=engine)) > > > cache_manager.regions['default'] = { > > 'type': 'memory', > > 'lock_dir': '/tmp', > > } > > > ### END INIT > > > ### TABLES > > > # groups > > > groups_table = Table('groups', metadata, > > Column('id', Integer, primary_key=True), > > Column('name', String(255), unique=True, nullable=False) > > ) > > > class Group(object): > > def __init__(self, name): > > self.name = name > > > mapper(Group, groups_table) > > > # users > > > users_table = Table('users', metadata, > > Column('id', Integer, primary_key=True), > > Column('username', String(255), unique=True, nullable=False), > > Column('first_name', Unicode(255), nullable=False), > > Column('last_name', Unicode(255), nullable=False), > > Column('middle_name', Unicode(255), nullable=False) > > ) > > > # users_groups > > > users_groups_table = Table('users_groups', metadata, > > Column('user_id', Integer, ForeignKey('users.id')), > > Column('group_id', Integer, ForeignKey('groups.id')) > > ) > > > class User(object): > > def __init__(self, username, first_name, last_name, middle_name): > > self.username = username > > self.first_name = first_name > > self.last_name = last_name > > self.middle_name = middle_name > > > mapper( > > User, > > users_table, > > properties={ > > 'groups': relationship(Group, lazy=True, > > secondary=users_groups_table, backref='users') > > } > > ) > > > cache_user_relationships = caching_query.RelationshipCache('default', > > 'by_id', User.groups) > > > ### END TABLES > > > ### HELPERS > > > def get_user(username): > > return Session.query(User).\ > > options(cache_user_relationships).\ > > options(caching_query.FromCache('default', > > 'by_username')).\ > > filter_by(username=username).one() > > > def print_groups(user): > > for g in user.groups: > > print g.name > > > ### END HELPERS > > > ### CREATE > > > metadata.create_all(engine) > > > ### END CREATE > > > ### POPULATE > > > u1 = User('sector119', u'A', u'B', u'C') > > u1.groups = [Group('G1')] > > u2 = User('sector120', u'D', u'E', u'F') > > u2.groups = [Group('G2')] > > Session.add_all([u1, u2]) > > > Session.commit() > > > ### END POPULATE > > > ### TEST ... > > > u = get_user('sector119') > > print '1. %s groups:' % u.username > > print_groups(u) > > print '2. %s groups:' % u.username > > print_groups(u) > > > u = get_user('sector120') > > print '1. %s groups:' % u.username > > print_groups(u) > > print '2. %s groups:' % u.username > > print_groups(u) > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: something wrong with relationship caching at _trunk_
Work correctly at changeset: 56917428859f Work wrong at changeset: 111017b8beaf changeset: 7043:111017b8beaf parent: 7040:7619cb5219ca parent: 7042:56917428859f user:zzzeek date:Fri Nov 26 16:28:41 2010 -0500 summary: - merge mapper simpler compile branch, [ticket:1966] changeset: 7042:56917428859f parent: 7041:6139773eaa41 parent: 6926:107643a1453c user:zzzeek date:Thu Nov 18 19:01:17 2010 -0500 summary: merge tip -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: something wrong with relationship caching at _trunk_
The same from dpaste.com: http://dpaste.com/289387/ It works with revision 6944: % pwd /home/eps/devel/src/sqlalchemy.6944 % python2.6 setup.py develop running develop ... Finished processing dependencies for SQLAlchemy==0.6.6dev % hg log|head -n1 changeset: 6944:b29164cca942 % python2.6 sacache.py 1. sector119 groups: G1 2. sector119 groups: G1 1. sector120 groups: G2 2. sector120 groups: G2 Now I would try to move from 6944 to 7195 (current) changeset by changeset and see where it would be broken. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: something wrong with relationship caching at _trunk_
% python2.6 sacache.py 1. sector119 groups: G1 2. sector119 groups: G1 1. sector120 groups: G1 2. sector120 groups: G1 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: something wrong with relationship caching at _trunk_
from beaker import cache from sqlalchemy import * from sqlalchemy.orm import mapper, sessionmaker, scoped_session, relationship from sqlalchemy.types import * # from examples/beaker_caching from eps.model import caching_query ### INIT cache_manager = cache.CacheManager() metadata = MetaData() engine = create_engine('postgresql+psycopg2:// LOGIN:passw...@127.0.0.1:5432/DBNAME', echo=False) Session = scoped_session(sessionmaker(autoflush=True, autocommit=False, query_cls=caching_query.query_callable(cache_manager), bind=engine)) cache_manager.regions['default'] = { 'type': 'memory', 'lock_dir': '/tmp', } ### END INIT ### TABLES # groups groups_table = Table('groups', metadata, Column('id', Integer, primary_key=True), Column('name', String(255), unique=True, nullable=False) ) class Group(object): def __init__(self, name): self.name = name mapper(Group, groups_table) # users users_table = Table('users', metadata, Column('id', Integer, primary_key=True), Column('username', String(255), unique=True, nullable=False), Column('first_name', Unicode(255), nullable=False), Column('last_name', Unicode(255), nullable=False), Column('middle_name', Unicode(255), nullable=False) ) # users_groups users_groups_table = Table('users_groups', metadata, Column('user_id', Integer, ForeignKey('users.id')), Column('group_id', Integer, ForeignKey('groups.id')) ) class User(object): def __init__(self, username, first_name, last_name, middle_name): self.username = username self.first_name = first_name self.last_name = last_name self.middle_name = middle_name mapper( User, users_table, properties={ 'groups': relationship(Group, lazy=True, secondary=users_groups_table, backref='users') } ) cache_user_relationships = caching_query.RelationshipCache('default', 'by_id', User.groups) ### END TABLES ### HELPERS def get_user(username): return Session.query(User).\ options(cache_user_relationships).\ options(caching_query.FromCache('default', 'by_username')).\ filter_by(username=username).one() def print_groups(user): for g in user.groups: print g.name ### END HELPERS ### CREATE metadata.create_all(engine) ### END CREATE ### POPULATE u1 = User('sector119', u'A', u'B', u'C') u1.groups = [Group('G1')] u2 = User('sector120', u'D', u'E', u'F') u2.groups = [Group('G2')] Session.add_all([u1, u2]) Session.commit() ### END POPULATE ### TEST ... u = get_user('sector119') print '1. %s groups:' % u.username print_groups(u) print '2. %s groups:' % u.username print_groups(u) u = get_user('sector120') print '1. %s groups:' % u.username print_groups(u) print '2. %s groups:' % u.username print_groups(u) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: something wrong with relationship caching at _trunk_
Yep, it's should be: "def print_groups(u)" Michael, do you need complete (with data) working (or working wrong:) sample? That code is working... But with existing data... On 21 Грд, 16:39, Michael Bayer wrote: > I couldn't begin to know what the issue is with code fragments like this. > Though your "print_groups()" function here is wrong: > > def print_groups(user): > for g in u.groups: > print g.name > > On Dec 21, 2010, at 9:15 AM, sector119 wrote: > > > > > > > > > Hello! > > > I have a problem with my relationship caching with 0.7b1 (current > > trunk) > > > When I perform query on `User` model with `username` param, than > > access some lazy and cached separaterly from main query relationship - > > `groups`. > > After that I exec the same query on `User` model with another > > `username` param and access `groups` relationship I got the same > > groups as with first query and no sql being executed to get those > > groups... > > > The same code works correctly with 0.6.5. > > > To reproduce: > > > % sudo invoke-rc.d memcached restart > > [sudo] password for sector119: > > Restarting memcached: memcached. > > % > > > # sacache.py > > > from sqlalchemy import create_engine > > > from eps.model import init_model > > from eps.model import meta > > from eps.model import caching_query as cache > > from eps.model import cache_user_relationships > > from eps.model import User > > from eps.model import SYSTEM_SCHEMA > > > def get_user(username): > > user = meta.Session.query(User).\ > > options(cache_user_relationships).\ > > options(cache.FromCache('default', > > 'by_username')).\ > > filter_by(username=username, > > disabled=False).first() > > > meta.Session.connection().execute('SET search_path TO {0}, > > {1}'.format(SYSTEM_SCHEMA, > > > user.locality.schema)) > > > return user > > > def print_groups(user): > > for g in u.groups: > > print g.name > > > engine = create_engine('postgresql+psycopg2:// > > eps:mypassw...@127.0.0.1:5432/eps', echo=True) > > init_model(engine) > > > u = get_user('sector119') > > print '1. %s groups:' % u.username > > print_groups(u) > > print '2. %s groups:' % u.username > > print_groups(u) > > > u = get_user('privat') > > print '1. %s groups:' % u.username > > print_groups(u) > > print '2. %s groups:' % u.username > > print_groups(u) > > > Output: > > > 1. sector119 groups: > > wheel > > 2. sector119 groups: > > wheel > > > 1. privat groups: > > wheel > > 2. privat groups: > > wheel > > > Echoing SQL: > > > 2010-12-21 15:50:38,273 INFO sqlalchemy.engine.base.Engine select > > version() > > 2010-12-21 15:50:38,274 INFO sqlalchemy.engine.base.Engine {} > > 2010-12-21 15:50:38,275 INFO sqlalchemy.engine.base.Engine select > > current_schema() > > 2010-12-21 15:50:38,275 INFO sqlalchemy.engine.base.Engine {} > > 2010-12-21 15:50:38,276 INFO sqlalchemy.engine.base.Engine BEGIN > > (implicit) > > > # FIRST user > > > 2010-12-21 15:50:38,277 INFO sqlalchemy.engine.base.Engine SELECT > > system.users.id AS system_users_id, system. users.username AS > > system_users_username, system.users.password AS system_users_password, > > system.users. first_name AS system_users_first_name, > > system.users.last_name AS system_users_last_name, system.users. > > middle_name AS system_users_middle_name, system.users.locality_id AS > > system_users_locality_id, system.users. office_id AS > > system_users_office_id, system.users.email AS system_users_email, > > system.users.create_date AS system_users_create_date, > > system.users.last_login AS system_users_last_login, > > system.users.expire AS system_users_expire, > > system.users.disabled AS system_users_disabled > > FROM system.users > > WHERE system.users.username = %(username_1)s AND system.users.disabled > > = %(disabled_1)s > > LIMIT %(param_1)s OFFSET %(param_2)s > > 2010-12-21 15:50:38,277 INFO sqlalchemy.engine.base.Engine {'param_1': > > 1, 'disabled_1': False, 'username_1': 'sector119', 'param_2': 0} > > > 2010-12-21 15:50:38,283 INFO sqlalchemy.engine.base.Engine SELECT > > system.localities.id
[sqlalchemy] something wrong with relationship caching at _trunk_
Hello! I have a problem with my relationship caching with 0.7b1 (current trunk) When I perform query on `User` model with `username` param, than access some lazy and cached separaterly from main query relationship - `groups`. After that I exec the same query on `User` model with another `username` param and access `groups` relationship I got the same groups as with first query and no sql being executed to get those groups... The same code works correctly with 0.6.5. To reproduce: % sudo invoke-rc.d memcached restart [sudo] password for sector119: Restarting memcached: memcached. % # sacache.py from sqlalchemy import create_engine from eps.model import init_model from eps.model import meta from eps.model import caching_query as cache from eps.model import cache_user_relationships from eps.model import User from eps.model import SYSTEM_SCHEMA def get_user(username): user = meta.Session.query(User).\ options(cache_user_relationships).\ options(cache.FromCache('default', 'by_username')).\ filter_by(username=username, disabled=False).first() meta.Session.connection().execute('SET search_path TO {0}, {1}'.format(SYSTEM_SCHEMA, user.locality.schema)) return user def print_groups(user): for g in u.groups: print g.name engine = create_engine('postgresql+psycopg2:// eps:mypassw...@127.0.0.1:5432/eps', echo=True) init_model(engine) u = get_user('sector119') print '1. %s groups:' % u.username print_groups(u) print '2. %s groups:' % u.username print_groups(u) u = get_user('privat') print '1. %s groups:' % u.username print_groups(u) print '2. %s groups:' % u.username print_groups(u) Output: 1. sector119 groups: wheel 2. sector119 groups: wheel 1. privat groups: wheel 2. privat groups: wheel Echoing SQL: 2010-12-21 15:50:38,273 INFO sqlalchemy.engine.base.Engine select version() 2010-12-21 15:50:38,274 INFO sqlalchemy.engine.base.Engine {} 2010-12-21 15:50:38,275 INFO sqlalchemy.engine.base.Engine select current_schema() 2010-12-21 15:50:38,275 INFO sqlalchemy.engine.base.Engine {} 2010-12-21 15:50:38,276 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) # FIRST user 2010-12-21 15:50:38,277 INFO sqlalchemy.engine.base.Engine SELECT system.users.id AS system_users_id, system. users.username AS system_users_username, system.users.password AS system_users_password, system.users.first_name AS system_users_first_name, system.users.last_name AS system_users_last_name, system.users. middle_name AS system_users_middle_name, system.users.locality_id AS system_users_locality_id, system.users. office_id AS system_users_office_id, system.users.email AS system_users_email, system.users.create_date ASsystem_users_create_date, system.users.last_login AS system_users_last_login, system.users.expire AS system_users_expire, system.users.disabled AS system_users_disabled FROM system.users WHERE system.users.username = %(username_1)s AND system.users.disabled = %(disabled_1)s LIMIT %(param_1)s OFFSET %(param_2)s 2010-12-21 15:50:38,277 INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'disabled_1': False, 'username_1': 'sector119', 'param_2': 0} 2010-12-21 15:50:38,283 INFO sqlalchemy.engine.base.Engine SELECT system.localities.id AS system_localities_id, system.localities.name AS system_localities_name, system.localities.type AS system_localities_type, system.localities.schema AS system_localities_schema FROM system.localities WHERE system.localities.id = %(param_1)s 2010-12-21 15:50:38,283 INFO sqlalchemy.engine.base.Engine {'param_1': 1} 2010-12-21 15:50:38,285 INFO sqlalchemy.engine.base.Engine SET search_path TO system,ternopil 2010-12-21 15:50:38,285 INFO sqlalchemy.engine.base.Engine {} # FIRST user GROUPS 2010-12-21 15:50:38,286 INFO sqlalchemy.engine.base.Engine SELECT system.groups.id AS system_groups_id, system.groups.name AS system_groups_name FROM system.groups, system.users_groups WHERE %(param_1)s = system.users_groups.user_id AND system.groups.id = system.users_groups.group_id 2010-12-21 15:50:38,286 INFO sqlalchemy.engine.base.Engine {'param_1': 2} # NEXT user 2010-12-21 15:50:38,289 INFO sqlalchemy.engine.base.Engine SELECT system.users.id AS system_users_id, system. users.username AS system_users_username, system.users.password AS system_users_password, system.users.first_name AS system_users_first_name, system.users.last_name AS system_users_last_name, system.users. middle_name AS system_users_middle_name, system.users.locality_id AS system_users_locality_id, system.users. office_id AS system_users_office_id, system.users.email AS system_users_email, system.users.create_date ASsystem_users_create_date, system.users.last_login AS system_users_last
[sqlalchemy] Re: How to invalidate RelationshipCache?
And one more question about caching, how can I invalidate all cached queries for User model? For one user I make: q = meta.Session.query(User).\ options(cache_user_relationships).\ options(cache.FromCache('default', 'by_username')).\ filter_by(username=SOME_USERNAME, disabled=False) q.invalidate() But how to reset them for all users? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: How to invalidate RelationshipCache?
I was wrong. This works, but it's not handy to recreate query manually that was generated by by SA (it's lazy relation) :( Maybe it's possible to do that another way? q = meta.Session.query(Office).\ options(cache.FromCache('default', 'by_id')).\ filter_by(id=197) q.invalidate() -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: How to invalidate RelationshipCache?
Is it have sense to have something like RelationFromCache(region, namespace, attribute) ? To use it like: meta.Session.query(User).\ options(cache.RelationFromCache('default', 'by_id', User.office)).\ filter_by(username=username, disabled=False).\ invalidate() To invalidate certain relation. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: How to invalidate RelationshipCache?
> cache_user_relationships = cache.RelationshipCache('default', 'by_id', > User.locality).and_( > cache.RelationshipCache('default', 'by_id', > User.office)).and_( > cache.RelationshipCache('default', 'by_id', > User.groups)).and_( > cache.RelationshipCache('default', 'by_id', > User.roles)).and_( > cache.RelationshipCache('default', 'by_id', > User.localities)) Is it ok that I set the same `namespace` for RelationshipCache? I get that I can invalidate relationship cache with: q = meta.Session.query(User).\ options(cache.FromCache('default', 'by_id')).\ filter_by(username=username, disabled=False) q.invalidate() But it would invalidate all relationship caches? If I only need to invalidate `office` relationship cache I have to set different cache namesapces for all cached relations? Thank you! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] How to invalidate RelationshipCache?
Hi All! I have users_table mapped to User model. ### mapper( User, users_table, properties={ 'locality': relation(Locality, uselist=False), 'office': relation(Office, uselist=False, primaryjoin=users_table.c.office_id==offices_table.c.id, foreign_keys=[users_table.c.office_id]), 'groups': relation(Group, lazy=True, secondary=users_groups_table, backref='users'), 'roles': relation(Role, lazy=True, secondary=users_roles_table, backref='users'), 'localities': relation(Locality, lazy=True, secondary=users_localities_table, backref='users'), } ) # Caching options. A set of RelationshipCache options # which can be applied to Query(), causing the "lazy load" # of these attributes to be loaded from cache. cache_user_relationships = cache.RelationshipCache('default', 'by_id', User.locality).and_( cache.RelationshipCache('default', 'by_id', User.office)).and_( cache.RelationshipCache('default', 'by_id', User.groups)).and_( cache.RelationshipCache('default', 'by_id', User.roles)).and_( cache.RelationshipCache('default', 'by_id', User.localities)) ### When I try to invalidate query I make: q = meta.Session.query(User).\ options(cache_user_relationships).\ options(cache.FromCache('default', 'by_username')).\ filter_by(username=SOME_USERNAME, disabled=False) q.invalidate() But this invalidate only User model data, not data cached from relations that use RelationshipCache :( How can I invalidate relationship cache? If it's possible I want to invalidate only _certain_ relation cache, for example `office`. Thanks! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] need help with array of tuples and composite custom type in postgresql
Hi All. I have a problem with that query, how to rewrite it using sqlalchemy? What to do with tuples - "array_agg(row(meter_id, organization_reading, reading)::meterreading)" in psql I see it like {"(499680,137,141)","(500765,258,267)"} ? And composite custom type - "meterreading" ? How to create that type? And how to access array of tuples is it supported with sqlalchemy multi-dimesional ARRAY type? CREATE TYPE meterreading AS ( meter_id VARCHAR(255), organization_reading INTEGER, reading INTEGER); SELECT t.*, m.readings FROM transactions t LEFT OUTER JOIN (SELECT office_id, serial, commit_date, service_id, array_agg(row(meter_id, organization_reading, reading)::meterreading) AS readings FROM meter_readings GROUP BY office_id, serial, commit_date, service_id) m USING (office_id, serial, commit_date, service_id) WHERE commit_date = '2010-01-11'; Thanks a lot! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Something wrong with pool. Got "Connection is already closed None None" with copy_from and big file.
On Nov 4, 7:13 pm, "Michael Bayer" wrote: > copy_from() probably creates some state that is not compatible with the > connection being used afterwards for subsequent operations, or > alternatively copy_from() is not compatible with some previous state. > The pool does nothing special to the connections which it stores except > calling rollback() when they are returned. > > If you can try to isolate the issue to an exact sequence of events (i.e., > don't use a Session or ORM - just use an engine and connect()) that would > reveal more about what's going on. Now I try copy_from without Session or ORM, use engine only and everything is ok :) What does it mean? :) engine = create_engine(conf['sqlalchemy.url']) connection = engine.raw_connection() connection.cursor().cursor.copy_from(f, i.table, sep=chr(int(conf ['import.separator'])), columns=map(str, i.fields.split(','))) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=.
[sqlalchemy] Something wrong with pool. Got "Connection is already closed None None" with copy_from and big file.
I use sqlalchemy 0.6 and psycopg2 2.0.13. When I try to copy from some little file it's ok, when from some bigger one I got error and log posted below. When I use pgbouncer and pass poolclass=pool.NullPool to create_engine everything wor perfect with large file too. My query is: session.connection().connection.cursor().cursor.copy_from(f, table, sep=chr(int(conf['import.separator'])), columns=map(str, fields.split (','))) Log: 2009-11-04 18:43:30 EET LOG: statement: COPY people (id,street_id,building,corpus,appartment,block,last_name,hd,residents,space,rooms,privilege,comment) FROM stdin WITH DELIMITER AS E' ' 2009-11-04 18:44:08 EET LOG: could not receive data from client: Connection reset by peer 2009-11-04 18:44:08 EET CONTEXT: COPY people, line 6146 2009-11-04 18:44:08 EET STATEMENT: COPY people (id,street_id,building,corpus,appartment,block,last_name,hd,residents,space,rooms,privilege,comment) FROM stdin WITH DELIMITER AS E'' 2009-11-04 18:44:08 EET LOG: incomplete message from client 2009-11-04 18:44:08 EET CONTEXT: COPY people, line 6146 2009-11-04 18:44:08 EET STATEMENT: COPY people (id,street_id,building,corpus,appartment,block,last_name,hd,residents,space,rooms,privilege,comment) FROM stdin WITH DELIMITER AS E'' 2009-11-04 18:44:08 EET ERROR: unexpected EOF on client connection 2009-11-04 18:44:08 EET CONTEXT: COPY people, line 6146 2009-11-04 18:44:08 EET STATEMENT: COPY people (id,street_id,building,corpus,appartment,block,last_name,hd,residents,space,rooms,privilege,comment) FROM stdin WITH DELIMITER AS E'' 2009-11-04 18:44:08 EET LOG: could not send data to client: Broken pipe 2009-11-04 18:44:08 EET LOG: could not receive data from client: Connection reset by peer 2009-11-04 18:44:08 EET LOG: unexpected EOF on client connection 2009-11-04 18:44:08 EET LOG: disconnection: session time: 0:00:44.763 user=eps database=eps host=localhost port=44257 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ForeignKey and ForeignKeyConstraint copy() method make FK copy without arguments
done. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: not expected generated update query values
Oh, I forgot about that, it wasn't that behaviour that I realy axpect.. I modify sql/compiller.py a bit to show what I mean. If I specify some bindparam'eters at value(...) I want _only_ that columns to be at update SET or insert VALUES.. --- compiler.py.orig2009-11-02 18:00:17.548954070 +0200 +++ compiler.py 2009-11-02 18:01:11.682036402 +0200 @@ -793,9 +793,9 @@ # if we have statement parameters - set defaults in the # compiled params -if self.column_keys is None: -parameters = {} -else: +parameters = {} + +if self.column_keys is not None and not stmt.parameters: parameters = dict((sql._column_as_key(key), required) for key in self.column_keys if key not in bind_names) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ForeignKey and ForeignKeyConstraint copy() method make FK copy without arguments
--- schema.py 2009-11-04 13:27:37.124466356 +0200 +++ schema.py 2009-11-04 13:27:21.486350738 +0200 @@ -909,7 +909,17 @@ def copy(self, schema=None): """Produce a copy of this ForeignKey object.""" -return ForeignKey(self._get_colspec(schema=schema)) +return ForeignKey( +self._get_colspec(schema=schema), +constraint=self.constraint, +use_alter=self.use_alter, +name=self.name, +onupdate=self.onupdate, +ondelete=self.ondelete, +deferrable=self.deferrable, +initially=self.initially, +link_to_name=self.link_to_name +) def _get_colspec(self, schema=None): if schema: @@ -1494,7 +1504,10 @@ name=self.name, onupdate=self.onupdate, ondelete=self.ondelete, +deferrable=self.deferrable, +initially=self.initially, +link_to_name=self.link_to_name ) class PrimaryKeyConstraint(ColumnCollectionConstraint): --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] ForeignKey and ForeignKeyConstraint copy() method make FK copy without arguments
Hi All ForeignKey and ForeignKeyConstraint copy() method make FK copy without arguments, for example deferrable, ... class ForeignKey: def copy(self, schema=None): """Produce a copy of this ForeignKey object.""" return ForeignKey(self._get_colspec(schema=schema)) class ForeignKeyConstraint: def copy(self, **kw): return ForeignKeyConstraint( [x.parent.name for x in self._elements.values()], [x._get_colspec(**kw) for x in self._elements.values()], name=self.name, onupdate=self.onupdate, ondelete=self.ondelete, use_alter=self.use_alter ) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] UnicodeDecodeError with pg8000 postgresql.conf client_encoding=utf8, engine encoding=utf-8
Hi All. I've got UnicodeDecodeError: 'ascii' codec can't decode byte 0xa1 in position 3: ordinal not in range(128) Why it can happen? I use client_encoding=utf8 at postgresql.conf and encoding=utf-8 at create_engine. File '', line 2 in submit File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ Pylons-0.9.7-py2.6.egg/pylons/decorators/__init__.py', line 207 in wrapper return func(self, *args, **kwargs) File '/home/sector119/devel/eps_env/src/eps/eps/controllers/login.py', line 39 in submit user = meta.Session.query(User).options(eagerload ('locality')).filter_by(username=username).first() File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ SQLAlchemy-0.6beta1dev_r0-py2.6.egg/sqlalchemy/orm/query.py', line 1250 in first ret = list(self[0:1]) File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ SQLAlchemy-0.6beta1dev_r0-py2.6.egg/sqlalchemy/orm/query.py', line 1171 in __getitem__ return list(res) File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ SQLAlchemy-0.6beta1dev_r0-py2.6.egg/sqlalchemy/orm/query.py', line 1311 in __iter__ return self._execute_and_instances(context) File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ SQLAlchemy-0.6beta1dev_r0-py2.6.egg/sqlalchemy/orm/query.py', line 1314 in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none()) File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ SQLAlchemy-0.6beta1dev_r0-py2.6.egg/sqlalchemy/orm/session.py', line 739 in execute clause, params or {}) File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ SQLAlchemy-0.6beta1dev_r0-py2.6.egg/sqlalchemy/engine/base.py', line 975 in execute return Connection.executors[c](self, object, multiparams, params) File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ SQLAlchemy-0.6beta1dev_r0-py2.6.egg/sqlalchemy/engine/base.py', line 1037 in _execute_clauseelement return self.__execute_context(context) File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ SQLAlchemy-0.6beta1dev_r0-py2.6.egg/sqlalchemy/engine/base.py', line 1060 in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ SQLAlchemy-0.6beta1dev_r0-py2.6.egg/sqlalchemy/engine/base.py', line 1120 in _cursor_execute self.dialect.do_execute(cursor, statement, parameters, context=context) File '/home/sector119/devel/eps_env/lib/python2.6/site-packages/ SQLAlchemy-0.6beta1dev_r0-py2.6.egg/sqlalchemy/engine/default.py', line 181 in do_execute cursor.execute(statement, parameters) File 'build/bdist.linux-i686/egg/pg8000/dbapi.py', line 243 in _fn File 'build/bdist.linux-i686/egg/pg8000/dbapi.py', line 312 in execute File 'build/bdist.linux-i686/egg/pg8000/dbapi.py', line 317 in _execute File 'build/bdist.linux-i686/egg/pg8000/interface.py', line 303 in execute File 'build/bdist.linux-i686/egg/pg8000/interface.py', line 108 in __init__ File 'build/bdist.linux-i686/egg/pg8000/protocol.py', line 918 in _fn File 'build/bdist.linux-i686/egg/pg8000/protocol.py', line 1069 in parse File 'build/bdist.linux-i686/egg/pg8000/protocol.py', line 975 in _send File 'build/bdist.linux-i686/egg/pg8000/protocol.py', line 121 in serialize UnicodeDecodeError: 'ascii' codec can't decode byte 0xa1 in position 3: ordinal not in range(128) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: can't import ARRAY from dialects.postgresql
The latest trunk. >>> from sqlalchemy.dialects.postgresql import ARRAY Traceback (most recent call last): File "", line 1, in ImportError: cannot import name ARRAY >>> from sqlalchemy.dialects.postgresql.base import ARRAY >>> from sqlalchemy.dialects import postgresql >>> 'ARRAY' in dir(postgresql) False We have no ARRAY at sqlalchemy/dialects/postgresql/__init__.py __all__ list... % grep ARRAY dialects/postgresql/__init__.py % The same for orm, sql, can't import tham from sqlalchemy.. Should make from sqlalchemy.orm import mapper, relation, ... not just from sqlalchemy import orm :/ --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: can't import ARRAY from dialects.postgresql
It's on trunk. Elso can't import orm from sqlalchemy :/ On Oct 23, 6:00 pm, sector119 wrote: > Is there something wrong with ARRAY type in SA 0.6? no dialects/ > /__init__.py has ARRAY at __all__ !? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] can't import ARRAY from dialects.postgresql
Is there something wrong with ARRAY type in SA 0.6? no dialects/ /__init__.py has ARRAY at __all__ !? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] own compiler visit_update method
Hi! Is it possible to easily add tables to FROM clause, now I use following code to append additional table names but it look like a hack.. at PGCompiler.visit_update() I see self.stack.append({'from': set ([update_stmt.table])}) where and how it's used, may be using this I can append some elements to the FROM ? def visit_update(self, update_stmt): text = super(PGCompiler, self).visit_update(update_stmt) if 'postgresql_from' in update_stmt.kwargs: text = self._append_from(text, update_stmt) return text def _append_from(self, text, stmt): return text.replace(' WHERE', ' FROM ' + string.join([table.name for table in stmt.kwargs['postgresql_from']], ', ') + ' WHERE') --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: not expected generated update query values
Thanks a lot, Michael! On Oct 23, 4:09 am, Michael Bayer wrote: > On Oct 22, 2009, at 3:26 PM, sector119 wrote: > > > > > Something strange, Michael.. All bindparams are different. Compliller > > should not add to SET all params if values() has bindparam args, no? > > where() have: > > bindparam('commit_date'), bindparam('serial'), bindparam > > ('office_id') > > values() have: > > bindparam('rollback_date'), bindparam('rollback_time'), bindparam > > ('rollback_user_id') > > OK i have this fully repaired in trunk r6428. if you use a bindparam > () with a column name, it will be honored as is and won't be > implicitly added to the SET or VALUES clause of an update or insert. > > > > > All bindparams are different. > > > params={'commit_date':'2009-10-22', 'serial':1, 'office_id':1, > > 'rollback_date':'2009-10-22', 'rollback_time':'11:12:15', > > 'rollback_user_id':1, 'foobar':1, 'sum':111}) > > > UPDATE transactions SET serial=%(serial)s, office_id=%(office_id)s, > > sum=%(sum) > > s, commit_date=%(commit_date)s, rollback_date=%(rollback_date)s, > > rollback_time=%(rollback_time)s, rollback_user_id=%(rollback_user_id) > > s > > WHERE transactions.commit_date = %(commit_date)s AND > > transactions.serial = %(serial)s AND transactions.office_id = % > > (office_id)s > > > On 22 окт, 19:45, "Michael Bayer" wrote: > >> sector119 wrote: > > >>>> though likely cleaner to pass the exact set of parameters desired. > > >>> How to pass that params if I use bindparam at where() and values(), > >>> but I don't want to update colums that are at where() clause, only > >>> at > >>> values() ? > > >> if you are using bindparam() objects, you'd given them all distinct > >> names. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: not expected generated update query values
Something strange, Michael.. All bindparams are different. Compliller should not add to SET all params if values() has bindparam args, no? where() have: bindparam('commit_date'), bindparam('serial'), bindparam ('office_id') values() have: bindparam('rollback_date'), bindparam('rollback_time'), bindparam ('rollback_user_id') All bindparams are different. params={'commit_date':'2009-10-22', 'serial':1, 'office_id':1, 'rollback_date':'2009-10-22', 'rollback_time':'11:12:15', 'rollback_user_id':1, 'foobar':1, 'sum':111}) UPDATE transactions SET serial=%(serial)s, office_id=%(office_id)s, sum=%(sum) s, commit_date=%(commit_date)s, rollback_date=%(rollback_date)s, rollback_time=%(rollback_time)s, rollback_user_id=%(rollback_user_id) s WHERE transactions.commit_date = %(commit_date)s AND transactions.serial = %(serial)s AND transactions.office_id = % (office_id)s On 22 окт, 19:45, "Michael Bayer" wrote: > sector119 wrote: > > >> though likely cleaner to pass the exact set of parameters desired. > > > How to pass that params if I use bindparam at where() and values(), > > but I don't want to update colums that are at where() clause, only at > > values() ? > > if you are using bindparam() objects, you'd given them all distinct names. > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: not expected generated update query values
> though likely cleaner to pass the exact set of parameters desired. How to pass that params if I use bindparam at where() and values(), but I don't want to update colums that are at where() clause, only at values() ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] not expected generated update query values
Hi All! Why I get at SET part not only items from values(...), but all from params passed to session.execute? >>> transactions_update = >>> model.transactions_table.update().where(and_(model.transactions_table.c.commit_date==bindparam('commit_date'), >>> model.transactions_table.c.serial==bindparam('serial'), >>> model.transactions_table.c.office_id==bindparam('office_id'))).values(rollback_date=bindparam('rollback_date'), >>> rollback_time=bindparam('rollback_time'), >>> rollback_user_id=bindparam('rollback_user_id')) >>> meta.Session.execute(transactions_update, >>> params={'commit_date':'2009-10-22', 'serial':1, 'office_id':1, >>> 'rollback_date':'2009-10-22', 'rollback_time':'11:12:15', >>> 'rollback_user_id':1, 'foobar':1, 'sum':111}) 17:31:50,761 INFO [sqlalchemy.engine.base.Engine.0x...f34c] UPDATE transactions SET serial=%(serial)s, office_id=%(office_id)s, sum=%(sum) s, commit_date=%(commit_date)s, rollback_date=%(rollback_date)s, rollback_time=%(rollback_time)s, rollback_user_id=%(rollback_user_id)s WHERE transactions.commit_date = %(commit_date)s AND transactions.serial = %(serial)s AND transactions.office_id = % (office_id)s 17:31:50,761 INFO [sqlalchemy.engine.base.Engine.0x...f34c] {'rollback_date': '2009-10-22', 'sum': 111, 'commit_date': '2009-10-22', 'office_id': 1, 'rollback_time': '11:12:15', 'rollback_user_id': 1, 'serial': 1} --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] unbound method execute() must be called with Session instance as first argument (got Select instance instead)
Hi all! Why I get "unbound method execute() must be called with Session instance as first argument (got Select instance instead)" with following code? from twisted.internet import reactor, task, threads from twisted.application import service from twisted.python import log from sqlalchemy import orm, create_engine from sqlalchemy.sql import select from eps.model import offices_table url = 'postgres://test:t...@127.0.0.1:5432/eps' def create_session(): return orm.sessionmaker(bind=create_engine(url), expire_on_commit=False) def require_session(f): def wrapper(*args, **kwargs): s = create_session() try: return f(session=s, *args, **kwargs) except Exception, e: log.err(e) s.rollback() raise finally: s.close() return wrapper @require_session def _getTimers(session=None): return session.execute(select ([offices_table.c.dayopen_time]).distinct(). order_by (offices_table.c.dayopen_time)).fetchall() def getTimers(): return threads.deferToThread(_getTimers) log.msg('Timers: %r' % getTimers()) application = service.Application('Timmer') --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] eagerload and count() doesn't use join
Hello! Why second query show different from 1,3 query result? Why it doesn't provide join? >>> meta.Session.query(model.Report).join((model.User, >>> model.Report.reporter_id==model.User.id)).filter(model.User.office_id==62).count() 18:08:29,568 INFO [sqlalchemy.engine.base.Engine.0x...888c] SELECT count(1) AS count_1 FROM system.reports JOIN system.users ON system.reports.reporter_id = system.users.id WHERE system.users.office_id = %(office_id_1)s 18:08:29,569 INFO [sqlalchemy.engine.base.Engine.0x...888c] {'office_id_1': 62} 14L >>> meta.Session.query(model.Report).options(eagerload('reporter')).filter(model.User.office_id==62).count() 18:09:08,488 INFO [sqlalchemy.engine.base.Engine.0x...888c] SELECT count(1) AS count_1 FROM system.users, system.reports WHERE system.users.office_id = %(office_id_1)s 18:09:08,489 INFO [sqlalchemy.engine.base.Engine.0x...888c] {'office_id_1': 62} 42L >>> len(meta.Session.query(model.Report).options(eagerload('reporter')).filter(model.User.office_id==62).all()) 18:09:22,053 INFO [sqlalchemy.engine.base.Engine.0x...888c] SELECT system.reports.id AS system_reports_id, system.reports.reporter_id AS system_reports_reporter_id, system.reports.owner_id AS system_reports_owner_id, system.reports.type AS system_reports_type, system.reports.version AS system_reports_version, system.reports.component AS system_reports_component, system.reports.priority AS system_reports_priority, system.reports.status AS system_reports_status, system.reports.summary AS system_reports_summary, system.reports.description AS system_reports_description, system.reports.resolution AS system_reports_resolution, system.reports.commit_date AS system_reports_commit_date, system.reports.commit_time AS system_reports_commit_time, users_1.id AS users_1_id, users_1.username AS users_1_username, users_1.password AS users_1_password, users_1.first_name AS users_1_first_name, users_1.last_name AS users_1_last_name, users_1.middle_name AS users_1_middle_name, users_1.locality_id AS users_1_locality_id, users_1.office_id AS users_1_office_id, users_1.email AS users_1_email, users_1.create_date AS users_1_create_date, users_1.last_login AS users_1_last_login, users_1.expire AS users_1_expire FROM system.users, system.reports LEFT OUTER JOIN system.users AS users_1 ON users_1.id = system.reports.reporter_id WHERE system.users.office_id = %(office_id_1)s 18:09:22,054 INFO [sqlalchemy.engine.base.Engine.0x...888c] {'office_id_1': 62} 14 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] How to set default join onclause if table has more than one FK to another table?
Hi All! How to set default join condition if table has more than one FK to another table? It may be Transaction.user_id==User.id or Transaction.rollback_user_id==User.id, I want first one to be default, is it possible to do? transactions_table = sa.Table('transactions', meta.metadata, sa.Column('id', sa.types.Integer, primary_key=True), sa.Column('serial', sa.types.Integer, index=True, nullable=False), sa.Column('person_id', sa.types.Integer, sa.ForeignKey ('people.id'), index=True, nullable=False), sa.Column('user_id', sa.types.Integer, sa.ForeignKey(SYSTEM_SCHEMA +'.users.id'), index=True, nullable=False), sa.Column('service_id', sa.types.Integer, sa.ForeignKey ('services.id'), index=True, nullable=False), sa.Column('sum', sa.types.Integer, nullable=False), sa.Column('commit_date', sa.types.Date, index=True, nullable=False), sa.Column('commit_time', sa.types.Time, index=True, nullable=False), sa.Column('rollback_date', sa.types.Date, index=True, nullable=True), sa.Column('rollback_time', sa.types.Time, index=True, nullable=True), sa.Column('rollback_user_id', sa.types.Integer, sa.ForeignKey (SYSTEM_SCHEMA+'.users.id'), index=True, nullable=True) ) Thanks! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: miss for postgres_from in update() :(
I create a small (4 lines) patch for databases/postgres.py def _append_from(self, text, stmt): return text.replace(' WHERE', ' FROM ' + string.join([table.name for table in stmt.kwargs['postgres_from']], ', ') + ' WHERE') def visit_update(self, update_stmt): text = super(PGCompiler, self).visit_update(update_stmt) if 'postgres_returning' in update_stmt.kwargs: text = self._append_returning(text, update_stmt) if 'postgres_from' in update_stmt.kwargs: text = self._append_from(text, update_stmt) return text But I think that text.replace(...) is ugly, may be you know how to insert FROM clause before WHERE without replacements? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] miss for postgres_from in update() :(
Hi all! u = transactions_table.update().\ where(and_(transactions_table.c.commit_date==current_date, \ transactions_table.c.serial==serial, \ transactions_table.c.user_id==users_table.c.id, \ users_table.c.office_id==id)).\ values(rollback_date=current_date, rollback_time=current_time) When I perform this query I get (ProgrammingError) missing FROM-clause entry for table "users". I use PostgreSQL and I can't find any postgres_from keyword argument in update() is it possible to add it, or where I have to look to try to implement it, or how to perform this query with sqlalchemy? Thanks a lot! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---