CODEWITHSUNDEEP
pythontwilio
pzale
pzale

Reputation: 11

twilio export message logs to csv via python

I'm trying to retrieve a month of message logs from twilio using python. The code below works fine when retrieving a day or two but is extremely slow when expanding the date range. Is there a better way to do this?

import pandas as pd
from datetime import date
from twilio.rest import Client

# Your Account Sid and Auth Token from twilio.com/user/account
account_sid = "####"
auth_token = "####"
client = Client(account_sid, auth_token)


messages = client.messages.list(
    date_sent_after=date(2017, 3, 1),
    date_sent_before=date(2017, 3, 2)
)


d = []
for message in messages:
   d.append((message.from_, message.to, message.body, message.status, 
   message.date_sent, message.price))

print(d)

df = pd.DataFrame(d, columns=('From', 'To', 'Body', 'Status', 'Sent Date', 'Price'))


print(df.dtypes)

df.to_csv('export.csv', index=False, encoding='utf-8')

Upvotes: 1

Views: 1956

Answers (3)

Jason F
Jason F

Reputation: 357

If you came here looking on how to pull call logs (instead of message logs), here's an example. You'll notice this is very close to the answers above (and was in fact derived from them). 

import csv, json
import requests
account_sid = ''
auth_token = ''

results_per_page = 1000
num_pages = 1000
start_date = '2010-04-01'
base_url = 'https://api.twilio.com'
initial_page = '/' + start_date + '/Accounts/' + account_sid + '/Calls.json?PageSize=' + str(results_per_page)
response = requests.get(base_url + initial_page, auth=(account_sid, auth_token)).json()
next_page = response['next_page_uri']

page = 0
data = response['calls']

# print response['previous_page_uri']
# print response
while page < num_pages and next_page is not None:
    print('On page: ' + str(page))
    response = requests.get(base_url + next_page, auth=(account_sid, auth_token)).json()
    next_page = response['next_page_uri']
    data = data + response['calls']
    page += 1

csv_file = open('twilio-call-logs.csv', 'w')
csvwriter = csv.writer(csv_file)

count = 0
for message in data:
    if count == 0:
        header = message.keys()
        csvwriter.writerow(header)
        count += 1

    csvwriter.writerow(message.values())

csv_file.close()
print('Done!')

Upvotes: 1

videoking
videoking

Reputation: 51

Twilio's "export to CSV" option on their logs page simply won't be able to export 100s of thousands of results. In order to do this you will need your own script that paginates the results from Twilio's API and generates one CSV.

Here's how I recommend doing that:

import csv
import requests

account_sid = ""
auth_pass = ""

results_per_page = 1000
num_pages = 500
start_date = "2010-04-01"

base_url = "https://api.twilio.com"
initial_page = "/" + start_date + "/Accounts/" + account_sid + "/Messages.json?PageSize=" + str(results_per_page)

response = requests.get(base_url + initial_page, auth=(account_sid, auth_pass)).json()
next_page = response['next_page_uri']

page = 0
data = response['messages']

# print response['previous_page_uri']
# print response

if not next_page == None:
  while page < num_pages:
    response = requests.get(base_url + next_page, auth=(account_sid, auth_pass)).json()

    next_page = response['next_page_uri']

    print "On page: " + str(page)

    data = data + response['messages']
    page += 1

    if next_page == None:
      print "No more pages"
      break

csv_file = open('export.csv', 'w')
csvwriter = csv.writer(csv_file)

count = 0

for message in data:

  if count == 0:
    header = message.keys()
    csvwriter.writerow(header)
    count += 1

  #only include outbound sms because inbound ascii/emojis throws csv writer errors
  if message.values()[2] == "outbound-api":
    csvwriter.writerow(message.values())

csv_file.close()

print "Done!"

You will need to input account_sid and auth_pass with your credentials. Check Twilio API Explorer if unsure what these are. Create a twilio.py file with this code in it and then to run the script in mac terminal: python twilio.py

One known issue that I ran across: if inbound messages contain emojis or strange characters the csv writing throws an error. Fixed this by filtering to only write outbound messages.

If you'd like to improve this script for others here's a link to the github page: https://github.com/benjitastic/twilio-export-messages

Upvotes: 4

philnash
philnash

Reputation: 73055

Twilio developer evangelist here.

I have a little trick for you here. You can actually get CSVs straight from Twilio. All you need to do is construct the URL for your data and then add .csv to the end.

Edit

To construct the URL for your messages you should check out the Messages resource.

The list is at the URL:

https://api.twilio.com/2010-04-01/Accounts/{AccountSid}/Messages

and to turn it to a CSV, just add .csv

https://api.twilio.com/2010-04-01/Accounts/{AccountSid}/Messages.csv

You will need to request the URL with your account SID and auth token as authentication and you can add any query parameters to the end of the URL that you like too.

To do date ranges, like your example of after 01/03/2017 and before 02/03/2017 your URL would look like this:

https://api.twilio.com/2010-04-01/Accounts/{AccountSid}/Messages.csv?DateSent>=2017-03-01&DateSent<=2017-03-02

Check out the example of listing messages with curl to see this in more detail.

Upvotes: 0

Related Questions