Issue 1:
Currently, let's assume I write a query like the following

q = session.query(Customer).join(Sales, Customer.id==Sales.customer_id).filter(Customer.id==1).all()

under the assumption, e.g., that Customer 1 has 5 sales.

Now, q.count() would return 5, as also SQL would do if I run the corresponding SQL query.

However, q.all() will only return a list of 1 Customer, as it deduplicates results - this is done here:

sqlalchemy/lib/sqlalchemy/orm/loading.py

Lines 88 to 89
in
a0d1030

if filtered:

rows = util.unique_list(rows, filter_fn)

where filtered is defined a few lines above from query._has_mapper_entities`

sqlalchemy/lib/sqlalchemy/orm/loading.py

Line 38
in
a0d1030

filtered = query._has_mapper_entities

The suggested solution, see e.g.

#4143 (comment), is apparently to run instead

q = session.query(Customer.id).join(Sales, Customer.id==Sales.customer_id).filter(Customer.id==1).all()

that indeed would return [1,1,1,1,1] (as I would expect from a SQL point of view).

I imagine this is to make life easier for some users, but actually we need (aiidateam/aiida-core#1600) a way to get the SQL behaviour without deduplication, i.e. a list of 5 Customer entities (we could hack the query and do q._has_mapper_entities = False, but this clearly looks like the wrong thing to do)

Is there a proper way to get the 5 results (i.e. not do a 'distinct') already?

Or would it be possible to add an official, documented method on the query that we can call to internally set q._has_mapper_entities to false? So we can rely on it and not on internal functionality of sqlalchemy that might change in the future.

Issue 2:
Describe your question

I'm having table A with foreign key of table B. Now When I'm adding a New object to table A with following code.

it gives me the following error describe below.

Example

Proof 1 see 2nd answer it works fine there

Complete stack trace

problem occured on line session.commit()
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ratings_rating_key"
DETAIL: Key (rating)=(8.8) already exists.

[SQL: INSERT INTO ratings (rating) VALUES (%(rating)s) RETURNING ratings.id]
[parameters: {'rating': 8.8}]

Versions

OS: Windows10

Python: 3.8.3

SQLAlchemy: 1.3.22

Database: postgres

DBAPI:

Additional context

Code

session = self.Session()
performer = Performer(
name = item['name'],
aliases = item['aliases'],
gender = item['gender'],
description = item['description'],
profile_pic = item['profile_pic'],
date_of_birth = item['date_of_birth'], # get age from here
ethnicity = item['ethnicity'],
birth_place = item['birth_place'],
height = item['height'],

)

rating = session.query(Rating).filter_by(rating=item['rating']).first()
rating_exists = rating is not None

if rating_exists:
performer.rating = rating
else:
performer.rating = Rating(rating=item['rating'])

performer_exists = session.query(Performer).filter_by(name=item['name']).first() is not None

if performer_exists:
logging.info(f'Item {performer} is in db')
return item
else:
try:
session.add(performer)
session.commit()
logging.info(f'Item {performer} stored in db')
except:
logging.info(f'Failed to add {performer} to db')
session.rollback()
raise
finally:
session.close()

Have a nice day!

The really thing is I don't know how to add performer and add it but when when rating exist it should use that or other wise insert so basically how to add some fields of model and update some specific ones.