Re: Querying PostgreSQL / PostGIS Databases in Python - Mailing list pgsql-general

You may write stored procedures using PL/pgSQL,alternatively you may write your queries in python.

You may use psycopg2 to query the DB from Python.

You may have a mix of the two, it will depend on your preference.

Ideally you may not want your users running queries against the data by connecting to the database directly using database tools psql or pgadmin3 or pgadmin4.

This means that having a database access application written in Python to restrict the and encapsulate data access may be advisable.

In this case you may place all the DML statements in python and execute them or you may have much of the data access logic written into several PL/pgSQL functions, then call these functions via 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;

On Fri, Jul 31, 2020 at 12:30 PM Shaozhong SHI <shishaozhong@gmail.com> wrote:

Hi,

What is the advantage of querying in Python?

Has anyone got much experience?

What not just use standard query?

What is the rationale for querying in Python?

Would the performance be better?

Regards,

Shao