Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Brian Cherinka]

Yeah, that might ultimately be the best way to go if things get too 
complicated.   I think people might not want to re-run several lines of 
code to change some parameters but that could be a thing I make them just 
live with.

On Thursday, March 3, 2016 at 3:06:57 AM UTC-5, Ladislav Lenart wrote:
>
> Hello. 
>
> I think it would be (much) easier to simply rebuild the query from scratch 
> before each run. IMHO the time to build the query is not that big a factor 
> to 
> justify the added source code complexity. 
>
> HTH, 
>
> Ladislav Lenart 
>
>
> On 3.3.2016 05:47, Brian Cherinka wrote: 
> > 
> > 
> > well you need a list of names so from a mapped class you can get: 
> > 
> > for name in inspect(MyClass).column_attrs.keys(): 
> > if name in : 
> > q = q.filter_by(name = bindparam(name)) 
> > 
> > though I'd think if you're dynamically building the query you'd have 
> the 
> > values already, not sure how it's working out that you need 
> bindparam() 
> > at that stage... 
> > 
> >   
> > Ok.  I'll try this out. This looks like it could work.  I think I need 
> it for 
> > the cases where a user specifies a query with condition e.g. X < 10, 
> runs it, 
> > gets results.  Then they want to change the condition to X < 5 and rerun 
> the 
> > query.  As far as I know, if condition 2 gets added into the filter, you 
> would 
> > have both X < 10 and X < 5 in your filter expression.  Rather than a 
> single 
> > updated X < 5. 
> > 
> > What would be even more awesome is if there was a way to also update the 
> > operator in place as well.  So changing X < 10 to X > 10.   
> > 
> > 
> > 
>
>

-- 
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] Re: Assertion error on deleting one end of many-to-many relation

[Mike Bayer]

On 03/03/2016 07:36 AM, Lele Gaifax wrote:

Simon King  writes:


In general I think it is not recommended to use "secondary" with a table
that you have also mapped a class to. (eg. see the warning at the bottom of
http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#association-object
).


Thank you for the link.


You *might* be able to fix this by setting the cascade behaviour on
Person.preferred_activities to "all, delete-orphan":


Will try that approach. My current workaround is to explicitly delete the
intermediary items before deleting the person instance.


this is likely the solution and we probably need to start writing FAQ 
entries for what this error message means, it comes up in context of 
association objects pretty often.





ciao, lele.



--
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] I wish to know how I use table ALIAS in alchemy core

[Simon King]

On Thu, Mar 3, 2016 at 12:01 PM, Krishnakant  wrote:

>
>
> On Thursday 25 February 2016 03:50 PM, Simon King wrote:
>
> On Thu, Feb 25, 2016 at 9:43 AM, Krishnakant 
> wrote:
>
>> Hello,
>> I have a query where there are 2 alias for a single table.
>> This is because the table contains a self referencing foreign key.
>> the table is (groupcode integer primary key, groupname text, subgroupof
>> integer foreign key references groupcode).
>> Now let's say I wish to have a 2 column query with groups and their
>> respective subgroups, I need to join the table to itself making 2 aliases.
>> I know the raw query but need to do it through sqlalchemy core.
>> I don't use ORM for my project.and need this in the expression language.
>>
>>
> Something like this perhaps:
>
> import sqlalchemy as sa
> md = sa.MetaData()
> t = sa.Table(
> 't', md,
> sa.Column('groupcode', sa.Integer, primary_key=True),
> sa.Column('groupname', sa.Text()),
> sa.Column('subgroupof', sa.ForeignKey('t.groupcode')),
> )
>
> subgroup = t.alias('subgroup')
> j = t.join(subgroup, subgroup.c.subgroupof == t.c.groupcode)
> print sa.select([t.c.groupcode, subgroup.c.groupcode]).select_from(j)
>
>
> Output:
>
> SELECT t.groupcode, subgroup.groupcode
> FROM t JOIN t AS subgroup ON subgroup.subgroupof = t.groupcode
>
>
> Hope that helps,
>
> Thanks a lot for the help.
> I have one query.
> do I not need to import alias?  some thing like,
> from sqlalchemy import alias
> I tryed this and I get import error for the above mentioned line.
> and the query you provided did not work without alias.
>

In the example above, I was using the alias *method* of the Table object,
so it was not necessary to import anything. There is also a standalone
function, sqlalchemy.alias, which does the same thing. Given a table "t",
these are equivalent:

  subgroup = t.alias('subgroup')
  subgroup = sqlalchemy.alias(t, 'subgroup')

I don't know what your import error is about. "from sqlalchemy import
alias" works for me in SA 1.0.12.

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] Re: Assertion error on deleting one end of many-to-many relation

[Lele Gaifax]

Simon King  writes:

> In general I think it is not recommended to use "secondary" with a table
> that you have also mapped a class to. (eg. see the warning at the bottom of
> http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#association-object
> ).

Thank you for the link.

> You *might* be able to fix this by setting the cascade behaviour on
> Person.preferred_activities to "all, delete-orphan":

Will try that approach. My current workaround is to explicitly delete the
intermediary items before deleting the person instance.

ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
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] I wish to know how I use table ALIAS in alchemy core

[Krishnakant]

On Thursday 25 February 2016 03:50 PM, Simon King wrote:
On Thu, Feb 25, 2016 at 9:43 AM, Krishnakant > wrote:


Hello,
I have a query where there are 2 alias for a single table.
This is because the table contains a self referencing foreign key.
the table is (groupcode integer primary key, groupname text,
subgroupof integer foreign key references groupcode).
Now let's say I wish to have a 2 column query with groups and
their respective subgroups, I need to join the table to itself
making 2 aliases.
I know the raw query but need to do it through sqlalchemy core.
I don't use ORM for my project.and need this in the expression
language.


Something like this perhaps:

import sqlalchemy as sa
md = sa.MetaData()
t = sa.Table(
't', md,
sa.Column('groupcode', sa.Integer, primary_key=True),
sa.Column('groupname', sa.Text()),
sa.Column('subgroupof', sa.ForeignKey('t.groupcode')),
)

subgroup = t.alias('subgroup')
j = t.join(subgroup, subgroup.c.subgroupof == t.c.groupcode)
print sa.select([t.c.groupcode, subgroup.c.groupcode]).select_from(j)


Output:

SELECT t.groupcode, subgroup.groupcode
FROM t JOIN t AS subgroup ON subgroup.subgroupof = t.groupcode


Hope that helps,


Thanks a lot for the help.
I have one query.
do I not need to import alias?  some thing like,
from sqlalchemy import alias
I tryed this and I get import error for the above mentioned line.
and the query you provided did not work without alias.
Can you help?
Happy hacking.
Krishnakant.

--
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] Assertion error on deleting one end of many-to-many relation

[Simon King]

On Thu, Mar 3, 2016 at 8:27 AM, Lele Gaifax  wrote:

> Hi all,
>
> I have a simple m2m relationship between two entities, with a secondary
> table
> in the middle.
>
> From the left item I need to read both the right items and the middle
> ones, so
> I have two relationships on the left entity.
>
> When I delete one left item, I get the following exception:
>
>   AssertionError: Dependency rule tried to blank-out primary key column
>   'persons2activities.idperson' on instance
>   '
>
> I tried to investigate, but without luck. Am I missing something?
>
> The following script exhibits the problem:
>
>
> from sqlalchemy import create_engine
> from sqlalchemy import Column, Integer, ForeignKey, String
> from sqlalchemy.orm import relationship, sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> class Person(Base):
> __tablename__ = 'persons'
> id = Column(Integer, primary_key=True, nullable=False)
> name = Column(String, nullable=False)
> activities = relationship('Activity', secondary='persons2activities')
>
>
> class Activity(Base):
> __tablename__ = 'activities'
> id = Column(Integer, primary_key=True, nullable=False)
> name = Column(String, nullable=False)
>
>
> class PersonActivity(Base):
> __tablename__ = 'persons2activities'
> idperson = Column(Integer, ForeignKey('persons.id'), nullable=False,
> primary_key=True)
> idactivity = Column(Integer, ForeignKey('activities.id'),
> nullable=False, primary_key=True)
> # Comment out the following, and everything works without errors
> person = relationship(Person, backref='preferred_activities')
>
>
> def main():
> engine = create_engine('sqlite:///:memory:', echo=True)
> Session = sessionmaker(bind=engine)
> sess = Session()
> Base.metadata.create_all(engine)
>
> person = Person(id=1, name='lele')
> activity = Activity(id=1, name='carrom')
> person.activities.append(activity)
>
> sess.add(person)
> sess.flush()
> sess.commit()
>
> sess.expunge_all()
>
> person = sess.query(Person).get(1)
> sess.delete(person)
> sess.flush()
> sess.commit()
>
>
> if __name__ == '__main__':
> main()
>
>
In general I think it is not recommended to use "secondary" with a table
that you have also mapped a class to. (eg. see the warning at the bottom of
http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#association-object
).

If SQLAlchemy is handling the many-to-many relationship itself (via the
"secondary" argument), it will delete rows from the association table when
one of the related objects is deleted. However, your
"Person.preferred_activities" relationship is one-to-many, and so the
default behaviour when deleting the Person will be to set the foreign keys
pointing to it to NULL. In this case that means setting
PersonActivity.idperson to NULL, but this is impossible since that column
is part of the primary key of the table.

You *might* be able to fix this by setting the cascade behaviour on
Person.preferred_activities to "all, delete-orphan":

http://docs.sqlalchemy.org/en/rel_1_0/orm/cascades.html#unitofwork-cascades

Hope that helps,

Simon

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Assertion error on deleting one end of many-to-many relation

[Lele Gaifax]

Hi all,

I have a simple m2m relationship between two entities, with a secondary table
in the middle.

>From the left item I need to read both the right items and the middle ones, so
I have two relationships on the left entity.

When I delete one left item, I get the following exception:

  AssertionError: Dependency rule tried to blank-out primary key column
  'persons2activities.idperson' on instance
  '

I tried to investigate, but without luck. Am I missing something?

The following script exhibits the problem:


from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, ForeignKey, String
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Person(Base):
__tablename__ = 'persons'
id = Column(Integer, primary_key=True, nullable=False)
name = Column(String, nullable=False)
activities = relationship('Activity', secondary='persons2activities')


class Activity(Base):
__tablename__ = 'activities'
id = Column(Integer, primary_key=True, nullable=False)
name = Column(String, nullable=False)


class PersonActivity(Base):
__tablename__ = 'persons2activities'
idperson = Column(Integer, ForeignKey('persons.id'), nullable=False, 
primary_key=True)
idactivity = Column(Integer, ForeignKey('activities.id'), nullable=False, 
primary_key=True)
# Comment out the following, and everything works without errors
person = relationship(Person, backref='preferred_activities')


def main():
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
sess = Session()
Base.metadata.create_all(engine)

person = Person(id=1, name='lele')
activity = Activity(id=1, name='carrom')
person.activities.append(activity)

sess.add(person)
sess.flush()
sess.commit()

sess.expunge_all()

person = sess.query(Person).get(1)
sess.delete(person)
sess.flush()
sess.commit()


if __name__ == '__main__':
main()


Thanks in advance,
ciao, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
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] replacing a filter attribute, or iterating over a booleanclauselist?

[Ladislav Lenart]

Hello.

I think it would be (much) easier to simply rebuild the query from scratch
before each run. IMHO the time to build the query is not that big a factor to
justify the added source code complexity.

HTH,

Ladislav Lenart


On 3.3.2016 05:47, Brian Cherinka wrote:
> 
> 
> well you need a list of names so from a mapped class you can get:
> 
> for name in inspect(MyClass).column_attrs.keys():
> if name in :
> q = q.filter_by(name = bindparam(name))
> 
> though I'd think if you're dynamically building the query you'd have the
> values already, not sure how it's working out that you need bindparam()
> at that stage...
> 
>  
> Ok.  I'll try this out. This looks like it could work.  I think I need it for
> the cases where a user specifies a query with condition e.g. X < 10, runs it,
> gets results.  Then they want to change the condition to X < 5 and rerun the
> query.  As far as I know, if condition 2 gets added into the filter, you would
> have both X < 10 and X < 5 in your filter expression.  Rather than a single
> updated X < 5. 
> 
> What would be even more awesome is if there was a way to also update the
> operator in place as well.  So changing X < 10 to X > 10.  
> 
> 
> 
> 
> -- 
> 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.

-- 
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.