19

I am using the Peewee module as the ORM for my project.

I read the entire documentation, there is no clear example on how to process the result from db.execute_sql().

I traced the code, only can find db.execute_sql() return back the cursor.

Does anyone knows how to process the cursor, such as iterate over it and get back the result from complex select statement.

Update: I just found the following source code from peewee folder, it should help me to resolve this problem. 

class QueryResultWrapper(object):
    """
    Provides an iterator over the results of a raw Query, additionally doing
    two things:
    - converts rows from the database into python representations
    - ensures that multiple iterations do not result in multiple queries
    """
    def __init__(self, model, cursor, meta=None):
        self.model = model
        self.cursor = cursor

        self.__ct = 0
        self.__idx = 0

        self._result_cache = []
        self._populated = False
        self._initialized = False

        if meta is not None:
            self.column_meta, self.join_meta = meta
        else:
            self.column_meta = self.join_meta = None

    def __iter__(self):
        self.__idx = 0

        if not self._populated:
            return self
        else:
            return iter(self._result_cache)

    def process_row(self, row):
        return row

    def iterate(self):
        row = self.cursor.fetchone()
        if not row:
            self._populated = True
            raise StopIteration
        elif not self._initialized:
            self.initialize(self.cursor.description)
            self._initialized = True
        return self.process_row(row)

    def iterator(self):
        while True:
            yield self.iterate()

    def next(self):
        if self.__idx  self.__ct):
            try:
                self.next()
            except StopIteration:
                break
user1187968
  • 7,154
  • 16
  • 81
  • 152
  • What is the type of the object returned? Run `sql_execute()` and print the result to see it's type. – Ayush Aug 29 '13 at 06:22

1 Answers1

34

Peewee returns a cursor. Then you can use the db-api 2 to iterate over it:

cursor = db.execute_sql('select * from tweets;')
for row in cursor.fetchall():
    print(row)

cursor = db.execute_sql('select count(*) from tweets;')
res = cursor.fetchone()
print('Total: ', res[0])

Docs: Database.execute_sql

Mart Van de Ven
  • 378
  • 2
  • 8
coleifer
  • 24,887
  • 6
  • 60
  • 75
  • 5
    is there a fetch that returns a dictionary which maps column name? – KJW Jun 01 '14 at 07:40
  • @SagarShah It have to be mapped to a Class with given table name and field, how's about some query like `show table status from db_name` ? It maps to none table – TomSawyer May 20 '19 at 10:49
  • 2
    @KJW Try using the `cursor.description` to grab the column names: `col_names = [col[0] for col in cursor.description] res = [dict(zip(col_names, row)) for row in cursor.fetchall()]` – Mart Van de Ven Feb 03 '20 at 13:02