Menu
How do I serialize pyodbc cursor output (from .fetchone, .fetchmany or .fetchall) as a Python dictionary?
I'm using bottlepy and need to return dict so it can return it as JSON.
893
fetchall() Method. The method fetches all (or all remaining) rows of a query result set and returns a list of tuples. If no more rows are available, it returns an empty list. You must fetch all rows for the current query before executing new statements using the same connection.
wiki. Cursors represent a database cursor (and map to ODBC HSTMTs), which is used to manage the context of a fetch operation. 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.
Steps for using fetchall() in Mysql using Python: Next, create a cursor object with the cursor() method. Now create and execute the query using “SELECT *” statement with execute() method to retrieve the data. Use fetchall() method on the result variable. print the result using for each loop to display all.
class cursor. Allows Python code to execute PostgreSQL command in a database session. Cursors are created by the connection. cursor() 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.
If you don't know columns ahead of time, use Cursor.description to build a list of column names and zip with each row to produce a list of dictionaries. Example assumes connection and query are built:
>>> cursor = connection.cursor().execute(sql) >>> columns = [column[0] for column in cursor.description] >>> print(columns) ['name', 'create_date'] >>> results = [] >>> for row in cursor.fetchall(): ... results.append(dict(zip(columns, row))) ... >>> print(results) [{'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'master'}, {'create_date': datetime.datetime(2013, 1, 30, 12, 31, 40, 340000), 'name': u'tempdb'}, {'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'model'}, {'create_date': datetime.datetime(2010, 4, 2, 17, 35, 8, 970000), 'name': u'msdb'}] If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
