I haven't used PugSQL yet, but I like the principle because it allows to keep SQL and Python code in separate files, which makes it easier to use linters or even just proper syntax highlighters for the SQL compared to strings embedded into python or other programming languages.

First, good linters and syntax highlighters are supposed to work in embedded code. It works for HTML, JS, CSS. It works for docstring in Python, regex or f-strings. But the tooling state of SQL is quite terrible.

Secondly, you can do that with Path.read_text() and pass it to SQLalchemy-core if a query is so complex you want to (although you would lose many other architectural benefits by doing so, it is sometime necessary).

But writing your entire program like that seems very slow, SQL being verbose, and you having to write additional wrappers on top of it anyway.

> First, good linters and syntax highlighters are supposed to work in embedded code. It works for HTML, JS, CSS. It works for docstring in Python, regex or f-strings. But the tooling state of SQL is quite terrible.

As you write, in practice it sucks for SQL. Formatting is another issue, can become quite awkward in embedded code

> Secondly, you can do that with Path.read_text() and pass it to SQLalchemy-core if a query is so complex you want to

Yes, if you use one file per query. Otherwise you'd need something similar to the naming-scheme of PugSQL, just implemented by your self.

> But writing your entire program like that seems very slow, SQL being verbose, and you having to write additional wrappers on top of it anyway.

This depends entirely on your program and/or problem domain. It might be overkill for simple CRUD applications with a few INSERT/UPDATE/SELECT queries, but might make a lot of sense if you use more advanced SQL features.

One clear advantage of the yesql/hugsql style is that you get to manage your SQL files in a consistent way. You're going to have migration files and stored procedure/view/etc definitions in .sql files too. This lets you export your queries as Clojure/Python functions simply but still have them live next to the rest of the SQL, for easy refactoring etc.

Looks like sqlalchemy is just used for the "being able to handle multiple databases" part, not for its higher level abstractions.

You may of course already be aware of this but it's worth pointing out. To many, SQLAlchemy is an ORM, but the SA developers very purposefully made the ORM layer completely optional. The SQLAlchamy-Core layer can be used to functionally compose queries without mapping types at all, and below that, SA can be used as an abstract but featureful generic client for executing just raw SQL.

Thank you (and sibling for the example), I was not aware of that. I did check SA out, but missed this somehow, so I'm using psycopg2 directly now... But if I gain portability to other DBs for low effort, then I'm all for it. Thanks!

FYI, you can also execute queries directly against a database in SQLAlchemy:

    engine = create_engine('mysql://scott:tiger@localhost/test')
    connection = engine.connect()
    result = connection.execute("select username from users")
    for row in result:
        print("username:", row['username'])
    connection.close()

SQLAlchemy Core is powerful but hardly complete; every real-world application I've written with it has ended up needing substantial hand-written SQL to cover the gaps. So I see a clear use case on top of SQLAlchemy to organize those queries.