Example #1
0
    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
Example #2
0
 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
Example #7
0
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