I have an Oracle database that I have been extracting the certain geometry tables into QGIS and then creating shapefile files for those tables.

Does anyone have any tips or tricks to automatic the process with python?

I also need to reproject the data before creating the shapefiles.


There is a Python Oracle library cx_Oracle. You have to download the library and copy it to your Python install to have the modules recognized. Once you, do then you can make connections like and use code like this:

import cx_Oracle
con = cx_Oracle.connect('username/password@hostname:port/DBname')
cur = con.cursor()

Open a file:

f = open(r'path to file, 'w')
con.execute("Your select")

Iterate the results set:

for row in cur:
    f.write("%s\n" % str(row))

For a simple example, to get you started. Then you could use ShapeFile library to take it a step further and write out to a Shapefile. But if I were you, first I'd test just making the connection, write out to a .txt file, get that working and then, I'd use Shapefile to write out to a Shapefile.

Here's an example on how to do that:

import shapefile as shp
w = shp.Writer(filename,shp.POINT)
w.autoBalance = 1 

And you'd use this instead of using the file write out example from above.

| improve this answer | |

I suggest using GDAL and the ogr2ogr command line. That lets you export a database table to a variety of formats, applying some filtering (select the rows and columns to export), renaming (column names - remember that shape files have short names for attributes) and reprojection.

You can then easily automate that via simple shell scripting, or python scripting: use the cx_oracle access to get the list of tables to export and your own logic to map database column names to shape file attribute names, construct the ogr2ogr command and submit them for execution.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.