CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
DigitalDisaster
DigitalDisaster

Reputation: 517

Building dynamic SQL queries with psycopg2 and postgresql

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.

Upvotes: 0

Views: 906

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248165

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.

Upvotes: 2

Related Questions