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]: