Remove/Filter a query.all() results, add a 'virtual' column

[Cp Divers]

Hello Guys, this is my very first post here. I'm not sure this the the right place. I'm a week old with Python and SQLAlchemy. And I believe I'm missing a couple concept, hopefully you can help|

I do have this class

class BLPart(db.Model):
    __tablename__ = 'BL_parts'

    ITEMTYPE = Column(String(1, 'utf8mb4_unicode_ci'), nullable=False)
    ITEMID = Column(String(20, 'utf8mb4_unicode_ci'), primary_key=True)
    ITEMDESC = Column(Text(collation='utf8mb4_unicode_ci'), nullable=False, index=True)
    CATEGORY = Column(ForeignKey('BL_categories.category_id'), nullable=False, index=True, server_default=text("0"))
    ITEMWEIGHT = Column(String(10, 'utf8mb4_unicode_ci'))
    ITEMDIMX = Column(Float, server_default=text("0"))
    ITEMDIMY = Column(Float, server_default=text("0"))
    ITEMDIMZ = Column(Float, server_default=text("0"))
In my code python code I got something like this:

if form.validate_onsubmit():
search = form.search.data.strip()
query = db.session.query(BLPart).filter(
or(
BLPart.ITEMDESC.contains(search, autoescape=True),
BLPart.ITEMID.contains(search, autoescape=True)
)
)

results= query.all()
print(type(results)) #<class 'list'>
print(results) #[<BLPart 3005>, <BLPart 30055>, <BLPart 30056>, <BLPart 3005f1>, <BLPart 3005f2>, <BLPart 3005f3>, <BLPart 3005pb001>, <BLPa

Here are my 2 main questions,

1) I do have some 'smart' filtering that I would like to do after I get the results
Based on that filter I'd like to remove some rows from the results variables

2) In my class, I have 8 columns, based on my filtering I want to create a new 'virtual' column which does not exist in the DB
Let's say I want to create a Foreground color and a background color 'column' in the results variable
How can I achieve this ?

[Simon King]

Hi, welcome to Python and SQLAlchemy :-)

If you want to do some extra filtering on the results, you can iterate
over the results, decide whether each item matches your filter
conditions, and if it does, append it to a new list, something like
this:

filtered_results = []
for part in query.all():
if <your filter conditions go here>:
filtered_results.append(part)

You can add new properties and methods to your BLPart class. The exact
syntax would depend on how you want to use them. Here's the simplest
example:

class BLPart(db.Model):
# column definitions etc.
ITEMTYPE = Column(...)

# Non-db attributes
foreground = "#000"
background = "#fff"

Now every BLPart instance will have "foreground" and "background"
attributes with those values.

If you need something more complicated than that, let us know how you
would want to use them.

Hope that helps,

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/01812940-c02f-4ad4-9a2d-adfdb736a13cn%40googlegroups.com.

[Cp Divers]

Hello Simon, thank you for your help. I've also done more research and tests on my side and here are my solution:

1) remove some item from the list - this was quite easy !

items_to_delete=[] # add all the row/item index that I want to delete

for idx,p in enumerate(found):

if  not re.search(regex,(p.ITEMID).casefold()): # search what I want to get rid of

items_to_delete.append(idx)

#reverse the list so the highest item is first

items_to_delete.sort(reverse=True)

#delete the row from the list

for idx in items_to_delete:

del found[idx]

2) was even easier
# no need to change my Class, or the db to add the columns

        for p in found:

            p.Newcolumn1 = "test1"

            p.Newcolumn2 = "test2"

I hope it will help others !