Package install :: Package MoSTBioDat :: Package DataBase :: Package Query :: Package SQLBuilder :: Module sqlbuilder
[hide private]
[frames] | no frames]

Source Code for Module install.MoSTBioDat.DataBase.Query.SQLBuilder.sqlbuilder

   1  """ 
   2  sqlobject.sqlbuilder 
   3  -------------------- 
   4   
   5  :author: Ian Bicking <ianb@colorstudy.com> 
   6   
   7  Builds SQL expressions from normal Python expressions. 
   8   
   9  Disclaimer 
  10  ---------- 
  11   
  12  This program is free software; you can redistribute it and/or modify 
  13  it under the terms of the GNU Lesser General Public License as 
  14  published by the Free Software Foundation; either version 2.1 of the 
  15  License, or (at your option any later version. 
  16   
  17  This program is distributed in the hope that it will be useful, 
  18  but WITHOUT ANY WARRANTY; without even the implied warranty of 
  19  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
  20  GNU General Public License for more details. 
  21   
  22  You should have received a copy of the GNU Lesser General Public 
  23  License along with this program; if not, write to the Free Software 
  24  Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, 
  25  USA. 
  26   
  27  Instructions 
  28  ------------ 
  29   
  30  To begin a SQL expression, you must use some sort of SQL object -- a 
  31  field, table, or SQL statement (``SELECT``, ``INSERT``, etc.)  You can 
  32  then use normal operators, with the exception of: `and`, `or`, `not`, 
  33  and `in`.  You can use the `AND`, `OR`, `NOT`, and `IN` functions 
  34  instead, or you can also use `&`, `|`, and `~` for `and`, `or`, and 
  35  `not` respectively (however -- the precidence for these operators 
  36  doesn't work as you would want, so you must use many parenthesis). 
  37   
  38  To create a sql field, table, or constant/function, use the namespaces 
  39  `table`, `const`, and `func`.  For instance, ``table.address`` refers 
  40  to the ``address`` table, and ``table.address.state`` refers to the 
  41  ``state`` field in the address table.  ``const.NULL`` is the ``NULL`` 
  42  SQL constant, and ``func.NOW()`` is the ``NOW()`` function call 
  43  (`const` and `func` are actually identicle, but the two names are 
  44  provided for clarity).  Once you create this object, expressions 
  45  formed with it will produce SQL statements. 
  46   
  47  The ``sqlrepr(obj)`` function gets the SQL representation of these 
  48  objects, as well as the proper SQL representation of basic Python 
  49  types (None==NULL). 
  50   
  51  There are a number of DB-specific SQL features that this does not 
  52  implement.  There are a bunch of normal ANSI features also not present 
  53  -- particularly left joins, among others.  You may wish to only use 
  54  this to generate ``WHERE`` clauses. 
  55   
  56  See the bottom of this module for some examples, and run it (i.e. 
  57  ``python sql.py``) to see the results of those examples. 
  58   
  59  """ 
  60   
  61  ######################################## 
  62  ## Constants 
  63  ######################################## 
  64   
65 -class VersionError(Exception):
66 pass
67 -class NoDefault:
68 pass
69 70 True, False = (1==1), (0==1) 71 72 import re, fnmatch 73 import operator 74 import threading 75 import types 76 77 from MoSTBioDat.DataBase.Query.SQLBuilder.converters import sqlrepr, registerConverter, TRUE, FALSE 78 79 safeSQLRE = re.compile(r'^[a-zA-Z_][a-zA-Z0-9_\.]*$')
80 -def sqlIdentifier(obj):
81 # some db drivers return unicode column names 82 return isinstance(obj, types.StringTypes) and bool(safeSQLRE.search(obj.strip()))
83 84
85 -def execute(expr, executor):
86 if hasattr(expr, 'execute'): 87 return expr.execute(executor) 88 else: 89 return expr
90 91 ######################################## 92 ## Expression generation 93 ######################################## 94
95 -class SQLExpression:
96 - def __add__(self, other):
97 return SQLOp("+", self, other)
98 - def __radd__(self, other):
99 return SQLOp("+", other, self)
100 - def __sub__(self, other):
101 return SQLOp("-", self, other)
102 - def __rsub__(self, other):
103 return SQLOp("-", other, self)
104 - def __mul__(self, other):
105 return SQLOp("*", self, other)
106 - def __rmul__(self, other):
107 return SQLOp("*", other, self)
108 - def __div__(self, other):
109 return SQLOp("/", self, other)
110 - def __rdiv__(self, other):
111 return SQLOp("/", other, self)
112 - def __pos__(self):
113 return SQLPrefix("+", self)
114 - def __neg__(self):
115 return SQLPrefix("-", self)
116 - def __pow__(self, other):
117 return SQLConstant("POW")(self, other)
118 - def __rpow__(self, other):
119 return SQLConstant("POW")(other, self)
120 - def __abs__(self):
121 return SQLConstant("ABS")(self)
122 - def __mod__(self, other):
123 return SQLConstant("MOD")(self, other)
124 - def __rmod__(self, other):
125 return SQLConstant("MOD")(other, self)
126
127 - def __lt__(self, other):
128 return SQLOp("<", self, other)
129 - def __le__(self, other):
130 return SQLOp("<=", self, other)
131 - def __gt__(self, other):
132 return SQLOp(">", self, other)
133 - def __ge__(self, other):
134 return SQLOp(">=", self, other)
135 - def __eq__(self, other):
136 if other is None: 137 return ISNULL(self) 138 else: 139 return SQLOp("=", self, other)
140 - def __ne__(self, other):
141 if other is None: 142 return ISNOTNULL(self) 143 else: 144 return SQLOp("<>", self, other)
145
146 - def __and__(self, other):
147 return SQLOp("AND", self, other)
148 - def __rand__(self, other):
149 return SQLOp("AND", other, self)
150 - def __or__(self, other):
151 return SQLOp("OR", self, other)
152 - def __ror__(self, other):
153 return SQLOp("OR", other, self)
154 - def __invert__(self):
155 return SQLPrefix("NOT", self)
156
157 - def __call__(self, *args):
158 return SQLCall(self, args)
159
160 - def __repr__(self):
161 try: 162 return self.__sqlrepr__(None) 163 except AssertionError: 164 return '<%s %s>' % ( 165 self.__class__.__name__, hex(id(self))[2:])
166
167 - def __str__(self):
168 return repr(self)
169
170 - def __cmp__(self, other):
171 raise VersionError, "Python 2.1+ required"
172 - def __rcmp__(self, other):
173 raise VersionError, "Python 2.1+ required"
174
175 - def startswith(self, s):
176 return STARTSWITH(self, s)
177 - def endswith(self, s):
178 return ENDSWITH(self, s)
179 - def contains(self, s):
180 return CONTAINSSTRING(self, s)
181
182 - def components(self):
183 return []
184
185 - def tablesUsed(self):
186 return self.tablesUsedDict().keys()
187 - def tablesUsedDict(self):
188 tables = {} 189 for table in self.tablesUsedImmediate(): 190 tables[str(table)] = 1 191 for component in self.components(): 192 tables.update(tablesUsedDict(component)) 193 return tables
194 - def tablesUsedImmediate(self):
195 return []
196 197 ####################################### 198 # Converter for SQLExpression instances 199 ####################################### 200
201 -def SQLExprConverter(value, db):
202 return value.__sqlrepr__()
203 204 registerConverter(SQLExpression, SQLExprConverter) 205
206 -def tablesUsedDict(obj):
207 if hasattr(obj, "tablesUsedDict"): 208 return obj.tablesUsedDict() 209 else: 210 return {}
211 212 operatorMap = { 213 "+": operator.add, 214 "/": operator.div, 215 "-": operator.sub, 216 "*": operator.mul, 217 "<": operator.lt, 218 "<=": operator.le, 219 "=": operator.eq, 220 "!=": operator.ne, 221 ">=": operator.ge, 222 ">": operator.gt, 223 "IN": operator.contains, 224 "IS": operator.eq, 225 } 226
227 -class SQLOp(SQLExpression):
228 - def __init__(self, op, expr1, expr2):
229 self.op = op.upper() 230 self.expr1 = expr1 231 self.expr2 = expr2
232 - def __sqlrepr__(self, db):
233 s1 = sqlrepr(self.expr1, db) 234 s2 = sqlrepr(self.expr2, db) 235 if s1[0] != '(' and s1 != 'NULL': 236 s1 = '(' + s1 + ')' 237 if s2[0] != '(' and s2 != 'NULL': 238 s2 = '(' + s2 + ')' 239 return "(%s %s %s)" % (s1, self.op, s2)
240 - def components(self):
241 return [self.expr1, self.expr2]
242 - def execute(self, executor):
243 if self.op == "AND": 244 return execute(self.expr1, executor) \ 245 and execute(self.expr2, executor) 246 elif self.op == "OR": 247 return execute(self.expr1, executor) \ 248 or execute(self.expr2, executor) 249 else: 250 return operatorMap[self.op.upper()](execute(self.expr1, executor), 251 execute(self.expr2, executor))
252 253 registerConverter(SQLOp, SQLExprConverter) 254
255 -class SQLCall(SQLExpression):
256 - def __init__(self, expr, args):
257 self.expr = expr 258 self.args = args
259 - def __sqlrepr__(self, db):
260 return "%s%s" % (sqlrepr(self.expr, db), sqlrepr(self.args, db))
261 - def components(self):
262 return [self.expr] + list(self.args)
263 - def execute(self, executor):
264 raise ValueError, "I don't yet know how to locally execute functions"
265 266 registerConverter(SQLCall, SQLExprConverter) 267
268 -class SQLPrefix(SQLExpression):
269 - def __init__(self, prefix, expr):
270 self.prefix = prefix 271 self.expr = expr
272 - def __sqlrepr__(self, db):
273 return "%s %s" % (self.prefix, sqlrepr(self.expr, db))
274 - def components(self):
275 return [self.expr]
276 - def execute(self, executor):
277 expr = execute(self.expr, executor) 278 if self.prefix == "+": 279 return expr 280 elif self.prefix == "-": 281 return -expr 282 elif self.prefix.upper() == "NOT": 283 return not expr
284 285 registerConverter(SQLPrefix, SQLExprConverter) 286
287 -class SQLConstant(SQLExpression):
288 - def __init__(self, const):
289 self.const = const
290 - def __sqlrepr__(self, db):
291 return self.const
292 - def execute(self, executor):
293 raise ValueError, "I don't yet know how to execute SQL constants"
294 295 registerConverter(SQLConstant, SQLExprConverter) 296
297 -class SQLTrueClauseClass(SQLExpression):
298 - def __sqlrepr__(self, db):
299 return "1 = 1"
300 - def execute(self, executor):
301 return 1
302 303 SQLTrueClause = SQLTrueClauseClass() 304 305 registerConverter(SQLTrueClauseClass, SQLExprConverter) 306 307 ######################################## 308 ## Namespaces 309 ######################################## 310
311 -class Field(SQLExpression):
312 - def __init__(self, tableName, fieldName):
313 self.tableName = tableName 314 self.fieldName = fieldName
315 - def __sqlrepr__(self, db):
316 return self.tableName + "." + self.fieldName
317 - def tablesUsedImmediate(self):
318 return [self.tableName]
319 - def execute(self, executor):
320 return executor.field(self.tableName, self.fieldName)
321
322 -class SQLObjectField(Field):
323 - def __init__(self, tableName, fieldName, original):
324 self.original = original 325 Field.__init__(self, tableName, fieldName)
326 327 registerConverter(SQLObjectField, SQLExprConverter) 328 329
330 -class UnicodeField(SQLObjectField):
331 - def __init__(self, tableName, fieldName, original, column):
332 SQLObjectField.__init__(self, tableName, fieldName, original) 333 self.column = column
334 - def __eq__(self, other):
335 if other is None: 336 return ISNULL(self) 337 if isinstance(other, unicode): 338 other = other.encode(self.column.dbEncoding) 339 return SQLOp('=', self, other)
340 - def __ne__(self, other):
341 if other is None: 342 return ISNOTNULL(self) 343 if isinstance(other, unicode): 344 other = other.encode(self.column.dbEncoding) 345 return SQLOp('<>', self, other)
346 347 registerConverter(UnicodeField, SQLExprConverter) 348 349
350 -class Table(SQLExpression):
351 FieldClass = Field 352
353 - def __init__(self, tableName):
354 self.tableName = tableName
355 - def __getattr__(self, attr):
356 if attr.startswith('__'): 357 raise AttributeError 358 return self.FieldClass(self.tableName, attr)
359 - def __sqlrepr__(self, db):
360 return str(self.tableName)
361 - def execute(self, executor):
362 raise ValueError, "Tables don't have values"
363
364 -class SQLObjectTable(Table):
365 FieldClass = SQLObjectField 366 UnicodeFieldClass = UnicodeField 367
368 - def __init__(self, soClass):
369 self.soClass = soClass 370 assert soClass.sqlmeta.table, ( 371 "Bad table name in class %r: %r" 372 % (soClass, soClass.sqlmeta.table)) 373 Table.__init__(self, soClass.sqlmeta.table)
374
375 - def __getattr__(self, attr):
376 if attr.startswith('__'): 377 raise AttributeError 378 if attr == 'id': 379 return self.FieldClass(self.tableName, self.soClass.sqlmeta.idName, attr) 380 elif attr not in self.soClass.sqlmeta.columns: 381 raise AttributeError("%s instance has no attribute '%s'" % (self.soClass.__name__, attr)) 382 else: 383 column = self.soClass.sqlmeta.columns[attr] 384 if hasattr(column, "dbEncoding"): 385 return self.UnicodeFieldClass(self.tableName, column.dbName, 386 attr, column) 387 else: 388 return self.FieldClass(self.tableName, column.dbName, attr)
389
390 -class TableSpace:
391 TableClass = Table 392
393 - def __getattr__(self, attr):
394 if attr.startswith('__'): 395 raise AttributeError 396 return self.TableClass(attr)
397
398 -class ConstantSpace:
399 - def __getattr__(self, attr):
400 if attr.startswith('__'): 401 raise AttributeError 402 return SQLConstant(attr)
403 404 405 ######################################## 406 ## Table aliases 407 ######################################## 408
409 -class AliasField(Field):
410 as_string = '' # set it to "AS" if your database requires it 411
412 - def __init__(self, tableName, fieldName, alias):
413 Field.__init__(self, tableName, fieldName) 414 self.alias = alias
415
416 - def __sqlrepr__(self, db):
417 return self.alias + "." + self.fieldName
418
419 - def tablesUsedImmediate(self):
420 return ["%s %s %s" % (self.tableName, self.as_string, self.alias)]
421
422 -class AliasTable(Table):
423 FieldClass = AliasField 424 425 _alias_lock = threading.Lock() 426 _alias_counter = 0 427
428 - def __init__(self, table, alias=None):
429 if hasattr(table, "sqlmeta"): 430 tableName = table.sqlmeta.table 431 else: 432 tableName = table 433 table = None 434 Table.__init__(self, tableName) 435 self.table = table 436 if alias is None: 437 self._alias_lock.acquire() 438 try: 439 AliasTable._alias_counter += 1 440 alias = "%s_alias%d" % (tableName, AliasTable._alias_counter) 441 finally: 442 self._alias_lock.release() 443 self.alias = alias
444
445 - def __getattr__(self, attr):
446 if attr.startswith('__'): 447 raise AttributeError 448 if self.table: 449 attr = getattr(self.table.q, attr).fieldName 450 return self.FieldClass(self.tableName, attr, self.alias)
451
452 -class Alias:
453 - def __init__(self, table, alias=None):
454 self.q = AliasTable(table, alias)
455 456 457 ######################################## 458 ## SQL Statements 459 ######################################## 460
461 -class Select(SQLExpression):
462 - def __init__(self, items, where=NoDefault, groupBy=NoDefault, 463 having=NoDefault, orderBy=NoDefault, limit=NoDefault, join=NoDefault):
464 if type(items) is not type([]) and type(items) is not type(()): 465 items = [items] 466 self.items = items 467 self.whereClause = where 468 self.groupBy = groupBy 469 self.having = having 470 self.orderBy = orderBy 471 self.limit = limit 472 self.join = join
473
474 - def __sqlrepr__(self, db):
475 select = "SELECT %s" % ", ".join([sqlrepr(v, db) for v in self.items]) 476 join = [] 477 if self.join is not NoDefault: 478 if isinstance(self.join, SQLJoin): 479 join.append(self.join) 480 else: 481 join.extend(self.join) 482 tables = {} 483 things = list(self.items) 484 485 486 if self.whereClause is not NoDefault: 487 things.append(self.whereClause) 488 489 for thing in things: 490 if isinstance(thing, SQLExpression): 491 tables.update(tablesUsedDict(thing)) 492 for j in join: 493 if j.table1 in tables: del tables[j.table1] 494 if j.table2 in tables: del tables[j.table2] 495 tables = tables.keys() 496 if tables: 497 select += " FROM %s" % ", ".join(tables) 498 elif join: 499 select += " FROM " 500 for j in join: 501 if tables and j.table1: 502 sep = ", " 503 else: 504 sep = " " 505 select += sep + sqlrepr(j, db) 506 507 if self.whereClause is not NoDefault: 508 select += " WHERE %s" % sqlrepr(self.whereClause, db) 509 if self.groupBy is not NoDefault: 510 groupBy = sqlrepr(self.groupBy, db) 511 if isinstance(self.groupBy, list) or isinstance(self.groupBy, tuple): 512 groupBy = groupBy[1:-1] # Remove parents 513 select += " GROUP BY %s" % groupBy 514 if self.having is not NoDefault: 515 select += " HAVING %s" % sqlrepr(self.having, db) 516 if self.orderBy is not NoDefault: 517 orderBy = sqlrepr(self.orderBy, db) 518 if isinstance(self.orderBy, list) or isinstance(self.orderBy, tuple): 519 orderBy = orderBy[1:-1] # Remove parents 520 select += " ORDER BY %s" % orderBy 521 if self.limit is not NoDefault: 522 select += " LIMIT %s" % sqlrepr(self.limit, db) 523 return select
524 525 registerConverter(Select, SQLExprConverter) 526
527 -class Insert(SQLExpression):
528 - def __init__(self, table, valueList=None, values=None, template=NoDefault):
529 self.template = template 530 self.table = table 531 if valueList: 532 if values: 533 raise TypeError, "You may only give valueList *or* values" 534 self.valueList = valueList 535 else: 536 self.valueList = [values]
537 - def __sqlrepr__(self, db):
538 if not self.valueList: 539 return '' 540 insert = "INSERT INTO %s" % self.table 541 allowNonDict = True 542 template = self.template 543 if template is NoDefault and type(self.valueList[0]) is type({}): 544 template = self.valueList[0].keys() 545 allowNonDict = False 546 if template is not NoDefault: 547 insert += " (%s)" % ", ".join(template) 548 insert += " VALUES " 549 listToJoin = [] 550 listToJoin_app = listToJoin.append 551 for value in self.valueList: 552 if type(value) is type({}): 553 if template is NoDefault: 554 raise TypeError, "You can't mix non-dictionaries with dictionaries in an INSERT if you don't provide a template (%s)" % repr(value) 555 value = dictToList(template, value) 556 elif not allowNonDict: 557 raise TypeError, "You can't mix non-dictionaries with dictionaries in an INSERT if you don't provide a template (%s)" % repr(value) 558 listToJoin_app("(%s)" % ", ".join([sqlrepr(v, db) for v in value])) 559 insert = "%s%s" % (insert, ", ".join(listToJoin)) 560 return insert
561 562 registerConverter(Insert, SQLExprConverter) 563
564 -def dictToList(template, dict):
565 list = [] 566 for key in template: 567 list.append(dict[key]) 568 if len(dict.keys()) > len(template): 569 raise TypeError, "Extra entries in dictionary that aren't asked for in template (template=%s, dict=%s)" % (repr(template), repr(dict)) 570 return list
571
572 -class Update(SQLExpression):
573 - def __init__(self, table, values, template=NoDefault, where=NoDefault):
574 self.table = table 575 self.values = values 576 self.template = template 577 self.whereClause = where
578 - def __sqlrepr__(self, db):
579 update = "%s %s" % (self.sqlName(), self.table) 580 update += " SET" 581 first = True 582 if self.template is not NoDefault: 583 for i in range(len(self.template)): 584 if first: 585 first = False 586 else: 587 update += "," 588 update += " %s=%s" % (self.template[i], sqlrepr(self.values[i], db)) 589 else: 590 for key, value in self.values.items(): 591 if first: 592 first = False 593 else: 594 update += "," 595 update += " %s=%s" % (key, sqlrepr(value, db)) 596 if self.whereClause is not NoDefault: 597 update += " WHERE %s" % sqlrepr(self.whereClause, db) 598 return update
599 - def sqlName(self):
600 return "UPDATE"
601 602 registerConverter(Update, SQLExprConverter) 603
604 -class Delete(SQLExpression):
605 """To be safe, this will signal an error if there is no where clause, 606 unless you pass in where=None to the constructor."""
607 - def __init__(self, table, where=NoDefault):
608 self.table = table 609 if where is NoDefault: 610 raise TypeError, "You must give a where clause or pass in None to indicate no where clause" 611 self.whereClause = where
612 - def __sqlrepr__(self, db):
613 whereClause = self.whereClause 614 if whereClause is None: 615 return "DELETE FROM %s" % self.table 616 if isinstance(whereClause, SQLExpression): 617 whereClause = sqlrepr(whereClause, db) 618 return "DELETE FROM %s WHERE %s" % (self.table, whereClause)
619 620 registerConverter(Delete, SQLExprConverter) 621
622 -class Replace(Update):
623 - def sqlName(self):
624 return "REPLACE"
625 626 registerConverter(Replace, SQLExprConverter) 627 628 ######################################## 629 ## SQL Builtins 630 ######################################## 631
632 -class DESC(SQLExpression):
633
634 - def __init__(self, expr):
635 self.expr = expr
636
637 - def __sqlrepr__(self, db):
638 if isinstance(self.expr, DESC): 639 return sqlrepr(self.expr.expr, db) 640 return '%s DESC' % sqlrepr(self.expr, db)
641
642 -def AND(*ops):
643 if not ops: 644 return None 645 op1 = ops[0] 646 ops = ops[1:] 647 if ops: 648 return SQLOp("AND", op1, AND(*ops)) 649 else: 650 return op1
651
652 -def OR(*ops):
653 if not ops: 654 return None 655 op1 = ops[0] 656 ops = ops[1:] 657 if ops: 658 return SQLOp("OR", op1, OR(*ops)) 659 else: 660 return op1
661
662 -def NOT(op):
663 return SQLPrefix("NOT", op)
664
665 -def _IN(item, list):
666 return SQLOp("IN", item, list)
667
668 -def IN(item, list):
669 if isinstance(list, Select): 670 return INSubquery(item, list) 671 else: 672 return _IN(item, list)
673
674 -def NOTIN(item, list):
675 if isinstance(list, Select): 676 return NOTINSubquery(item, list) 677 else: 678 return NOT(_IN(item, list))
679
680 -def STARTSWITH(expr, pattern):
681 return SQLOp("LIKE", expr, _LikeQuoted(pattern) + '%')
682
683 -def ENDSWITH(expr, pattern):
684 return SQLOp("LIKE", expr, '%' + _LikeQuoted(pattern))
685
686 -def CONTAINSSTRING(expr, pattern):
687 return SQLOp("LIKE", expr, '%' + _LikeQuoted(pattern) + '%')
688
689 -def ISNULL(expr):
690 return SQLOp("IS", expr, None)
691
692 -def ISNOTNULL(expr):
693 return SQLOp("IS NOT", expr, None)
694
695 -class _LikeQuoted:
696 # It assumes prefix and postfix are strings; usually just a percent sign. 697 698 # @@: I'm not sure what the quoting rules really are for all the 699 # databases 700
701 - def __init__(self, expr):
702 self.expr = expr 703 self.prefix = '' 704 self.postfix = ''
705
706 - def __radd__(self, s):
707 self.prefix = s + self.prefix 708 return self
709
710 - def __add__(self, s):
711 self.postfix += s 712 return self
713
714 - def __sqlrepr__(self, db):
715 s = self.expr 716 if isinstance(s, SQLExpression): 717 values = [] 718 if self.prefix: 719 values.append("'%s'" % self.prefix) 720 s = _quote_percent(sqlrepr(s, db), db) 721 values.append(s) 722 if self.postfix: 723 values.append("'%s'" % self.postfix) 724 if db == "mysql": 725 return "CONCAT(%s)" % ", ".join(values) 726 else: 727 return " || ".join(values) 728 else: # assuming s is a string 729 s = _quote_percent(s, db) 730 return "'%s%s%s'" % (self.prefix, s, self.postfix)
731
732 -def _quote_percent(s, db):
733 if db in ('postgres', 'mysql'): 734 s = s.replace('%', '\\%') 735 else: 736 s = s.replace('%', '%%') 737 return s
738 739 ######################################## 740 ## SQL JOINs 741 ######################################## 742
743 -class SQLJoin(SQLExpression):
744 - def __init__(self, table1, table2, op=','):
745 if table1 and type(table1) <> str: 746 if isinstance(table1, Alias): 747 table1 = "%s %s %s" % (table1.q.tableName, AliasField.as_string, table1.q.alias) 748 else: 749 table1 = table1.sqlmeta.table 750 if type(table2) <> str: 751 if isinstance(table2, Alias): 752 table2 = "%s %s %s" % (table2.q.tableName, AliasField.as_string, table2.q.alias) 753 else: 754 table2 = table2.sqlmeta.table 755 self.table1 = table1 756 self.table2 = table2 757 self.op = op
758
759 - def __sqlrepr__(self, db):
760 if self.table1: 761 return "%s%s %s" % (self.table1, self.op, self.table2) 762 else: 763 return "%s %s" % (self.op, self.table2)
764 765 registerConverter(SQLJoin, SQLExprConverter) 766
767 -def JOIN(table1, table2):
768 return SQLJoin(table1, table2, " JOIN")
769
770 -def INNERJOIN(table1, table2):
771 return SQLJoin(table1, table2, " INNER JOIN")
772
773 -def CROSSJOIN(table1, table2):
774 return SQLJoin(table1, table2, " CROSS JOIN")
775
776 -def STRAIGHTJOIN(table1, table2):
777 return SQLJoin(table1, table2, " STRAIGHT JOIN")
778
779 -def LEFTJOIN(table1, table2):
780 return SQLJoin(table1, table2, " LEFT JOIN")
781
782 -def LEFTOUTERJOIN(table1, table2):
783 return SQLJoin(table1, table2, " LEFT OUTER JOIN")
784
785 -def NATURALJOIN(table1, table2):
786 return SQLJoin(table1, table2, " NATURAL JOIN")
787
788 -def NATURALLEFTJOIN(table1, table2):
789 return SQLJoin(table1, table2, " NATURAL LEFT JOIN")
790
791 -def NATURALLEFTOUTERJOIN(table1, table2):
792 return SQLJoin(table1, table2, " NATURAL LEFT OUTER JOIN")
793
794 -def RIGHTJOIN(table1, table2):
795 return SQLJoin(table1, table2, " RIGHT JOIN")
796
797 -def RIGHTOUTERJOIN(table1, table2):
798 return SQLJoin(table1, table2, " RIGHT OUTER JOIN")
799
800 -def NATURALRIGHTJOIN(table1, table2):
801 return SQLJoin(table1, table2, " NATURAL RIGHT JOIN")
802
803 -def NATURALRIGHTOUTERJOIN(table1, table2):
804 return SQLJoin(table1, table2, " NATURAL RIGHT OUTER JOIN")
805
806 -def FULLJOIN(table1, table2):
807 return SQLJoin(table1, table2, " FULL JOIN")
808
809 -def FULLOUTERJOIN(table1, table2):
810 return SQLJoin(table1, table2, " FULL OUTER JOIN")
811
812 -def NATURALFULLJOIN(table1, table2):
813 return SQLJoin(table1, table2, " NATURAL FULL JOIN")
814
815 -def NATURALFULLOUTERJOIN(table1, table2):
816 return SQLJoin(table1, table2, " NATURAL FULL OUTER JOIN")
817
818 -class SQLJoinConditional(SQLJoin):
819 """Conditional JOIN"""
820 - def __init__(self, table1, table2, op, on_condition=None, using_columns=None):
821 """For condition you must give on_condition or using_columns but not both 822 823 on_condition can be a string or SQLExpression, for example 824 Table1.q.col1 == Table2.q.col2 825 using_columns can be a string or a list of columns, e.g. 826 (Table1.q.col1, Table2.q.col2) 827 """ 828 if not on_condition and not using_columns: 829 raise TypeError, "You must give ON condition or USING columns" 830 if on_condition and using_columns: 831 raise TypeError, "You must give ON condition or USING columns but not both" 832 SQLJoin.__init__(self, table1, table2, op) 833 self.on_condition = on_condition 834 self.using_columns = using_columns
835
836 - def __sqlrepr__(self, db):
837 if self.on_condition: 838 on_condition = self.on_condition 839 if hasattr(on_condition, "__sqlrepr__"): 840 on_condition = sqlrepr(on_condition, db) 841 join = "%s %s ON %s" % (self.op, self.table2, on_condition) 842 if self.table1: 843 join = "%s %s" % (self.table1, join) 844 return join 845 elif self.using_columns: 846 using_columns = [] 847 for col in self.using_columns: 848 if hasattr(col, "__sqlrepr__"): 849 col = sqlrepr(col, db) 850 using_columns.append(col) 851 using_columns = ", ".join(using_columns) 852 join = "%s %s USING (%s)" % (self.op, self.table2, using_columns) 853 if self.table1: 854 join = "%s %s" % (self.table1, join) 855 return join 856 else: 857 RuntimeError, "Impossible error"
858 859 registerConverter(SQLJoinConditional, SQLExprConverter) 860
861 -def INNERJOINConditional(table1, table2, on_condition=None, using_columns=None):
862 return SQLJoinConditional(table1, table2, "INNER JOIN", on_condition, using_columns)
863
864 -def LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None):
865 return SQLJoinConditional(table1, table2, "LEFT JOIN", on_condition, using_columns)
866
867 -def LEFTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
868 return SQLJoinConditional(table1, table2, "LEFT OUTER JOIN", on_condition, using_columns)
869
870 -def RIGHTJOINConditional(table1, table2, on_condition=None, using_columns=None):
871 return SQLJoinConditional(table1, table2, "RIGHT JOIN", on_condition, using_columns)
872
873 -def RIGHTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
874 return SQLJoinConditional(table1, table2, "RIGHT OUTER JOIN", on_condition, using_columns)
875
876 -def FULLJOINConditional(table1, table2, on_condition=None, using_columns=None):
877 return SQLJoinConditional(table1, table2, "FULL JOIN", on_condition, using_columns)
878
879 -def FULLOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
880 return SQLJoinConditional(table1, table2, "FULL OUTER JOIN", on_condition, using_columns)
881
882 -class SQLJoinOn(SQLJoinConditional):
883 """Conditional JOIN ON"""
884 - def __init__(self, table1, table2, op, on_condition):
885 SQLJoinConditional.__init__(self, table1, table2, op, on_condition)
886 887 registerConverter(SQLJoinOn, SQLExprConverter) 888
889 -class SQLJoinUsing(SQLJoinConditional):
890 """Conditional JOIN USING"""
891 - def __init__(self, table1, table2, op, using_columns):
892 SQLJoinConditional.__init__(self, table1, table2, op, None, using_columns)
893 894 registerConverter(SQLJoinUsing, SQLExprConverter) 895
896 -def INNERJOINOn(table1, table2, on_condition):
897 return SQLJoinOn(table1, table2, "INNER JOIN", on_condition)
898
899 -def LEFTJOINOn(table1, table2, on_condition):
900 return SQLJoinOn(table1, table2, "LEFT JOIN", on_condition)
901
902 -def LEFTOUTERJOINOn(table1, table2, on_condition):
903 return SQLJoinOn(table1, table2, "LEFT OUTER JOIN", on_condition)
904
905 -def RIGHTJOINOn(table1, table2, on_condition):
906 return SQLJoinOn(table1, table2, "RIGHT JOIN", on_condition)
907
908 -def RIGHTOUTERJOINOn(table1, table2, on_condition):
909 return SQLJoinOn(table1, table2, "RIGHT OUTER JOIN", on_condition)
910
911 -def FULLJOINOn(table1, table2, on_condition):
912 return SQLJoinOn(table1, table2, "FULL JOIN", on_condition)
913
914 -def FULLOUTERJOINOn(table1, table2, on_condition):
915 return SQLJoinOn(table1, table2, "FULL OUTER JOIN", on_condition)
916
917 -def INNERJOINUsing(table1, table2, using_columns):
918 return SQLJoinUsing(table1, table2, "INNER JOIN", using_columns)
919
920 -def LEFTJOINUsing(table1, table2, using_columns):
921 return SQLJoinUsing(table1, table2, "LEFT JOIN", using_columns)
922
923 -def LEFTOUTERJOINUsing(table1, table2, using_columns):
924 return SQLJoinUsing(table1, table2, "LEFT OUTER JOIN", using_columns)
925
926 -def RIGHTJOINUsing(table1, table2, using_columns):
927 return SQLJoinUsing(table1, table2, "RIGHT JOIN", using_columns)
928
929 -def RIGHTOUTERJOINUsing(table1, table2, using_columns):
930 return SQLJoinUsing(table1, table2, "RIGHT OUTER JOIN", using_columns)
931
932 -def FULLJOINUsing(table1, table2, using_columns):
933 return SQLJoinUsing(table1, table2, "FULL JOIN", using_columns)
934
935 -def FULLOUTERJOINUsing(table1, table2, using_columns):
936 return SQLJoinUsing(table1, table2, "FULL OUTER JOIN", using_columns)
937 938 939 ######################################## 940 ## Subqueries (subselects) 941 ######################################## 942
943 -class OuterField(Field):
944 - def tablesUsedImmediate(self):
945 return []
946
947 -class OuterTable(Table):
948 FieldClass = OuterField 949
950 - def __init__(self, table):
951 if hasattr(table, "sqlmeta"): 952 tableName = table.sqlmeta.table 953 else: 954 tableName = table 955 table = None 956 Table.__init__(self, tableName) 957 self.table = table
958
959 -class Outer:
960 - def __init__(self, table):
961 self.q = OuterTable(table)
962 963
964 -class LIKE(SQLExpression):
965 op = "LIKE" 966
967 - def __init__(self, expr, string):
968 self.expr = expr 969 self.string = string
970 - def __sqlrepr__(self, db):
971 return "(%s %s (%s))" % (sqlrepr(self.expr, db), self.op, sqlrepr(self.string, db))
972 - def components(self):
973 return [self.expr, self.string]
974 - def execute(self, executor):
975 if not hasattr(self, '_regex'): 976 # @@: Crude, not entirely accurate 977 dest = self.string 978 dest = dest.replace("%%", "\001") 979 dest = dest.replace("*", "\002") 980 dest = dest.replace("%", "*") 981 dest = dest.replace("\001", "%") 982 dest = dest.replace("\002", "[*]") 983 self._regex = re.compile(fnmatch.translate(dest), re.I) 984 return self._regex.search(execute(self.expr, executor))
985
986 -class RLIKE(LIKE):
987 op = "RLIKE" 988
989 - def _get_op(self, db):
990 if db in ('mysql', 'maxdb', 'firebird'): 991 return "RLIKE" 992 elif db == 'sqlite': 993 return "REGEXP" 994 elif db == 'postgres': 995 return "~" 996 else: 997 return "LIKE"
998 - def __sqlrepr__(self, db):
999 return "(%s %s (%s))" % ( 1000 sqlrepr(self.expr, db), self._get_op(db), sqlrepr(self.string, db) 1001 )
1002 - def execute(self, executor):
1003 self.op = self._get_op(self.db) 1004 return LIKE.execute(self, executor)
1005 1006
1007 -class INSubquery(SQLExpression):
1008 op = "IN" 1009
1010 - def __init__(self, item, subquery):
1011 self.item = item 1012 self.subquery = subquery
1013 - def components(self):
1014 return [self.item]
1015 - def __sqlrepr__(self, db):
1016 return "%s %s (%s)" % (sqlrepr(self.item, db), self.op, sqlrepr(self.subquery, db))
1017
1018 -class NOTINSubquery(INSubquery):
1019 op = "NOT IN"
1020 1021
1022 -class Subquery(SQLExpression):
1023 - def __init__(self, op, subquery):
1024 self.op = op 1025 self.subquery = subquery
1026
1027 - def __sqlrepr__(self, db):
1028 return "%s (%s)" % (self.op, sqlrepr(self.subquery, db))
1029
1030 -def EXISTS(subquery):
1031 return Subquery("EXISTS", subquery)
1032
1033 -def NOTEXISTS(subquery):
1034 return Subquery("NOT EXISTS", subquery)
1035
1036 -def SOME(subquery):
1037 return Subquery("SOME", subquery)
1038
1039 -def ANY(subquery):
1040 return Subquery("ANY", subquery)
1041
1042 -def ALL(subquery):
1043 return Subquery("ALL", subquery)
1044 1045 1046 ######################################## 1047 ## Global initializations 1048 ######################################## 1049 1050 table = TableSpace() 1051 const = ConstantSpace() 1052 func = const 1053 1054 ##### MAIN ############################# 1055 ### Example of usage ################### 1056 if __name__ == "__main__": 1057 pass 1058 # query=sqlrepr(Select([table.adress.name])) 1059 # print query 1060 # query=Select([table.address.name, table.address.state],where=(table.address.name)) 1061 # print sqlrepr(query,db='mysql') 1062 # query=Insert(table.address, [{"name": "Andrzej", "address": "Neufeld"}, {"name": "Thomas", "address": "Wien"}]) 1063 # print sqlrepr(query,db='mysql') 1064 # query= Delete(table.address, where="Andrzej"==table.address.name) 1065 # print sqlrepr(query,db='mysql') 1066 # query=Select([table.Users.user_ID, table.Users.user_name], where=LIKE(table.Users.user_ID, "%1%")) 1067 # print sqlrepr(query,db='mysql') 1068 # query=Update(table.address, {"lastModified": const.NOW()}) 1069 # print sqlrepr(query,db='mysql') 1070 # query=Replace(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address')) 1071 # print sqlrepr(query,db='mysql') 1072