Accessing Column Objects In SQLAlchemy

by Navpreet Ahuja and Raaisa Mahajan

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.

Id Hmdb_id Kegg_id
1 HMDB00122 C00031
1 HMDB00190 C00186
1 HMDB00263 C00074

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

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

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

Here,

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

is an SQLAlchemy query function which should be executed.

Now,

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

Now,

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

ReturnsNone’

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

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

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

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

Finally,

This gives the required column object

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

Now, let’s use this to query some valuesScreen Shot 2016-07-13 at 2.08.28 PM.png

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

Terminal Output:

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

Works properly!

3 Replies to “Accessing Column Objects In SQLAlchemy

Leave a Reply

Your email address will not be published. Required fields are marked *