Debian Codegraph @

csb/io/tsv.py

raw
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(Table._isnull, 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 NULL = ''
416
417 def __init__(self, definition, name='TSV', backend=SQLiteRepository):
418
419 if not issubclass(backend, RepositoryImp):
420 raise TypeError('The Table Backend must be a Repository Implementor')
421
422 self._name = name
423 self._backend = backend
424 self._imp = backend(name)
425 self._metadata = []
426
427 try:
428 if isinstance(definition[0], ColumnInfo):
429 self._metadata = [ c.copy() for c in definition ]
430 else:
431 if isinstance(definition, csb.core.string):
432 definition = [ (d.split(':')[0], getattr(builtins, d.split(':')[1])) for d in definition.split() ]
433 self._metadata = [ ColumnInfo(c[0], c[1]) for c in definition ]
434 if len(self._metadata) < 1:
435 raise ValueError()
436 except UnsupportedTypeError:
437 raise
438 except (TypeError, IndexError, ValueError, NameError, AttributeError):
439 raise ValueError('Invalid table definition')
440
441 self._imp.create(self._metadata)
442
443 @staticmethod
444 def from_tsv(tsv, definition=None, delimiter='\t', skip=0, name='TSV',
445 backend=SQLiteRepository):
446 """
447 Table factory: build a L{Table} from a TSV file.
448
449 @param tsv: TSV path and filename. This can be either a conventional
450 TSV/CSV file, or a file created with C{table.dump(tsv)}
451 @type tsv: str
452 @param definition: table column definition (see L{Table}). If defined,
453 this parameter will determine the structure of the
454 table. Otherwise, the table definition will be
455 extracted from the TSV header. If the file contains
456 no TSV header, this parameter is mandatory.
457 @type definition: str, tuple of L{ColumnInfo}
458 @param delimiter: column separator used in the file
459 @type delimiter: str
460 @param skip: skip the first N number of rows (the header can still be
461 extracted from those however)
462 @type skip: int
463
464 @rtype: L{Table}
465
466 @raise ValueError: if neither a table C{definition} is provided,
467 nor the C{tsv} file has a header line
468 """
469
470 if not definition:
471 with open(tsv) as tsvfile:
472 for line in tsvfile:
473 if line.startswith(Table.HEADER):
474 definition = line[ len(Table.HEADER) : ]
475
476 if not definition:
477 raise ValueError('No header definition found')
478
479 table = Table(definition, name=name, backend=backend)
480
481 with open(tsv) as tsvfile:
482 for i, line in enumerate(tsvfile, start=1):
483 if (skip and i <= skip) or line.startswith(Table.HEADER):
484 continue
485 data = line.rstrip(os.linesep).split(delimiter)
486 table.insert(data)
487
488 return table
489
490 @staticmethod
491 def from_iterable(iterable, definition, name='TSV', backend=SQLiteRepository):
492 """
493 Table factory: build a L{Table} from a 2D iterable/data reader.
494
495 @param iterable: data container
496 @type iterable: iterable (2D)
497 @param definition: table column definition (see L{Table}).
498 @type definition: str, tuple of L{ColumnInfo}
499
500 @rtype: L{Table}
501 """
502 table = Table(definition, name=name, backend=backend)
503
504 for row in iterable:
505 table.insert(list(row))
506
507 return table
508
509 @staticmethod
510 def from_table(table, data=False, name='TSV', backend=SQLiteRepository):
511 """
512 Table factory: build a L{Table} with the definition of another L{Table}.
513
514 @param table: template table
515 @type table: L{Table}
516 @param data: if True, also copy the data from the source C{table}
517 @type data: bool
518
519 @rtype: L{Table}
520 """
521 if data:
522 return Table.from_iterable(table, table._metadata, name=name, backend=backend)
523 else:
524 return Table(table._metadata, name=name, backend=backend)
525
526 @staticmethod
527 def _isnull(value):
528 if value is None or str(value) == "":
529 return Table.NULL
530 else:
531 return str(value)
532
533 def _convert(self, row):
534
535 if len(row) != len(self._metadata):
536 raise ValueError("{0} columns expected, got {1}".format(
537 len(self._metadata), len(row)))
538
539 data = []
540
541 for value, ci in zip(row, self._metadata):
542 if value == Table.NULL:
543 data.append(None)
544 elif isinstance(value, csb.core.string):
545 data.append(ci.type(value))
546 else:
547 data.append(value)
548
549 return data
550
551 def __del__(self):
552 self._imp.close()
553
554 def __len__(self):
555 return self._imp.count()
556
557 def __iter__(self):
558 rn = 0
559 exp = Expression(self.columns)
560
561 for row in self._imp.execute(exp):
562 rn += 1
563 yield DataRow(self.columns, rn, row)
564
565 def __array__(self):
566 import numpy
567 return numpy.array([ tuple(row) for row in self ])
568
569 def __getstate__(self):
570
571 temp = csb.io.MemoryStream()
572 self.dump(temp)
573 return temp.getvalue()
574
575 def __setstate__(self, state):
576
577 with csb.io.TempFile() as temp:
578 temp.write(state)
579 temp.flush()
580 clone = Table.from_tsv(temp.name)
581
582 self.__init__(definition=clone._metadata, name=clone.name, backend=clone._backend)
583
584 for row in clone:
585 self.insert(row)
586
587 def __setitem__(self, i, value):
588
589 exp = self._interpret(i)
590
591 if len(exp.select) != 1:
592 raise NotImplementedError('single-column expression expected')
593 if csb.core.iterable(value):
594 raise NotImplementedError("single-value assignment expected")
595
596 exp.data = value
597 self._update(exp)
598
599 def __getitem__(self, i):
600
601 exp = self._interpret(i)
602
603 if exp.scalar:
604 return self.scalar(i[0], exp.select[0])
605 else:
606 return self._execute(exp)
607
608 def _interpret(self, i):
609 """
610 Parse a table slice and convert it into an L{Expression}.
611 @rtype: L{Expression}
612 """
613
614 if not csb.core.iterable(i):
615 i = [i, slice(None, None)]
616 else:
617 i = list(i)
618
619 if len(i) not in (1, 2):
620 raise ValueError('Tables are only 2 dimensional')
621 if len(i) == 1:
622 i.append(slice(None, None))
623
624 exp = Expression(self.columns)
625 columns = self._getcols(i[1])
626 if len(columns) < 1:
627 raise ValueError('Column slices must return at least one column')
628 exp.select = columns
629 exp.where = self.pk
630
631 if isinstance(i[0], int):
632 self._checkrow(i[0])
633 if len(columns) == 1 and isinstance(i[1], (int, csb.core.string)):
634 exp.scalar = True
635 exp.predicate = Equals(i[0] + 1)
636
637 elif csb.core.iterable(i[0]):
638 params = list(i[0])
639 self._checkrow(params)
640 params = list(map(lambda x: x + 1, params))
641 exp.predicate = In(params)
642
643 elif isinstance(i[0], slice):
644
645 sl = i[0]
646 if sl.step is not None:
647 raise NotImplementedError('Row slice steps are not supported')
648
649 if sl == slice(None, None):
650 exp.where = None
651 elif sl.start is None:
652 self._checkrow(sl.stop)
653 exp.predicate = Lower(sl.stop + 1)
654 elif sl.stop is None:
655 self._checkrow(sl.start)
656 exp.predicate = GreaterOrEquals(sl.start + 1)
657 else:
658 self._checkrow([sl.start, sl.stop])
659 exp.predicate = Between(sl.start + 1, sl.stop)
660
661 else:
662 raise TypeError("Can't handle row slice expression: {0}".format(i[0]))
663
664 return exp
665
666 def _checkrow(self, i):
667
668 if isinstance(i, int):
669 if i < 0:
670 raise NotImplementedError('Negative row indices are not supported')
671 elif csb.core.iterable(i):
672 for j in i:
673 self._checkrow(j)
674 else:
675 raise TypeError(i)
676
677 def _getcols(self, spec, ifnull=None):
678
679 columns = list(self.columns)
680
681 if spec is None and ifnull is not None:
682 return [ifnull]
683
684 elif isinstance(spec, int):
685 try:
686 return [columns[spec]]
687 except:
688 raise IndexError('Column {0} out of range'.format(spec))
689
690 elif isinstance(spec, csb.core.string):
691 if spec in columns:
692 return [spec]
693 else:
694 raise InvalidColumnError(spec)
695
696 elif isinstance(spec, slice):
697 start = self._getcols(spec.start, columns[0])
698 start = columns.index(start[0])
699
700 end = self._getcols(spec.stop, columns[-1])
701 end = columns.index(end[0])
702 if spec.stop is None:
703 end += 1
704
705 return [columns[i] for i in range(start, end, spec.step or 1)]
706
707 elif csb.core.iterable(spec):
708 return [self._getcols(i)[0] for i in spec]
709
710 else:
711 raise TypeError("Can't handle column slice expression: {0}".format(spec))
712
713 @property
714 def name(self):
715 """
716 Table name
717 @rtype: str
718 """
719 return self._name
720
721 @property
722 def columns(self):
723 """
724 All column names
725 @rtype: tuple
726 """
727 return tuple(i.name for i in self._metadata)
728
729 @property
730 def pk(self):
731 return self._imp.pk
732
733 def dump(self, file):
734 """
735 Dump the table in a file.
736
737 @param file: destination stream or filename
738 @type file: file (stream) or str (filename)
739 """
740
741 with csb.io.EntryWriter(file, close=False) as out:
742
743 definition = map(str, self._metadata)
744 out.write(Table.HEADER)
745 out.writeall(definition, delimiter=' ')
746 out.write(csb.io.NEWLINE)
747
748 for row in self:
749 out.writeline(row.dump(delimiter='\t'))
750
751 def query(self, sql, params=None):
752 """
753 Execute a native SQL query against the storage engine.
754
755 @param sql: SQL query text. May contain parameter binding placeholders
756 (see L{Predicate.PH}). The SQL dialect of the query depends
757 on the SQL C{backend} being used by the table.
758
759 @return: native data reader
760 @rtype: iterable (2D)
761 """
762 return self._imp.query(sql, params)
763
764 def insert(self, row):
765 """
766 Insert a new row in the table.
767
768 @param row: a tuple of the appropriate length
769 @type row: tuple
770 """
771 data = self._convert(row)
772 self._imp.insert(data)
773
774 def _project(self, columns):
775
776 metadata = dict((c.name, c) for c in self._metadata)
777 try:
778 return [metadata[cn].copy() for cn in columns]
779 except KeyError as ke:
780 raise InvalidColumnError(ke.message)
781 except:
782 raise
783
784 def _execute(self, exp):
785
786 newdef = self._project(exp.select)
787 reader = self._imp.execute(exp)
788 return Table.from_iterable(reader, newdef, name=self.name, backend=self._backend)
789
790 def _update(self, exp):
791
792 if exp.select[0] not in self.columns:
793 raise InvalidColumnError(exp.select[0])
794
795 self._imp.update(exp)
796 return self
797
798 def where(self, column):
799 """
800 @param column: column name
801 @type column: str
802 @raise InvalidColumnError: when an invalid column is requested
803 """
804 exp = Expression(self.columns)
805 return Where(self, exp, column)
806
807 def select(self, *columns):
808 """
809 @return: a new L{Table}
810
811 @param columns: column names; defaults to all columns
812 @type columns: str, tuple of str
813 @raise InvalidColumnError: when an invalid column is requested
814 """
815 columns = Expression.array(columns)
816
817 exp = Expression(self.columns)
818 exp.select = columns
819
820 return self._execute(exp)
821
822 def update(self, column, value):
823 """
824 Update C{column} for all rows in the table.
825
826 @param column: column to update (name)
827 @type column: str
828 @param value: new column value
829 @raise InvalidColumnError: when an invalid column is referenced
830 """
831 exp = Expression(self.columns)
832 exp.select = [column]
833 exp.data = value
834
835 return self._update(exp)
836
837 def scalar(self, row=None, column=None):
838 """
839 @return: a scalar value at the specified row and column.
840
841 @param row: row index; if not specified - take the first row
842 @type row: int
843 @param column: column name; if not specified - take the first
844 @type column: str
845
846 @raise IndexError: when an invalid row is requested
847 @raise InvalidColumnError: when an invalid column is requested
848 """
849
850 if row is None:
851 row = 0
852 row += 1
853 if column is None:
854 column = self.columns[0]
855 elif column not in self.columns:
856 raise InvalidColumnError(column)
857
858 exp = Expression(self.columns)
859 exp.select = [column]
860 exp.where = self.pk
861 exp.predicate = Equals([row])
862
863 reader = list(self._imp.execute(exp))
864 if len(reader) > 0:
865 return reader[0][0]
866 else:
867 raise IndexError()
868
869 def column(self, column):
870 """
871 @return: a list all values in the specified column
872
873 @param column: column to fetch
874 @type column: str
875 """
876 if column not in self.columns:
877 raise InvalidColumnError(column)
878
879 return [ row[column] for row in self ]
880
881 class Expression(object):
882 """
883 Metadata container: represents a table select or update expression.
884 """
885
886 def __init__(self, columns):
887
888 self._table = None
889 self._columns = []
890
891 self._columns = list(columns)
892 self._select = []
893 self._where = None
894 self._predicate = None
895 self._data = None
896 self._scalar = False
897
898 self.select = '*'
899
900 @staticmethod
901 def array(args):
902 if len(args) == 1 and csb.core.iterable(args[0]):
903 args = args[0]
904 return list(args)
905
906 @property
907 def all(self):
908 return tuple(self._columns)
909
910 @property
911 def params(self):
912 if self.where and self.predicate:
913 return self.predicate.params
914 else:
915 return None
916
917 @property
918 def select(self):
919 return self._select
920 @select.setter
921 def select(self, value):
922 self._select = []
923 if not value:
924 self._select = list(self.all)
925 else:
926 if not csb.core.iterable(value):
927 value = [value]
928 for i in value:
929 if i == '*':
930 self._select.extend(self.all)
931 else:
932 if i not in self._columns:
933 raise InvalidColumnError(i)
934 self._select.append(i)
935
936 @property
937 def where(self):
938 return self._where
939 @where.setter
940 def where(self, value):
941 if not value:
942 self._where = None
943 self._predicate = None
944 else:
945 self._where = value
946
947 @property
948 def predicate(self):
949 return self._predicate
950 @predicate.setter
951 def predicate(self, value):
952 if not value:
953 self._where = None
954 self._predicate = None
955 else:
956 self._predicate = value
957
958 @property
959 def data(self):
960 return self._data
961 @data.setter
962 def data(self, value):
963 self._data = value
964
965 @property
966 def scalar(self):
967 return self._scalar
968 @scalar.setter
969 def scalar(self, value):
970 self._scalar = value
971
972 class Step(object):
973
974 def __init__(self, table, expression):
975
976 self._table = table
977 self._expression = expression
978
979 @property
980 def table(self):
981 return self._table
982
983 @property
984 def expression(self):
985 return self._expression
986
987 class Where(Step):
988
989 def __init__(self, table, expression, column):
990
991 if column not in table.columns and column != table.pk:
992 raise InvalidColumnError(column)
993
994 expression.where = column
995 super(Where, self).__init__(table, expression)
996
997 def in_(self, *values):
998 return Operator(self.table, self.expression, In(values))
999
1000 def notin(self, *values):
1001 return Operator(self.table, self.expression, NotIn(values))
1002
1003 def between(self, start, end):
1004 return Operator(self.table, self.expression, Between(start, end))
1005
1006 def equals(self, value):
1007 return Operator(self.table, self.expression, Equals(value))
1008
1009 def notequals(self, value):
1010 return Operator(self.table, self.expression, NotEquals(value))
1011
1012 def greater(self, value):
1013 return Operator(self.table, self.expression, Greater(value))
1014
1015 def lower(self, value):
1016 return Operator(self.table, self.expression, Lower(value))
1017
1018 class Operator(Step):
1019
1020 def __init__(self, table, expression, predicate):
1021
1022 expression.predicate = predicate
1023 super(Operator, self).__init__(table, expression)
1024
1025 def select(self, *columns):
1026 """
1027 @return: a new L{Table}
1028
1029 @param columns: column names; defaults to all columns
1030 @type columns: str, tuple of str
1031 @raise InvalidColumnError: when an invalid column is requested
1032 """
1033 exp = self.expression
1034 exp.select = columns
1035
1036 return self.table._execute(exp)
1037
1038 def update(self, column, value):
1039 """
1040 Update C{column} for all rows in the table.
1041
1042 @param column: column to update (name)
1043 @type column: str
1044 @param value: new column value
1045 @raise InvalidColumnError: when an invalid column is referenced
1046 """
1047 exp = self.expression
1048 exp.select = [column]
1049 exp.data = value
1050
1051 return self.table._update(exp)
1052
1053 class Predicate(object):
1054
1055 __metaclass__ = ABCMeta
1056
1057 PH = '?'
1058
1059 def __init__(self, params):
1060
1061 self._params = []
1062
1063 if not csb.core.iterable(params):
1064 params = [params]
1065
1066 for p in params:
1067 if csb.core.iterable(p):
1068 self._params.extend(p)
1069 else:
1070 self._params.append(p)
1071
1072 self._validate()
1073
1074 @property
1075 def params(self):
1076 return tuple(self._params)
1077
1078 def _validate(self):
1079
1080 if len(self._params) < 1:
1081 raise ValueError('{0} predicate with no params'.format(self.__class__.__name__))
1082
1083 @abstractproperty
1084 def sql(self):
1085 pass
1086
1087 def __str__(self):
1088 return self.sql
1089
1090 class In(Predicate):
1091
1092 @property
1093 def sql(self):
1094 p = [Predicate.PH for dummy in self.params]
1095 return 'IN ({0})'.format(', '.join(p))
1096
1097 class NotIn(Predicate):
1098
1099 @property
1100 def sql(self):
1101 p = [Predicate.PH for dummy in self.params]
1102 return 'NOT IN ({0})'.format(', '.join(p))
1103
1104 class Equals(Predicate):
1105
1106 @property
1107 def sql(self):
1108 return '= {0}'.format(Predicate.PH)
1109
1110 class NotEquals(Predicate):
1111
1112 @property
1113 def sql(self):
1114 return '!= {0}'.format(Predicate.PH)
1115
1116 class Greater(Predicate):
1117
1118 @property
1119 def sql(self):
1120 return '> {0}'.format(Predicate.PH)
1121
1122 class GreaterOrEquals(Predicate):
1123
1124 @property
1125 def sql(self):
1126 return '>= {0}'.format(Predicate.PH)
1127
1128 class Lower(Predicate):
1129
1130 @property
1131 def sql(self):
1132 return '< {0}'.format(Predicate.PH)
1133
1134 class LowerOrEquals(Predicate):
1135
1136 @property
1137 def sql(self):
1138 return '<= {0}'.format(Predicate.PH)
1139
1140 class Between(Predicate):
1141
1142 def __init__(self, start, end):
1143 super(Between, self).__init__([start, end])
1144 @property
1145 def sql(self):
1146 return 'BETWEEN {0} AND {0}'.format(Predicate.PH)