Package csb :: Package io :: Module tsv
[frames] | no frames]

Source Code for Module csb.io.tsv

   1  """ 
   2  Read, query and update textual tables via flexible SQL interface. 
   3   
   4  L{Table}s can be created and populated with data from scratch, built from TSV 
   5  files, 2D lists or other tables. Once the data is loaded in memory, each 
   6  storage operation on the table object is delegated via bridge to an SQL 
   7  storage backend (by default this is SQLite). However the table uses the backend 
   8  only as a temp storage to ensure maximum portability of the data. Tables can be 
   9  stored persistently as text (TSV) files and then loaded back in memory when 
  10  needed. 
  11   
  12  These Tables can be queried and updated in a vast number of ways; each query 
  13  returns a new L{Table}:   
  14   
  15      1. Using slice expressions. The general form of a slice expression is 
  16      C{[rows, columns]}, where C{rows} can be: 
  17       
  18       
  19          - a row index, 0-based, e.g. C{5} 
  20          - a tuple of row indices, e.g. C{(1, 3, 6)} 
  21          - a standard Python slice, e.g. C{1:3} or C{:5} or C{:} 
  22          - omitted (means: all rows) 
  23           
  24      and C{columns} can be: 
  25       
  26          - a column index, 0-based, e.g. C{5}     
  27          - a tuple of columns indices, 0-based 
  28          - a column name, e.g. C{'TmScore'}         
  29          - a tuple of column names, e.g. C{('ID', 'TmScore')} 
  30          - a standard Python slice using column indices 
  31          - a slice using column names, e.g. C{'ID':'TM'} or C{:'TM'} or C{:} 
  32          - omitted (means: all columns) 
  33       
  34      2. Using query expressions, for example: 
  35       
  36       
  37      >>> table.where('ID').between(1, 5).select('TmScore', 'RMSD') 
  38      Table ('TmScore', 'RMSD') 
  39       
  40      >>> table.where('ID').between(1, 5).update('RMSD', 0.2) 
  41      Table (the same table) 
  42           
  43      3. With SQL queries: 
  44       
  45       
  46      >>> t.query(r'''SELECT  ColumnB * ColumnA AS ComputedValue 
  47                      FROM    {0.name} 
  48                      WHERE   ColumnC IN ({1}, {1})'''.format(t, Predicate.PH), 
  49                  [12, 55]) 
  50      iterable 
  51       
  52  The data contained in a Table can be extracted in several ways: 
  53   
  54      - if you need a single (scalar) value -- with the C{table[row, column]} 
  55      indexing expression or with the dedicated C{table.scalar(row, column)} method. 
  56      - by treating the table as an iterator; each cycle will then yield a L{DataRow} 
  57      object 
  58      - with text (TSV) serialization: simply call C{table.dump(file)}. 
  59       
  60  See L{Table} for full API details. 
  61  """ 
  62   
  63  try: 
  64      import __builtin__ as builtins 
  65  except ImportError: 
  66      import builtins 
  67   
  68  import os 
  69   
  70  import csb.io 
  71  import csb.core 
  72   
  73  from abc import ABCMeta, abstractmethod, abstractproperty 
74 75 76 -class RepositoryImp(object):
77 """ 78 Abstract SQL backend interface. Defines a number of platform-specific 79 operations, that each concrete backend implementor must provide. 80 """ 81 82 __metaclass__ = ABCMeta 83
84 - def __init__(self, tablename):
85 self._table = tablename
86 87 @abstractproperty
88 - def pk(self):
89 pass
90 91 @property
92 - def table(self):
93 """ 94 Table name 95 @rtype: str 96 """ 97 return self._table
98
99 - def query(self, sql, params=None):
100 """ 101 Execute a native SQL query against the backend, as-is. 102 103 @param sql: SQL query 104 @type sql: str 105 @param params: query bound parameters, if any 106 @type params: tuple 107 108 @return: data reader (2D iterable) 109 """ 110 raise NotImplementedError()
111 112 @abstractmethod
113 - def count(self):
114 """ 115 Count the number of rows in the table. 116 117 @rtype: int 118 """ 119 pass
120 121 @abstractmethod
122 - def execute(self, expression):
123 """ 124 Perform a select operation given L{expression}. 125 126 @type expression: L{Expression} 127 @return: data reader (2D iterable) 128 """ 129 pass
130 131 @abstractmethod
132 - def update(self, expression):
133 """ 134 Perform an update operation given L{expression}. 135 136 @type expression: L{Expression} 137 @return: void 138 """ 139 pass
140 141 @abstractmethod
142 - def insert(self, row):
143 """ 144 Insert a new tuple in the table. 145 146 @type row: tuple 147 @return: void 148 """ 149 pass
150 151 @abstractmethod
152 - def create(self, metadata):
153 """ 154 Create a table given L{metadata}. 155 156 @type metadata: tuple of L{ColumnInfo} 157 @return: void 158 """ 159 pass
160 161 @abstractmethod
162 - def close(self):
163 """ 164 Perform cleanup (e.g. close connections). 165 """ 166 pass
167
168 -class InvalidColumnError(KeyError):
169 pass
170
171 -class UnsupportedTypeError(ValueError):
172 pass
173
174 -class SQLiteRepository(RepositoryImp):
175 """ 176 SQLite-based concrete repository implementor. 177 This is the default L{Table} backend. 178 """ 179 180 PK = 'ROWID' 181 TYPES = { int: 'BIGINT', float: 'REAL', str: 'VARCHAR' } 182
183 - class ChunkedReader(object):
184 185 SIZE = 10000 186
187 - def __init__(self, cursor):
188 self._cursor = cursor
189
190 - def __iter__(self):
191 try: 192 while True: 193 rows = self._cursor.fetchmany(self.SIZE) 194 if not rows: 195 break 196 else: 197 for row in rows: 198 yield row 199 finally: 200 self._cursor.close()
201
202 - def __init__(self, tablename):
203 import sqlite3 204 205 self._conn = sqlite3.connect(':memory:') 206 self._pk = SQLiteRepository.PK 207 208 super(SQLiteRepository, self).__init__(tablename)
209 210 @property
211 - def pk(self):
212 return self._pk
213
214 - def _cursor(self, sql, params=None):
215 216 sql = sql.replace(Predicate.PH, '?') 217 if not params: 218 params = [] 219 220 return self._conn.execute(sql, params)
221
222 - def query(self, sql, params=None):
223 224 return self._cursor(sql, params).fetchall()
225
226 - def count(self):
227 228 query = 'SELECT COUNT(*)\nFROM {0}\n'.format(self.table) 229 return self._cursor(query).fetchone()[0]
230
231 - def execute(self, exp):
232 233 query = 'SELECT {0}\nFROM {1}\n'.format(', '.join(exp.select), self.table) 234 235 if exp.where: 236 predicate = str(exp.predicate).replace(Predicate.PH, '?') 237 query += 'WHERE {0} {1}\n'.format(exp.where, predicate) 238 239 query += 'ORDER BY {0} ASC\n'.format(self.pk) 240 241 cursor = self._cursor(query, exp.params) 242 return SQLiteRepository.ChunkedReader(cursor)
243
244 - def update(self, exp):
245 246 params = [exp.data] 247 query = 'UPDATE {0}\n SET {1} = ?\n'.format(self.table, exp.select[0]) 248 249 if exp.where: 250 predicate = str(exp.predicate).replace(Predicate.PH, '?') 251 query += 'WHERE {0} {1}\n'.format(exp.where, predicate) 252 if exp.params: 253 params.extend(list(exp.params)) 254 255 return self.query(query, params)
256
257 - def insert(self, row):
258 259 row = list(row) 260 params = ','.join(['?' for dummy in row]) 261 query = 'INSERT INTO {0} VALUES({1})'.format(self.table, params) 262 self.query(query, row)
263
264 - def create(self, metadata):
265 266 cols = [] 267 268 for ci in metadata: 269 type = self._gettype(ci.type) 270 cols.append('{0} {1}'.format(ci.name, type)) 271 272 statement = 'CREATE TABLE {0} (\n {1}\n);'.format(self.table, ',\n '.join(cols)) 273 274 self._conn.execute(statement)
275
276 - def _gettype(self, type):
277 try: 278 return SQLiteRepository.TYPES[type] 279 except KeyError: 280 raise UnsupportedTypeError(type)
281
282 - def close(self):
283 try: 284 return self._conn.close() 285 except: 286 pass
287
288 -class ColumnInfo(object):
289 """ 290 Holder object for column metadata. 291 292 @param name: column name 293 @type name: str 294 @param type: column data type (Python) 295 @type type: type 296 """ 297
298 - def __init__(self, name, type):
299 self._name = name 300 self._type = type
301 302 @property
303 - def name(self):
304 """ 305 Colulmn name 306 @rtype: str 307 """ 308 return self._name
309 310 @property
311 - def type(self):
312 """ 313 Python data type 314 @rtype: class 315 """ 316 return self._type
317
318 - def __str__(self):
319 return '{0.name}:{0.type.__name__}'.format(self)
320
321 - def copy(self):
322 """ 323 @return: a deep copy of C{self} 324 """ 325 return ColumnInfo(self.name, self.type)
326
327 -class DataRow(object):
328 """ 329 Represents a table data row. This is basically what a table iterator 330 yields for each row in a table. Provides both index (position) and 331 column name-based access to the data. 332 """ 333
334 - def __init__(self, columns, number, row):
335 336 self._number = number 337 self._row = tuple(row) 338 self._columns = {} 339 for i, c in enumerate(columns): 340 self._columns[c] = i 341 assert len(self._columns) == len(self._row)
342
343 - def __iter__(self):
344 return iter(self._row)
345
346 - def __getitem__(self, i):
347 348 if isinstance(i, csb.core.string): 349 return self._row[self._columns[i]] 350 else: 351 return self._row[i]
352
353 - def __len__(self):
354 return len(self._row)
355
356 - def __repr__(self):
357 return '{0}: {1}'.format(self.__class__.__name__, repr(self._row))
358
359 - def __str__(self):
360 return self.dump()
361
362 - def dump(self, delimiter='\t'):
363 """ 364 Dump the row as a string. 365 366 @param delimiter: column separator (defaults to tab) 367 @type delimiter: str 368 """ 369 return delimiter.join(map(str, self._row))
370 371 @property
372 - def columns(self):
373 """ 374 Available column names 375 @rtype: tuple 376 """ 377 c = dict((self._columns[cn], cn) for cn in self._columns) 378 return tuple(c[i] for i in sorted(c))
379 380 @property
381 - def number(self):
382 """ 383 Row number 384 @rtype: int 385 """ 386 return self._number
387
388 -class Table(object):
389 """ 390 Build and query a TSV Table. See the documentation of L{csb.io.tsv} for 391 details and examples. 392 393 @param definition: column definition string: C{ColA:typeA colB:typeB ...}, 394 where C{ColN} is a column name and C{typeN} is one of the 395 base Python data types: str, int, long, float. 396 Alternatively, the table definition may be specified 397 directly as a list of metadata objects. 398 @type definition: str, tuple of L{ColumnInfo} 399 @param name: name of the table on the SQL backend. Useful when you need to 400 execute native SQL queries against the table. 401 @type name: str 402 @param backend: table backend storage engine. This must be a proper 403 L{RepositoryImp} bridge implementor. 404 @type backend: type (reference to a L{RepositoryImp} subclass) 405 406 @raise UnsupportedTypeError: when an unsupported type is used in the table 407 C{definition} 408 @raise ValueError: if the C{definition} is not valid 409 """ 410 411 """ 412 Table header string, used when saving and restoring TSV files. 413 """ 414 HEADER = '# @TSV ' 415
416 - def __init__(self, definition, name='TSV', backend=SQLiteRepository):
417 418 if not issubclass(backend, RepositoryImp): 419 raise TypeError('The Table Backend must be a Repository Implementor') 420 421 self._name = name 422 self._backend = backend 423 self._imp = backend(name) 424 425 try: 426 if isinstance(definition[0], ColumnInfo): 427 self._metadata = [ c.copy() for c in definition ] 428 else: 429 if isinstance(definition, csb.core.string): 430 definition = [ (d.split(':')[0], getattr(builtins, d.split(':')[1])) for d in definition.split() ] 431 self._metadata = [ ColumnInfo(c[0], c[1]) for c in definition ] 432 if len(self._metadata) < 1: 433 raise ValueError() 434 except UnsupportedTypeError: 435 raise 436 except (TypeError, IndexError, ValueError, NameError, AttributeError): 437 raise ValueError('Invalid table definition') 438 439 self._imp.create(self._metadata)
440 441 @staticmethod
442 - def from_tsv(tsv, definition=None, delimiter='\t', skip=0, name='TSV', backend=SQLiteRepository):
443 """ 444 Table factory: build a L{Table} from a TSV file. 445 446 @param tsv: TSV path and filename. This can be either a conventional 447 TSV/CSV file, or a file created with C{table.dump(tsv)} 448 @type tsv: str 449 @param definition: table column definition (see L{Table}). If defined, 450 this parameter will determine the structure of the 451 table. Otherwise, the table definition will be 452 extracted from the TSV header. If the file contains 453 no TSV header, this parameter is mandatory. 454 @type definition: str, tuple of L{ColumnInfo} 455 @param delimiter: column separator used in the file 456 @type delimiter: str 457 @param skip: skip the first N number of rows (the header can still be 458 extracted from those however) 459 @type skip: int 460 461 @rtype: L{Table} 462 463 @raise ValueError: if neither a table C{definition} is provided, 464 nor the C{tsv} file has a header line 465 """ 466 467 if not definition: 468 with open(tsv) as tsvfile: 469 for line in tsvfile: 470 if line.startswith(Table.HEADER): 471 definition = line[ len(Table.HEADER) : ] 472 473 if not definition: 474 raise ValueError('No header definition found') 475 476 table = Table(definition, name=name, backend=backend) 477 478 with open(tsv) as tsvfile: 479 for i, line in enumerate(tsvfile, start=1): 480 if (skip and i <= skip) or line.startswith(Table.HEADER): 481 continue 482 table.insert(line.rstrip(os.linesep).split(delimiter)) 483 484 return table
485 486 @staticmethod
487 - def from_iterable(iterable, definition, name='TSV', backend=SQLiteRepository):
488 """ 489 Table factory: build a L{Table} from a 2D iterable/data reader. 490 491 @param iterable: data container 492 @type iterable: iterable (2D) 493 @param definition: table column definition (see L{Table}). 494 @type definition: str, tuple of L{ColumnInfo} 495 496 @rtype: L{Table} 497 """ 498 table = Table(definition, name=name, backend=backend) 499 500 for row in iterable: 501 table.insert(list(row)) 502 503 return table
504 505 @staticmethod
506 - def from_table(table, data=False, name='TSV', backend=SQLiteRepository):
507 """ 508 Table factory: build a L{Table} with the definition of another L{Table}. 509 510 @param table: template table 511 @type table: L{Table} 512 @param data: if True, also copy the data from the source C{table} 513 @type data: bool 514 515 @rtype: L{Table} 516 """ 517 if data: 518 return Table.from_iterable(table, table._metadata, name=name, backend=backend) 519 else: 520 return Table(table._metadata, name=name, backend=backend)
521
522 - def __del__(self):
523 self._imp.close()
524
525 - def __len__(self):
526 return self._imp.count()
527
528 - def __iter__(self):
529 rn = 0 530 exp = Expression(self.columns) 531 532 for row in self._imp.execute(exp): 533 rn += 1 534 yield DataRow(self.columns, rn, row)
535
536 - def __array__(self):
537 import numpy 538 return numpy.array([ tuple(row) for row in self ])
539
540 - def __getstate__(self):
541 542 temp = csb.io.MemoryStream() 543 self.dump(temp) 544 return temp.getvalue()
545
546 - def __setstate__(self, state):
547 548 with csb.io.TempFile() as temp: 549 temp.write(state) 550 temp.flush() 551 clone = Table.from_tsv(temp.name) 552 553 self.__init__(definition=clone._metadata, name=clone.name, backend=clone._backend) 554 555 for row in clone: 556 self.insert(row)
557
558 - def __setitem__(self, i, value):
559 560 exp = self._interpret(i) 561 562 if len(exp.select) != 1: 563 raise NotImplementedError('single-column expression expected') 564 if csb.core.iterable(value): 565 raise NotImplementedError("single-value assignment expected") 566 567 exp.data = value 568 self._update(exp)
569
570 - def __getitem__(self, i):
571 572 exp = self._interpret(i) 573 574 if exp.scalar: 575 return self.scalar(i[0], exp.select[0]) 576 else: 577 return self._execute(exp)
578
579 - def _interpret(self, i):
580 """ 581 Parse a table slice and convert it into an L{Expression}. 582 @rtype: L{Expression} 583 """ 584 585 if not csb.core.iterable(i): 586 i = [i, slice(None, None)] 587 else: 588 i = list(i) 589 590 if len(i) not in (1, 2): 591 raise ValueError('Tables are only 2 dimensional') 592 if len(i) == 1: 593 i.append(slice(None, None)) 594 595 exp = Expression(self.columns) 596 columns = self._getcols(i[1]) 597 if len(columns) < 1: 598 raise ValueError('Column slices must return at least one column') 599 exp.select = columns 600 exp.where = self.pk 601 602 if isinstance(i[0], int): 603 self._checkrow(i[0]) 604 if len(columns) == 1 and isinstance(i[1], (int, csb.core.string)): 605 exp.scalar = True 606 exp.predicate = Equals(i[0] + 1) 607 608 elif csb.core.iterable(i[0]): 609 params = list(i[0]) 610 self._checkrow(params) 611 params = list(map(lambda x: x + 1, params)) 612 exp.predicate = In(params) 613 614 elif isinstance(i[0], slice): 615 616 sl = i[0] 617 if sl.step is not None: 618 raise NotImplementedError('Row slice steps are not supported') 619 620 if sl == slice(None, None): 621 exp.where = None 622 elif sl.start is None: 623 self._checkrow(sl.stop) 624 exp.predicate = Lower(sl.stop + 1) 625 elif sl.stop is None: 626 self._checkrow(sl.start) 627 exp.predicate = GreaterOrEquals(sl.start + 1) 628 else: 629 self._checkrow([sl.start, sl.stop]) 630 exp.predicate = Between(sl.start + 1, sl.stop) 631 632 else: 633 raise TypeError("Can't handle row slice expression: {0}".format(i[0])) 634 635 return exp
636
637 - def _checkrow(self, i):
638 639 if isinstance(i, int): 640 if i < 0: 641 raise NotImplementedError('Negative row indices are not supported') 642 elif csb.core.iterable(i): 643 for j in i: 644 self._checkrow(j) 645 else: 646 raise TypeError(i)
647
648 - def _getcols(self, spec, ifnull=None):
649 650 columns = list(self.columns) 651 652 if spec is None and ifnull is not None: 653 return [ifnull] 654 655 elif isinstance(spec, int): 656 try: 657 return [columns[spec]] 658 except: 659 raise IndexError('Column {0} out of range'.format(spec)) 660 661 elif isinstance(spec, csb.core.string): 662 if spec in columns: 663 return [spec] 664 else: 665 raise InvalidColumnError(spec) 666 667 elif isinstance(spec, slice): 668 start = self._getcols(spec.start, columns[0]) 669 start = columns.index(start[0]) 670 671 end = self._getcols(spec.stop, columns[-1]) 672 end = columns.index(end[0]) 673 if spec.stop is None: 674 end += 1 675 676 return [columns[i] for i in range(start, end, spec.step or 1)] 677 678 elif csb.core.iterable(spec): 679 return [self._getcols(i)[0] for i in spec] 680 681 else: 682 raise TypeError("Can't handle column slice expression: {0}".format(spec))
683 684 @property
685 - def name(self):
686 """ 687 Table name 688 @rtype: str 689 """ 690 return self._name
691 692 @property
693 - def columns(self):
694 """ 695 All column names 696 @rtype: tuple 697 """ 698 return tuple(i.name for i in self._metadata)
699 700 @property
701 - def pk(self):
702 return self._imp.pk
703
704 - def dump(self, file):
705 """ 706 Dump the table in a file. 707 708 @param file: destination stream or filename 709 @type file: file (stream) or str (filename) 710 """ 711 712 with csb.io.EntryWriter(file, close=False) as out: 713 714 definition = map(str, self._metadata) 715 out.write(Table.HEADER) 716 out.writeall(definition, delimiter=' ') 717 out.write(csb.io.NEWLINE) 718 719 for row in self: 720 out.writeline(row.dump(delimiter='\t'))
721
722 - def query(self, sql, params=None):
723 """ 724 Execute a native SQL query against the storage engine. 725 726 @param sql: SQL query text. May contain parameter binding placeholders 727 (see L{Predicate.PH}). The SQL dialect of the query depends 728 on the SQL C{backend} being used by the table. 729 730 @return: native data reader 731 @rtype: iterable (2D) 732 """ 733 return self._imp.query(sql, params)
734
735 - def insert(self, row):
736 """ 737 Insert a new row in the table. 738 739 @param row: a tuple of the appropriate length 740 @type row: tuple 741 """ 742 self._imp.insert(row)
743
744 - def _project(self, columns):
745 746 metadata = dict((c.name, c) for c in self._metadata) 747 try: 748 return [metadata[cn].copy() for cn in columns] 749 except KeyError as ke: 750 raise InvalidColumnError(ke.message) 751 except: 752 raise
753
754 - def _execute(self, exp):
755 756 newdef = self._project(exp.select) 757 reader = self._imp.execute(exp) 758 return Table.from_iterable(reader, newdef, name=self.name, backend=self._backend)
759
760 - def _update(self, exp):
761 762 if exp.select[0] not in self.columns: 763 raise InvalidColumnError(exp.select[0]) 764 765 self._imp.update(exp) 766 return self
767
768 - def where(self, column):
769 """ 770 @param column: column name 771 @type column: str 772 @raise InvalidColumnError: when an invalid column is requested 773 """ 774 exp = Expression(self.columns) 775 return Where(self, exp, column)
776
777 - def select(self, *columns):
778 """ 779 @return: a new L{Table} 780 781 @param columns: column names; defaults to all columns 782 @type columns: str, tuple of str 783 @raise InvalidColumnError: when an invalid column is requested 784 """ 785 columns = Expression.array(columns) 786 787 exp = Expression(self.columns) 788 exp.select = columns 789 790 return self._execute(exp)
791
792 - def update(self, column, value):
793 """ 794 Update C{column} for all rows in the table. 795 796 @param column: column to update (name) 797 @type column: str 798 @param value: new column value 799 @raise InvalidColumnError: when an invalid column is referenced 800 """ 801 exp = Expression(self.columns) 802 exp.select = [column] 803 exp.data = value 804 805 return self._update(exp)
806
807 - def scalar(self, row=None, column=None):
808 """ 809 @return: a scalar value at the specified row and column. 810 811 @param row: row index; if not specified - take the first row 812 @type row: int 813 @param column: column name; if not specified - take the first 814 @type column: str 815 816 @raise IndexError: when an invalid row is requested 817 @raise InvalidColumnError: when an invalid column is requested 818 """ 819 820 if row is None: 821 row = 0 822 row += 1 823 if column is None: 824 column = self.columns[0] 825 elif column not in self.columns: 826 raise InvalidColumnError(column) 827 828 exp = Expression(self.columns) 829 exp.select = [column] 830 exp.where = self.pk 831 exp.predicate = Equals([row]) 832 833 reader = list(self._imp.execute(exp)) 834 if len(reader) > 0: 835 return reader[0][0] 836 else: 837 raise IndexError()
838
839 - def column(self, column):
840 """ 841 @return: a list all values in the specified column 842 843 @param column: column to fetch 844 @type column: str 845 """ 846 if column not in self.columns: 847 raise InvalidColumnError(column) 848 849 return [ row[column] for row in self ]
850
851 -class Expression(object):
852 """ 853 Metadata container: represents a table select or update expression. 854 """ 855
856 - def __init__(self, columns):
857 858 self._table = None 859 self._columns = [] 860 861 self._columns = list(columns) 862 self._select = [] 863 self._where = None 864 self._predicate = None 865 self._data = None 866 self._scalar = False 867 868 self.select = '*'
869 870 @staticmethod
871 - def array(args):
872 if len(args) == 1 and csb.core.iterable(args[0]): 873 args = args[0] 874 return list(args)
875 876 @property
877 - def all(self):
878 return tuple(self._columns)
879 880 @property
881 - def params(self):
882 if self.where and self.predicate: 883 return self.predicate.params 884 else: 885 return None
886 887 @property
888 - def select(self):
889 return self._select
890 @select.setter
891 - def select(self, value):
892 self._select = [] 893 if not value: 894 self._select = list(self.all) 895 else: 896 if not csb.core.iterable(value): 897 value = [value] 898 for i in value: 899 if i == '*': 900 self._select.extend(self.all) 901 else: 902 if i not in self._columns: 903 raise InvalidColumnError(i) 904 self._select.append(i)
905 906 @property
907 - def where(self):
908 return self._where
909 @where.setter
910 - def where(self, value):
911 if not value: 912 self._where = None 913 self._predicate = None 914 else: 915 self._where = value
916 917 @property
918 - def predicate(self):
919 return self._predicate
920 @predicate.setter
921 - def predicate(self, value):
922 if not value: 923 self._where = None 924 self._predicate = None 925 else: 926 self._predicate = value
927 928 @property
929 - def data(self):
930 return self._data
931 @data.setter
932 - def data(self, value):
933 self._data = value
934 935 @property
936 - def scalar(self):
937 return self._scalar
938 @scalar.setter
939 - def scalar(self, value):
940 self._scalar = value
941
942 -class Step(object):
943
944 - def __init__(self, table, expression):
945 946 self._table = table 947 self._expression = expression
948 949 @property
950 - def table(self):
951 return self._table
952 953 @property
954 - def expression(self):
955 return self._expression
956
957 -class Where(Step):
958
959 - def __init__(self, table, expression, column):
960 961 if column not in table.columns and column != table.pk: 962 raise InvalidColumnError(column) 963 964 expression.where = column 965 super(Where, self).__init__(table, expression)
966
967 - def in_(self, *values):
968 return Operator(self.table, self.expression, In(values))
969
970 - def notin(self, *values):
971 return Operator(self.table, self.expression, NotIn(values))
972
973 - def between(self, start, end):
974 return Operator(self.table, self.expression, Between(start, end))
975
976 - def equals(self, value):
977 return Operator(self.table, self.expression, Equals(value))
978
979 - def notequals(self, value):
980 return Operator(self.table, self.expression, NotEquals(value))
981
982 - def greater(self, value):
983 return Operator(self.table, self.expression, Greater(value))
984
985 - def lower(self, value):
986 return Operator(self.table, self.expression, Lower(value))
987
988 -class Operator(Step):
989
990 - def __init__(self, table, expression, predicate):
994
995 - def select(self, *columns):
996 """ 997 @return: a new L{Table} 998 999 @param columns: column names; defaults to all columns 1000 @type columns: str, tuple of str 1001 @raise InvalidColumnError: when an invalid column is requested 1002 """ 1003 exp = self.expression 1004 exp.select = columns 1005 1006 return self.table._execute(exp)
1007
1008 - def update(self, column, value):
1009 """ 1010 Update C{column} for all rows in the table. 1011 1012 @param column: column to update (name) 1013 @type column: str 1014 @param value: new column value 1015 @raise InvalidColumnError: when an invalid column is referenced 1016 """ 1017 exp = self.expression 1018 exp.select = [column] 1019 exp.data = value 1020 1021 return self.table._update(exp)
1022
1023 -class Predicate(object):
1024 1025 __metaclass__ = ABCMeta 1026 1027 PH = '?' 1028
1029 - def __init__(self, params):
1030 1031 self._params = [] 1032 1033 if not csb.core.iterable(params): 1034 params = [params] 1035 1036 for p in params: 1037 if csb.core.iterable(p): 1038 self._params.extend(p) 1039 else: 1040 self._params.append(p) 1041 1042 self._validate()
1043 1044 @property
1045 - def params(self):
1046 return tuple(self._params)
1047
1048 - def _validate(self):
1049 1050 if len(self._params) < 1: 1051 raise ValueError('{0} predicate with no params'.format(self.__class__.__name__))
1052 1053 @abstractproperty
1054 - def sql(self):
1055 pass
1056
1057 - def __str__(self):
1058 return self.sql
1059
1060 -class In(Predicate):
1061 1062 @property
1063 - def sql(self):
1064 p = [Predicate.PH for dummy in self.params] 1065 return 'IN ({0})'.format(', '.join(p))
1066
1067 -class NotIn(Predicate):
1068 1069 @property
1070 - def sql(self):
1071 p = [Predicate.PH for dummy in self.params] 1072 return 'NOT IN ({0})'.format(', '.join(p))
1073
1074 -class Equals(Predicate):
1075 1076 @property
1077 - def sql(self):
1078 return '= {0}'.format(Predicate.PH)
1079
1080 -class NotEquals(Predicate):
1081 1082 @property
1083 - def sql(self):
1084 return '!= {0}'.format(Predicate.PH)
1085
1086 -class Greater(Predicate):
1087 1088 @property
1089 - def sql(self):
1090 return '> {0}'.format(Predicate.PH)
1091
1092 -class GreaterOrEquals(Predicate):
1093 1094 @property
1095 - def sql(self):
1096 return '>= {0}'.format(Predicate.PH)
1097
1098 -class Lower(Predicate):
1099 1100 @property
1101 - def sql(self):
1102 return '< {0}'.format(Predicate.PH)
1103
1104 -class LowerOrEquals(Predicate):
1105 1106 @property
1107 - def sql(self):
1108 return '<= {0}'.format(Predicate.PH)
1109
1110 -class Between(Predicate):
1111
1112 - def __init__(self, start, end):
1113 super(Between, self).__init__([start, end])
1114 @property
1115 - def sql(self):
1116 return 'BETWEEN {0} AND {0}'.format(Predicate.PH)
1117