bzPython-FM easily performs Python code within FileMaker, using the bBox plugin.
There are literally thousands of Python packages out there, for AI and Machine Learning, data analysis, data science and IT automation — now at your fingertips from within FileMaker.
Overview
bzPython-FM provides an easily scripted method of performing Python code from the FileMaker scripting environment. All a FileMaker developer needs to do is create the Python code [or partner with another developer] then pass it to the module. bzPython-FM, using bBox, will then perform the code and return output from Python via print().
As well, the bzPython-FM module will return any errors that Python encounters when attempting to run the code. These include errors accessing the Python environment, syntax issues in the Python code as well as errors encountered when executing the code (unreferenced variables, missing libraries, etc.).
bzPython includes demo examples for JSON arrays, and numerical and multi-variable data visualizations.
Download bzPython here
Background
The Python environment provides a robust set of functionalities both natively and through a vast variety of packages. Python can also perform many types of data calculations at a much more performant clip that the FileMaker calculation engine.
FileMaker Pro does not have a native Perform Python Code[] script step. Luckily, the bBox plugin from Beezwax does contain this capability, via the bBoxPython3Run() command.
Method
As stated above, the bBox plugin provides the method of interacting with the Python environment.
Installing bBox
The bzPython-FM demo file contains a way to install bBox. The most recent version of bBox can also be downloaded from the Beezwax website here: https://www.beezwax.net/products/bbox
Note: bBox can only be installed on macOS (with FileMaker Pro or FileMaker Server) or Linux Ubuntu, with Python3 installed. Therefore bzPython will not function outside of these environments.
Executing bzPython
To execute code using bzPython, simply pass your Python code as the parameter to the following script:
bzPython . public . execute bBox_Python3Run
The script will run, attempt to execute the Python code and return a JSON object to the calling script via the Exit Script[] result.
This script result object will contain the following keys:
- status – indication if code ran successfully or hit errors
- "error" or "success"
- error – numerical code communicating type of error
- blank if no error occurred.
- 1 if any error occurs ( FMP, bBox_LastError(-3) , Python syntax, python execute)
- message – explanation of any error that occurred
- no error occurs: blank
- FMP error occurs: a human-readable description of the error
- bBox_LastError occurs: a human-readable description of the error with the bBox_LastError(-3) resulting code
- Python compile error: resulting traceback error response from the Python engine upon attempt to compile code
- Python execute error: resulting traceback error response from the Python engine upon attempt to execute
- data – output of the executed Python code
- all output of the
print()function in the executed Python code
- all output of the
Note: If an error occurs during execution after a portion of the output has been already printed, this value will contain all of the printed data up until the error occurred. In this case, the status will be 'error' but 'data' will be populated.
To see a detailed breakdown of the mechanics of compiling, executing, and parsing out the data and error results, see Appendix B: Anatomy of bzPython Code Execution
Installing Python packages
To use a Python package in your code, you must install it first. While packages can be installed directly via Terminal, the demo file also contains a method to install a package by name. This is done by using Python to run a simple shell command.
Installing using the Demo File
Navigate to the 'Packages' tab. Here you can:
- Get a list of previously installed Python packages
- Install a new package by name
- Un-install a new package by name
Once a package has been successfully installed, you can access that package in your Python code and start to use it.
Note: Installing Packages on a server must be done manually.
Testing Your Code
The demo file contains a Workshop for testing your Python Code. In the 'Workshop' tab, simply enter your Python code on the left and click ‘Execute the Code’. The result of your print() commands will show in the result and any errors encountered below that. As well, duration of execution in seconds is tracked.
If this file is hosted on FileMaker Server and you have installed bBox on the server, you can perform the code as Perform Script on Server, wait and the results will displayed the same as if run locally.
The code used in the Workshop is a great blueprint for how to integrate bzPython into your FileMaker apps.
A note on duration: Simply importing packages will cause a longer duration of your script, especially if this is the first time in your OS session loading this package. Because bzPython starts a new Python session every time it executes, packages need to be imported every time as part of your Python code. As well, variables are not stored.
Demos
The file contains three basic demos using a small set of Python libraries. All three are based on the concept of exporting a FileMaker dataset as an xlsx file and having Python load the dataset using the pandas library. See also the blog post Leveraging pandas with Python to Analyze FileMaker Data Sets.
Note: If your Python environment has not installed the required packages, an error will be returned.
Demo 1: Convert to JSON array.
Simply put, this converts your exported xlsx into a JSON array and saves it back to FileMaker as a container field. Once the container field with JSON is populated, there are two buttons to display the JSON in a webviewer. One displays as pure JSON text, the other using a JS library called Tabulator.
Required Packages: pandas
Demo 2: Numerical Analysis.
This demo does a simple box plot analysis one of the numerical variables in your dataset. This uses the pandas package to read the data and then the matplotlib and seaborn packages to visualize the data.
Required Packages: pandas, matplotlib, seaborn
Demo 3: Multi-Variable Analysis.
This demo does a box plot analysis one of the numerical variables in your dataset broken out by one of the categorical variables in the dataset. The boxplots are vertical and each value amongst the category broken out horizontally. This uses the pandas package to read the data and then the matplotlib and seaborn packages to visualize the data.
Required Packages: pandas, matplotlib, seaborn
Appendix A: Generalized Implementation Technique for bzPython
0. Prep your environment (prior to attempting code)
To ensure your code will run properly, do the following:
- Install bBox on your environment (make sure the plugin is enabled as well).
Install manually from beezwax.net download or using demo file. - Test bzPython functionality
FileMaker Perform ScriptbzPython . test bBox_Python3Run - Install any Python packages you will be importing as part of your code.
Install manually in shell commands or using demo file.
1. Gather your Python Code and Export Your Dataset to File(s), if needed
- Start with a Pre-written code template.
This will include importing any packages needed to run as well as the code to execute. Write your python executable code ahead of time, including handlebar merge tags ( ex: {{datasetFilePath}} ) . - Export FM Dataset ( if needed)
Export your FileMaker Pro dataset to an xlsx file so that python can ingest it. Make sure to save that file path. - Merge file paths and any other data into template
Convert any filepaths from fmp format to the POSIX format using ConvertFromFileMakerPath ( $_dataset_file_path ; PosixPath ).
Substitute these into your code template. This can also include merging JSON data directly as well as merging an output file path for Python to write its data output.
2. Execute your code using bzPython
Perform the FileMaker Script from the demo file: bzPython . execute bBox_Python3Run and pass your Python code as the Script Parameter.
3. Determine Status of execution attempt
The FileMaker script will attempt to execute the code and capture all errors. A FileMaker error will not surface to the user. The script will result a JSON object containing a key of "status". The value of status will be success or error. If the status is error, there is a key of "message" which will contain a detailed account of the error.
4. Read the result of your Executed Code and Python Created File(s)
If your attempt returns a status of 'success', then the code executed without issue. If you chose to return data via the print() command in Python, any printed data will be in the script result object in the key of "result". If you chose to return to a file path, then you can read the data using FileMaker Pro's Data File script steps. The demo file contains a script for easily reading data files called u . read data from file path
Appendix B: Anatomy of bzPython Code Execution
1. Read Executable Python Code
Code is passed as the Get(ScriptParameter). An error occurs if no code is passed.
2. Check if bBox is installed
If bBox_Version("") returns a "?" then bBox is not installed.
3. Clear any existing bBox errors
Performing bBox_PythonFinalize() will clear out any existing errors that would surface with bBox_LastError(-3).
4. Test bBox_Python3Run()
bBox_Python3Run() with a simple print command of print('1234', end ='') should result in the text 1234. Anything else results in an error.
5. Attempt to compile Python code (in Python)
First the code is converted to a Python-readable string by FileMaker. Then in the Python environment we attempt to compile using a built-in Python package called ast. This attempt is wrapped in a try. If the ast.compile()step throws an exception, the traceback results will be returned as the error message.
6. Execute Python Code
Python executes that same Python-readable string using the exec()function, wrapped in a try. The code will execute in Python. Any print() commands will output until Python hits the end of the code or encounters any traceback error.
7. Check for bBox_LastError
Performing the code in an exec()wrapped in a try means that any standard errors will not surface to bBox_LastError(-3). However, if somehow there was an error just running the execute command, we check for that.
8. Parse the bBox output into print() output and traceback errors
bBox_Python3Run() will use mode = 32 to ensure that both the print() output and the stderr output are returned. To delineate where print() output ends and traceback errors begin, we insert a UUID in the output. These two values are returned.
9. Return the print() result and any error details
The print() results are now isolated as well as any traceback errors. Any text in the traceback error will result in a 'status' value of "error", even if some printed results are also returned, otherwise status is "success". The "error" value is generally a boolean returning True if any error occurred at any. 'message' will contain either a human-readable description of an error occurred during scripting or the Python traceback code for a compile or execution error. Finally 'result' contains any output of the print() command, even if an error occurred during the script.