Accessing Column Objects In SQLAlchemy
Product & Engineering

Accessing Column Objects In SQLAlchemy

July 6, 2016

We were building an abstraction on top of a bunch of databases using SQLAlchemy. We wanted to enable the user to be able to query databases by giving just the name of table and column.

Now, this seems to be easily doable by SQLAlchemy Query function but while implementing we came across a parsing problem. When a user inputs the name of table and column, it is read as a string but what we want is a Column object of SQLAlchemy. This can be done in a few ways and we will describe the approach we used with an example.

Let the following be a database with a table hmdbkegg containing columns hmdb_id and kegg_id.


This can be constructed by using a class extending Base class of SQLAlchemy as follows:

Accessing Column Objects In SQLAlchemy Elucidata

Here id, kegg_id, and hmdb_id are Column objects and HMDB is a class with table name ‘hmdbkegg’.

Now, to query ‘kegg_id’ from given ‘hmdb_id’ we try using column names directly which are of type string, we wrote a simple function (which raises obvious errors when running)

def query(target_column, source_column, value):
   rows = session.query(target_column).filter_by(source_column = value)
   return rows


rows = session.query(HMDB.kegg_id).filter(HMDB.hmdb_id == “HMDB00122”)

is an SQLAlchemy query function which should be executed.


rows = query(“kegg_id”, “hmdb_id”, ”HMDB00001”)

Returns error, Entity ‘<class ‘tables.HMDB’>’ has no property ‘column_name

And this

rows = query(kegg_id, hmdb_id, ”HMDB00001”)

Returns error, name ‘hmdb_id’ is not defined

Using .filter method

def query(target_column, source_column, value):
   rows = session.query(target_column).filter(source_column == 'value')
   return rows


rows = query(“HMDB.kegg_id” , ”HMDB.hmdb_id” , ”HMDB00001”)

Returns ‘None’

A correct solution is to create a dictionary containing a mapping for Column objects and Column names.

Accessing Column Objects In SQLAlchemy Elucidata

And use this to access column object based on column name provided. For every new table/column, a new entry has to be added to the dictionary. Maybe we can do better?

If this dictionary is created by itself, that might help. These columns are defined in the class so exploring its attributes should help.

In the final solution, we defined a function which takes the class name and column name(type String) as input and returns the Column (SQLAlchemy column object). Inside this function, an SQLAlchemy Column Collection is created consisting of all Column objects for mapped class. The Column Collection is like a dictionary, with keys as column names. This function returns the Column object corresponding to column_name.

This gives classname from given table name, here ‘hmdbkegg’ as input table name and returns “HMDB” as classname

Accessing Column Objects In SQLAlchemy


This gives the required column object

Accessing Column Objects In SQLAlchemy

Now, let’s use this to query some values

Accessing Column Objects In SQLAlchemy

rows = query(‘hmdbkegg’, “kegg_id”, “hmdb_id”, ”HMDB00122”)

Terminal Output:

Screen Shot 2016-07-13 at 2.09.05 PM.png

Works properly!

Try Polly platform for huge biological data analysis. Book a demo today!

Subscribe to our newsletter
Only data insights. No spam!
Thank you! Please click on the link to start the download.
Download Now
Oops! Something went wrong while submitting the form.

Blog Categories