SQLAlchemy select a plurality of colums using ORM in a generic function

user_5614935 0
Viewed 39

I am using the following class:

class PostGreSQL:
    def __init__(self, db:str, table:Table):
        self.db = db
        self.table = table
        engine = create_engine(DATABASE_URL+db, pool_size=3, max_overflow=0)
        metadata.create_all(engine)
        self.conn = engine.connect()

    def __read(self,col:str="",cond:str=""):
        if cond:
            cmd = select(self.table.c[col] if col!="" else self.table).where(text(cond))
        else:
            cmd = select(self.table.c[col] if col!="" else self.table)
        return self.conn.execute(cmd)

To select the value of only one column I used to call the function with:

pgs = PostGreSQL(my_db, Users)
age = pgs._read(col="age", cond="name = 'John Doe'").fetchone()

Now I would like to have returned multiple columns, let's say the parameters "age" and "hobby" of a specific user.

If I call the same function with:

response = pgs._read(col="age, hobby", cond="name = 'John Doe'").fetchone()

I get the error:

KeyError: 'age,hobby'

which seems to indicate that table.c[] accepts only one column key.

My question is: how should I modify the function in order to return both columns?

The function should be generic and extensible to accept any combination of column keys.

Any suggestion is much appreciated.

sqlalchemy python
1 Answers

If you want to maintain the same interface for __read() you can use the following code:

from sqlalchemy.sql import select
from sqlalchemy.sql.expression import column, text

def __read(col:str="",cond:str=""):
   col = [column(x) for x in col.split(", ")] if col!="" else []
   cmd = select(col) if col!=[] else select(["*"])
   cmd = cmd.select_from(self.table)
   if cond:
      cmd = cmd.where(text(cond))
   
   return self.conn.execute(cmd)

If you can change the interface, you can directly pass the column list and remove the list comprehension

user_140834530
Related