Re: Querying PostgreSQL / PostGIS Databases in Python

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Querying PostgreSQL / PostGIS Databases in Python

Shaozhong SHI
Hi, Laura,

Thank you for your reply.

1.  Please look at this documentation link.

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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Querying PostgreSQL / PostGIS Databases in Python

ruvenml

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.

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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users