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)
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
EDIT – Query performance: As @NeilLunn pointed out in his comments, you should not be filtering the documents manually, but use .find(…) for that instead: db.snapshots.find({ roundedDate: { $exists: true }, stream: { $exists: true }, sid: { $exists: false } }) Also, using .bulkWrite(), available as from MongoDB 3.2, will be far way more … Read more
Whenever you are dealing with Cursors, ALWAYS check for null and check for moveToFirst() without fail. if( cursor != null && cursor.moveToFirst() ){ num = cursor.getString(cursor.getColumnIndex(“ContactNumber”)); cursor.close(); } Place logs appropriately to see whether it is returning null or an empty cursor. According to that check your query. Update Put both the checks in a … Read more
Something like this should do the trick (However, read after the snippet for more info) CREATE PROCEDURE GetFilteredData() BEGIN DECLARE bDone INT; DECLARE var1 CHAR(16); — or approriate type DECLARE var2 INT; DECLARE var3 VARCHAR(50); DECLARE curs CURSOR FOR SELECT something FROM somewhere WHERE some stuff; DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = … Read more
Most often the cause for this error are non closed cursors. Make sure you close all cursors after using them (even in the case of an error). Cursor cursor = null; try { cursor = db.query(… // do some work with the cursor here. } finally { // this gets called even if there is … Read more
The “overhead” with cursors is merely part of the API. Cursors are how parts of the RDBMS work under the hood. Often CREATE TABLE and INSERT have SELECT statements, and the implementation is the obvious internal cursor implementation. Using higher-level “set-based operators” bundles the cursor results into a single result set, meaning less API back-and-forth. … Read more
There’s a limit of 1MB on internal assets due to dynamic decompression; the 1MB limit also seems to apply to Cursor blobs but this doesn’t seem to be documented anywhere. Generally you should avoid blobs in SQLite as they perform poorly; instead save the blob data as a file and store the location of the … Read more
Generally speaking I always look for a set based approach (sometimes at the expense of changing the schema). However, this snippet does have its place.. — Declare & init (2008 syntax) DECLARE @CustomerID INT = 0 — Iterate over all customers WHILE (1 = 1) BEGIN — Get next customerId SELECT TOP 1 @CustomerID = … Read more