[sqlalchemy] Re: Get the row count from a query

[Sergey V.]

An alternative would be

session.query(User).filter(User.id_ == id).count()

On Saturday, June 4, 2022 at 4:45:49 AM UTC+10 jason@stormfish-sci.com 
wrote:

> I believe I finally found a solution:
>
>  select(func.count(User.id_)).where(User.id_ == id)
>
> Thank you for taking the time to look.
>
> On Friday, June 3, 2022 at 1:45:39 PM UTC-4 Jason Hoppes wrote:
>
>> I would like to generate the following SQL using the ORM. I am testing 
>> for no rows returning:
>>
>> select count(*) from users where id = 
>>
>> I found session.query(User.id_).count() however that does not have a 
>> where clause. How would this be performed in the ORM with a where clause?
>>
>> Thanks,
>>
>> Jason
>>
>

-- 
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/6d98ca9d-f2da-4748-8e3f-2a11348d5b4fn%40googlegroups.com.

[sqlalchemy] Re: SqlAlchemy with Postgres: How can I make a query that checks if a string is in a list inside a json column

[Sergey V.]

Use .any():

session.query(Gizmo).filter(Gizmo.users.any('user1'))


On Wednesday, October 13, 2021 at 11:50:16 PM UTC+10 chat...@gmail.com 
wrote:

> Imagine a Postgres JSON column with values like below:
> "["user1", "user2"]" 
>
> Is there any way to query a postgres JSON (not JSONB) column with 
> SqlAlchemy,like above that checks if the value "user1" is contained in this 
> column?
>

-- 
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/8ed928f8-e79c-4228-a5cc-942ce87f1a26n%40googlegroups.com.

[sqlalchemy] Re: Don't want foreign key on child to be updated when parent is updated

[Sergey V.]

This thing is called referential integrity 
(https://en.wikipedia.org/wiki/Referential_integrity) and is enforced on 
the database level - you can't have a value in Child.parent_id which is not 
in Parent.id. The ForeignKey creates a constraint in the database which 
ensures the referential integrity of a column.

If you don't need it, you can define a column as a plain integer:

parent_id = Column(Integer, nullable=False)

but then SQLAlchemy won't be able to figure the joins so you'll need to 
specify them manually:

parent = relationship('parent', primaryjoin='Parent.id==Child.parent_id')


On Wednesday, February 27, 2019 at 8:29:39 AM UTC+10, Daniel Leon wrote:
>
> Suppose I have a Parent and Child table with Child having
> parent_id = Column(Integer, ForeignKey('parent.id'), back_populates=
> 'children', nullable=False)
> parent = relationship('parent')
> and Parent having
> children = relationship('child', back_populates='parent')
>
> Then if I try to delete a Parent, since Child has parent_id non-nullable 
> I'd get error *Cannot insert the value NULL into column parent_id*. I 
> want Child to retain its parent_id after its Parent is deleted.
>
> I didn't find a cascade option that accomplishes this.
>

-- 
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: Dynamic table on the same declarative model

[Sergey V.]

We have a similar setup (a PostgreSQL schema per tenant), we deal with 
different database versions by running different *code versions* - i.e. if 
you need a new column you release a new code version which knows how to 
deal with the new column. Switching a tenant to that code version runs an 
Alembic migration which adds the column to their schema. Each request is 
routed to a particular code version depending on the tenant's database 
schema.

This way the old code never sees the new database structure and vice versa.

-- 
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: `func.similarity` performance

[Sergey V.]

Robert, my understanding is that SQLAlchemy knows nothing about the 
Postgres's `similarity` function - sqlalchemy.func just magically generates 
the SQL output depending on which member you invoke. Try 
`func.magic_unicorns()`. So, there's not much to optimize here - it outputs 
what you give it. See this for 
details: 
http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.func

It's up to you to build a query which uses the `%` syntax if you need it. 
SQLAlchemy's Columns have the `.op()` method for that 
(http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.op)

So this raw sql query: "... WHERE model.description  % 'string' AND 
similarity(model.description, 'string') > 0.5" becomes

  
my_query.filter(Model.description.op('%')('string')).filter(func.similarity(Model.description,
 
'string') > 0.5)


-- 
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] How do I get an object from a relationship by object's PK?

[Sergey V.]

Hi all,

I've asked this question on stackoverflow: 
http://stackoverflow.com/questions/12031861/sqlalchemy-how-do-i-get-an-object-from-a-relationship-by-objects-pk/12032405

Basically, I'm looking for a dict-like access to a relationship to be able 
to quickly retrieve items by some key (item's PK). To illustrate, a 
normal relationship is list-like:

for book in library.books:
print book.id  # prints 10, 20, 30

*In addition,* I'd like to be able to access books from an already-loaded 
relationship by their id:

book1 = library.books.by_id(10)
book2 = library.books.by_id(20)
book3 = library.books.by_id(23)  # raises KeyError

while still being able to iterate over library.books in a list-like manner.

I'm looking at MappedCollection 
(http://docs.sqlalchemy.org/en/latest/orm/collections.html?highlight=collection#custom-dictionary-based-collections)
 
- would building a custom collection class be the right solution to the 
problem? Or is there something pre-built for this purpose in the depths of 
SQLAlchemy?

Thanks,

-- 
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/-/mUIFlVguh34J.
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: Two self references, conflicting

[Sergey V.]

From your code it's not clear how you're going to differentiate
between matches and merges - basically, you need something in your
database which makes matches different from merges and then
configure the retationships to use those fields.

See the Boston addresses example in the documentation:
http://docs.sqlalchemy.org/en/latest/orm/relationships.html#specifying-alternate-join-conditions

Or maybe you even need two FKs - source_id and another one, and,
again, configure the relationships to use those FKs.
http://docs.sqlalchemy.org/en/latest/orm/relationships.html#specifying-foreign-keys

On Mar 18, 7:18 pm, Devraj Mukherjee dev...@gmail.com wrote:
 Hi all,

 I am trying to created self references to a Table, I actually need two
 self references back to the same Table.

     matches = relationship(Criterion)
     merges = relationship(Criterion)

 What I notice happens is the references are getting mixed up. Hence
 when I add to matches it adds to the merges property.

 Is there a better / cleaner way of doing this?

 Here's what I have for now:

 class Criterion(Base):

     __tablename__ = 'criterion'
     criterion_id = Column(Integer, Sequence('criterion_sequence'),
 primary_key=True)
     standard_id = Column(Integer, ForeignKey('standard.standard_id',
 ondelete=CASCADE), nullable=False)
     source_id = Column(Integer, ForeignKey('criterion.criterion_id'),
 nullable=True)

     number = Column(Integer())
     title = Column(Text())
     statement = Column(Text())
     created = Column(DateTime, default=func.current_timestamp())
     last_updated = Column(DateTime, default=func.current_timestamp(),
 onupdate=func.current_timestamp())

     matches = relationship(Criterion)
     merges = relationship(Criterion)

-- 
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: Database-side data mangling

[Sergey V.]

Thanks for your answer Michael.

I experimented with column_property and generally it works, however
now I have 2 separate attributes - one for setting a value and
another, read-only one, for reading the modified value.

class User(Base):
...
email = sa.Column(String)

@validates('email')
def validate_email(self, key, value):
return sa.func.lower(value)

email_upper = column_property(sa.func.upper(email))

Is there a way to have just a single field which looks like a normal
attribute, but performs some processing on the database side on the
way in and out? What I'm looking for is:

user.email = john.len...@beatles.com
session.flush()
session.select(text(SELECT email FROM users WHERE name='Lennon'))
# returns john.len...@beatles.com - just to illustrate that it's
stored lower-case in the database
user = session.query(User).filter(...).one()
print user.email
# prints john.len...@beatles.com

I was able to achieve something similar using a property:

class User(Base):
...
_email = Column(email, String)

@property
def email(self):
return object_session(self)\
.scalar(
select([func.upper(self._email)])
)

@email.setter
def email(self, value):
self._email = object_session(self)\
.scalar(
select([func.lower(value)])
)

- but obviously the email attribute is not loaded inline -
instead, a separate query is issued each time the attribute is
accessed. Also, the _email attribute IS loaded and then sent back to
the server, which sort of undermines the idea of database-side
processing...

On Jul 28, 2:06 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 On Jul 27, 2011, at 8:56 PM, Sergey V. wrote:

  Good day,

  I'm trying to figure out how to do something similar to the Symmetric
  Encryption recipe (http://www.sqlalchemy.org/trac/wiki/UsageRecipes/
  SymmetricEncryption), only on the database side, not in Python.

  I have a suspicion that @compiles decorator may provide a solution,
  but having trouble understaning how to apply it to my case.

  For simplicity, let's imagine a field which stores data in upper case
  but always returns it in lower case... so it needs to generate SQL
  similar to

  INSERT INTO tablename VALUES (..., upper(...), ...)

 this can be assigned, (1) i.e.

 myobject.fieldname = func.upper(somename)    

 which you can apply with a @validates decorator  (2)



  on insert and

  SELECT ..., lower(fieldname) as fieldname, ... FROM tablename

 for this you'd use column_property().  (3)

 for the SQL functions themselves we're using func (4)

 1:http://www.sqlalchemy.org/docs/orm/session.html#embedding-sql-insert-...
 2:http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators
 3:http://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions...
 4:http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sq...

 For a slightly old example of some of this kind of thing (probably more 
 complicated than you'd need here), see the PostGIS example under 
 examples/postgis/.

-- 
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] Database-side data mangling

[Sergey V.]

Good day,

I'm trying to figure out how to do something similar to the Symmetric
Encryption recipe (http://www.sqlalchemy.org/trac/wiki/UsageRecipes/
SymmetricEncryption), only on the database side, not in Python.

I have a suspicion that @compiles decorator may provide a solution,
but having trouble understaning how to apply it to my case.

For simplicity, let's imagine a field which stores data in upper case
but always returns it in lower case... so it needs to generate SQL
similar to

INSERT INTO tablename VALUES (..., upper(...), ...)

on insert and

SELECT ..., lower(fieldname) as fieldname, ... FROM tablename

on select.

I'm using orm and I imagine the final result would look like

class MyModel(Base):
...
myfield = AlwaysLowercaseColumn(sqlalchemy.String)

or

class MyModel(Base):
...
myfield = sqlalchemy.Column(AlwaysLowercaseString)

Thanks,
Sergey

-- 
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: confused on avoiding sql injections using ORM

[Sergey V.]

 Say I create an instance of a mapped class and then attach some values
 to it.
 And want to do session.add.

If you're worried about something like this:

user = User()
user.name = ;DROP TABLE users;
session.add(user)

then don't be, there is no possibility of SQL injection here,
SQLAlchemy takes care of that. Unless your tests show otherwise, of
course :)

What you shouldn't do though, as Malthe points out, is to manually
construct SQL statements from bits which potentially come from user
input:

   name = raw_input(Enter your name)
   session.execute(sa.text(INSERT INTO users VALUES (' + name +
')))

- that's where you should use expression api instead.

-- 
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: confused on avoiding sql injections using ORM

[Sergey V.]

Can you give an example of sql injection working with ORM? Some sample
code etc.

On Jul 5, 5:41 am, Krishnakant Mane krm...@gmail.com wrote:
 Hello all.
 I use Pylons 0.9.7 and sqlalchemy.
 I use the Object Relational Mapper with declarative syntax in a few of
 my modules.
 I was reading chapter 7 of the Pylons book and I understood that sql
 injections can be avoided using the expression api.
 But can this be also done using ORM?
 I tryed on my software and sql injections do work.
 Is it possible to avoide it with ORM or will i have to totally avoide
 using an ORM layer of sqlalchemy and only use the expression api?
 Happy hacking.
 Krishnakant.

-- 
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: Speed matters

[Sergey V.]

Hi,

One easy/obvious improvement would be to delete all user's
identifiers and groups at once without iterating over them for
every user. Actually, iterating over the list of user_ids is not
necessary too I think.

code
session.query(Identifier).filter(Identifier.user_id.in_(user_ids)).delete()
session.query(User).filter(User.id.in_(user_ids)).delete()
/code

I'm not sure about deleting groups in your code - I suppose you
don't want to delete the actual group but only the association between
the user and the group, i.e. the record from the intermediate table.
But the idea is the same - .filter(blah-
blah.user_id.in_(user_ids)).delete()

An even better solution would be to set up proper cascade rules on
your relationships so all dependent items are deleted automatically
when a user is deleted. Then the method will be a one-liner:

code
session.query(User).filter(User.id.in_(user_ids)).delete()
/code


On Jun 1, 7:40 pm, Fabien Ribes fri...@gmail.com wrote:
 Hi,

 I have three tables (user, identifiers, groups) with 'many to one' and
 'many to many' relationships. My code for deleting a user works
 correctly but I would like to have it run faster. What improvements do
 you suggest ?

 code
     def delete_list(self, user_ids):
         Delete a list of users by id

         session = self._get_orm_session()
         for user_id in user_ids:
             try:
                 user = session.query(User).filter(User.id ==
 user_id).one()
                 if user:
                     # loop to free the user's identifiers
                     for ident in user.identifiers:
                         ident.user = None

                     # loop to remove the user from the groups
                     for group in user.groups:
                         del group

                     session.query(User).filter(User.id ==
 user_id).delete()
             except NoResultFound, ex:
                 raise SmartResourceNotFound(User not found  +
 str(ex))

         session.commit()
 /code

-- 
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: Setting column value that changes everyday

[Sergey V.]

 Do you need to store expiry_code? seeing as it is a function of
 last_con and the current date.

Second that. I would also point out that phone number probably
shouldn't be an integer - how would you store phone numbers which
start with 0, for example?

I'd rather make it a String.

-- 
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: Odd many-to-one problem

[Sergey V.]

 relationship() expects a class or a mapper instance, not a string. I
 got this error:

 ArgumentError: relationship 'available_deals' expects a class or a
 mapper argument (received: type 'str')

Hmm... I'm not sure what I'm doing wrong but passing strings to
relation() definitely works for me:


class Host(Base):

__tablename__ = 'hosts'
id = sa.Column(sa.Integer, primary_key = True)
...
datacentre_id = sa.Column(sa.Integer,
sa.ForeignKey('datacentres.id'))
datacentre = sa.orm.relation('Datacentre', backref='hosts')

Can it be because I'm using declarative? In my case I don't even need
to import Datacentre class before I declare Host class.

-- 
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: Odd many-to-one problem

[Sergey V.]

 The twist is that I've spread out my tables and ORM classes across
 several files. I've tried to keep it so that I don't have circular
 dependencies. That means I've defined Merchant first, and then Deal
 later, in separate files

To avoid problems with imports and dependencies you can pass strings
to the relationship function instead of the actual classes:

mapper(Deal, deals, properties=dict(
  merchant=relationship('Merchant', backref='deals'),
  ))

This greatly simplifies everything if you split your classes into
separate files.

Regarding 'available_deals', 'deleted_deals' etc. - the approach with
properties is sub-optimal. Consider a merchant having thousands of
deals, only a few of which are available - the method would have to
fetch all those deals only to discard most of them. Also, that won't
work with eager loading. The optimal way would be to make SA to
generate a query like SELECT ... FROM Deals WHERE ... AND deleted=1
which would return only the records we're interested in. I'm sure it's
possible but I'll leave it to you to find it in SA docs :) When you
find it please post it here :)

-- 
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] association_proxy question... I think

[Sergey V.]

Hi all,

I've got two tables: Users (id, password) and UserProperties (user_id,
name, value). Is there a way to map the properties stored in
UserProperties as attributes of User object?

I mean,

john = User('john', 'password')
john.name = John Smith # creates UserProperty('john', 'name', 'John
Smith')

The set of possible attributes is fixed so I don't mind hard-wiring
them to the User object one by one, something like that:

class User(...):
   ...
   name = some_magic_function(UserProperty, 'name')
   address = some_magic_function(UserProperty, 'address')

I've read on association_proxy but couldn't figure out how to use it
in this case.

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] Check if an item exists in a relation

[Sergey V.]

Hi all,

I must be missing something obvious here...

Let's suppose I have the following class:

class User(Base):
# 
addresses = relation(Address, backref=user)

and I have a number which may be an ID of an Address object. How do I
check if the number is an ID of one of Addresses of a given User?

I could do that just iterating over the addresses:

for address in user.addresses:
 if address.id == ID:
print TADA!

... but this doesn't seem like a good solution. There must be a way to
make SQLAlchemy to return the value.

(to make it a bit more interesting - the code needs to be generic,
i.e. the function just gets some SA-mapped object and property name,
so I can't just build a query manually like this -

addr = session.query(Address).filter(id=address_id).filter(user_id =
user.id).one()

- because I don't know what the join fields are (and if possible I'd
like this to work with many-to-many relations too)
)

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: Check if an item exists in a relation

[Sergey V.]

 Some assumptions:
 1. SA-mapped object means the user object in the example
 2. property name means addresses in the example
 3. The function shouldn't assume that you want an Address object
 4. The ID attribute is known ahead of time (e.g. its always id). If
 not, your function will need another parameter.
 5. The function needs to work on many-to-many relationships in addition
 to one-to-many.

 Then this should work:
 def get_related_by_id(obj, property_name, id):
     relation = getattr(obj.__class__, property_name) # in example:
 User.addresses
     related_class = relation.property.argument # in example: Address
     return Session.query(related_class).filter(relation.any(id=id)).first()

 example usage:
 address_exists = get_related_by_id(user, addresses, 1234) is not None

Cool, it almost solves my problem!

However, it doesn't check if a User have a given Address, it just
checks if an Address exists in general. It would be easy to add
another filter() by User.id, but that wouldn't work for many-to-many
relations.

My hope was to somehow get a Query object from a relation property
with everything already set up (imagining that a relation somewhere
stores the query it itself uses) and then just attach another filter()
to it.

Imaginary code:

relation = getattr(obj.__class__, property_name)
q = relation.get_query(...)
result = q.filter(id = address_id).first()

This approach works in Django's ORM, so it definitely should be doable
in SA :)
--~--~-~--~~~---~--~~
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: Check if an item exists in a relation

[Sergey V.]

Ahh... I missed the relation.any() part of your example - with it the
code should behave exactly as I need. I think. I need to give it a
try.

Thanks!

On Oct 30, 9:53 am, Sergey V. sergey.volob...@gmail.com wrote:
  Some assumptions:
  1. SA-mapped object means the user object in the example
  2. property name means addresses in the example
  3. The function shouldn't assume that you want an Address object
  4. The ID attribute is known ahead of time (e.g. its always id). If
  not, your function will need another parameter.
  5. The function needs to work on many-to-many relationships in addition
  to one-to-many.

  Then this should work:
  def get_related_by_id(obj, property_name, id):
      relation = getattr(obj.__class__, property_name) # in example:
  User.addresses
      related_class = relation.property.argument # in example: Address
      return Session.query(related_class).filter(relation.any(id=id)).first()

  example usage:
  address_exists = get_related_by_id(user, addresses, 1234) is not None

 Cool, it almost solves my problem!

 However, it doesn't check if a User have a given Address, it just
 checks if an Address exists in general. It would be easy to add
 another filter() by User.id, but that wouldn't work for many-to-many
 relations.

 My hope was to somehow get a Query object from a relation property
 with everything already set up (imagining that a relation somewhere
 stores the query it itself uses) and then just attach another filter()
 to it.

 Imaginary code:

 relation = getattr(obj.__class__, property_name)
 q = relation.get_query(...)
 result = q.filter(id = address_id).first()

 This approach works in Django's ORM, so it definitely should be doable
 in SA :)
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---