Updating table with LDAP attribute

[yblis]

Hello everyone,

I wanted to share with you this Python script for Seatable.

I use it to update an LDAP table with the following attributes: cn, sAMAccountName, and mail
to be modified according to your needs:

It will populate the table and pause after 990 lines (my limit, which you can modify at the end of the script).

You will need to install “ldap3”:

pip install ldap3

Here is the script:

from ldap3 import Server, Connection, ALL
from seatable_api import Base, context
import time

# Paramètres de connexion à l'Active Directory
LDAP_SERVER = 'YOURLDAPSERVER'
LDAP_USER = 'DOMAIN\LOGIN'
LDAP_PASSWORD = 'YOURPASSWORD

# DN de recherche dans l'Active Directory pour différentes OUs
OU_CENTRALE = 'OU=Utilisateurs,DC=EXEMPLE,DC=fr'
OU_EXT = 'OU=Utilisateurs Autres,DC=EXEMPLE,DC=fr'
OU_VIP = 'OU=VIPs,DC=EXEMPLE,DC=fr'
OU_VPN = 'OU=VPN,DC=EXEMPLE,DC=fr'
OU_DEPOTS = 'OU=Utilisateurs,DC=EXEMPLE,DC=fr'

SEARCH_BASES = [OU_CENTRALE, OU_EXT, OU_VIP, OU_VPN, OU_DEPOTS]

# Paramètres Seatable
api_token = context.api_token
server_url = context.server_url
TABLE_NAME = 'LDAP'

base = Base(api_token, server_url)
base.auth()

# Connexion à l'Active Directory
server = Server(LDAP_SERVER, get_info=ALL)
conn = Connection(server, user=LDAP_USER, password=LDAP_PASSWORD)
conn.bind()

# Récupération de toutes les lignes de la table Seatable
all_rows = base.list_rows(TABLE_NAME)

# Compteur pour les requêtes LDAP
request_count = 0

# Mise à jour de la table Seatable pour chaque OU
for search_base in SEARCH_BASES:
    conn.search(search_base, '(objectClass=person)', attributes=['cn', 'sAMAccountName', 'mail'])
    
    for entry in conn.entries:
        cn = entry.cn.value
        sam_account_name = entry.sAMAccountName.value
        mail = entry.mail.value if entry.mail else ''

        # Recherche de la ligne correspondante dans Seatable
        filtered_rows = [row for row in all_rows if row.get('cn') == cn]
        if filtered_rows:
            row_id = filtered_rows[0]['_id']
            update_data = {'sAMAccountName': sam_account_name, 'mail': mail}
            base.update_row(TABLE_NAME, row_id, update_data)
        else:
            # Ajouter une nouvelle ligne si l'utilisateur n'est pas trouvé
            new_data = {'cn': cn, 'sAMAccountName': sam_account_name, 'mail': mail}
            base.append_row(TABLE_NAME, new_data)

        # Incrémenter le compteur de requêtes
        request_count += 1

        # Pause après 900 requêtes pour éviter d'atteindre la limite
        if request_count % 900 == 0:
            time.sleep(60)  # Pause d'une minute

conn.unbind()

Sorry for french comment ^^

[cdb]

Thanks for this example. I will add ldap3 to the python runner image with the next update.
It is ok, if I take this example and add it to Examples - SeaTable Developer Manual?