[Zope] Call SQLMethods/SQL Statements directly with python

Thomas B. Passin tpassin@mitretek.org
Thu, 4 Apr 2002 12:37:43 -0500


[Philipp Giere]

> Somebody out there who can give me a hint how to call SQL directly
> from python and not with SQLMethods
>

The  Python Database SIG has developed a standard database interface, and it
is supported by many database modules.  On Windows, I use ODBC.  The package
to get is mxODBC.  Whether or not you use ODBC, the interface methods are
the same.

Here's a sketch of how to use the SIG's interface:

import ODBC.Windows # Or whatever driver you use

# Connect to database and get cursor
conn=ODBC.Windows.Connect('nesdis_quest')
cursor=conn.cursor()

# Build a query statement - in this example, an INSERT statement
insertStr='INSERT INTO %s(%s) values(%s)' %\
               (table,cols,values) # calculated elsewhere

# Execute
cursor.execute(insertStr)

# Number of inserted rows is in  cursor.rowcount

# Commit transaction and clean up
cursor.close()
conn.commit()
conn.close()

Not hard at all!

Cheers,

Tom P