Created
March 27, 2016 20:38
-
-
Save luanntvn/a2cc99ea5754c025bfe1 to your computer and use it in GitHub Desktop.
Because the SalesForce Analytics API limits up to 2000 records (https://developer.salesforce.com/forums/?id=906F0000000BKDaIAO ) so we need to pull report in CSV
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import requests, csv, re, sys | |
| from simple_salesforce import Salesforce | |
| import MySQLdb | |
| sfConfig = { | |
| 'username': 'zzz', | |
| 'password': 'zzz', | |
| 'security_token': 'zzzz', | |
| 'url': 'zzz.my.salesforce.com', | |
| 'report_id': 'zzzz' | |
| } | |
| mysqlConfig = { | |
| 'host': 'localhost', | |
| 'user': 'zzz', | |
| 'passwd': 'zzz', | |
| 'db': 'zzz', | |
| 'table': 'zzz' | |
| } | |
| def fetchSFcsv(): | |
| sf = Salesforce(username=sfConfig['username'], password=sfConfig['password'], security_token=sfConfig['security_token']) | |
| with requests.session() as s: | |
| d = s.get(("https://%s/%s?export=1&enc=UTF-8&xf=csv" % (sfConfig['url'], sfConfig['report_id'])), | |
| headers=sf.headers, cookies={'sid': sf.session_id}) | |
| return d.content | |
| def nullify(L): | |
| """Convert empty strings in the given list to None.""" | |
| # helper function | |
| def f(x): | |
| if(x == ""): | |
| return None | |
| else: | |
| return x | |
| return [f(x) for x in L] | |
| def buildInsertCmd(table, numfields): | |
| """ | |
| Create a query string with the given table name and the right number of format placeholders. | |
| example: | |
| >>> buildInsertCmd("foo", 3) | |
| 'insert into foo values (%s, %s, %s)' | |
| """ | |
| assert(numfields > 0) | |
| placeholders = (numfields-1) * "%s, " + "%s" | |
| query = ("INSERT INTO %s" % table) + (" VALUES (%s);" % placeholders) | |
| return query | |
| def buildCreateCmd(table, header): | |
| query = ', '.join([("`%s` varchar(255) DEFAULT NULL" % re.sub(r'\W+', '', f)) for f in header]) | |
| query = "CREATE TABLE `%s` (%s) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;" % (table, query) | |
| return query | |
| def execQuery(cursor, query, vals): | |
| try: | |
| cursor.execute(query, vals); | |
| except MySQLdb.Error as e: | |
| try: | |
| print "MySQL Error [%d]: %s" % (e.args[0], e.args[1]) | |
| except IndexError: | |
| print "MySQL Error: %s" % str(e) | |
| except: | |
| print "Unexpected error:", sys.exc_info()[0] | |
| raise | |
| def insertMySQL(cvs_content): | |
| mydb = MySQLdb.connect(host=mysqlConfig['host'], user=mysqlConfig['user'], passwd=mysqlConfig['passwd'], db=mysqlConfig['db']) | |
| cursor = mydb.cursor() | |
| lines = cvs_content.splitlines() | |
| csv_data = csv.reader(lines) | |
| header = csv_data.next() | |
| numfields = len(header) | |
| query = ("DROP TABLE IF EXISTS `%s`;" % mysqlConfig['table']) | |
| execQuery(cursor, query, None) | |
| query = buildCreateCmd(mysqlConfig['table'], header) | |
| execQuery(cursor, query, None) | |
| query = buildInsertCmd(mysqlConfig['table'], numfields) | |
| for row in csv_data: | |
| vals = nullify(row) | |
| execQuery(cursor, query, vals) | |
| mydb.commit() | |
| cursor.close() | |
| if __name__ == "__main__": | |
| sfreport = fetchSFcsv() | |
| insertMySQL(sfreport) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment