HOW TO

Connect and run SQL queries to a PostgreSQL database from Python

Last Published: April 25, 2020

Summary

Connecting to PostgreSQL databases from Python provides the ability to run essential SQL queries that can be utilized in geoprocessing tasks.

Procedure

Instructions provided describe how to connect to a PostgreSQL database and run SQL queries from a Python script.

  1. Download and install the appropriate psycopg2 module for the installed Python release (for example, Python 2.6, 2.7, etc.):

    http://www.stickpeople.com/projects/python/win-psycopg/

  2. Import the module in the Python script:

    import psycopg2

  3. Make a connection to a PostgreSQL database by passing in the appropriate user/password to the following connection string:

    connection = psycopg2.connect(host='prod', database='sde', user='sde', password='sde')

  4. Define a parameter to access the cursor method:

    cursor = connection.cursor()

  5. Create a query string and pass to cursor method:

    cursor.execute('select * from PARCELS WHERE OBJECTID < 70000')

  6. Create a for loop and print results

    for query in cursor:
    print str(query)

Article ID:000011670

Software:
  • ArcMap
  • Legacy Products

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options