/doc/html/cursor.html
HTML | 637 lines | 600 code | 37 blank | 0 comment | 0 complexity | 2a937c81f5b34b3527a2bf9077f37e4a MD5 | raw file
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
- "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
- <html xmlns="http://www.w3.org/1999/xhtml">
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
-
- <title>The cursor class — Psycopg v2.4.2 documentation</title>
- <link rel="stylesheet" href="_static/psycopg.css" type="text/css" />
- <link rel="stylesheet" href="_static/pygments.css" type="text/css" />
- <script type="text/javascript">
- var DOCUMENTATION_OPTIONS = {
- URL_ROOT: '',
- VERSION: '2.4.2',
- COLLAPSE_INDEX: false,
- FILE_SUFFIX: '.html',
- HAS_SOURCE: true
- };
- </script>
- <script type="text/javascript" src="_static/jquery.js"></script>
- <script type="text/javascript" src="_static/underscore.js"></script>
- <script type="text/javascript" src="_static/doctools.js"></script>
- <link rel="top" title="Psycopg v2.4.2 documentation" href="index.html" />
- <link rel="next" title="More advanced topics" href="advanced.html" />
- <link rel="prev" title="The connection class" href="connection.html" />
- </head>
- <body>
- <div class="related">
- <h3>Navigation</h3>
- <ul>
- <li class="right" style="margin-right: 10px">
- <a href="genindex.html" title="General Index"
- accesskey="I">index</a></li>
- <li class="right" >
- <a href="py-modindex.html" title="Python Module Index"
- >modules</a> |</li>
- <li class="right" >
- <a href="advanced.html" title="More advanced topics"
- accesskey="N">next</a> |</li>
- <li class="right" >
- <a href="connection.html" title="The connection class"
- accesskey="P">previous</a> |</li>
- <li><a href="index.html">Psycopg v2.4.2 documentation</a> »</li>
- </ul>
- </div>
- <div class="document">
- <div class="documentwrapper">
- <div class="bodywrapper">
- <div class="body">
-
- <div class="section" id="the-cursor-class">
- <h1>The <tt class="docutils literal"><span class="pre">cursor</span></tt> class<a class="headerlink" href="#the-cursor-class" title="Permalink to this headline">¶</a></h1>
- <dl class="class">
- <dt id="cursor">
- <em class="property">class </em><tt class="descname">cursor</tt><a class="headerlink" href="#cursor" title="Permalink to this definition">¶</a></dt>
- <dd><p>Allows Python code to execute PostgreSQL command in a database session.
- Cursors are created by the <a class="reference internal" href="connection.html#connection.cursor" title="connection.cursor"><tt class="xref py py-obj docutils literal"><span class="pre">connection.cursor()</span></tt></a> method: they are
- bound to the connection for the entire lifetime and all the commands are
- executed in the context of the database session wrapped by the connection.</p>
- <p>Cursors created from the same connection are not isolated, i.e., any
- changes done to the database by a cursor are immediately visible by the
- other cursors. Cursors created from different connections can or can not
- be isolated, depending on the connections’ <a class="reference internal" href="usage.html#transactions-control"><em>isolation level</em></a>. See also <a class="reference internal" href="connection.html#connection.rollback" title="connection.rollback"><tt class="xref py py-obj docutils literal"><span class="pre">rollback()</span></tt></a> and
- <a class="reference internal" href="connection.html#connection.commit" title="connection.commit"><tt class="xref py py-obj docutils literal"><span class="pre">commit()</span></tt></a> methods.</p>
- <p>Cursors are <em>not</em> thread safe: a multithread application can create
- many cursors from the same connection and should use each cursor from
- a single thread. See <a class="reference internal" href="usage.html#thread-safety"><em>Thread and process safety</em></a> for details.</p>
- <dl class="attribute">
- <dt id="cursor.description">
- <tt class="descname">description</tt><a class="headerlink" href="#cursor.description" title="Permalink to this definition">¶</a></dt>
- <dd><p>This read-only attribute is a sequence of 7-item sequences.</p>
- <p>Each of these sequences is a named tuple (a regular tuple if
- <a class="reference external" href="http://docs.python.org/3.2/library/collections.html#collections.namedtuple" title="(in Python v3.2)"><tt class="xref py py-func docutils literal"><span class="pre">collections.namedtuple()</span></tt></a> is not available) containing information
- describing one result column:</p>
- <ol class="arabic simple" start="0">
- <li><tt class="xref py py-obj docutils literal"><span class="pre">name</span></tt>: the name of the column returned.</li>
- <li><tt class="xref py py-obj docutils literal"><span class="pre">type_code</span></tt>: the PostgreSQL OID of the column. You can use the
- <a class="reference external" href="http://www.postgresql.org/docs/9.0/static/catalog-pg-type.html"><tt class="sql docutils literal"><span class="pre">pg_type</span></tt></a> system table to get more informations about the type.
- This is the value used by Psycopg to decide what Python type use
- to represent the value. See also
- <a class="reference internal" href="advanced.html#type-casting-from-sql-to-python"><em>Type casting of SQL types into Python objects</em></a>.</li>
- <li><tt class="xref py py-obj docutils literal"><span class="pre">display_size</span></tt>: the actual length of the column in bytes.
- Obtaining this value is computationally intensive, so it is
- always <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt> unless the <span class="target" id="index-0"></span><tt class="xref std std-envvar docutils literal"><span class="pre">PSYCOPG_DISPLAY_SIZE</span></tt> parameter
- is set at compile time. See also <a class="reference external" href="http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-PQGETLENGTH">PQgetlength</a>.</li>
- <li><tt class="xref py py-obj docutils literal"><span class="pre">internal_size</span></tt>: the size in bytes of the column associated to
- this column on the server. Set to a negative value for
- variable-size types See also <a class="reference external" href="http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-PQFSIZE">PQfsize</a>.</li>
- <li><tt class="xref py py-obj docutils literal"><span class="pre">precision</span></tt>: total number of significant digits in columns of
- type <a class="reference external" href="http://www.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL"><tt class="sql docutils literal"><span class="pre">NUMERIC</span></tt></a>. <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt> for other types.</li>
- <li><tt class="xref py py-obj docutils literal"><span class="pre">scale</span></tt>: count of decimal digits in the fractional part in
- columns of type <tt class="sql docutils literal"><span class="pre">NUMERIC</span></tt>. <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt> for other types.</li>
- <li><tt class="xref py py-obj docutils literal"><span class="pre">null_ok</span></tt>: always <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt> as not easy to retrieve from the libpq.</li>
- </ol>
- <p>This attribute will be <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt> for operations that do not return rows
- or if the cursor has not had an operation invoked via the
- <a class="reference internal" href="#execute"><tt class="xref py py-obj docutils literal"><span class="pre">execute*()</span></tt></a> methods yet.</p>
- <p class="versionchanged">
- <span class="versionmodified">Changed in version 2.4: </span>if possible, columns descriptions are named tuple instead of
- regular tuples.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.close">
- <tt class="descname">close</tt><big>(</big><big>)</big><a class="headerlink" href="#cursor.close" title="Permalink to this definition">¶</a></dt>
- <dd><p>Close the cursor now (rather than whenever <tt class="xref py py-obj docutils literal"><span class="pre">del</span></tt> is executed).
- The cursor will be unusable from this point forward; an
- <a class="reference internal" href="module.html#psycopg2.InterfaceError" title="psycopg2.InterfaceError"><tt class="xref py py-obj docutils literal"><span class="pre">InterfaceError</span></tt></a> will be raised if any operation is
- attempted with the cursor.</p>
- </dd></dl>
- <dl class="attribute">
- <dt id="cursor.closed">
- <tt class="descname">closed</tt><a class="headerlink" href="#cursor.closed" title="Permalink to this definition">¶</a></dt>
- <dd><p>Read-only boolean attribute: specifies if the cursor is closed
- (<tt class="xref py py-obj xref docutils literal"><span class="pre">True</span></tt>) or not (<tt class="xref py py-obj xref docutils literal"><span class="pre">False</span></tt>).</p>
- <div class="admonition-db-api-extension dbapi-extension admonition ">
- <p class="first admonition-title">DB API extension</p>
- <p class="last">The <a class="reference internal" href="#cursor.closed" title="cursor.closed"><tt class="xref py py-obj docutils literal"><span class="pre">closed</span></tt></a> attribute is a Psycopg extension to the
- DB API 2.0.</p>
- </div>
- <p class="versionadded">
- <span class="versionmodified">New in version 2.0.7.</span></p>
- </dd></dl>
- <dl class="attribute">
- <dt id="cursor.connection">
- <tt class="descname">connection</tt><a class="headerlink" href="#cursor.connection" title="Permalink to this definition">¶</a></dt>
- <dd><p>Read-only attribute returning a reference to the <a class="reference internal" href="connection.html#connection" title="connection"><tt class="xref py py-obj docutils literal"><span class="pre">connection</span></tt></a>
- object on which the cursor was created.</p>
- </dd></dl>
- <dl class="attribute">
- <dt id="cursor.name">
- <tt class="descname">name</tt><a class="headerlink" href="#cursor.name" title="Permalink to this definition">¶</a></dt>
- <dd><p>Read-only attribute containing the name of the cursor if it was
- creates as named cursor by <a class="reference internal" href="connection.html#connection.cursor" title="connection.cursor"><tt class="xref py py-obj docutils literal"><span class="pre">connection.cursor()</span></tt></a>, or <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt> if
- it is a client side cursor. See <a class="reference internal" href="usage.html#server-side-cursors"><em>Server side cursors</em></a>.</p>
- <div class="admonition-db-api-extension dbapi-extension admonition ">
- <p class="first admonition-title">DB API extension</p>
- <p class="last">The <a class="reference internal" href="#cursor.name" title="cursor.name"><tt class="xref py py-obj docutils literal"><span class="pre">name</span></tt></a> attribute is a Psycopg extension to the DB API 2.0.</p>
- </div>
- </dd></dl>
- <p class="rubric" id="execute">Commands execution methods</p>
- <dl class="method">
- <dt id="cursor.execute">
- <tt class="descname">execute</tt><big>(</big><em>operation</em><span class="optional">[</span>, <em>parameters</em><span class="optional">]</span><big>)</big><a class="headerlink" href="#cursor.execute" title="Permalink to this definition">¶</a></dt>
- <dd><p>Prepare and execute a database operation (query or command).</p>
- <p>Parameters may be provided as sequence or mapping and will be bound to
- variables in the operation. Variables are specified either with
- positional (<tt class="docutils literal"><span class="pre">%s</span></tt>) or named (<tt class="samp docutils literal"><span class="pre">%(</span><em><span class="pre">name</span></em><span class="pre">)s</span></tt>) placeholders. See
- <a class="reference internal" href="usage.html#query-parameters"><em>Passing parameters to SQL queries</em></a>.</p>
- <p>The method returns <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt>. If a query was executed, the returned
- values can be retrieved using <a class="reference internal" href="#fetch"><tt class="xref py py-obj docutils literal"><span class="pre">fetch*()</span></tt></a> methods.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.executemany">
- <tt class="descname">executemany</tt><big>(</big><em>operation</em>, <em>seq_of_parameters</em><big>)</big><a class="headerlink" href="#cursor.executemany" title="Permalink to this definition">¶</a></dt>
- <dd><p>Prepare a database operation (query or command) and then execute it
- against all parameter tuples or mappings found in the sequence
- <tt class="xref py py-obj docutils literal"><span class="pre">seq_of_parameters</span></tt>.</p>
- <p>The function is mostly useful for commands that update the database:
- any result set returned by the query is discarded.</p>
- <p>Parameters are bounded to the query using the same rules described in
- the <a class="reference internal" href="#cursor.execute" title="cursor.execute"><tt class="xref py py-obj docutils literal"><span class="pre">execute()</span></tt></a> method.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.callproc">
- <tt class="descname">callproc</tt><big>(</big><em>procname</em><span class="optional">[</span>, <em>parameters</em><span class="optional">]</span><big>)</big><a class="headerlink" href="#cursor.callproc" title="Permalink to this definition">¶</a></dt>
- <dd><p>Call a stored database procedure with the given name. The sequence of
- parameters must contain one entry for each argument that the procedure
- expects. The result of the call is returned as modified copy of the
- input sequence. Input parameters are left untouched, output and
- input/output parameters replaced with possibly new values.</p>
- <p>The procedure may also provide a result set as output. This must then
- be made available through the standard <a class="reference internal" href="#fetch"><tt class="xref py py-obj docutils literal"><span class="pre">fetch*()</span></tt></a> methods.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.mogrify">
- <tt class="descname">mogrify</tt><big>(</big><em>operation</em><span class="optional">[</span>, <em>parameters</em><span class="optional">]</span><big>)</big><a class="headerlink" href="#cursor.mogrify" title="Permalink to this definition">¶</a></dt>
- <dd><p>Return a query string after arguments binding. The string returned is
- exactly the one that would be sent to the database running the
- <a class="reference internal" href="#cursor.execute" title="cursor.execute"><tt class="xref py py-obj docutils literal"><span class="pre">execute()</span></tt></a> method or similar.</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">mogrify</span><span class="p">(</span><span class="s">"INSERT INTO test (num, data) VALUES (</span><span class="si">%s</span><span class="s">, </span><span class="si">%s</span><span class="s">)"</span><span class="p">,</span> <span class="p">(</span><span class="mi">42</span><span class="p">,</span> <span class="s">'bar'</span><span class="p">))</span>
- <span class="go">"INSERT INTO test (num, data) VALUES (42, E'bar')"</span>
- </pre></div>
- </div>
- <div class="admonition-db-api-extension dbapi-extension admonition ">
- <p class="first admonition-title">DB API extension</p>
- <p class="last">The <a class="reference internal" href="#cursor.mogrify" title="cursor.mogrify"><tt class="xref py py-obj docutils literal"><span class="pre">mogrify()</span></tt></a> method is a Psycopg extension to the DB API 2.0.</p>
- </div>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.setinputsizes">
- <tt class="descname">setinputsizes</tt><big>(</big><em>sizes</em><big>)</big><a class="headerlink" href="#cursor.setinputsizes" title="Permalink to this definition">¶</a></dt>
- <dd><p>This method is exposed in compliance with the DB API 2.0. It currently
- does nothing but it is safe to call it.</p>
- </dd></dl>
- <p class="rubric" id="fetch">Results retrieval methods</p>
- <p>The following methods are used to read data from the database after an
- <a class="reference internal" href="#cursor.execute" title="cursor.execute"><tt class="xref py py-obj docutils literal"><span class="pre">execute()</span></tt></a> call.</p>
- <div class="admonition note" id="cursor-iterable">
- <p class="first admonition-title">Note</p>
- <p><a class="reference internal" href="#cursor" title="cursor"><tt class="xref py py-obj docutils literal"><span class="pre">cursor</span></tt></a> objects are iterable, so, instead of calling
- explicitly <a class="reference internal" href="#cursor.fetchone" title="cursor.fetchone"><tt class="xref py py-obj docutils literal"><span class="pre">fetchone()</span></tt></a> in a loop, the object itself can
- be used:</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SELECT * FROM test;"</span><span class="p">)</span>
- <span class="gp">>>> </span><span class="k">for</span> <span class="n">record</span> <span class="ow">in</span> <span class="n">cur</span><span class="p">:</span>
- <span class="gp">... </span> <span class="k">print</span> <span class="n">record</span>
- <span class="gp">...</span>
- <span class="go">(1, 100, "abc'def")</span>
- <span class="go">(2, None, 'dada')</span>
- <span class="go">(3, 42, 'bar')</span>
- </pre></div>
- </div>
- <p class="last versionchanged">
- <span class="versionmodified">Changed in version 2.4: </span>iterating over a <a class="reference internal" href="usage.html#server-side-cursors"><em>named cursor</em></a>
- fetches <a class="reference internal" href="#cursor.itersize" title="cursor.itersize"><tt class="xref py py-obj docutils literal"><span class="pre">itersize</span></tt></a> records at time from the backend.
- Previously only one record was fetched per roundtrip, resulting
- in a large overhead.</p>
- </div>
- <dl class="method">
- <dt id="cursor.fetchone">
- <tt class="descname">fetchone</tt><big>(</big><big>)</big><a class="headerlink" href="#cursor.fetchone" title="Permalink to this definition">¶</a></dt>
- <dd><p>Fetch the next row of a query result set, returning a single tuple,
- or <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt> when no more data is available:</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SELECT * FROM test WHERE id = </span><span class="si">%s</span><span class="s">"</span><span class="p">,</span> <span class="p">(</span><span class="mi">3</span><span class="p">,))</span>
- <span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
- <span class="go">(3, 42, 'bar')</span>
- </pre></div>
- </div>
- <p>A <a class="reference internal" href="module.html#psycopg2.ProgrammingError" title="psycopg2.ProgrammingError"><tt class="xref py py-obj docutils literal"><span class="pre">ProgrammingError</span></tt></a> is raised if the previous call
- to <a class="reference internal" href="#execute"><tt class="xref py py-obj docutils literal"><span class="pre">execute*()</span></tt></a> did not produce any result set or no call was issued
- yet.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.fetchmany">
- <tt class="descname">fetchmany</tt><big>(</big><span class="optional">[</span><em>size=cursor.arraysize</em><span class="optional">]</span><big>)</big><a class="headerlink" href="#cursor.fetchmany" title="Permalink to this definition">¶</a></dt>
- <dd><p>Fetch the next set of rows of a query result, returning a list of
- tuples. An empty list is returned when no more rows are available.</p>
- <p>The number of rows to fetch per call is specified by the parameter.
- If it is not given, the cursor’s <a class="reference internal" href="#cursor.arraysize" title="cursor.arraysize"><tt class="xref py py-obj docutils literal"><span class="pre">arraysize</span></tt></a> determines
- the number of rows to be fetched. The method should try to fetch as
- many rows as indicated by the size parameter. If this is not possible
- due to the specified number of rows not being available, fewer rows
- may be returned:</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SELECT * FROM test;"</span><span class="p">)</span>
- <span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">fetchmany</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
- <span class="go">[(1, 100, "abc'def"), (2, None, 'dada')]</span>
- <span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">fetchmany</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
- <span class="go">[(3, 42, 'bar')]</span>
- <span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">fetchmany</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
- <span class="go">[]</span>
- </pre></div>
- </div>
- <p>A <a class="reference internal" href="module.html#psycopg2.ProgrammingError" title="psycopg2.ProgrammingError"><tt class="xref py py-obj docutils literal"><span class="pre">ProgrammingError</span></tt></a> is raised if the previous call to
- <a class="reference internal" href="#execute"><tt class="xref py py-obj docutils literal"><span class="pre">execute*()</span></tt></a> did not produce any result set or no call was issued yet.</p>
- <p>Note there are performance considerations involved with the size
- parameter. For optimal performance, it is usually best to use the
- <a class="reference internal" href="#cursor.arraysize" title="cursor.arraysize"><tt class="xref py py-obj docutils literal"><span class="pre">arraysize</span></tt></a> attribute. If the size parameter is used,
- then it is best for it to retain the same value from one
- <a class="reference internal" href="#cursor.fetchmany" title="cursor.fetchmany"><tt class="xref py py-obj docutils literal"><span class="pre">fetchmany()</span></tt></a> call to the next.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.fetchall">
- <tt class="descname">fetchall</tt><big>(</big><big>)</big><a class="headerlink" href="#cursor.fetchall" title="Permalink to this definition">¶</a></dt>
- <dd><p>Fetch all (remaining) rows of a query result, returning them as a list
- of tuples. An empty list is returned if there is no more record to
- fetch.</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SELECT * FROM test;"</span><span class="p">)</span>
- <span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
- <span class="go">[(1, 100, "abc'def"), (2, None, 'dada'), (3, 42, 'bar')]</span>
- </pre></div>
- </div>
- <p>A <a class="reference internal" href="module.html#psycopg2.ProgrammingError" title="psycopg2.ProgrammingError"><tt class="xref py py-obj docutils literal"><span class="pre">ProgrammingError</span></tt></a> is raised if the previous call to
- <a class="reference internal" href="#execute"><tt class="xref py py-obj docutils literal"><span class="pre">execute*()</span></tt></a> did not produce any result set or no call was issued yet.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.scroll">
- <tt class="descname">scroll</tt><big>(</big><em>value</em><span class="optional">[</span>, <em>mode='relative'</em><span class="optional">]</span><big>)</big><a class="headerlink" href="#cursor.scroll" title="Permalink to this definition">¶</a></dt>
- <dd><p>Scroll the cursor in the result set to a new position according
- to mode.</p>
- <p>If <tt class="xref py py-obj docutils literal"><span class="pre">mode</span></tt> is <tt class="docutils literal"><span class="pre">relative</span></tt> (default), value is taken as offset to
- the current position in the result set, if set to <tt class="docutils literal"><span class="pre">absolute</span></tt>,
- value states an absolute target position.</p>
- <p>If the scroll operation would leave the result set, a
- <a class="reference internal" href="module.html#psycopg2.ProgrammingError" title="psycopg2.ProgrammingError"><tt class="xref py py-obj docutils literal"><span class="pre">ProgrammingError</span></tt></a> is raised and the cursor position is
- not changed.</p>
- <p>The method can be used both for client-side cursors and
- <a class="reference internal" href="usage.html#server-side-cursors"><em>server-side cursors</em></a>.</p>
- <div class="admonition note">
- <p class="first admonition-title">Note</p>
- <p>According to the <a class="reference external" href="http://www.python.org/dev/peps/pep-0249/">DB API 2.0</a>, the exception raised for a cursor out
- of bound should have been <tt class="xref py py-obj docutils literal"><span class="pre">IndexError</span></tt>. The best option is
- probably to catch both exceptions in your code:</p>
- <div class="last highlight-python"><div class="highlight"><pre><span class="k">try</span><span class="p">:</span>
- <span class="n">cur</span><span class="o">.</span><span class="n">scroll</span><span class="p">(</span><span class="mi">1000</span> <span class="o">*</span> <span class="mi">1000</span><span class="p">)</span>
- <span class="k">except</span> <span class="p">(</span><span class="n">ProgrammingError</span><span class="p">,</span> <span class="ne">IndexError</span><span class="p">),</span> <span class="n">exc</span><span class="p">:</span>
- <span class="n">deal_with_it</span><span class="p">(</span><span class="n">exc</span><span class="p">)</span>
- </pre></div>
- </div>
- </div>
- </dd></dl>
- <dl class="attribute">
- <dt id="cursor.arraysize">
- <tt class="descname">arraysize</tt><a class="headerlink" href="#cursor.arraysize" title="Permalink to this definition">¶</a></dt>
- <dd><p>This read/write attribute specifies the number of rows to fetch at a
- time with <a class="reference internal" href="#cursor.fetchmany" title="cursor.fetchmany"><tt class="xref py py-obj docutils literal"><span class="pre">fetchmany()</span></tt></a>. It defaults to 1 meaning to fetch
- a single row at a time.</p>
- </dd></dl>
- <dl class="attribute">
- <dt id="cursor.itersize">
- <tt class="descname">itersize</tt><a class="headerlink" href="#cursor.itersize" title="Permalink to this definition">¶</a></dt>
- <dd><p>Read/write attribute specifying the number of rows to fetch from the
- backend at each network roundtrip during <a class="reference internal" href="#cursor-iterable"><em>iteration</em></a> on a <a class="reference internal" href="usage.html#server-side-cursors"><em>named cursor</em></a>. The
- default is 2000.</p>
- <p class="versionadded">
- <span class="versionmodified">New in version 2.4.</span></p>
- <div class="admonition-db-api-extension dbapi-extension admonition ">
- <p class="first admonition-title">DB API extension</p>
- <p class="last">The <a class="reference internal" href="#cursor.itersize" title="cursor.itersize"><tt class="xref py py-obj docutils literal"><span class="pre">itersize</span></tt></a> attribute is a Psycopg extension to the DB API 2.0.</p>
- </div>
- </dd></dl>
- <dl class="attribute">
- <dt id="cursor.rowcount">
- <tt class="descname">rowcount</tt><a class="headerlink" href="#cursor.rowcount" title="Permalink to this definition">¶</a></dt>
- <dd><p>This read-only attribute specifies the number of rows that the last
- <a class="reference internal" href="#execute"><tt class="xref py py-obj docutils literal"><span class="pre">execute*()</span></tt></a> produced (for <abbr title="Data Query Language">DQL</abbr> statements
- like <tt class="sql docutils literal"><span class="pre">SELECT</span></tt>) or affected (for
- <abbr title="Data Manipulation Language">DML</abbr> statements like <tt class="sql docutils literal"><span class="pre">UPDATE</span></tt>
- or <tt class="sql docutils literal"><span class="pre">INSERT</span></tt>).</p>
- <p>The attribute is -1 in case no <tt class="xref py py-obj docutils literal"><span class="pre">execute*()</span></tt> has been performed on
- the cursor or the row count of the last operation if it can’t be
- determined by the interface.</p>
- <div class="admonition note">
- <p class="first admonition-title">Note</p>
- <p class="last">The <a class="reference external" href="http://www.python.org/dev/peps/pep-0249/">DB API 2.0</a> interface reserves to redefine the latter case to
- have the object return <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt> instead of -1 in future versions
- of the specification.</p>
- </div>
- </dd></dl>
- <dl class="attribute">
- <dt id="cursor.rownumber">
- <tt class="descname">rownumber</tt><a class="headerlink" href="#cursor.rownumber" title="Permalink to this definition">¶</a></dt>
- <dd><p>This read-only attribute provides the current 0-based index of the
- cursor in the result set or <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt> if the index cannot be
- determined.</p>
- <p>The index can be seen as index of the cursor in a sequence (the result
- set). The next fetch operation will fetch the row indexed by
- <a class="reference internal" href="#cursor.rownumber" title="cursor.rownumber"><tt class="xref py py-obj docutils literal"><span class="pre">rownumber</span></tt></a> in that sequence.</p>
- </dd></dl>
- <span class="target" id="index-1"></span><dl class="attribute">
- <dt id="cursor.lastrowid">
- <tt class="descname">lastrowid</tt><a class="headerlink" href="#cursor.lastrowid" title="Permalink to this definition">¶</a></dt>
- <dd><p>This read-only attribute provides the OID of the last row inserted
- by the cursor. If the table wasn’t created with OID support or the
- last operation is not a single record insert, the attribute is set to
- <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt>.</p>
- <div class="admonition note">
- <p class="first admonition-title">Note</p>
- <p class="last">PostgreSQL currently advices to not create OIDs on the tables and
- the default for <a class="reference external" href="http://www.postgresql.org/docs/9.0/static/sql-createtable.html"><tt class="sql docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></tt></a> is to not support them. The
- <a class="reference external" href="http://www.postgresql.org/docs/9.0/static/sql-insert.html"><tt class="sql docutils literal"><span class="pre">INSERT</span> <span class="pre">...</span> <span class="pre">RETURNING</span></tt></a> syntax available from PostgreSQL 8.3 allows
- more flexibility.</p>
- </div>
- </dd></dl>
- <dl class="attribute">
- <dt id="cursor.query">
- <tt class="descname">query</tt><a class="headerlink" href="#cursor.query" title="Permalink to this definition">¶</a></dt>
- <dd><p>Read-only attribute containing the body of the last query sent to the
- backend (including bound arguments). <tt class="xref py py-obj xref docutils literal"><span class="pre">None</span></tt> if no query has been
- executed yet:</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"INSERT INTO test (num, data) VALUES (</span><span class="si">%s</span><span class="s">, </span><span class="si">%s</span><span class="s">)"</span><span class="p">,</span> <span class="p">(</span><span class="mi">42</span><span class="p">,</span> <span class="s">'bar'</span><span class="p">))</span>
- <span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">query</span>
- <span class="go">"INSERT INTO test (num, data) VALUES (42, E'bar')"</span>
- </pre></div>
- </div>
- <div class="admonition-db-api-extension dbapi-extension admonition ">
- <p class="first admonition-title">DB API extension</p>
- <p class="last">The <a class="reference internal" href="#cursor.query" title="cursor.query"><tt class="xref py py-obj docutils literal"><span class="pre">query</span></tt></a> attribute is a Psycopg extension to the DB API 2.0.</p>
- </div>
- </dd></dl>
- <dl class="attribute">
- <dt id="cursor.statusmessage">
- <tt class="descname">statusmessage</tt><a class="headerlink" href="#cursor.statusmessage" title="Permalink to this definition">¶</a></dt>
- <dd><p>Read-only attribute containing the message returned by the last
- command:</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"INSERT INTO test (num, data) VALUES (</span><span class="si">%s</span><span class="s">, </span><span class="si">%s</span><span class="s">)"</span><span class="p">,</span> <span class="p">(</span><span class="mi">42</span><span class="p">,</span> <span class="s">'bar'</span><span class="p">))</span>
- <span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">statusmessage</span>
- <span class="go">'INSERT 0 1'</span>
- </pre></div>
- </div>
- <div class="admonition-db-api-extension dbapi-extension admonition ">
- <p class="first admonition-title">DB API extension</p>
- <p class="last">The <a class="reference internal" href="#cursor.statusmessage" title="cursor.statusmessage"><tt class="xref py py-obj docutils literal"><span class="pre">statusmessage</span></tt></a> attribute is a Psycopg extension to the
- DB API 2.0.</p>
- </div>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.cast">
- <tt class="descname">cast</tt><big>(</big><em>oid</em>, <em>s</em><big>)</big><a class="headerlink" href="#cursor.cast" title="Permalink to this definition">¶</a></dt>
- <dd><p>Convert a value from the PostgreSQL string representation to a Python
- object.</p>
- <p>Use the most specific of the typecasters registered by
- <a class="reference internal" href="extensions.html#psycopg2.extensions.register_type" title="psycopg2.extensions.register_type"><tt class="xref py py-obj docutils literal"><span class="pre">register_type()</span></tt></a>.</p>
- <p class="versionadded">
- <span class="versionmodified">New in version 2.4.</span></p>
- <div class="admonition-db-api-extension dbapi-extension admonition ">
- <p class="first admonition-title">DB API extension</p>
- <p class="last">The <a class="reference internal" href="#cursor.cast" title="cursor.cast"><tt class="xref py py-obj docutils literal"><span class="pre">cast()</span></tt></a> method is a Psycopg extension to the DB API 2.0.</p>
- </div>
- </dd></dl>
- <dl class="attribute">
- <dt id="cursor.tzinfo_factory">
- <tt class="descname">tzinfo_factory</tt><a class="headerlink" href="#cursor.tzinfo_factory" title="Permalink to this definition">¶</a></dt>
- <dd><p>The time zone factory used to handle data types such as
- <tt class="sql docutils literal"><span class="pre">TIMESTAMP</span> <span class="pre">WITH</span> <span class="pre">TIME</span> <span class="pre">ZONE</span></tt>. It should be a <a class="reference external" href="http://docs.python.org/3.2/library/datetime.html#datetime.tzinfo" title="(in Python v3.2)"><tt class="xref py py-obj docutils literal"><span class="pre">tzinfo</span></tt></a>
- object. A few implementations are available in the <a class="reference internal" href="tz.html#module-psycopg2.tz" title="psycopg2.tz"><tt class="xref py py-obj docutils literal"><span class="pre">psycopg2.tz</span></tt></a>
- module.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.nextset">
- <tt class="descname">nextset</tt><big>(</big><big>)</big><a class="headerlink" href="#cursor.nextset" title="Permalink to this definition">¶</a></dt>
- <dd><p>This method is not supported (PostgreSQL does not have multiple data
- sets) and will raise a <a class="reference internal" href="module.html#psycopg2.NotSupportedError" title="psycopg2.NotSupportedError"><tt class="xref py py-obj docutils literal"><span class="pre">NotSupportedError</span></tt></a> exception.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.setoutputsize">
- <tt class="descname">setoutputsize</tt><big>(</big><em>size</em><span class="optional">[</span>, <em>column</em><span class="optional">]</span><big>)</big><a class="headerlink" href="#cursor.setoutputsize" title="Permalink to this definition">¶</a></dt>
- <dd><p>This method is exposed in compliance with the DB API 2.0. It currently
- does nothing but it is safe to call it.</p>
- </dd></dl>
- <p class="rubric">COPY-related methods</p>
- <div class="admonition-db-api-extension dbapi-extension admonition ">
- <p class="first admonition-title">DB API extension</p>
- <p class="last">The <tt class="sql docutils literal"><span class="pre">COPY</span></tt> command is a PostgreSQL extension to the SQL standard.
- As such, its support is a Psycopg extension to the DB API 2.0.</p>
- </div>
- <dl class="method">
- <dt id="cursor.copy_from">
- <tt class="descname">copy_from</tt><big>(</big><em>file</em>, <em>table</em>, <em>sep='\t'</em>, <em>null='\N'</em>, <em>size=8192</em>, <em>columns=None</em><big>)</big><a class="headerlink" href="#cursor.copy_from" title="Permalink to this definition">¶</a></dt>
- <dd><p>Read data <em>from</em> the file-like object <em>file</em> appending them to
- the table named <em>table</em>. See <a class="reference internal" href="usage.html#copy"><em>Using COPY TO and COPY FROM</em></a> for an overview.</p>
- <table class="docutils field-list" frame="void" rules="none">
- <col class="field-name" />
- <col class="field-body" />
- <tbody valign="top">
- <tr class="field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
- <li><strong>file</strong> – file-like object to read data from. It must have both
- <tt class="xref py py-obj docutils literal"><span class="pre">read()</span></tt> and <tt class="xref py py-obj docutils literal"><span class="pre">readline()</span></tt> methods.</li>
- <li><strong>table</strong> – name of the table to copy data into.</li>
- <li><strong>sep</strong> – columns separator expected in the file. Defaults to a tab.</li>
- <li><strong>null</strong> – textual representation of <tt class="sql docutils literal"><span class="pre">NULL</span></tt> in the file.</li>
- <li><strong>size</strong> – size of the buffer used to read from the file.</li>
- <li><strong>columns</strong> – iterable with name of the columns to import.
- The length and types should match the content of the file to read.
- If not specified, it is assumed that the entire table matches the
- file structure.</li>
- </ul>
- </td>
- </tr>
- </tbody>
- </table>
- <p>Example:</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">f</span> <span class="o">=</span> <span class="n">StringIO</span><span class="p">(</span><span class="s">"42</span><span class="se">\t</span><span class="s">foo</span><span class="se">\n</span><span class="s">74</span><span class="se">\t</span><span class="s">bar</span><span class="se">\n</span><span class="s">"</span><span class="p">)</span>
- <span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">copy_from</span><span class="p">(</span><span class="n">f</span><span class="p">,</span> <span class="s">'test'</span><span class="p">,</span> <span class="n">columns</span><span class="o">=</span><span class="p">(</span><span class="s">'num'</span><span class="p">,</span> <span class="s">'data'</span><span class="p">))</span>
- <span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"select * from test where id > 5;"</span><span class="p">)</span>
- <span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
- <span class="go">[(6, 42, 'foo'), (7, 74, 'bar')]</span>
- </pre></div>
- </div>
- <p class="versionchanged">
- <span class="versionmodified">Changed in version 2.0.6: </span>added the <em>columns</em> parameter.</p>
- <p class="versionchanged">
- <span class="versionmodified">Changed in version 2.4: </span>data read from files implementing the <a class="reference external" href="http://docs.python.org/3.2/library/io.html#io.TextIOBase" title="(in Python v3.2)"><tt class="xref py py-obj docutils literal"><span class="pre">io.TextIOBase</span></tt></a> interface
- are encoded in the connection <a class="reference internal" href="connection.html#connection.encoding" title="connection.encoding"><tt class="xref py py-obj docutils literal"><span class="pre">encoding</span></tt></a> when sent to
- the backend.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.copy_to">
- <tt class="descname">copy_to</tt><big>(</big><em>file</em>, <em>table</em>, <em>sep='\t'</em>, <em>null='\N'</em>, <em>columns=None</em><big>)</big><a class="headerlink" href="#cursor.copy_to" title="Permalink to this definition">¶</a></dt>
- <dd><p>Write the content of the table named <em>table</em> <em>to</em> the file-like
- object <em>file</em>. See <a class="reference internal" href="usage.html#copy"><em>Using COPY TO and COPY FROM</em></a> for an overview.</p>
- <table class="docutils field-list" frame="void" rules="none">
- <col class="field-name" />
- <col class="field-body" />
- <tbody valign="top">
- <tr class="field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
- <li><strong>file</strong> – file-like object to write data into. It must have a
- <tt class="xref py py-obj docutils literal"><span class="pre">write()</span></tt> method.</li>
- <li><strong>table</strong> – name of the table to copy data from.</li>
- <li><strong>sep</strong> – columns separator expected in the file. Defaults to a tab.</li>
- <li><strong>null</strong> – textual representation of <tt class="sql docutils literal"><span class="pre">NULL</span></tt> in the file.</li>
- <li><strong>columns</strong> – iterable with name of the columns to export.
- If not specified, export all the columns.</li>
- </ul>
- </td>
- </tr>
- </tbody>
- </table>
- <p>Example:</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">copy_to</span><span class="p">(</span><span class="n">sys</span><span class="o">.</span><span class="n">stdout</span><span class="p">,</span> <span class="s">'test'</span><span class="p">,</span> <span class="n">sep</span><span class="o">=</span><span class="s">"|"</span><span class="p">)</span>
- <span class="go">1|100|abc'def</span>
- <span class="go">2|\N|dada</span>
- <span class="gp">...</span>
- </pre></div>
- </div>
- <p class="versionchanged">
- <span class="versionmodified">Changed in version 2.0.6: </span>added the <em>columns</em> parameter.</p>
- <p class="versionchanged">
- <span class="versionmodified">Changed in version 2.4: </span>data sent to files implementing the <a class="reference external" href="http://docs.python.org/3.2/library/io.html#io.TextIOBase" title="(in Python v3.2)"><tt class="xref py py-obj docutils literal"><span class="pre">io.TextIOBase</span></tt></a> interface
- are decoded in the connection <a class="reference internal" href="connection.html#connection.encoding" title="connection.encoding"><tt class="xref py py-obj docutils literal"><span class="pre">encoding</span></tt></a> when read
- from the backend.</p>
- </dd></dl>
- <dl class="method">
- <dt id="cursor.copy_expert">
- <tt class="descname">copy_expert</tt><big>(</big><em>sql</em>, <em>file</em>, <em>size=8192</em><big>)</big><a class="headerlink" href="#cursor.copy_expert" title="Permalink to this definition">¶</a></dt>
- <dd><p>Submit a user-composed <tt class="sql docutils literal"><span class="pre">COPY</span></tt> statement. The method is useful to
- handle all the parameters that PostgreSQL makes available (see
- <a class="reference external" href="http://www.postgresql.org/docs/9.0/static/sql-copy.html"><tt class="sql docutils literal"><span class="pre">COPY</span></tt></a> command documentation).</p>
- <table class="docutils field-list" frame="void" rules="none">
- <col class="field-name" />
- <col class="field-body" />
- <tbody valign="top">
- <tr class="field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
- <li><strong>sql</strong> – the <tt class="sql docutils literal"><span class="pre">COPY</span></tt> statement to execute.</li>
- <li><strong>file</strong> – a file-like object; must be a readable file for
- <tt class="sql docutils literal"><span class="pre">COPY</span> <span class="pre">FROM</span></tt> or an writeable file for <tt class="sql docutils literal"><span class="pre">COPY</span> <span class="pre">TO</span></tt>.</li>
- <li><strong>size</strong> – size of the read buffer to be used in <tt class="sql docutils literal"><span class="pre">COPY</span> <span class="pre">FROM</span></tt>.</li>
- </ul>
- </td>
- </tr>
- </tbody>
- </table>
- <p>Example:</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">copy_expert</span><span class="p">(</span><span class="s">"COPY test TO STDOUT WITH CSV HEADER"</span><span class="p">,</span> <span class="n">sys</span><span class="o">.</span><span class="n">stdout</span><span class="p">)</span>
- <span class="go">id,num,data</span>
- <span class="go">1,100,abc'def</span>
- <span class="go">2,,dada</span>
- <span class="gp">...</span>
- </pre></div>
- </div>
- <p class="versionadded">
- <span class="versionmodified">New in version 2.0.6.</span></p>
- <p class="versionchanged">
- <span class="versionmodified">Changed in version 2.4: </span>files implementing the <a class="reference external" href="http://docs.python.org/3.2/library/io.html#io.TextIOBase" title="(in Python v3.2)"><tt class="xref py py-obj docutils literal"><span class="pre">io.TextIOBase</span></tt></a> interface are dealt with
- using Unicode data instead of bytes.</p>
- </dd></dl>
- </dd></dl>
- </div>
- </div>
- </div>
- </div>
- <div class="sphinxsidebar">
- <div class="sphinxsidebarwrapper">
- <h4>Previous topic</h4>
- <p class="topless"><a href="connection.html"
- title="previous chapter">The <tt class="docutils literal docutils literal"><span class="pre">connection</span></tt> class</a></p>
- <h4>Next topic</h4>
- <p class="topless"><a href="advanced.html"
- title="next chapter">More advanced topics</a></p>
- <h3>This Page</h3>
- <ul class="this-page-menu">
- <li><a href="_sources/cursor.txt"
- rel="nofollow">Show Source</a></li>
- </ul>
- <div id="searchbox" style="display: none">
- <h3>Quick search</h3>
- <form class="search" action="search.html" method="get">
- <input type="text" name="q" size="18" />
- <input type="submit" value="Go" />
- <input type="hidden" name="check_keywords" value="yes" />
- <input type="hidden" name="area" value="default" />
- </form>
- <p class="searchtip" style="font-size: 90%">
- Enter search terms or a module, class or function name.
- </p>
- </div>
- <script type="text/javascript">$('#searchbox').show(0);</script>
- </div>
- </div>
- <div class="clearer"></div>
- </div>
- <div class="related">
- <h3>Navigation</h3>
- <ul>
- <li class="right" style="margin-right: 10px">
- <a href="genindex.html" title="General Index"
- >index</a></li>
- <li class="right" >
- <a href="py-modindex.html" title="Python Module Index"
- >modules</a> |</li>
- <li class="right" >
- <a href="advanced.html" title="More advanced topics"
- >next</a> |</li>
- <li class="right" >
- <a href="connection.html" title="The connection class"
- >previous</a> |</li>
- <li><a href="index.html">Psycopg v2.4.2 documentation</a> »</li>
- </ul>
- </div>
- <div class="footer">
- © Copyright 2001-2011, Federico Di Gregorio. Documentation by Daniele Varrazzo.
- Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.0.7.
- </div>
- </body>
- </html>