pysql.pysqldb

33 """ Handles database interface""" 34 MAXIMUM_FETCH_SIZE = 10000 # Maximum size of a result set to fetch in one time 35 FETCHALL_FETCH_SIZE = 30 # Size of cursor for fetching all type queries 36

37 - def __init__(self, connectString, mode=""):

38 # Instance attributs 39 self.connection = None 40 self.cursor = None 41 42 # Read Conf 43 self.conf = PysqlConf.getConfig() 44 45 # Keep connection string to allow future connection 46 self.connectString = connectString.encode(self.conf.getCodec(), "ignore") 47 48 # Connect to Oracle 49 try: 50 if mode == "sysoper": 51 try: 52 self.connection = connect(self.connectString, mode=SYSOPER) 53 except (DatabaseError), e: 54 print CYAN + _("Connected to an idle instance") + RESET 55 self.connection = connect(self.connectString, mode=SYSOPER | PRELIM_AUTH) 56 elif mode == "sysdba": 57 try: 58 self.connection = connect(self.connectString, mode=SYSDBA) 59 except (DatabaseError), e: 60 print CYAN + _("Connected to an idle instance") + RESET 61 self.connection = connect(self.connectString, mode=SYSDBA | PRELIM_AUTH) 62 else: 63 self.connection = connect(self.connectString) 64 except (DatabaseError, RuntimeError, InterfaceError), e: 65 raise PysqlException(_("Cannot connect to Oracle: %s") % e)

66

67 - def startup(self, mode="normal"):

68 """Starts up Oracle instance""" 69 if not CX_STARTUP_SHUTDOWN: 70 raise PysqlException(_("Your Oracle and/or cx_Oracle version is too old to support startup option")) 71 try: 72 self.connection.startup() 73 self.connection = connect("/", mode=SYSDBA) 74 self.cursor = self.connection.cursor() 75 self.cursor.execute("alter database mount") 76 if mode == "normal": 77 self.cursor.execute("alter database open") 78 except DatabaseError, e: 79 raise PysqlException(_("Cannot start instance up: %s") % e)

80

81 - def shutdown(self, mode="normal"):

82 """Shuts down Oracle instance""" 83 if not CX_STARTUP_SHUTDOWN: 84 raise PysqlException(_("Your Oracle and/or cx_Oracle version is too old to support shutdown option")) 85 86 try: 87 if mode == "abort": 88 self.connection.shutdown(mode=DBSHUTDOWN_ABORT) 89 elif mode == "immediate": 90 self.connection.shutdown(mode=DBSHUTDOWN_IMMEDIATE) 91 self.connection.shutdown(mode=DBSHUTDOWN_FINAL) 92 else: 93 self.connection.shutdown(mode=DBSHUTDOWN_TRANSACTIONAL) 94 self.connection.shutdown(mode=DBSHUTDOWN_FINAL) 95 self.connection = connect("/", mode=SYSDBA | PRELIM_AUTH) 96 except DatabaseError, e: 97 raise PysqlException(_("Cannot shut instance down: %s") % e)

98

99 - def commit(self):

100 """Commit pending transaction""" 101 try: 102 self.connection.commit() 103 except DatabaseError, e: 104 raise PysqlException(_("Cannot commit: %s") % e)

105

106 - def rollback(self):

107 """Rollback pending transaction""" 108 try: 109 self.connection.rollback() 110 except DatabaseError, e: 111 raise PysqlException(_("Cannot rollback: %s") % e)

112

113 - def executeAll(self, sql, param=[]):

114 """Executes the request given in parameter and 115 returns a list of record (a cursor.fetchall()) 116 Use a private cursor not to pollute execute on. 117 So the getDescription does not work for executeAll""" 118 sql = self.encodeSql(sql) 119 param = self.encodeSql(param) 120 try: 121 if self.cursor is None: 122 self.cursor = self.connection.cursor() 123 self.cursor.arraysize = self.FETCHALL_FETCH_SIZE 124 if param == []: 125 self.cursor.execute(sql) 126 else: 127 self.cursor.prepare(sql) 128 self.cursor.execute(None, param) 129 return self.decodeData(self.cursor.fetchall()) 130 except (DatabaseError, InterfaceError), e: 131 raise PysqlException(_("Cannot execute query: %s") % e)

132

133 - def execute(self, sql, fetch=True, cursorSize=None):

134 """Executes the request given in parameter. 135 136 For a select request, returns a list of record and a flag to indicate if there's more record 137 For insert/update/delete, return the number of record processed 138 @param fetch: for select queries, start fetching (default is true) 139 @param cursorSize: if defined, overide the config cursor size""" 140 sql = self.encodeSql(sql) 141 if not sys.stdin.isatty(): 142 fetch = False 143 try: 144 if self.cursor is None: 145 self.cursor = self.connection.cursor() 146 if cursorSize: 147 self.cursor.arraysize = cursorSize 148 else: 149 self.cursor.arraysize = self.conf.get("fetchSize") 150 self.cursor.execute(sql) 151 if sql.upper().startswith("SELECT") and fetch: 152 return self.fetchNext() 153 elif sql.upper().startswith("SELECT") and not fetch: 154 return (self.decodeData(self.cursor.fetchall()), False) 155 else: 156 return self.getRowCount() 157 except (DatabaseError, InterfaceError), e: 158 raise PysqlException(_("Cannot execute query: %s") % e)

159

160 - def validate(self, sql):

161 """Validates the syntax of the DML query given in parameter. 162 @param sql: SQL query to validate 163 @return: None but raise PysqlException if sql cannot be validated""" 164 sql = self.encodeSql(sql) 165 try: 166 if self.cursor is None: 167 self.cursor = self.connection.cursor() 168 self.cursor.arraysize = 1 169 if sql.upper().startswith("SELECT"): 170 self.cursor.execute(sql) 171 return True 172 elif (sql.upper().startswith("INSERT") 173 or sql.upper().startswith("UPDATE") 174 or sql.upper().startswith("DELETE")): 175 self.connection.begin() 176 self.cursor.execute(sql) 177 self.connection.rollback() 178 return True 179 else: 180 raise PysqlException(_("Can only validate DML queries")) 181 except (DatabaseError, InterfaceError), e: 182 raise PysqlException(_("Cannot validate query: %s") % e)

183

184 - def getCursor(self):

185 """Returns the cursor of the current query""" 186 return self.cursor

187

188 - def getDescription(self, short=True):

189 """Returns header description of cursor with column name and type 190 If short is set to false, the type is given after the column name""" 191 if self.cursor is not None: 192 if short: 193 return [i[0] for i in self.cursor.description] 194 else: 195 return [i[0] + " (" + i[1].__name__ + ")" for i in self.cursor.description]

196

197 - def getRowCount(self):

198 """Returns number of line processed with last request""" 199 if self.cursor is not None: 200 return self.cursor.rowcount 201 else: 202 return 0

203

204 - def fetchNext(self, nbLines=0):

205 """Fetches nbLines from current cursor. 206 Returns a list of record and a flag to indicate if there's more record""" 207 try: 208 moreRows = False 209 if self.cursor is not None: 210 if nbLines <= 0: 211 # Ok, default value or stupid value. Using Cursor array size 212 nbLines = self.cursor.arraysize 213 elif nbLines > self.MAXIMUM_FETCH_SIZE: 214 # Don't fetch too much! 215 nbLines = self.MAXIMUM_FETCH_SIZE 216 217 result = self.cursor.fetchmany(nbLines) 218 if len(result) == nbLines: 219 moreRows = True 220 return (self.decodeData(result), moreRows) 221 else: 222 raise PysqlException(_("No result set. Execute a query before fetching result !")) 223 except (DatabaseError, InterfaceError), e: 224 raise PysqlException(_("Error while fetching results: %s") % e)

225

226 - def getServerOuput(self):

227 """Gets the server buffer output filled with dbms_output.put_line 228 dbms_output should be enabled (should we do this automatically at cursor creation ?) 229 Return list of string or empty list [] if there's nothing to get.""" 230 if not self.cursor: 231 return 232 result = [] 233 serverOutput = self.cursor.var(STRING) 234 serverRC = self.cursor.var(STRING) 235 while True: 236 self.cursor.execute("""begin dbms_output.get_line(:x,:y); end;""", 237 [serverOutput, serverRC]) 238 if serverOutput.getvalue(): 239 result.append(serverOutput.getvalue()) 240 else: 241 break 242 return result

243

244 - def getUsername(self):

245 """Gets the name of the user connected to the database 246 @return: username (unicode)""" 247 return unicode(self.connection.username)

248

249 - def getDSN(self):

250 """Gets the database service name 251 @return: databse service name (unicode)""" 252 return unicode(self.connection.dsn)

253

254 - def getConnectString(self):

255 """Gets the connection string used to create this instance 256 @return: connect string (unicode)""" 257 return self.connectString

258

259 - def getVersion(self):

260 """Gets the version number of the database server 261 @return: db server version (unicode)""" 262 return unicode(self.connection.version)

263

264 - def close(self):

265 """Releases object connection""" 266 #self.cursor.close() 267 try: 268 self.connection.close() 269 except (DatabaseError, InterfaceError), e: 270 raise PysqlException(_("Cannot close connection: %s") % e)

271

272 - def encodeSql(self, sql):

273 """Encode sql request in the proper encoding. 274 @param sql: sql request in unicode format or list of unicode string 275 @return: sql text encoded 276 """ 277 if self.connection: 278 encoding = self.connection.nencoding 279 else: 280 raise PysqlException(_("Cannot encode data, not connected to Oracle")) 281 if sql is None: 282 return None 283 if isinstance(sql, list): 284 # Recurse to encode each item 285 return [self.encodeSql(i) for i in sql] 286 287 if isinstance(sql, str): 288 warn(_("string '%s' is already encoded") % sql) 289 return sql 290 try: 291 sql = sql.encode(encoding) 292 except UnicodeEncodeError: 293 sql = sql.encode(encoding, "replace") 294 warn(_("Got unicode error while encoding '%s'") % sql) 295 return sql

296

297 - def decodeData(self, data):

298 """Encode data fetch out database to unicode 299 @param data: str or list or str 300 @return: encoded data""" 301 #TODO: factorise code with encodeSql function 302 if self.connection: 303 encoding = self.connection.nencoding 304 else: 305 raise PysqlException("Cannot decode data, not connected to Oracle") 306 if data is None: # This correspond to the NULL Oracle object 307 return None 308 elif isinstance(data, (list, tuple)): 309 # Recurse to decode each item 310 return [self.decodeData(i) for i in data] 311 elif isinstance(data, (int, float)): 312 # Nothing to do 313 return data 314 elif isinstance(data, datetime): 315 #TODO: use user define format or Oracle settings 316 # Don't use strftime because it does not support year < 1900 317 data = unicode(data) 318 elif isinstance(data, date): 319 #TODO: use user define format or Oracle settings 320 # Don't use strftime because it does not support year < 1900 321 data = unicode(data) 322 elif isinstance(data, LOB): 323 data = data.read(1, data.size()) 324 elif isinstance(data, unicode): 325 warn(_("Warning, string '%s' is already Unicode") % data) 326 return data 327 328 # Decode data 329 try: 330 data = data.decode(encoding) 331 except UnicodeDecodeError: 332 data = data.decode(encoding, "ignore") 333 warn(_("Can't decode '%s' with %s codec. Check your NLS_LANG variable") % (data, encoding)) 334 except AttributeError: 335 warn(_("Cannot decode %s object") % type(data)) 336 return data

Source Code for Module pysql.pysqldb