title: psycopg2 named cursor withhold=True
body: <p>I am using a named cursor to fetch 200K+ rows, and using the attribute, 'withhold=True', this way I can iterate by fetching many (50K) at a time - but my cursor is not persisting...</p>
  
  <p>Here is the error / stacktrace</p>
  
  <pre><code>Traceback (most recent call last):
    File "/home/me/code/etl/etl.py", line 179, in main
      _pg_data = _fetch(_some)
  psycopg2.ProgrammingError: named cursor isn't valid anymore
  
  During handling of the above exception, another exception occurred:
  
  Traceback (most recent call last):
    File "/home/me/code/etl/etl.py", line 330, in &lt;module&gt;
      main()
    File "/home/me/code/etl/etl.py", line 271, in main
      logging.error(Fore.LIGHTRED_EX + e + Fore.RESET, exc_info=True)
  TypeError: must be str, not ProgrammingError
  </code></pre>
  
  <p>Here is my code</p>
  
  <pre><code>from colorama import Fore
  from datetime import datetime
  import argparse, logging, psycopg2, pyodbc, sys, time, yaml
  import _classes.Utils as cpu
  
  def main():
      _cfg_path = "/home/me/code/etl/db.yml"
  
      with open(_cfg_path, 'r') as _ymlfile:
          _cfg = yaml.load(_ymlfile, Loader=yaml.CLoader)
  
      # create a connection to the database
          _conn = psycopg2.connect("host={0} dbname={1} user={2} password={3} port={4}".format(_cfg['local_postgres']['host'], _cfg['local_postgres']['db'],
                                                                                            _cfg['local_postgres']['user'], _cfg['local_postgres']['passwd'],
                                                                                               _cfg['local_postgres']['port']))
  
      _curs_pgsql = _conn.cursor()
      _curs_pgsql.callproc('usp_outbound', ['curs'])
      _curs2_pgsql = _conn.cursor('curs', withhold=True)
  
      _push_date = datetime.now().strftime("%Y-%m-%d")
  
      _some = 50000
      _fetch = _curs2_pgsql.fetchmany
      while True:
          _pg_data = _fetch(_some)
          if not _pg_data:
              break
          for _row in _pg_data:
              _params = ()
              _sql = "INSERT INTO dbo.tbl VALUES (?, ?, ?)"
              _params = (_row[0], _row[1], _row[2])
              # ...insert into destination database
  
              # ...now update source database and set the push and push date flags
              _curs_pgsql.execute("UPDATE products SET pushed = TRUE, pushed_date = (%s) WHERE id = (%s)", (_push_date, _row[2],))
              _conn.commit()
  
      if _conn:
          # close cursor / close the communication with the PostgreSQL database server
          _curs2_pgsql.close()
          _curs_pgsql.close()
          _conn.close()
  </code></pre>
  
  <p>Clearly I am missing something with my named cursor and how it's supposed to be defined...</p>
  
  <p>According to the documentation - </p>
  
  <blockquote>
    <p>Set the value before calling execute() or use the connection.cursor() withhold parameter, otherwise the value will have no effect.</p>
  </blockquote>
  
  <p>... ... ...</p>
  
  <blockquote>
    <p>Trying to fetch from a named cursor after a commit() or to create a named cursor when the connection is in autocommit mode will result in an exception. It is possible to create a WITH HOLD cursor by specifying a True value for the withhold parameter to cursor() or by setting the withhold attribute to True before calling execute() on the cursor.</p>
  </blockquote>
  
  <p>What am I missing?</p>