CODEWITHSUNDEEP
pythonpython-3.xpandas
r0xette
r0xette

Reputation: 908

sfdc select result in pandas dataframe

How can I insert the output of query_result in pandas dataframe? The query result first gets 500 records and using QueryMore the remaining records are pulled. I am unable to push everything in df along with column names. 

#!/usr/bin/env python3

import beatbox
import sqlalchemy

engine_str = 'mysql+mysqlconnector://db-user:db-pass@localhost/db-name'
engine = sqlalchemy.create_engine(engine_str, echo=False, encoding='utf-8')
connection = engine.connect()

sf = beatbox._tPartnerNS
service = beatbox.Client()
def sync_user(objectSOQL):
    service.login('sfdc-username', 'sfdc-creds')
    query_result = service.query(objectSOQL)
    while True:
        for row in query_result[sf.records:]:
            SQL_query = 'INSERT INTO user(' \
                        'id, ' \
                        'name, ' \
                        'department, ' \
                        'sales_team) ' \
                        'VALUES(' \
                        '\"{}\",\"{}\",\"{}\",\"{}\"' \
                        ') ON DUPLICATE KEY UPDATE '\
                        'name = VALUES(name), '\
                        'department = VALUES(department), '\
                        'sales_team = VALUES(sales_team) '\
                        .format(
                        row[2],
                        row[3],
                        row[4],
                        row[5]
                        )
            # SQL_query = SQL_query.replace('\"None\"', 'NULL')

            try:
                connection.execute(SQL_query)
            except Exception as e:
                print(id, e)

        if str(query_result[sf.done]) == 'true':
            break
        query_result = service.queryMore(str(query_result[sf.queryLocator]))

if __name__ == "__main__":
    SOQL = 'SELECT id, Name, Department, Sales_Team__c FROM User'
    sync_user(SOQL)

Upvotes: 0

Views: 235

Answers (1)

Kiv
Kiv

Reputation: 32718

One way is to make a list and append a DataFrame to it each time you go through the loop and have new results. Then at the end you can use pandas.concat to combine all the individual DataFrames into one.

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html#pandas-concat

Upvotes: 1

Related Questions