Python! SQL With File Geodatabase...

Home

GIS Data & Resources

Scripts and Code

Python

Python! SQL With File Geodatabase...

Written by Kevin Bell,

Have you ever wanted to simply plug a SQL statement into a GIS tool to get the results into your GIS, but the SQL that you need to use is 400 lines, and the existing tools in ArcGIS don't really accept standard SQL in a plug n chug manner?

Here's some code that should get you started. With this code I start out by deleting all of the features out of a fgdb, then reloading the data.

pyodbc returns records as a list, so you index the list items to feed your insert cursor.

import pyodbc
import arcgisscripting
gp = arcgisscripting.create(9.3)

tbl = r"P:\Accella.gdb\permits"
gp.MakeTableView_management (tbl, "tbl_view")
gp.DeleteRows_management("tbl_view")
print "cleared out the table"

#-------------------------------------

def insertRow(inrow, gp):
    incur = gp.InsertCursor(tbl)
    incur.Reset()
    row = incur.NewRow()

    if inrow[0] == None:
        row.PermitNumber = ""
    else:
        row.PermitNumber = inrow[0]

    if inrow[1] == None:
        row.B1_PER_GROUP = ""
    else:
        row.B1_PER_GROUP = inrow[1]


    incur.InsertRow(row)
    print "inserted row " + inrow[0]

#--------------------------------------

con = pyodbc.connect('DRIVER={SQL Server};SERVER=XXXX;DATABASE=XXXX;UID=XXXX;PWD=XXXX')
cur = con.cursor()

sql = '''SELECT...''' # --- REALLY LONG SQL GOES HERE, TRIPLE QUOTES LET YOU WRAP LINES...

rows = cur.execute(sql).fetchall()

print len(rows)
cur.close()

for r in rows:
    insertRow(r, gp)

print " done"

Users' Comments

No comment posted

Add your comment

06, May. 2009

Last Updated ( 06, May. 2009 )

< Prev		Next >

[ Back ]

Main Menu

Registered User Menu

User Login