Connect and run queries to a SQL Server database from Python

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:
   https://github.com/mkleehammer/pyodbc​

Note:
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):

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

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

   • Database authentication string:
   • Windows authentication string:
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()