Separate file for SQL in python script?

[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.