Knowledge Base - Technical Articles


Technical Article   HowTo:  Connect and run queries to a SQL Server database from Python

Article ID: 40599
Software:  ArcSDE 10, 10.1 ArcGIS - ArcEditor 10 ArcGIS - ArcInfo 10 ArcGIS - ArcView 10 ArcGIS for Desktop Advanced 10.1 ArcGIS for Desktop Standard 10.1 ArcGIS for Desktop Basic 10.1
Platforms:  Windows Server 2008, Windows 7 RHEL 5, 6

Summary

Connecting to SQL Server and making SQL queries can be incorporated into Python to aid common GIS geoprocessing tasks.

Procedure

The procedures below describe how to connect to a SQL Server database and make SQL queries from Python.

  1. Download and install the appropriate pyodbc module for the installed Python release (for example, Python 2.6, 2.7) from the following URL:

    http://code.google.com/p/pyodbc/



     Since ArcGIS Desktop by default installs the 32-bit version of Python, install the 32-bit version of pyodbc.

  2. Make a connection to the SQL Server database using database authentication or Windows authentication by passing in the appropriate parameters such as the server name, user ID (UID) and password (PWD):

    Database authentication string:

    con = pyodbc.connect('DRIVER={SQL Server};SERVER=Prod1\SQL2008R2;DATABASE=SDE;UID=sa;PWD=sa')


    Windows authentication string:

    con = pyodbc.connect('Trusted_Connection=yes', driver = '{SQL Server}',server = ‘Prod1\SQL2008R2 ‘, database = ‘SDE')

  3. Define a parameter to access the cursor method:

    cur = con.cursor()

  4. Create a query string:

    querystring = "select * into ParcelsA from ParcelsB"

  5. Pass the query string into the cursor method:

    cur.execute(querystring)
    
    con.commit()


Related Information


Created: 10/5/2012
Last Modified: 7/12/2013

Article Rating: (3)
If you would like to post a comment, please login

Comments

By williamskg6 - 10/22/2013 3:11 PM

The article is incorrect or the solution didn’t work.

Just like jmzambrana, execution #1 on ArcGIS Server 10.1 works, but the second time fails and crashes so badly that even a try/except/finally can't catch the crash. This behavior occurs every time I attempt to execute a GP service that runs a script with pyodbc. (First time works, second time crashes every time.) The only thing I can think of is to try un-checking the "Run Python script in process" check box in the script's toolbox properties dialog but that would make it run very slowly. It's very frustrating for ESRI to suggest using a module that is known to not play well with ESRI software.

Rating:

By jmzambrana - 05/31/2013 2:36 PM

Other - See details below.

I'm using pyodbc 3.0.6 32-bit version, for python 2.6 and I have a script that the first time works fine, but the second time ArcGIS stop working an close. Is there any explanation for this behavior? I read the following thread: http://forums.arcgis.com/threads/6952-Problem-using-PYODBC-in-ArcGIS-script But don't have an answer for this problem. Is there a document or article to check this behavior of pyodbc??

Rating:

By sanchel - 02/21/2013 9:27 AM

Other - See details below.

When I tried to install 64-bit pyodbc 3.0.6 for python 2.6 in a 64=bit machine it shows a message saying: "Python version 2.6 required, which was not found in the registyr" I have pythong 2.6 in my machine. Later I tried the 32-bit version of pyodbc 3.0.6 for python 2.6 in the same 64-bit machine and worked fine. Thank you, Luis Sanchez sanchelf@oge.com

Rating: