isql2.py

isql for acess,sqlite , mysql etc

##import gadfly
import sqlite
import odbc
import win32com.client
import _sqlite
import pywintypes
##import adodbapi
##from pyPgSQL import PgSQL
class MyError(Exception):
    def __init__(self, n,str):
        self.no=n
        self.value = str
##        self.unic=unicode(str,"gb2312")
    def __str__(self):
         return repr(self.value)
class isql:
    def __init__(self):
        self.database=None
    def dbConnect(self,directory,db):
        pass
    def getTables(self):
        pass
    def execQuery(self,str1):
        curs = self.database.cursor()
        curs.execute(str1)
        if str1[:6].lower()=="select":
            r=self.pp(curs)
            self.database.commit()
            return(r)
        else:
            return()
    def pp(self,cursor):
        try:
            rows = cursor.fetchall()
        except:
            return "No description"
        desc = cursor.description
        i=0
        n=len(desc)
        r=[]
        r1=[]
        while i<n:
            r1.append(desc[i][0])
            i=i+1
        r.append(r1)
        i=0
        n=len(desc)
        m=len(rows)
        while i<m:
            j=0
            r1=[]
            while j<n:
                r1.append(rows[i][j])
                j=j+1
            r.append(r1)
            i=i+1
        return r
##class Gisql(isql):
##    def __init__(self):
##        isql.__init__(self)
##    def dbConnect(self,directory,db):
##        if self.database!=None:
##            self.database.close()
##        self.database =self.database =gadfly.gadfly(db,directory)
##    def getTables(self):
##        tables = self.database.table_names()
##	return(tables)
class Sisql(isql):
    def __init__(self):
        isql.__init__(self)
    def dbConnect(self,path):
        if self.database!=None:
            self.database.close()
        # open the new connection
        self.database =sqlite.connect(path)
    def execQuery(self,str1):
        try:
            curs = self.database.cursor()
            curs.execute(str1)
            if str1[:6].lower()=="select":
                r=self.pp(curs)
                self.database.commit()
                return(r)
            else:
                return()
        except _sqlite.DatabaseError,e:
            print e
            print dir(e)
            raise MyError(1,str(e))
    def getTables(self):
        cur=self.database.cursor()
	cur.execute("select tbl_name from sqlite_master where type='table' order by tbl_name")
	tables = []
	for row in cur.fetchall():
	        tables.append(row.tbl_name)
	return(tables)
class Oisql(isql): #odbc
    def __init__(self):
        isql.__init__(self)
    def dbConnect(self,directory,db):
        if self.database!=None:
            self.database.close()
        # open the new connection
        self.database =odbc.odbc(db)
    def getTables(self):
        return(None)
class Aisql(isql): #ado
    def __init__(self):
        isql.__init__(self)
    def dbConnect(self,db):
        if self.database!=None:
            self.database.close()
        # open the new connection
        self.database=win32com.client.Dispatch("ADODB.Connection")
        self.database.Open(db)
    def getTables(self):
        return(None)
    def execQuery(self,s):
        try:
            rs=self.database.Execute(s)
            rs=rs[0]
            r=[]
            n=rs.Fields.Count
            a=[]
            for i in range(n):
                 x=rs.Fields(i).Name
                 a.append(x)
            r.append(a)
            while not rs.EOF:
                n=rs.Fields.Count
                b=[]
                for i in range(n):
                    x=rs.Fields(i).Value
                    b.append(x)
                r.append(b)
                rs.MoveNext()
            #self.database.Close()
            return(r)
        except pywintypes.com_error,e:
            raise MyError(1,e[2][2])

class AcessIsql(Aisql): #adodbapi    
    def dbConnect(self,dbf):
        if self.database!=None:
            self.database.Close()
        # open the new connection
        self.database=win32com.client.Dispatch("ADODB.Connection")
        constr=r"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+dbf+";Persist Security Info=False"
        self.database.Open(constr)
    def getTables(self):
        r=[]
        tbls = self.database.OpenSchema(20)
        tbls.Filter = ' TABLE_TYPE=\'TABLE\' '
        while not tbls.EOF:
            r.append(tbls.Fields('TABLE_Name').Value)
            tbls.MoveNext()
        return(r)

class Adoisql(isql): #adodbapi
    def __init__(self):
        isql.__init__(self)
    def dbConnect(self,str):
        if self.database!=None:
            self.database.close()
        self.database=adodbapi.connect(str)
    def getTables(self):
        return(None)
    def execQuery(self,s):
        c=self.database.cursor()
        c.execute(s)
        r=c.fetchall()
        print r
##class Pisql(isql): #postgres
##    def __init__(self):
##        isql.__init__(self)
##    def dbConnect(self,directory,db):
##        if self.database!=None:
##            self.database.close()
##        # open the new connection
##        self.database =PgSQL.connect(db)  #'127.0.0.1:5432:ncscs:postgres:3333'
##    def getTables(self):
##        return(None)
if __name__ == "__main__":
##    i=Pisql()
##    i.dbConnect(None,'127.0.0.1:5432:ncscs:postgres:3333')
##    print i.execQuery("select * from test")
##    i=Gisql()
##    i.dbConnect(r"E:\ma\python\isql","test")
##    print i.getTables()
##    print i.execQuery("select * from dual")
    i=Sisql()
    i.dbConnect(r"E:\ma\python\isql","trac.db")
    print i.getTables()
    print i.execQuery("select * from ma")
##    i=Oisql()
##    i.dbConnect("","DSN=photo")
##    print i.getTables()
##    print i.execQuery("select * from chunks")
##    constr=r"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=j:\NCS_CS\src\access_2000\data.mdb;Persist Security Info=False"
####    i=Aisql()
####    i.dbConnect("",constr)
####    print i.getTables()
####    print i.execQuery("select * from sample")
##    i=Adoisql()
##    i.dbConnect(constr)
##    print i.getTables()
##    print i.execQuery("select * from sample")