Filtering data from an exisiting table using psycopg2+sqlalchemy

Question

I am using psycopg2 & sqlalchemy to connect to a database and extract information from a table as follows:

def db_connection():
 eng = db.create_engine('my_URI')
 conn = eng.connect()
 return eng, conn
def table_conn():
 engine, connection = db_connection()
 metadata = db.MetaData()
 admin_table = db.Table(
 'myTableName',
 metadata,
 autoload=True,
 autoload_with=engine
 )
 return engine, connection, admin_table
def get_table_data(user_id):
 _, connection, table_data = table_conn()
 # first filter using user_id and sort the data by datetime column
 query = db.select([table_data]).where(
 table_data.columns.user_id == user_id,
 ).order_by(
 table_data.columns.created_at.desc()
 )
 result = connection.execute(query).fetchall()
 # 5th element is time
 # filtering the data to find data that has been saved at the same time
 # making it the latest data
 time = result[0][5]
 time_filtering_query = db.select([table_data]).where(
 table_data.columns.created_at == time
 )
 time_result = connection.execute(time_filtering_query).fetchall()
 return time_result

The functions: db_connection() & table_conn() are connecting to the database and the table respectively.

In the get_table_data function, I am doing the following:

1. Use the user_id to filter the table
2. Order the table in desc order on the created_at column (which is the 5th column of the table)
3. Extract the first created_at value (which is result[0][5] in the code above)
4. And use this extracted value to filter the table again

In the code, I am hardcoding the column index in the result[0][5] part. Is there a way the above code can be modified to avoid hardcoding of the values and filter the values, if possible, based on the column names or in any other neater way?

Answer 1

You should avoid doing [0] after your query. Instead, add a .limit(1) at the end of your select.

SQA supports named column references on individual result tuples. So in addition to supporting [5], which is (as you've identified) a bad idea - it should just support .time assuming that's what your column name is. However, even better is - rather than selecting the entire table_data - simply select only the column you want.