Re: [sqlalchemy] Remove/Filter a query.all() results, add a 'virtual' column
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 !
On Monday, February 21, 2022 at 2:55:42 AM UTC-8 Simon King wrote:
> 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 :
> 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
>
> On Sun, Feb 20, 2022 at 9:37 PM Cp Divers wrote:
> >
> > 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)) #
> > print(results) #[, , , 3005f1>, , , , >
> > 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 ?
> >
> >
> > --
> > 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
> .
>
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/2498c9ae-ccde-40ed-8eba-f3ac45240f86n%40googlegroups.com.
Re: [sqlalchemy] CTE w/VALUES in SELECT?
> On Feb 21, 2022, at 9:50 AM, Mike Bayer wrote:
>
> I thought 1.3 had "values", but if not, then you'd need to roll a recipe of
> some kind, the original recipe is at
> https://github.com/sqlalchemy/sqlalchemy/wiki/PGValues
Thanks. In 1.3 sqlalchemy.sql.expression.ValuesBase exists, but it inherits
from UpdateBase and is tied (AFAICT) to inserts and updates.
>
> On Mon, Feb 21, 2022, at 9:06 AM, Philip Semanchuk wrote:
>> Thanks! It looks like 1.4 is required for this, correct? Any way to do this
>> under 1.3?
>>
>> > On Feb 20, 2022, at 8:17 PM, Mike Bayer wrote:
>> >
>> > the Values construct doesn't have CTE direct support right now so you need
>> > to make a subquery first, then CTE from that
>> >
>> > from sqlalchemy import Column
>> > from sqlalchemy import column
>> > from sqlalchemy import Integer
>> > from sqlalchemy import select
>> > from sqlalchemy import String
>> > from sqlalchemy import values
>> > from sqlalchemy.ext.declarative import declarative_base
>> > from sqlalchemy.orm import declarative_base
>> >
>> > Base = declarative_base()
>> >
>> >
>> > class A(Base):
>> > __tablename__ = "my_table"
>> >
>> > id = Column(Integer, primary_key=True)
>> > name = Column(String)
>> >
>> >
>> > v1 = select(
>> > values(
>> > column("name", Integer), column("color", String), name="my_values"
>> > ).data([("Lancelot", "blue"), ("Galahad", "blue. no, yellow")])
>> > ).cte()
>> >
>> >
>> > stmt = select(A, v1.c.color).join_from(A, v1, A.name == v1.c.name)
>> > print(stmt)
>> >
>> >
>> >
>> >
>> > On Thu, Feb 17, 2022, at 1:00 PM, Philip Semanchuk wrote:
>> >> Hi,
>> >> I'm trying to use a VALUES statement in a CTE, and I can't figure out the
>> >> correct SQLAlchemy constructs to make this happen. I'd appreciate any
>> >> help. Here's the SQL I'd like to express in SQLAlchemy --
>> >>
>> >> WITH knights(name, favorite_color) AS (
>> >> VALUES
>> >> ('Lancelot', 'blue'),
>> >> ('Galahad', 'blue. no, yellow')
>> >> ),
>> >> SELECT my_table.*, favorite_color
>> >> FROM my_table
>> >> JOIN knights USING (name)
>> >>
>> >> Ideally, I would like to be able to express this as a Query that I can
>> >> later execute or pass to an insert().from_select(columns, my_query).
>> >>
>> >> The backend is Postgres 11, and our SQLAlchemy version is 1.3.
>> >>
>> >> Thanks
>> >> Philip
>> >>
>> >> --
>> >> 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+unsubscr...@googlegroups.com.
>> >> To view this discussion on the web visit
>> >> https://groups.google.com/d/msgid/sqlalchemy/E6CA442E-CB28-431B-9056-61144A9838D2%40americanefficient.com.
>> >>
>> >
>> >
>> > --
>> > 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+unsubscr...@googlegroups.com.
>> > To view this discussion on the web visit
>> > https://groups.google.com/d/msgid/sqlalchemy/52b46002-1700-4fc9-b2be-fa2976edf5ef%40www.fastmail.com.
>>
>> --
>> 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+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/17721A18-526A-42FC-AD2D-9FD5BA202890%40americanefficient.com.
>>
>
>
> --
> 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,
Re: [sqlalchemy] CTE w/VALUES in SELECT?
I thought 1.3 had "values", but if not, then you'd need to roll a recipe of
some kind, the original recipe is at
https://github.com/sqlalchemy/sqlalchemy/wiki/PGValues
On Mon, Feb 21, 2022, at 9:06 AM, Philip Semanchuk wrote:
> Thanks! It looks like 1.4 is required for this, correct? Any way to do this
> under 1.3?
>
> > On Feb 20, 2022, at 8:17 PM, Mike Bayer wrote:
> >
> > the Values construct doesn't have CTE direct support right now so you need
> > to make a subquery first, then CTE from that
> >
> > from sqlalchemy import Column
> > from sqlalchemy import column
> > from sqlalchemy import Integer
> > from sqlalchemy import select
> > from sqlalchemy import String
> > from sqlalchemy import values
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import declarative_base
> >
> > Base = declarative_base()
> >
> >
> > class A(Base):
> > __tablename__ = "my_table"
> >
> > id = Column(Integer, primary_key=True)
> > name = Column(String)
> >
> >
> > v1 = select(
> > values(
> > column("name", Integer), column("color", String), name="my_values"
> > ).data([("Lancelot", "blue"), ("Galahad", "blue. no, yellow")])
> > ).cte()
> >
> >
> > stmt = select(A, v1.c.color).join_from(A, v1, A.name == v1.c.name)
> > print(stmt)
> >
> >
> >
> >
> > On Thu, Feb 17, 2022, at 1:00 PM, Philip Semanchuk wrote:
> >> Hi,
> >> I'm trying to use a VALUES statement in a CTE, and I can't figure out the
> >> correct SQLAlchemy constructs to make this happen. I'd appreciate any
> >> help. Here's the SQL I'd like to express in SQLAlchemy --
> >>
> >> WITH knights(name, favorite_color) AS (
> >> VALUES
> >> ('Lancelot', 'blue'),
> >> ('Galahad', 'blue. no, yellow')
> >> ),
> >> SELECT my_table.*, favorite_color
> >> FROM my_table
> >> JOIN knights USING (name)
> >>
> >> Ideally, I would like to be able to express this as a Query that I can
> >> later execute or pass to an insert().from_select(columns, my_query).
> >>
> >> The backend is Postgres 11, and our SQLAlchemy version is 1.3.
> >>
> >> Thanks
> >> Philip
> >>
> >> --
> >> 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+unsubscr...@googlegroups.com
> >> .
> >> To view this discussion on the web visit
> >> https://groups.google.com/d/msgid/sqlalchemy/E6CA442E-CB28-431B-9056-61144A9838D2%40americanefficient.com.
> >>
> >
> >
> > --
> > 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+unsubscr...@googlegroups.com
> > .
> > To view this discussion on the web visit
> > https://groups.google.com/d/msgid/sqlalchemy/52b46002-1700-4fc9-b2be-fa2976edf5ef%40www.fastmail.com.
>
> --
> 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+unsubscr...@googlegroups.com
> .
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/17721A18-526A-42FC-AD2D-9FD5BA202890%40americanefficient.com.
>
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
Re: [sqlalchemy] CTE w/VALUES in SELECT?
Thanks! It looks like 1.4 is required for this, correct? Any way to do this
under 1.3?
> On Feb 20, 2022, at 8:17 PM, Mike Bayer wrote:
>
> the Values construct doesn't have CTE direct support right now so you need to
> make a subquery first, then CTE from that
>
> from sqlalchemy import Column
> from sqlalchemy import column
> from sqlalchemy import Integer
> from sqlalchemy import select
> from sqlalchemy import String
> from sqlalchemy import values
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import declarative_base
>
> Base = declarative_base()
>
>
> class A(Base):
> __tablename__ = "my_table"
>
> id = Column(Integer, primary_key=True)
> name = Column(String)
>
>
> v1 = select(
> values(
> column("name", Integer), column("color", String), name="my_values"
> ).data([("Lancelot", "blue"), ("Galahad", "blue. no, yellow")])
> ).cte()
>
>
> stmt = select(A, v1.c.color).join_from(A, v1, A.name == v1.c.name)
> print(stmt)
>
>
>
>
> On Thu, Feb 17, 2022, at 1:00 PM, Philip Semanchuk wrote:
>> Hi,
>> I'm trying to use a VALUES statement in a CTE, and I can't figure out the
>> correct SQLAlchemy constructs to make this happen. I'd appreciate any help.
>> Here's the SQL I'd like to express in SQLAlchemy --
>>
>> WITH knights(name, favorite_color) AS (
>> VALUES
>> ('Lancelot', 'blue'),
>> ('Galahad', 'blue. no, yellow')
>> ),
>> SELECT my_table.*, favorite_color
>> FROM my_table
>> JOIN knights USING (name)
>>
>> Ideally, I would like to be able to express this as a Query that I can later
>> execute or pass to an insert().from_select(columns, my_query).
>>
>> The backend is Postgres 11, and our SQLAlchemy version is 1.3.
>>
>> Thanks
>> Philip
>>
>> --
>> 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+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/E6CA442E-CB28-431B-9056-61144A9838D2%40americanefficient.com.
>>
>
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/52b46002-1700-4fc9-b2be-fa2976edf5ef%40www.fastmail.com.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/17721A18-526A-42FC-AD2D-9FD5BA202890%40americanefficient.com.
Re: [sqlalchemy] Remove/Filter a query.all() results, add a 'virtual' column
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 :
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
On Sun, Feb 20, 2022 at 9:37 PM Cp Divers wrote:
>
> 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)) #
> print(results) #[, , , 3005f1>, , , ,
> 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 ?
>
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/01812940-c02f-4ad4-9a2d-adfdb736a13cn%40googlegroups.com.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexe_%3DMzCEpRWO%2BOQotOucJNqMb9zDCOPKXdJWh2vA-qHHg%40mail.gmail.com.
