def __init__(self, profileName=None):
'''
Initialize a SQLForce session optionally connecting to salesforce.
profileName -- if provided, the name of connection parameters that have been previously registered with this module.
'''
self._session = SQLForceSession()
if (profileName):
self.connectProfile(profileName)
def __init__(self, profileName=None):
'''
Initialize a SQLForce session optionally connecting to salesforce.
profileName -- if provided, the name of connection parameters that have been previously registered with this module.
'''
self._session = SQLForceSession()
if(profileName):
self.connectProfile(profileName)
class Session:
'''
Manage a connection to a single SalesForce instance.
'''
def __init__(self, profileName=None):
'''
Initialize a SQLForce session optionally connecting to salesforce.
profileName -- if provided, the name of connection parameters that have been previously registered with this module.
'''
self._session = SQLForceSession()
if (profileName):
self.connectProfile(profileName)
def connectProfile(self, profileName):
'''
Connect to a Salesforce session using a pre-defined connection profile.
If the connection is successful then the function will return
None. Otherwise the reason the connection failed will be returned.
'''
self.runCommands("CONNECT PROFILE " + profileName)
return self.getenv("STATUS")
def connect(self, sessionType, username, password, securityToken=""):
'''
Connect to a Salesforce session.
If the connection is successful then the function will return
None. Otherwise the reason the connection failed will be returned.
'''
self._session.runCommands("CONNECT " \
+ sessionType + \
" " + username \
+ " " + password \
+ " " + securityToken)
return self.getenv("STATUS")
def getenv(self, token):
'''
Get a SQLForce environment variable.
The most useful variables include:
ROW_COUNT -- number of rows returned/effect by the most recent SOQL statement
STATUS -- None if the most recent command worked, otherwise the error from the most recent command.
'''
return self._session.getenv(token)
class SelectHandler(ContentHandler):
def __init__(self, rowCallback):
self.callback = rowCallback
self.lastData = ""
self.row = []
def characters(self, data):
self.lastData += data
def endElement(self, name):
if "C" == name:
self.row.append(self.lastData)
self.lastData = ""
if "Row" == name:
self.callback(self.row)
self.row = []
def select2(self, sql, rowCallback):
'''
Select rows calling a user supplied method for each row.
The callback method must have a single argument -- a list
of values -- one for each column.
Returns the number of rows found.
'''
tmpFile = File.createTempFile("SQLForceSelect", ".tmp")
tmpFile.deleteOnExit()
sql = sql.lstrip()
selectxPattern = re.compile(r'^SELECTX', re.IGNORECASE)
if not selectxPattern.match(sql):
sql = "SELECTX" + sql[6:]
self._session.runCommands(sql + " OUTPUT '" \
+ tmpFile.getAbsolutePath() + "'")
fSelect = open(tmpFile.getAbsolutePath(), "r")
parser = make_parser()
handler = self.SelectHandler(rowCallback)
parser.setContentHandler(handler)
parser.parse(fSelect)
fSelect.close()
tmpFile.delete()
return self.getenv("ROW_COUNT")
def select(self, sql):
'''
Return all rows found by a SELECT query as a list of lists.
'''
results = []
self.select2(sql, lambda row: results.append(row))
return results
def selectRecords2(self, sql, callback):
'''
Select records calling a user supplied callback for each row (each record contains attributes
for each column in the select statement).
Example: SELECT id, name, Account.name From Contact
will return records with attributes:
+ id
+ name
+ Account.name
'''
parser = SelectParser()
parser.parse(sql)
table = parser.tableName
columns = parser.columnNames
def myCallback(row):
record = _SQLForceRecord(table, columns)
n = 0
for value in row:
record.setValue(columns[n], value)
n = n + 1
callback(record)
return self.select2(sql, myCallback)
def selectRecords(self, sql):
'''
Select records where each column in the sql is returned as an attribute of an object.
Example: SELECT id, name, Account.name From Contact
will return records with attributes:
+ id
+ name
+ Account.name
Return a list of the records found or an empty list if no records are found.
'''
records = []
def myCallback(record):
records.append(record)
self.selectRecords2(sql, myCallback)
return records
def runCommands(self, cmd):
'''
Run one or more SQLForce commands returning the status of the last
command.
DELETE, UPDATE, and INSERT commands are generally run using this method.
Example:
runCommands( "DELETE FROM Contact WHERE LastName='PleaseDeleteMe' )
'''
self._session.runCommands(cmd)
return self.getenv("STATUS")
def _valueListToString(self, list, delim=",", quote="'"):
'''
Convert a list of values to a string of delimiter separated tokens.
Recognized an empty value as the special value null.
'''
result = None
qq = None
if None != quote and len(quote) > 0:
qq = quote
for value in list:
if None == value:
quotedValue = "null"
else:
vv = str(value)
if None != qq:
vv = vv.replace(qq, "\\")
quotedValue = qq + vv.replace(qq, "\\") + qq
else:
quotedValue = vv
if None == result:
result = quotedValue
else:
result += delim + quotedValue
return result
def insert(self, table, columns, data):
'''
Insert a set of rows into a table, building the appropriate SQLForce command from the supplied data.
The SQL force command executed by this method will be the same as calling runCommands() with:
INSERT table( ','.join(columns) ) VALUES( ','.join(data[0] ), (','.join(data[0])), etc.
or more concretely:
INSERT Contact(FirstName, LastName) VALUE('Gregory', 'Smith'), ('Mary', 'Smith'), etc.
table -- table to receive the rows
columns -- name of the columns to fill in with data.
data -- one row (a list) for each new row to create. The column order must match the order on the arg "columns"
return a list of the ids inserted into Salesforce
'''
insertPrefix = "INSERT INTO " + table + "(" + ','.join(columns) + ")"
nPending = 0
sql = insertPrefix
for row in data:
if 0 == nPending:
sql += " VALUES"
else:
sql += ","
sql += "(" + self._valueListToString(row, ",", '"') + ")"
nPending += 1
self.runCommands(sql)
return self._session.getenv("INSERT_IDS").split(",")
def delete(self, table, idsList):
'''
Delete a set of records from a table given a list of unique record ids.
'''
sql = "DELETE FROM " + table + " WHERE ID IN (" + self._valueListToString(
idsList) + ")"
return self.runCommands(sql)
def update(self, table, columns, data):
'''
Update a set of records where the id of each record is known.
table -- table to update
columns -- name of columns to update
data -- one row for each update where row[0]=id of row to update and remaining
elements contain data for each column.
'''
updatePrefix = "UPDATE " + table + "(" + ','.join(columns) + ") "
nPending = 0
sql = updatePrefix
for row in data:
if nPending > 0:
sql += ","
sql += row[0] + "=(" + self._valueListToString(row[1:]) + ")"
nPending += 1
return self.runCommands(sql)
class Session:
'''
Manage a connection to a single SalesForce instance.
'''
def __init__(self, profileName=None):
'''
Initialize a SQLForce session optionally connecting to salesforce.
profileName -- if provided, the name of connection parameters that have been previously registered with this module.
'''
self._session = SQLForceSession()
if(profileName):
self.connectProfile(profileName)
def connectProfile(self, profileName):
'''
Connect to a Salesforce session using a pre-defined connection profile.
If the connection is successful then the function will return
None. Otherwise the reason the connection failed will be returned.
'''
self.runCommands("CONNECT PROFILE " + profileName)
return self.getenv("STATUS")
def connect(self, sessionType, username, password, securityToken = ""):
'''
Connect to a Salesforce session.
If the connection is successful then the function will return
None. Otherwise the reason the connection failed will be returned.
'''
self._session.runCommands("CONNECT " \
+ sessionType + \
" " + username \
+ " " + password \
+ " " + securityToken)
return self.getenv("STATUS")
def getenv(self, token):
'''
Get a SQLForce environment variable.
The most useful variables include:
ROW_COUNT -- number of rows returned/effect by the most recent SOQL statement
STATUS -- None if the most recent command worked, otherwise the error from the most recent command.
'''
return self._session.getenv(token)
class SelectHandler( ContentHandler ):
def __init__(self, rowCallback):
self.callback = rowCallback
self.lastData = ""
self.row = []
def characters(self, data):
self.lastData += data
def endElement(self, name):
if "C" == name:
self.row.append(self.lastData)
self.lastData = ""
if "Row" == name:
self.callback(self.row)
self.row = []
def select2(self, sql, rowCallback):
'''
Select rows calling a user supplied method for each row.
The callback method must have a single argument -- a list
of values -- one for each column.
Returns the number of rows found.
'''
tmpFile = File.createTempFile("SQLForceSelect", ".tmp")
tmpFile.deleteOnExit();
sql = sql.lstrip()
selectxPattern = re.compile(r'^SELECTX', re.IGNORECASE)
if not selectxPattern.match(sql):
sql = "SELECTX" + sql[6:]
self._session.runCommands(sql + " OUTPUT '" \
+ tmpFile.getAbsolutePath() + "'")
fSelect = open(tmpFile.getAbsolutePath(), "r");
parser = make_parser()
handler = self.SelectHandler(rowCallback)
parser.setContentHandler(handler)
parser.parse(fSelect)
fSelect.close()
tmpFile.delete();
return self.getenv("ROW_COUNT")
def select(self, sql):
'''
Return all rows found by a SELECT query as a list of lists.
'''
results = []
self.select2(sql, lambda row: results.append(row))
return results
def selectRecords2(self, sql, callback ):
'''
Select records calling a user supplied callback for each row (each record contains attributes
for each column in the select statement).
Example: SELECT id, name, Account.name From Contact
will return records with attributes:
+ id
+ name
+ Account.name
'''
parser = SelectParser()
parser.parse(sql)
table = parser.tableName
columns = parser.columnNames
def myCallback(row ):
record = _SQLForceRecord( table, columns )
n = 0
for value in row:
record.setValue( columns[n], value )
n = n + 1
callback(record)
return self.select2(sql, myCallback)
def selectRecords(self, sql ):
'''
Select records where each column in the sql is returned as an attribute of an object.
Example: SELECT id, name, Account.name From Contact
will return records with attributes:
+ id
+ name
+ Account.name
Return a list of the records found or an empty list if no records are found.
'''
records = []
def myCallback(record ):
records.append(record)
self.selectRecords2( sql, myCallback )
return records
def runCommands(self, cmd):
'''
Run one or more SQLForce commands returning the status of the last
command.
DELETE, UPDATE, and INSERT commands are generally run using this method.
Example:
runCommands( "DELETE FROM Contact WHERE LastName='PleaseDeleteMe' )
'''
self._session.runCommands(cmd)
return self.getenv("STATUS")
def _valueListToString(self, list, delim=",", quote="'"):
'''
Convert a list of values to a string of delimiter separated tokens.
Recognized an empty value as the special value null.
'''
result = None
qq = None
if None!=quote and len(quote)>0:
qq = quote
for value in list:
if None == value:
quotedValue = "null"
else:
vv = str(value)
if None != qq:
vv = vv.replace(qq, "\\")
quotedValue = qq + vv.replace(qq, "\\") + qq
else:
quotedValue = vv
if None == result:
result = quotedValue
else:
result += delim + quotedValue
return result
def insert(self, table, columns, data):
'''
Insert a set of rows into a table, building the appropriate SQLForce command from the supplied data.
The SQL force command executed by this method will be the same as calling runCommands() with:
INSERT table( ','.join(columns) ) VALUES( ','.join(data[0] ), (','.join(data[0])), etc.
or more concretely:
INSERT Contact(FirstName, LastName) VALUE('Gregory', 'Smith'), ('Mary', 'Smith'), etc.
table -- table to receive the rows
columns -- name of the columns to fill in with data.
data -- one row (a list) for each new row to create. The column order must match the order on the arg "columns"
return a list of the ids inserted into Salesforce
'''
insertPrefix = "INSERT INTO " + table + "(" + ','.join(columns) + ")"
nPending = 0;
sql = insertPrefix
for row in data:
if 0 == nPending:
sql += " VALUES"
else:
sql += ","
sql += "(" + self._valueListToString(row, ",", '"') + ")"
nPending += 1;
self.runCommands(sql)
return self._session.getenv("INSERT_IDS").split(",")
def delete(self, table, idsList ):
'''
Delete a set of records from a table given a list of unique record ids.
'''
sql = "DELETE FROM " + table + " WHERE ID IN (" + self._valueListToString(idsList) + ")"
return self.runCommands( sql )
def update(self, table, columns, data ):
'''
Update a set of records where the id of each record is known.
table -- table to update
columns -- name of columns to update
data -- one row for each update where row[0]=id of row to update and remaining
elements contain data for each column.
'''
updatePrefix = "UPDATE " + table + "(" + ','.join(columns) + ") "
nPending = 0
sql = updatePrefix
for row in data:
if nPending > 0:
sql += ","
sql += row[0] + "=(" + self._valueListToString(row[1:]) + ")"
nPending += 1
return self.runCommands(sql)
