CODEWITHSUNDEEP
pythonsqlalchemy
no13bus
no13bus

Reputation: 401

how to get some fields list by sqlalchemy?

This is how I get all of the field topicid values in Topics table.

all_topicid = [i.topicid for i in session.query(Topics)]

But when Topics table have lots of values, the vps killed this process. So is there some good method to resolve this?

Thanks everyone. I edit my code again, My code is below:

last = session.query(Topics).order_by('-topicid')[0].topicid
all_topicid = [i.topicid for i in session.query(Topics.topicid)]
all_id = range(1, last+1)
diff = list(set(all_id).difference(set(all_topicid)))

I want to get diff. Now it is faster than before. So are there other method to improve this code?

Upvotes: 0

Views: 414

Answers (1)

Michael Moura
Michael Moura

Reputation: 229

you could try by changing your query to return a list of id's with something like:

all_topic_id = session.query(Topics.topicid).all()

if the table contains duplicate topicid's you could add distinct to the above to return unique values

from sqlalchemy import distinct

 all_topic_id = session.query(distinct(Topics.topicid)).all()

if this still causes an issue I would probably go for writing a stored procedure that returns the list of topicid's and have sqlalchemy call it.

for the second part I would do something like the below.

from sqlalchemy import distinct, func  

all_topic_id = session.query(distinct(Topics.topicid)).all()  # gets all ids
max_id = session.query(func.max(Topics.topicid)).one()  # gets the last id
all_ids = range(1, max_number[0] + 1))  # creates list of all id's 
missing_ids = list(set(all_topic_ids) - set(max_id))  # creates a list of missing id's

Upvotes: 1

Related Questions