SnapLogic Python Read and Execute SQL File

I have a simple SQL file that I'd like to read and execute using a Python Script Snap in SnapLogic. I created an expression library file to reference the Redshift account and have included it as a parameter in the pipeline.

I have the code below from another post. Is there a way to reference the pipeline parameter to connect to the Redshift database, read the uploaded SQL file and execute the commands?

fd = open('shared/PythonExecuteTest.sql', 'r')
sqlFile = fd.read()
fd.close()

sqlCommands = sqlFile.split(';')

for command in sqlCommands:
    try:
        c.execute(command)
    except OperationalError, msg:
        print "Command skipped: ", msg

You can access pipeline parameters in scripts using $_.

Let's say, you have a pipeline parameter executionId. Then to access it in the script you can do $_executionId.

Following is a test pipeline.

With the following pipeline parameter.

Following is the test data.

Following is the script

# Import the interface required by the Script snap.
from com.snaplogic.scripting.language import ScriptHook
import java.util

class TransformScript(ScriptHook):
    def __init__(self, input, output, error, log):
        self.input = input
        self.output = output
        self.error = error
        self.log = log

    # The "execute()" method is called once when the pipeline is started
    # and allowed to process its inputs or just send data to its outputs.
    def execute(self):
        self.log.info("Executing Transform script")
        while self.input.hasNext():
            try:
                # Read the next document, wrap it in a map and write out the wrapper
                in_doc = self.input.next()
                wrapper = java.util.HashMap()
                wrapper['output'] = in_doc
                wrapper['output']['executionId'] = $_executionId

                self.output.write(in_doc, wrapper)
            except Exception as e:
                errWrapper = {
                    'errMsg' : str(e.args)
                }
                self.log.error("Error in python script")
                self.error.write(errWrapper)

        self.log.info("Finished executing the Transform script")

# The Script Snap will look for a ScriptHook object in the "hook"
# variable.  The snap will then call the hook's "execute" method.
hook = TransformScript(input, output, error, log)

Output:

Here, you can see that the executionId was read from the pipeline parameters.

Note: Accessing pipeline parameters from scripts is a valid scenario but accessing other external systems from the script is complicated (because you would need to load the required libraries) and not recommended. Use the snaps provided by SnapLogic to access external systems. Also, if you want to use other libraries inside scripts, try sticking to Javascript instead of going to python because there are a lot of open source CDNs that you can use in your scripts.

Also, you can't access any configured expression library directly from the script. If you need some logic in the script, you would keep it in the script and not somewhere else. And, there is no point in accessing account names in the script (or mappers) because, even if you know the account name, you can't use the credentials/configurations stored in that account directly; that is handled by SnapLogic. Use the provided snaps and mappers as much as possible.

---

Update #1

• You can't access the account directly. Accounts are managed and used internally by the snaps. You can only create and set accounts through the accounts tab of the relevant snap.
• Avoid using script snap as much as possible; especially, if you can do the same thing using normal snaps.

---

Update #2

The simplest solution to this requirement would be as follows.

• Read the file using a file reader
• Split based on ;
• Execute each SQL command using the Generic JDBC Execute Snap