编程示例

下面是python跨模块函数调用方式，给出完整示例代码，请参考使用：

文件一：EMP.py

#!/usr/bin/env python
import pypyodbc

class stemp:
# init class
    def __init__(self, name, conn, cur):
        self.name = name
        self.conn = conn
        self.cur = cur

    # create a table
    def createTable(self, name):
        try:
                self.cur.execute('create table ' + name + '(id int, name varchar(20), sex int, age int, info varchar(50))')
                self.conn.commit()
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # insert single record
    def insert(self, name, value):
        try:
            self.cur.execute('insert into '+ name + ' values(?,?,?,?,?)',value)
            self.conn.commit()
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # insert more records
    def insertMore(self, name, values):
        try:
            self.cur.executemany('insert into ' + name + ' values(?,?,?,?,?)', values)
            self.conn.commit()
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # update single record from table
    # name: table name
    # values: waiting to update data
    def updateSingle(self, name, value):
        try:
            self.cur.execute('update ' + name + ' set name=?, sex=?, age=?, info=? where id=?;', value)
            self.conn.commit()
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # update some record from table
    def update(self, name, values):
        try:
            self.cur.executemany('update ' + name + ' set name=?, sex=?, age=?, info=? where id=?;', values)
            self.conn.commit()
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # get record count from db table
    def getCount(self, name):
        try:
            count = self.cur.execute('select * from ' + name)
            return count
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # select first record from database
    def selectFirst(self, name):
        try:
            self.cur.execute('select * from ' + name + ';')
            result = self.cur.fetchone()
            return result
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # select last record from database
    def selectLast(self, name):
        try:
            self.cur.execute('SELECT * FROM ' + name + ' ORDER BY id DESC;')
            result = self.cur.fetchone()
            return result
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # select next n records from database
    def selectNRecord(self, name, n):
        try:
            self.cur.execute('select * from ' + name + ';')
            results = self.cur.fetchmany(n)
            return results
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # select all records
    def selectAll(self, name):
        try:
            self.cur.execute('select * from ' + name + ';')
            results = self.cur.fetchall()
            return results
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # delete a record
    def deleteByID(self,name,id):
        try:
            self.cur.execute('delete from ' + name + ' where id=?;', id)
            self.conn.commit()
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    # delete some record
    def deleteSome(self, name):
            self.cur.execute('delete from ' + name + ';')
            self.conn.commit()

    # drop the table
    def dropTable(self, name):
        try:
            self.cur.execute('drop table ' + name + ';')
            self.conn.commit()
        except Exception,e:
            print "oscar Error %d: %s" % (e.args[0], e.args[1])

    def __del__(self):
        if self.cur != None:
            self.cur.close()
        if self.conn != None:
            self.conn.close()

文件二：testEMP.py

#!/usr/bin/env python

import pypyodbc
from EMP import stemp


def main():
    conn=pypyodbc.connect('DRIVER={OSCAR ODBC DRIVER};SERVER=localhost;UID=sysdba;PWD=szoscar55;DATABASE=test;Port=2004;charset=utf-8')
    print 'connect ok......'
    cur = conn.cursor()
    # ------------------------------------------- create -----------------------------------------------------
    emp = stemp('emptable',conn,cur)
    emp.createTable('emptable')

    # ------------------------------------------- insert -----------------------------------------------------
    #emp.insert('emptable',[3,'ppp',0,2000,'fffffffffddf'])
    emp.insertMore('emptable',[(1,'ppp',0,2000,'fffffffffddf'),(2,'ffp',0,5000,'fffffffffddf'),(3,'ffp',0,455,'fghj')])
    # ------------------------------------------- select -----------------------------------------------------
    print '-' * 60
    print 'first record'
    result = emp.selectFirst('emptable')
    print result

    print '-' * 60
    print 'last record'
    result = emp.selectLast('emptable')
    print result

    print '-' * 60
    print 'more record'
    results = emp.selectNRecord('emptable', 3)
    for item in results:
        print item

    print '-' * 60
    print 'all record'
    results = emp.selectAll('emptable')
    for item in results:
        print item

    # ------------------------------------------- update -----------------------------------------------------
    emp.updateSingle('emptable', ['Zeus', 1, 22000, 'The god.', 2])

    values = []
    values.append(['SunWukong', 1, 1300, 'The hero who in fairy tale.', 1])
    values.append(['Zeus', 1, 50000, 'The king who in The Quartet myth.', 2])
    values.append(['Prophet', 1, 20000, 'The hero who in fairy tale.3', 3])
    emp.update('emptable', values)

    # ------------------------------------------- delete -----------------------------------------------------
    emp.deleteSome('emptable')

    emp.dropTable('emptable')


if __name__ == '__main__':
main()

运行结果：

[root@localhost pypyodbc-1.3.3]# python testEMP.py
 connect ok......
 ------------------------------------------------------------
 first record
 (1, 'ppp', 0, 2000, 'fffffffffddf')
 ------------------------------------------------------------
 last record
 (3, 'ffp', 0, 455, 'fghj')
 ------------------------------------------------------------
 more record
 (1, 'ppp', 0, 2000, 'fffffffffddf')
 (2, 'ffp', 0, 5000, 'fffffffffddf')
 (3, 'ffp', 0, 455, 'fghj')
 ------------------------------------------------------------
 all record
 (1, 'ppp', 0, 2000, 'fffffffffddf')
 (2, 'ffp', 0, 5000, 'fffffffffddf')
 (3, 'ffp', 0, 455, 'fghj')