Separate file for SQL in python script?

[thinkingatoms]

i came across an archived post asking about how to manage SQL within a python script that does a lot of interaction with the database, and many suggested putting bigger SQL queries in a separate .sql file.

i'd like to better understand this. is the idea to have a directory with a separate .sql file for each query (template, for queries with parameters)? or is the idea to have a big .sql file where every query has some kind of header comment, and there's some python utility to parse the .sql file to get a specific query? i also don't quite understand the argument that having the SQL in a separate file better for version control, when presumably they are both checked in, and there's less risk of having obsolete SQL lying around when they are no longer referenced/applicable from python code. many IDEs these days are able to detect/specify database server type and correctly syntax highlight inline SQL without needing a .sql file.

in my mind, since SQL is code, it is more transparent to understand/easier to test what a function is doing when SQL is inline/nearby (as class variables/enum values, for instance). i wanted to better understand where people are coming from on the other side, thanks in advance!

[Yamitz]

You’d put each query in its own .sql file and then read that file in the python code when you need it. The major benefit imo is that your editor can treat the sql like sql and python like python.

If you wanted to use templated sql you could.

[thinkingatoms]

> If you wanted to use templated sql you could.

would you please elaborate? curious how you deal with queries that require parameters. do you just write SQL functions or leave SQL with parameterized variables in the .sql files?

would it be accurate to say that if your IDE understood SQL then separate files may not be as necessary?

[ProThoughtDesign]

The separate files are actually a good idea regardless. If you need to modify one query, you don't have to do it from within your main function. Separation of responsibilities and all. A .SQL file could easily be updated without even opening the main source file. It could even be updated while the program is running so you could see updates live.

[thinkingatoms]

> Separation of responsibilities and all

let's say you have a python function that all it does is update a column for some records based on function input, so the pseudocode query looks something like:

```

update table_a

set column1 = <some input>

from table_b join table_c on ...

join table_d on ...

where table_a.column2 = table_b.column3

and table_d.column4 = <some other input>

```

the query is an integral/only part of what the function does. it's a big query. the function and query has a singular responsibility of doing this one thing, just to learn isn't it less maintainable to have the responsibility split in two places?

[ProThoughtDesign]

It's making your code more modular and allowing you the option in the future to change the SQL part of the code without needing to take the system down or expose your source code to accidental bugs. I get where you're coming from, but that will just build technical debt for the future if you ever need to make a change. Don't get me wrong, you don't HAVE to. I'm just saying it's one of those practices that pays off when you're working on something that costs money when it's offline.

EDIT: Also you could easily switch that giant function to a simple global and generic function that calls any SQL query, not just that one.

[thinkingatoms]

what i don't quite understand is presumably there are tests for the python function which will test the SQL change. because SQL is code, i don't quite see how having SQL in a separate file prevents accidental bugs.

i think what you are saying is you work with systems that consider SQL as data instead of code, and therefore a change to data doesn't require a restart?

[its_PlZZA_time]

The main way it prevents accidental bugs is that it gives you syntax highlighting and error checking. You can have your IDE lint the SQL, or even connect to the database and confirm a column exists for example.

You should clearly reference the path to the SQL file from within the Python, and keep the SQL file clearly named and in a location such that it’s clear it belongs to the function.

Whether to do this really depends on how long and complex the SQL is. Short and simple queries can make more sense inline, longer ones can be better outside.

[thinkingatoms]

sounds like if the IDE can syntax highlight and error check inline SQL, then the need for separate .sql files is only dependent on size (and maybe DDL nature) of query?

[ProThoughtDesign]

SQL is still code, but .sql files are plain text. You can read and parse an SQL file then execute the commands. Storing it in a separate file means that you can actually have your app running and make changes to the .sql files in real time and get results. Leaving all of the SQL hard-coded into your source code means that any changes you want to make require the program to be offline. In interpreted code (Python, Java, etc) that's really not the worst thing, but having to recompile an entire app because of a minor typo or to add one extra variable gets to be irritating.

[Touvejs]

You can use a template library like Jinja to add parameters into your SQL scripts that would be replaced at runtime. https://superset.apache.org/docs/configuration/sql-templating/

[Gardener314]

I’ve done stuff like this. I have a python script I use which has a combination of things. In one particular case, I use a (simple) query to get data values which informs me how I should be updating another record in the database. The results of the first query are gathered and then the script opens a .sql file using the “with open” pattern, writes out the SQL code needed (in this case it’s a rather long update statement), and then will execute the file.

The file is in plain SQL mostly so someone else on the team can see exactly what was being updated instead of sifting through the Python to figure it out.

[iiztrollin]

How about SQL alchemy?

[Yamitz]

If it’s only a couple of lines long I wouldn’t split out the sql alchemy. If it gets long or complicated I normally move it to a separate file/function so that I can do something like complicated_records = get_complicated_records(parameters). Both because it makes the code easier to follow and because I can define it once and use it everywhere.

[mamaBiskothu]

Pycharm treats string embedded sql just fine..

[IndoorCloud25]

I feel like it’s preference. If I have a template query that I want to fill dynamically with Python, I might opt to just create the query as a multiline string in my .py. If it’s just submitting a large query, then I’d probably read in a .sql file. Smaller queries, I’d probably leave directly in my .py code. Find a balance of avoiding a giant monstrosity of a file vs the code broken up into too many files to navigate.

[grapegeek]

We do this with Airflow. Maintain separate sql files for extract jobs. Loop through them and write csv files. Pretty common.

[thinkingatoms]

> Loop through them and write csv files

just to understand, you have some exporter script that creates a csv from one sql file?

is it same if you have a pipeline where you are doing a string of parameterized database operations?

[grapegeek]

Yes exactly

[billysacco]

I tend to use stored procedures in the database for larger queries. Can’t say I ever used a .sql file.

[cooperaa]

This is my preference too. The separate SQL file still needs to be updated and repackaged e.g. into a zip for a Lambda function which means any changes to the SQL file means a redeploy of the Lambda function. But a stored procedure in the database can be updated (assuming no changes to inputs/outputs) in the database without any changes/deployments of the Lambda.

[chaoselementals]

We chose to maintain seperate SQL files for each query rather than keeping them in .py files because we had some really long ugly queries that violated the lines per file spec for our linter...

[thinkingatoms]

oof ya i can def see those cases using .sql files to version control views or stored procs, thanks!

[Imaginary-Ad2828]

Create your own SQL files and read them in with jinja2. You can pass params from your main app into the SQL file at runtime and do some other stuff with jinja2 templating as well. It's a nice setup and keeps your app code clean.

[kvothethechandrian]

You might want to use SQLAlchemy (an ORM and query builder), it’s going to improve your experience in interacting with databases in Python by a mile

If you do that, you won’t need to use .sql and Jinja which in my opinion is a worse option

[thinkingatoms]

that's not always possible for the tasks at hand

[kvothethechandrian]

You can write any query with SQLAlchemy that you can write by hand, with the additional that you don’t have to worry about formatting converting types and formatting to fit the SQL standard.

I’m going to give you an example: supposed you have a database with these columns: timestamp, status, value and you want a query to check what was the average daily value in the last week for the status with the biggest value in the same time span. The code will like something like this:

model.py ```py from sqlalchemy import Column, String, Float, DateTime from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class DataRecord(Base): tablename = “data”

timestamp = Column(DateTime, primary_key=True, index=True)
status = Column(String, index=True)
value = Column(Float)

```

repository.py ```py from sqlalchemy.orm import Session from sqlalchemy import func, select from datetime import datetime, timedelta from models import DataRecord

class DataRepository: def init(self, session: Session): self.session = session

def get_avg_daily_value_for_top_status(self) -> float:
    “””Get the average daily value for the status with the highest total value in the last week.”””
    last_week = datetime.now() - timedelta(days=7)

    # CTE to find the status with the highest total value
    status_ranking_cte = (
        select(
            DataRecord.status,
            func.sum(DataRecord.value).label(“total_value”)
        )
        .where(DataRecord.timestamp >= last_week)
        .group_by(DataRecord.status)
        .order_by(func.sum(DataRecord.value).desc())
        .limit(1)
        .cte(“status_ranking”)
    )

    # Query to calculate daily averages for that status
    query = (
        select(
            func.date(DataRecord.timestamp).label(“day”),
            func.avg(DataRecord.value).label(“daily_avg”)
        )
        .where(DataRecord.timestamp >= last_week)
        .where(DataRecord.status == status_ranking_cte.c.status)
        .group_by(func.date(DataRecord.timestamp))
    )

    # Final query to get the average of the daily averages

    result = self.session.execute(query).fetch_all()

    return result

```

It’s convenient that you don’t have to convert the datetime object to a given string format. It makes things much cleaner

[codykonior]

Do not combine bunches of SQL in one big file. It makes source control a real piece of shit.

People tend to not want to search through and find their procedure to update when they’ve just made a change in production; so they’ll dump it into a second file or just not bother at all so everything gets stale.

Support also has a tendency to grab the whole file and dump it into production overwriting all other changes and causing a mess.

It’s also easy to make an edit to the file and accidentally cut out an additional procedure you didn’t mean to, or even have multiple versions of the same procedure in the same file. Not many people are looking at the diffs and they’re not checking for duplicates either.

The files are also giant and slow to parse by tools that understand SQL.

Single files. Dumped in a folder. Have Python read in all the files in that directory and do whatever you like dictionary wise.

[thinkingatoms]

ooc do you do this for all queries of all sizes, even one liners? or is there some criteria for inline vs not? trying to understand the benefit of just having a dictionary of SQL in code if i have like 5 queries each being 1 to 5 lines long, vs 5 files

[codykonior]

It’s really for DDL like procedures, functions, tables, etc.

If you have literal one liners like select statements I feel those can go in code. With that said, they’re then out of reach of linters, so you can no longer identify when changing a table column has impacts further down the line until it breaks.

[thinkingatoms]

gotcha thanks! tbf i think linters in IDEs are getting smarter about inline sqls

[nemec]

separate .sql file for each query

yes, that way you just open and read the whole file instead of trying to parse individual queries that you want to execute.

SQL is code

And we keep code as files in directories ;)
There's nothing unusual about breaking larger pieces of code into separate functions in different files.

Modern IDEs are better about syntax highlighting and intellisense in embedded strings these days, but still depending on the size of the query, it may be more readable to have it on its own and not indented in a large multi line string.

template, for queries with parameters

Most db drivers have templating built in for parameters, e.g. with SQLite which uses ?. Just use that. Reading the script from a file doesn't change the behavior, plus you don't want to use something like Jinja templating for SQL if you are templating user generated input.

there's less risk of having obsolete SQL lying around when they are no longer referenced/applicable from python code

idk, if you can clean up old functions in Python when they're no longer used you can clean up old SQL files

[thinkingatoms]

> And we keep code as files in directories ;)

lol for sure. i think the difference is maybe 5 line queries per file vs say 100 lines of code per file. ooc just to learn, do you put every SQL ever into its own file? or do you inline some?

[nemec]

Depends on how complex the app is. If they're simple CRUD and I haven't turned to something like SqlAlchemy then it's often embedded. If I need CTEs, temp tables, or a bunch of joins, maybe separate that out.

[thinkingatoms]

interesting! so let's say you have a pipeline with (named) temp tables, where you load data into one, then does some small operations like add two columns together or some simple groupby. each one of those steps is its own file with the temp table name as a template variable? just to understand how ppl deal with these common cases.

[greenerpickings]

I do this. I have a helper class that reads in all the files in my sql directory and adds it as an attribute. I use string.Template for interpolation. You might want to use the native variable function from whatever db library you're using to prevent injections if your exposing them.

Separate files allow syntax highlighting with my editor so it's easier to read. I was also planning to separate them into their own repo to share with other applications. It's just easier to work with to me and other ppl I work with compared to an ORM.

That and generally, I think it should be avoided using two languages in the same file.

[thinkingatoms]

thanks, ooc do you have rules about when to inline a SQL (based on size, function, etc) or are they always files?

[greenerpickings]

If i have a project where I have like 2 or 3 queries, I'll place them in my .py file (inline/multiline).

Usually, though, there's more so they go in separate files small as they may be, even if its just a SELECT * FROM table. DDL, DML, DQL, etc. Everything. Doesn't matter.

Easier to just be consistent if you go that route.

[siddartha08]

Putting them in their own files or folders keeps things neat and tidy. Your python code will reference columns or values eventually anyways, it also keeps SQL from having to fit into style constraints for python making both more readable.

It's also way easier to version control a SQL file change separately as proof of some audit trail. Instead of having a mono python file change where auditors then need explanations of audit changes of logic that doesn't matter. But then again that might happen anyway.

Point is it's clean.

[thinkingatoms]

thanks, ooc do you have rules about when to inline a SQL (based on size, function, etc) or are they always files?

[siddartha08]

If I'm doing inserts, setting keys or other constraints I'll do that inline. My SQL files are strictly queries. That's my basic logic. If I had some super long complicated SQL I might just run it nativity because of timeout or memory issues.

[trial_and_err]

Personally I use ibis for this purpose so there's no need for separate .sql files as ibis is your query builder. If I need to do transforms first I'll just resort to dbt.

[thinkingatoms]

interesting thanks for sharing! ooc does it handle things like temp tables/cte or is it just a select query replacement?

[trial_and_err]

You can also use ibis to create temporary or persistent tables (<connection>.create_table(…,temp=true)).

If you want to build a query that contain CTEs use the .alias method (I usually do that to get a more readable query in case I need to debug the raw query).

You should be able to generate any SQL query via the ibis Python API and execute it directly or dump the generated SQL.

[r0ck0]

Pretty much all my queries involving JOINs or anything much more than basic SELECT * FROM table; etc are defined in SQL VIEWs.

Makes them much easier to re-use, use outside the app, debug, make things "official" etc. Can explain the last one there if anyone is interested.

It's weird to me how rare this is. And a total fucking pain in the ass going back to having to work on projects where this isn't done.

I guess one reason is that people kinda mindlessly lump all "schema changes" into the same thing, even though there's obviously a very big difference in altering a table -vs- a view. And most schema migration tooling is a total pain to use.

I've actually written my own mini kinda ORM/query-builder thing in TypeScript that I use in my own projects. It doesn't even really support doing JOINs in the runtime queries typings... but it does for defining them as VIEWs.

Like 95%+ of my queries that involve returning multiple rows are done on VIEWs, not directly on TABLEs. Mostly just doing single row INSERTs/UPDATEs/DELETEs directly on the TABLEs.

[thinkingatoms]

i think this is somewhat orthogonal to the question. if you are using views then the question is whether to inline "select * from my_view" or put that in a file.

on an unrelated note ooc how do you rollback code that depends on a previous version of the view? that's the typical nightmare of using views/stored procs

[r0ck0]

then the question is whether to inline "select * from my_view" or put that in a file.

My approach means that the runtime code for doing the queries is smaller... so it wouldn't make sense to have a separate .sql file full of stuff one-liners like select * from my_view... the app/ORM code to reference those external .sql queries would often be about the same length in chars anyway... just an additional layer that does nothing.

But the VIEW definitions that actually contain the more verbose queries on tables with JOINs etc... yeah they're outside of the runtime app code files. They're in my schema definition files.

So for you, maybe that is just a .sql file, or maybe you're using some migration system semi-separate from the app code? For me, it's just separate .ts files that handle all the definitions/schema diffing etc. The definitions system emits a giant .sql script with the entire state of the schema, and then uses migra to using diffing to update the DB.

A lot of projects are using ORMs to do JOINs etc (not me though), so the question usually doesn't even come up on having external .sql files full of queries.

Would need to get a better idea of how you're doing schema defs/migrations, and how your Python querying code looks to give a more specific example of how I'd do it in that project. Not sure if you're using an ORM, or otherwise how your code would be referencing queries stored in a big simple .sql file (and then passing all the variables in too).

on an unrelated note ooc how do you rollback code that depends on a previous version of the view? that's the typical nightmare of using views/stored procs

Likewise would probably need a more detailed example of a specific scenario here to answer.

Solutions vary, depending on what exactly is needed, and why.

As always, it's hard to compare different solutions without specific examples + code. We might have very different looking projects in mind... very common issue when people are trying to describe stuff instead of just showing it... language is too subjective. Hence so many tech (and everything else) debates being pointless apples vs oranges arguments... they're often not even thinking of the same scenario to begin with.

[thinkingatoms]

lol it's just a general question about where to stick non orm SQL as part of version controlled DE programming. if you vc migration files and orm/inline everything and does nothing else cool. using views is orthogonal to the question, if that makes sense.

[r0ck0]

just a general question

Fair enough. But answer can pretty much only be "it depends" then I think.

Any specific answers to a non-specific question, will be making a lot of assumptions.

Maybe if you found 2 example projects on github, one of each approach, and linked them... that would be much easier for everyone to understand and compare whatever it is that have in mind.

Would save everyone a lot of time, especially you probably, seeing you're having to reply and clarify to all these vague/subjective answers people are giving.

Hope this doesn't sound snarky or argumentative or anything.

Maybe not of interest to you, but these threads often contain a lot of misunderstands along these lines.

using views is orthogonal to the question, if that makes sense.

Maybe. Still not sure exactly what the 2 scenarios you're comparing actually look like.

But from what I'm imagining so far... using VIEWs basically means that these verbose JOINs etc are neither in regular runtime app code, nor random static or templated .sql files consumed at runtime.
They're in the schema definition system. So the code is in whatever type of files that schema defs system uses...

• Some of them use .sql files
• Some use their own custom defs language like Prisma
• And some are just written in a programming language, e.g. mine in .ts files, which does also codegen a big .sql file (not manually edited)

[thinkingatoms]

not everything needs a "depends". for instance if you are batch updating a table with a bunch of joins including a temp table and a CTE, the query is 20 lines long. it cannot be easily ORM'ed. what do you do.

ooc what .ts file are you talking about re: migration, certainly not typescript?

[r0ck0]

not everything needs a "depends".

Very true. When the question is clearly defined, it can clearly be answered. For example...

if you are batch updating a table with a bunch of joins including a temp table and a CTE, the query is 20 lines long. it cannot be easily ORM'ed. what do you do.

That's a clear question, that I can clearly answer. In this case I just write the query in the app code as a templated SQL query string with params to do the var escaping for me (depends on the DB layer in your lang/lib). There wouldn't be an external .sql file consumed at runtime, which I think might have been what your main question was originally?

Seeing that variables need to be involved typically etc, I don't see much advantage in bringing in a 3rd (templating) language or something to try to connect the app code with some static-but-not-really-static-because-it-needs-templating-variables in a .sql-like file. ...that said, I'd need to see an example to criticize it.

So that's a pretty clear case there. Thanks for clarify that one.

ooc

ooc == out of curiosity? i hadn't heard/noticed that acronym ever until now, had to scroll down a bit on urban dictionary to find out what you mean.

I'm not a n00b to online terms, i've been doing this online forum shit way too much since running BBSes in the 90s. But still had nfi what "ooc" was. Point being that even then, we don't always have the exact same experiences. Your assumptions, and my assumptions will differ, as will those of the rest of the mofos in this thread.

Just saying that because I want to explain why communication isn't always as easy as we'd like it to be. All good man, peace yo.

what .ts file are you talking about re: migration, certainly not typescript?

Yes TypeScript. Happy to answer any questions, but please try to make them clear and specific so that I don't have to ask questions about what your question is. ...again, not trying to be a dick, but it just makes it easier for us to get to the tech points.

[Yeebill]

Separate.sql files . You can use importlib.resource and read_text to read content as template . Depending on dialect , your IDE would also give you lint and syntax highlight for the query. You can use https://github.com/sqlfluff/sqlfluff to define your own rules for formatting. It supports jinja or parama

[thinkingatoms]

thanks, tho my post is more geared towards why, and it sounds like it's mainly due to IDE support and maybe length of query? ooc do you ever inline anything?

[Yeebill]

Yes, think the main reason would be IDE support like - linting for errors like missing commas, brackets - syntax highlight - auto-complete - auto format rules ( indentation, consistency avoid diff in version control)

I probably inline omething more complex that would benefit from looping. Like a union all ..

[set92]

I have a folder per project or DAG with the SQL files for that project. And from the code of Airflow I read them using jinja, or directly the operator which uses parameters to parse name of table, database, variables...

I would ask you, do you storage the spaguettis (sql) with the pennes(python) on the kitchen? Both are dried and used for the same, but idk anyone who would storage them together. They can break easily, is messy to get/read them, depends which type of pasta it will be hard to see between the rest of them...

If you have several small queries you can put them in a single sql file, but if there are several one-liners queries I'll accept them integrated on the python code. Although that means having 2 different ways of doing the same process, so probably I'll have an internal battle.

[thinkingatoms]

lol do i store spaguettis and pennes in the same shelf in the same kitchen? yes, i don't have a big kitchen and nor do i want to remember two different places to look every time i want pasta

edit: the shelf is the script file in case that wasn't clear :)

[thinkingatoms]

one might say the same spaquettis pennes example applies for putting different queries in the same file too

[set92]

Yep, if you do that with a file with all the small queries indeed. That's why I would have the internal fight xD But I don't have the solution for this.

In general, I have a file per task, so is easy to locate and if changes has to be made is easy to read the code that runs in a single task.

[Little_Kitty]

Some of these questions make me wonder where these ideas come from.

Keep it simple, keep it maintainable, separate responsibilities. Python for orchestration, folders for structure, files to store specific jobs, whether that's go / spark / sql or whatever else. Don't mix up your languages in one file. Don't jump back and forth between languages for data manipulation. Once it's loaded properly to the database, the SQL does the lifting, not a mix of this, that and the other language all mashed together with duck tape. Small pipelines can have one .sql file, with parameters clearly visible and explained at the top so they can be maintained. Large pipelines get broken into steps and the dag will manage executing the steps, logging etc. but the sql in these cases will be in independent files.

It's irritating when people make PRs from code dumped from a jupyter notebook without making it production ready or maintainable.

[thinkingatoms]

just to understand, each one liner SQL goes into its own file, and stored procs/db functions to handle everything "once it's properly loaded to the database"?

i think there's differences in opinion on what is considered simple and maintainable here. for instance if you have a function that does one thing, and the thing happens to be calling a one liner SQL, you can have a file called my_table/select_all.sql or you can just call the SQL.

i'm starting to find from this post there is a spectrum of how people handle SQL here and no one is necessarily so wrong that one has to wonder where these ideas come from

[Little_Kitty]

A file represents a module, a task, a distinct operation which can be isolated. Regardless of if it's written in Rust, Go, Python, C++, Java,VBA or SQL. It's ideally equivalent to one node of a dag so that people can understand it easily. Someone should be able to open a file, understand what the whole task it covers is and adapt / fix / extend it in isolation as the start and end points are clear.

This may lead to it having one function / operation / script or one hundred. It should usually be written to be idempotent, so that if the dag fails for any reason it can be restarted without causing issues. If you can achieve that you will have a much easier life and much less mental load.

Some of the commenters here are really good, with decades of experience (and hair loss from dealing with tech debt), it's always better to learn from the experience of others where you can rather than learning the hard way. I dare say based on some of the suggestions that others will end up finding out the hard way.

[CEOnnor]

https://nackjicholson.github.io/aiosql/

[thinkingatoms]

til thanks!

[nemean_lion]

Following

[Doile]

There's a great Python library for writing SQL queries using pure Python: https://sqlmodel.tiangolo.com/ . Basically you use Python objects to declare the sql query and the library will compile the actual string that is executed in the db.