1. Implicit cursor It’s almost always better to use the implicit cursor of a FOR loop than to resort to a somewhat slower and unwieldy explicit cursor. I have written thousands of PL/pgSQL functions and only a hand full of times explicit cursors made any sense. CREATE OR REPLACE FUNCTION avoidable_states() RETURNS SETOF varchar LANGUAGE … Read more
If you are using the default cursor, a MySQLdb.cursors.Cursor, the entire result set will be stored on the client side (i.e. in a Python list) by the time the cursor.execute() is completed. Therefore, even if you use for row in cursor: you will not be getting any reduction in memory footprint. The entire result set … Read more
You can read large blobs in pieces. First find out which ones need this treatment: SELECT id, length(blobcolumn) FROM mytable WHERE length(blobcolumn) > 1000000 and then read chunks with substr: SELECT substr(blobcolumn, 1, 1000000) FROM mytable WHERE id = 123 SELECT substr(blobcolumn, 1000001, 1000000) FROM mytable WHERE id = 123 … You could also compile … Read more
Note: This code is untested Define a record for your refCursor return type, call it rec. For example: TYPE MyRec IS RECORD (col1 VARCHAR2(10), col2 VARCHAR2(20), …); –define the record rec MyRec; — instantiate the record Once you have the refcursor returned from your procedure, you can add the following code where your comments are … Read more
Using cursors compared to big resultsets is like using video streaming instead of downloading an video in one swoop, and watching it when it has downloaded. If you download, you have to have a few gigs of space and the patience to wait until the download finished. Now, no matter how fast your machine or … Read more
Here’s a comparison between toArray() and cursors after a find() in the Node.js MongoDB driver. Common code: var MongoClient = require(‘mongodb’).MongoClient, assert = require(‘assert’); MongoClient.connect(‘mongodb://localhost:27017/crunchbase’, function (err, db) { assert.equal(err, null); console.log(‘Successfully connected to MongoDB.’); const query = { category_code: “biotech” }; // toArray() vs. cursor code goes here }); Here’s the toArray() code that … Read more
I am in agreement with Otto Allmendinger’s answer, but to make explicit Denis Otkidach’s comment, here is how you can iterate over the results without using Otto’s fetch() function: import MySQLdb.cursors connection=MySQLdb.connect( host=”thehost”,user=”theuser”, passwd=”thepassword”,db=”thedb”, cursorclass = MySQLdb.cursors.SSCursor) cursor=connection.cursor() cursor.execute(query) for row in cursor: print(row)
The docs for SQLiteDatabase.query() say that the query methods return: “A Cursor object, which is positioned before the first entry.” Calling moveToFirst() does two things: it allows you to test whether the query returned an empty set (by testing the return value) and it moves the cursor to the first result (when the set is … Read more
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 = [] … Read more
The main reason that I’m aware of is that set-based operations can be optimised by the engine by running them across multiple threads. For example, think of a quicksort – you can separate the list you’re sorting into multiple “chunks” and sort each separately in their own thread. SQL engines can do similar things with … Read more