web.web.db

1 """ 2 Database API 3 (part of web.py) 4 """ 5 6 __all__ = [ 7 "UnknownParamstyle", "UnknownDB", "TransactionError", 8 "sqllist", "sqlors", "reparam", "sqlquote", 9 "SQLQuery", "SQLParam", "sqlparam", 10 "SQLLiteral", "sqlliteral", 11 "database", 'DB', 12 ] 13 14 import time 15 try: 16 import datetime 17 except ImportError: 18 datetime = None 19 20 try: set 21 except NameError: 22 from sets import Set as set 23 24 from utils import threadeddict, storage, iters, iterbetter, safestr, safeunicode 25 26 try: 27 # db module can work independent of web.py 28 from webapi import debug, config 29 except: 30 import sys 31 debug = sys.stderr 32 config = storage() 33

34 -class UnknownDB(Exception):

35 """raised for unsupported dbms""" 36 pass

37

38 -class _ItplError(ValueError):

39 - def __init__(self, text, pos):

40 ValueError.__init__(self) 41 self.text = text 42 self.pos = pos

43 - def __str__(self):

44 return "unfinished expression in %s at char %d" % ( 45 repr(self.text), self.pos)

46

47 -class TransactionError(Exception): pass

48

49 -class UnknownParamstyle(Exception):

50 """ 51 raised for unsupported db paramstyles 52 53 (currently supported: qmark, numeric, format, pyformat) 54 """ 55 pass 56

57 -class SQLParam:

58 """ 59 Parameter in SQLQuery. 60 61 >>> q = SQLQuery(["SELECT * FROM test WHERE name=", SQLParam("joe")]) 62 >>> q 63 <sql: "SELECT * FROM test WHERE name='joe'"> 64 >>> q.query() 65 'SELECT * FROM test WHERE name=%s' 66 >>> q.values() 67 ['joe'] 68 """

69 - def __init__(self, value):

70 self.value = value

71

72 - def get_marker(self, paramstyle='pyformat'):

73 if paramstyle == 'qmark': 74 return '?' 75 elif paramstyle == 'numeric': 76 return ':1' 77 elif paramstyle is None or paramstyle in ['format', 'pyformat']: 78 return '%s' 79 raise UnknownParamstyle, paramstyle

80

81 - def sqlquery(self):

82 return SQLQuery([self])

83

84 - def __add__(self, other):

85 return self.sqlquery() + other

86

87 - def __radd__(self, other):

88 return other + self.sqlquery()

89

90 - def __str__(self):

91 return str(self.value) 92

93 - def __repr__(self):

94 return '<param: %s>' % repr(self.value)

95 96 sqlparam = SQLParam 97

98 -class SQLQuery:

99 """ 100 You can pass this sort of thing as a clause in any db function. 101 Otherwise, you can pass a dictionary to the keyword argument `vars` 102 and the function will call reparam for you. 103 104 Internally, consists of `items`, which is a list of strings and 105 SQLParams, which get concatenated to produce the actual query. 106 """ 107 # tested in sqlquote's docstring

108 - def __init__(self, items=[]):

109 r"""Creates a new SQLQuery. 110 111 >>> SQLQuery("x") 112 <sql: 'x'> 113 >>> q = SQLQuery(['SELECT * FROM ', 'test', ' WHERE x=', SQLParam(1)]) 114 >>> q 115 <sql: 'SELECT * FROM test WHERE x=1'> 116 >>> q.query(), q.values() 117 ('SELECT * FROM test WHERE x=%s', [1]) 118 >>> SQLQuery(SQLParam(1)) 119 <sql: '1'> 120 """ 121 if isinstance(items, list): 122 self.items = items 123 elif isinstance(items, SQLParam): 124 self.items = [items] 125 elif isinstance(items, SQLQuery): 126 self.items = list(items.items) 127 else: 128 self.items = [items] 129 130 # Take care of SQLLiterals 131 for i, item in enumerate(self.items): 132 if isinstance(item, SQLParam) and isinstance(item.value, SQLLiteral): 133 self.items[i] = item.value.v

134

135 - def __add__(self, other):

136 if isinstance(other, basestring): 137 items = [other] 138 elif isinstance(other, SQLQuery): 139 items = other.items 140 else: 141 return NotImplemented 142 return SQLQuery(self.items + items)

143

144 - def __radd__(self, other):

145 if isinstance(other, basestring): 146 items = [other] 147 else: 148 return NotImplemented 149 150 return SQLQuery(items + self.items)

151

152 - def __iadd__(self, other):

153 if isinstance(other, basestring): 154 items = [other] 155 elif isinstance(other, SQLQuery): 156 items = other.items 157 else: 158 return NotImplemented 159 self.items.extend(items) 160 return self

161

162 - def __len__(self):

163 return len(self.query())

164

165 - def query(self, paramstyle=None):

166 """ 167 Returns the query part of the sql query. 168 >>> q = SQLQuery(["SELECT * FROM test WHERE name=", SQLParam('joe')]) 169 >>> q.query() 170 'SELECT * FROM test WHERE name=%s' 171 >>> q.query(paramstyle='qmark') 172 'SELECT * FROM test WHERE name=?' 173 """ 174 s = [] 175 for x in self.items: 176 if isinstance(x, SQLParam): 177 x = x.get_marker(paramstyle) 178 s.append(safestr(x)) 179 else: 180 x = safestr(x) 181 # automatically escape % characters in the query 182 # For backward compatability, ignore escaping when the query looks already escaped 183 if paramstyle in ['format', 'pyformat']: 184 if '%' in x and '%%' not in x: 185 x = x.replace('%', '%%') 186 s.append(x) 187 return "".join(s)

188

189 - def values(self):

190 """ 191 Returns the values of the parameters used in the sql query. 192 >>> q = SQLQuery(["SELECT * FROM test WHERE name=", SQLParam('joe')]) 193 >>> q.values() 194 ['joe'] 195 """ 196 return [i.value for i in self.items if isinstance(i, SQLParam)]

197

198 - def join(items, sep=' '):

199 """ 200 Joins multiple queries. 201 202 >>> SQLQuery.join(['a', 'b'], ', ') 203 <sql: 'a, b'> 204 """ 205 if len(items) == 0: 206 return SQLQuery("") 207 208 q = SQLQuery(items[0]) 209 for item in items[1:]: 210 q += sep 211 q += item 212 return q

213 214 join = staticmethod(join) 215

216 - def _str(self):

217 try: 218 return self.query() % tuple([sqlify(x) for x in self.values()]) 219 except (ValueError, TypeError): 220 return self.query()

221

222 - def __str__(self):

223 return safestr(self._str())

224

225 - def __unicode__(self):

226 return safeunicode(self._str())

227

228 - def __repr__(self):

229 return '<sql: %s>' % repr(str(self))

230

231 -class SQLLiteral:

232 """ 233 Protects a string from `sqlquote`. 234 235 >>> sqlquote('NOW()') 236 <sql: "'NOW()'"> 237 >>> sqlquote(SQLLiteral('NOW()')) 238 <sql: 'NOW()'> 239 """

240 - def __init__(self, v):

241 self.v = v 242

243 - def __repr__(self):

244 return self.v 245 246 sqlliteral = SQLLiteral 247

248 -def _sqllist(values):

249 """ 250 >>> _sqllist([1, 2, 3]) 251 <sql: '(1, 2, 3)'> 252 """ 253 items = [] 254 items.append('(') 255 for i, v in enumerate(values): 256 if i != 0: 257 items.append(', ') 258 items.append(sqlparam(v)) 259 items.append(')') 260 return SQLQuery(items)

261

262 -def reparam(string_, dictionary):

263 """ 264 Takes a string and a dictionary and interpolates the string 265 using values from the dictionary. Returns an `SQLQuery` for the result. 266 267 >>> reparam("s = $s", dict(s=True)) 268 <sql: "s = 't'"> 269 >>> reparam("s IN $s", dict(s=[1, 2])) 270 <sql: 's IN (1, 2)'> 271 """ 272 dictionary = dictionary.copy() # eval mucks with it 273 vals = [] 274 result = [] 275 for live, chunk in _interpolate(string_): 276 if live: 277 v = eval(chunk, dictionary) 278 result.append(sqlquote(v)) 279 else: 280 result.append(chunk) 281 return SQLQuery.join(result, '') 282

283 -def sqlify(obj):

284 """ 285 converts `obj` to its proper SQL version 286 287 >>> sqlify(None) 288 'NULL' 289 >>> sqlify(True) 290 "'t'" 291 >>> sqlify(3) 292 '3' 293 """ 294 # because `1 == True and hash(1) == hash(True)` 295 # we have to do this the hard way... 296 297 if obj is None: 298 return 'NULL' 299 elif obj is True: 300 return "'t'" 301 elif obj is False: 302 return "'f'" 303 elif datetime and isinstance(obj, datetime.datetime): 304 return repr(obj.isoformat()) 305 else: 306 if isinstance(obj, unicode): obj = obj.encode('utf8') 307 return repr(obj) 308

309 -def sqllist(lst):

310 """ 311 Converts the arguments for use in something like a WHERE clause. 312 313 >>> sqllist(['a', 'b']) 314 'a, b' 315 >>> sqllist('a') 316 'a' 317 >>> sqllist(u'abc') 318 u'abc' 319 """ 320 if isinstance(lst, basestring): 321 return lst 322 else: 323 return ', '.join(lst) 324

325 -def sqlors(left, lst):

326 """ 327 `left is a SQL clause like `tablename.arg = ` 328 and `lst` is a list of values. Returns a reparam-style 329 pair featuring the SQL that ORs together the clause 330 for each item in the lst. 331 332 >>> sqlors('foo = ', []) 333 <sql: '1=2'> 334 >>> sqlors('foo = ', [1]) 335 <sql: 'foo = 1'> 336 >>> sqlors('foo = ', 1) 337 <sql: 'foo = 1'> 338 >>> sqlors('foo = ', [1,2,3]) 339 <sql: '(foo = 1 OR foo = 2 OR foo = 3 OR 1=2)'> 340 """ 341 if isinstance(lst, iters): 342 lst = list(lst) 343 ln = len(lst) 344 if ln == 0: 345 return SQLQuery("1=2") 346 if ln == 1: 347 lst = lst[0] 348 349 if isinstance(lst, iters): 350 return SQLQuery(['('] + 351 sum([[left, sqlparam(x), ' OR '] for x in lst], []) + 352 ['1=2)'] 353 ) 354 else: 355 return left + sqlparam(lst)

356

357 -def sqlwhere(dictionary, grouping=' AND '):

358 """ 359 Converts a `dictionary` to an SQL WHERE clause `SQLQuery`. 360 361 >>> sqlwhere({'cust_id': 2, 'order_id':3}) 362 <sql: 'order_id = 3 AND cust_id = 2'> 363 >>> sqlwhere({'cust_id': 2, 'order_id':3}, grouping=', ') 364 <sql: 'order_id = 3, cust_id = 2'> 365 >>> sqlwhere({'a': 'a', 'b': 'b'}).query() 366 'a = %s AND b = %s' 367 """ 368 return SQLQuery.join([k + ' = ' + sqlparam(v) for k, v in dictionary.items()], grouping) 369

370 -def sqlquote(a):

371 """ 372 Ensures `a` is quoted properly for use in a SQL query. 373 374 >>> 'WHERE x = ' + sqlquote(True) + ' AND y = ' + sqlquote(3) 375 <sql: "WHERE x = 't' AND y = 3"> 376 >>> 'WHERE x = ' + sqlquote(True) + ' AND y IN ' + sqlquote([2, 3]) 377 <sql: "WHERE x = 't' AND y IN (2, 3)"> 378 """ 379 if isinstance(a, list): 380 return _sqllist(a) 381 else: 382 return sqlparam(a).sqlquery() 383

384 -class Transaction:

385 """Database transaction."""

386 - def __init__(self, ctx):

387 self.ctx = ctx 388 self.transaction_count = transaction_count = len(ctx.transactions) 389 390 class transaction_engine: 391 """Transaction Engine used in top level transactions.""" 392 def do_transact(self): 393 ctx.commit(unload=False)

394 395 def do_commit(self): 396 ctx.commit()

397 398 def do_rollback(self): 399 ctx.rollback() 400 401 class subtransaction_engine: 402 """Transaction Engine used in sub transactions.""" 403 def query(self, q): 404 db_cursor = ctx.db.cursor() 405 ctx.db_execute(db_cursor, SQLQuery(q % transaction_count)) 406 407 def do_transact(self): 408 self.query('SAVEPOINT webpy_sp_%s') 409 410 def do_commit(self): 411 self.query('RELEASE SAVEPOINT webpy_sp_%s') 412 413 def do_rollback(self): 414 self.query('ROLLBACK TO SAVEPOINT webpy_sp_%s') 415 416 class dummy_engine: 417 """Transaction Engine used instead of subtransaction_engine 418 when sub transactions are not supported.""" 419 do_transact = do_commit = do_rollback = lambda self: None 420 421 if self.transaction_count: 422 # nested transactions are not supported in some databases 423 if self.ctx.get('ignore_nested_transactions'): 424 self.engine = dummy_engine() 425 else: 426 self.engine = subtransaction_engine() 427 else: 428 self.engine = transaction_engine() 429 430 self.engine.do_transact() 431 self.ctx.transactions.append(self) 432

433 - def __enter__(self):

434 return self

435

436 - def __exit__(self, exctype, excvalue, traceback):

437 if exctype is not None: 438 self.rollback() 439 else: 440 self.commit()

441

442 - def commit(self):

443 if len(self.ctx.transactions) > self.transaction_count: 444 self.engine.do_commit() 445 self.ctx.transactions = self.ctx.transactions[:self.transaction_count]

446

447 - def rollback(self):

448 if len(self.ctx.transactions) > self.transaction_count: 449 self.engine.do_rollback() 450 self.ctx.transactions = self.ctx.transactions[:self.transaction_count]

451

452 -class DB:

453 """Database"""

454 - def __init__(self, db_module, keywords):

455 """Creates a database. 456 """ 457 # some DB implementaions take optional paramater `driver` to use a specific driver modue 458 # but it should not be passed to connect 459 keywords.pop('driver', None) 460 461 self.db_module = db_module 462 self.keywords = keywords 463 464 465 self._ctx = threadeddict() 466 # flag to enable/disable printing queries 467 self.printing = config.get('debug', False) 468 self.supports_multiple_insert = False 469 470 try: 471 import DBUtils 472 # enable pooling if DBUtils module is available. 473 self.has_pooling = True 474 except ImportError: 475 self.has_pooling = False 476 477 # Pooling can be disabled by passing pooling=False in the keywords. 478 self.has_pooling = self.keywords.pop('pooling', True) and self.has_pooling

479

480 - def _getctx(self):

481 if not self._ctx.get('db'): 482 self._load_context(self._ctx) 483 return self._ctx 484 ctx = property(_getctx) 485

486 - def _load_context(self, ctx):

487 ctx.dbq_count = 0 488 ctx.transactions = [] # stack of transactions 489 490 if self.has_pooling: 491 ctx.db = self._connect_with_pooling(self.keywords) 492 else: 493 ctx.db = self._connect(self.keywords) 494 ctx.db_execute = self._db_execute 495 496 if not hasattr(ctx.db, 'commit'): 497 ctx.db.commit = lambda: None 498 499 if not hasattr(ctx.db, 'rollback'): 500 ctx.db.rollback = lambda: None 501 502 def commit(unload=True): 503 # do db commit and release the connection if pooling is enabled. 504 ctx.db.commit() 505 if unload and self.has_pooling: 506 self._unload_context(self._ctx)

507 508 def rollback(): 509 # do db rollback and release the connection if pooling is enabled. 510 ctx.db.rollback() 511 if self.has_pooling: 512 self._unload_context(self._ctx) 513 514 ctx.commit = commit 515 ctx.rollback = rollback 516

517 - def _unload_context(self, ctx):

518 del ctx.db

519

520 - def _connect(self, keywords):

521 return self.db_module.connect(**keywords)

522

523 - def _connect_with_pooling(self, keywords):

524 def get_pooled_db(): 525 from DBUtils import PooledDB 526 527 # In DBUtils 0.9.3, `dbapi` argument is renamed as `creator` 528 # see Bug#122112 529 530 if PooledDB.__version__.split('.') < '0.9.3'.split('.'): 531 return PooledDB.PooledDB(dbapi=self.db_module, **keywords) 532 else: 533 return PooledDB.PooledDB(creator=self.db_module, **keywords)

534 535 if getattr(self, '_pooleddb', None) is None: 536 self._pooleddb = get_pooled_db() 537 538 return self._pooleddb.connection() 539

540 - def _db_cursor(self):

541 return self.ctx.db.cursor()

542

543 - def _param_marker(self):

544 """Returns parameter marker based on paramstyle attribute if this database.""" 545 style = getattr(self, 'paramstyle', 'pyformat') 546 547 if style == 'qmark': 548 return '?' 549 elif style == 'numeric': 550 return ':1' 551 elif style in ['format', 'pyformat']: 552 return '%s' 553 raise UnknownParamstyle, style

554

555 - def _db_execute(self, cur, sql_query):

556 """executes an sql query""" 557 self.ctx.dbq_count += 1 558 559 try: 560 a = time.time() 561 paramstyle = getattr(self, 'paramstyle', 'pyformat') 562 out = cur.execute(sql_query.query(paramstyle), sql_query.values()) 563 b = time.time() 564 except: 565 if self.printing: 566 print >> debug, 'ERR:', str(sql_query) 567 if self.ctx.transactions: 568 self.ctx.transactions[-1].rollback() 569 else: 570 self.ctx.rollback() 571 raise 572 573 if self.printing: 574 print >> debug, '%s (%s): %s' % (round(b-a, 2), self.ctx.dbq_count, str(sql_query)) 575 return out 576

577 - def _where(self, where, vars):

578 if isinstance(where, (int, long)): 579 where = "id = " + sqlparam(where) 580 #@@@ for backward-compatibility 581 elif isinstance(where, (list, tuple)) and len(where) == 2: 582 where = SQLQuery(where[0], where[1]) 583 elif isinstance(where, SQLQuery): 584 pass 585 else: 586 where = reparam(where, vars) 587 return where 588

589 - def query(self, sql_query, vars=None, processed=False, _test=False):

590 """ 591 Execute SQL query `sql_query` using dictionary `vars` to interpolate it. 592 If `processed=True`, `vars` is a `reparam`-style list to use 593 instead of interpolating. 594 595 >>> db = DB(None, {}) 596 >>> db.query("SELECT * FROM foo", _test=True) 597 <sql: 'SELECT * FROM foo'> 598 >>> db.query("SELECT * FROM foo WHERE x = $x", vars=dict(x='f'), _test=True) 599 <sql: "SELECT * FROM foo WHERE x = 'f'"> 600 >>> db.query("SELECT * FROM foo WHERE x = " + sqlquote('f'), _test=True) 601 <sql: "SELECT * FROM foo WHERE x = 'f'"> 602 """ 603 if vars is None: vars = {} 604 605 if not processed and not isinstance(sql_query, SQLQuery): 606 sql_query = reparam(sql_query, vars) 607 608 if _test: return sql_query 609 610 db_cursor = self._db_cursor() 611 self._db_execute(db_cursor, sql_query) 612 613 if db_cursor.description: 614 names = [x[0] for x in db_cursor.description] 615 def iterwrapper(): 616 row = db_cursor.fetchone() 617 while row: 618 yield storage(dict(zip(names, row))) 619 row = db_cursor.fetchone() 620 out = iterbetter(iterwrapper()) 621 out.__len__ = lambda: int(db_cursor.rowcount) 622 out.list = lambda: [storage(dict(zip(names, x))) \ 623 for x in db_cursor.fetchall()] 624 else: 625 out = db_cursor.rowcount 626 627 if not self.ctx.transactions: 628 self.ctx.commit() 629 return out 630

631 - def select(self, tables, vars=None, what='*', where=None, order=None, group=None, 632 limit=None, offset=None, _test=False):

633 """ 634 Selects `what` from `tables` with clauses `where`, `order`, 635 `group`, `limit`, and `offset`. Uses vars to interpolate. 636 Otherwise, each clause can be a SQLQuery. 637 638 >>> db = DB(None, {}) 639 >>> db.select('foo', _test=True) 640 <sql: 'SELECT * FROM foo'> 641 >>> db.select(['foo', 'bar'], where="foo.bar_id = bar.id", limit=5, _test=True) 642 <sql: 'SELECT * FROM foo, bar WHERE foo.bar_id = bar.id LIMIT 5'> 643 """ 644 if vars is None: vars = {} 645 sql_clauses = self.sql_clauses(what, tables, where, group, order, limit, offset) 646 clauses = [self.gen_clause(sql, val, vars) for sql, val in sql_clauses if val is not None] 647 qout = SQLQuery.join(clauses) 648 if _test: return qout 649 return self.query(qout, processed=True) 650

651 - def where(self, table, what='*', order=None, group=None, limit=None, 652 offset=None, _test=False, **kwargs):

653 """ 654 Selects from `table` where keys are equal to values in `kwargs`. 655 656 >>> db = DB(None, {}) 657 >>> db.where('foo', bar_id=3, _test=True) 658 <sql: 'SELECT * FROM foo WHERE bar_id = 3'> 659 >>> db.where('foo', source=2, crust='dewey', _test=True) 660 <sql: "SELECT * FROM foo WHERE source = 2 AND crust = 'dewey'"> 661 """ 662 where = [] 663 for k, v in kwargs.iteritems(): 664 where.append(k + ' = ' + sqlquote(v)) 665 return self.select(table, what=what, order=order, 666 group=group, limit=limit, offset=offset, _test=_test, 667 where=SQLQuery.join(where, ' AND '))

668

669 - def sql_clauses(self, what, tables, where, group, order, limit, offset):

670 return ( 671 ('SELECT', what), 672 ('FROM', sqllist(tables)), 673 ('WHERE', where), 674 ('GROUP BY', group), 675 ('ORDER BY', order), 676 ('LIMIT', limit), 677 ('OFFSET', offset)) 678

679 - def gen_clause(self, sql, val, vars):

680 if isinstance(val, (int, long)): 681 if sql == 'WHERE': 682 nout = 'id = ' + sqlquote(val) 683 else: 684 nout = SQLQuery(val) 685 #@@@ 686 elif isinstance(val, (list, tuple)) and len(val) == 2: 687 nout = SQLQuery(val[0], val[1]) # backwards-compatibility 688 elif isinstance(val, SQLQuery): 689 nout = val 690 else: 691 nout = reparam(val, vars) 692 693 def xjoin(a, b): 694 if a and b: return a + ' ' + b 695 else: return a or b 696 697 return xjoin(sql, nout) 698

699 - def insert(self, tablename, seqname=None, _test=False, **values):

700 """ 701 Inserts `values` into `tablename`. Returns current sequence ID. 702 Set `seqname` to the ID if it's not the default, or to `False` 703 if there isn't one. 704 705 >>> db = DB(None, {}) 706 >>> q = db.insert('foo', name='bob', age=2, created=SQLLiteral('NOW()'), _test=True) 707 >>> q 708 <sql: "INSERT INTO foo (age, name, created) VALUES (2, 'bob', NOW())"> 709 >>> q.query() 710 'INSERT INTO foo (age, name, created) VALUES (%s, %s, NOW())' 711 >>> q.values() 712 [2, 'bob'] 713 """ 714 def q(x): return "(" + x + ")" 715 716 if values: 717 _keys = SQLQuery.join(values.keys(), ', ') 718 _values = SQLQuery.join([sqlparam(v) for v in values.values()], ', ') 719 sql_query = "INSERT INTO %s " % tablename + q(_keys) + ' VALUES ' + q(_values) 720 else: 721 sql_query = SQLQuery("INSERT INTO %s DEFAULT VALUES" % tablename) 722 723 if _test: return sql_query 724 725 db_cursor = self._db_cursor() 726 if seqname is not False: 727 sql_query = self._process_insert_query(sql_query, tablename, seqname) 728 729 if isinstance(sql_query, tuple): 730 # for some databases, a separate query has to be made to find 731 # the id of the inserted row. 732 q1, q2 = sql_query 733 self._db_execute(db_cursor, q1) 734 self._db_execute(db_cursor, q2) 735 else: 736 self._db_execute(db_cursor, sql_query) 737 738 try: 739 out = db_cursor.fetchone()[0] 740 except Exception: 741 out = None 742 743 if not self.ctx.transactions: 744 self.ctx.commit() 745 return out 746

747 - def multiple_insert(self, tablename, values, seqname=None, _test=False):

748 """ 749 Inserts multiple rows into `tablename`. The `values` must be a list of dictioanries, 750 one for each row to be inserted, each with the same set of keys. 751 Returns the list of ids of the inserted rows. 752 Set `seqname` to the ID if it's not the default, or to `False` 753 if there isn't one. 754 755 >>> db = DB(None, {}) 756 >>> db.supports_multiple_insert = True 757 >>> values = [{"name": "foo", "email": "foo@example.com"}, {"name": "bar", "email": "bar@example.com"}] 758 >>> db.multiple_insert('person', values=values, _test=True) 759 <sql: "INSERT INTO person (name, email) VALUES ('foo', 'foo@example.com'), ('bar', 'bar@example.com')"> 760 """ 761 if not values: 762 return [] 763 764 if not self.supports_multiple_insert: 765 out = [self.insert(tablename, seqname=seqname, _test=_test, **v) for v in values] 766 if seqname is False: 767 return None 768 else: 769 return out 770 771 keys = values[0].keys() 772 #@@ make sure all keys are valid 773 774 # make sure all rows have same keys. 775 for v in values: 776 if v.keys() != keys: 777 raise ValueError, 'Bad data' 778 779 sql_query = SQLQuery('INSERT INTO %s (%s) VALUES ' % (tablename, ', '.join(keys))) 780 781 data = [] 782 for row in values: 783 d = SQLQuery.join([SQLParam(row[k]) for k in keys], ', ') 784 data.append('(' + d + ')') 785 sql_query += SQLQuery.join(data, ', ') 786 787 if _test: return sql_query 788 789 db_cursor = self._db_cursor() 790 if seqname is not False: 791 sql_query = self._process_insert_query(sql_query, tablename, seqname) 792 793 if isinstance(sql_query, tuple): 794 # for some databases, a separate query has to be made to find 795 # the id of the inserted row. 796 q1, q2 = sql_query 797 self._db_execute(db_cursor, q1) 798 self._db_execute(db_cursor, q2) 799 else: 800 self._db_execute(db_cursor, sql_query) 801 802 try: 803 out = db_cursor.fetchone()[0] 804 out = range(out-len(values)+1, out+1) 805 except Exception: 806 out = None 807 808 if not self.ctx.transactions: 809 self.ctx.commit() 810 return out

811 812

813 - def update(self, tables, where, vars=None, _test=False, **values):

814 """ 815 Update `tables` with clause `where` (interpolated using `vars`) 816 and setting `values`. 817 818 >>> db = DB(None, {}) 819 >>> name = 'Joseph' 820 >>> q = db.update('foo', where='name = $name', name='bob', age=2, 821 ... created=SQLLiteral('NOW()'), vars=locals(), _test=True) 822 >>> q 823 <sql: "UPDATE foo SET age = 2, name = 'bob', created = NOW() WHERE name = 'Joseph'"> 824 >>> q.query() 825 'UPDATE foo SET age = %s, name = %s, created = NOW() WHERE name = %s' 826 >>> q.values() 827 [2, 'bob', 'Joseph'] 828 """ 829 if vars is None: vars = {} 830 where = self._where(where, vars) 831 832 query = ( 833 "UPDATE " + sqllist(tables) + 834 " SET " + sqlwhere(values, ', ') + 835 " WHERE " + where) 836 837 if _test: return query 838 839 db_cursor = self._db_cursor() 840 self._db_execute(db_cursor, query) 841 if not self.ctx.transactions: 842 self.ctx.commit() 843 return db_cursor.rowcount 844

845 - def delete(self, table, where, using=None, vars=None, _test=False):

846 """ 847 Deletes from `table` with clauses `where` and `using`. 848 849 >>> db = DB(None, {}) 850 >>> name = 'Joe' 851 >>> db.delete('foo', where='name = $name', vars=locals(), _test=True) 852 <sql: "DELETE FROM foo WHERE name = 'Joe'"> 853 """ 854 if vars is None: vars = {} 855 where = self._where(where, vars) 856 857 q = 'DELETE FROM ' + table 858 if where: q += ' WHERE ' + where 859 if using: q += ' USING ' + sqllist(using) 860 861 if _test: return q 862 863 db_cursor = self._db_cursor() 864 self._db_execute(db_cursor, q) 865 if not self.ctx.transactions: 866 self.ctx.commit() 867 return db_cursor.rowcount 868

869 - def _process_insert_query(self, query, tablename, seqname):

870 return query

871

872 - def transaction(self):

873 """Start a transaction.""" 874 return Transaction(self.ctx) 875

876 -class PostgresDB(DB):

877 """Postgres driver."""

878 - def __init__(self, **keywords):

879 if 'pw' in keywords: 880 keywords['password'] = keywords.pop('pw') 881 882 db_module = import_driver(["psycopg2", "psycopg", "pgdb"], preferred=keywords.pop('driver', None)) 883 if db_module.__name__ == "psycopg2": 884 import psycopg2.extensions 885 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE) 886 887 # if db is not provided postgres driver will take it from PGDATABASE environment variable 888 if 'db' in keywords: 889 keywords['database'] = keywords.pop('db') 890 891 self.dbname = "postgres" 892 self.paramstyle = db_module.paramstyle 893 DB.__init__(self, db_module, keywords) 894 self.supports_multiple_insert = True 895 self._sequences = None

896

897 - def _process_insert_query(self, query, tablename, seqname):

898 if seqname is None: 899 # when seqname is not provided guess the seqname and make sure it exists 900 seqname = tablename + "_id_seq" 901 if seqname not in self._get_all_sequences(): 902 seqname = None 903 904 if seqname: 905 query += "; SELECT currval('%s')" % seqname 906 907 return query

908

909 - def _get_all_sequences(self):

910 """Query postgres to find names of all sequences used in this database.""" 911 if self._sequences is None: 912 q = "SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'" 913 self._sequences = set([c.relname for c in self.query(q)]) 914 return self._sequences

915

916 - def _connect(self, keywords):

917 conn = DB._connect(self, keywords) 918 conn.set_client_encoding('UTF8') 919 return conn

920

921 - def _connect_with_pooling(self, keywords):

922 conn = DB._connect_with_pooling(self, keywords) 923 conn._con._con.set_client_encoding('UTF8') 924 return conn

925

926 -class MySQLDB(DB):

927 - def __init__(self, **keywords):

928 import MySQLdb as db 929 if 'pw' in keywords: 930 keywords['passwd'] = keywords['pw'] 931 del keywords['pw'] 932 933 if 'charset' not in keywords: 934 keywords['charset'] = 'utf8' 935 elif keywords['charset'] is None: 936 del keywords['charset'] 937 938 self.paramstyle = db.paramstyle = 'pyformat' # it's both, like psycopg 939 self.dbname = "mysql" 940 DB.__init__(self, db, keywords) 941 self.supports_multiple_insert = True

942

943 - def _process_insert_query(self, query, tablename, seqname):

944 return query, SQLQuery('SELECT last_insert_id();')

945

946 -def import_driver(drivers, preferred=None):

947 """Import the first available driver or preferred driver. 948 """ 949 if preferred: 950 drivers = [preferred] 951 952 for d in drivers: 953 try: 954 return __import__(d, None, None, ['x']) 955 except ImportError: 956 pass 957 raise ImportError("Unable to import " + " or ".join(drivers))

958

959 -class SqliteDB(DB):

960 - def __init__(self, **keywords):

961 db = import_driver(["sqlite3", "pysqlite2.dbapi2", "sqlite"], preferred=keywords.pop('driver', None)) 962 963 if db.__name__ in ["sqlite3", "pysqlite2.dbapi2"]: 964 db.paramstyle = 'qmark' 965 966 self.paramstyle = db.paramstyle 967 keywords['database'] = keywords.pop('db') 968 self.dbname = "sqlite" 969 DB.__init__(self, db, keywords)

970

971 - def _process_insert_query(self, query, tablename, seqname):

972 return query, SQLQuery('SELECT last_insert_rowid();')

973

974 - def query(self, *a, **kw):

975 out = DB.query(self, *a, **kw) 976 if isinstance(out, iterbetter): 977 del out.__len__ 978 return out

979

980 -class FirebirdDB(DB):

981 """Firebird Database. 982 """

983 - def __init__(self, **keywords):

984 try: 985 import kinterbasdb as db 986 except Exception: 987 db = None 988 pass 989 if 'pw' in keywords: 990 keywords['passwd'] = keywords['pw'] 991 del keywords['pw'] 992 keywords['database'] = keywords['db'] 993 del keywords['db'] 994 DB.__init__(self, db, keywords)

995

996 - def delete(self, table, where=None, using=None, vars=None, _test=False):

997 # firebird doesn't support using clause 998 using=None 999 return DB.delete(self, table, where, using, vars, _test)

1000

1001 - def sql_clauses(self, what, tables, where, group, order, limit, offset):

1002 return ( 1003 ('SELECT', ''), 1004 ('FIRST', limit), 1005 ('SKIP', offset), 1006 ('', what), 1007 ('FROM', sqllist(tables)), 1008 ('WHERE', where), 1009 ('GROUP BY', group), 1010 ('ORDER BY', order) 1011 )

1012

1013 -class MSSQLDB(DB):

1014 - def __init__(self, **keywords):

1015 import pymssql as db 1016 if 'pw' in keywords: 1017 keywords['password'] = keywords.pop('pw') 1018 keywords['database'] = keywords.pop('db') 1019 self.dbname = "mssql" 1020 DB.__init__(self, db, keywords)

1021

1022 - def sql_clauses(self, what, tables, where, group, order, limit, offset):

1023 return ( 1024 ('SELECT', what), 1025 ('TOP', limit), 1026 ('FROM', sqllist(tables)), 1027 ('WHERE', where), 1028 ('GROUP BY', group), 1029 ('ORDER BY', order), 1030 ('OFFSET', offset))

1031

1032 - def _test(self):

1033 """Test LIMIT. 1034 1035 Fake presence of pymssql module for running tests. 1036 >>> import sys 1037 >>> sys.modules['pymssql'] = sys.modules['sys'] 1038 1039 MSSQL has TOP clause instead of LIMIT clause. 1040 >>> db = MSSQLDB(db='test', user='joe', pw='secret') 1041 >>> db.select('foo', limit=4, _test=True) 1042 <sql: 'SELECT * TOP 4 FROM foo'> 1043 """ 1044 pass

1045

1046 -class OracleDB(DB):

1047 - def __init__(self, **keywords):

1048 import cx_Oracle as db 1049 if 'pw' in keywords: 1050 keywords['password'] = keywords.pop('pw') 1051 1052 #@@ TODO: use db.makedsn if host, port is specified 1053 keywords['dsn'] = keywords.pop('db') 1054 self.dbname = 'oracle' 1055 db.paramstyle = 'numeric' 1056 self.paramstyle = db.paramstyle 1057 1058 # oracle doesn't support pooling 1059 keywords.pop('pooling', None) 1060 DB.__init__(self, db, keywords) 1061

1062 - def _process_insert_query(self, query, tablename, seqname):

1063 if seqname is None: 1064 # It is not possible to get seq name from table name in Oracle 1065 return query 1066 else: 1067 return query + "; SELECT %s.currval FROM dual" % seqname 1068 1069 _databases = {}

1070 -def database(dburl=None, **params):

1071 """Creates appropriate database using params. 1072 1073 Pooling will be enabled if DBUtils module is available. 1074 Pooling can be disabled by passing pooling=False in params. 1075 """ 1076 dbn = params.pop('dbn') 1077 if dbn in _databases: 1078 return _databases[dbn](**params) 1079 else: 1080 raise UnknownDB, dbn

1081

1082 -def register_database(name, clazz):

1083 """ 1084 Register a database. 1085 1086 >>> class LegacyDB(DB): 1087 ... def __init__(self, **params): 1088 ... pass 1089 ... 1090 >>> register_database('legacy', LegacyDB) 1091 >>> db = database(dbn='legacy', db='test', user='joe', passwd='secret') 1092 """ 1093 _databases[name] = clazz

1094 1095 register_database('mysql', MySQLDB) 1096 register_database('postgres', PostgresDB) 1097 register_database('sqlite', SqliteDB) 1098 register_database('firebird', FirebirdDB) 1099 register_database('mssql', MSSQLDB) 1100 register_database('oracle', OracleDB) 1101

1102 -def _interpolate(format):

1103 """ 1104 Takes a format string and returns a list of 2-tuples of the form 1105 (boolean, string) where boolean says whether string should be evaled 1106 or not. 1107 1108 from <http://lfw.org/python/Itpl.py> (public domain, Ka-Ping Yee) 1109 """ 1110 from tokenize import tokenprog 1111 1112 def matchorfail(text, pos): 1113 match = tokenprog.match(text, pos) 1114 if match is None: 1115 raise _ItplError(text, pos) 1116 return match, match.end() 1117 1118 namechars = "abcdefghijklmnopqrstuvwxyz" \ 1119 "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_"; 1120 chunks = [] 1121 pos = 0 1122 1123 while 1: 1124 dollar = format.find("$", pos) 1125 if dollar < 0: 1126 break 1127 nextchar = format[dollar + 1] 1128 1129 if nextchar == "{": 1130 chunks.append((0, format[pos:dollar])) 1131 pos, level = dollar + 2, 1 1132 while level: 1133 match, pos = matchorfail(format, pos) 1134 tstart, tend = match.regs[3] 1135 token = format[tstart:tend] 1136 if token == "{": 1137 level = level + 1 1138 elif token == "}": 1139 level = level - 1 1140 chunks.append((1, format[dollar + 2:pos - 1])) 1141 1142 elif nextchar in namechars: 1143 chunks.append((0, format[pos:dollar])) 1144 match, pos = matchorfail(format, dollar + 1) 1145 while pos < len(format): 1146 if format[pos] == "." and \ 1147 pos + 1 < len(format) and format[pos + 1] in namechars: 1148 match, pos = matchorfail(format, pos + 1) 1149 elif format[pos] in "([": 1150 pos, level = pos + 1, 1 1151 while level: 1152 match, pos = matchorfail(format, pos) 1153 tstart, tend = match.regs[3] 1154 token = format[tstart:tend] 1155 if token[0] in "([": 1156 level = level + 1 1157 elif token[0] in ")]": 1158 level = level - 1 1159 else: 1160 break 1161 chunks.append((1, format[dollar + 1:pos])) 1162 else: 1163 chunks.append((0, format[pos:dollar + 1])) 1164 pos = dollar + 1 + (nextchar == "$") 1165 1166 if pos < len(format): 1167 chunks.append((0, format[pos:])) 1168 return chunks 1169 1170 if __name__ == "__main__": 1171 import doctest 1172 doctest.testmod() 1173

Source Code for Module web.web.db