[postgis-users] Querying PostgreSQL / PostGIS Databases in Python

[ruvenml at beamerbrooks.com]

Shao,
   There are literally thousands of languages which can be used to 
formulate queries and send them to the PostgreSQL server for 
processing.  You mentioned Python.  How about Java, Javascript, 
Typescript, C, C++, C#, Objective-C, Swift,  Go, Matlab or R to name a 
randomly chosen few?  Interfaces like ODBC/JDBC make it really easy to 
add PostgreSQL support to a programming language or calculation package.

All of these languages are handy for writing things which are difficult 
to express in SQL and all of them offer capabilities which may make it 
easier to get data into and out of the application but all of them take 
about the same amount of time to process a query because queries are 
processed by the PostgreSQL server, not by the programming language. The 
only time PL/<whatever> or one of these other languages helps with 
performance is when the algorithm used has higher performance than what 
is available in SQL.  Often, the opposite turns out to be the case.   In 
other words, writing things in PL/<whatever> may actually slow things down.

What should you do?  Step 1 is to make sure that you are writing 
efficient queries.   You need to have a deep understanding of the 
results of the EXPLAIN command and know how to re-arrange queries to 
control the PostgreSQL processing.

Suppose that no matter how you re-write the query, it's still slow.   
Step 2 is to treat yourself to an online course on analysis of 
algorithms.   Not only will you learn ways of speeding up algorithms but 
you will also learn to recognize when it's impossible to speed up an 
algorithm.

Ruven Brooks

On 7/31/2020 6:59 AM, Shaozhong SHI wrote:
> Hi, Laura,
>
> Thank you for your reply.
>
> 1.  Please look at this documentation link.
> https://www.postgresql.org/docs/9.1/external-pl.html 
> <https://www.postgresql.org/docs/9.1/external-pl.html>
>
> There is also PL/sh language.
>
> So many varieties.  I wonder the use cases for all these.
>
> 2.  My immediate concern is to know which one is good for periodically 
> processing Big Data.
>
> Standard queries in PostgreSQL may fail or keep running for days, 
> weeks when data is very big.
>
> If you do a programme with standard queries of PostgreSQL, I would 
> think that it could run forever.
>
> 3.  Does that mean Python can be treated as a native language in 
> PostgreSQL/PostGIS?  Will it run faster?
>
> Regards,
>
> Shao
>
> On Fri, 31 Jul 2020 at 12:01, Augori <augori at gmail.com 
> <mailto:augori at gmail.com>> wrote:
>
>     Hi Shao,
>     Using Python allows you to integrate a query into a workflow that
>     has some batch component to it. Examples would be a query which
>     allows you to select a set of records that need to be updated
>     daily. Or it could be a query that needs to be run on hundreds of
>     files to build a summary of the data or it could be part of a
>     process that is triggered by some outside event, like a file being
>     updated.  I hope I haven't misinterpreted your question but you
>     seem to be asking why you'd want to automate interactions with a
>     database, a question to which there are thousands of responses.
>     Please clarify if that is not the case.
>
>     Kind regards,
>     Laura
>
>     On Fri, Jul 31, 2020, 5:05 AM Shaozhong SHI
>     <shishaozhong at gmail.com <mailto:shishaozhong at 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?
>
>         Regards,
>
>         Shao
>         _______________________________________________
>         postgis-users mailing list
>         postgis-users at lists.osgeo.org
>         <mailto:postgis-users at lists.osgeo.org>
>         https://lists.osgeo.org/mailman/listinfo/postgis-users
>         <https://lists.osgeo.org/mailman/listinfo/postgis-users>
>
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     https://lists.osgeo.org/mailman/listinfo/postgis-users
>     <https://lists.osgeo.org/mailman/listinfo/postgis-users>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200731/4134291e/attachment.html>