Building dynamic SQL queries with psycopg2 and postgresql

Question

I'm not really sure the best way to go about this or if i'm just asking for a life that's easier than it should be. I have a backend for a web application and I like to write all of the queries in raw SQL. For instance getting a specific user profile, or a number of users I have a query like this:

SELECT accounts.id,
    accounts.username,
    accounts.is_brony,
WHERE accounts.id IN %(ids)s;

This is really nice because I can get one user profile, or many user profiles with the same query. Now my real query is actually almost 50 lines long. It has a lot of joins and other conditions for this profile.

Lets say I want to get all of the same information from a user profile but instead of getting a specific user ID i want to get a single random user? I don't think it makes sense to copy and paste 50 lines of code just to modify two lines at the end.

SELECT accounts.id,
    accounts.username,
    accounts.is_brony,
ORDER BY Random()
LIMIT 1;

Is there some way to use some sort of inheritance in building queries, so that at the end I can modify a couple of conditions while keeping the core similarities the same?

I'm sure I could manage it by concatenating strings and such, but I was curious if there's a more widely accepted method for approaching such a situation. Google has failed me.

Subqueries? Something like select * from (<core query here>) as t order by random() limit 1; — Abelisto
– Abelisto, Commented Jan 12, 2017 at 22:39
that makes sense. is the any big performance impact with sticking a query into a subquery? I try to test it myself when i get a bit of time. — DigitalDisaster
– DigitalDisaster, Commented Jan 12, 2017 at 22:44
I'll run both through postgres explain and see what it spits out — DigitalDisaster
– DigitalDisaster, Commented Jan 12, 2017 at 23:14

Laurenz Albe · Accepted Answer · 2017-01-13 08:19:06Z

2

The canonical answer is to create a view and use that with different WHERE and ORDER BY clauses in queries.

But, depending on your query and your tables, that might not be a good solution for your special case.

A query that is blazingly fast with WHERE accounts.id IN (1, 2, 3) might perform abysmally with ORDER BY random() LIMIT 1. In that case you'll have to come up with a different query for the second requirement.

answered Jan 13, 2017 at 8:19

Laurenz Albe

256k22 gold badges310 silver badges386 bronze badges

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

1 Comment

DigitalDisaster Over a year ago

Thank you. This is what I've essentially done now. I will just have to test the outcomes with large sets of dummy data to make sure there will be no future performance concerns.

Collectives™ on Stack Overflow

Building dynamic SQL queries with psycopg2 and postgresql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related