[DB-SIG] Could someone please write a Python DB Tutorial?

[Dave Cole]

>>>>> "mal" == M -A Lemburg <mal@lemburg.com> writes:

mal> BTW, I'd suggest asking in a forum like this before taking off
mal> into an uncertain direction. The subscribers on this list do have
mal> a lot of experience and may well be able to point you to a
mal> possible solution.

mal> For the Linux -> MS SQL access I would have pointed you to the
mal> OpenLink/Merant ODBC driver kits which should allow you to
mal> connect from Linux to NT. Another possibility would have been to
mal> try to use a Sybase ODBC driver for Linux -- MS SQL Server's wire
mal> protocol is said to be compatible with Sybase's.

They are extremely compatible.  I have been slowly developing a DB API
2.0 Sybase module.  The compliance is pretty good, but still needs
more work.

The biggest area of non conformance is that you can only use SELECT
statements in the Cursor.execute() method.  INSERT, UPDATE, DELETE,
... can only be used in the Connect.execute() method.  After
implementing cursors, I discovered that I was using the wrong set of
Sybase API's.  At some time in the not-to-distant future, I am going
to fix this.

The other area of non-conformance is DATETIME values.  I still have to
integrate mxDateTime...

Anyway, I have actually used it on Linux to talk to an MS SQL database
with good results.  The cursor functionality does not work, but you
can simply use the Connect.execute() method.  I even wrote a small
MSSQL wrapper class which makes it look like cursors work:

The Connect.execute() method returns multiple result sets, whereas the
Cursor.execute() method only returns a single result set.  This makes
Connect.execute() useful for getting the results of stored procedures
such as sp_help which return multiple result sets.

- Dave

import Sybase

class MSSQLCursor:
    def __init__(self, db):
        self.db = db

    def execute(self, cmd):
        result_set = self.db.execute(cmd)
        if len(result_set) > 0:
            self.rows = result_set[0]
        else:
            self.rows = []
        self.row_num = 0

    def fetchone(self):
        if self.row_num >= len(self.rows):
            return None
        row = self.rows[self.row_num]
        self.row_num = self.row_num + 1
        return row

    def fetchmany(self, num = 1):
        if self.row_num + num > len(self.rows):
            num = len(self.rows) - self.row_num
        if num > 0:
            rows = self.rows[self.row_num: self.row_num + num]
            self.row_num = self.row_num + num
        else:
            return []
        return rows

    def fetchall(self):
        return self.fetchmany(len(self.rows))

    def close(self):
        pass

class MSSQL:
    def __init__(self, server, user, passwd, database = None):
        db = Sybase.connect(server, user, database)
        if database:
            db.execute('use %s' % database)
        self.db = db

    def cursor(self):
        return MSSQLCursor(self.db)

    def execute(self, cmd):
        return self.db.execute(cmd)

db = MSSQL('pissey', 'sa', '')
c = db.cursor()
c.execute('select * from sysobjects where type = "P" order by name')
print string.join(map(lambda x: x[0], c.fetchall()), '\n')

-- 
http://www.object-craft.com.au