MS SQL Server
Kris J. Zaragoza
kzaragoza at mediaone.net
Tue Jun 26 13:22:18 EDT 2001
On Tue, 26 Jun 2001 11:52:51 -0400, Bill Bell
<bill-bell at bill-bell.hamilton.on.ca> wrote:
>Ben <b.e.n. at .r.e.h.a.m.e...c.o.m> wrote, in part:
>> Unfortunately I have to upload data to a MS SQL server ... Can anyone
>> recommend a tool for doing this or docs\examples :] ... is it ANSI
>> SQL compliant like MySQL and therefore can use the same commands?
>Is it possible that the data source you want to process is one of
>those provided for by the "Data Transformation Services (DTS)
>Wizard" that is (or used to be) shipped with SQL Server? If not,
>what is the source of the data you need to process?
And if it's something that you need to automate, you may want to look
at using the bcp.exe command line tool.
Under Windows, you have a number of options for working with SQL
Server. Each has its pros and cons:
* ADO via win32com automation. This is a nice generic interface that
should work across databases, but it's slow as hell for loading lots
* ODBC via odbc or mxODBC. I haven't used the mx package myself, but
i've read good things about it. Either way, I find using Python's DBI
API to be a bit quicker than ADO for moving lots of data around.
* SQL-DMO via win32com automation. This gives you access to all sorts
of SQL Server specific features, including BulkCopy as mentioned in
another reply. However, it does require the SQL-DMO libraries to be
installed on the machine using it.
* bcp.exe via os.system() or one of the spawn/exec variants. This
really becomes more like shell scripting, so it's far from an elegant
solution. However, if all you need to do is munge a little data and
then slurp it all in, it's probably going to be the fastest solution.
If you have lots of data (hundreds of MB and up) to import into a
single table, this should beat any solution involving an INSERT hands
Follow up with more details if you have any problems. I've done lots
of data slinging with SQL Server. :-)
Kris J. Zaragoza | "Unfortunately, most people can't out-think a
kzaragoza at mediaone.net | grapefruit." --Jon Bodner
More information about the Python-list