Sql Database -Plone 5

I have created a custom form. It contains fields of type choice. I want to populate the choices of field with the value from the sql database . Kindly post the solution

  1. Connect and query your sql database, eventually using https://www.sqlalchemy.org/ - and there are plenty of other python examples around. be sure you got the data for further proicessing.
  2. Here are examples how to provide a vocabulary to your custom form: https://docs.plone.org/develop/plone/forms/vocabularies.html Example 4 is probably what you want, fill in the code from above and build a vocabulary from the data gathered above like in Example 1.

How do i add it in the model xml file

<field name="sector" type="zope.schema.Choice">

You can configure a vocabulary inside the supermodel as

    <field name="kreditor_sap_id" type="zope.schema.Choice">
      <description>SAP ID des Kreditors (0=Lagermaterial)</description>
      <title>SAP ID Kreditor.</title>

This requires a ZCML declaration like this:


with a factory like this returning a SimpleVocabulary instance:

def Kreditoren(context):
    """ Kreditor as vocabulary """
    items = list()
    catalog = plone.api.portal.get_tool('portal_catalog')
    brains = catalog(portal_type='kreditor', sort_on='sortable_title')
    tokens_seen = set()
    for brain in brains:
        token = brain.kreditor_sap_id
        if token not in tokens_seen:
                value=token, token=token, title=brain.Title))
    return SimpleVocabulary(items)

The implementation of the SQL part is left to you and belongs into the factory of the vocabulary.

thanks dude was really helpful
I will try to implement it , If I have query I will contact you people