Сейчас вы находитесь в том же положении, что и я около 8 месяцев назад. Я успешно интегрировал базу данных MySQL в Salesforce. По сути, клиент регистрирует продукт на нашем веб-сайте, затем у меня есть python, который извлекает данные из базы данных, форматирует все это в файл xlsx, а затем отправляет эти данные в отдел продаж с помощью пакета simple-salesforce, который отлично работает. Ниже приведен фрагмент кода, который должен помочь вам начать работу. Дайте знать, если у вас появятся вопросы.
import os
import sys
import xlrd
import xlwt
import time
import base64
import smtplib
import MySQLdb
import logging
import datetime
import collections
from xlrd.sheet import ctype_text
from collections import OrderedDict
from simple_salesforce import SFType
from simple_salesforce import Salesforce
from xlsxwriter.workbook import Workbook
from MySQLdb import OperationalError
from mysql.connector import MySQLConnection, Error
logger = logging.getLogger('view_rows')
hdlr = logging.FileHandler('C:\Users\Chris\Documents\Email Logs\myapp.log')
formatter = logging.Formatter('%(asctime)s %(levelname)s %(message)s')
hdlr.setFormatter(formatter)
logger.addHandler(hdlr)
logger.setLevel(logging.WARNING)
# MySQL Database Credentials
ur = 'user_name' # your username
pd = 'my_password' # your password
ht = 'localhost' # your host
pt = 3306
db = 'my_database' # database where your table is stored
con = MySQLdb.connect(user=ur, passwd=pd, host=ht, port=pt, db=db)
cursor = con.cursor()
con.autocommit(True)
# Salesforce Credentials
sf = Salesforce(username='[email protected]', password='my_SF_password', security_token='my_Generated_token_from_Salesforce', client_id='This does not really matter')
orderedDict = collections.OrderedDict()
# Gets the new customers that have created an account on the Warranty Portal
def GetNewContact():
# Creates a xlsx file
workbook = Workbook('outfile.xlsx')
sheet = workbook.add_worksheet("Warranty Info")
query = "SELECT RC.cus_id, RC.cus_username, RC.cus_subsc FROM my_database.rma_customer RC INNER JOIN my_database.rma_customer_address CA ON RC.cus_id=CA.cus_id WHERE CA.cus_address_type=0 AND CA.cus_updated=1"
query0 = "SELECT RCD.cus_firstname, RCD.cus_lastname, RCD.cus_cell_phone FROM my_database.rma_customer_details RCD INNER JOIN my_database.rma_customer_address CA ON RCD.cus_id=CA.cus_id WHERE CA.cus_address_type=0 AND CA.cus_updated=1"
query1 = "SELECT CA.cus_address_1, CA.cus_address_2, CA.cus_city, CA.cus_postcode FROM my_database.rma_customer_address CA LEFT JOIN my_database.rma_customer RC ON CA.cus_id=RC.cus_id WHERE CA.cus_address_type=0 AND CA.cus_updated=1"
cursor.execute(query)
headers = ['ID', 'Email', 'Newsletter', 'FirstName', 'LastName', 'Phone', 'Street', 'APT', 'City', 'Postal']
for i, header in enumerate(headers):
sheet.write(0, i, header)
for r, row in enumerate(cursor.fetchall()):
r = r + 1
for c, col in enumerate(row):
sheet.write(r, c, col)
cursor.execute(query0)
for r, row in enumerate(cursor.fetchall()):
r = r + 1
for c, col in enumerate(row):
sheet.write(r, c+3, col)
cursor.execute(query1)
for r, row in enumerate(cursor.fetchall()):
r = r + 1
for c, col in enumerate(row):
sheet.write(r, c+6, col)
#Updates the database after it has grabbed the needed info to create a contact in salesforce so that it does not get that same data again
workbook.close()
def CreateContact():
# Knows what file to look into for the information to create a contact
path = 'outfile.xlsx'
workbook = xlrd.open_workbook(path, on_demand = True)
sheet = workbook.sheet_by_index(0)
for rowx in xrange(1, sheet.nrows):
try:
Id = sheet.row_values(rowx, start_colx=0, end_colx=None)[0]
Email1 = sheet.row_values(rowx, start_colx=1, end_colx=None)[0]
Newsletter = sheet.row_values(rowx, start_colx=2, end_colx=None)[0]
First = sheet.row_values(rowx, start_colx=3, end_colx=None)[0]
Last = sheet.row_values(rowx, start_colx=4, end_colx=None)[0]
Phone = sheet.row_values(rowx, start_colx=5, end_colx=None)[0]
Street = sheet.row_values(rowx, start_colx=6, end_colx=None)[0]
Apt = sheet.row_values(rowx, start_colx=7, end_colx=None)[0]
City = sheet.row_values(rowx, start_colx=8, end_colx=None)[0]
Postal = sheet.row_values(rowx, start_colx=9, end_colx=None)[0]
d = sf.query("SELECT Id FROM Contact WHERE Phone ='%s' OR Email='%s'" % (Phone, Email1))
# d = returns more than just a case number so data = will specifically only get the case number
data = [e["Id"] for e in d["records"]]
sf.Contact.update(data[0],{'OwnerId' : "005U0000005jwPH", 'Phone' : Phone, 'Email' : Email1, 'Newsletter__c' : Newsletter, 'MailingStreet' : Street+" "+Apt, 'MailingCity' : City, 'MailingPostalCode' : Postal, 'AccountId' : "001U000001ZEB89"})
cursor.execute("UPDATE rma_customer AS RC INNER JOIN rma_customer_address CA ON RC.cus_id = CA.cus_id SET RC.SF_created=1 WHERE RC.cus_id='%s'" % Id)
cursor.execute("UPDATE rma_customer_address AS CA INNER JOIN rma_customer RC ON CA.cus_id = RC.cus_id SET CA.SF_created=1 WHERE CA.cus_id='%s'" % Id)
cursor.execute("UPDATE rma_customer_address AS CA INNER JOIN rma_customer RC ON CA.cus_id = RC.cus_id SET CA.cus_updated=0 WHERE CA.cus_id='%s'" % Id)
cursor.execute("UPDATE rma_customer_details AS RCD INNER JOIN rma_customer RC ON RCD.cus_id = RC.cus_id SET RCD.SF_created=1 WHERE RCD.cus_id='%s'" % Id)
# If Contact exists, skips and goes to next customer in excel sheet.
except Exception, e:
#print "Exception %s"%str(e)
Id = sheet.row_values(rowx, start_colx=0, end_colx=None)[0]
Email1 = sheet.row_values(rowx, start_colx=1, end_colx=None)[0]
Newsletter = sheet.row_values(rowx, start_colx=2, end_colx=None)[0]
First = sheet.row_values(rowx, start_colx=3, end_colx=None)[0]
Last = sheet.row_values(rowx, start_colx=4, end_colx=None)[0]
Phone = sheet.row_values(rowx, start_colx=5, end_colx=None)[0]
Street = sheet.row_values(rowx, start_colx=6, end_colx=None)[0]
Apt = sheet.row_values(rowx, start_colx=7, end_colx=None)[0]
City = sheet.row_values(rowx, start_colx=8, end_colx=None)[0]
Postal = sheet.row_values(rowx, start_colx=9, end_colx=None)[0]
logger.error("Unable to Create Contact: "+Email1+" "+Phone)
logger.error(e)
sf.Contact.create({'FirstName' : First, 'LastName' : Last, 'Newsletter__c' : Newsletter, 'Email' : Email1, 'Phone' : Phone, 'MailingStreet' : Street+" "+Apt, 'MailingCity' : City, 'MailingPostalCode' : Postal, 'AccountId' : "001U000001ZEB89"})
#print "Contact Not Found. Created New Contact!"
cursor.execute("UPDATE rma_customer AS RC INNER JOIN rma_customer_address CA ON RC.cus_id = CA.cus_id SET RC.SF_created=1 WHERE RC.cus_id='%s'" % Id)
cursor.execute("UPDATE rma_customer_address AS CA INNER JOIN rma_customer RC ON CA.cus_id = RC.cus_id SET CA.SF_created=1 WHERE CA.cus_id='%s'" % Id)
cursor.execute("UPDATE rma_customer_address AS CA INNER JOIN rma_customer RC ON CA.cus_id = RC.cus_id SET CA.cus_updated=0 WHERE CA.cus_id='%s'" % Id)
cursor.execute("UPDATE rma_customer_details AS RCD INNER JOIN rma_customer RC ON RCD.cus_id = RC.cus_id SET RCD.SF_created=1 WHERE RCD.cus_id='%s'" % Id)
#time.sleep(10)
# Sleep for 30 seconds to allow Salesforce to catch up before creating the cases to ensure they are correctly associated
workbook.release_resources()
time.sleep(5)
GetNewContact()
CreateContact()
19.06.2017
