Changeset 812

Timestamp:

03/08/07 22:49:41 (2 years ago)

Author:

mfenniak

Message:

Complete prepared statement work

Files:

• pg8000/trunk/pg8000.py (modified) (14 diffs)
• pg8000/trunk/pg8000-test.py (modified) (2 diffs)

pg8000/trunk/pg8000.py

@@ -68 +68 @@
-
-class DataIterator(object):
-
-
-
-
-
-
+
+
+
-
-    def __iter__(self):
@@ -79 +76 @@
-
-    def next(self):
-method(self.connection
+func(self.obj
-        if retval == None:
-            raise StopIteration()
-        return retval
-
+##
+# This class represents a prepared statement.  A prepared statement is
+# pre-parsed on the server, which reduces the need to parse the query every
+# time it is run.  The statement can have parameters in the form of $1, $2, $3,
+# etc.  When parameters are used, the types of the parameters need to be
+# specified when creating the prepared statement.
+# <p>
+# Stability: Added in v1.00, stability guaranteed for v1.xx.
+#
+# @param connection     An instance of {@link Connection Connection}.
+#
+# @param statement      The SQL statement to be represented, often containing
+# parameters in the form of $1, $2, $3, etc.
+#
+# @param types          Python type objects for each parameter in the SQL
+# statement.  For example, int, float, str.
-class PreparedStatement(object):
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
-    row_cache_size = 100
-
@@ -105 +120 @@
-        self._parse_row_desc = self.c.parse(self._statement_name, statement, types)
-
+    def __del__(self):
+        # This __del__ should work with garbage collection / non-instant
+        # cleanup.  It only really needs to be called right away if the same
+        # object id (and therefore the same statement name) might be reused
+        # soon, and clearly that wouldn't happen in a GC situation.
+        self.c.close_statement(self._statement_name)
+
+    ##
+    # Run the SQL prepared statement with the given parameters.
+    # <p>
+    # Stability: Added in v1.00, stability guaranteed for v1.xx.
-    def execute(self, *args):
-        if not self._command_complete:
-            # cleanup last execute
-            self._cached_rows = []
-
+_portal
-        self._command_complete = False
-        self._row_desc = self.c.bind(self._portal_name, self._statement_name, args, self._parse_row_desc)
+        if self._row_desc:
+            # We execute our cursor right away to fill up our cache.  This
+            # prevents the cursor from being destroyed, apparently, by a rogue
+            # Sync between Bind and Execute.  Since it is quite likely that
+            # data will be read from us right away anyways, this seems a safe
+            # move for now.
+            self._fill_cache()
+
+    def _fill_cache(self):
+        if self._cached_rows:
+            raise InternalError("attempt to fill cache that isn't empty")
+        end_of_data, rows = self.c.fetch_rows(self._portal_name, self.row_cache_size, self._row_desc)
+        self._cached_rows = rows
+        if end_of_data:
+            self._command_complete = True
-
-    def _fetch(self):
@@ -117 +158 @@
-            if self._command_complete:
-                return None
-
-
-
-
-
-
-
-
+
+
+
+
+
+
-        row = self._cached_rows[0]
-        del self._cached_rows[0]
@@ -132 +171 @@
-    # Read a row from the database server, and return it in a dictionary
-    # indexed by column name/alias.  This method will raise an error if two
-
+  Returns None after the last row.
-    # <p>
-    # Stability: Added in v1.00, stability guaranteed for v1.xx.
@@ -149 +188 @@
-    ##
-    # Read a row from the database server, and return it as a tuple of values.
+    # Returns None after the last row.
-    # <p>
-    # Stability: Added in v1.00, stability guaranteed for v1.xx.
@@ -158 +198 @@
-
-    ##
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-class Cursor(object):
-    def __init__(self, connection):
@@ -172 +230 @@
-        self._stmt = None
-
+    ##
+    # Run an SQL statement using this cursor.  The SQL statement can have
+    # parameters in the form of $1, $2, $3, etc., which will be filled in by
+    # the additional arguments passed to this function.
+    # <p>
+    # Stability: Added in v1.00, stability guaranteed for v1.xx.
+    # @param query      The SQL statement to execute.
-    def execute(self, query, *args):
-        self._stmt = PreparedStatement(self.connection, query, *[type(x) for x in args])
-        self._stmt.execute(*args)
-
+    ##
+    # Read a row from the database server, and return it in a dictionary
+    # indexed by column name/alias.  This method will raise an error if two
+    # columns have the same name.  Returns None after the last row.
+    # <p>
+    # Stability: Added in v1.00, stability guaranteed for v1.xx.
-    def read_dict(self):
-        if self._stmt == None:
-eturn None
+aise ProgrammingError("attempting to read from unexecuted cursor")
-        return self._stmt.read_dict()
-
+    ##
+    # Read a row from the database server, and return it as a tuple of values.
+    # Returns None after the last row.
+    # <p>
+    # Stability: Added in v1.00, stability guaranteed for v1.xx.
-    def read_tuple(self):
-        if self._stmt == None:
-eturn None
+aise ProgrammingError("attempting to read from unexecuted cursor")
-        return self._stmt.read_tuple()
-
+    ##
+    # Return an iterator for the output of this statement.  The iterator will
+    # return a tuple for each row, in the same manner as {@link
+    # #PreparedStatement.read_tuple read_tuple}.
+    # <p>
+    # Stability: Added in v1.00, stability guaranteed for v1.xx.
+    def iterate_tuple(self):
+        if self._stmt == None:
+            raise ProgrammingError("attempting to read from unexecuted cursor")
+        return self._stmt.iterate_tuple()
+
+    ##
+    # Return an iterator for the output of this statement.  The iterator will
+    # return a dict for each row, in the same manner as {@link
+    # #PreparedStatement.read_dict read_dict}.
+    # <p>
+    # Stability: Added in v1.00, stability guaranteed for v1.xx.
+    def iterate_dict(self):
+        if self._stmt == None:
+            raise ProgrammingError("attempting to read from unexecuted cursor")
+        return self._stmt.iterate_dict()
-
-##
-# This class represents a connection to a PostgreSQL database.
-# <p>
- and
-provides access to the database's unnamed cursor through the standard Cursor
-methods.  It also provides transaction control via the 'begin', 'commit', and
-'rollback' methods.  Without beginning a transaction explicitly, all
-statements will autocommit to 
+
+which provides a default cursor for running queries.  It also provides
+transaction control via the 'begin', 'commit', and 'rollback' methods.
+Without beginning a transaction explicitly, all statements will autocommit to
+
-# <p>
-# Stability: Added in v1.00, stability guaranteed for v1.xx.
@@ -221 +318 @@
-# Defaults to 60 seconds.
-class Connection(Cursor):
-
-    ##
-    # A configuration variable that determines whether iterating over the
-    # connection will return tuples of queried rows (False), or dictionaries
-    # indexed by column name/alias (True).  By default, this variable is set to
-    # False.
-    # <p>
-    # Stability: Added in v1.00, stability guaranteed for v1.xx.
-    iterate_dicts = False
-
-    def __init__(self, host, user, port=5432, database=None, password=None, socket_timeout=60):
-        self._row_desc = None
@@ -240 +327 @@
-            raise InterfaceError("communication error", e)
-        Cursor.__init__(self, self)
-
+
+
+
+
+
+
+
+
-    def begin(self):
-
-
+
+
+
+
+
+
-    def commit(self):
-
-
+
+
+
+
+
+
-    def rollback(self):
-raise NotSupportedError("uncoded"
+self._rollback.execute(
-
-
@@ -600 +702 @@
-
-        def _send(self, msg):
-
+
+
+
-
-        def _read_message(self):
@@ -607 +711 @@
-            message_code = bytes[0]
-            data_len = struct.unpack("!i", bytes[1:])[0] - 4
-
+
+
+
+
-            msg = Protocol.message_types[message_code].createFromData(bytes)
-            if isinstance(msg, Protocol.NoticeResponse):
@@ -687 +794 @@
-                output_fc = [Types.py_type_info(f) for f in row_desc.fields]
-            self._send(Protocol.Bind(portal, statement, param_fc, params, output_fc, self._client_encoding))
-
-
+
+
+
-            self._send(Protocol.DescribePortal(portal))
-            self._send(Protocol.Flush())
@@ -762 +870 @@
-            return end_of_data, rows
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-            self._send(Protocol.ClosePortal(portal))
-            self._send(Protocol.Sync())

pg8000/trunk/pg8000-test.py

@@ -8 +8 @@
-#db = pg8000.Connection(host='joy.fenniak.net', user='Mathieu Fenniak', database="software", password="hello", socket_timeout=5)
-db = pg8000.Connection(host='localhost', user='mfenniak')
-db.iterate_dicts = True
-
-
-
-
-
-
-
-
-
+
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
-
-print "begin query..."
+db.execute("SELECT * FROM t1")
+for row in db.iterate_dict():
+    print repr(row)
+print "end query..."
+
+print "begin query..."
+cur1 = pg8000.Cursor(db)
-cur1.execute("SELECT * FROM t1")
+s1 = pg8000.PreparedStatement(db, "SELECT * FROM t1 WHERE f1 > $1", int)
-i = 0
-
+.iterate_dict()
-    i = i + 1
-    print i, repr(row1)
-db.execute("SELECT * FROM t1 WHERE f1 > $1", 
-db
+s1.execute(
+s1.iterate_dict()
-        print "\t", repr(row2)
-print "end query..."
@@ -47 +42 @@
-
-cur1.execute("SELECT $1", 5)
-
+.iterate_dict()
-
-cur1.execute("SELECT 5000::smallint")
-
+.iterate_dict()
-
-cur1.execute("SELECT 5000::integer")
-
+.iterate_dict()
-
-cur1.execute("SELECT 50000000000000::bigint")
-
+.iterate_dict()
-
-cur1.execute("SELECT 5000.023232::decimal")
-
+.iterate_dict()
-
-cur1.execute("SELECT 1.1::real")
-
+.iterate_dict()
-
-cur1.execute("SELECT 1.1::double precision")
-
+.iterate_dict()
-
-cur1.execute("SELECT 'hello'::varchar(50)")
-
+.iterate_dict()
-
-cur1.execute("SELECT 'hello'::char(20)")
-
+.iterate_dict()
-
-cur1.execute("SELECT 'hello'::text")
-
+.iterate_dict()
-
-#cur1.execute("SELECT 'hell\007o'::bytea")
-
+.iterate_dict()
-
-cur1.execute("SELECT '2001-02-03 04:05:06.17'::timestamp")
-
+.iterate_dict()
-
-#cur1.execute("SELECT '2001-02-03 04:05:06.17'::timestamp with time zone")
-
+.iterate_dict()
-
-cur1.execute("SELECT '1 month'::interval")
-
-
+.iterate_dict()
+.iterate_dict()
-
-print "Type checks complete."