Changeset 6

Timestamp:

02/12/07 22:34:25

Author:

fumanchu

Message:

Added 'pytype' attribute to Columns:

1. Added select_all, select_one methods to Table (which handle imperfect sql).
   
2. Database.insert now coerces returned ID's to Python types.
   
3. Database.select now returns an iterable (a ResultSet? object) that has an "imperfect" attribute.
   
4. Replaced SelectWriter?.colnames with SelectWriter?.columns, a list of 3-tuples: (table, col, qname).
   

Also renamed Database.col_def to "columnclause".

Files:

• trunk/geniusql/__init__.py (modified) (15 diffs)
• trunk/geniusql/providers/ado.py (modified) (4 diffs)
• trunk/geniusql/providers/firebird.py (modified) (5 diffs)
• trunk/geniusql/providers/mysql.py (modified) (4 diffs)
• trunk/geniusql/providers/psycopg.py (modified) (2 diffs)
• trunk/geniusql/providers/pypgsql.py (modified) (2 diffs)
• trunk/geniusql/providers/sqlite.py (modified) (3 diffs)
• trunk/geniusql/select.py (modified) (2 diffs)
• trunk/geniusql/test/zoo_fixture.py (modified) (1 diff)

trunk/geniusql/__init__.py

@@ -129 +129 @@
-    name: the SQL name for this table (unquoted).
-    qname: the SQL name for this table (quoted).
+    pytype: the Python type (the actual type object, not its name).
-    dbtype: the database type name (as used in a CREATE TABLE statement).
-
+Python 
-    hints: a dict of implementation hints, such as precision, scale, or bytes.
-    key: True if this column is part of the table's primary key.
@@ -143 +144 @@
-    """
-    
-
+pytype, 
-                 name=None, qname=None):
+        self.pytype = pytype
-        self.dbtype = dbtype
-        self.name = name
@@ -164 +166 @@
-    
-    def __repr__(self):
-btype=%r, default=%r, hints=%r, key
+efault=%r, hints=%r, key=%r, name=%r, qname
-                (self.__module__, self.__class__.__name__,
-
-
+
+
+
-                )
-    
-    def __copy__(self):
-
-
+
+
+
-        newcol.autoincrement = self.autoincrement
-        newcol.initial = self.initial
@@ -245 +249 @@
-    def _add_column(self, column):
-        """Internal function to add the column to the database."""
-_def
+umnclause
-        self.db.execute("ALTER TABLE %s ADD COLUMN %s;" % (self.qname, coldef))
-    
@@ -337 +341 @@
-        self.indices[columnkey] = i
-        return i
+    
+    def select_all(self, restriction=None):
+        """Yield data dicts matching the given restriction."""
+        attrs = self.keys()
+        data = self.db.select(self, attrs, restriction)
+        for row in data:
+            row = dict(zip(attrs, row))
+            if restriction and data.imperfect:
+                # Run a dummy object through our restriction before yielding.
+                if not restriction(ImperfectDummy(**row)):
+                    continue
+            yield row
+    
+    def select_one(self, restriction=None):
+        """Return a single data dict matching the given restriction (or None)."""
+        try:
+            return self.select_all(restriction).next()
+        except StopIteration:
+            return None
+
+
+class ImperfectDummy(object):
+    """A dummy object for resolving imperfect queries."""
+    def __init__(self, **kwargs):
+        for k, v in kwargs.iteritems():
+            setattr(self, k, v)
-
-
@@ -362 +392 @@
-    
-    selectwriter = SelectWriter
-    def select(self, *args, **kwargs):
-        return self.selectwriter(self, *args, **kwargs)
-    
-    tableclass = Table
-    
@@ -536 +563 @@
-    #                              Container                              #
-    
-_def
+umnclause
-        """Return a clause for the given column for CREATE or ALTER TABLE.
-        
@@ -581 +608 @@
-                    self.create_sequence(table, column)
-                
-_def
+umnclause
-                if column.key:
-                    pk.append(column.qname)
@@ -671 +698 @@
-        return self.sql_name(columnkey)
-    
-
+btype=None, d
-               key=False, autoincrement=False):
-        """Return a Column object from the given arguments."""
-
-
+
+
+
+
+
+
-        pytype2 = self.python_type(col.dbtype)
-        col.imperfect_type = not self.isrelatedtype(pytype, pytype2)
-
-
+
-        return col
-    
@@ -720 +750 @@
-    
-    def execute(self, query, conn=None):
-execute(query, conn=None) -> result set
+Return a native response for the given query
-        if conn is None:
-            conn = self.connection()
@@ -739 +769 @@
-        res = self.execute(query, conn)
-        return res.row_list, res.col_defs
+    
+    def select(self, relation, attributes, restriction=None, distinct=False):
+        """Yield matching data, coerced to Python types (where known)."""
+        sel = self.selectwriter(self, relation, attributes, restriction)
+        data, _ = self.fetch(sel.sql(distinct), self.get_transaction())
+        return ResultSet(data, sel.columns, sel.imperfect)
-    
-    def create_database(self):
@@ -919 +955 @@
-        """Insert a row and return {idcolkey: newid}."""
-        t = self[tablekey]
+        coerce_out = self.adaptertosql.coerce
+        coerce_in = self.adapterfromdb.coerce
-        
-        fields = []
@@ -929 +967 @@
-                continue
-            if key in inputs:
-self.adaptertosql.coerce
+coerce_out
-                fields.append(col.qname)
-                values.append(val)
@@ -938 +976 @@
-        values = ", ".join(values)
-        self.execute('INSERT INTO %s (%s) VALUES (%s);' %
-   
+
-        
-        if idkeys:
-
+
+
+
+
+
-        else:
-            return {}
-    
-    def _grab_new_ids(self, table, idkeys):
+        # Override this to fetch and return new autoincrement values.
-        raise NotImplementedError
-    
@@ -968 +1011 @@
-            self.execute(sql, self.get_transaction())
-
+
+class ResultSet:
+    
+    def __init__(self, data, columns, imperfect):
+        self.data = data
+        self.columns = columns
+        self.imperfect = imperfect
+        self.cursor = 0
+    
+    def __iter__(self):
+        return self
+    
+    def next(self):
+        try:
+            row = self.data[self.cursor]
+            self.cursor += 1
+        except IndexError:
+            raise StopIteration
+        
+        coerced_row = []
+        for i, (table, col, qname) in enumerate(self.columns):
+            val = row[i]
+            if table and col:
+                val = table.db.adapterfromdb.coerce(val, col.dbtype, col.pytype)
+            coerced_row.append(val)
+        return coerced_row
+

trunk/geniusql/providers/ado.py

@@ -363 +363 @@
-    def _add_column(self, column):
-        """Internal function to add the column to the database."""
-_def
+umnclause
-        # SQL Server doesn't use the "COLUMN" keyword with "ADD"
-        self.db.execute("ALTER TABLE %s ADD %s;" % (self.qname, coldef))
@@ -473 +473 @@
-            
-            name = str(row[3])
-
-
+
+
+
-            
-            # This only works for SQL Server. The MSAccessDatabase will
@@ -893 +894 @@
-            self.unlock()
-    
-_def
+umnclause
-        """Return a clause for the given column for CREATE or ALTER TABLE.
-        
@@ -1111 +1112 @@
-        return ADODatabase.python_type(self, dbtype)
-    
-_def
+umnclause
-        """Return a clause for the given column for CREATE or ALTER TABLE.
-        

trunk/geniusql/providers/firebird.py

@@ -241 +241 @@
-    def _add_column(self, column):
-        """Internal function to add the column to the database."""
-_def
+umnclause
-        # FB doesn't recognize the keyword "COLUMN" in "ADD".
-        self.db.execute("ALTER TABLE %s ADD %s;" % (self.qname, coldef))
@@ -362 +362 @@
-                    default = float(default)
-            
-
-
+
+
+
-            cols.append(col)
-        return cols
@@ -416 +417 @@
-                        "to a Python type." % dbtype)
-    
-_def
+umnclause
-        """Return a clause for the given column for CREATE or ALTER TABLE.
-        
@@ -660 +661 @@
-        """Insert a row and return {idcolkey: newid}."""
-        t = self[tablekey]
+        coerce_out = self.adaptertosql.coerce
+        coerce_in = self.adapterfromdb.coerce
-        
-        newids = {}
@@ -669 +672 @@
-                data, _ = self.fetch("SELECT GEN_ID(%s, 1) FROM RDB$DATABASE;"
-                                     % col.sequence_name)
-val, = data[0]
-val
+newid = coerce_in(data[0][0], col.dbtype, col.pytype)
+newid
-                
-self.adaptertosql.coerce(val
+coerce_out(newid
-                fields.append(col.qname)
-                values.append(val)
-            elif key in inputs:
-self.adaptertosql.coerce
+coerce_out
-                fields.append(col.qname)
-                values.append(val)

trunk/geniusql/providers/mysql.py

@@ -215 +215 @@
-        return "MySQL Version: %s" % self._version
-    
-_def
+umnclause
-        """Return a clause for the given column for CREATE or ALTER TABLE.
-        
@@ -246 +246 @@
-        pk = []
-        for colkey, col in table.iteritems():
-_def
+umnclause
-            
-            if col.autoincrement:
@@ -324 +324 @@
-        cols = []
-        for row in data:
-
-
+
-            dbtype = row[1].upper()
-            parenpos = dbtype.find("(")
@@ -333 +332 @@
-                if baretype in ("DECIMAL", "NUMERIC"):
-                    args = [x.strip() for x in args.split(",")]
-c.hints['precision'], c.
+hints['precision'], 
-                else:
-c.
+
-            elif dbtype == "FLOAT":
-c.
+
-            elif dbtype.startswith("DOUBLE"):
-c.
+
-            elif dbtype in ("TINYBLOB", "TINYTEXT"):
-c.
+
-            elif dbtype in ("BLOB", "TEXT"):
-c.
+
-            elif dbtype in ("MEDIUMBLOB", "MEDIUMTEXT"):
-c.
+
-            elif dbtype in ("LONGBLOB", "LONGTEXT"):
-
-
-
+
+
+
+
+
+
+
-            
-            if row[4]:
-
-
+
-            if "auto_increment" in row[5].lower():
-
-
-
-
-
+
+
+
-        return cols
-    

trunk/geniusql/providers/psycopg.py

@@ -216 +216 @@
-            else:
-                dbtype = None
-
-
+
+
+
-            
-            if dbtype in ('FLOAT4', 'FLOAT8'):
@@ -315 +316 @@
-                        "to a Python type." % dbtype)
-    
-_def
+umnclause
-        """Return a clause for the given column for CREATE or ALTER TABLE.
-        

trunk/geniusql/providers/pypgsql.py

@@ -203 +203 @@
-            else:
-                dbtype = None
-
-
+
+
+
-            
-            if dbtype in ('FLOAT4', 'FLOAT8'):
@@ -302 +303 @@
-                        "to a Python type." % dbtype)
-    
-_def
+umnclause
-        """Return a clause for the given column for CREATE or ALTER TABLE.
-        

trunk/geniusql/providers/sqlite.py

@@ -527 +527 @@
-        cols = []
-        for row in data:
-
-
+
+
+
+
-            
-            # "A single row can hold up to 2 ** 30 bytes of data
@@ -621 +623 @@
-        return {idkeys[0]: new_id}
-    
-_def
+umnclause
-        """Return a clause for the given column for CREATE or ALTER TABLE.
-        
@@ -652 +654 @@
-        autoincr_col = None
-        for col in table.itervalues():
-_def
+umnclause
-            
-            if col.autoincrement:

trunk/geniusql/select.py

@@ -510 +510 @@
-        self.whereclause, self.imperfect = self.where()
-        
+        self.columns = []
-        if self.attributes is None:
-            # Return all columns
-            self.colnames = []
-            for t in self.tables:
-names.extend(self.
+umns.extend(self._get_
-        else:
-            if isinstance(relation, Join):
-                self.colnames = []
-                for t, attrs in zip(self.tables, self.attributes):
-
-
-
-
-
-
+
+
+
+
+
+
+
+
-        tablename = tablewrapper.alias or tablewrapper.qname
+        table = tablewrapper.table
-        if attrs is None:
-            # Place the identifier (primary key) properties first
-            # in case others depend upon them.
-
-
+
-            cols.sort()
-
-
-
-
-
+
+
+
+
+
+
+
+
-    
-    def sql(self, distinct=False):
@@ -545 +549 @@
-            d = ''
-        
-self.colnames
+[qname for t, c, qname in self.columns]
-        
-        w = self.whereclause

trunk/geniusql/test/zoo_fixture.py

@@ -257 +257 @@
-    def test_3_Properties(self):
-        # Zoos
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-##    def test_4_Expressions(self):
-##        box = arena.new_sandbox()