Import data from Salesforce using python

Question

I am trying to pull data from Salesforce to python. I am able to connect to Salesforce and pull the data as well in Python but the problem is that Python is giving me output in JSON format which I don't want.

I need help to convert JSON format in dataframe.

Here is my code:

#pip install salesforce_reporting
from simple_salesforce import Salesforce
from salesforce_reporting import Connection,ReportParser,MatrixParser
#import psycopg2 as psy


print("start connecting with salesforce database")
sf = Salesforce(username='XXXXX',password='XXXX',instance_url = 'https://na97.salesforce.com/',sandbox=0, security_token='')
print("connected with salesforce database")

print("start connecting with salesforce database")
sf = Salesforce(username='XXXXX',password='XXXX',instance_url = 'https://na97.salesforce.com/',sandbox=0, security_token='')
print("connected with salesforce database")
print("pull data from salesforce database object")
pd = sf.query("select name from Account where name ='New Technology Sweden AB'") # if we use sf.query_all then will can get all records from spefific objects
#print(pd)

Here is my output:.

# to read data
pd

OrderedDict([('totalSize', 2),
             ('done', True),
             ('records',
              [OrderedDict([('attributes',
                             OrderedDict([('type', 'Account'),
                                          ('url',
                                           '/services/data/v38.0/sobjects/Account/0018000000UG7VaAAL')])),
                            ('Name', 'New Technology Sweden AB')]),
               OrderedDict([('attributes',
                             OrderedDict([('type', 'Account'),
                                          ('url',
                                           '/services/data/v38.0/sobjects/Account/0018000000vhiN4AAI')])),
                            ('Name', 'New Technology Sweden AB')])])])

Have you looked at [this?](https://stackoverflow.com/questions/21104592/json-to-pandas-dataframe) — Jamie, Feb 11 '20 at 09:53
Does this answer your question? [JSON to pandas DataFrame](https://stackoverflow.com/questions/21104592/json-to-pandas-dataframe) — Tobiah Rex, Feb 11 '20 at 16:02

score 0 · Answer 1 · answered Feb 11 '20 at 14:18

"output in JSON format which I don't want"

You need to help pure Salesforce guys here who have never written a piece of Python in their life. What formats can "dataframe" accept?

Salesforce REST API (used by simple_salesforce) supports JSON and XML. You specify XML by simply sending a HTTP "Accept" header. If you can work with XML then maybe you can open up the library's source code or maybe there's some config option to specify custom headers... Worst case you can craft the GET message yourself, it's not rocket science once you're logged in, GET to right address + Authorization Bearer <session id here> + that optional Accept application/xml

If you need CSV format... The library says it also supports SF Bulk API. That one can do JSON/XML/CSV although you specify it slightly different. Again, maybe you can open up the simple salesforce's source code and mess around a bit, maybe you'll write the request yourself. Training might help: https://trailhead.salesforce.com/en/content/learn/modules/api_basics/api_basics_bulk

Import data from Salesforce using python

1 Answers1