お見苦しいソースですが、salesforceとmysqlのデータ連携を図っておりまして、
salesfoceへの接続 s2hdb.py
アップデーター s2h_updater.py
下記プログラム samlple.com
mysqlの最新更新日とsalesforceの最終更新日を比較しsalesforce側の日付が新しければmysql側をupsertするというプログラムです。
実行すると
File "/home/dbcon/sample.py", line 96, in lo_update_companies
cur.execute(q)
UnboundLocalError: local variable 'q' referenced before assignment
とエラーが出てしまします。
色々試したのですが解決できず。。。。
解決方法を教えていただきたいです。
python2.7
1#!/usr/bin/env python 2# -*- coding: utf-8 -*- 3import s2hdb 4from datetime import datetime as dt 5from operator import add 6# 7# Salesforce handle Functions 8# 9def sf_get_last_modified_date(): 10 sf = s2hdb.sf_get_descriptor() 11 q = "SELECT LastModifiedDate FROM Account " + \ 12 "ORDER BY LastModifiedDate DESC LIMIT 1" 13 r = sf.query_all(q) 14 last_modified = dt.strptime(r["records"][0]["LastModifiedDate"], 15 '%Y-%m-%dT%H:%M:%S.000+0000') 16 return last_modified 17def sf_get_updated_companies(last_modified): 18 sf = s2hdb.sf_get_descriptor() 19 q = "SELECT Name, Website, Description, Description_JP__c, " + \ 20 "Core_Technology__c, Core_Technology_JP__c, Development_Status__c, " + \ 21 "Development_Status_JP__c, Application__c, Application_JP__c, Tags__c" + \ 22 " FROM Account WHERE LastModifiedDate > " + \ 23 last_modified.strftime('%Y-%m-%dT%H:%M:%S.000+0000') 24 results = sf.query_all(q) 25 coms = [] 26 for r in results["records"]: 27 com = [] 28 elements = ['Name', 'Website', 'Description', 'Description_JP__c', 29 'Core_Technology__c', 'Core_Technology_JP__c', 'Development_Status__c', 30 'Development_Status_JP__c', 'Application__c', 'Application_JP__c', 'Tags__c'] 31 elements = [str(e) for e in elements] 32 for e in elements: 33 # Convert NoneType Object as "" 34 if (r[e] is None): 35 r[e] = str("") 36 r[e] = r[e].encode('utf-8') 37 r[e] = r[e].replace('\'', '\'\'') 38 if (e == 'Tags__c'): 39 if (r[e] is None): 40 r[e] = str("") 41 com.append('{0}'.format(r[e]).strip().split(";")) 42 else: 43 com.append('\'{0}\''.format(r[e])) 44 # Duplication check. 45 # It seems salesforce returns duplicated records. 46 duplicated = False 47 for c in coms: 48 if coms[0] == '\'{0}\''.format(r["Name"]): 49 duplicated = True 50 if (duplicated is True): 51 continue 52 coms.append(com) 53 return coms 54# 55# Local DB handle functions 56# 57def lo_get_last_modified_date(): 58 con = s2hdb.lo_get_descriptor() 59 cur = con.cursor() 60 q = "SELECT last_modified from company ORDER BY last_modified DESC LIMIT 1" 61 cur.execute(q) 62 if (cur.rowcount == 0): 63 # We want to move all from salesforce, so just set very early date 64 last_modified = dt.strptime("2000/1/1 00:00:00", '%Y/%m/%d %H:%M:%S') 65 else: 66 last_modified = cur.fetchone()[0] 67 cur.close() 68 return last_modified 69def lo_update_companies(data): 70 if (len(data) == 0): 71 return 72 con = s2hdb.lo_get_descriptor() 73 cur = con.cursor() 74 coms = [] 75 for t in data: 76 coms.append(t[0:10]) 77 q = "INSERT INTO company(legal_name, website, desc, desc_jp, " + \ 78 "core_tech, core_tech_jp, " + \ 79 "development_status, development_status_jp, application, application_jp, cat_name) " + \ 80 "VALUES " + \ 81 ", ".join(map(lambda x: "(" + ",".join(x) + ")", coms)) + " " + \ 82 "ON DUPLICATE KEY UPDATE legal_name = VALUES (legal_name)" + \ 83 cur.execute(q) 84 cur.close() 85 con.commit() 86コード
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/06/12 10:10
2018/06/12 10:14
2018/06/12 10:15