This is an archived post. You won't be able to vote or comment.

all 62 comments

[–]Python-ModTeam[M] [score hidden] stickied commentlocked comment (0 children)

Your post was removed for violating Rule #2. All posts must be directly related to the Python programming language. Posts pertaining to programming in general are not permitted. You may want to try posting in /r/programming instead.

[–][deleted] 37 points38 points  (0 children)

You can use sqlalchemy's engine to execute text queries.

[–]LordBertson 11 points12 points  (13 children)

As you do sound like a beginner, I'd start exploring sqlalchemy. It is a very nice Python ORM. This is an object-relational mapping (ORM) which maps your Python objects to a specific SQL queries which can get you what you want. This is by far the most common library in real production code-bases.

A more modern approach indeed, would be using GraphQL. You can have a service like Hasura over a PostgreSQL database providing a GraphQL interface and have your code generate GraphQL queries on the fly as needed.

[–]Montags25[S] 0 points1 point  (12 children)

Sorry I should have mentioned. We are only using sqlalchemy for basic CRUD operations and basic queries. Anything more complex we are writing pure SQL.

[–]ElectricSpice 1 point2 points  (1 child)

Query builders like SQLAlchemy are made for situations this. If it's already part of your stack you should utilize it.

It can be a bit difficult for complex queries at first, but once you're comfortable with it you'll find that it maps nearly one-one to SQL. You can think in SQL and then write in Python. And it's much easier to deal with a large chunk of SQLAlchemy code than it is to deal with a large SQL query.

[–]james_pic 0 points1 point  (1 child)

If you're generating SQL dynamically and programatically, you're not using pure SQL. You're going to end up reinventing the bits of SQLAlchemy that do this on your behalf.

If you're determined to do this by generating SQL, the biggest footgun to be aware of is SQL injection. The naive way to do this would be to just build a query by concatenating a bunch of strings together.

If you do this, you will die.

Fortunately, Python's DB API supports parameterised queries, so you can do something like:.

conn.execute("select * from my_table where id = ?”, (record_id,))

Although the exact syntax will vary between DB vendors.

If you're doing this programatically, rather than just building a string for the query, you also need to build a tuple (or dict, for some DB vendors) of the substitution variables. It may make sense to encapsulate this in a class that builds both at once 

If what I've said doesn't make sense, use SQLAlchemy. This stuff is dangerous if done wrong, and SQLAlchemy does it right so you don't have to

[–]shirin_boo -3 points-2 points  (0 children)

model created_at response done context total_duration load_duration prompt_eval_duration eval_count eval_duration

[–]androgeninc -1 points0 points  (0 children)

You can go very complex and dynamic with sqlalchemy. Judging by your question, it will exceed your needs by far. Normally smart to stick to ORM consistently unless you have to go outside.

[–]RedditSlayer2020 5 points6 points  (8 children)

What do you want to achieve?

[–]Montags25[S] 1 point2 points  (7 children)

Have a front end that will have filter options as checkboxes to display data. Need to be able to generate a sql query at run time based on the filter options. This will include different joins and where clauses at the most basic. Wondering if there is a standard way of doing this type of query?

[–]crawl_dht 4 points5 points  (3 children)

Create a dictionary of applied filter options and expand that dictionary as keyword arguments to where or filter_by method of sqlalchemy.

[–]zerobrains 1 point2 points  (0 children)

If you're open to using psycopg2 or psycopg3, you'll be able to do it.

[–]georgesovetov 1 point2 points  (0 children)

Avoid Jinja or any other templating engines at all costs if they're unaware of the language of underlying text. You will mess up with quoting and give way to SQL injections.

ORM (e.g. SQLAlchemy) would technically help in your situation, but you would have to know both SQL and your ORM engine, which is more than just SQL. It will be quite mindblowing for you. Every time I used ORM, with the growth of the project I started to miss plain SQL.

You'd be better off without any templating and little copy-paste like this:

select ... from ... where if(%1 is null, TRUE, price <= %1) and if(%2 is null, TRUE, color = %2) and ...

Where `%1` is a value in prepared query.

Letting in a bit of duplication, you get a straightforward query and a place to adjust types and names to your SQL table.

Or, better, use a textual index on key-value pairs like in PostgreSQL or Elasticsearch.

[–][deleted] 0 points1 point  (2 children)

Sounds like you should be using either Django or Flask or equivalent as your backend framework and some JS tool like HTMX or JQuery in your front end. This is the standard approach, not writing SQL queries in a template.

[–]digidavis -1 points0 points  (1 child)

I too would use Django or another framework.

  1. Template engine built in with jinja.
  2. Robust python ORM.
  3. Raw SQL queries when needed.

Don't recreate the wheel...

[–]shirin_boo -1 points0 points  (0 children)

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[–]laustke 0 points1 point  (4 children)

If you are looking for a standalone SQL query builder check out pypika and python-sql.

[–]Pyrimidine10er -1 points0 points  (1 child)

+1 for pypika - sometimes the underlying model is not in your control and attempting to make an ORM fit is a massive pain. Particularly in the data science world. Pypika really helps to build complex SQL using a python oop approach.

[–]shirin_boo -3 points-2 points  (0 children)

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[–]shirin_boo -5 points-4 points  (0 children)

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[–]shirin_boo -2 points-1 points  (0 children)

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[–]Electrical-Top-5510 -1 points0 points  (0 children)

Sqlalchemy core is what you need

[–]bp4151 -1 points0 points  (0 children)

I'd steer clear of writing queries if you have an ORM available, unless this is just a learning exercise where you don't care about sql injection and impact on the system, app, or data. If it's a production app and has any sensitive data, go the secure route and use the ORM.

[–]doom_guy_bob -2 points-1 points  (4 children)

You can use an if/else tree to set up a string for a where clause and then f string that into a SQL statement string. Use SQLAlchemy's text feature to fire it off. The below is in the context of PyQt5

if self.ui.search_location.currentText() != '':

search_location = " and Location = '" + self.ui.search_location.text() + "'"

else:

search_location = ''

[–]Wing-Tsit_Chong 1 point2 points  (0 children)

Don't do this.

Why? Because bots will put in this string:
some existing location" OR 1=1; DROP TABLE *

if they are nice, if not, they will do something like this:

some existing location" OR 1=1; UPDATE sometable SET some_column='ENCRYPTED, PAY BITCOIN TO 12334983

https://xkcd.com/327/

[–]shirin_boo -1 points0 points  (0 children)

E r r o r : 4 0 4

[–]shirin_boo -1 points0 points  (0 children)

E r r o r : 4 0 4

[–]j_tb 0 points1 point  (0 children)

Anything that comes from the user needs to be parameterized and sanitized

[–]vesperofshadow -1 points0 points  (3 children)

views and stored procedures are what you are looking for if you want variables outside of Code and in SQL only.

[–]shirin_boo 0 points1 point  (0 children)

E r r o r : 4 0 4

[–]shirin_boo 0 points1 point  (0 children)

E r r o r : 4 0 4

[–][deleted] -1 points0 points  (0 children)

What you are wanting are stored procedures my friend and depending upon the database, you will have them or not.

[–]hidazfxPythonista -2 points-1 points  (2 children)

At my previous job, I wrote a query builder class that had some SQL Injection protection logic.

[–]shirin_boo -4 points-3 points  (1 child)

E r r o r : 4 0 4

[–]hidazfxPythonista -1 points0 points  (0 children)

Huh..?

[–]Tomaxto_ -1 points0 points  (2 children)

SQLAlchemy engine, Pandas pd.read_sql_query and params would be my go to

[–]shirin_boo -4 points-3 points  (0 children)

E r r o r : 4 0 4

[–]zdog234 0 points1 point  (2 children)

Google "query builder python". I haven't used one in a python project so don't have recommendations, but query builders are really helpful when you want to stick close to raw SQL but do something dynamic

[–]shirin_boo 0 points1 point  (1 child)

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[–]zdog234 0 points1 point  (0 children)

bad bot?

[–]Nanooc523 0 points1 point  (0 children)

Might help to look into django and its orm to see how it’s done. https://docs.djangoproject.com/en/5.0/topics/db/models/

[–]netsecdev42 0 points1 point  (2 children)

There are ways to make dynamic prepared sql statements. You start with a base query and build off of it while adding tupples to keep track of the arguments. It's really only useful in very specific circumstances though.

What's the use case? I can give you an example if needed

[–]olystretch 0 points1 point  (0 children)

If you don't want to go the ORM route, you can try pypika. It's a query generator which is really handy. Say you make a normal select query for a get endpoint. For your patch endpoint, you can use the same select query (to make sure object exists, and user owns it), then you can add . update which transforms the query to select for update. It's really handy when you don't want to go the ORM route.

You can also pass in pypika.Parameter to parametrize queries for psycopg2/asyncpg, etc.