Skip to content

Commit 58d56a1

Browse files
author
Daniel Kinzler
committed
recycle cursor object for update queries
git-svn-id: https://svn.toolserver.org/svnroot/daniel/duesenstuff/trunk/gpClient@581 9f2c43bc-b3c0-43f4-b155-41619b16f219
1 parent 7741f85 commit 58d56a1

File tree

2 files changed

+87
-45
lines changed

2 files changed

+87
-45
lines changed

‎python/gp/mediawiki.py‎

Lines changed: 23 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@
2323

2424
class MediaWikiGlue (MySQLGlue) :
2525

26-
def __init__( self, transport, graphname ) :
26+
def __init__( self, transport, graphname = None ) :
2727
super(MediaWikiGlue, self).__init__(transport, graphname)
2828

2929
self.table_prefix = ""
@@ -122,7 +122,7 @@ def new_client_connection( graphname, host = False, port = False ) :
122122

123123
@staticmethod
124124
def new_slave_connection( command, cwd = None, env = None ) :
125-
return MediaWikiGlue( SlaveTransport(command, cwd, env) )
125+
return MediaWikiGlue( SlaveTransport(command, cwd, env), None )
126126

127127

128128

@@ -172,7 +172,7 @@ def create_table ( self, ) :
172172
sql += self.table_obj.get_field_definitions()
173173
sql += ")"
174174

175-
self._query(sql)
175+
self._update(sql)
176176

177177
self.table = table
178178
self.table_obj.set_name( self.table )
@@ -186,6 +186,8 @@ def _query( self, sql ) :
186186
if ( self.big ): return self.glue.mysql_unbuffered_query(sql)
187187
else: return self.glue.mysql_query(sql)
188188

189+
def _update( self, sql ) : #TODO: port to PHP; use in PHP!
190+
return self.glue.mysql_update(sql)
189191

190192
def add_from_select ( self, select ) :
191193
sql= "REPLACE INTO " + self.table + " "
@@ -195,22 +197,22 @@ def add_from_select ( self, select ) :
195197
sql += self.title_field + " ) "
196198
sql += select
197199

198-
return self._query( sql )
200+
return self._update( sql )
199201

200202

201203
def delete_where ( self, where ) :
202204
sql= "DELETE FROM " + self.table + " "
203205
sql += where
204206

205-
return self._query( sql )
207+
return self._update( sql )
206208

207209

208210
def delete_using ( self, using, tableAlias = "T" ) :
209211
sql= "DELETE FROM " + tableAlias + " "
210212
sql += "USING " + self.table + " AS " + tableAlias + " "
211213
sql += using
212214

213-
return self._query( sql )
215+
return self._update( sql )
214216

215217

216218
def resolve_ids ( self, ) :
@@ -225,7 +227,7 @@ def resolve_ids ( self, ) :
225227
sql += " FROM " + self.table
226228
sql += " WHERE page_title IS NULL"
227229

228-
self._query( sql ); #copy page ids with no page title into temp table
230+
self._update( sql ); #copy page ids with no page title into temp table
229231

230232
sql = "SELECT P.page_id, P.page_namespace, P.page_title "
231233
sql += " FROM " + self.glue.wiki_table("page") + " AS P "
@@ -343,7 +345,7 @@ def subtract_table ( self, table, id_field = None ) :
343345
sql += " JOIN " + table.get_name() + " AS R "
344346
sql += " ON T." + self.id_field + " = R." + id_field
345347

346-
self._query(sql)
348+
self._update(sql)
347349
return True
348350

349351

@@ -356,7 +358,7 @@ def retain_table ( self, table, id_field = None ) :
356358
sql += " ON T." + self.id_field + " = R." + id_field
357359
sql += " WHERE R." + id_field + " IS NULL"
358360

359-
self._query(sql)
361+
self._update(sql)
360362
return True
361363

362364

@@ -365,15 +367,15 @@ def remove_page ( self, ns, title ) :
365367
sql += " WHERE " + self.namespace_field + " = %i" % int(ns)
366368
sql += " AND " + self.title_field + " = " + self.glue.quote_string(title)
367369

368-
self._query(sql)
370+
self._update(sql)
369371
return True
370372

371373

372374
def remove_page_id ( self, id ) :
373375
sql = "DELETE FROM " + self.table
374376
sql += " WHERE " + self.id_field + " = %i" % int(id)
375377

376-
self._query(sql)
378+
self._update(sql)
377379
return True
378380

379381

@@ -384,7 +386,7 @@ def strip_namespace ( self, ns, inverse = False ) :
384386
if ( isinstance(ns, (tuple, list, set)) ): sql += ( " not in " if inverse else " in " ) + self.glue.as_list( ns )
385387
else: sql += ( " != " if inverse else " = " ) + str(int(ns))
386388

387-
self._query(sql)
389+
self._update(sql)
388390
return True
389391

390392

@@ -401,7 +403,7 @@ def add_page ( self, id, ns, title ) :
401403
sql += " VALUES "
402404
sql += self.glue.as_list(values)
403405

404-
self._query( sql )
406+
self._update( sql )
405407
return True
406408

407409

@@ -413,7 +415,7 @@ def add_page_id ( self, id ) :
413415
sql += " VALUES "
414416
sql += self.glue.as_list(values)
415417

416-
self._query( sql )
418+
self._update( sql )
417419
return True
418420

419421

@@ -430,7 +432,7 @@ def expand_categories ( self, ns = None ) :
430432
sql += " from " + self.table + " as T "
431433
sql += " where page_namespace = %i " % NS_CATEGORY
432434

433-
self._query( sql )
435+
self._update( sql )
434436
#self.glue.dump_query("select * from " +tmp.get_name())
435437

436438
# ----------------------------------------------------------
@@ -481,8 +483,10 @@ def _add_subcategory_ids( self, cat, depth, without = None, without_depth = None
481483

482484
def get_size(self):
483485
res = self._query("SELECT COUNT(*) FROM " + self.table)
484-
row = res.fetchone()
485-
res.close()
486+
try:
487+
row = res.fetchone()
488+
finally:
489+
res.close()
486490

487491
return row[0]
488492

@@ -514,13 +518,13 @@ def add_pages_transclusing ( self, tag, ns = None ) :
514518

515519
def clear ( self, ) :
516520
sql = "TRUNCATE " + self.table
517-
self._query(sql)
521+
self._update(sql)
518522
return True
519523

520524

521525
def dispose ( self, ) :
522526
sql = "DROP TEMPORARY TABLE " + self.table
523-
self._query(sql)
527+
self._update(sql)
524528
return True
525529

526530

‎python/gp/mysql.py‎

Lines changed: 64 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -199,7 +199,7 @@ def insert (self, values ):
199199
sql += " VALUES "
200200
sql += self.as_list(values)
201201

202-
self.glue.mysql_query( sql )
202+
self.glue.mysql_update( sql )
203203

204204

205205

@@ -234,7 +234,7 @@ def insert (self, values ):
234234

235235
def flush (self):
236236
if len(self.buffer)>0:
237-
self.glue.mysql_query( self.buffer )
237+
self.glue.mysql_update( self.buffer )
238238
self.buffer = ""
239239

240240

@@ -273,7 +273,7 @@ def __init__( self, inserter, glue, table ):
273273
def drop (self):
274274
sql = "DROP TEMPORARY TABLE IF EXISTS %s" % self.table.get_name()
275275

276-
ok = self.glue.mysql_query( sql )
276+
ok = self.glue.mysql_update( sql )
277277
return ok
278278

279279

@@ -302,7 +302,9 @@ def __init__(self, transport, graphname = None ):
302302
super(MySQLGlue, self).__init__(transport, graphname)
303303

304304
self.connection = None
305+
305306
self.unbuffered = False
307+
self._update_cursor = None
306308

307309
self.temp_table_prefix = "gp_temp_"
308310
self.temp_table_db = None
@@ -341,30 +343,48 @@ def mysql_connect( self, server, username, password, db, port = 3306 ):
341343
def mysql_unbuffered_query( self, sql ):
342344
return self.mysql_query( sql, True )
343345

344-
def mysql_query( self, sql, unbuffered = None, dict_rows = False ):
346+
def mysql_update( self, sql ): #TODO: port to PHP; use in PHP!
347+
if not self.update_cursor:
348+
self._update_cursor = MySQLdb.cursors.SSCursor(self.connection)
349+
350+
self.mysql_query( sql, True, False, self._update_cursor )
351+
352+
return self.connection.affected_rows()
353+
354+
def mysql_query( self, sql, unbuffered = None, dict_rows = False, cursor = None ):
345355
if unbuffered is None:
346356
unbuffered = self.unbuffered
347357

348-
if unbuffered:
349-
if dict_rows:
350-
# no buffering, returns dicts
351-
cursor = MySQLdb.cursors.SSDictCursor(self.connection) # TESTME
352-
else:
353-
# no buffering, returns tuples
354-
cursor = MySQLdb.cursors.SSCursor(self.connection) # TESTME
358+
if cursor:
359+
using_new_cursor = False
355360
else:
356-
if dict_rows:
357-
# buffers result, returns dicts
358-
cursor = MySQLdb.cursors.DictCursor(self.connection) # TESTME
361+
using_new_cursor = True
362+
363+
if unbuffered:
364+
if dict_rows:
365+
# no buffering, returns dicts
366+
cursor = MySQLdb.cursors.SSDictCursor(self.connection) # TESTME
367+
else:
368+
# no buffering, returns tuples
369+
cursor = MySQLdb.cursors.SSCursor(self.connection) # TESTME
359370
else:
360-
# default: buffered tuples
361-
cursor = MySQLdb.cursors.Cursor(self.connection)
371+
if dict_rows:
372+
# buffers result, returns dicts
373+
cursor = MySQLdb.cursors.DictCursor(self.connection) # TESTME
374+
else:
375+
# default: buffered tuples
376+
cursor = MySQLdb.cursors.Cursor(self.connection)
362377

363378
with warnings.catch_warnings():
364379
#ignore MySQL warnings. use cursor.nfo() to get them.
365380
warnings.simplefilter("ignore")
366-
367-
cursor.execute( sql )
381+
382+
try:
383+
cursor.execute( sql )
384+
except:
385+
if using_new_cursor:
386+
cursor.close() #NOTE: *always* close the cursor if an exception ocurred.
387+
raise
368388

369389
if not dict_rows:
370390
# HACK: glue a fetch_dict method to a cursor that natively returns sequences from fetchone()
@@ -516,7 +536,7 @@ def next_id (self):
516536

517537
def drop_temp_table (self, spec ):
518538
sql = "DROP TEMPORARY TABLE %s" % spec.get_name()
519-
self.mysql_query(sql)
539+
self.mysql_update(sql)
520540

521541

522542
def make_temp_table (self, spec ):
@@ -533,7 +553,7 @@ def make_temp_table (self, spec ):
533553
sql += spec.get_field_definitions()
534554
sql += ")"
535555

536-
self.mysql_query(sql)
556+
self.mysql_update(sql)
537557

538558
return MySQLTable(table, spec.get_fields())
539559

@@ -546,8 +566,10 @@ def mysql_query_value (self, sql ):
546566

547567
def mysql_query_record (self, sql ):
548568
cursor = self.mysql_query( sql )
549-
a = cursor.fetchone()
550-
cursor.close()
569+
try:
570+
a = cursor.fetchone()
571+
finally:
572+
cursor.close()
551573

552574
if ( not a ): return None
553575
else: return a
@@ -623,7 +645,10 @@ def query_to_file (self, query, file, remote = False ):
623645
query += " INTO %s DATA OUTFILE " % r #TESTME
624646
query += self.quote_string(file)
625647

626-
return self.mysql_query(query)
648+
cursor = self.mysql_query(query)
649+
cursor.close()
650+
651+
return self.connection.affected_rows()
627652

628653

629654
def insert_from_file (self, table, file, remote = False ):
@@ -634,10 +659,20 @@ def insert_from_file (self, table, file, remote = False ):
634659
query += self.quote_string(file)
635660
query += " INTO TABLE %s " % table
636661

637-
return self.mysql_query(query)
662+
cursor = self.mysql_query(query)
663+
cursor.close()
664+
665+
return self.connection.affected_rows()
638666

639667

640668
def close(self):
669+
if self._update_cursor:
670+
try:
671+
self._update_cursor.close()
672+
except Exception as e:
673+
self._trace(__function__(), "failed to close mysql cursor: %s" % e)
674+
#XXX: do we really not care? can we go on? could there have been a commit pending?
675+
641676
if self.connection:
642677
try:
643678
self._trace(__function__(), "closing mysql connection")
@@ -656,7 +691,7 @@ def new_client_connection(graphname, host = False, port = False ):
656691

657692
@staticmethod
658693
def new_slave_connection(command, cwd = None, env = None ):
659-
return MySQLGlue( SlaveTransport(command, cwd, env) )
694+
return MySQLGlue( SlaveTransport(command, cwd, env), None )
660695

661696

662697
def dump_query (self, sql ):
@@ -665,7 +700,10 @@ def dump_query (self, sql ):
665700
res = self.mysql_query( sql )
666701
if ( not res ): return False
667702

668-
return self.dump_result( res )
703+
c = self.dump_result( res )
704+
res.close()
705+
706+
return c
669707

670708

671709
def dump_result (self, res ):

0 commit comments

Comments
 (0)