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_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
and_(
genre.c.id == 1,
artist.c.id == 1,
)))
# In[136]:
for row in result.fetchall():
print(row)
# 연결 종료와 메타데이터 클리어
# In[137]:
conn.close()
metadata.clear()
# ### Declare
# declarative_base
# In[138]:
from sqlalchemy.ext.declarative import declarative_base
# In[139]:
from sqlalchemy import create_engine
# In[140]:
