Using Python and MS-SQL Server

[hwcowan]

Hello,

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.

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).

Thanks so much,

Hugh

 

[Tim Golden]

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

 

[Ed Leafe]

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.

Dabo may be what you need. It is a 3-tier framework for developing
desktop applications, so even if you are doing web apps, the data
access layer is fully usable by itself. We support MS SQL Server, as
well as several other database backends.

-- Ed Leafe
-- http://dabodev.com