3 ms·

Which doesn't work for table names, or for building up where clauses gradually, etc. SQLAlchemy makes those much more trivial to deal w/ the SQL Expression bits

Which doesn't work for table names, or for building up where clauses gradually, etc. SQLAlchemy makes those much more trivial to deal w/ the SQL Expression bits. (Though technically, that's not an ORM.) Also, getting the library to properly expose the formatting interface is also a trick. We would do massive loads/dumps that we did not want to use Python for (but we worked in Python) as the DB-API pulls the entire result set into RAM, and we were pulling >RAM (and we didn't want to do something lower-level w/ the driver). We'd spawn psql, but that needs the query, as a complete, pre-templated string — it does not support any sort of positional parameters. And the Python library really doesn't (last I checked) have a public interface to the templater portion of the library. (I think this is b/c PG just sends the parameters, and the binding is done server-side. So, really, psql needs an interface to supply those values as arguments to the CLI.)

SmirkingRevenge 7y ago
> Which doesn't work for table names, or for building up where clauses gradually, etc. SQLAlchemy makes those much more trivial to deal w/ the SQL Expression bits. (Though technically, that's not an ORM.) Thin SQL wrappers/dsl's like the SQLAlchemy expression library are great. IMO those thin wrappers are what most people should reach for first, over a full blown ORM. A good mimimal sql wrapper will: * Save people from pain, problems, and security issues involved in building queries through string manipulation. * Map very closely to raw SQL * Make it easy to compose query expressions and queries, using language native features. * Help devs develop their SQL skills. Learning the wrapper IS learning SQL, for the most part. I have a hard time seeing many good reasons to add any more layers of abstraction on top. That last point is particularly important to me. My first exposure to many advanced SQL techniques, was through such libraries. Since the code maps to sql quite naturally, that learning can be applied in a much wider variety of contexts. Teach someone how to do aggregations using Django's ORM, and you've taught them to do aggregations in Django's ORM, and basically nowhere else.
yellowapple 7y ago
> We'd spawn psql, but that needs the query, as a complete, pre-templated string — it does not support any sort of positional parameters. [...] So, really, psql needs an interface to supply those values as arguments to the CLI. I haven't tried this before, so caveat emptor, but per psql's manpage this should be supported: QUERY="SELECT * FROM sometable WHERE name = :'foo'" echo $QUERY | psql -v foo=asdf # ... other args ... Basically: you can use colon-prefixed variables in the query text (with the variable name optionally quoted to indicate how the result should be quoted, apparently?), and set them with the -v option (which is equivalent to using the \set command within the query string itself). This also happens to work for table names, at least per the example in the manpage: testdb=> \set foo 'my_table' testdb=> SELECT * FROM :"foo"; Except in this case it'd be more echo 'SELECT * FROM :"foo";' | psql -v foo=my_table # ... other args ... Still no option there for positional parameters, but it's a start, right?

SmirkingRevenge 7y ago

> Which doesn't work for table names, or for building up where clauses gradually, etc. SQLAlchemy makes those much more trivial to deal w/ the SQL Expression bits. (Though technically, that's not an ORM.) Thin SQL wrappers/dsl's like the SQLAlchemy expression library are great. IMO those thin wrappers are what most people should reach for first, over a full blown ORM. A good mimimal sql wrapper will: * Save people from pain, problems, and security issues involved in building queries through string manipulation. * Map very closely to raw SQL * Make it easy to compose query expressions and queries, using language native features. * Help devs develop their SQL skills. Learning the wrapper IS learning SQL, for the most part. I have a hard time seeing many good reasons to add any more layers of abstraction on top. That last point is particularly important to me. My first exposure to many advanced SQL techniques, was through such libraries. Since the code maps to sql quite naturally, that learning can be applied in a much wider variety of contexts. Teach someone how to do aggregations using Django's ORM, and you've taught them to do aggregations in Django's ORM, and basically nowhere else.

yellowapple 7y ago

> We'd spawn psql, but that needs the query, as a complete, pre-templated string — it does not support any sort of positional parameters. [...] So, really, psql needs an interface to supply those values as arguments to the CLI. I haven't tried this before, so caveat emptor, but per psql's manpage this should be supported: QUERY="SELECT * FROM sometable WHERE name = :'foo'" echo $QUERY | psql -v foo=asdf # ... other args ... Basically: you can use colon-prefixed variables in the query text (with the variable name optionally quoted to indicate how the result should be quoted, apparently?), and set them with the -v option (which is equivalent to using the \set command within the query string itself). This also happens to work for table names, at least per the example in the manpage: testdb=> \set foo 'my_table' testdb=> SELECT * FROM :"foo"; Except in this case it'd be more echo 'SELECT * FROM :"foo";' | psql -v foo=my_table # ... other args ... Still no option there for positional parameters, but it's a start, right?