Re: Querying PostgreSQL / PostGIS Databases in Python

Below is python code illustrating the use of psycopg2. This code has not been run so expect some errors.

Here I am executing an SQL query on a table, you may modify this code to execute a PL/pgSQL function.


import psycopg2;
from psycopg2 import sql;
import psycopg2.extras;
from psycopg2.extensions import AsIs;


db__pg_conn__str="host='host_name_of_computer_hosting_pg_db_service' port=5432 dbname='your_pg_db_name' user='your_username' password='user_password'";
db__pg_conn=psycopg2.connect(db__pg_conn__str);

query_table(
    dataset_name
    ,some_value_2
    ,db__pg_conn
);


def query_table(
    dataset_name
    ,some_value_2
    ,db__pg_conn
):
    """
    """;
    table__id=-1;
    _sql_query1a="""
        SELECT {}::TEXT AS some_string,a.id AS table__id,a.*,clock_timestamp() FROM {}.{} a WHERE a.dataset_name={}::TEXT
        ;
    """;
    sqlSQL1a=None;
    sqlSQL1a=sql.SQL(_sql_query1a);
    pg_cursor1a=db__pg_conn.cursor(cursor_factory=psycopg2.extras.DictCursor);
    _sql_query1a_processed=pg_cursor1a.mogrify(
            sqlSQL1a.format(
                sql.SQL(',').join(map(psycopg2.sql.Placeholder,['some_value_1']))
                ,psycopg2.sql.Identifier("my_schema.my_table".split(".")[0])
                ,psycopg2.sql.Identifier("my_schema.my_table".split(".")[1])
                ,sql.SQL(',').join(map(psycopg2.sql.Placeholder,['some_value_2']))
            )
            ,{
                'some_value_1':'ABC'
                ,'some_value_2':dataset_name
            }
    );
    _sql_query1a_processed=_sql_query1a_processed.decode().replace("\\'","'");
   
    #LOGGER.info(" '{0}', -------------- _sql_query1a_processed is:'{1}'.".format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')[:-1],_sql_query1a_processed));
    pg_cursor1a.execute(
            _sql_query1a_processed
    );
    rowcount1a=pg_cursor1a.rowcount;
    rows=None;
    rows=pg_cursor1a.fetchall();
    row_cnt=0;
    for row in rows:
        pass;
        row_cnt+=1;
        table__id=row["table__id"];//do something with table__id
    //do something with rows.
    rows=None;
    db__pg_conn.commit();
    sqlSQL1a=None;
    pg_cursor1a=None;