attachment:isql2.py of MicroProj/2005-07-06

   1 #import gadfly
   2 #import sqlite
   3 #import odbc
   4 import win32com.client
   5 #from pyPgSQL import PgSQL
   6 class isql:
   7     def __init__(self):
   8         self.database=None
   9     def dbConnect(self,directory,db):
  10         pass
  11     def getTables(self):
  12         pass
  13     def execQuery(self,str1):
  14         curs = self.database.cursor()
  15         curs.execute(str1)
  16         r=self.pp(curs)
  17         self.database.commit()
  18         return(r)
  19     def pp(self,cursor):
  20         try:
  21             rows = cursor.fetchall()
  22         except:
  23             return "No description"
  24         desc = cursor.description
  25         i=0
  26         n=len(desc)
  27         r=[]
  28         r1=[]
  29         while i<n:
  30             r1.append(desc[i][0])
  31             i=i+1
  32         r.append(r1)
  33         i=0
  34         n=len(desc)
  35         m=len(rows)
  36         while i<m:
  37             j=0
  38             r1=[]
  39             while j<n:
  40                 r1.append(rows[i][j])
  41                 j=j+1
  42             r.append(r1)
  43             i=i+1
  44         return r
  45 class Gisql(isql):
  46     def __init__(self):
  47         isql.__init__(self)
  48     def dbConnect(self,directory,db):
  49         if self.database!=None:
  50             self.database.close()
  51         self.database =self.database =gadfly.gadfly(db,directory)
  52     def getTables(self):
  53         tables = self.database.table_names()
  54 	return(tables)
  55 class Sisql(isql):
  56     def __init__(self):
  57         isql.__init__(self)
  58     def dbConnect(self,directory,db):
  59         if self.database!=None:
  60             self.database.close()
  61         # open the new connection
  62         self.database =sqlite.connect(directory+"\\"+db)
  63     def getTables(self):
  64         cur=self.database.cursor()
  65 	cur.execute("select tbl_name from sqlite_master where type='table' order by tbl_name")
  66 	tables = []
  67 	for row in cur.fetchall():
  68 	        tables.append(row.tbl_name)
  69 	return(tables)
  70 class Oisql(isql): #odbc
  71     def __init__(self):
  72         isql.__init__(self)
  73     def dbConnect(self,directory,db):
  74         if self.database!=None:
  75             self.database.close()
  76         # open the new connection
  77         self.database =odbc.odbc(db)
  78     def getTables(self):
  79         return(None)
  80 class Aisql(isql): #ado
  81     def __init__(self):
  82         isql.__init__(self)
  83     def dbConnect(self,directory,db):
  84         if self.database!=None:
  85             self.database.close()
  86         # open the new connection
  87         self.database=win32com.client.Dispatch("ADODB.Connection")
  88         self.database.Open(db)
  89     def getTables(self):
  90         return(None)
  91     def execQuery(self,s):
  92         rs=self.database.Execute(s)
  93         #print "execQuery"
  94         #print rs
  95         #######to do add error treat
  96         rs=rs[0]
  97         r=[]
  98         n=rs.Fields.Count
  99         a=[]
 100         for i in range(n):
 101              x=rs.Fields(i).Name
 102              a.append(x)
 103         r.append(a)
 104         while not rs.EOF:
 105             n=rs.Fields.Count
 106             b=[]
 107             for i in range(n):
 108                 x=rs.Fields(i).Value
 109                 b.append(x)
 110             r.append(b)
 111             rs.MoveNext()
 112         #self.database.Close()
 113         return(r)
 114 class Pisql(isql): #postgres
 115     def __init__(self):
 116         isql.__init__(self)
 117     def dbConnect(self,directory,db):
 118         if self.database!=None:
 119             self.database.close()
 120         # open the new connection
 121         self.database =PgSQL.connect(db)  #'127.0.0.1:5432:ncscs:postgres:3333'
 122     def getTables(self):
 123         return(None)
 124 if __name__ == "__main__":
 125     i=Pisql()
 126     i.dbConnect(None,'127.0.0.1:5432:ncscs:postgres:3333')
 127     print i.execQuery("select * from test")
 128 ##    i=Gisql()
 129 ##    i.dbConnect(r"E:\ma\python\isql","test")
 130 ##    print i.getTables()
 131 ##    print i.execQuery("select * from dual")
 132 ##    i=Sisql()
 133 ##    i.dbConnect(r"E:\ma\python\isql","trac.db")
 134 ##    print i.getTables()
 135 ##    print i.execQuery("select * from ma")
 136 ##    i=Oisql()
 137 ##    i.dbConnect("","DSN=photo")
 138 ##    print i.getTables()
 139 ##    print i.execQuery("select * from chunks")
 140 ##    constr=r"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\ma\vb\NCS_CS\src\access_2000\data.mdb;Persist Security Info=False"
 141 ##    i=Aisql()
 142 ##    i.dbConnect("",constr)
 143 ##    print i.getTables()
 144 ##    print i.execQuery("select * from sample")
 145