Question

Generate list of column names from database table

  • 18 October 2017
  • 7 replies
  • 30 views

Badge +8

I am working on a custom transformer, where one of the input parameters is based on columns in a PostgreSQL table. Initially I wanted to give the user freedom to choose the database connection, schema, and table name... After playing around with the POSTGRES reader I had to realize that the table name seems impossible to alter afterwards - I can live with that.

I am still struggling with the list of choices that I want to generate from the database table columns.

 

Is there a possibility in FME to retrieve the column names of a database table before I run a workspace? If one can do that with Python, then that's fine for me, but at the moment I cannot see how to do this. psycop2 isn't part of the Python in FME (afaik) and using FMEs own modules I do not know how to do it.

Do I have to use fme.macroValues or can it be done using fmeobjects.getAllAttributeNames? And how to reference the PostgreSQL table in the script?


7 replies

Badge

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 = []
 
conn = psycopg2.connect(host="%s"%FME_MacroValues['HOST'], port=FME_MacroValues['PORT'], dbname="%s"%FME_MacroValues['DATABASE'], user="%s"%FME_MacroValues['USER'], password="%s"%FME_MacroValues['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(row[0])
row = cur.fetchone()
 
return ",".join(colnames)

2017-10-18-09-38-11-none-none-c-data-local-dev-supget-cols.fmw

Badge +8

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).

Badge

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.

Badge

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.

 

You can also up vote this idea.

 

 

Userlevel 4

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.

Userlevel 2
Badge +17

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)

Reply