Hi Ed Some example of how using this new feature ? Also it is possible, instead of a field any expression ?
Thanks for your effort Ed !!! -- Jaime Mora Ramones Tantoyuca Veracruz, Mexico "Ed Leafe" <[EMAIL PROTECTED]> escribió en el mensaje news:[EMAIL PROTECTED] > dabo Commit > Revision 2243 > Date: 2006-06-29 10:57:56 -0700 (Thu, 29 Jun 2006) > Author: ed > > Changed: > U trunk/dabo/db/__init__.py > U trunk/dabo/db/dCursorMixin.py > A trunk/dabo/db/dDataSet.py > > Log: > Refactored the old DataSet class out of dCursorMixin and into its own Dabo base class, named 'dDataSet'. > > Added a 'filter()' method that will accept some basic info, such as field and value, and return a filtered data set. This is working, but I plan on adding a function to clear the filter and return the original data set, as well as add a filter to a filter, etc. > > > Diff: > Modified: trunk/dabo/db/__init__.py > =================================================================== > --- trunk/dabo/db/__init__.py 2006-06-29 15:33:18 UTC (rev 2242) > +++ trunk/dabo/db/__init__.py 2006-06-29 17:57:56 UTC (rev 2243) > @@ -31,4 +31,5 @@ > from dMemento import dMemento > from dConnectInfo import dConnectInfo > from dTable import dTable > -from dNoEscQuoteStr import dNoEscQuoteStr > \ No newline at end of file > +from dNoEscQuoteStr import dNoEscQuoteStr > +from dDataSet import dDataSet > > Modified: trunk/dabo/db/dCursorMixin.py > =================================================================== > --- trunk/dabo/db/dCursorMixin.py 2006-06-29 15:33:18 UTC (rev 2242) > +++ trunk/dabo/db/dCursorMixin.py 2006-06-29 17:57:56 UTC (rev 2243) > @@ -11,12 +11,6 @@ > from decimal import Decimal > except ImportError: > _USE_DECIMAL = False > -# We also need to know if sqlite is installed > -_useSQLite = True > -try: > - from pysqlite2 import dbapi2 as sqlite > -except ImportError: > - _useSQLite = False > > import dabo > import dabo.dConstants as kons > @@ -26,6 +20,7 @@ > from dabo.dObject import dObject > from dabo.db import dNoEscQuoteStr > from dabo.db import dTable > +from dabo.db.dDataSet import dDataSet > > > class dCursorMixin(dObject): > @@ -80,7 +75,7 @@ > # it will be a separate object. > self.sqlManager = self > # Attribute that holds the data of the cursor > - self._records = DataSet() > + self._records = dDataSet() > # Attribute that holds the current row number > self.__rownumber = -1 > > @@ -201,7 +196,7 @@ > # Not all backends support 'fetchall' after executing a query > # that doesn't return records, such as an update. > try: > - self._records = DataSet(self.fetchall()) > + self._records = dDataSet(self.fetchall()) > except: > pass > > @@ -240,7 +235,7 @@ > else: > dic[fldNames[i]] = row[i] > tmpRows.append(dic) > - self._records = DataSet(tmpRows) > + self._records = dDataSet(tmpRows) > else: > # Make all string values into unicode > for row in self._records: > @@ -272,8 +267,8 @@ > # Usually blob data > row[fld] = val.tostring() > > - # Convert to DataSet > - self._records = DataSet(self._records) > + # Convert to dDataSet > + self._records = dDataSet(self._records) > return res > > > @@ -454,7 +449,7 @@ > newRows = [] > for elem in sortList: > newRows.append(elem[1]) > - self._records = DataSet(newRows) > + self._records = dDataSet(newRows) > > # restore the RowNumber > if currRowKey: > @@ -776,7 +771,7 @@ > ret = [tmprec.copy() for tmprec in tmp] > except AttributeError: > # return empty dataset > - return DataSet() > + return dDataSet() > > internals = (kons.CURSOR_MEMENTO, kons.CURSOR_NEWFLAG, > kons.CURSOR_TMPKEY_FIELD) > @@ -793,7 +788,7 @@ > if k not in flds: > del rec[k] > > - ret = DataSet(ret) > + ret = dDataSet(ret) > return ret > > > @@ -806,7 +801,7 @@ > with an equals sign. All expressions will therefore be a string > beginning with '='. Literals can be of any type. > """ > - if isinstance(self._records, DataSet): > + if isinstance(self._records, dDataSet): > self._records.replace(field, valOrExpr, scope=scope) > > > @@ -982,7 +977,7 @@ > # Copy the _blank dict to the _records, and adjust everything accordingly > tmprows = list(self._records) > tmprows.append(self._blank.copy()) > - self._records = DataSet(tmprows) > + self._records = dDataSet(tmprows) > # Adjust the RowCount and position > self.RowNumber = self.RowCount - 1 > # Add the 'new record' flag to the last record (the one we just added) > @@ -1096,7 +1091,7 @@ > """ > lRec = list(self._records) > del lRec[rr] > - self._records = DataSet(lRec) > + self._records = dDataSet(lRec) > self.RowNumber = min(self.RowNumber, self.RowCount-1) > > > @@ -1864,551 +1859,3 @@ > > UserSQL = property(_getUserSQL, _setUserSQL, None, > _("SQL statement to run. If set, the automatic SQL builder will not be used.")) > - > - > - > -class DataSetOld(tuple): > - """ This class assumes that its contents are not ordinary tuples, but > - rather tuples consisting of dicts, where the dict keys are field names. > - This is the data structure returned by the dCursorMixin class. > - """ > - # List comprehensions used in this class require a non-conflicting > - # name. This is unlikely to be used anywhere else. > - _dictSubName = "_dataSet_rec" > - > - > - def _fldReplace(self, expr, dictName=None): > - """The list comprehensions require the field names be the keys > - in a dictionary expression. Users, though, should not have to know > - about this. This takes a user-defined, SQL-like expressions, and > - substitutes any field name with the corresponding dict > - expression. > - """ > - keys = self[0].keys() > - patTemplate = "(.*\\b)%s(\\b.*)" > - ret = expr > - if dictName is None: > - dictName = self._dictSubName > - for kk in keys: > - pat = patTemplate % kk > - mtch = re.match(pat, ret) > - if mtch: > - ret = mtch.groups()[0] + "%s['%s']" % (dictName, kk) + mtch.groups()[1] > - return ret > - > - > - def processFields(self, fields, aliasDict): > - if isinstance(fields, basestring): > - fields = fields.split(",") > - for num, fld in enumerate(fields): > - fld = fld.replace(" AS ", " as ").replace(" As ", " as ").strip() > - fa = fld.split(" as ") > - if len(fa) > 1: > - # An alias is specified > - fld = fa[0].strip() > - aliasDict[fld] = fa[1].strip() > - fields[num] = fld > - return fields, aliasDict > - > - > - def select(self, fields=None, where=None, orderBy=None): > - fldList = [] > - fldAliases = {} > - whereList = [] > - orderByList = [] > - keys = self[0].keys() > - if fields is None or fields == "*": > - # All fields > - fields = keys > - fields, fldAliases = self.processFields(fields, fldAliases) > - for fld in fields: > - fldList.append("'%s' : %s" % (fld, self._fldReplace(fld))) > - fieldsToReturn = ", ".join(fldList) > - fieldsToReturn = "{%s}" % fieldsToReturn > - > - # Where list elements > - if where is None: > - whereClause = "" > - else: > - if isinstance(where, basestring): > - where = [where] > - for wh in where: > - whereList.append(self._fldReplace(wh)) > - whereClause = " and ".join(whereList) > - if whereClause: > - whereClause = " if %s" % whereClause > - stmnt = "[%s for %s in self %s]" % (fieldsToReturn, self._dictSubName, whereClause) > - resultSet = eval(stmnt) > - > - if fldAliases: > - # We need to replace the keys for the field names with the > - # appropriate alias names > - for rec in resultSet: > - for key, val in fldAliases.items(): > - orig = rec.get(key) > - if orig: > - rec[val] = orig > - del rec[key] > - > - if orderBy: > - # This should be a comma separated string in the format: > - # fld1, fld2 desc, fld3 asc > - # After the field name is an optional direction, either 'asc' > - # (ascending, default) or 'desc' (descending). > - # IMPORTANT! Fields referenced in 'orderBy' MUST be in > - # the result data set! > - orderByList = orderBy.split(",") > - sortList = [] > - > - def orderBySort(val1, val2): > - ret = 0 > - compList = orderByList[:] > - while not ret: > - comp = compList[0] > - compList = compList[1:] > - if comp[-4:].lower() == "desc": > - compVals = (-1, 1) > - else: > - compVals = (1, -1) > - # Remove the direction, if any, from the comparison. > - compWords = comp.split(" ") > - if compWords[-1].lower() in ("asc", "desc"): > - compWords = compWords[:-1] > - comp = " ".join(compWords) > - cmp1 = self._fldReplace(comp, "val1") > - cmp2 = self._fldReplace(comp, "val2") > - eval1 = eval(cmp1) > - eval2 = eval(cmp2) > - if eval1 > eval2: > - ret = compVals[0] > - elif eval1 < eval2: > - ret = compVals[1] > - else: > - # They are equal. Continue comparing using the > - # remaining terms in compList, if any. > - if not compList: > - break > - return ret > - > - resultSet.sort(orderBySort) > - > - return DataSet(resultSet) > - > - > - def join(self, target, sourceAlias, targetAlias, condition, > - sourceFields=None, targetFields=None, where=None, > - orderBy=None, joinType=None): > - """This method joins the current DataSet and the target > - DataSet, based on the specified condition. The 'joinType' > - parameter will determine the type of join (inner, left, right, full). > - Where and orderBy will affect the result of the join, and so they > - should reference fields in the result set without alias qualifiers. > - """ > - if joinType is None: > - joinType = "inner" > - joinType = joinType.lower().strip() > - if joinType == "outer": > - # This is the same as 'left outer' > - joinType = "left" > - if "outer" in joinType.split(): > - tmp = joinType.split() > - tmp.remove("outer") > - joinType = tmp[0] > - > - leftDS = self > - rightDS = target > - leftAlias = sourceAlias > - rightAlias = targetAlias > - leftFields = sourceFields > - rightFields = targetFields > - leftFldAliases = {} > - rightFldAliases = {} > - if joinType == "right": > - # Same as left; we just need to reverse things > - (leftDS, rightDS, leftAlias, rightAlias, leftFields, > - rightFields) = (rightDS, leftDS, rightAlias, leftAlias, > - rightFields, leftFields) > - > - > - leftFields, leftFldAliases = self.processFields(leftFields, leftFldAliases) > - rightFields, rightFldAliases = self.processFields(rightFields, rightFldAliases) > - > - # Parse the condition. It should have an '==' in it. If not, > - # raise an error. > - condList = condition.split("==") > - if len(condList) == 1: > - # No equality specified > - errMsg = _("Bad join: no '==' in join condition: %s") % condition > - raise dException.QueryException, errMsg > - > - leftCond = None > - rightCond = None > - leftPat = "(.*)(\\b%s\\b)(.*)" % leftAlias > - rightPat = "(.*)(\\b%s\\b)(.*)" % rightAlias > - > - mtch = re.match(leftPat, condList[0]) > - if mtch: > - leftCond = condList[0].strip() > - else: > - mtch = re.match(leftPat, condList[1]) > - if mtch: > - leftCond = condList[1].strip() > - mtch = re.match(rightPat, condList[0]) > - if mtch: > - rightCond = condList[0].strip() > - else: > - mtch = re.match(rightPat, condList[1]) > - if mtch: > - rightCond = condList[1].strip() > - condError = "" > - if leftCond is None: > - condError += _("No join condition specified for alias '%s'") % leftAlias > - if rightCond is None: > - if condError: > - condError += "; " > - condError += _("No join condition specified for alias '%s'") % rightAlias > - if condError: > - raise dException.QueryException, condError > - > - # OK, we now know how to do the join. The plan is this: > - # create an empty result list > - # scan through all the left records > - # if leftFields, run a select to get only those fields. > - # find all the matching right records using select > - # if matches, update each with the left select and add > - # to the result. > - # if no matches: > - # if inner join: > - # skip to next > - # else: > - # get dict.fromkeys() for right select > - # update left with fromkeys and add to result > - # > - # We'll worry about full joins later. > - > - resultSet = [] > - for leftRec in leftDS: > - if leftFields: > - leftSelect = DataSet([leftRec]).select(fields=leftFields)[0] > - else: > - leftSelect = leftRec > - tmpLeftCond = leftCond.replace(leftAlias, "leftRec") > - tmpLeftCond = "%s['%s']" % tuple(tmpLeftCond.split(".")) > - leftVal = eval(tmpLeftCond) > - > - if isinstance(leftVal, basestring): > - leftVal = "'%s'" % leftVal > - rightWhere = rightCond.replace(rightAlias + ".", "") + "== %s" % leftVal > - rightRecs = rightDS.select(fields=rightFields, where=rightWhere) > - > - if rightRecs: > - for rightRec in rightRecs: > - rightRec.update(leftSelect) > - resultSet.append(rightRec) > - else: > - if not joinType == "inner": > - rightKeys = rightDS.select(fields=rightFields)[0].keys() > - leftSelect.update(dict.fromkeys(rightKeys)) > - resultSet.append(leftSelect) > - > - resultSet = DataSet(resultSet) > - if where or orderBy: > - resultSet = resultSet.select(where=where, orderBy=orderBy) > - return resultSet > - > - > - > -class DataSet(tuple): > - """ This class assumes that its contents are not ordinary tuples, but > - rather tuples consisting of dicts, where the dict keys are field names. > - This is the data structure returned by the dCursorMixin class. > - > - It is used to give these data sets the ability to be queried, joined, etc. > - This is accomplished by using SQLite in-memory databases. If SQLite > - and pysqlite2 are not installed on the machine this is run on, a > - warning message will be printed out and the SQL functions will return > - None. The data will still be usable, though. > - """ > - def __init__(self, *args, **kwargs): > - super(DataSet, self).__init__(*args, **kwargs) > - if _useSQLite: > - self._connection = None > - self._cursor = None > - self._populated = False > - # We may need to encode fields that are not legal names. > - self.fieldAliases = {} > - # Pickling mementos is slow. This dict will hold them > - # instead > - self._mementoHold = {} > - self._mementoSequence = 0 > - # Register the adapters > - sqlite.register_adapter(dMemento, self._adapt_memento) > - if _USE_DECIMAL: > - sqlite.register_adapter(Decimal, self._adapt_decimal) > - > - # Register the converters > - sqlite.register_converter("memento", self._convert_memento) > - if _USE_DECIMAL: > - sqlite.register_converter("decimal", self._convert_decimal) > - > - self._typeDict = {int: "integer", long: "integer", str: "text", > - unicode: "text", float: "real", datetime.date: "date", > - datetime.datetime: "timestamp", dMemento : "memento"} > - if _USE_DECIMAL: > - self._typeDict[Decimal] = "decimal" > - > - > - def __del__(self): > - if _useSQLite: > - if self._cursor is not None: > - self._cursor.close() > - if self._connection is not None: > - self._connection.close() > - > - > - def _adapt_decimal(self, decVal): > - """Converts the decimal value to a string for storage""" > - return str(decVal) > - > - > - def _convert_decimal(self, strval): > - """This is a converter routine. Takes the string > - representation of a Decimal value and return an actual > - decimal, if that module is present. If not, returns a float. > - """ > - if _USE_DECIMAL: > - ret = Decimal(strval) > - else: > - ret = float(strval) > - return ret > - > - > - def _adapt_memento(self, mem): > - """Substitutes a sequence for the memento for storage""" > - pos = self._mementoSequence > - self._mementoSequence += 1 > - self._mementoHold[pos] = mem > - return str(pos) > - > - > - def _convert_memento(self, strval): > - """Replaces the placeholder sequence with the actual memento.""" > - pos = int(strval) > - return self._mementoHold[pos] > - > - > - def replace(self, field, valOrExpr, scope=None): > - """Replaces the value of the specified field with the given value > - or expression. All records matching the scope are affected; if > - no scope is specified, all records are affected. > - > - 'valOrExpr' will be treated as a literal value, unless it is prefixed > - with an equals sign. All expressions will therefore be a string > - beginning with '='. Literals can be of any type. > - """ > - if scope is None: > - scope = "True" > - else: > - scope = self._fldReplace(scope, "rec") > - > - literal = True > - if isinstance(valOrExpr, basestring): > - if valOrExpr.strip()[0] == "=": > - literal = False > - valOrExpr = valOrExpr.replace("=", "", 1) > - valOrExpr = self._fldReplace(valOrExpr, "rec") > - for rec in self: > - if eval(scope): > - if literal: > - rec[field] = valOrExpr > - else: > - expr = "rec['%s'] = %s" % (field, valOrExpr) > - exec(expr) > - > - > - def sort(self, col, ascdesc=None, caseSensitive=None): > - if ascdesc is None: > - ascdesc = "ASC" > - casecollate = "" > - if caseSensitive is False: > - # The default of None will be case-sensitive > - casecollate = " COLLATE NOCASE " > - stmnt = "select * from dataset order by %s %s %s" > - stmnt = stmnt % (col, casecollate, ascdesc) > - ret = self.execute(stmnt) > - return ret > - > - > - def _fldReplace(self, expr, dictName=None): > - """The list comprehensions require the field names be the keys > - in a dictionary expression. Users, though, should not have to know > - about this. This takes a user-defined, SQL-like expressions, and > - substitutes any field name with the corresponding dict > - expression. > - """ > - keys = self[0].keys() > - patTemplate = "(.*\\b)%s(\\b.*)" > - ret = expr > - if dictName is None: > - dictName = self._dictSubName > - for kk in keys: > - pat = patTemplate % kk > - mtch = re.match(pat, ret) > - if mtch: > - ret = mtch.groups()[0] + "%s['%s']" % (dictName, kk) + mtch.groups()[1] > - return ret > - > - > - def _makeCreateTable(self, ds, alias=None): > - """Makes the CREATE TABLE string needed to represent > - this data set. There must be at least one record in the > - data set, or we can't get the necessary column info. > - """ > - if len(ds) == 0: > - return None > - if alias is None: > - # Use the default > - alias = "dataset" > - rec = ds[0] > - keys = rec.keys() > - retList = [] > - > - for key in keys: > - if key.startswith("dabo-"): > - # This is an internal field > - safekey = key.replace("-", "_") > - self.fieldAliases[safekey] = key > - else: > - safekey = key > - typ = type(rec[key]) > - try: > - retList.append("%s %s" % (safekey, ds._typeDict[typ])) > - except KeyError: > - retList.append(safekey) > - return "create table %s (%s)" % (alias, ", ".join(retList)) > - > - > - def _populate(self, ds, alias=None): > - """This is the method that converts a Python dataset > - into a SQLite table with the name specified by 'alias'. > - """ > - if alias is None: > - # Use the default > - alias = "dataset" > - if len(ds) == 0: > - # Can't create and populate a table without a structure > - dabo.errorLog.write(_("Cannot populate without data for alias %s") > - % alias) > - return None > - if ds._populated: > - # Data's already there; no need to re-load it > - return > - self._cursor.execute(self._makeCreateTable(ds, alias)) > - > - flds, vals = ds[0].keys(), ds[0].values() > - # Fields may contain illegal names. This will correct them > - flds = [fld.replace("dabo-", "dabo_") for fld in flds] > - fldParams = [":%s" % fld for fld in flds] > - fldCnt = len(flds) > - insStmnt = "insert into %s (%s) values (%s)" % (alias, > - ", ".join(flds), ", ".join(fldParams)) > - > - def recGenerator(ds): > - for rec in ds: > - yield rec > - > - self._cursor.executemany(insStmnt, recGenerator(ds)) > - if ds is self: > - self._populated = True > - > - > - def execute(self, sqlExpr, cursorDict=None): > - """This method allows you to work with a Python data set > - (i.e., a tuple of dictionaries) as if it were a SQL database. You > - can run any sort of statement that you can in a normal SQL > - database. It requires that SQLite and pysqlite2 are installed; > - if they aren't, this will return None. > - > - The SQL expression can be any standard SQL expression; however, > - the FROM clause should always be: 'from dataset', since these > - datasets do not have table names. > - > - If you want to do multi-dataset joins, you need to pass the > - additional DataSet objects in a dictionary, where the value is the > - DataSet, and the key is the alias used to reference that DataSet > - in your join statement. > - """ > - def dict_factory(cursor, row): > - dd = {} > - for idx, col in enumerate(cursor.description): > - dd[col[0]] = row[idx] > - return dd > - > - class DictCursor(sqlite.Cursor): > - def __init__(self, *args, **kwargs): > - sqlite.Cursor.__init__(self, *args, **kwargs) > - self.row_factory = dict_factory > - > - if not _useSQLite: > - dabo.errorLog.write(_("SQLite and pysqlite2 must be installed to use this function")) > - return None > - if self._connection is None: > - self._connection = sqlite.connect(":memory:", > - detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES, > - isolation_level="EXCLUSIVE") > - if self._cursor is None: > - self._cursor = self._connection.cursor(factory=DictCursor) > - > -# import time > -# st = time.clock() > -# print "starting" > - > - # Create the table for this DataSet > - self._populate(self, "dataset") > - > -# pt = time.clock() > -# print "POPULATED", pt-st > - # Now create any of the tables for the join DataSets > - if cursorDict is not None: > - for alias, ds in cursorDict.items(): > - self._populate(ds, alias) > - > - # We have a table now with the necessary data. Run the query! > - self._cursor.execute(sqlExpr) > - > -# et = time.clock() > -# print "EXECUTED", et - pt > - # We need to know what sort of statement was run. Only a 'select' > - # will return results. The rest ('update', 'delete', 'insert') return > - # nothing. In those cases, we need to run a 'select *' to get the > - # modified data set. > - if not sqlExpr.lower().strip().startswith("select "): > - self._cursor.execute("select * from dataset") > - tmpres = self._cursor.fetchall() > - > -# ft = time.clock() > -# print "FETCH", ft-et > - return DataSet(tmpres) > - > -# res = [] > -# if tmpres: > -# # There will be no description if there are no records. > -# dscrp = [fld[0] for fld in self._cursor.description] > -# for tmprec in tmpres: > -# rec = {} > -# for pos, val in enumerate(tmprec): > -# fld = dscrp[pos] > -# if self.fieldAliases.has_key(fld): > -# fld = self.fieldAliases[fld] > -# rec[fld] = val > -# res.append(rec) > -# > -# dt = time.clock() > -# print "CONVERTED", dt-ft > - > - > - > - > - > - > - > > Added: trunk/dabo/db/dDataSet.py > =================================================================== > --- trunk/dabo/db/dDataSet.py 2006-06-29 15:33:18 UTC (rev 2242) > +++ trunk/dabo/db/dDataSet.py 2006-06-29 17:57:56 UTC (rev 2243) > @@ -0,0 +1,607 @@ > +from dabo.db.dMemento import dMemento > +from dabo.dLocalize import _ > +import datetime > + > +# Make sure that the user's installation supports Decimal. > +_USE_DECIMAL = True > +try: > + from decimal import Decimal > +except ImportError: > + _USE_DECIMAL = False > +try: > + from pysqlite2 import dbapi2 as sqlite > +except ImportError: > + msg = """ > + > +Dabo requires SQLite 3 and the pysqlite2 module. You will have to install these > +free products before running Dabo. You can get them from the following locations: > + > +SQLite: http://www.sqlite.org/download.html > +pysqlite2: http://initd.org/tracker/pysqlite > + > +""" > + sys.exit(msg) > + > + > + > +class dDataSet(tuple): > + """ This class assumes that its contents are not ordinary tuples, but > + rather tuples consisting of dicts, where the dict keys are field names. > + This is the data structure returned by the dCursorMixin class. > + > + It is used to give these data sets the ability to be queried, joined, etc. > + This is accomplished by using SQLite in-memory databases. If SQLite > + and pysqlite2 are not installed on the machine this is run on, a > + warning message will be printed out and the SQL functions will return > + None. The data will still be usable, though. > + """ > + def __init__(self, *args, **kwargs): > + super(dDataSet, self).__init__(*args, **kwargs) > + self._connection = None > + self._cursor = None > + self._populated = False > + # We may need to encode fields that are not legal names. > + self.fieldAliases = {} > + # Pickling mementos is slow. This dict will hold them > + # instead > + self._mementoHold = {} > + self._mementoSequence = 0 > + # Register the adapters > + sqlite.register_adapter(dMemento, self._adapt_memento) > + if _USE_DECIMAL: > + sqlite.register_adapter(Decimal, self._adapt_decimal) > + # When filtering datasets, we need a reference to the dataset > + # this dataset was derived from. > + self._sourceDataSet = None > + > + # Register the converters > + sqlite.register_converter("memento", self._convert_memento) > + if _USE_DECIMAL: > + sqlite.register_converter("decimal", self._convert_decimal) > + > + self._typeDict = {int: "integer", long: "integer", str: "text", > + unicode: "text", float: "real", datetime.date: "date", > + datetime.datetime: "timestamp", dMemento : "memento"} > + if _USE_DECIMAL: > + self._typeDict[Decimal] = "decimal" > + > + > + def __del__(self): > + if self._cursor is not None: > + self._cursor.close() > + if self._connection is not None: > + self._connection.close() > + > + > + def _adapt_decimal(self, decVal): > + """Converts the decimal value to a string for storage""" > + return str(decVal) > + > + > + def _convert_decimal(self, strval): > + """This is a converter routine. Takes the string > + representation of a Decimal value and return an actual > + decimal, if that module is present. If not, returns a float. > + """ > + if _USE_DECIMAL: > + ret = Decimal(strval) > + else: > + ret = float(strval) > + return ret > + > + > + def _adapt_memento(self, mem): > + """Substitutes a sequence for the memento for storage""" > + pos = self._mementoSequence > + self._mementoSequence += 1 > + self._mementoHold[pos] = mem > + return str(pos) > + > + > + def _convert_memento(self, strval): > + """Replaces the placeholder sequence with the actual memento.""" > + pos = int(strval) > + return self._mementoHold[pos] > + > + > + def replace(self, field, valOrExpr, scope=None): > + """Replaces the value of the specified field with the given value > + or expression. All records matching the scope are affected; if > + no scope is specified, all records are affected. > + > + 'valOrExpr' will be treated as a literal value, unless it is prefixed > + with an equals sign. All expressions will therefore be a string > + beginning with '='. Literals can be of any type. > + """ > + if scope is None: > + scope = "True" > + else: > + scope = self._fldReplace(scope, "rec") > + > + literal = True > + if isinstance(valOrExpr, basestring): > + if valOrExpr.strip()[0] == "=": > + literal = False > + valOrExpr = valOrExpr.replace("=", "", 1) > + valOrExpr = self._fldReplace(valOrExpr, "rec") > + for rec in self: > + if eval(scope): > + if literal: > + rec[field] = valOrExpr > + else: > + expr = "rec['%s'] = %s" % (field, valOrExpr) > + exec(expr) > + > + > + def sort(self, col, ascdesc=None, caseSensitive=None): > + if ascdesc is None: > + ascdesc = "ASC" > + casecollate = "" > + if caseSensitive is False: > + # The default of None will be case-sensitive > + casecollate = " COLLATE NOCASE " > + stmnt = "select * from dataset order by %s %s %s" > + stmnt = stmnt % (col, casecollate, ascdesc) > + ret = self.execute(stmnt) > + ret._sourceDataSet = self > + return ret > + > + > + def filter(self, fld, expr, op="="): > + """This takes a field name, an expression, and an optional operator, > + and returns a dataset that is filtered on that field by that expression. > + If the operator is specified, it will be used literally in the evaluation > + instead of the equals sign, unless it is one of the following strings, > + which will be interpreted as indicated: > + eq, equals: = > + gt: > > + gte: >= > + lt: < > + lte: <= > + startswith, beginswith: LIKE '<expr>%' > + endswith: LIKE '%<expr>' > + contains: LIKE '%<expr>%' > + """ > + opDict = {"eq": " = ?", > + "equals": " = ?", > + "gt": " > ?", > + "gte": " >= ?", > + "lt": " < ?", > + "lte": " <= ?", > + "startswith": " LIKE ? ", > + "beginswith": " LIKE ? ", > + "endswith": " LIKE ? ", > + "contains": " LIKE ? " } > + clause = opDict.get(op.lower(), " = ?") > + oplow = op.lower() > + if oplow in ("startswith", "beginswith"): > + param = "%s%%" % expr > + elif oplow == "endswith": > + param = "%%%s" % expr > + elif oplow == "contains": > + param = "%%%s%%" % expr > + else: > + param = expr > + stmnt = "select * from dataset where %s %s" % (fld, clause) > + ret = self.execute(stmnt, params=param) > + ret._sourceDataSet = self > + return ret > + > + > + def _fldReplace(self, expr, dictName=None): > + """The list comprehensions require the field names be the keys > + in a dictionary expression. Users, though, should not have to know > + about this. This takes a user-defined, SQL-like expressions, and > + substitutes any field name with the corresponding dict > + expression. > + """ > + keys = self[0].keys() > + patTemplate = "(.*\\b)%s(\\b.*)" > + ret = expr > + if dictName is None: > + dictName = self._dictSubName > + for kk in keys: > + pat = patTemplate % kk > + mtch = re.match(pat, ret) > + if mtch: > + ret = mtch.groups()[0] + "%s['%s']" % (dictName, kk) + mtch.groups()[1] > + return ret > + > + > + def _makeCreateTable(self, ds, alias=None): > + """Makes the CREATE TABLE string needed to represent > + this data set. There must be at least one record in the > + data set, or we can't get the necessary column info. > + """ > + if len(ds) == 0: > + return None > + if alias is None: > + # Use the default > + alias = "dataset" > + rec = ds[0] > + keys = rec.keys() > + retList = [] > + > + for key in keys: > + if key.startswith("dabo-"): > + # This is an internal field > + safekey = key.replace("-", "_") > + self.fieldAliases[safekey] = key > + else: > + safekey = key > + typ = type(rec[key]) > + try: > + retList.append("%s %s" % (safekey, ds._typeDict[typ])) > + except KeyError: > + retList.append(safekey) > + return "create table %s (%s)" % (alias, ", ".join(retList)) > + > + > + def _populate(self, ds, alias=None): > + """This is the method that converts a Python dataset > + into a SQLite table with the name specified by 'alias'. > + """ > + if alias is None: > + # Use the default > + alias = "dataset" > + if len(ds) == 0: > + # Can't create and populate a table without a structure > + dabo.errorLog.write(_("Cannot populate without data for alias %s") > + % alias) > + return None > + if ds._populated: > + # Data's already there; no need to re-load it > + return > + self._cursor.execute(self._makeCreateTable(ds, alias)) > + > + flds, vals = ds[0].keys(), ds[0].values() > + # Fields may contain illegal names. This will correct them > + flds = [fld.replace("dabo-", "dabo_") for fld in flds] > + fldParams = [":%s" % fld for fld in flds] > + fldCnt = len(flds) > + insStmnt = "insert into %s (%s) values (%s)" % (alias, > + ", ".join(flds), ", ".join(fldParams)) > + > + def recGenerator(ds): > + for rec in ds: > + yield rec > + > + self._cursor.executemany(insStmnt, recGenerator(ds)) > + if ds is self: > + self._populated = True > + > + > + def execute(self, sqlExpr, params=(), cursorDict=None): > + """This method allows you to work with a Python data set > + (i.e., a tuple of dictionaries) as if it were a SQL database. You > + can run any sort of statement that you can in a normal SQL > + database. It requires that SQLite and pysqlite2 are installed; > + if they aren't, this will return None. > + > + The SQL expression can be any standard SQL expression; however, > + the FROM clause should always be: 'from dataset', since these > + datasets do not have table names. > + > + If you want to do multi-dataset joins, you need to pass the > + additional DataSet objects in a dictionary, where the value is the > + DataSet, and the key is the alias used to reference that DataSet > + in your join statement. > + """ > + def dict_factory(cursor, row): > + dd = {} > + for idx, col in enumerate(cursor.description): > + dd[col[0]] = row[idx] > + return dd > + > + class DictCursor(sqlite.Cursor): > + def __init__(self, *args, **kwargs): > + sqlite.Cursor.__init__(self, *args, **kwargs) > + self.row_factory = dict_factory > + > + if self._connection is None: > + self._connection = sqlite.connect(":memory:", > + detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES, > + isolation_level="EXCLUSIVE") > + if self._cursor is None: > + self._cursor = self._connection.cursor(factory=DictCursor) > + > +# import time > +# st = time.clock() > +# print "starting" > + > + # Create the table for this dDataSet > + self._populate(self, "dataset") > + > +# pt = time.clock() > +# print "POPULATED", pt-st > + # Now create any of the tables for the join dDataSets > + if cursorDict is not None: > + for alias, ds in cursorDict.items(): > + self._populate(ds, alias) > + > + # We have a table now with the necessary data. Run the query! > + if params and not isinstance(params, tuple): > + params = (params, ) > + self._cursor.execute(sqlExpr, params) > + > +# et = time.clock() > +# print "EXECUTED", et - pt > + # We need to know what sort of statement was run. Only a 'select' > + # will return results. The rest ('update', 'delete', 'insert') return > + # nothing. In those cases, we need to run a 'select *' to get the > + # modified data set. > + if not sqlExpr.lower().strip().startswith("select "): > + self._cursor.execute("select * from dataset") > + tmpres = self._cursor.fetchall() > + > +# ft = time.clock() > +# print "FETCH", ft-et > + return dDataSet(tmpres) > + > +# res = [] > +# if tmpres: > +# # There will be no description if there are no records. > +# dscrp = [fld[0] for fld in self._cursor.description] > +# for tmprec in tmpres: > +# rec = {} > +# for pos, val in enumerate(tmprec): > +# fld = dscrp[pos] > +# if self.fieldAliases.has_key(fld): > +# fld = self.fieldAliases[fld] > +# rec[fld] = val > +# res.append(rec) > +# > +# dt = time.clock() > +# print "CONVERTED", dt-ft > + > + > + > + > +# class DataSetOld(tuple): > +# """ This class assumes that its contents are not ordinary tuples, but > +# rather tuples consisting of dicts, where the dict keys are field names. > +# This is the data structure returned by the dCursorMixin class. > +# """ > +# # List comprehensions used in this class require a non-conflicting > +# # name. This is unlikely to be used anywhere else. > +# _dictSubName = "_dataSet_rec" > +# > +# > +# def _fldReplace(self, expr, dictName=None): > +# """The list comprehensions require the field names be the keys > +# in a dictionary expression. Users, though, should not have to know > +# about this. This takes a user-defined, SQL-like expressions, and > +# substitutes any field name with the corresponding dict > +# expression. > +# """ > +# keys = self[0].keys() > +# patTemplate = "(.*\\b)%s(\\b.*)" > +# ret = expr > +# if dictName is None: > +# dictName = self._dictSubName > +# for kk in keys: > +# pat = patTemplate % kk > +# mtch = re.match(pat, ret) > +# if mtch: > +# ret = mtch.groups()[0] + "%s['%s']" % (dictName, kk) + mtch.groups()[1] > +# return ret > +# > +# > +# def processFields(self, fields, aliasDict): > +# if isinstance(fields, basestring): > +# fields = fields.split(",") > +# for num, fld in enumerate(fields): > +# fld = fld.replace(" AS ", " as ").replace(" As ", " as ").strip() > +# fa = fld.split(" as ") > +# if len(fa) > 1: > +# # An alias is specified > +# fld = fa[0].strip() > +# aliasDict[fld] = fa[1].strip() > +# fields[num] = fld > +# return fields, aliasDict > +# > +# > +# def select(self, fields=None, where=None, orderBy=None): > +# fldList = [] > +# fldAliases = {} > +# whereList = [] > +# orderByList = [] > +# keys = self[0].keys() > +# if fields is None or fields == "*": > +# # All fields > +# fields = keys > +# fields, fldAliases = self.processFields(fields, fldAliases) > +# for fld in fields: > +# fldList.append("'%s' : %s" % (fld, self._fldReplace(fld))) > +# fieldsToReturn = ", ".join(fldList) > +# fieldsToReturn = "{%s}" % fieldsToReturn > +# > +# # Where list elements > +# if where is None: > +# whereClause = "" > +# else: > +# if isinstance(where, basestring): > +# where = [where] > +# for wh in where: > +# whereList.append(self._fldReplace(wh)) > +# whereClause = " and ".join(whereList) > +# if whereClause: > +# whereClause = " if %s" % whereClause > +# stmnt = "[%s for %s in self %s]" % (fieldsToReturn, self._dictSubName, whereClause) > +# resultSet = eval(stmnt) > +# > +# if fldAliases: > +# # We need to replace the keys for the field names with the > +# # appropriate alias names > +# for rec in resultSet: > +# for key, val in fldAliases.items(): > +# orig = rec.get(key) > +# if orig: > +# rec[val] = orig > +# del rec[key] > +# > +# if orderBy: > +# # This should be a comma separated string in the format: > +# # fld1, fld2 desc, fld3 asc > +# # After the field name is an optional direction, either 'asc' > +# # (ascending, default) or 'desc' (descending). > +# # IMPORTANT! Fields referenced in 'orderBy' MUST be in > +# # the result data set! > +# orderByList = orderBy.split(",") > +# sortList = [] > +# > +# def orderBySort(val1, val2): > +# ret = 0 > +# compList = orderByList[:] > +# while not ret: > +# comp = compList[0] > +# compList = compList[1:] > +# if comp[-4:].lower() == "desc": > +# compVals = (-1, 1) > +# else: > +# compVals = (1, -1) > +# # Remove the direction, if any, from the comparison. > +# compWords = comp.split(" ") > +# if compWords[-1].lower() in ("asc", "desc"): > +# compWords = compWords[:-1] > +# comp = " ".join(compWords) > +# cmp1 = self._fldReplace(comp, "val1") > +# cmp2 = self._fldReplace(comp, "val2") > +# eval1 = eval(cmp1) > +# eval2 = eval(cmp2) > +# if eval1 > eval2: > +# ret = compVals[0] > +# elif eval1 < eval2: > +# ret = compVals[1] > +# else: > +# # They are equal. Continue comparing using the > +# # remaining terms in compList, if any. > +# if not compList: > +# break > +# return ret > +# > +# resultSet.sort(orderBySort) > +# > +# return DataSet(resultSet) > +# > +# > +# def join(self, target, sourceAlias, targetAlias, condition, > +# sourceFields=None, targetFields=None, where=None, > +# orderBy=None, joinType=None): > +# """This method joins the current DataSet and the target > +# DataSet, based on the specified condition. The 'joinType' > +# parameter will determine the type of join (inner, left, right, full). > +# Where and orderBy will affect the result of the join, and so they > +# should reference fields in the result set without alias qualifiers. > +# """ > +# if joinType is None: > +# joinType = "inner" > +# joinType = joinType.lower().strip() > +# if joinType == "outer": > +# # This is the same as 'left outer' > +# joinType = "left" > +# if "outer" in joinType.split(): > +# tmp = joinType.split() > +# tmp.remove("outer") > +# joinType = tmp[0] > +# > +# leftDS = self > +# rightDS = target > +# leftAlias = sourceAlias > +# rightAlias = targetAlias > +# leftFields = sourceFields > +# rightFields = targetFields > +# leftFldAliases = {} > +# rightFldAliases = {} > +# if joinType == "right": > +# # Same as left; we just need to reverse things > +# (leftDS, rightDS, leftAlias, rightAlias, leftFields, > +# rightFields) = (rightDS, leftDS, rightAlias, leftAlias, > +# rightFields, leftFields) > +# > +# > +# leftFields, leftFldAliases = self.processFields(leftFields, leftFldAliases) > +# rightFields, rightFldAliases = self.processFields(rightFields, rightFldAliases) > +# > +# # Parse the condition. It should have an '==' in it. If not, > +# # raise an error. > +# condList = condition.split("==") > +# if len(condList) == 1: > +# # No equality specified > +# errMsg = _("Bad join: no '==' in join condition: %s") % condition > +# raise dException.QueryException, errMsg > +# > +# leftCond = None > +# rightCond = None > +# leftPat = "(.*)(\\b%s\\b)(.*)" % leftAlias > +# rightPat = "(.*)(\\b%s\\b)(.*)" % rightAlias > +# > +# mtch = re.match(leftPat, condList[0]) > +# if mtch: > +# leftCond = condList[0].strip() > +# else: > +# mtch = re.match(leftPat, condList[1]) > +# if mtch: > +# leftCond = condList[1].strip() > +# mtch = re.match(rightPat, condList[0]) > +# if mtch: > +# rightCond = condList[0].strip() > +# else: > +# mtch = re.match(rightPat, condList[1]) > +# if mtch: > +# rightCond = condList[1].strip() > +# condError = "" > +# if leftCond is None: > +# condError += _("No join condition specified for alias '%s'") % leftAlias > +# if rightCond is None: > +# if condError: > +# condError += "; " > +# c > > (1946 bytes were truncated as it was too long for the email (max 40000 bytes.) > > _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
