def __init__(self, configuration): """ Constructs the DatabaseConnector object and connects to the database specified by the options given in databaseSettings. To connect to a database give ``{'sqlalchemy.url': 'driver://*****:*****@host/database'``} as configuration. Further databases can be attached by passing a list of URLs or names for keyword ``'attach'``. .. seealso:: documentation of sqlalchemy.create_engine() :type configuration: dict :param configuration: database connection options for SQLAlchemy """ if not configuration: configuration = {} elif isinstance(configuration, basestring): # backwards compatibility to option databaseUrl configuration = {'sqlalchemy.url': configuration} else: configuration = configuration.copy() # allow 'url' as parameter, but move to 'sqlalchemy.url' if 'url' in configuration: if ('sqlalchemy.url' in configuration and configuration['sqlalchemy.url'] != configuration['url']): raise ValueError("Two different URLs specified" " for 'url' and 'sqlalchemy.url'." "Check your configuration.") else: configuration['sqlalchemy.url'] = configuration.pop('url') self.databaseUrl = configuration['sqlalchemy.url'] """Database url""" registerUnicode = configuration.pop('registerUnicode', False) if isinstance(registerUnicode, basestring): registerUnicode = (registerUnicode.lower() in ['1', 'yes', 'true', 'on']) self.registerUnicode = registerUnicode self.engine = engine_from_config(configuration, prefix='sqlalchemy.') """SQLAlchemy engine object""" self.connection = self.engine.connect() """SQLAlchemy database connection object""" self.metadata = MetaData(bind=self.connection) """SQLAlchemy metadata object""" # multi-database table access self.tables = LazyDict(self._tableGetter()) """Dictionary of SQLAlchemy table objects""" if self.engine.name == 'sqlite': # Main database can be prefixed with 'main.' self._mainSchema = 'main' else: # MySQL uses database name for prefix self._mainSchema = self.engine.url.database # attach other databases self.attached = OrderedDict() """Mapping of attached database URLs to internal schema names""" attach = configuration.pop('attach', []) searchPaths = self.engine.name == 'sqlite' for url in self._findAttachableDatabases(attach, searchPaths): self.attachDatabase(url) # register unicode functions self.compatibilityUnicodeSupport = False if self.registerUnicode: self._registerUnicode()
class DatabaseConnector(object): """ Database connection object. """ @classmethod @deprecated def getDBConnector(cls, configuration=None, projectName='cjklib'): """ .. note:: Deprecated method, use :meth:`~cjklib.dbconnector.getDBConnector` instead. """ return getDBConnector(configuration, projectName) @classmethod @deprecated def getDefaultConfiguration(cls, projectName='cjklib'): """ .. note:: Deprecated method, use :meth:`~cjklib.dbconnector.getDefaultConfiguration` instead. """ return getDefaultConfiguration(projectName) def __init__(self, configuration): """ Constructs the DatabaseConnector object and connects to the database specified by the options given in databaseSettings. To connect to a database give ``{'sqlalchemy.url': 'driver://*****:*****@host/database'``} as configuration. Further databases can be attached by passing a list of URLs or names for keyword ``'attach'``. .. seealso:: documentation of sqlalchemy.create_engine() :type configuration: dict :param configuration: database connection options for SQLAlchemy """ if not configuration: configuration = {} elif isinstance(configuration, basestring): # backwards compatibility to option databaseUrl configuration = {'sqlalchemy.url': configuration} else: configuration = configuration.copy() # allow 'url' as parameter, but move to 'sqlalchemy.url' if 'url' in configuration: if ('sqlalchemy.url' in configuration and configuration['sqlalchemy.url'] != configuration['url']): raise ValueError("Two different URLs specified" " for 'url' and 'sqlalchemy.url'." "Check your configuration.") else: configuration['sqlalchemy.url'] = configuration.pop('url') self.databaseUrl = configuration['sqlalchemy.url'] """Database url""" registerUnicode = configuration.pop('registerUnicode', False) if isinstance(registerUnicode, basestring): registerUnicode = (registerUnicode.lower() in ['1', 'yes', 'true', 'on']) self.registerUnicode = registerUnicode self.engine = engine_from_config(configuration, prefix='sqlalchemy.') """SQLAlchemy engine object""" self.connection = self.engine.connect() """SQLAlchemy database connection object""" self.metadata = MetaData(bind=self.connection) """SQLAlchemy metadata object""" # multi-database table access self.tables = LazyDict(self._tableGetter()) """Dictionary of SQLAlchemy table objects""" if self.engine.name == 'sqlite': # Main database can be prefixed with 'main.' self._mainSchema = 'main' else: # MySQL uses database name for prefix self._mainSchema = self.engine.url.database # attach other databases self.attached = OrderedDict() """Mapping of attached database URLs to internal schema names""" attach = configuration.pop('attach', []) searchPaths = self.engine.name == 'sqlite' for url in self._findAttachableDatabases(attach, searchPaths): self.attachDatabase(url) # register unicode functions self.compatibilityUnicodeSupport = False if self.registerUnicode: self._registerUnicode() def _findAttachableDatabases(self, attachList, searchPaths=False): """ Returns URLs for databases that can be attached to a given database. :type searchPaths: bool :param searchPaths: if ``True`` default search paths will be checked for attachable SQLite databases. """ attachable = [] for name in attachList: if '://' in name: # database url attachable.append(name) elif os.path.isabs(name): # path if not os.path.exists(name): continue files = glob.glob(os.path.join(name, "*.db")) files.sort() attachable.extend([('sqlite:///%s' % f) for f in files]) elif '/' not in name and '\\' not in name: # project name configuration = getDefaultConfiguration(name) # first add main database attachable.append(configuration['sqlalchemy.url']) # add attachables from the given project attach = configuration['attach'] if name in attach: # default search path attach.remove(name) if searchPaths: attach.extend(getSearchPaths(name)) attachable.extend( self._findAttachableDatabases(attach, searchPaths)) else: raise ValueError(("Invalid database reference '%s'." " Check your 'attach' settings!") % name) return attachable def _registerUnicode(self): """ Register functions and collations to bring Unicode support to certain engines. """ if self.engine.name == 'sqlite': uUmlaut = self.selectScalar(text(u"SELECT lower('Ü');")) if uUmlaut != u'ü': # register own Unicode aware functions con = self.connection.connection con.create_function("lower", 1, lambda s: s and s.lower()) con.create_collation( "NOCASE", lambda a, b: cmp( a.decode('utf8').lower(), b.decode('utf8').lower())) self.compatibilityUnicodeSupport = True #{ Multiple database support def _getViews(self): """ Returns all views. :rtype: list of str :return: list of views :note: Currently only works for MySQL and SQLite. """ # get views that are currently not (well) supported by SQLalchemy # http://www.sqlalchemy.org/trac/ticket/812 schemas = [self._mainSchema] + self.attached.values() if self.engine.name == 'mysql': views = [] for schema in schemas: viewList = self.execute(text( "SELECT table_name FROM Information_schema.views" " WHERE table_schema = :schema"), schema=schema).fetchall() views.extend([view for view, in viewList if view not in views]) elif self.engine.name == 'sqlite': views = [] identifier_preparer = self.engine.dialect.identifier_preparer for schema in schemas: qschema = identifier_preparer.quote_identifier(schema) s = ("SELECT name FROM %s.sqlite_master " "WHERE type='view' ORDER BY name") % qschema viewList = self.execute(text(s)).fetchall() views.extend([view for view, in viewList if view not in views]) else: logging.warning( "Don't know how to get all views from database." " Unable to register." " Views will not show up in list of available tables.") return [] return views def attachDatabase(self, databaseUrl): """ Attaches a database to the main database. .. versionadded:: 0.3 :type databaseUrl: str :param databaseUrl: database URL :rtype: str :return: the database's schema used to access its tables, ``None`` if that database has been attached before """ if databaseUrl == self.databaseUrl or databaseUrl in self.attached: return url = make_url(databaseUrl) if url.drivername != self.engine.name: raise ValueError( ("Unable to attach database '%s': Incompatible engines." " Check your 'attach' settings!") % databaseUrl) if self.engine.name == 'sqlite': databaseFile = url.database _, dbName = os.path.split(databaseFile) if dbName.endswith('.db'): dbName = dbName[:-3] schema = '%s_%d' % (dbName, len(self.attached)) self.execute(text("""ATTACH DATABASE :database AS :schema"""), database=databaseFile, schema=schema) else: schema = url.database self.attached[databaseUrl] = schema return schema def getTableNames(self): """ Gets the unique list of names of all tables (and views) from the databases. .. versionadded:: 0.3 :rtype: iterable :return: all tables and views """ tables = set(self._getViews()) tables.update(self.engine.table_names(schema=self._mainSchema)) for schema in self.attached.values(): tables.update(self.engine.table_names(schema=schema)) return tables def _tableGetter(self): """ Returns a function that retrieves a SQLAlchemy Table object for a given table name. """ def getTable(tableName): schema = self._findTable(tableName) if schema is not None: return Table(tableName, self.metadata, autoload=True, autoload_with=self.engine, schema=schema) raise KeyError("Table '%s' not found in any database" % tableName) return getTable def _findTable(self, tableName): """ Gets the schema (database name) of the database that offers the given table. The databases will be accessed in the order as attached. :type tableName: str :param tableName: name of table to be located :rtype: str :return: schema name of database including table """ def has_table(tableName, schema): identifier_preparer = self.engine.dialect.identifier_preparer qschema = identifier_preparer.quote_identifier(schema) tableNames = self.selectScalars( text("SELECT name FROM %s.sqlite_master" % qschema)) return tableName in tableNames import sys if sys.platform == 'win32' and self.engine.name == 'sqlite': # work around bug http://bugs.python.org/issue8192 hasTable = has_table else: hasTable = self.engine.has_table if hasTable(tableName, schema=self._mainSchema): return self._mainSchema else: for schema in self.attached.values(): if hasTable(tableName, schema=schema): return schema return None def hasTable(self, tableName): """ Returns ``True`` if the given table exists in one of the databases. .. versionadded:: 0.3 :type tableName: str :param tableName: name of table to be located :rtype: bool :return: ``True`` if table is found, ``False`` otherwise """ schema = self._findTable(tableName) return schema is not None def mainHasTable(self, tableName): """ Returns ``True`` if the given table exists in the main database. .. versionadded:: 0.3 :type tableName: str :param tableName: name of table to be located :rtype: bool :return: ``True`` if table is found, ``False`` otherwise """ return self.engine.has_table(tableName, schema=self._mainSchema) #} #{ Select commands def execute(self, *options, **keywords): """ Executes a request on the given database. """ return self.connection.execute(*options, **keywords) def _decode(self, data): """ Decodes a data row. MySQL will currently return utf8_bin collated values as string object encoded in utf8. We need to fix that here. :param data: a tuple or scalar value """ if hasattr(data, '__iter__'): newData = [] for cell in data: if type(cell) == type(''): cell = cell.decode('utf8') newData.append(cell) return tuple(newData) else: if type(data) == type(''): return data.decode('utf8') else: return data def selectScalar(self, request): """ Executes a select query and returns a single variable. :param request: SQL request :return: a scalar """ result = self.execute(request) assert result.rowcount <= 1 firstRow = result.fetchone() assert not firstRow or len(firstRow) == 1 if firstRow: return self._decode(firstRow[0]) def selectScalars(self, request): """ Executes a select query and returns a list of scalars. :param request: SQL request :return: a list of scalars """ result = self.execute(request) return [self._decode(row[0]) for row in result.fetchall()] def iterScalars(self, request): """ Executes a select query and returns an iterator of scalars. .. versionadded:: 0.3 :param request: SQL request :return: an iterator of scalars """ result = self.execute(request) return imap(self._decode, imap(operator.itemgetter(0), result)) def selectRow(self, request): """ Executes a select query and returns a single table row. :param request: SQL request :return: a list of scalars """ result = self.execute(request) assert result.rowcount <= 1 firstRow = result.fetchone() if firstRow: return self._decode(tuple(firstRow)) def selectRows(self, request): """ Executes a select query and returns a list of table rows. :param request: SQL request :return: a list of tuples """ result = self.execute(request) return [self._decode(tuple(row)) for row in result.fetchall()] def iterRows(self, request): """ Executes a select query and returns an iterator of table rows. .. versionadded:: 0.3 :param request: SQL request :return: an iterator of tuples """ result = self.execute(request) return imap(self._decode, result)
class DatabaseConnector(object): """ Database connection object. """ @classmethod @deprecated def getDBConnector(cls, configuration=None, projectName='cjklib'): """ .. note:: Deprecated method, use :meth:`~cjklib.dbconnector.getDBConnector` instead. """ return getDBConnector(configuration, projectName) @classmethod @deprecated def getDefaultConfiguration(cls, projectName='cjklib'): """ .. note:: Deprecated method, use :meth:`~cjklib.dbconnector.getDefaultConfiguration` instead. """ return getDefaultConfiguration(projectName) def __init__(self, configuration): """ Constructs the DatabaseConnector object and connects to the database specified by the options given in databaseSettings. To connect to a database give ``{'sqlalchemy.url': 'driver://*****:*****@host/database'``} as configuration. Further databases can be attached by passing a list of URLs or names for keyword ``'attach'``. .. seealso:: documentation of sqlalchemy.create_engine() :type configuration: dict :param configuration: database connection options for SQLAlchemy """ if not configuration: configuration = {} elif isinstance(configuration, basestring): # backwards compatibility to option databaseUrl configuration = {'sqlalchemy.url': configuration} else: configuration = configuration.copy() # allow 'url' as parameter, but move to 'sqlalchemy.url' if 'url' in configuration: if ('sqlalchemy.url' in configuration and configuration['sqlalchemy.url'] != configuration['url']): raise ValueError("Two different URLs specified" " for 'url' and 'sqlalchemy.url'." "Check your configuration.") else: configuration['sqlalchemy.url'] = configuration.pop('url') self.databaseUrl = configuration['sqlalchemy.url'] """Database url""" registerUnicode = configuration.pop('registerUnicode', False) if isinstance(registerUnicode, basestring): registerUnicode = (registerUnicode.lower() in ['1', 'yes', 'true', 'on']) self.registerUnicode = registerUnicode self.engine = engine_from_config(configuration, prefix='sqlalchemy.') """SQLAlchemy engine object""" self.connection = self.engine.connect() """SQLAlchemy database connection object""" self.metadata = MetaData(bind=self.connection) """SQLAlchemy metadata object""" # multi-database table access self.tables = LazyDict(self._tableGetter()) """Dictionary of SQLAlchemy table objects""" if self.engine.name == 'sqlite': # Main database can be prefixed with 'main.' self._mainSchema = 'main' else: # MySQL uses database name for prefix self._mainSchema = self.engine.url.database # attach other databases self.attached = OrderedDict() """Mapping of attached database URLs to internal schema names""" attach = configuration.pop('attach', []) searchPaths = self.engine.name == 'sqlite' for url in self._findAttachableDatabases(attach, searchPaths): self.attachDatabase(url) # register unicode functions self.compatibilityUnicodeSupport = False if self.registerUnicode: self._registerUnicode() def _findAttachableDatabases(self, attachList, searchPaths=False): """ Returns URLs for databases that can be attached to a given database. :type searchPaths: bool :param searchPaths: if ``True`` default search paths will be checked for attachable SQLite databases. """ attachable = [] for name in attachList: if '://' in name: # database url attachable.append(name) elif os.path.isabs(name): # path if not os.path.exists(name): continue files = glob.glob(os.path.join(name, "*.db")) files.sort() attachable.extend([('sqlite:///%s' % f) for f in files]) elif '/' not in name and '\\' not in name: # project name configuration = getDefaultConfiguration(name) # first add main database attachable.append(configuration['sqlalchemy.url']) # add attachables from the given project attach = configuration['attach'] if name in attach: # default search path attach.remove(name) if searchPaths: attach.extend(getSearchPaths(name)) attachable.extend(self._findAttachableDatabases(attach, searchPaths)) else: raise ValueError(("Invalid database reference '%s'." " Check your 'attach' settings!") % name) return attachable def _registerUnicode(self): """ Register functions and collations to bring Unicode support to certain engines. """ if self.engine.name == 'sqlite': uUmlaut = self.selectScalar(text(u"SELECT lower('Ü');")) if uUmlaut != u'ü': # register own Unicode aware functions con = self.connection.connection con.create_function("lower", 1, lambda s: s and s.lower()) con.create_collation("NOCASE", lambda a, b: cmp(a.decode('utf8').lower(), b.decode('utf8').lower())) self.compatibilityUnicodeSupport = True #{ Multiple database support def _getViews(self): """ Returns all views. :rtype: list of str :return: list of views :note: Currently only works for MySQL and SQLite. """ # get views that are currently not (well) supported by SQLalchemy # http://www.sqlalchemy.org/trac/ticket/812 schemas = [self._mainSchema] + self.attached.values() if self.engine.name == 'mysql': views = [] for schema in schemas: viewList = self.execute( text("SELECT table_name FROM Information_schema.views" " WHERE table_schema = :schema"), schema=schema).fetchall() views.extend([view for view, in viewList if view not in views]) elif self.engine.name == 'sqlite': views = [] identifier_preparer = self.engine.dialect.identifier_preparer for schema in schemas: qschema = identifier_preparer.quote_identifier(schema) s = ("SELECT name FROM %s.sqlite_master " "WHERE type='view' ORDER BY name") % qschema viewList = self.execute(text(s)).fetchall() views.extend([view for view, in viewList if view not in views]) else: logging.warning("Don't know how to get all views from database." " Unable to register." " Views will not show up in list of available tables.") return [] return views def attachDatabase(self, databaseUrl): """ Attaches a database to the main database. .. versionadded:: 0.3 :type databaseUrl: str :param databaseUrl: database URL :rtype: str :return: the database's schema used to access its tables, ``None`` if that database has been attached before """ if databaseUrl == self.databaseUrl or databaseUrl in self.attached: return url = make_url(databaseUrl) if url.drivername != self.engine.name: raise ValueError( ("Unable to attach database '%s': Incompatible engines." " Check your 'attach' settings!") % databaseUrl) if self.engine.name == 'sqlite': databaseFile = url.database _, dbName = os.path.split(databaseFile) if dbName.endswith('.db'): dbName = dbName[:-3] schema = '%s_%d' % (dbName, len(self.attached)) self.execute(text("""ATTACH DATABASE :database AS :schema"""), database=databaseFile, schema=schema) else: schema = url.database self.attached[databaseUrl] = schema return schema def getTableNames(self): """ Gets the unique list of names of all tables (and views) from the databases. .. versionadded:: 0.3 :rtype: iterable :return: all tables and views """ tables = set(self._getViews()) tables.update(self.engine.table_names(schema=self._mainSchema)) for schema in self.attached.values(): tables.update(self.engine.table_names(schema=schema)) return tables def _tableGetter(self): """ Returns a function that retrieves a SQLAlchemy Table object for a given table name. """ def getTable(tableName): schema = self._findTable(tableName) if schema is not None: return Table(tableName, self.metadata, autoload=True, autoload_with=self.engine, schema=schema) raise KeyError("Table '%s' not found in any database" % tableName) return getTable def _findTable(self, tableName): """ Gets the schema (database name) of the database that offers the given table. The databases will be accessed in the order as attached. :type tableName: str :param tableName: name of table to be located :rtype: str :return: schema name of database including table """ def has_table(tableName, schema): identifier_preparer = self.engine.dialect.identifier_preparer qschema = identifier_preparer.quote_identifier(schema) tableNames = self.selectScalars( text("SELECT name FROM %s.sqlite_master" % qschema)) return tableName in tableNames import sys if sys.platform == 'win32' and self.engine.name == 'sqlite': # work around bug http://bugs.python.org/issue8192 hasTable = has_table else: hasTable = self.engine.has_table if hasTable(tableName, schema=self._mainSchema): return self._mainSchema else: for schema in self.attached.values(): if hasTable(tableName, schema=schema): return schema return None def hasTable(self, tableName): """ Returns ``True`` if the given table exists in one of the databases. .. versionadded:: 0.3 :type tableName: str :param tableName: name of table to be located :rtype: bool :return: ``True`` if table is found, ``False`` otherwise """ schema = self._findTable(tableName) return schema is not None def mainHasTable(self, tableName): """ Returns ``True`` if the given table exists in the main database. .. versionadded:: 0.3 :type tableName: str :param tableName: name of table to be located :rtype: bool :return: ``True`` if table is found, ``False`` otherwise """ return self.engine.has_table(tableName, schema=self._mainSchema) #} #{ Select commands def execute(self, *options, **keywords): """ Executes a request on the given database. """ return self.connection.execute(*options, **keywords) def _decode(self, data): """ Decodes a data row. MySQL will currently return utf8_bin collated values as string object encoded in utf8. We need to fix that here. :param data: a tuple or scalar value """ if hasattr(data, '__iter__'): newData = [] for cell in data: if type(cell) == type(''): cell = cell.decode('utf8') newData.append(cell) return tuple(newData) else: if type(data) == type(''): return data.decode('utf8') else: return data def selectScalar(self, request): """ Executes a select query and returns a single variable. :param request: SQL request :return: a scalar """ result = self.execute(request) assert result.rowcount <= 1 firstRow = result.fetchone() assert not firstRow or len(firstRow) == 1 if firstRow: return self._decode(firstRow[0]) def selectScalars(self, request): """ Executes a select query and returns a list of scalars. :param request: SQL request :return: a list of scalars """ result = self.execute(request) return [self._decode(row[0]) for row in result.fetchall()] def iterScalars(self, request): """ Executes a select query and returns an iterator of scalars. .. versionadded:: 0.3 :param request: SQL request :return: an iterator of scalars """ result = self.execute(request) return imap(self._decode, imap(operator.itemgetter(0), result)) def selectRow(self, request): """ Executes a select query and returns a single table row. :param request: SQL request :return: a list of scalars """ result = self.execute(request) assert result.rowcount <= 1 firstRow = result.fetchone() if firstRow: return self._decode(tuple(firstRow)) def selectRows(self, request): """ Executes a select query and returns a list of table rows. :param request: SQL request :return: a list of tuples """ result = self.execute(request) return [self._decode(tuple(row)) for row in result.fetchall()] def iterRows(self, request): """ Executes a select query and returns an iterator of table rows. .. versionadded:: 0.3 :param request: SQL request :return: an iterator of tuples """ result = self.execute(request) return imap(self._decode, result)