def create_reflected_test_database(read_engine, write_engine): meta = MetaData(bind=write_engine) meta.reflect(bind=read_engine) for tablename in ('matviews', 'stats_station_var'): meta.remove(meta.tables[tablename]) logger.info("Overriding PG types that are unknown to sqlite") meta.tables['meta_history'].columns['tz_offset'].type = Integer() meta.tables['obs_raw'].columns['mod_time'].server_default = None meta.tables['meta_history'].columns['the_geom'].type = Integer() # These are all BIGINT in postgres meta.tables['obs_raw'].columns['obs_raw_id'].type = Integer() meta.tables['obs_raw_native_flags'].columns['obs_raw_id'].type = Integer() meta.tables['obs_raw_pcic_flags'].columns['obs_raw_id'].type = Integer() logger.info("Unsetting all of the sequence defaults") for tablename, table in meta.tables.iteritems(): if hasattr(table, 'primary_key'): for column in table.primary_key.columns.values(): if column.server_default: column.server_default = None logger.info("Creating a subset of the tables") to_search = [ 'obs_raw', 'meta_history', 'meta_station', 'meta_network', 'meta_vars', 'meta_contact' ] to_create = [ table for tablename, table in meta.tables.iteritems() if tablename in to_search ] # Don't have contact in the postgres database yet 2013.12.04 meta.tables['meta_network'].append_column(Column('contact_id', Integer)) meta.create_all(tables=to_create)
class Database(object): def __init__(self, *args, **kw): self.engine = create_engine(*args, **kw) self.metadata = MetaData(bind=self.engine) self.metadata.reflect(bind=self.engine) Session = sessionmaker(bind=self.engine) self.session = Session() def __contains__(self, tablename): return tablename in self.metadata.tables.keys() def __getitem__(self, tablename): return Table(self.metadata.tables[tablename], self.session) def __setitem__(self, dn, node): raise NotImplementedError() def __delitem__(self, tablename): table = self[tablename] table._table.drop(self.engine) self.metadata.remove(table._table) def __iter__(self): for tablename in self.metadata.tables: yield tablename def __len__(self): return sum(1 for table in iter(self)) iterkeys = __iter__ def itervalues(self): for table in self.metadata.tables.values(): yield Table(table, self.session) def iteritems(self): return ((table.name, table) for table in ValuesView(self)) def items(self): return ItemsView(dictionary=self) def keys(self): return KeysView(dictionary=self) def values(self): return ValuesView(dictionary=self)
class OpusDatabase(object): """Represents a connection a database, administered through sqlalchemy.""" def __init__(self, database_server_configuration, database_name, show_output=False): """ Connects to this database. """ self.show_output = show_output self.protocol_manager = DatabaseEngineManagerFactory.get_engine( database_server_configuration) self.database_name = self.protocol_manager.format_db_name( database_name) self.database_server_config = database_server_configuration self.open() self.show_output = False protocol = property(lambda self: self.database_server_config.protocol) host_name = property(lambda self: self.database_server_config.host_name) user_name = property(lambda self: self.database_server_config.user_name) password = property(lambda self: self.database_server_config.password) def get_connection_string(self, scrub=False): return self.protocol_manager.get_connection_string( database_name=self.database_name, scrub=scrub) def open(self): self.protocol_manager.create_default_database_if_absent( self.database_server_config) self.engine = create_engine(self.get_connection_string()) self.metadata = MetaData(bind=self.engine) self.reflect(recurse=False) def reflect(self, clear=True, recurse=True): kwargs = {} if sqlalchemy_supports_view: kwargs.update({"views": True}) try: if clear: self.metadata.clear() if self.protocol_manager.uses_schemas: self.metadata.reflect(bind=self.engine, schema=self.database_name, **kwargs) else: self.metadata.reflect(bind=self.engine, **kwargs) except: if recurse: self.close() self.open() self.reflect(clear=False, recurse=False) else: raise def close(self): """Explicitly close the connection, without waiting for object deallocation""" try: self.engine.dispose() except: pass self.engine = None self.metadata = None gc.collect() def execute(self, *args, **kwargs): recurse = kwargs.pop('recurse', False) try: return self.engine.execute(*args, **kwargs) except: if recurse: self.close() self.open() kwargs['recurse'] = True self.execute(*args, **kwargs) else: raise '''Deprecated: DO NOT USE''' def DoQuery(self, query): """ Executes an SQL statement that changes data in some way. Does not return data. Args; query = an SQL statement """ self.reflect() preprocessed_query = convert_to_mysql_datatype(query) if self.show_output: _log_sql(preprocessed_query) self.execute(preprocessed_query) self.reflect(clear=False) def GetResultsFromQuery(self, query): """ Returns records from query, as a list, the first element of which is a list of field names Args: query = query to execute """ self.reflect() preprocessed_query = convert_to_mysql_datatype(query) if self.show_output: _log_sql(preprocessed_query) result = self.execute(preprocessed_query) self.reflect(clear=False) results = result.fetchall() resultlist = [list(row) for row in results] return [[d[0] for d in result.cursor.cursor.description]] + resultlist def get_schema_from_table(self, table_name): """Returns this table's schema (a dictionary of field_name:field_type). """ self.reflect() t = self.get_table(table_name) schema = {} for col in t.columns: schema[str(col.name)] = inverse_type_mapper(col.type) return schema def get_table(self, table_name): self.reflect() if not self.protocol_manager.uses_schemas and self.engine.has_table( table_name=table_name): t = self.metadata.tables[table_name] elif self.protocol_manager.uses_schemas and self.engine.has_table( table_name=table_name, schema=self.database_name): t = self.metadata.tables['%s.%s' % (self.database_name, table_name)] else: raise Exception('Table %s not found in %s' % (table_name, self.database_name)) return t def create_table_from_schema(self, table_name, table_schema): columns = [] for col_name, type_val in table_schema.items(): col = Column(col_name, type_mapper(type_val)) columns.append(col) self.create_table(table_name, columns) def create_table(self, table_name, columns): """Create a table called table_name in the set database with the given schema (a dictionary of field_name:field_type). Note that table constraints are not added. """ self.reflect() if self.table_exists(table_name): return kwargs = {} if self.protocol_manager.uses_schemas: kwargs = {'schema': self.database_name} new_table = Table(table_name, self.metadata, *columns, **kwargs) new_table.create(checkfirst=True) return new_table def drop_table(self, table_name): if self.table_exists(table_name): t = self.get_table(table_name) t.drop(bind=self.engine) self.metadata.remove(t) def table_exists(self, table_name): self.reflect() if not self.protocol_manager.uses_schemas and \ self.engine.has_table(table_name = table_name): t = self.metadata.tables[table_name] elif self.protocol_manager.uses_schemas and \ self.engine.has_table(table_name = table_name, schema=self.database_name): t = self.metadata.tables['%s.%s' % (self.database_name, table_name)] else: return False return t.exists() def get_tables_in_database(self): """Returns a list of the tables in this database chain.""" self.reflect() return self.protocol_manager.get_tables_in_database( metadata=self.metadata) def get_primary_keys_for_table(self, table): self.reflect() primary_keys = [] for col in table.c: if col.primary_key: primary_keys.append(col) return primary_keys
class OpusDatabase(object): """Represents a connection a database, administered through sqlalchemy.""" def __init__(self, database_server_configuration, database_name, show_output=False): """ Connects to this database. """ self.show_output = show_output self.protocol = database_server_configuration.protocol self.host_name = database_server_configuration.host_name self.user_name = database_server_configuration.user_name self.password = database_server_configuration.password if self.protocol == "postgres": database_name = database_name.lower() self.protocol_manager = PostgresServerManager() elif self.protocol == "mysql": self.protocol_manager = MySQLServerManager() elif self.protocol == "sqlite": self.protocol_manager = SqliteServerManager(database_server_configuration.sqlite_db_path) elif self.protocol == "mssql": self.protocol_manager = MSSQLServerManager() self.database_name = database_name self.database_server_config = database_server_configuration self.open() self.show_output = False def get_connection_string(self, scrub=False): return self.protocol_manager.get_connection_string( server_config=self.database_server_config, database_name=self.database_name, scrub=scrub ) def open(self): self.protocol_manager.create_default_database_if_absent(self.database_server_config) self.engine = create_engine(self.get_connection_string()) self.metadata = MetaData(bind=self.engine) self.reflect(recurse=False) def reflect(self, clear=True, recurse=True): try: if clear: self.metadata.clear() if self.protocol_manager.uses_schemas: self.metadata.reflect(bind=self.engine, schema=self.database_name) else: self.metadata.reflect(bind=self.engine) except: if recurse: self.close() self.open() self.reflect(clear=False, recurse=False) else: raise def close(self): """Explicitly close the connection, without waiting for object deallocation""" try: self.engine.dispose() except: pass self.engine = None self.metadata = None gc.collect() def execute(self, *args, **kwargs): recurse = kwargs.pop("recurse", False) try: return self.engine.execute(*args, **kwargs) except: if recurse: self.close() self.open() kwargs["recurse"] = True self.execute(*args, **kwargs) else: raise """Deprecated: DO NOT USE""" def DoQuery(self, query): """ Executes an SQL statement that changes data in some way. Does not return data. Args; query = an SQL statement """ self.reflect() preprocessed_query = convert_to_mysql_datatype(query) if self.show_output: _log_sql(preprocessed_query) self.execute(preprocessed_query) self.reflect(clear=False) def GetResultsFromQuery(self, query): """ Returns records from query, as a list, the first element of which is a list of field names Args: query = query to execute """ self.reflect() preprocessed_query = convert_to_mysql_datatype(query) if self.show_output: _log_sql(preprocessed_query) result = self.execute(preprocessed_query) self.reflect(clear=False) results = result.fetchall() resultlist = [list(row) for row in results] return [[d[0] for d in result.cursor.cursor.description]] + resultlist def get_schema_from_table(self, table_name): """Returns this table's schema (a dictionary of field_name:field_type). """ self.reflect() t = self.get_table(table_name) schema = {} for col in t.columns: schema[str(col.name)] = inverse_type_mapper(col.type) return schema def get_table(self, table_name): self.reflect() if not self.protocol_manager.uses_schemas and self.engine.has_table(table_name=table_name): t = self.metadata.tables[table_name] elif self.protocol_manager.uses_schemas and self.engine.has_table( table_name=table_name, schema=self.database_name ): t = self.metadata.tables["%s.%s" % (self.database_name, table_name)] else: raise Exception("Table %s not found in %s" % (table_name, self.database_name)) return t def create_table_from_schema(self, table_name, table_schema): columns = [] for col_name, type_val in table_schema.items(): col = Column(col_name, type_mapper(type_val)) columns.append(col) self.create_table(table_name, columns) def create_table(self, table_name, columns): """Create a table called table_name in the set database with the given schema (a dictionary of field_name:field_type). Note that table constraints are not added. """ self.reflect() if self.table_exists(table_name): return kwargs = {} if self.protocol_manager.uses_schemas: kwargs = {"schema": self.database_name} new_table = Table(table_name, self.metadata, *columns, **kwargs) new_table.create(checkfirst=True) return new_table def drop_table(self, table_name): if self.table_exists(table_name): t = self.get_table(table_name) t.drop(bind=self.engine) self.metadata.remove(t) def table_exists(self, table_name): self.reflect() if not self.protocol_manager.uses_schemas and self.engine.has_table(table_name=table_name): t = self.metadata.tables[table_name] elif self.protocol_manager.uses_schemas and self.engine.has_table( table_name=table_name, schema=self.database_name ): t = self.metadata.tables["%s.%s" % (self.database_name, table_name)] else: return False return t.exists() def get_tables_in_database(self): """Returns a list of the tables in this database chain.""" self.reflect() return self.protocol_manager.get_tables_in_database(metadata=self.metadata) def get_primary_keys_for_table(self, table): self.reflect() primary_keys = [] for col in table.c: if col.primary_key: primary_keys.append(col) return primary_keys