I have a malformed query problem when using simple salesforce for my AWS python Lambda. The query uses the AccountContactRelation object.
When an event is dropped into an AWS S3 bucket for taxonomy CSV files that contain a mix of lead and contact records, the extraction integration is supposed to upload those records into Salesforce as either new contacts and leads or to update the existing Salesforce record. Here is the process that the integration uses:
- Verify if the lead or contact extracted has its SF ID or an email
- If the record does not contain those 2 attributes (EMail, SF ID) then do an internal query.
The query code for leads in Simple Salesforce works well:
def find_leads(self, info):
print('In find_leads')
try:
if 'FirstName' in info and 'LastName' in info and 'Company' in info and 'State' in info and 'City' in info and info['FirstName'] is not None and info['FirstName'] != '' and info['LastName'] is not None and info['LastName'] != '' and info['Company'] is not None and info['Company'] != '' and info['State'] is not None and info['State'] != '' and info['City'] is not None and info['City'] != '':
self.query = self.__sf.query_all("SELECT Id FROM Lead WHERE FirstName = '" + self.adding_escape_char(info['FirstName']) + "' AND LastName = '"+ self.adding_escape_char(info['LastName']) + "' AND Company LIKE'" + self.adding_escape_char(info['Company']) + "%' AND State = '" + self.adding_escape_char(info['State']) + "' AND City = '" + self.adding_escape_char(info['City']) + "' AND IsConverted = FALSE limit 1" )
print(self.query)
if self.query['records'] == []:
return('New', '-')
else:
print('Number of Matching records found: ',len(self.query['records']))
return ('Lead', json.loads(json.dumps(self.query['records']))[0]['Id'])
else:
return('New', '-')
except Exception as e:
print(e)
print('in find_leads Exception')
self.__logging.unprocessed_records_logging('Fetch','Lead', e)
However the Contact query in Simple Salesforce either comes up empty (but there is a value) or I get a Malformed query. However, I have tested using other tools to show that the query does return a result.
def find_contact(self, info):
print('<< In find_contact >>')
print(info['FirstName'])
print(info['LastName'])
print(info['Company'])
print(info['State'])
print(info['City'])
try:
if 'FirstName' in info and 'LastName' in info and 'Company' in info and 'State' in info and 'City' in info and info['FirstName'] is not None and info['FirstName'] != '' and info['LastName'] is not None and info['LastName'] != '' and info['Company'] is not None and info['Company'] != '' and info['State'] is not None and info['State'] != '' and info['City'] is not None and info['City'] != '':
self.__sf.query_all("SELECT ContactId FROM AccountContactRelation WHERE Account_Status__c IN ('Prospect' , 'Terminated') AND MailingState = '" + self.adding_escape_char(info['State']) + "' AND MailingCity = '"+ self.adding_escape_char(info['City']) + "' AND Name LIKE '" + self.adding_escape_char(info['Company']) + "%' limit 1" )
print(self.query)
if self.query['records'] == []:
print('<< QUERY DIED>>')
return('New', '-')
else:
#print('Number of Matching records found: ', json.loads(json.dumps(query['records']))[0]['Id'])
#return ('Contact',json.loads(json.dumps(self.query['records']))[0]['Contact']['Id'])
return ('Lead', json.loads(json.dumps(self.query['records']))[0]['Id'])
else:
return('New', '-')
except Exception as e:
print(e)
print('in find_contact Exception')
self.__logging.unprocessed_records_logging('Fetch','Contact', e)
return('New', '-')
Here is the AWS Cloud Watch Log that shows the empty dictionary
Note: When I run the queries using either Workbench or Developer Console, the query for the contact works well and returns a value. Here are the following screen scrapes for both workbench and Dev Console. I can't figure out what I am missing. Thanks for your help.
