#import gadfly
#import sqlite
#import odbc
import win32com.client
#from pyPgSQL import PgSQL
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)
        r=self.pp(curs)
        self.database.commit()
        return(r)
    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,directory,db):
        if self.database!=None:
            self.database.close()
        # open the new connection
        self.database =sqlite.connect(directory+"\\"+db)
    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,directory,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):
        rs=self.database.Execute(s)
        #print "execQuery"
        #print rs
        #######to do add error treat
        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)
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=E:\ma\vb\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")