1

I use pgdb(PyGreSQL) in my project to connect to the working DB. I wrote generator to fetch rows with the pagination from huge tables.

# DbHelper.py

...

def GetMultipleRowsGenerator(self, query, page_size=None):
    """Execute a query and return the generator
    for a fetching rows
    """
    if self._condition:
        self._condition.acquire()
    try:
        self.VerifyConnection()
        return pgsqlUtils.dbGetMultipleRowsGenerator(self._dbConnection, query, page_size)
    finally:
        if self._condition:
            self._condition.release()

...

# pgsqlUtils.py

...

def dbGetMultipleRowsGenerator(db, query, page_size=None):
    if not query.strip().lower().startswith('select '):
        raise ValueError("Bad query '%s'. Query must starts with 'SELECT ...' for a generator." % query)

    if page_size is not None:
        try:
            page_size = int(page_size)
        except TypeError:
            raise ValueError("Expected int type for 'page_size' (%s given)." % page_size)

        if page_size <= 0:
            raise ValueError("The 'page_size' must be greater than 0 (but %s given)." % page_size)

    def get_generator(q):
        c = None
        try:
            c = db.cursor()
        except SyntaxError:
            pass

        try:
            c.execute(q)

            while True:
                r = c.fetchone()
                if r is None:
                    break
                yield r

        except SyntaxError:
            pass
        finally:
            if c is not None:
                c.close()
            db.commit()

    orig_query = query
    page = -1

    while True:
        query = orig_query
        if page_size is not None:
            page += 1
            offset = page * page_size
            query = "%s OFFSET %s LIMIT %s" % (orig_query, offset, page_size)

        generator = get_generator(query)

        row = None
        while True:
            try:
                row = next(generator)
            except StopIteration:
                break

            yield row

        if row is None:
            break  # no rows - end of table

...

using the generator:

curr_data_generator = \
    self.conn.GetMultipleRowsGenerator("SELECT * FROM %s" % temp_tablename, page_size=100000)

while True: 
    try:
        data = next(curr_data_generator)
    except StopIteration:
        break

    # do something with data ...

And it works well but sometimes I get following exception:

   File "pgsqlUtils.py", line 111, in dbGetMultipleRowsGenerator
   File "pgsqlUtils.py", line 84, in get_generator
   File "/usr/myproject/depot/Python-3.4.3/lib/python3.4/site-packages/PyGreSQL-5.0-py3.4-linux-x86_64.egg/pgdb.py", line 958, in fetchone
    res = self.fetchmany(1, False)
   File "/usr/myproject/depot/Python-3.4.3/lib/python3.4/site-packages/PyGreSQL-5.0-py3.4-linux-x86_64.egg/pgdb.py", line 981, in fetchmany
    result = self._src.fetch(size)
 pg.DatabaseError: Last query did not return tuples

Environment details

  • python version: 3.4.3
  • os: CentOS release 6.6
  • I use this code in the multithread mode.

Why is this happening? And how can I fix it?

Stan Zeez
  • 1,138
  • 2
  • 16
  • 38
  • BTW, This is WRONG: `raise ValueError("Bad query '%s'. Query must starts with 'SELECT ...' for a generator." % query)` . A query *can* start with `WITH`. Also: it is not an error for a query to return no rows. – wildplasser May 05 '18 at 18:28
  • wildplasser, thank you for your remark but this functionality use in the context of simple fetch queries like a "SELECT * FROM ..." – Stan Zeez May 05 '18 at 18:33
  • How about: `SELECTED * FROM my_table` ? – wildplasser May 05 '18 at 19:22
  • @wildplasser, you are right, it should be query.strip().lower().startswith('select ') – Stan Zeez May 09 '18 at 19:22

0 Answers0