1

This 2nd format_soql is not working. I have tried replacing the single quotes to double quotes as per the format defined here: https://github.com/simple-salesforce/simple-salesforce#queries

query1 = "Select id,ParentId__c,Attachment_Id__c,ParentId_Target__c,ParentName_Target__c ,Drive_Id__c,Attachment_Type__c from Attachments__c WHERE Attachment_Type__c != ''"

key_result = sf.query(query1)
#get values from key documents
key_docs = key_result.get('records', {})
#create map
key_docMap = {}
for key_doc in key_docs:
    #key_docMap.append(key_doc.get('Attachment_Id__c'),key_doc.get('ParentId_Target__c')+"_"+key_doc.get('Attachment_Type__c')+"_")
    key_docMap[key_doc.get('Attachment_Id__c')] = key_doc.get('ParentId_Target__c')+"_"+key_doc.get('Attachment_Type__c')+"_"

print(json.dumps(list(key_docMap.keys())))
result = sf.query(format_soql("SELECT Id, ParentId, Name, Body FROM Attachment WHERE Id IN {keys} limit 2", keys = json.dumps(list(key_docMap.keys()))))

I am new to python if anyone can point me in the right direction that will be great.

Update: full code

from simple_salesforce import Salesforce
from simple_salesforce import format_soql
import requests
import logging
import argparse
import os
import sys
import codecs
import json

ACCOUNT_TO_FILE_CSV = './attachments.csv'

def download_attachments(args):
    session = requests.Session()
    try:
        sf = Salesforce(username=args.get('user'), 
                        password=args.get('passwd'), 
                        security_token=args.get('token'),
                        domain='test', 
                        session=session)
    except Exception as e:
        logging.error("Failed to connect SFDC: %s", str(e))
        return

    auth_id = 'Bearer ' + sf.session_id
    req_headers = {'Authorization': auth_id}

    query1 = "Select id,ParentId__c,Attachment_Id__c,ParentId_Target__c,ParentName_Target__c ,Drive_Id__c,Attachment_Type__c from Attachments__c WHERE Attachment_Type__c != ''"
    #query = "SELECT Id, ParentId, Name, Body FROM Attachment WHERE bodylength > 9000000"
    
    key_result = sf.query(query1)
    #get values from key documents
    key_docs = key_result.get('records', {})
    #create map
    key_docMap = {}
    for key_doc in key_docs:
        key_docMap[key_doc.get('Attachment_Id__c')] = key_doc.get('ParentId_Target__c')+"_"+key_doc.get('Attachment_Type__c')+"_"

    print(list(key_docMap.keys()))
    result = sf.query(format_soql("SELECT Id, ParentId, Name, Body FROM Attachment WHERE Id IN {keys} limit 2", keys = key_docMap.keys()))
    total_records = result.get('totalSize', 0)
    if not total_records:
        logging.info("No attachments found")
        return

    logging.debug("Starting to download %d attachments", total_records)
    
    acc_to_file = []    
    ignore_words = map(str.lower, args.get('ignore', []))
    storage_dir = args.get('storage')
    sf_pod = sf.base_url.replace("https://", "").split('.salesforce.com')[0]

    records = result.get('records', {})
    for record in records:
        body_uri = record.get('Body')
        if not body_uri:
            logging.warning("No body URI for file id %s", record.get('Id', ''))
            continue

        remote_file = record.get('Name')
        remote_file_lower = remote_file.lower()
        if any(w in remote_file_lower for w in ignore_words):
            logging.info("File %s contains a word to ignore", remote_file)
            continue

        remote_path = "https://{0}.salesforce.com{1}".format(sf_pod, body_uri)
        #local_file = '%s_%s' % (record.get('Id'), remote_file)
        local_file = '%s_%s' % (record.get('ParentId'), remote_file)
        local_path = os.path.join(storage_dir, local_file)
        
        logging.info("Downloading %s to %s", remote_file, local_path)
        logging.debug("Remote URL: %s", remote_path)
        
        resp = session.get(remote_path, headers=req_headers)
        if resp.status_code != 200:
            logging.error("Download failed [%d]", resp.status_code)
            continue

        with open(local_path, 'wb') as out_file:
            out_file.write(resp.content)

        logging.debug("Account ID: %s", record.get('ParentId'))
        acc_to_file.append((record.get('ParentId'), local_file))

    with codecs.open(ACCOUNT_TO_FILE_CSV, 'wb', 'utf-16') as csv_file:
        csv_file.write('ParentId,FileName\n')
        csv_file.write('\n'.join('"%s","%s"' % l for l in acc_to_file))

if __name__ == "__main__":
    cli_parser = argparse.ArgumentParser(description='SFDC Attachments Downloader')
    cli_parser.add_argument('-u', 
                            '--user', 
                            help='SFDC username', 
                            required=True)
    cli_parser.add_argument('-p', 
                            '--passwd', 
                            help='SFDC password', 
                            required=True)
    cli_parser.add_argument('-t', 
                            '--token', 
                            help='SFDC security token', 
                            required=True)
    cli_parser.add_argument('-s', 
                            '--storage', 
                            help='Path to store attachments', 
                            required=True)
    cli_parser.add_argument('--account-only', 
                            action='store_true',
                            help='Download Account attachments only')
    cli_parser.add_argument('--ignore',
                            nargs='*',
                            metavar='word',
                            default=[],
                            help='Ignore filenames containing words')
                            
    args = cli_parser.parse_args()
    if any(v is None for v in vars(args).values()):
        cli_parser.print_help()
        sys.exit(1)

    if not os.path.exists(args.storage):
        print("ERROR: Storage path doesn't exist")
        sys.exit(1)

    if not os.path.isdir(args.storage):
        print("ERROR: Storage path must be a directory")
        sys.exit(1)

    print("Starting downloader...")

    logging.basicConfig(level=logging.DEBUG, 
                        format='%(asctime)s - %(levelname)s - %(message)s',
                        filename='attachments_downloader.log',
                        filemode='w')

    download_attachments(vars(args))

    print("Done.")

Update Error:

Traceback (most recent call last):   File "crs_attachments.py", line 136, in <module>
    download_attachments(vars(args))   File "crs_attachments.py", line 40, in download_attachments
    result = sf.query(format_soql("SELECT Id, ParentId, Name, Body FROM Attachment WHERE Id IN {keys} limit 2", keys = key_docMap.keys()))   File "/home/sumit/pythonsf/venv3/lib/python3.8/site-packages/simple_salesforce/format.py", line 45, in format_soql
    return SoqlFormatter().vformat(query, args, kwargs)   File "/usr/lib/python3.8/string.py", line 167, in vformat
    result, _ = self._vformat(format_string, args, kwargs, used_args, 2)   File "/usr/lib/python3.8/string.py", line 220, in _vformat
    result.append(self.format_field(obj, format_spec))   File "/home/sumit/pythonsf/venv3/lib/python3.8/site-packages/simple_salesforce/format.py", line 30, in format_field
    return quote_soql_value(value)   File "/home/sumit/pythonsf/venv3/lib/python3.8/site-packages/simple_salesforce/format.py", line 73, in quote_soql_value
    raise ValueError('unquotable value type') ValueError: unquotable value type
0

1 Answer 1

1

From the simple-salesforce doc, an example of format_soql with binding of a list looks like this:

sf.query(format_soql("SELECT Id, Email FROM Contact WHERE LastName IN {names}", names=["Smith", "Jones"]))

The value of names in the example a list of strings. In your case the list you want is a list of keys in your dict. Adapting to your code:

result = sf.query(format_soql("SELECT Id, ParentId, Name, Body FROM Attachment WHERE Id IN {keys} limit 2", keys = list(key_docMap.keys())))

For Python 3.5 or later, a list of dict keys can be had via an unpacking operator:

result = sf.query(format_soql("SELECT Id, ParentId, Name, Body FROM Attachment WHERE Id IN {keys} limit 2", keys = [*key_docMap])
5
  • That was my first try it didnt work.
    – summer
    Oct 30, 2020 at 16:28
  • @summer Your list might be empty but syntactically this is correct. Please edit your question and provide more of a Minimum, Reproducible Example. If you think of your question as a unit test, a complete sketch would include the setup of the test, the test itself and expected vs observed result.
    – identigral
    Oct 30, 2020 at 16:31
  • thanks for replying. I have added the whole code. I can see values in the print(list(key_docMap.keys())) so the list is not empty.
    – summer
    Oct 30, 2020 at 17:25
  • I have added the error now. I am not sure what is wrong
    – summer
    Oct 30, 2020 at 18:52
  • @summer See update. Your original attempt was pretty close.
    – identigral
    Oct 30, 2020 at 20:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .