def __call__(self, context): saconnect = ISQLAlchemyConnectionStrings(component.getUtility(ISiteRoot)) items = [SimpleTerm(url, url, name) for name, url in saconnect.items()] return SimpleVocabulary(items)
class TestView(form.Form): ignoreContext = True template = ViewPageTemplateFile('templates/sql_test.pt') def __init__(self, context, request): super(TestView, self).__init__(context, request) self.saconnect = ISQLAlchemyConnectionStrings( component.getUtility(ISiteRoot)) self.Base = None self.tablesVoc = [] self.table = [] self.columnsVoc = [] self.columns = [] self.items = [] def updateFields(self): fields = field.Fields(ITestForm) connections = [] for name, url in self.saconnect.items(): connections.append(SimpleTerm(url, url, name)) fields['connection'].field.vocabulary = SimpleVocabulary(connections) if len(connections) == 1: if self.Base == None: self.initBase(connections[0].value) self.tablesVoc = self.getTables() fields['table'].field.vocabulary = SimpleVocabulary.fromValues( self.tablesVoc) if len(self.tablesVoc) == 1: self.columnsVoc = self.getColumns(self.tablesVoc[0]) fields[ 'columns'].field.value_type.vocabulary = SimpleVocabulary.fromValues( self.columnsVoc) fields['columns'].field.default = fields[ 'columns'].field.values self.fields = fields def update(self): self.updateFields() self.updateWidgets() data, errors = self.extractData() table = data.get('table') if data.get('connection'): if self.Base == None: self.initBase(data.get('connection')) if not self.tablesVoc: self.tablesVoc = self.getTables() self.fields[ 'table'].field.vocabulary = SimpleVocabulary.fromValues( self.tablesVoc) if len(self.tablesVoc) == 1 and table == None: table = self.tablesVoc[0] if table: if not self.columnsVoc: self.columnsVoc = self.getColumns(table) self.fields[ 'columns'].field.value_type.vocabulary = SimpleVocabulary.fromValues( self.columnsVoc) # self.fields['columns'].field.value_type.default = self.columnsVoc super(TestView, self).update() def initBase(self, url): engine = create_engine(url, echo=True) self.Base = automap_base(bind=engine) def getTables(self): conn = self.Base.metadata.bind.connect() return self.Base.metadata.bind.engine.table_names([], connection=conn) def getColumns(self, table): self.Base.metadata.reflect(only=[table]) return [a.name for a in self.Base.metadata.tables[table].c] def getItems(self, table, columns): self.Base.prepare(self.Base.metadata.bind) Session = scoped_session( sessionmaker(bind=self.Base.metadata.bind, extension=ZopeTransactionExtension())) session = Session() item = getattr(self.Base.classes, table, None) return session.query(item).all() @button.buttonAndHandler(_('Apply'), name='apply') def handleApply(self, action): data, errors = self.extractData() if errors: self.status = self.formErrorsMessage return if data.get('table') and data.get('columns'): self.table = data.get('table') self.columns = data.get('columns') self.items = self.getItems(self.table, self.columns)
class TestView(form.Form): ignoreContext = True template = ViewPageTemplateFile('templates/sql_test.pt') def __init__(self, context, request): super(TestView, self).__init__(context, request) self.saconnect = ISQLAlchemyConnectionStrings( component.getUtility(ISiteRoot)) self.Base = None self.tablesVoc = [] self.table = [] self.columnsVoc = [] self.columns = [] self.items = [] def updateFields(self): fields = field.Fields(ITestForm) connections = [] for name, url in self.saconnect.items(): connections.append(SimpleTerm(url, url, name)) fields['connection'].field.vocabulary = SimpleVocabulary(connections) if len(connections) == 1: if self.Base == None: self.initBase(connections[0].value) self.tablesVoc = self.getTables() fields['table'].field.vocabulary = SimpleVocabulary.fromValues(self.tablesVoc) if len(self.tablesVoc) == 1: self.columnsVoc = self.getColumns(self.tablesVoc[0]) fields['columns'].field.value_type.vocabulary = SimpleVocabulary.fromValues(self.columnsVoc) fields['columns'].field.default = fields['columns'].field.values self.fields = fields def update(self): self.updateFields() self.updateWidgets() data, errors = self.extractData() table = data.get('table') if data.get('connection'): if self.Base == None: self.initBase(data.get('connection')) if not self.tablesVoc: self.tablesVoc = self.getTables() self.fields['table'].field.vocabulary = SimpleVocabulary.fromValues(self.tablesVoc) if len(self.tablesVoc) == 1 and table == None: table = self.tablesVoc[0] if table: if not self.columnsVoc: self.columnsVoc = self.getColumns(table) self.fields['columns'].field.value_type.vocabulary = SimpleVocabulary.fromValues(self.columnsVoc) # self.fields['columns'].field.value_type.default = self.columnsVoc super(TestView, self).update() def initBase(self, url): engine = create_engine(url, echo=True) self.Base = automap_base(bind=engine) def getTables(self): conn = self.Base.metadata.bind.connect() return self.Base.metadata.bind.engine.table_names([], connection=conn) def getColumns(self, table): self.Base.metadata.reflect(only=[table]) return [a.name for a in self.Base.metadata.tables[table].c] def getItems(self, table, columns): self.Base.prepare(self.Base.metadata.bind) Session = scoped_session(sessionmaker(bind=self.Base.metadata.bind, extension=ZopeTransactionExtension())) session = Session() item = getattr(self.Base.classes, table, None) return session.query(item).all() @button.buttonAndHandler(_('Apply'), name='apply') def handleApply(self, action): data, errors = self.extractData() if errors: self.status = self.formErrorsMessage return if data.get('table') and data.get('columns'): self.table = data.get('table') self.columns = data.get('columns') self.items = self.getItems(self.table, self.columns)