6

Is it possible to create an OLE DB connection (*.odc) to a database in ArcGIS 10 , but using Python instead of the ArcCatalog GUI wizard? I don't need to necessarily create a file on disk, just make the connection to the database. I am trying to automate a procedure that many users may use, and do not want to rely on every user remembering to create the OLE DB connection manually through ArcCatalog.

The Python code provided by @blord-castillo in a similar question shows How to create an SDE Connection using Python, so I am looking for something similar that creates an OLE DB connection. I cannot seem to find any reference to any Python code for this task.

Eventually, the python code sample using Make Query Table is basically what I want to do, but it assumes you already have your *.odc connection built.

Anyone have suggestions or pointers for places to look for sample code? I am working against a SQL Server database, in case that matters.

Improve this question
  • This isn't specific to GIS or ArcGIS. Look at the package win32com.client in Python. Do you really mean .odc? That's an Office Data Connection. Maybe ODBC? Also see wiki.python.org/moin/SQL%20Server – Sean Jul 17 '12 at 0:16
  • 1
    I understand what you are talking about, and do not want a generic ODBC connection. I am looking to create an ArcGIS specific OLE DB connection for ArcCatalog, which does create an *.odc connection string. See the ArcGIS help on Adding OLE DB connections through ArcCatalog. I just want to do it via Python, not the GUI form as seen in help. – RyanKDalton Jul 17 '12 at 4:35
  • Did you get success to OLE DB database connection using ArcPY? I need to get it connected via ArcPY and automate the query process as well. FYI: I have ArcGIS 10.0. Would apprecaite your reply. Thanks – Kuma Apr 7 '14 at 23:08
  • This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. You can also add a bounty to draw more attention to this question once you have enough reputation. – Fezter♦ Apr 8 '14 at 0:13
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. – Paul Apr 8 '14 at 5:53
4
+50

Can you use arcpy 10.1 yet? OLEDB connections are created as .sde files at 10.1 using a new function: http://resourcesbeta.arcgis.com/en/help/main/10.1/index.html#/Create_Database_Connection/00170000016q000000/

arcpy.CreateDatabaseCOnnection_management("C:\\MyDirectory", "test.sde", "SQL_SERVER", "servername", "DATABASE_AUTH", "MyUserId", "MyPassword", "SAVE_USERNAME", "databasename")
Improve this answer
  • I do not have 10.1 but that is great news!!! – RyanKDalton Jul 20 '12 at 16:21
  • 1
    I have 10.1 and I have tried this. It turns out that Query Tables and Query Layers can't be built from an sde file connection (which CreateDatabaseConnection_management creates). It's the wrong workspace type. It must be an OLE DB connection which Esri doesn't expose with Python. It can be done from ArcCatalog 10.1 though. You have to search the customize mode commands for "OLE DB", add it to your menus, and then you can create a new OLE DB connection with the User Interface. This isn't available through ArcDesktop though. Once the connection is there, then Python can use it. – cndnflyr Jul 11 '14 at 18:55
3

I don't think the ability to create ArcGIS OLE DB connection files is exposed in arcpy. You'll need to either create the .odc files beforehand, use ArcObjects in Python to create them, use ODBC, e.g. through pyodbc, or use one of these other APIs, to get at the data you need.

Improve this answer
  • I fear you are correct, but I was hoping that wouldn't be the case. When I looked in arcpy for something similar to arcpy.CreateArcSDEConnectionFile_management() but for OLEDB connections, I didn't find anything. – RyanKDalton Jul 18 '12 at 14:50
  • Quick warning using pyodbc though is that because of the way ArcMap handles connection pooling, if you try using pyodbc to create the connection from within ArcMap (or Catalog) itself it will work for the first execution and then crash ArcMap on the second. – om_henners Jul 24 '12 at 1:26
1

This is kind of a late response. Have you tried to create the .odc connection in ArcCatalog then then copy the connection over to a "standard" location on the network and call that connection using Python? For example, we have a standard location on the network specifically intended for database connections. This includes .sde and .odc connections. We make sure that the folder includes all of the connections that we need to the various databases. Then I just call those connections from python when setting parameters. Hopefully this helps.

Thanks! Eugene Durshpek

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.