How to install gdal postgresql driver in Ubuntu

Question

I want to use Python to load PostGIS table by ogr. But after I try ogr.open in python (it works if loading a shapefile), I can only get null value.

Now I have tried GDAL debug, the output shows like this: GDAL: In GDALDestroy - unloading GDAL shared library. and I have found that the problem is lack of postgresql driver. So how should I install it in ubuntu?

• Vince: Please edit the question to contain your code. You should also include exact versions of all software in use.
• natsuapo: @Vince Sorry for that, I have added my code.
• Matte: It could be that you are not allowed to connect to your database. Did you check your pg_hba.conf? Otherwise check your db status or log if the connection is open there. The connection should stay open the way you use it in your code until the program is closed and you should have "idle in transaction" in your server status for this connection.
• natsuapo: I can connect through datagrip (a client software) at the same ip address, so I suppose that it is not the configuration problem of server. Thanks anyway.
• natsuapo: By the way, I have found this question: gis.stackexchange.com/questions/13734/… shows that some extra libraries are needed for connecting to PostGIS. But still it is in Windows.

Answer 1

OGR method, courtesy of this page:

from osgeo import ogr
conx = ogr.Open('PG:dbname=my_db user=postgres password=12345678')
sql = 'SELECT * FROM table_name LIMIT 10;'
for row in conx.ExecuteSQL(sql):
 print row.GetField(0) #gets first column, usually the id

Another way: Adapted from here. Doesn't use ogr but it does let you access the data a bit more easily IMHO.

import psycopg2
import sys
conx = None
conx = psycopg2.connect(database='my_db', user='postgres', password='12345678') 
curx = conx.cursor()
#Prints version number
curx.execute('SELECT version()') 
ver = curx.fetchone()
print ver
#Prints first row of table
curx.execute('SELECT * FROM my_db LIMIT 1') 
onerow = curx.fetchone()
print onerow

If you're doing this in qgis, you might want to look at Load a specific spatial data from a postGIS table or Load PostGIS layer/table to QGIS canvas using Python .

• natsuapo: Thank you for your answer but the former one doesn't work for me.
• weiji14: If you post your full database structure, that might help. E.g. dbname, user, password(as ***), host, port.
• natsuapo: It still doesn't work. I think maybe I need some extra libraries to execute it?

Answer 2

I found out the cause is that I use Anaconda, and if I build GDAL by myself, the problem can be solved.

Answer 3

If you are using the anaconda gdal package (installed via conda install gdal , then using psycopg2 is the way to go. According to this anaconda issue/feature request, no anaconda gdal package includes a postgis driver. I checked reinstalling from https://pypi.python.org/pypi/GDAL/ via pip install gdal --no-cache-dir, but that seemed to be an identical package.