[Tutor] Multi-Line SQL Statement in Python

[Danny Yoo]

On Fri, 19 Nov 2004, Gooch, John wrote:

> I have a rather involved query to run on an MS SQL Server 2000 system. I
> developed the query using the MS SQL Query Analyzer, and it returns 5
> records when I run it from there.

Hi John,

Have you gotten a response for one of the Tutors here yet?  It looks like
most of us may be stumped!


As far as I understand, the execute() method of a Python database
connection can only execute a single command.  I don't believe it has a
provision for executing multiple commands at once.

So you may need to split up your query into three pieces, or restructure
the query so that it's a single SQL statement.  I don't know if MS SQL
supports subselect, but you may be able to restructure the query to
something like:


###
query = """
--Get the latest Drive Space Records
SELECT adh.Legend,ad.DetailShort
FROM
       dbo.ArchiveDataHeader As adh (NOLOCK),
       dbo.ArchiveData As ad (NOLOCK)
WHERE
       adh.MCMachineObjID in
            (
             SELECT
                 ob.objid
             FROM
                 dbo.object as ob
             WHERE
                 ob.name = 'SUPERMAN'
            )
       AND
       ad.DataID=adh.DataID
       AND
       adh.Legend LIKE 'Ldsk: \%AVAIL^^MB'
       AND
       DATEDIFF( day, DATEADD( second, ad.BiasedTime,
                               '19700101' ), getdate() ) < 2
ORDER BY
       DATEADD( second, ad.BiasedTime, '19700101' ) DESC
"""
resultCount = cursor.execute(query)
print resultCount
###


But I could be totally wrong about this.  *grin* Unfortunately, I can't
test this, since I have no access to a MS SQL database. You may want to
double check with the db-sig folks at:

    http://mail.python.org/mailman/listinfo/db-sig


They'd have more competency about the database issues, so you should
probably give them a try.


Good luck to you!