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_engine(server_config): if server_config.protocol == 'postgres': return PostgresServerManager(server_config) elif server_config.protocol == 'mysql': return MySQLServerManager(server_config) elif server_config.protocol == 'sqlite': return SqliteServerManager(server_config) elif server_config.protocol == 'mssql': return MSSQLServerManager(server_config) else: raise Exception("Unknown protocol: '%s" % server_config.protocol)
def __init__(self, database_server_configuration, creating_base_database = False): """ Connects to this database server. """ self.config = database_server_configuration 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': self.protocol_manager = PostgresServerManager() elif self.protocol == 'mysql': self.protocol_manager = MySQLServerManager() elif self.protocol == 'sqlite': self.protocol_manager = SqliteServerManager(self.config.sqlite_db_path) elif self.protocol == 'mssql': self.protocol_manager = MSSQLServerManager() self.open(creating_base_database) self.show_output = False self.open_databases = {}
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
class DatabaseServer(object): """ Handles all non-transactional queries to the database server. Will also return a connection to a specific database. """ def __init__(self, database_server_configuration, creating_base_database = False): """ Connects to this database server. """ self.config = database_server_configuration 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': self.protocol_manager = PostgresServerManager() elif self.protocol == 'mysql': self.protocol_manager = MySQLServerManager() elif self.protocol == 'sqlite': self.protocol_manager = SqliteServerManager(self.config.sqlite_db_path) elif self.protocol == 'mssql': self.protocol_manager = MSSQLServerManager() self.open(creating_base_database) self.show_output = False self.open_databases = {} #print self.get_connection_string() def open(self, creating_base_database = False): if not creating_base_database: self.protocol_manager.create_default_database_if_absent(self.config) connect_string = self.get_connection_string() else: connect_string = self.get_connection_string(get_base_db = True) self.engine = create_engine(connect_string, connect_args={}) self.metadata = MetaData( bind = self.engine ) def get_connection_string(self, get_base_db = False, scrub = False): return self.protocol_manager.get_connection_string(server_config = self.config, get_base_db = get_base_db, scrub = scrub) def log_sql(self, sql_query): logger.log_status("SQL: " + sql_query, tags=["database"], verbosity_level=3) def execute(self, query): try: result = self.engine.execute(query) except: print query raise return result '''Deprecated: DO NOT USE''' def DoQuery(self, query): """ Executes an SQL statement that changes data in some way. Does not return data. Args; query = a SQL statement """ from opus_core.database_management.opus_database import convert_to_mysql_datatype, _log_sql preprocessed_query = convert_to_mysql_datatype(query) if self.show_output: _log_sql(preprocessed_query) self.execute(preprocessed_query) def create_database(self, database_name): """ Create a database on this database server. """ if not self.has_database(database_name): self.protocol_manager.create_database(server = self, database_name = database_name) def drop_database(self, database_name): """ Drop this database. """ if self.has_database(database_name): if database_name in self.open_databases: for db in self.open_databases[database_name]: db.close() self.protocol_manager.drop_database(server = self, database_name = database_name) def get_database(self, database_name, create_if_doesnt_exist = True): """ Returns an object connecting to this database on this database server. """ from opus_core.database_management.opus_database import OpusDatabase if create_if_doesnt_exist: self.create_database(database_name = database_name) database = OpusDatabase( database_server_configuration = self.config, database_name=database_name) if database_name in self.open_databases: self.open_databases[database_name].append(database) else: self.open_databases[database_name] = [database] return database def has_database(self, database_name): return self.protocol_manager.has_database(server = self, database_name = database_name) def close(self): """Explicitly close the connection, without waiting for object deallocation""" for database_name, dbs in self.open_databases.items(): for db in dbs: try: db.close() except: pass self.engine.dispose() del self.engine del self.metadata
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
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
class DatabaseServer(object): """ Handles all non-transactional queries to the database server. Will also return a connection to a specific database. """ def __init__(self, database_server_configuration, creating_base_database=False): """ Connects to this database server. """ self.config = database_server_configuration 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': self.protocol_manager = PostgresServerManager() elif self.protocol == 'mysql': self.protocol_manager = MySQLServerManager() elif self.protocol == 'sqlite': self.protocol_manager = SqliteServerManager( self.config.sqlite_db_path) elif self.protocol == 'mssql': self.protocol_manager = MSSQLServerManager() self.open(creating_base_database) self.show_output = False self.open_databases = {} #print self.get_connection_string() def open(self, creating_base_database=False): if not creating_base_database: self.protocol_manager.create_default_database_if_absent( self.config) connect_string = self.get_connection_string() else: connect_string = self.get_connection_string(get_base_db=True) self.engine = create_engine(connect_string, connect_args={}) self.metadata = MetaData(bind=self.engine) def get_connection_string(self, get_base_db=False, scrub=False): return self.protocol_manager.get_connection_string( server_config=self.config, get_base_db=get_base_db, scrub=scrub) def log_sql(self, sql_query): logger.log_status("SQL: " + sql_query, tags=["database"], verbosity_level=3) def execute(self, query): try: result = self.engine.execute(query) except: print query raise return result '''Deprecated: DO NOT USE''' def DoQuery(self, query): """ Executes an SQL statement that changes data in some way. Does not return data. Args; query = a SQL statement """ from opus_core.database_management.opus_database import convert_to_mysql_datatype, _log_sql preprocessed_query = convert_to_mysql_datatype(query) if self.show_output: _log_sql(preprocessed_query) self.execute(preprocessed_query) def create_database(self, database_name): """ Create a database on this database server. """ if not self.has_database(database_name): self.protocol_manager.create_database(server=self, database_name=database_name) def drop_database(self, database_name): """ Drop this database. """ if self.has_database(database_name): if database_name in self.open_databases: for db in self.open_databases[database_name]: db.close() self.protocol_manager.drop_database(server=self, database_name=database_name) def get_database(self, database_name, create_if_doesnt_exist=True): """ Returns an object connecting to this database on this database server. """ from opus_core.database_management.opus_database import OpusDatabase if create_if_doesnt_exist: self.create_database(database_name=database_name) database = OpusDatabase(database_server_configuration=self.config, database_name=database_name) if database_name in self.open_databases: self.open_databases[database_name].append(database) else: self.open_databases[database_name] = [database] return database def has_database(self, database_name): return self.protocol_manager.has_database(server=self, database_name=database_name) def close(self): """Explicitly close the connection, without waiting for object deallocation""" for database_name, dbs in self.open_databases.items(): for db in dbs: try: db.close() except: pass self.engine.dispose() del self.engine del self.metadata