Using Python and MS-SQL Server

[Tim Golden]

hwcowan at hotmail.com wrote:
> I have programmed before, but I am new to using Python.  I am
> currently using the ArcGIS software which uses Python as its scripting
> language for automating tasks.
> 
> The current script that I am working on requires pulling in some
> information from a Microsoft SQL Server.
> 
> I was wondering if anyone could suggest the best way of doing this?  I
> have looked at the different modules that are specific to SQL server,
> but none of them seem to be active or up to date.
> 
> If not, could anyone make any suggestions?  Or would it be better to
> go the ODBC route?  I am not talking about large amounts of data, so I
> am not concerned about performance so ODBC would be fine to use as
> well.

Have a look at this:

http://ramblings.timgolden.me.uk/2007/09/26/using-mssql-from-within-python-25/

It's hardly comprehensive, but it more-or-less answers
your question.

> Also, being new to Python, I recently read about dictionaries and was
> wondering if there was a quick way to dump a table into a dictionary?
> 
> For example, I have a customer list with a customer ID.  It would be
> great to have the ID as the "key" and the name as the "data" (or even
> better, have a list as the data element containing all the information
> about the customer).
> 
> I am sure that this could be done manually, by looping through each
> record and adding it to the dictionary -- but I was just wondering if
> something like this has already been done (I don't need to reinvent
> the wheel here).

The key phrase you're looking for here is ORM (Object-Relational
Mapper). Again, not an exact match for what you're saying, but
unless you app remains *remarkably* simple, you're going to end
up reinventing an ORM anyway. Probably the front runner these
days is sqlalchemy (which certainly supports MS-SQL):

http://sqlalchemy.org

but just Google for "python orm" for any number of discussions
and comparisons.

TJG