Execute SQL on remote Postgres server.

pgcall.py

#!/usr/bin/env python
import argparse
import urllib.parse
import textwrap

import psycopg2
import psycopg2.extras


def main():
    parser = argparse.ArgumentParser(
        description=textwrap.dedent("""
        Execute SQL on remote Postgres server.

        Example:
            pgcall postgres://user:pass@host:5432/mydb -c 'SELECT version()'
        """),
        formatter_class=argparse.RawDescriptionHelpFormatter,
    )

    parser.add_argument('db_url', nargs=1)
    parser.add_argument('-c', '--sql', required=True)
    args = parser.parse_args()

    db_url = urllib.parse.urlparse(args.db_url[0])

    with psycopg2.connect(
        dbname=db_url.path[1:],
        user=db_url.username,
        password=db_url.password,
        host=db_url.hostname,
        port=db_url.port,
    ) as conn:
        with conn.cursor(
            cursor_factory=psycopg2.extras.DictCursor
        ) as cur:
            cur.execute(args.sql)
            for row in cur:
                print(dict(row))


if __name__ == "__main__":
    main()