""" Read, query and update textual tables via flexible SQL interface. L{Table}s can be created and populated with data from scratch, built from TSV files, 2D lists or other tables. Once the data is loaded in memory, each storage operation on the table object is delegated via bridge to an SQL storage backend (by default this is SQLite). However the table uses the backend only as a temp storage to ensure maximum portability of the data. Tables can be stored persistently as text (TSV) files and then loaded back in memory when needed. These Tables can be queried and updated in a vast number of ways; each query returns a new L{Table}: 1. Using slice expressions. The general form of a slice expression is C{[rows, columns]}, where C{rows} can be: - a row index, 0-based, e.g. C{5} - a tuple of row indices, e.g. C{(1, 3, 6)} - a standard Python slice, e.g. C{1:3} or C{:5} or C{:} - omitted (means: all rows) and C{columns} can be: - a column index, 0-based, e.g. C{5} - a tuple of columns indices, 0-based - a column name, e.g. C{'TmScore'} - a tuple of column names, e.g. C{('ID', 'TmScore')} - a standard Python slice using column indices - a slice using column names, e.g. C{'ID':'TM'} or C{:'TM'} or C{:} - omitted (means: all columns) 2. Using query expressions, for example: >>> table.where('ID').between(1, 5).select('TmScore', 'RMSD') Table ('TmScore', 'RMSD') >>> table.where('ID').between(1, 5).update('RMSD', 0.2) Table (the same table) 3. With SQL queries: >>> t.query(r'''SELECT ColumnB * ColumnA AS ComputedValue FROM {0.name} WHERE ColumnC IN ({1}, {1})'''.format(t, Predicate.PH), [12, 55]) iterable The data contained in a Table can be extracted in several ways: - if you need a single (scalar) value -- with the C{table[row, column]} indexing expression or with the dedicated C{table.scalar(row, column)} method. - by treating the table as an iterator; each cycle will then yield a L{DataRow} object - with text (TSV) serialization: simply call C{table.dump(file)}. See L{Table} for full API details. """ try: import __builtin__ as builtins except ImportError: import builtins import os import csb.io import csb.core from abc import ABCMeta, abstractmethod, abstractproperty class RepositoryImp(object): """ Abstract SQL backend interface. Defines a number of platform-specific operations, that each concrete backend implementor must provide. """ __metaclass__ = ABCMeta def __init__(self, tablename): self._table = tablename @abstractproperty def pk(self): pass @property def table(self): """ Table name @rtype: str """ return self._table def query(self, sql, params=None): """ Execute a native SQL query against the backend, as-is. @param sql: SQL query @type sql: str @param params: query bound parameters, if any @type params: tuple @return: data reader (2D iterable) """ raise NotImplementedError() @abstractmethod def count(self): """ Count the number of rows in the table. @rtype: int """ pass @abstractmethod def execute(self, expression): """ Perform a select operation given L{expression}. @type expression: L{Expression} @return: data reader (2D iterable) """ pass @abstractmethod def update(self, expression): """ Perform an update operation given L{expression}. @type expression: L{Expression} @return: void """ pass @abstractmethod def insert(self, row): """ Insert a new tuple in the table. @type row: tuple @return: void """ pass @abstractmethod def create(self, metadata): """ Create a table given L{metadata}. @type metadata: tuple of L{ColumnInfo} @return: void """ pass @abstractmethod def close(self): """ Perform cleanup (e.g. close connections). """ pass class InvalidColumnError(KeyError): pass class UnsupportedTypeError(ValueError): pass class SQLiteRepository(RepositoryImp): """ SQLite-based concrete repository implementor. This is the default L{Table} backend. """ PK = 'ROWID' TYPES = { int: 'BIGINT', float: 'REAL', str: 'VARCHAR' } class ChunkedReader(object): SIZE = 10000 def __init__(self, cursor): self._cursor = cursor def __iter__(self): try: while True: rows = self._cursor.fetchmany(self.SIZE) if not rows: break else: for row in rows: yield row finally: self._cursor.close() def __init__(self, tablename): import sqlite3 self._conn = sqlite3.connect(':memory:') self._pk = SQLiteRepository.PK super(SQLiteRepository, self).__init__(tablename) @property def pk(self): return self._pk def _cursor(self, sql, params=None): sql = sql.replace(Predicate.PH, '?') if not params: params = [] return self._conn.execute(sql, params) def query(self, sql, params=None): return self._cursor(sql, params).fetchall() def count(self): query = 'SELECT COUNT(*)\nFROM {0}\n'.format(self.table) return self._cursor(query).fetchone()[0] def execute(self, exp): query = 'SELECT {0}\nFROM {1}\n'.format(', '.join(exp.select), self.table) if exp.where: predicate = str(exp.predicate).replace(Predicate.PH, '?') query += 'WHERE {0} {1}\n'.format(exp.where, predicate) query += 'ORDER BY {0} ASC\n'.format(self.pk) cursor = self._cursor(query, exp.params) return SQLiteRepository.ChunkedReader(cursor) def update(self, exp): params = [exp.data] query = 'UPDATE {0}\n SET {1} = ?\n'.format(self.table, exp.select[0]) if exp.where: predicate = str(exp.predicate).replace(Predicate.PH, '?') query += 'WHERE {0} {1}\n'.format(exp.where, predicate) if exp.params: params.extend(list(exp.params)) return self.query(query, params) def insert(self, row): row = list(row) params = ','.join(['?' for dummy in row]) query = 'INSERT INTO {0} VALUES({1})'.format(self.table, params) self.query(query, row) def create(self, metadata): cols = [] for ci in metadata: type = self._gettype(ci.type) cols.append('{0} {1}'.format(ci.name, type)) statement = 'CREATE TABLE {0} (\n {1}\n);'.format(self.table, ',\n '.join(cols)) self._conn.execute(statement) def _gettype(self, type): try: return SQLiteRepository.TYPES[type] except KeyError: raise UnsupportedTypeError(type) def close(self): try: return self._conn.close() except: pass class ColumnInfo(object): """ Holder object for column metadata. @param name: column name @type name: str @param type: column data type (Python) @type type: type """ def __init__(self, name, type): self._name = name self._type = type @property def name(self): """ Colulmn name @rtype: str """ return self._name @property def type(self): """ Python data type @rtype: class """ return self._type def __str__(self): return '{0.name}:{0.type.__name__}'.format(self) def copy(self): """ @return: a deep copy of C{self} """ return ColumnInfo(self.name, self.type) class DataRow(object): """ Represents a table data row. This is basically what a table iterator yields for each row in a table. Provides both index (position) and column name-based access to the data. """ def __init__(self, columns, number, row): self._number = number self._row = tuple(row) self._columns = {} for i, c in enumerate(columns): self._columns[c] = i assert len(self._columns) == len(self._row) def __iter__(self): return iter(self._row) def __getitem__(self, i): if isinstance(i, csb.core.string): return self._row[self._columns[i]] else: return self._row[i] def __len__(self): return len(self._row) def __repr__(self): return '{0}: {1}'.format(self.__class__.__name__, repr(self._row)) def __str__(self): return self.dump() def dump(self, delimiter='\t'): """ Dump the row as a string. @param delimiter: column separator (defaults to tab) @type delimiter: str """ return delimiter.join(map(Table._isnull, self._row)) @property def columns(self): """ Available column names @rtype: tuple """ c = dict((self._columns[cn], cn) for cn in self._columns) return tuple(c[i] for i in sorted(c)) @property def number(self): """ Row number @rtype: int """ return self._number class Table(object): """ Build and query a TSV Table. See the documentation of L{csb.io.tsv} for details and examples. @param definition: column definition string: C{ColA:typeA colB:typeB ...}, where C{ColN} is a column name and C{typeN} is one of the base Python data types: str, int, long, float. Alternatively, the table definition may be specified directly as a list of metadata objects. @type definition: str, tuple of L{ColumnInfo} @param name: name of the table on the SQL backend. Useful when you need to execute native SQL queries against the table. @type name: str @param backend: table backend storage engine. This must be a proper L{RepositoryImp} bridge implementor. @type backend: type (reference to a L{RepositoryImp} subclass) @raise UnsupportedTypeError: when an unsupported type is used in the table C{definition} @raise ValueError: if the C{definition} is not valid """ """ Table header string, used when saving and restoring TSV files. """ HEADER = '# @TSV ' NULL = '' def __init__(self, definition, name='TSV', backend=SQLiteRepository): if not issubclass(backend, RepositoryImp): raise TypeError('The Table Backend must be a Repository Implementor') self._name = name self._backend = backend self._imp = backend(name) self._metadata = [] try: if isinstance(definition[0], ColumnInfo): self._metadata = [ c.copy() for c in definition ] else: if isinstance(definition, csb.core.string): definition = [ (d.split(':')[0], getattr(builtins, d.split(':')[1])) for d in definition.split() ] self._metadata = [ ColumnInfo(c[0], c[1]) for c in definition ] if len(self._metadata) < 1: raise ValueError() except UnsupportedTypeError: raise except (TypeError, IndexError, ValueError, NameError, AttributeError): raise ValueError('Invalid table definition') self._imp.create(self._metadata) @staticmethod def from_tsv(tsv, definition=None, delimiter='\t', skip=0, name='TSV', backend=SQLiteRepository): """ Table factory: build a L{Table} from a TSV file. @param tsv: TSV path and filename. This can be either a conventional TSV/CSV file, or a file created with C{table.dump(tsv)} @type tsv: str @param definition: table column definition (see L{Table}). If defined, this parameter will determine the structure of the table. Otherwise, the table definition will be extracted from the TSV header. If the file contains no TSV header, this parameter is mandatory. @type definition: str, tuple of L{ColumnInfo} @param delimiter: column separator used in the file @type delimiter: str @param skip: skip the first N number of rows (the header can still be extracted from those however) @type skip: int @rtype: L{Table} @raise ValueError: if neither a table C{definition} is provided, nor the C{tsv} file has a header line """ if not definition: with open(tsv) as tsvfile: for line in tsvfile: if line.startswith(Table.HEADER): definition = line[ len(Table.HEADER) : ] if not definition: raise ValueError('No header definition found') table = Table(definition, name=name, backend=backend) with open(tsv) as tsvfile: for i, line in enumerate(tsvfile, start=1): if (skip and i <= skip) or line.startswith(Table.HEADER): continue data = line.rstrip(os.linesep).split(delimiter) table.insert(data) return table @staticmethod def from_iterable(iterable, definition, name='TSV', backend=SQLiteRepository): """ Table factory: build a L{Table} from a 2D iterable/data reader. @param iterable: data container @type iterable: iterable (2D) @param definition: table column definition (see L{Table}). @type definition: str, tuple of L{ColumnInfo} @rtype: L{Table} """ table = Table(definition, name=name, backend=backend) for row in iterable: table.insert(list(row)) return table @staticmethod def from_table(table, data=False, name='TSV', backend=SQLiteRepository): """ Table factory: build a L{Table} with the definition of another L{Table}. @param table: template table @type table: L{Table} @param data: if True, also copy the data from the source C{table} @type data: bool @rtype: L{Table} """ if data: return Table.from_iterable(table, table._metadata, name=name, backend=backend) else: return Table(table._metadata, name=name, backend=backend) @staticmethod def _isnull(value): if value is None or str(value) == "": return Table.NULL else: return str(value) def _convert(self, row): if len(row) != len(self._metadata): raise ValueError("{0} columns expected, got {1}".format( len(self._metadata), len(row))) data = [] for value, ci in zip(row, self._metadata): if value == Table.NULL: data.append(None) elif isinstance(value, csb.core.string): data.append(ci.type(value)) else: data.append(value) return data def __del__(self): self._imp.close() def __len__(self): return self._imp.count() def __iter__(self): rn = 0 exp = Expression(self.columns) for row in self._imp.execute(exp): rn += 1 yield DataRow(self.columns, rn, row) def __array__(self): import numpy return numpy.array([ tuple(row) for row in self ]) def __getstate__(self): temp = csb.io.MemoryStream() self.dump(temp) return temp.getvalue() def __setstate__(self, state): with csb.io.TempFile() as temp: temp.write(state) temp.flush() clone = Table.from_tsv(temp.name) self.__init__(definition=clone._metadata, name=clone.name, backend=clone._backend) for row in clone: self.insert(row) def __setitem__(self, i, value): exp = self._interpret(i) if len(exp.select) != 1: raise NotImplementedError('single-column expression expected') if csb.core.iterable(value): raise NotImplementedError("single-value assignment expected") exp.data = value self._update(exp) def __getitem__(self, i): exp = self._interpret(i) if exp.scalar: return self.scalar(i[0], exp.select[0]) else: return self._execute(exp) def _interpret(self, i): """ Parse a table slice and convert it into an L{Expression}. @rtype: L{Expression} """ if not csb.core.iterable(i): i = [i, slice(None, None)] else: i = list(i) if len(i) not in (1, 2): raise ValueError('Tables are only 2 dimensional') if len(i) == 1: i.append(slice(None, None)) exp = Expression(self.columns) columns = self._getcols(i[1]) if len(columns) < 1: raise ValueError('Column slices must return at least one column') exp.select = columns exp.where = self.pk if isinstance(i[0], int): self._checkrow(i[0]) if len(columns) == 1 and isinstance(i[1], (int, csb.core.string)): exp.scalar = True exp.predicate = Equals(i[0] + 1) elif csb.core.iterable(i[0]): params = list(i[0]) self._checkrow(params) params = list(map(lambda x: x + 1, params)) exp.predicate = In(params) elif isinstance(i[0], slice): sl = i[0] if sl.step is not None: raise NotImplementedError('Row slice steps are not supported') if sl == slice(None, None): exp.where = None elif sl.start is None: self._checkrow(sl.stop) exp.predicate = Lower(sl.stop + 1) elif sl.stop is None: self._checkrow(sl.start) exp.predicate = GreaterOrEquals(sl.start + 1) else: self._checkrow([sl.start, sl.stop]) exp.predicate = Between(sl.start + 1, sl.stop) else: raise TypeError("Can't handle row slice expression: {0}".format(i[0])) return exp def _checkrow(self, i): if isinstance(i, int): if i < 0: raise NotImplementedError('Negative row indices are not supported') elif csb.core.iterable(i): for j in i: self._checkrow(j) else: raise TypeError(i) def _getcols(self, spec, ifnull=None): columns = list(self.columns) if spec is None and ifnull is not None: return [ifnull] elif isinstance(spec, int): try: return [columns[spec]] except: raise IndexError('Column {0} out of range'.format(spec)) elif isinstance(spec, csb.core.string): if spec in columns: return [spec] else: raise InvalidColumnError(spec) elif isinstance(spec, slice): start = self._getcols(spec.start, columns[0]) start = columns.index(start[0]) end = self._getcols(spec.stop, columns[-1]) end = columns.index(end[0]) if spec.stop is None: end += 1 return [columns[i] for i in range(start, end, spec.step or 1)] elif csb.core.iterable(spec): return [self._getcols(i)[0] for i in spec] else: raise TypeError("Can't handle column slice expression: {0}".format(spec)) @property def name(self): """ Table name @rtype: str """ return self._name @property def columns(self): """ All column names @rtype: tuple """ return tuple(i.name for i in self._metadata) @property def pk(self): return self._imp.pk def dump(self, file): """ Dump the table in a file. @param file: destination stream or filename @type file: file (stream) or str (filename) """ with csb.io.EntryWriter(file, close=False) as out: definition = map(str, self._metadata) out.write(Table.HEADER) out.writeall(definition, delimiter=' ') out.write(csb.io.NEWLINE) for row in self: out.writeline(row.dump(delimiter='\t')) def query(self, sql, params=None): """ Execute a native SQL query against the storage engine. @param sql: SQL query text. May contain parameter binding placeholders (see L{Predicate.PH}). The SQL dialect of the query depends on the SQL C{backend} being used by the table. @return: native data reader @rtype: iterable (2D) """ return self._imp.query(sql, params) def insert(self, row): """ Insert a new row in the table. @param row: a tuple of the appropriate length @type row: tuple """ data = self._convert(row) self._imp.insert(data) def _project(self, columns): metadata = dict((c.name, c) for c in self._metadata) try: return [metadata[cn].copy() for cn in columns] except KeyError as ke: raise InvalidColumnError(ke.message) except: raise def _execute(self, exp): newdef = self._project(exp.select) reader = self._imp.execute(exp) return Table.from_iterable(reader, newdef, name=self.name, backend=self._backend) def _update(self, exp): if exp.select[0] not in self.columns: raise InvalidColumnError(exp.select[0]) self._imp.update(exp) return self def where(self, column): """ @param column: column name @type column: str @raise InvalidColumnError: when an invalid column is requested """ exp = Expression(self.columns) return Where(self, exp, column) def select(self, *columns): """ @return: a new L{Table} @param columns: column names; defaults to all columns @type columns: str, tuple of str @raise InvalidColumnError: when an invalid column is requested """ columns = Expression.array(columns) exp = Expression(self.columns) exp.select = columns return self._execute(exp) def update(self, column, value): """ Update C{column} for all rows in the table. @param column: column to update (name) @type column: str @param value: new column value @raise InvalidColumnError: when an invalid column is referenced """ exp = Expression(self.columns) exp.select = [column] exp.data = value return self._update(exp) def scalar(self, row=None, column=None): """ @return: a scalar value at the specified row and column. @param row: row index; if not specified - take the first row @type row: int @param column: column name; if not specified - take the first @type column: str @raise IndexError: when an invalid row is requested @raise InvalidColumnError: when an invalid column is requested """ if row is None: row = 0 row += 1 if column is None: column = self.columns[0] elif column not in self.columns: raise InvalidColumnError(column) exp = Expression(self.columns) exp.select = [column] exp.where = self.pk exp.predicate = Equals([row]) reader = list(self._imp.execute(exp)) if len(reader) > 0: return reader[0][0] else: raise IndexError() def column(self, column): """ @return: a list all values in the specified column @param column: column to fetch @type column: str """ if column not in self.columns: raise InvalidColumnError(column) return [ row[column] for row in self ] class Expression(object): """ Metadata container: represents a table select or update expression. """ def __init__(self, columns): self._table = None self._columns = [] self._columns = list(columns) self._select = [] self._where = None self._predicate = None self._data = None self._scalar = False self.select = '*' @staticmethod def array(args): if len(args) == 1 and csb.core.iterable(args[0]): args = args[0] return list(args) @property def all(self): return tuple(self._columns) @property def params(self): if self.where and self.predicate: return self.predicate.params else: return None @property def select(self): return self._select @select.setter def select(self, value): self._select = [] if not value: self._select = list(self.all) else: if not csb.core.iterable(value): value = [value] for i in value: if i == '*': self._select.extend(self.all) else: if i not in self._columns: raise InvalidColumnError(i) self._select.append(i) @property def where(self): return self._where @where.setter def where(self, value): if not value: self._where = None self._predicate = None else: self._where = value @property def predicate(self): return self._predicate @predicate.setter def predicate(self, value): if not value: self._where = None self._predicate = None else: self._predicate = value @property def data(self): return self._data @data.setter def data(self, value): self._data = value @property def scalar(self): return self._scalar @scalar.setter def scalar(self, value): self._scalar = value class Step(object): def __init__(self, table, expression): self._table = table self._expression = expression @property def table(self): return self._table @property def expression(self): return self._expression class Where(Step): def __init__(self, table, expression, column): if column not in table.columns and column != table.pk: raise InvalidColumnError(column) expression.where = column super(Where, self).__init__(table, expression) def in_(self, *values): return Operator(self.table, self.expression, In(values)) def notin(self, *values): return Operator(self.table, self.expression, NotIn(values)) def between(self, start, end): return Operator(self.table, self.expression, Between(start, end)) def equals(self, value): return Operator(self.table, self.expression, Equals(value)) def notequals(self, value): return Operator(self.table, self.expression, NotEquals(value)) def greater(self, value): return Operator(self.table, self.expression, Greater(value)) def lower(self, value): return Operator(self.table, self.expression, Lower(value)) class Operator(Step): def __init__(self, table, expression, predicate): expression.predicate = predicate super(Operator, self).__init__(table, expression) def select(self, *columns): """ @return: a new L{Table} @param columns: column names; defaults to all columns @type columns: str, tuple of str @raise InvalidColumnError: when an invalid column is requested """ exp = self.expression exp.select = columns return self.table._execute(exp) def update(self, column, value): """ Update C{column} for all rows in the table. @param column: column to update (name) @type column: str @param value: new column value @raise InvalidColumnError: when an invalid column is referenced """ exp = self.expression exp.select = [column] exp.data = value return self.table._update(exp) class Predicate(object): __metaclass__ = ABCMeta PH = '?' def __init__(self, params): self._params = [] if not csb.core.iterable(params): params = [params] for p in params: if csb.core.iterable(p): self._params.extend(p) else: self._params.append(p) self._validate() @property def params(self): return tuple(self._params) def _validate(self): if len(self._params) < 1: raise ValueError('{0} predicate with no params'.format(self.__class__.__name__)) @abstractproperty def sql(self): pass def __str__(self): return self.sql class In(Predicate): @property def sql(self): p = [Predicate.PH for dummy in self.params] return 'IN ({0})'.format(', '.join(p)) class NotIn(Predicate): @property def sql(self): p = [Predicate.PH for dummy in self.params] return 'NOT IN ({0})'.format(', '.join(p)) class Equals(Predicate): @property def sql(self): return '= {0}'.format(Predicate.PH) class NotEquals(Predicate): @property def sql(self): return '!= {0}'.format(Predicate.PH) class Greater(Predicate): @property def sql(self): return '> {0}'.format(Predicate.PH) class GreaterOrEquals(Predicate): @property def sql(self): return '>= {0}'.format(Predicate.PH) class Lower(Predicate): @property def sql(self): return '< {0}'.format(Predicate.PH) class LowerOrEquals(Predicate): @property def sql(self): return '<= {0}'.format(Predicate.PH) class Between(Predicate): def __init__(self, start, end): super(Between, self).__init__([start, end]) @property def sql(self): return 'BETWEEN {0} AND {0}'.format(Predicate.PH)