Records: Python library for making raw SQL queries to Postgres databases
I have something similar, that I think &quot;goes further&quot; than this, which I use as part of my Python Cydia backends. I got extremely addicted to using the moral equivalent of TCL&#x27;s uplevel, inspect.currentframe(-...), to build interfaces in Python where I don&#x27;t have to pull out parameters. My &quot;cyql&quot; interface (which is barely anything, really: just a thin wrapper for psycopg2, which is itself excellent; in Clojure I wrap a slightly modified copy of the PostgreSQL JDBC driver to provide an even better implementation that does &quot;compile&quot;-time type SQL statement verification), uses this to allow me to remove what feels like <i>all</i> of the boilerplate.<p><a href="http:&#x2F;&#x2F;gitweb.saurik.com&#x2F;cyql.git" rel="nofollow">http:&#x2F;&#x2F;gitweb.saurik.com&#x2F;cyql.git</a><p>In addition to .run (which returns the number of affected rows) and .all (which returns an array of ordered dicts?), I have .has (which returns a bool and wraps an exists query), .gen (which returns a generator and iterates a cursor), and .one (which verifies you only got back a single row and returns just that row). I also have easy support for transactions (either on an existing connection or in one line as part of making a connection), easily turning off synchronous commit (for log tables), and I carefully manage autocommit to make certain that I am using the minimum number of possible SQL statements to the server (which I care about a lot).<p><a href="http:&#x2F;&#x2F;gitweb.saurik.com&#x2F;cyql.git&#x2F;blob&#x2F;HEAD:&#x2F;__init__.py" rel="nofollow">http:&#x2F;&#x2F;gitweb.saurik.com&#x2F;cyql.git&#x2F;blob&#x2F;HEAD:&#x2F;__init__.py</a><p>That said, I agree with the post by masklinn below: neither the linked library nor my library are solving problems that I believe are worth inheriting code from someone else over. There is some basic configuration of psycopg2 which is necessary, but the underlying library itself is what works here. I have spent over a decade thinking about how I like to build SQL interfaces, and have now implemented a similar interface for myself in numerous languages, each time evolving the design slightly (and sometimes having enough of an epiphany that I go back and retrofit some of the older ones), but it ends up being built around the way I think about stuff.<p><a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=11053877" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=11053877</a><p>And that also means that as I learn more and &quot;level up&quot;, I start making different decisions. My implementation in Clojure stresses stored procedures a lot more, as while it took me a long time to really figure out how to use them in my workflow, I now see them as exceedingly correct and feel a lot of the code I&#x27;ve written in the past where I had tons of free statements is essentially &quot;what I wrote from back when I didn&#x27;t know how to use the database to organize my API layer&quot; (though I still haven&#x27;t worked out some of the tooling around shifting to stored procedures, and have been distracted with other higher-level problems the last couple years).<p>Essentially, I&#x27;m arguing that the same will happen to you. Put differently: some problems are hard, and some problems are easy; I find a lot of libraries that seem to be solving easy problems that new developers think are hard, and a lot of libraries that pretend to solve a hard problem, but only because the problem looked easy and the result doesn&#x27;t actually work (such as the PostgreSQL drivers that were available in Ruby for a long time, which were all unusably bad). Wrapping something that works well so it is slightly easier for you to use can be valuable if it is upstreamed into the original project, but even then is likely to be something you will paper over yourself in time as you will think about the problem differently than they did.<p><pre><code>    # at the top of the code somewhere
    dsn = {&#x27;port&#x27;: ..., &#x27;user&#x27;: &#x27;...&#x27;, &#x27;password&#x27;: &#x27;...&#x27;, &#x27;database&#x27;: &#x27;...&#x27;}
    
    with cyql.connect(dsn) as sql:
        provider, account, key = sql.one(&#x27;&#x27;&#x27;
            select
                &quot;payment&quot;.&quot;provider&quot;,
                &quot;payment&quot;.&quot;account&quot;,
                &quot;payment&quot;.&quot;transaction&quot;
            from &quot;cydia&quot;.&quot;payment&quot;
            where
                &quot;payment&quot;.&quot;id&quot; = %(payment_id)s
        &#x27;&#x27;&#x27;)
    
    with cyql.connect(dsn) as sql:
        sql.run(&#x27;&#x27;&#x27;
            update &quot;cydia&quot;.&quot;token&quot; set
                &quot;token&quot; = %(token)s,
                &quot;email&quot; = %(email)s,
                &quot;country&quot; = %(country)s,
                &quot;shipping&quot; = %(shipping)s,
                &quot;billing&quot; = %(billing)s,
                &quot;data&quot; = %(data)s
            where
                &quot;id&quot; = %(token_id)s and
                &quot;token&quot; is null
        &#x27;&#x27;&#x27;)</code></pre>