1 Topic by jeff.stelmach

  • jeff.stelmach
  • Member
  • Offline
  • Registered: 2018-02-27
  • Posts: 5

Topic: bulk user add via api python

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release):  0.9.9 MySQL Backend
- Deployed with iRedMail Easy or the downloadable installer?
- Linux/BSD distribution name and version: Centos7
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): nginx
- Manage mail accounts with iRedAdmin-Pro? - yes

====




Hello everyone, I am having a tough time trying to run a bulk user import from either a csv or json formatted file via api.  I am able to run the below python code for creating a single user successfully using POST then running the next 2 PUT for updating user and mailing list  but I would like to run the same code as below by passing variables from a csv file for example $emailaddress $password $employeeid and have the code loop theough the csv file creating and updating all of the users.


I am by no means a coder any help would be appreciated


Just to note, I am aware that I can run a script locally that will inject the users into SQL database but for several reasons this is the way I am tasked to solve this problem.



below is my code



Thanks






import sys
import requests

url = 'https://domain.com/iredadmin/api'

# Admin email address and password.
admin = 'postmaster@domain.com'
pw = 'domainpassword'

# Login
r = requests.post(url + '/login', data={'username': admin,
                                        'password': pw})

# Get returned JSON data
data = r.json()
if not data['_success']:
    sys.exit('Login failed')

cookies = r.cookies


# Create user: <user>@domain.com
#  (url + api endpoint , data = parameters
# hard coded preferred language & mail quota

requests.post(url + '/user/test505@domain.com',
              cookies=cookies,
              data={'cn': 'My Name',
                    'password': '1@Password12345',
                    'preferredLanguage': 'en_US',
                    'quota': '5120'})


# Update user: <user>@containergraphics.com

requests.put(url + '/user/test505@domain.com',
             cookies=cookies,
             data={'cn': 'My New Name',
                   'employeeid' : 'testID',
                   'language': 'en_US',
                   })



requests.put(url + '/ml/testmailing@domain.com',
             cookies=cookies,
             data= {'add_subscribers': 'test505@domain.com',
                    })

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,788

Re: bulk user add via api python

Dear Jeff,

You can use a "for" loop in shell scripting to read content from CSV file, then either use the shell or python script to import it.

Btw, could you show us few sample lines of this CSV file? You should replace sensitive info before pasting.

3 Reply by jeff.stelmach

  • jeff.stelmach
  • Member
  • Offline
  • Registered: 2018-02-27
  • Posts: 5

Re: bulk user add via api python

ZhangHuangbin wrote:

Dear Jeff,

You can use a "for" loop in shell scripting to read content from CSV file, then either use the shell or python script to import it.

Btw, could you show us few sample lines of this CSV file? You should replace sensitive info before pasting.



Zhang,



The CSV file is formatted as such just email address, password, EmployeeID.  What I would like to do is assign a variable to each of the items and then pass them through the POST and PUT commands in the python script... that is the part I am having trouble with


testuser507@domain.com test504123 testID
testuser508@domain.com test504124 testID
testuser509@domain.com test504125 testID
testuser509@domain.com test504126 testID

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,788

Re: bulk user add via api python

Try this Python code:

with open('file.csv', 'r') as f:
    for line in f.readlines():
        (email, password, user_id) = line.split()
        print('{} {} {}'.format(email, password, user_id))

5 Reply by jeff.stelmach

  • jeff.stelmach
  • Member
  • Offline
  • Registered: 2018-02-27
  • Posts: 5

Re: bulk user add via api python

Zhang,


thanks for the suggestion, I ended up having to do this a different way w/ the help of my coworker.  He suggested I pull directly from old mail servers sql database and import into the script and cURL to  API... 2 reasons we went this way

1)the built in python sql script was was automatically creating an alias for every user... although deleting the alias in the DB takes less than a second in workbench this way requires no extra tweaking once the user is added.
2) never a good idea of having an unencrypted csv of username / email address / password lying around on anyones machine


Below I have included the code for the database pull and the working csv python script as well maybe this will save someone some time down the road or you can use it as a built in option in future releases



Thanks again



Jeff
-----------------------------------------------------------------------------------------------------------------------------------------------
CSV


import csv

csvFile = input("Enter CSV filename: ")

input_file = csv.DictReader(open(csvFile))


def add_email(address, password, plant, full_name):
    print("Adding email for: " + full_name + " - "
          + address + "(" + password + ") for plant " + plant)


for row in input_file:
    add_email(str(row["email address"]), str(row["password"]),
str(row["plant number"]), "Full Name Goes Here")


---------------------------------------------------------------------------------------------------------------------------------------
SQL python script  (must use .env file and python sql connector)




import mysql.connector
import os
from dotenv import load_dotenv, find_dotenv
load_dotenv(find_dotenv())

connection_args = {
    'host': os.getenv("DBHOST"),
    'user': os.getenv("DBUSER"),
    'database': os.getenv("DBDB"),
    'password': os.getenv("DBPASSWORD")
}

db_conn = mysql.connector.connect(**connection_args)
db_cursor = db_conn.cursor()
# db_cursor.execute("SELECT * FROM mail.mailbox")
db_cursor.execute("SELECT * FROM mail.mailbox LIMIT 10")
result = db_cursor.fetchall()


def add_email(address, password, plant, full_name):
    print("Adding email for: " + full_name + " - "
          + address + "(" + password + ") for plant " + plant)


for row in result:
    add_email(str(row[3]), str(row[1]),
str(row[10]), str(row[4]))



ZhangHuangbin wrote:

Try this Python code:

with open('file.csv', 'r') as f:
    for line in f.readlines():
        (email, password, user_id) = line.split()
        print('{} {} {}'.format(email, password, user_id))





---------------------------------------------------------------------------
.env file



DBUSER=username
DBHOST=host
DBPASSWORD=password
DBDB=mail
MAIL_USER=user
MAIL_PASSWORD=password

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,788

Re: bulk user add via api python

Thanks for sharing. smile