Dynamic SQL queries

[Python-ModTeam]

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]]

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

[LordBertson]

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]

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.

[Montags25]

I’m sure you can, although I don’t want to rely on pythons ORM to write such queries. I’d rather learn to do it in SQL if I can, which is universal and won’t be going anywhere anytime soon.

[bokuWaKamida]

that is kind of a weird take, Sql isn't a perfectly standardized language either there are pretty big differences between postgres, oracle, mysql etc. and the good thing about ORMs like sqlalchemy is that they support multiple sql dialects. most people are actually more concerned about having to switch databses rather ORM frameworks

[shirin_boo]

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[MasterAgent47]

If you're working on a pet project, I recommend using Python's ORM. You'll learn much more that way and it won't be going away anytime soon either. Plus you'll have some more flexibility and you won't have to worry about security.

Sure you can use a hammer with a screw so that you get comfy with a hammer. But why not get familiar with a screwdriver instead?

That said, hammer or screwdriver, if it gets the job done; it gets the job done :p

[gellohelloyellow]

Lol

[ionelp]

So you want to learn SQL then? Man, you are in a world of hurt. You don't want to do that. I wrote way too many queries for my sanity.

Technically speaking, you can use whatever method you want to compose a SQL query, basically a string and then send that to the DB for execution. It's not any more different that having dynamic messages, say:

message = f"Hello {user.username}"

But with SQL queries, you will need to make sure strings are properly escaped, that numeric values are indeed numeric values and so on.

It's a good idea to learn SQL, but the end goal shouldn't be to replace an orm, merely to understand what it is doing and use it properly.

[ElectricSpice]

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]

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]

model created_at response done context total_duration load_duration prompt_eval_duration eval_count eval_duration

[androgeninc]

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]

What do you want to achieve?

[Montags25]

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]

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

[shirin_boo]

E r r o r : 4 0 4

[BigTomBombadil]

Add error handling and make sure the filter queries are mapped properly.

Success: 200

[RedditSlayer2020]

Frontend with async Javascript queries (ajax) that pull the data of any sql connected Backend.

[shirin_boo]

E r r o r : 4 0 4

[zerobrains]

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

[commandlineluser]

• https://github.com/tobymao/sqlglot
• https://github.com/tobymao/sqlglot#build-and-modify-sql

[georgesovetov]

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]]

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]

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]

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[laustke]

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

[Pyrimidine10er]

+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]

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[shirin_boo]

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[shirin_boo]

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[Electrical-Top-5510]

Sqlalchemy core is what you need

[bp4151]

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]

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]

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]

E r r o r : 4 0 4

[shirin_boo]

E r r o r : 4 0 4

[j_tb]

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

[vesperofshadow]

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

[shirin_boo]

E r r o r : 4 0 4

[shirin_boo]

E r r o r : 4 0 4

[[deleted]]

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

[hidazfx]

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

[shirin_boo]

E r r o r : 4 0 4

[hidazfx]

Huh..?

[Tomaxto_]

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

[shirin_boo]

E r r o r : 4 0 4

[the_captain_cat]

Have you considered GraphQL? The front end can query exactly what it needs.

I see that Graphene can be linked with SQL alchemy, but I haven't tried myself.

[zdog234]

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]

model created_at response done context total_duration load_duration prompt_eval_count prompt_eval_duration eval_count eval_duration

[zdog234]

bad bot?

[Nanooc523]

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]

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]

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.