ais.sqlhelp

1 #!/usr/bin/env python 2 3 __version__ = '$Revision: 6398 $'.split()[1] # See man ident 4 __date__ = '$Date: 2007-06-20 10:42:04 -0700 (Wed, 20 Jun 2007) $'.split()[1] # FIX: pull out just the date 5 __author__ = 'Kurt Schwehr' 6 __doc__=''' 7 Helper functions to create SQL statements. 8 9 @license: GPL 10 @todo: How do I assemble queries like this:: 11 12 SELECT COUNT(samplename) AS count FROM \\ 13 (SELECT DISTINCT(samplename) AS samplename FROM \\ 14 ams WHERE corenum=1 AND coretype='p'); 15 16 @todo: subqueries 17 @todo: make a super class so that inserts and selects can verify based on the create str 18 @todo: take the super class info from the database? 19 20 @bug: FIX: write some doc tests! 21 @bug: had no protection from SQL injection attacks or quoting mistakes 22 23 @note: This is not as snazzy as SQLAlchemy or SQLObject, but it works and is simple 24 25 @author: '''+__author__+''' 26 @version: ''' + __version__ +''' 27 @copyright: 2006 28 @note: postgres is sort of case sensitive, so go all lowercase for fields and tables 29 @var __date__: Date of last svn commit 30 31 @undocumented: __version__ __author__ __doc__ myparser 32 ''' 33 34 # Python standard libraries 35 import sys 36 37 from BitVector import BitVector 38 39 # Local modules 40 # import verbosity 41 # from verbosity import BOMBASTIC,VERBOSE,TRACE,TERSE,ALWAYS 42 43 BOMBASTIC= 4 44 VERBOSE = 3 45 TRACE = 2 46 TERSE = 1 47 ALWAYS = 0 48 NEVER = 0 # Confusing, eh? 49 # Pass in 0 for NEVER from the user side 50 51 #

52 -def addVerbosityOptions(parser):

53 ''' 54 Added the verbosity options to a parser 55 ''' 56 parser.add_option('-v','--verbose',action="count",dest='verbosity',default=0, 57 help='how much information to give. Specify multiple times to increase verbosity') 58 parser.add_option('--verbosity',dest='verbosity',type='int', 59 help='Specify verbosity. Should be in the range of ' 60 +str(ALWAYS)+'...'+str(BOMBASTIC)+' (None...Bombastic)') 61 parser.add_option('--noisy',dest='verbosity', action='store_const', const=2*BOMBASTIC, 62 help='Go for the max verbosity ['+str(2*BOMBASTIC)+']')

63 64 ###################################################################### 65 import datetime

66 -def sec2timestamp(utcsec):

67 ''' 68 Convert a UTC sec time to a SQL timestamp 69 70 >>> sec2timestamp(int(1169703371)) 71 '2007-01-25 05:36:11' 72 73 ''' 74 75 d = datetime.datetime.utcfromtimestamp(utcsec) 76 s = '%d-%02d-%02d %02d:%02d:%02d' % (d.year,d.month,d.day,d.hour,d.minute,d.second) 77 return s

78 79 ######################################################################

80 -class select:

81 ''' 82 Construct an sql select query 83 84 Sometimes it just gets ugly having all that comma and WHERE AND 85 logic in there. This code takes care of that 86 '''

87 - def __init__(self,dbType='postgres'):

88 self.fields = [] 89 self.where = [] 90 self.limit = None 91 self.from_tables = [] 92 self.orderby = None 93 self.desc = False # descending sort if true 94 return

95

96 - def setorderby(self,field,desc=False):

97 "Make the returned rows come in some order" 98 if str != type(field): print "ERROR: fix throw type exception" 99 self.orderby = field 100 self.desc = desc 101 return

102

103 - def addfield(self,fieldname):

104 "Add a field name to return" 105 if str != type(fieldname): print "ERROR: fix throw type exception" 106 self.fields.append(fieldname) 107 return

108

109 - def addwhere(self,boolTest):

110 " Add expressions to chain together with ANDs" 111 if str != type(boolTest): 112 print "ERROR: fix throw type exception" 113 self.where.append(boolTest) 114 return

115

116 - def addfrom(self,tableName):

117 "Which tables the query will pull from" 118 if str != type(tableName): 119 print "ERROR: fix throw type exception" 120 self.from_tables.append(tableName) 121 return

122

123 - def setlimit(self,numOfItems):

124 "Set the maximum number of items to return" 125 if int != type(numOfItems): 126 print "ERROR: fix throw type exception" 127 self.limit = numOfItems 128 return

129

130 - def __str__(self):

131 "Return the query as a string" 132 if len(self.fields) < 1: print "ERROR: Must specify at least one from!\n FIX: throw some exception?" 133 s = 'SELECT ' 134 #for i in range (len(self.fields)-1): s += self.fields[i]+',' 135 if dbType == 'postgres': 136 s+=','.join([f.lower() for f in self.fields]) 137 else: 138 s+=','.join(self.fields) 139 s += self.fields[-1] + ' ' 140 141 if len(self.from_tables)<1: print "ERROR: fix throw some exception" 142 s += 'FROM ' 143 for i in range (len(self.from_tables)-1): 144 s += self.from_tables[i]+',' 145 s += self.from_tables[-1] 146 147 if (len(self.where)>0): s += ' WHERE ' 148 for i in range (len(self.where)-1): 149 s += self.where[i]+' AND ' 150 if (len(self.where)>0): s += self.where[-1] 151 152 if (None != self.orderby): 153 s += ' ORDER BY ' + self.orderby 154 if self.desc: s += ' DESC' 155 156 if (None != self.limit): 157 s += ' LIMIT ' + str(self.limit) 158 159 s += ';' 160 return s

161

162 -class create:

163 ''' 164 Helper for building create SQL commands. 165 166 FIX: add type checking - what did I mean by this??? 167 @todo: FIX - add a remove command to nuke a field 168 ''' 169

170 - def __init__(self,table,dbType='postgres'):

171 '''Kick it off with no fields 172 173 table - which table are we going to insert into''' 174 self.table = table 175 self.dbType = dbType 176 self.fields = [] 177 self.types = [] 178 self.postgis = []; # Tuples of (field,typeName,dim,srid) 179 return

180

181 - def add(self,field,typeStr):

182 ''' 183 Unchecked field. Provide the field and type all in one. Use 184 this if nothing matches what you need. 185 186 e.g.: 187 create.add('corenumber','INTEGER') 188 create.add('username','VARCHAR(40)') 189 create.add('id','INTEGER PRIMARY KEY') 190 191 @param field: name of the field 192 @param typeStr: the type of field 193 194 @todo: Allow setting of primary key in a simple way 195 ''' 196 self.fields.append(field) 197 self.types.append(typeStr) 198 return

199

200 - def addPrimaryKey(self,keyName='key'):

201 ''' 202 Add a primary key based on the field name. 203 @todo: FIX: complain if trying to add a second primary key 204 ''' 205 206 self.fields.append(keyName) 207 if 'sqlite' ==self.dbType: self.types.append('INTEGER PRIMARY KEY') 208 elif 'postgres'==self.dbType: self.types.append('SERIAL PRIMARY KEY') 209 else: 210 print 'Do not know how to construct a primary key for database type of',self.dbType 211 assert False 212 return

213

214 - def addInt(self,field):

215 ''' 216 SQL integer field 217 @param field: name of the field 218 ''' 219 self.fields.append(field) 220 self.types.append("INTEGER")

221

222 - def addReal(self,field):

223 ''' 224 SQL floating point field 225 @param field: name of the field 226 ''' 227 228 self.fields.append(field) 229 self.types.append("REAL")

230

231 - def addVarChar(self,field,length):

232 ''' 233 SQL VARCHAR field... variable length up to a max size 234 @param field: name of the field 235 @param length: max length of the field 236 ''' 237 self.fields.append(field) 238 self.types.append("VARCHAR("+str(length)+")")

239 240

241 - def addBool(self,field):

242 ''' 243 SQL Boolean field 244 @param field: name of the field 245 ''' 246 self.fields.append(field) 247 self.types.append("BOOL")

248

249 - def addBitVarying(self,field,length):

250 ''' 251 SQL Boolean field 252 @param field: name of the field 253 @param length: largest possible size 254 ''' 255 assert (length>0) 256 self.fields.append(field) 257 self.types.append('BIT VARYING('+str(length)+')')

258 259

260 - def addDecimal(self,field,precision=5,scale=0):

261 ''' 262 @param precision: overall digits including to right of decimal 263 @param scale: number of digits to the right of decimal 264 ''' 265 self.fields.append(field) 266 self.types.append('DECIMAL('+str(precision)+','+str(scale)+')')

267

268 - def addTimestamp(self,field):

269 '''SQL TIMESTAMP field 270 @param field: name of the field 271 ''' 272 self.fields.append(field) 273 self.types.append("TIMESTAMP") 274 return

275

276 - def addPostGIS(self,field,typeName,dimension,SRID='-1'):

277 ''' 278 Add a spatial column to the table using the OpenGIS 279 AddGeometryColumn function using current schema: 280 281 AddGeometryColumn(<table_name>,<column_name>, <srid>, <type>, <dimension>) 282 283 @param field: Name of the field in the db table 284 @type field: str 285 @param typeName: OpenGIS geometry type (e.g. POINT) 286 @type typeName: str 287 @param dimension: x,y would be 2 288 @type dimension: int 289 @param SRID: spatial referencing system identifier (FIX: give some more info!) 290 @type SRID: int 291 ''' 292 293 int(dimension) # Force this to be an int 294 self.postgis.append((field,typeName,dimension,SRID))

295 296

297 - def __str__(self):

298 '''Return the SQL string for the table creation 299 @rtype: str''' 300 assert (len(self.fields)>0) 301 assert (len(self.types)>0) 302 assert (len(self.fields)==len(self.types)) 303 cstr = 'CREATE TABLE ' 304 if 'postgres'==self.dbType: 305 cstr += self.table.lower()+' (' 306 for i in range(len(self.fields)-1): 307 cstr += str(self.fields[i].lower())+' '+str(self.types[i])+', ' 308 cstr += str(self.fields[-1].lower())+' '+str(self.types[-1]) 309 else: 310 cstr += self.table+' ( ' 311 for i in range(len(self.fields)-1): 312 cstr += str(self.fields[i])+' '+str(self.types[i])+', ' 313 cstr += str(self.fields[-1])+' '+str(self.types[-1]) 314 cstr += ' ); ' 315 316 cmds=[] 317 for postgisFields in self.postgis: 318 table = '\''+self.table.lower()+'\'' 319 field = '\''+postgisFields[0].lower()+'\'' 320 typeName = '\''+postgisFields[1]+'\'' 321 dim = str(postgisFields[2]) 322 SRID = str(postgisFields[3]) 323 fieldStr = ','.join((table,field,SRID,typeName,dim)) 324 addCmd = 'SELECT AddGeometryColumn('+fieldStr+')' 325 cmds.append(addCmd) 326 327 retStr = cstr + ';'.join(cmds) 328 if len(cmds)>0: retStr += ';' 329 return retStr

330

331 -class insert:

332 ''' 333 Help create an SQL insert statement for injecting data into a database. Wee! 334 335 @todo: FIX: provide some sort of validation, maybe with the CREATE string or class? 336 @todo: Put in a remove/delete call to pull a value out so that it is not inserted 337 338 @todo: FIX: MUST REWRITE THIS CLASS TO BE TYPE AWARE. 339 '''

340 - def __init__(self,table,dbType='postgres'):

341 '''Create an insert with no values 342 343 @param table: which table are we going to insert into 344 @param dbType: sqlite can not handle True/False keyworks (at version 3.2.8) 345 ''' 346 self.table = table 347 self.dbType = dbType 348 self.fields = [] 349 self.values = [] 350 self.postGIS = [] 351 return

352

353 - def dump(self):

354 '''Print out a safer dump to std out rather than str for debugging''' 355 print '\n === dump insert for table',self.table,'===' 356 for i in range(1,len(self.fields)): 357 print self.fields[i], self.values[i],' (',type(self.fields[i]), type(self.values[i]),')' 358 print

359

360 - def __str__(self):

361 "Return the SQL string for the insert" 362 if 0==len(self.fields): 363 print "WARNING: empty insert. returning empty string" 364 return "" # FIX: throw exception and a hissy fit 365 366 s = 'INSERT INTO ' 367 368 if 'postgres'==self.dbType: s+= self.table.lower() + ' ' 369 else: s+= self.table + ' ' 370 371 assert(len(self.fields)==len(self.values)) 372 fields = None 373 if 'postgres'==self.dbType: 374 fields = [f.lower() for f in self.fields] 375 else: fields = self.fields 376 #s1 = '' 377 #s2 = '' 378 379 # FIX: insert the 1st without a leading ',' 380 #s1 += str(self.fields[0]) 381 #if str == type(self.values[0]): s2 += '"'+str(self.values[0])+'"' 382 #else: s2 += str(self.values[0]) 383 384 # FIX: switch to join of strings for speed AND SIMPLICITY!! 385 #s1List=[] 386 s2List=[] 387 for i in range(len(fields)): 388 #s1List.append(str(fields[i])) 389 if bool == type(self.values[i]): 390 if 'sqlite'==self.dbType: 391 if self.values[i]: s2List.append('1') 392 else: s2List.append('0') 393 else: s2List.append(str(self.values[i])) 394 elif isinstance(self.values[i],BitVector): s2List.append('\''+str(self.values[i])+'\'') 395 elif str == type(self.values[i]): s2List.append('\''+str(self.values[i])+'\'') 396 elif type(self.values[i]) in (int, float): s2List.append(str(self.values[i])) 397 398 elif not self.values[i]: 399 print 'FIX: what was I trying to accomplish with this?',fields[i],self.values[i] 400 s2List.append('NULL') 401 else: 402 s2List.append(str(self.values[i])) 403 404 s1List = fields 405 for entry in self.postGIS: 406 s1List.append(entry[0].lower()) 407 # FIX: this hard codes WGS 84. Not good for a general library! 408 s2List.append('GeomFromText(\'' + entry[1] + '\',4326)') 409 410 s += '(' + ','.join(s1List) + ') VALUES (' + ','.join(s2List) + ');' 411 return s

412

413 - def addPostGIS(self,field,value):

414 ''' 415 Handle postGIS geometry 416 ''' 417 self.postGIS.append((field,value))

418 419

420 - def add(self,field,value):

421 '''Add a field value pair to the insert 422 423 @note: Integers and floats should NOT be converted to strings. 424 @param field: name of the field 425 @param value: value to be assigned to that field. 426 ''' 427 428 if type(value)==str: 429 # Prevent quotes from breaking out of a string/varchar. "" is SQL for " in a character string 430 value = value.replace('"','""') 431 self.fields.append(field) 432 self.values.append(value) 433 return

434 435 436 437 ###################################################################### 438 import datetime 439

440 -def sqlInsertStrFromList (table,aList,dbType='postgres'):

441 ''' Take a list and make an insert string. This works with 442 dictionaries too. Here is a quick example: 443 444 >>> aList = [('one',1),('2','two'),('threepoint',3.)] 445 >>> sqlInsertStrFromList('myTable',aList,dbType='sqlite') 446 "insert into myTable (one,2,threepoint) values (1,'two',3.0);" 447 >>> sqlInsertStrFromList('myTable',aList) 448 "insert into mytable (one,2,threepoint) values (1,'two',3.0);" 449 450 @param table: Which table to insert into 451 @type table: str 452 @param aList: list of tubles pairs to insert - (name, value) 453 @type aList(list) 454 @return: complete SQL insert command 455 @rtype: str 456 ''' 457 458 if 'postgres'==dbType: table = table.lower() 459 ins = "insert into " + table + " (" 460 first = [] 461 if 'postgres'==dbType: first = [f[0].lower() for f in aList] 462 else: first = [f[0] for f in aList] 463 ins += ','.join(first) + ") values (" 464 first=True 465 for pair in aList: 466 value = pair[1] 467 if first: first=False 468 else: ins+="," 469 # Make sure to quote all strings and timestamps 470 # print type(value) 471 # <type 'DateTime'> 472 # What way is better to handle this? 473 #if type(value) == str or type(value) == type(datetime()): ins+="'" 474 if type(value)!=int and type(value)!=float: ins+="'" 475 ins+=str(value) 476 if type(value)!=int and type(value)!=float: ins+="'" 477 #if type(value) == str or type(value) == type(datetime()): ins+="'" 478 ins += ");" 479 return ins

480 481 482 483 ###################################################################### 484 if __name__=='__main__': 485 from optparse import OptionParser 486 myparser = OptionParser(usage="%prog [options]", 487 version="%prog "+__version__) 488 myparser.add_option('--test','--doc-test',dest='doctest',default=False,action='store_true', 489 help='run the documentation tests') 490 491 addVerbosityOptions(myparser) 492 (options,args) = myparser.parse_args() 493 494 success=True 495 496 if options.doctest: 497 import os; print os.path.basename(sys.argv[0]), 'doctests ...', 498 sys.argv= [sys.argv[0]] 499 if options.verbosity>=TERSE: sys.argv.append('-v') 500 import doctest 501 numfail,numtests=doctest.testmod() 502 if numfail==0: print 'ok' 503 else: 504 print 'FAILED' 505 success=False 506 507 if not success: 508 sys.exit('Something Failed') 509

Source Code for Module ais.sqlhelp