HOW TO

Connect and run queries to a SQL Server database from Python

Last Published: April 25, 2020

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.
  1. 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:
  2. Define a parameter to access the cursor method:
    cur = con.cursor()
  3. Create a query string:
    querystring = "select * into ParcelsA from ParcelsB"
  4. Pass the query string into the cursor method:
    cur.execute(querystring)
    con.commit()

Article ID:000011656

Software:
  • ArcMap
  • Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Related Information

Discover more on this topic