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