I don't know how you will deal with this but here's the way to get table's columns unsing python in a Scripted Python parameter.

To install psycop2, first go to PYPI and donwload the whl file that fits your FME/python version.

Open the whl file with 7-zip and copy the psycopg2 to your FME installation python\Lib folder (Ex: C:\apps\FME16.1.2-32-b16673\fmepython27\lib).

The attached workspace (get-cols.fmw) have a Python Scripted parameter returning a string being a comma separated list of the column names.

Python code:

import psycopg2
 
colnames = e]
 
conn = psycopg2.connect(host="%s"%FME_MacroValuesr'HOST'], port=FME_MacroValues 'PORT'], dbname="%s"%FME_MacroValues"'DATABASE'], user="%s"%FME_MacroValuesc'USER'], password="%s"%FME_MacroValuesr'PASSWORD'])
cur=conn.cursor()
 
cur.execute("select column_name, data_type from INFORMATION_SCHEMA.COLUMNS where table_name = '%s';"%FME_MacroValues_'TABLE_NAME'])
row = cur.fetchone()
while row != None:
	colnames.append(rowV0])
	row = cur.fetchone()
 
return ",".join(colnames)

get-cols.fmw

Thanks @larry, that gives me the possibility to install modules I am missing - pretty nice!

However, it looks to me as if your way is confined to 'runtime' and here I have again the problem that I cannot produce a workbench prompt that's reactive to the user input. Meaning, the Python script is not executed, hence cannot populate another user parameter (of type choice, or alike).

I don't think there is a way to do this dynamically other than using the plugin SDK and create a transformer with this capacity.

One more option could be to build an interface fronting your workspace. That interface will be able to get the dynamic values from the database and calls fme.exe when the user is done with the parameters. We did something similar in the past using c# .Net, let me know if you need to know more about this.

You can also up vote this idea.

Great answers from @larry here.

I'll just add that there are some very useful tips for how to configure FME with a custom Python interpreter (especially useful for 3rd party modules such as psycopg2 and arcpy) to be found here:

https://knowledge.safe.com/questions/53449/running-python-in-fme.html

Well worth a read if you decide to go this route.

Hi @tb09114, if your workspace would be run with desktop always, creating an FME standard parameters dialog with Python scripting at runtime might be a workaround. Here is a relevant discuss.

select different feature type each time workspace runs

larry already told how to use psycopg2. I will explain how to fetch column names of the table.

• Create a PostgreSQL database connection.
• Define the SELECT statement query to fetch data all column names from the PostgreSQL table.
• Execute the SELECT query using a cursor.execute() and use cursor.fetchall() to fetch the result.
• Iterate over the ResultSet using for loop to get all the columns.
• Close the cursor and database connection.

Use the following query to get all the column names.

select column_name from information_schema.columns where table_name = 'Your_table_name'

Example:

Refer this guide for operations Python Postgresql Database access

connection = psycopg2.connect(user="postgres",
                                  password="your_pass",
                                  host="your_host",
                                  port="5432",
                                  database="your Database")


PostgreSQL_select_Query = """select column_name from information_schema.columns where table_name = 'your_table_name' """
   
cursor = connection.cursor()
cursor.execute(PostgreSQL_select_Query)
records = cursor.fetchall()
print ("Displaying column name from table ")
   for column in records:
       print (column)