00001
00002
00003
00004
00005
00006
00007 __author__ = "John Lim (jlim#natsoft.com)"
00008 __credits__ = "(c) 2004-2007 John Lim"
00009
00010 import exceptions,sys,re
00011 from datetime import datetime
00012
00013 try:
00014 True, False
00015 except NameError:
00016
00017 True, False = 1, 0
00018
00019 MapTypes = {
00020 'VARCHAR' : 'C',
00021 'VARCHAR2' : 'C',
00022 'CHAR' : 'C',
00023 'C' : 'C',
00024 'STRING' : 'C',
00025 'NCHAR' : 'C',
00026 'NVARCHAR' : 'C',
00027 'VARYING' : 'C',
00028 'BPCHAR' : 'C',
00029 'CHARACTER' : 'C',
00030 'INTERVAL' : 'C',
00031
00032 'LONGCHAR' : 'X',
00033 'TEXT' : 'X',
00034 'NTEXT' : 'X',
00035 'M' : 'X',
00036 'X' : 'X',
00037 'CLOB' : 'X',
00038 'NCLOB' : 'X',
00039 'LVARCHAR' : 'X',
00040
00041 'BLOB' : 'B',
00042 'IMAGE' : 'B',
00043 'BINARY' : 'B',
00044 'VARBINARY' : 'B',
00045 'LONGBINARY' : 'B',
00046 'B' : 'B',
00047
00048 'YEAR' : 'D',
00049 'DATE' : 'D',
00050 'D' : 'D',
00051
00052 'TIME' : 'T',
00053 'TIMESTAMP' : 'T',
00054 'DATETIME' : 'T',
00055 'TIMESTAMPTZ' : 'T',
00056 'T' : 'T',
00057
00058 'BOOL' : 'L',
00059 'BOOLEAN' : 'L',
00060 'BIT' : 'L',
00061 'L' : 'L',
00062
00063 'COUNTER' : 'R',
00064 'R' : 'R',
00065 'SERIAL' : 'R', # ifx
00066 'INT IDENTITY' : 'R',
00067
00068 'INT' : 'I',
00069 'INTEGER' : 'I',
00070 'INTEGER UNSIGNED' : 'I',
00071 'SHORT' : 'I',
00072 'TINYINT' : 'I',
00073 'SMALLINT' : 'I',
00074 'I' : 'I',
00075
00076 'LONG' : 'N',
00077 'BIGINT' : 'N',
00078 'DECIMAL' : 'N',
00079 'DEC' : 'N',
00080 'REAL' : 'N',
00081 'DOUBLE' : 'N',
00082 'DOUBLE PRECISION' : 'N',
00083 'SMALLFLOAT' : 'N',
00084 'FLOAT' : 'N',
00085 'NUMBER' : 'N',
00086 'NUM' : 'N',
00087 'NUMERIC' : 'N',
00088 'MONEY' : 'N',
00089
00090 'SQLINT' : 'I',
00091 'SQLSERIAL' : 'I',
00092 'SQLSMINT' : 'I',
00093 'SQLSMFLOAT' : 'N',
00094 'SQLFLOAT' : 'N',
00095 'SQLMONEY' : 'N',
00096 'SQLDECIMAL' : 'N',
00097 'SQLDATE' : 'D',
00098 'SQLVCHAR' : 'C',
00099 'SQLCHAR' : 'C',
00100 'SQLDTIME' : 'T',
00101 'SQLINTERVAL' : 'N',
00102 'SQLBYTES' : 'B',
00103 'SQLTEXT' : 'X'}
00104
00105 class adodb_iter:
00106 cursor = None
00107
00108 def __iter__(self):
00109 return self
00110
00111 def next(self):
00112 if self.cursor.EOF: raise StopIteration
00113 ret = self.cursor.fields
00114 self.cursor.MoveNext()
00115 return ret
00116
00117
00118
00119 def NewADOConnection(modulename):
00120
00121 if modulename.find(':') >= 0:
00122
00123
00124 match=re.match('([^:]*)://(.*)/([^\?]*)\?*(.*)', modulename)
00125 if match:
00126 gps = match.groups()
00127 server = ''
00128 user = ''
00129 pwd = ''
00130 db = ''
00131
00132 if len(gps) >= 1:
00133 modulename = gps[0]
00134
00135 if len(gps) >= 2:
00136 mid = gps[1]
00137 if mid.find('@') >= 0:
00138 upwd,server = mid.split('@')
00139 if mid.find(':') >= 0:
00140 user,pwd = upwd.split(':')
00141 else:
00142 if mid.find(':') >= 0:
00143 user,pwd = mid.split(':')
00144 else:
00145 server = mid
00146
00147 if len(gps) >= 3:
00148 db = gps[2]
00149
00150
00151 conn = NewADOConnection(modulename)
00152 conn.Connect(server,user,pwd,db)
00153 return conn
00154
00155 if modulename == 'oracle':
00156 modulename = 'oci8'
00157
00158 try:
00159 modulename = 'adodb_'+modulename
00160 module = __import__(modulename,globals(), None, [modulename])
00161 except ImportError:
00162 return None
00163 klass = vars(module)[modulename]
00164 return klass()
00165
00166 def ADONewConnection(modulename):
00167 return NewADOConnection(modulename)
00168
00169 class ADOConnection:
00170 databaseType = None
00171 dataProvider = 'native'
00172 host = None
00173 user = None
00174 password = None
00175 database = None
00176 replaceQuote = "\\'"
00177 useExceptions = True
00178 debug = None
00179 getLOBs = True
00180 hasRowCount = True
00181 metaColSQL = 'Invalid'
00182 fmtDate = '%Y-%m-%d'
00183 fmtTimeStamp = '%Y-%m-%d %H:%M:%S'
00184
00185 _errormsg = ''
00186 _errno = 0
00187 _conn = None
00188 _autocommit = True
00189 _connected = True
00190
00191 def __init__(self):
00192 pass
00193
00194 def Connect(self,host=None,user=None,password=None,database=None):
00195 self.database = database
00196 self.host = host
00197 self.user = user
00198 self.password = password
00199 self._connect(host,user,password,database)
00200 return bool(self._conn)
00201
00202 def IsConnected(self):
00203 return bool(self._conn)
00204
00205 def DriverInfo(self):
00206 try:
00207 m = self.Module()
00208 print "Driver =",self.databaseType
00209 print "API Level =",m.apilevel
00210 print "Param Style =",m.paramstyle
00211 print "Thread Safety =",m.threadsafety," (0=none, 1=module, 2=connections, 3=cursors)"
00212 print "--------------"
00213 except:
00214 print "???????"
00215
00216 def ErrorMsg(self):
00217 return self._errormsg
00218
00219 def ErrorNo(self):
00220 return self._errno
00221
00222
00223 def qstr(self,s):
00224 if (self.replaceQuote == "\\'"): s = str(s).replace('\\','\\\\')
00225 return "'"+str(s).replace("'", self.replaceQuote)+"'"
00226
00227 def quote(self,s):
00228 return "'"+str(s).replace("'", self.replaceQuote)+"'"
00229
00230 def addq(self,s):
00231 if (self.replaceQuote == "\\'"): s = str(s).replace('\\','\\\\')
00232 return str(s).replace("'", self.replaceQuote)
00233
00234 def Conn(self):
00235 return self._conn
00236
00237 def _query(self,sql,params=None,_cursor=None):
00238
00239 try:
00240 if _cursor == None: _cursor = self._conn.cursor()
00241 if self.debug:
00242 s = "(%s): %s" % (self.databaseType, sql)
00243 if type(self.debug) is str:
00244 try:
00245 f = file(self.debug,'w')
00246 f.write(s+"\n")
00247 f.close()
00248 except:
00249 pass
00250 elif long(self.debug) == 2:
00251 print "<hr>"+s.replace('&','&').replace('<','<')+"<hr>"
00252 else:
00253 print s;
00254
00255
00256 if params == None: _cursor.execute(sql)
00257 else: _cursor.execute(sql,params)
00258 self._errormsg = ''
00259 self._errno = 0
00260
00261 except StandardError, err:
00262 self._errormsg = str(err)
00263 self._errno = -1
00264 if self.useExceptions:
00265 raise sys.exc_info()[0] ,str(err)+': '+sql
00266 _cursor = None
00267
00268 return _cursor
00269
00270 def SelectLimit(self,sql,limit,offset=-1,params=None):
00271 pass
00272
00273 def Execute(self,sql,params=None):
00274 c = self._query(sql,params)
00275 if c == None: return None
00276 rs = self._newcursor(c)
00277 return rs
00278
00279 def UpdateBlob(self,table,field,blob,where,blobtype='BLOB'):
00280 raise StandardError, 'UpdateBlob not supported'
00281
00282 def UpdateBlobFile(self,table,field,filepath,where,blobtype='BLOB'):
00283 f = file(filepath, 'rb')
00284 data = f.read()
00285 f.close()
00286 self.UpdateBlob(table,field,data,where,blobtype)
00287
00288 def UpdateClob(self,table,field,blob,where):
00289 self.UpdateBlob(table,field,blob,where,'CLOB')
00290
00291 def GetRows(self,sql,params=None):
00292 return self.GetAll(sql,params)
00293
00294 def GetArray(self,sql,params=None):
00295 return self.GetAll(sql,params)
00296
00297 def GetAll(self,sql,params=None):
00298 c = self._query(sql,params)
00299 if c == None: return None
00300 all = c.fetchall()
00301 c.close()
00302 return all
00303
00304 def GetRow(self,sql,params=None):
00305 c = self._query(sql,params)
00306 if c == None: return None
00307 c.close()
00308 return c.fetchone()
00309
00310 def GetRow(self,sql,params=None):
00311 c = self._query(sql,params)
00312 if c == None: return None
00313 row = c.fetchone()
00314 c.close()
00315 return row
00316
00317 def GetOne(self,sql,params=None):
00318 c = self._query(sql,params)
00319 if c == None: return None
00320 arr = c.fetchone()
00321 c.close()
00322 if (arr == None): return None
00323 return arr[0]
00324
00325 def GetCol(self, sql, params=None):
00326 rs = self.Execute(sql,params)
00327 arr = []
00328 while not rs.EOF:
00329 arr.append(rs.fields[0])
00330 rs.MoveNext()
00331 rs.Close()
00332 return arr
00333
00334 def GetAssoc(self, sql, params=None):
00335 rs = self.Execute(sql,params)
00336 dict = {}
00337 if rs.EOF:
00338 return None
00339
00340 if len(rs.fields) == 2:
00341 while not rs.EOF:
00342 dict[rs.fields[0]] = rs.fields[1]
00343 rs.MoveNext()
00344 elif len(rs.fields)>2:
00345 while not rs.EOF:
00346 dict[rs.fields[0]] = rs.fields[1:]
00347 rs.MoveNext()
00348 else:
00349 while not rs.EOF:
00350 dict[rs.fields[0]] = None
00351 rs.MoveNext()
00352
00353 return dict
00354
00355 def GetDict(self, sql, params=None):
00356 return self.GetAssoc(sql,params)
00357
00358 def BeginTrans(self):
00359 pass
00360
00361 def CommitTrans(self):
00362 pass
00363
00364 def RollbackTrans(self):
00365 pass
00366
00367 def Close(self):
00368 try:
00369 if self._conn != None: self._conn.close()
00370 except:
00371 pass
00372
00373 self._conn = None
00374
00375 def DBDate(self,d):
00376 return "'%s'" % d.strftime(self.fmtDate)
00377
00378 def DBTimeStamp(self,d):
00379 return "'%s'" % d.strftime(self.fmtTimeStamp)
00380
00381 _redate = None
00382
00383
00384 def Date(self,s):
00385 dates = str(s)
00386 if self._redate == None:
00387 self._redate = re.compile("^([0-9]{4})[-/\.]?([0-9]{1,2})[-/\.]?([0-9]{1,2})")
00388 match = self._redate.search(dates)
00389 if not match: return None
00390
00391 year = long(match.group(1))
00392 month = long(match.group(2))
00393 day = long(match.group(3))
00394 return datetime(year, month, day)
00395
00396 _rets = None
00397
00398
00399 def TimeStamp(self,s):
00400 ts = str(s)
00401 if self._rets == None:
00402 self._rets = re.compile("^([0-9]{4})[-/\.]?([0-9]{1,2})[-/\.]?([0-9]{1,2})[ ,-]*([0-9]{1,2}):?([0-9]{1,2}):?([0-9\.]{1,5})")
00403 match = self._rets.search(ts)
00404 if not match: return self.Date(ts)
00405
00406 year = long(match.group(1))
00407 month = long(match.group(2))
00408 day = long(match.group(3))
00409 hour = long(match.group(4))
00410 min = long(match.group(5))
00411 sec = long(float(match.group(6).strip()))
00412 return datetime(year, month, day, hour, min, sec)
00413
00414 def MetaType(self, dbtype):
00415 global MapTypes
00416
00417 dbtype = dbtype.upper()
00418 if MapTypes.has_key(dbtype):
00419 return MapTypes[dbtype]
00420
00421 return 'N'
00422
00423 def MetaColumns(self, table):
00424 sql = self.metaColSQL % table
00425 return self.GetAll(sql)
00426
00427
00428
00429 class ADOCursor:
00430 _cursor = None
00431 fields = None
00432 EOF = False
00433 _rowcount = 0
00434 _isselect = False
00435 _insertid = 0
00436 _conn = None
00437
00438 def __init__(self,rs,conn,norowcount=False):
00439 self._cursor = rs
00440 self._conn = conn
00441 if norowcount: self._rowcount = -1
00442 else: self._rowcount = rs.rowcount
00443
00444 try:
00445 self.MoveNext()
00446 self._isselect = True
00447 except:
00448 pass
00449 self.EOF = (self.fields == None)
00450
00451 def __iter__(self):
00452 iter = adodb_iter()
00453 iter.cursor = self
00454 return iter
00455
00456 def RecordCount(self):
00457 return self._rowcount
00458
00459 def MoveNext(self):
00460 self.fields = self._cursor.fetchone()
00461 self.EOF = (self.fields == None)
00462 return self.EOF
00463
00464 def FetchRow(self):
00465 row = self.fields
00466 self.fields = self._cursor.fetchone()
00467 self.EOF = (self.fields == None)
00468 return row
00469
00470
00471
00472 def FetchField(self,row):
00473
00474 if len(self._cursor.description) <= row: return None
00475 return self._cursor.description[row]
00476
00477 def Affected_Rows(self):
00478 if self._rowcount >= 0: return self._rowcount
00479 return 0
00480
00481 def Insert_ID(self):
00482 return self._insertid
00483
00484 def Cursor(self):
00485 return self._cursor
00486
00487 def GetRowAssoc(self,upper=1):
00488 d = {}
00489 i = 0
00490 desc = self._cursor.description
00491 if upper:
00492 for i in xrange(0,len(self.fields)):
00493 d[desc[i][0].upper()] = self.fields[i]
00494 elif not upper:
00495 for i in xrange(0,len(self.fields)):
00496 d[desc[i][0].lower()] = self.fields[i]
00497 else:
00498 for i in xrange(0,len(self.fields)):
00499 d[desc[i][0]] = self.fields[i]
00500
00501 return d
00502
00503 def Close(self):
00504 if self._cursor: self._cursor.close()
00505 self._cursor = None
00506
00507
00508
00509
00510
00511 def _Test_Eq(testid, correct, testval, errmsg=''):
00512 if correct == testval:
00513 print "Passed Test: "+testid
00514 else:
00515 print ""
00516 print "********* Failed Test: "+testid
00517 print "********************** "+str(errmsg)
00518 print "********************** expected="+str(correct)
00519 print "********************** actual="+str(testval)
00520
00521 def Test_Blob(db):
00522 import os
00523 src = 'c:/lensserver.gif'
00524 dest = 'c:/testpy1.gif'
00525 try:
00526 os.unlink(dest)
00527 except:
00528 pass
00529
00530 saved = db.debug
00531 saveb = db.getLOBs
00532 db.debug = True
00533 db.getLOBs = True
00534
00535 db.UpdateBlobFile('photos','photo',src,'id=1')
00536 data = db.GetOne('select photo from photos where id=1')
00537 f = file(dest,'wb')
00538 f.write(data)
00539 f.close()
00540
00541 rs = db.Execute('select * from photos')
00542 while not rs.EOF:
00543 print 'Fields=',rs.fields
00544 rs.MoveNext()
00545
00546 print "======================="
00547
00548 rows = db.GetAll('select * from photos where id<=1')
00549 print rows
00550
00551 db.getLOBs = saveb
00552 db.debug = saved
00553
00554 def Test(db,debug=False):
00555 db.DriverInfo()
00556
00557 if False:
00558 d = db.Date('2004-03-21')
00559 print '2004-03-21=',d
00560
00561 d = db.TimeStamp('2004-03-22 12:50:51')
00562 print '2004-03-22 12:50:51=',d
00563
00564 print "DBTimeStamp=", db.DBTimeStamp(d)
00565
00566 db.useExceptions = True
00567 try:
00568 sql = 'select * from xadoxyz where 0 < id and id < 3'
00569 rs = db.Execute(sql)
00570 _Test_Eq('Bad SQL',None, rs, sql)
00571 except:
00572 print "And you should see an error message indicating bad table was defined: "
00573 print "err=",db.ErrorMsg()
00574
00575 print "-----"
00576 rs = db.Execute('select * from ADOXYZ where 0 < id and id < 3 order by id')
00577 while not rs.EOF:
00578 print rs.fields
00579 rs.MoveNext()
00580
00581 print "You should see 2 rows of data here:"
00582 rs = db.Execute('select * from adoxyz where 0 < id and id < 3 order by id')
00583 print "rows=",rs.RecordCount()
00584 while (not rs.EOF):
00585 print rs.GetRowAssoc()
00586 rs.MoveNext()
00587
00588 print "-----"
00589 rs = db.Execute('select id,firstname from adoxyz where 0 < id and id < 3 order by id')
00590 _Test_Eq("Test FetchField",'FIRSTNAME',rs.FetchField(1)[0].upper())
00591 if (debug): print rs.FetchField(1)
00592 cnt = 0
00593 while 1:
00594 arr=rs.FetchRow()
00595 if arr == None: break
00596 cnt += 1
00597 _Test_Eq('Execute 2.0',cnt,arr[0])
00598
00599 _Test_Eq('Execute 2.1',2,cnt)
00600 if rs.RecordCount() == -1: print "*** RecordCount not supported: -1"
00601 else: _Test_Eq('Execute 2.1 RecordCount',2,rs.RecordCount())
00602
00603 rs = db.Execute("delete from adoxyz where id=997")
00604 cnt = rs.Affected_Rows()
00605 _Test_Eq('Affected_Rows',1,cnt)
00606
00607 ok = db.Execute("insert into adoxyz (id, firstname,lastname) values (997,'python','snake')")
00608 if not ok: _Test_Eq('DELETE/INSERT','inserted row','failed insert')
00609
00610 row = db.GetRow("select id,firstname from adoxyz where id=997");
00611 _Test_Eq('GetRow',str(997)+' '+'python',str(int(row[0]))+' '+row[1].rstrip(),row)
00612
00613 row = db.GetOne("select id,firstname from adoxyz where id=997");
00614 _Test_Eq('GetOne',997,row)
00615
00616 rs = db.SelectLimit("select id,firstname from adoxyz",3)
00617 cnt = 0
00618
00619 try:
00620 for row in rs:
00621 cnt += 1
00622
00623 _Test_Eq('SelectLimit',3,cnt)
00624 except:
00625 print "Failed Iteration"
00626 print sys.exc_info()[1]
00627
00628 d = db.GetOne('select created from adoxyz where id=1')
00629 d2 = db.TimeStamp(d)
00630 _Test_Eq('DBDate',str(d)[:19],str(d2))
00631
00632 if (db.qstr("\\show'boat") != "'\\\\show\\'boat'" and db.qstr("\\show'boat") != "'\\show''boat'"):
00633 _Test_Eq('qstr',"qstr(\\show'boat)", db.qstr("\\show'boat"))
00634 else:
00635 _Test_Eq('qstr','1','1')
00636
00637 try:
00638 db.debug=True
00639 print "Testing GetAssoc"
00640 arr = db.GetAssoc('select firstname,lastname from adoxyz')
00641 print arr
00642 print "Testing GetCol"
00643 arr = db.GetCol('select firstname from adoxyz')
00644 print arr
00645 except:
00646 print sys.exc_info()[1]
00647
00648 try:
00649 print "MetaColumns:"
00650 rows = db.MetaColumns('adoxyz')
00651 print rows
00652 except:
00653 print "Failed MetaColumns"
00654 print sys.exc_info()[1]
00655
00656 try:
00657 db.BeginTrans()
00658 ok = db.Execute("insert into adoxyz (id, firstname,lastname) values (1997,'python','snake')")
00659 db.RollbackTrans()
00660 val = db.GetOne('select * from adoxyz where id=1997')
00661 _Test_Eq('Rollback Test',None,val)
00662 except:
00663 print "Failed Rollback Test"
00664 print sys.exc_info()[1]