Getting duplicates when i try to iterate results from mysql using python

Question

I have the following SQLALCHEMY CODE

    my_query = db.session.query(Isps.isp_name, Ratings.rating_value,
    services.service_name, Service_metric.metric_name)

the above basically translates to this ,when i dump my query

SELECT isps.isp_name AS isps_isp_name, ratings.rating_value 
AS   ratings_rating_value, services.service_name 
AS services_service_name,      service_metric.metric_name 
AS service_metric_metric_name 
FROM isps, ratings, services, service_metric

if i do a for loop on the my_query like this

for data in my_query:
    print data.isp_name

this will give me duplicates

ISP1
ISP2
ISP3
ISP1
ISP1
ISP2
ISP3
ISP1

AND so on and so on.

my guess its looping through the other tables as well there by giving me duplicates

NB:THESE TABLES ARE UNRELATED , I AM SELECTING ALL THE DATA SO I CAN USE IT A DROPDOWNS ON MY FORM

That's what happens when you do cross joins ("Cartesian products"). What are you expecting if you don't use proper join syntax? — Gordon Linoff
– Gordon Linoff, Commented Apr 21, 2016 at 0:35
Can you show us the columns of each of the tables in your join? — Steven Moseley
– Steven Moseley, Commented Apr 21, 2016 at 0:39
Please note that the tables are unrelated ,what i want to achieve is selecting those specific fields and be able to reference them in a form , like for example dropdowns in a form that are populated using the data from the database isps has 3 columns id , isp_name ,isp_descpription services table has id ,service_name — Chamambom
– Chamambom, Commented Apr 21, 2016 at 0:48

Steven Moseley · Accepted Answer · 2016-04-21 14:56:16Z

2

What you're likely looking for is something like this.

Warning, I don't know what your schema looks like, so the names here (and possibly the actual structure) are probably off.

SELECT i.isp_name, 
    COALESCE(AVG(r.rating_value), 'n/a') AS avg_rating, 
    COUNT(DISTINCT r.rating_id) AS num_ratings,
    s.service_name,
    sm.metric_name 
FROM isps AS i
    LEFT JOIN ratings AS r ON r.isp_id = i.isp_id
    INNER JOIN services AS s ON i.service_id = s.service_id
    INNER JOIN service_metric AS sm ON s.metric_id = sm.metric_id
GROUP BY i.isp_name

What you're doing is joining on the domain tables (services and service metric) by their Foreign Key in the isps table, and on the ratings table by the isp id.

This will give you relativity in your query results - you'll get the services that relate to the isps, and not the "Cartesian Product' Gordon mentioned above, where you retrieve every service joined with every isp, for example.

To accomplish a JOIN in SQLAlchemy, use a select_from clause with a join clause as an arg, e.g.: http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.join

The GROUP clause at the end will cluster your data such that only one ISP is returned per row.

However, then you have to aggregate tables that might return multiple results against a single ISP (e.g. ratings).

We do that by using aggregate functions, like AVG and COUNT to extract meaningful data from a group of values.

To do this in SQLAlchemy, use a count with a group_by clause: http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.count

edited Apr 21, 2016 at 14:56

answered Apr 21, 2016 at 0:43

Steven Moseley

16.4k4 gold badges43 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chamambom Over a year ago

i was hoping to use the SQLALCHEMY syntax since i am using it as my underlying SQL library ...

Steven Moseley Over a year ago

@Chamambom you'll want to use JOIN syntax, with a select_from clause, as defined here: docs.sqlalchemy.org/en/rel_1_0/orm/…

Dahark · Accepted Answer · 2016-04-21 08:59:17Z

Well here is another idea. How about you create a view in your Database, that will put all the values you want into 2 Columns?

CREATE
 ALGORITHM = UNDEFINED
 DEFINER = root@localhost
 SQL SECURITY DEFINER
 VIEW `v`
 AS 
select `isps`.`isp_name` AS `VALUE`,'ISP_NAME' AS `TYPE` from `isps` 
union all 
select `ratings`.`rating_value` AS `VALUE`,'RATING_VALUE' AS `TYPE` from `ratings` union all 
select `services`.`service_name` AS `VALUE`,'SERVICE_NAME' AS `TYPE` from `services` 
union all 
select `service_metric`.`metric_name` AS `VALUE`,'METRIC_NAME' AS `TYPE` from `service_metric`

Now you should be able to query that view in python. When you loop through them you should get no duplicate entrys. If you want to populate dropdowns in a form you can simply add a filter to your query and select them by the "TYPE" from the view.

Collectives™ on Stack Overflow

Getting duplicates when i try to iterate results from mysql using python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related