Lookup indexes from SQL server

This script can be used to populate indexes from a SQL server database by matching a key field.

You will need to replace highlighted values with your own

#Automated script
import clr
clr.AddReference('System.Data')
from System.Data import *

#Enter your SQL server connection string. check out https://www.connectionstrings.com/sqlconnection/
TheConnection = SqlClient.SqlConnection("server=localhost/SQLEXPRESS;database=test;Trusted_Connection=True")
try:
	TheConnection.Open()

	for doc in scriptInput:

#Replace "Customer" with the index filed you want to lookup with
		keyValue =str(doc.fieldValues.Customer)

		if not keyValue :
			logger.Warn("Empty key field skipping")
        		continue

#Replace the SQL search string with values appropriate to your SQL schema           
		MyAction = SqlClient.SqlCommand("select [address],[contact] from [customers] where customer = '" + keyValue +"' ", TheConnection)
		MyReader = MyAction.ExecuteReader()

		if MyReader.Read():
#Enter the index fields that you want to populate
			doc.fieldValues.Billing_address =MyReader[0]
			doc.fieldValues.contact =MyReader[1]
#End of field list
		else:
			logger.Warn("key " + keyValue + " not found")

		MyReader.Close()
        
	TheConnection.Close()
	logger.Info("task completed")
    
except Exception,e:    
	logger.Error("Exception " + str(e))