Page MenuHomePhabricator
Paste P4254

make-toollabs-mailing-list.py
ActivePublic

Authored by bd808 on Oct 17 2016, 7:55 PM.
#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
# Generate a list of email addresses for all Tool Labs users who have
# validated their email address via Wikitech and who have not opted out of
# email contact.
import ldap
import subprocess
l = ldap.open('ldap-labs.eqiad.wikimedia.org')
l.protocol_version = ldap.VERSION3
baseDN = 'dc=wikimedia,dc=org'
searchScope = ldap.SCOPE_SUBTREE
# Get a list of all Tool Labs project members
res = l.search_s(baseDN, searchScope, 'cn=project-tools', ['member'])
members = res[0][1]['member']
# Get the LDAP email addresses for all Tool Labs project members
ldap_emails = []
for member in members:
res = l.search_s(baseDN, searchScope, member.split(',')[0], ['mail'])
if 'mail' in res[0][1]:
ldap_emails.append(res[0][1]['mail'][0])
ldap_emails = set(ldap_emails)
# Get the email addresses for all Wikitech accounts that have validated their
# address at some point and who have not opt-ed out of email contact by other
# users.
mysql_emails = subprocess.check_output(
'/usr/local/bin/sql labswiki < valid-emails.sql',
shell=True
).strip().split('\n')[1:]
mysql_emails = set(mysql_emails)
# Find the intersection of the two lists
emails = [mail for mail in ldap_emails if mail in mysql_emails]
emails.sort()
for email in emails:
print email

Event Timeline

valid-emails.sql
SELECT user_email
FROM user
WHERE user_email <> ''
  AND user_email_authenticated IS NOT NULL
  AND user_id NOT IN (
      SELECT up_user
      FROM user_properties
      WHERE up_property='disablemail'
        AND up_value=1
);