Re: [sqlalchemy] Why was the column information stripped away in SA 1.4.20 that existed in 1.3.20?

[Gord Thompson]

*With version 1.3:*

Base = declarative_base()


class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = Column(String)


Base.metadata.create_all(engine)

print(pd.__version__) # 1.3.2
print(sa.__version__) # 1.3.24

session = Session(engine)
session.add_all([User(name="Gord"),User(name="Bob"),])
session.commit()
result_set = session.query(User.id, User.name)
df = pd.DataFrame(result_set)
print(df)
"""
id name
0 1 Gord
1 2 Bob
"""

*With version 1.4:*

print(pd.__version__) # 1.3.2
print(sa.__version__) *# 1.4.23*

session = Session(engine)
session.add_all([User(name="Gord"),User(name="Bob"),])
session.commit()
result_set = session.query(User.id, User.name)
df = pd.DataFrame(result_set)
print(df)
"""
0 1
0 1 Gord
1 2 Bob
"""

On Tuesday, August 31, 2021 at 2:38:18 PM UTC-6 Mike Bayer wrote:

>
>
> On Tue, Aug 31, 2021, at 3:10 PM, Terrence-Monroe: Brannon wrote:
>
>
> Creating a pandas dataframe that contained descriptive column names 
> formerly was as easy as:
>
> result_set = session.query(cls.column_1)
> df = pandas.Dataframe(result_set)
> print df.column_1
>
> but while this works in 1.3.20, in later versions of SA such as 1.4.19,  
> there is not enough column info supplied by SA to form the same symbolic 
> column names. Thus, the above code would fail when attempting to access 
> df.column_1 in SA 1.4.19 but not fail in SA 1.3.20.
>
>
>
> not familiar with this change, nothing should have changed with how 
> session.query() returns rows and this may be some incompatibility with 
> pandas.  Upgrade to the latest Pandas and make sure the behavior remains, 
> then please provide an MCVE, thanks
>
>
>
>
>
> Anyway to supply a flag to query() to provide the extra column that 
> existed in older SA versions?
>
>
> -- 
> 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/2664002d-25cc-450d-bc51-8652a7df3d53n%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/c47f5717-8ddc-43c5-b018-43639f0867e4n%40googlegroups.com.

[sqlalchemy] ORM query returning separate columns instead of objects

[Gord Thompson]

My memory is failing me. I seem to recall that there was a succinct way to 
get a 1.4/2.0 ORM query to return individual columns instead of ORM 
objects. That is, to tell this

print(session.execute(select(User)).fetchall())
# [(<__main__.User object at 0x0090175EC700>,)]

to return the equivalent of this

print(session.execute(select(User.id, User.name)).fetchall())
# [(1, 'Gord')]

without explicitly naming each attribute (column). A modifier like 
`.as_columns()` or something like that …?

Interestingly, if I try to use `*` I only get the first (or maybe the PK) 
column

print(session.execute(select(text("*")).select_from(User)).fetchall())
# [(1,)]

-- 
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/3d1d98e8-a7ac-4748-8cd3-56c32f275644n%40googlegroups.com.

Re: [sqlalchemy] Why was the column information stripped away in SA 1.4.20 that existed in 1.3.20?

[Mike Bayer]

On Tue, Aug 31, 2021, at 3:10 PM, Terrence-Monroe: Brannon wrote:
> 
> Creating a pandas dataframe that contained descriptive column names formerly 
> was as easy as:
> 
> result_set = session.query(cls.column_1)
> df = pandas.Dataframe(result_set)
> print df.column_1
> 
> but while this works in 1.3.20, in later versions of SA such as 1.4.19,  
> there is not enough column info supplied by SA to form the same symbolic 
> column names. Thus, the above code would fail when attempting to access 
> df.column_1 in SA 1.4.19 but not fail in SA 1.3.20.


not familiar with this change, nothing should have changed with how 
session.query() returns rows and this may be some incompatibility with pandas.  
Upgrade to the latest Pandas and make sure the behavior remains, then please 
provide an MCVE, thanks




> 
> Anyway to supply a flag to query() to provide the extra column that existed 
> in older SA versions?
> 
> 
> 
> -- 
> 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/2664002d-25cc-450d-bc51-8652a7df3d53n%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/9103295e-ee3a-4e2e-8473-b5a9263ffc9d%40www.fastmail.com.

[sqlalchemy] Re: Why was the column information stripped away in SA 1.4.20 that existed in 1.3.20?

[Gord Thompson]

One option would be to replace

result_set = session.query(cls.column_1)

with

result_set = session.execute(select(cls.column_1)).mappings().all()


On Tuesday, August 31, 2021 at 1:10:17 PM UTC-6 thequie...@gmail.com wrote:

>
> Creating a pandas dataframe that contained descriptive column names 
> formerly was as easy as:
>
> result_set = session.query(cls.column_1)
> df = pandas.Dataframe(result_set)
> print df.column_1
>
> but while this works in 1.3.20, in later versions of SA such as 1.4.19,  
> there is not enough column info supplied by SA to form the same symbolic 
> column names. Thus, the above code would fail when attempting to access 
> df.column_1 in SA 1.4.19 but not fail in SA 1.3.20.
>
> Anyway to supply a flag to query() to provide the extra column that 
> existed in older SA versions?
>
>

-- 
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/5cdb2d1c-9c4f-4363-abca-27d54a7b589en%40googlegroups.com.

[sqlalchemy] Why was the column information stripped away in SA 1.4.20 that existed in 1.3.20?

[Terrence-Monroe: Brannon]

Creating a pandas dataframe that contained descriptive column names 
formerly was as easy as:

result_set = session.query(cls.column_1)
df = pandas.Dataframe(result_set)
print df.column_1

but while this works in 1.3.20, in later versions of SA such as 1.4.19,  
there is not enough column info supplied by SA to form the same symbolic 
column names. Thus, the above code would fail when attempting to access 
df.column_1 in SA 1.4.19 but not fail in SA 1.3.20.

Anyway to supply a flag to query() to provide the extra column that existed 
in older SA versions?

-- 
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/2664002d-25cc-450d-bc51-8652a7df3d53n%40googlegroups.com.

Re: [sqlalchemy] Using SQLAlchemy to check if column is in numeric ranges

[Simon King]

You want a combination of the "between" function/method:

https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.between

...and the "or_" function:

https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.or_

Something like this:

ranges = [(18, 25), (40, 55), (60, 70)]
conditions = [User.age.between(lower, upper) for (lower, upper) in ranges]
condition = sqlalchemy.or_(*conditions)
users = session.query(User).filter(condition).all()

Hope that helps,

Simon

On Tue, Aug 31, 2021 at 10:07 AM chat...@gmail.com  wrote:
>
> Hello All!! , I have a list of age ranges i.e 18-25, 40-55 and more how can I 
> make make a query using SQLAlchemy that will check if a Column is in ranges 
> [18,25] OR [40-55] OR [60-70]
> Regards,Christos
>
> --
> 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/e6a8019c-beb4-482a-9262-b752391a16f4n%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/CAFHwexd1zOhzkGiavjzPODr9wWr8KxhY4q6mSOVFN1UT1LE%3Daw%40mail.gmail.com.

Re: [sqlalchemy] Selectinload and bindparam

[Mike Bayer]

this is true, the parameters can't be known to be passed along to a secondary 
query. the approach here would require that you make careful use of custom 
user options and events in order to make it work.  the general event hook you 
would use is documented at 
https://docs.sqlalchemy.org/en/14/orm/session_events.html#adding-global-where-on-criteria
 .I dont have the resouces to work up a complete example however an example 
of creating custom user options is in the dogpile.cache example at 
https://docs.sqlalchemy.org/en/14/orm/examples.html#examples-caching .the 
user option object you would use is based on :  
https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/interfaces.py#L792

basically build a UserDefinedOption class that carries along the parameters you 
want.  then in do_orm_execute() apply those parameters to the query when the 
option is present.  you can consume user defined options using the 
user_defined_options collection: 
https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=ormexecutestate#sqlalchemy.orm.ORMExecuteState.user_defined_options





On Tue, Aug 31, 2021, at 4:46 AM, Tomas Pavlovsky wrote:
> Sorry, once again :)
> 
> class X(Base):
> id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
> a = relationship(A)
> 
> s = select(A).where(A.id == 1)
> s = s.options(joinedload(A.b_filtered_rel))
> or
> s = select(X).options(joinedload(X.a).options(joinedload(A.b_filtered_rel))) 
> result = session.execute(s, dict(c_global_attr_1=1))
> 
> s = s.options(*selectionload*(A.b_filtered_rel))
> or
> s = 
> select(X).options(*selectionload*(X.a).options(joinedload(A.b_filtered_rel))) 
> or
> s = 
> select(X).options(joinedload(X.a).options(*selectionload*(A.b_filtered_rel))) 
> result = session.execute(s, dict(c_global_attr_1=1))
> This query will fail, beacuse only params passed to query are related Pks.
> What do you think about ability to pass some arguments to query emitted with 
> selectinload?
> 
> wtorek, 31 sierpnia 2021 o 10:34:55 UTC+2 Tomas Pavlovsky napisał(a):
>> Hello Mike,
>> 
>> Thank you very much for your response. 
>> I ended up with that, but i wanna ask you about this solution.
>> 
>> class A(Base):
>> id: int = Column('a_id', Integer, primary_key=True, autoincrement=True)
>> 
>> #descr
>> class B(Base):
>> id: int = Column('b_id', Integer, primary_key=True, autoincrement=True)
>> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
>> c_relationship = relationship("C", secondary='b_rel_c', lazy='joined', 
>> uselist=False)
>> 
>> #descr_cat
>> class C(Base):
>> id: int = Column('c_id', Integer, primary_key=True, autoincrement=True)
>> c_attr1: int = Column(Integer, nullable=False)
>> c_global_attr_1: int = Column(Integer, nullable=False)
>> 
>> class B_rel_C(Base):
>> b_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
>> c_id: int = Column('a_id', Integer, ForeignKey('c.c_id'))
>> some_kw: int = Column(Integer)
>> 
>> class CustomSelectClass(Base):
>> __table__ = select(B, C.c_global_attr_1, C.c_attr1).join(B_rel_C, 
>> B_rel_C.b_id == B.id). \
>> join(C, C.id == B_rel_C.c_id).where(
>> B.a_id == A.id,
>> C.c_global_attr_1 == bindparam('c_global_attr_1')). \
>> limit(100).subquery().lateral()
>> 
>> A.b_filtered_rel = relationship(CustomSelectClass, lazy='joined')
>> 
>> class X(Base):
>> id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
>> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
>> 
>> I use this aproach also with another schema like this:  X -> A -> A_REL_B <- 
>> B -> C 
>> 
>> s = select(A).where(A.id == 1)
>> s = s.options(joinedload(A.b_filtered_rel))
>> or
>> s = select(X).options(joinedload(A).options(joinedload(B))) 
>> result = session.execute(s, dict(c_global_attr_1=1))
>> 
>> s = s.options(*selectionload*(A.b_filtered_rel))
>> or
>> s = select(X).options(*selectionload*(A).options(joinedload(B))) 
>> or
>> s = select(X).options(joinedload(A).options(*selectionload*(B))) 
>> result = session.execute(s, dict(c_global_attr_1=1))
>> 
>> What do you think about ability to pass some arguments to query emitted with 
>> selectinload
>> 
>> poniedziałek, 30 sierpnia 2021 o 17:50:49 UTC+2 Mike Bayer napisał(a):
>>> __
>>> yes, you would use bindparam("some_name") in conjunction with the "and_()" 
>>> feature, like  options(selectinload(MyClass.foo.and_(MyClass.name == 
>>> bindparam("some_name"
>>> 
>>> 
>>> 
>>> On Mon, Aug 30, 2021, at 10:55 AM, Tomas Pavlovsky wrote:
 Hello, 
 
 is it possible in sqlalchemy arguments from db.execute(query, args) to be 
 passed to query emitted by selectinload?
 
 Thanks,
 Tomas
 
 
 -- 
 SQLAlchemy - 
 The Python SQL Toolkit and Object Relational Mapper
  
 http://www.sqlalchemy.org/
  
 To post 

Re: [sqlalchemy] SqlAlchemy 1.4 and Sequences

[Mike Bayer]

issue https://github.com/sqlalchemy/sqlalchemy/issues/6963 is added to deal 
with possibly multiple failure modes in this case.

On Tue, Aug 31, 2021, at 10:24 AM, Mike Bayer wrote:
> Hi -
> 
> Your test case is omitting a critical detail which we would assume is you are 
> using the "implicit_returning=False" flag on your create_engine().dont 
> use this flag as it serves no useful purpose and that will fix your issue 
> here, we will be deprecating and removing this flag.
> 
> will try to fix the immediate issue if possible.
> 
> 
> On Tue, Aug 31, 2021, at 3:29 AM, Francesca L wrote:
>> Hi group, 
>> 
>> I am tring to migrate from version *1.3.24* to *1.4.23* of SqlAlchemy, using 
>> *PostgreSQL 10*.
>> 
>> I found that the following code example works with 1.3, but triggers a 
>> traceback with 1.4.
>> 
>> import sqlalchemy
>> 
>> session = ...
>> metadata = sqlalchemy.MetaData()
>> s_items = sqlalchemy.Sequence('s_items', start=1, increment=1, 
>> metadata=metadata)
>> t_items = sqlalchemy.Table('t_items', metadata,
>> sqlalchemy.Column('id', sqlalchemy.Integer, s_items, primary_key = True),
>> )
>> metadata.drop_all(bind=session.bind)
>> metadata.create_all(bind=session.bind)
>> 
>> class Item(object):
>> pass
>> 
>> sqlalchemy.orm.mapper(Item, t_items)
>> 
>> item1 = Item()
>> item2 = Item()
>> session.add_all([item1, item2])
>> session.flush()
>> 
>> This code, with 1.4 only, triggers the following traceback:
>> 
>> Traceback (most recent call last):
>>   File "C:\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1672, 
>> in _execute_context
>> dialect, self, conn, execution_options, *args, **kw
>>   File "C:\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 
>> 999, in _init_compiled
>> self._process_executemany_defaults()
>>   File "C:\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 
>> 1838, in _process_executemany_defaults
>> if c.default and c.default.is_scalar:
>> AttributeError: 'Sequence' object has no attribute 'is_scalar'
>> 
>> The only way I found to work around this with version 1.4, is to call *flush 
>> *after each single *add*:
>> 
>> item1 = Item()
>> session.add(item1)
>> session.flush()
>> item2 = Item()
>> session.add(item2)
>> session.flush()
>> 
>> But this seems weird, to me. 
>> Am I doing anything wrong?
>> Thank you very much for any suggestion.
>> 
>> Francesca Leon 
>> 
>> 
>> -- 
>> 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/c049bcf1-ff1c-4d78-b38f-6f2dba1a87e1n%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/0d33f6cb-fb89-4272-a301-83233df6c8b2%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/540a63a6-cad2-4b33-8a53-4844867c4cc1%40www.fastmail.com.

Re: [sqlalchemy] SqlAlchemy 1.4 and Sequences

[Mike Bayer]

Hi -

Your test case is omitting a critical detail which we would assume is you are 
using the "implicit_returning=False" flag on your create_engine().dont use 
this flag as it serves no useful purpose and that will fix your issue here, we 
will be deprecating and removing this flag.

will try to fix the immediate issue if possible.


On Tue, Aug 31, 2021, at 3:29 AM, Francesca L wrote:
> Hi group, 
> 
> I am tring to migrate from version *1.3.24* to *1.4.23* of SqlAlchemy, using 
> *PostgreSQL 10*.
> 
> I found that the following code example works with 1.3, but triggers a 
> traceback with 1.4.
> 
> import sqlalchemy
> 
> session = ...
> metadata = sqlalchemy.MetaData()
> s_items = sqlalchemy.Sequence('s_items', start=1, increment=1, 
> metadata=metadata)
> t_items = sqlalchemy.Table('t_items', metadata,
> sqlalchemy.Column('id', sqlalchemy.Integer, s_items, primary_key = True),
> )
> metadata.drop_all(bind=session.bind)
> metadata.create_all(bind=session.bind)
> 
> class Item(object):
> pass
> 
> sqlalchemy.orm.mapper(Item, t_items)
> 
> item1 = Item()
> item2 = Item()
> session.add_all([item1, item2])
> session.flush()
> 
> This code, with 1.4 only, triggers the following traceback:
> 
> Traceback (most recent call last):
>   File "C:\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1672, 
> in _execute_context
> dialect, self, conn, execution_options, *args, **kw
>   File "C:\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 
> 999, in _init_compiled
> self._process_executemany_defaults()
>   File "C:\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 
> 1838, in _process_executemany_defaults
> if c.default and c.default.is_scalar:
> AttributeError: 'Sequence' object has no attribute 'is_scalar'
> 
> The only way I found to work around this with version 1.4, is to call *flush 
> *after each single *add*:
> 
> item1 = Item()
> session.add(item1)
> session.flush()
> item2 = Item()
> session.add(item2)
> session.flush()
> 
> But this seems weird, to me. 
> Am I doing anything wrong?
> Thank you very much for any suggestion.
> 
> Francesca Leon 
> 
> 
> -- 
> 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/c049bcf1-ff1c-4d78-b38f-6f2dba1a87e1n%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/0d33f6cb-fb89-4272-a301-83233df6c8b2%40www.fastmail.com.

[sqlalchemy] Using SQLAlchemy to check if column is in numeric ranges

[chat...@gmail.com]

Hello All!! , I have a list of age ranges i.e 18-25, 40-55 and more how can 
I make make a query using *SQLAlchemy* that will check if a Column is in 
ranges [18,25] OR [40-55] OR [60-70]
Regards,Christos

-- 
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/e6a8019c-beb4-482a-9262-b752391a16f4n%40googlegroups.com.

Re: [sqlalchemy] Selectinload and bindparam

[Tomas Pavlovsky]

Sorry, once again :)

class X(Base):
id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
a = relationship(A)

s = select(A).where(A.id == 1)
s = s.options(joinedload(A.b_filtered_rel))
or
s = 
select(X).options(joinedload(X.a).options(joinedload(A.b_filtered_rel))) 
result = session.execute(s, dict(c_global_attr_1=1))

s = s.options(*selectionload*(A.b_filtered_rel))
or
s = select(X).options(*selectionload*
(X.a).options(joinedload(A.b_filtered_rel))) 
or
s = select(X).options(joinedload(X.a).options(*selectionload*
(A.b_filtered_rel))) 
result = session.execute(s, dict(c_global_attr_1=1))
This query will fail, beacuse only params passed to query are related Pks.
What do you think about ability to pass some arguments to query emitted 
with selectinload?

wtorek, 31 sierpnia 2021 o 10:34:55 UTC+2 Tomas Pavlovsky napisał(a):

> Hello Mike,
>
> Thank you very much for your response. 
> I ended up with that, but i wanna ask you about this solution.
>
> class A(Base):
> id: int = Column('a_id', Integer, primary_key=True, autoincrement=True)
>
> #descr
> class B(Base):
> id: int = Column('b_id', Integer, primary_key=True, autoincrement=True)
> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
> c_relationship = relationship("C", secondary='b_rel_c', lazy='joined', 
> uselist=False)
>
> #descr_cat
> class C(Base):
> id: int = Column('c_id', Integer, primary_key=True, autoincrement=True)
> c_attr1: int = Column(Integer, nullable=False)
> c_global_attr_1: int = Column(Integer, nullable=False)
>
> class B_rel_C(Base):
> b_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
> c_id: int = Column('a_id', Integer, ForeignKey('c.c_id'))
> some_kw: int = Column(Integer)
>
> class CustomSelectClass(Base):
> __table__ = select(B, C.c_global_attr_1, C.c_attr1).join(B_rel_C, 
> B_rel_C.b_id == B.id). \
> join(C, C.id == B_rel_C.c_id).where(
> B.a_id == A.id,
> C.c_global_attr_1 == bindparam('c_global_attr_1')). \
> limit(100).subquery().lateral()
>
> A.b_filtered_rel = relationship(CustomSelectClass, lazy='joined')
>
> class X(Base):
> id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
> a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
>
> I use this aproach also with another schema like this:  X -> A -> A_REL_B 
> <- B -> C 
>
> s = select(A).where(A.id == 1)
> s = s.options(joinedload(A.b_filtered_rel))
> or
> s = select(X).options(joinedload(A).options(joinedload(B))) 
> result = session.execute(s, dict(c_global_attr_1=1))
>
> s = s.options(*selectionload*(A.b_filtered_rel))
> or
> s = select(X).options(*selectionload*(A).options(joinedload(B))) 
> or
> s = select(X).options(joinedload(A).options(*selectionload*(B))) 
> result = session.execute(s, dict(c_global_attr_1=1))
>
> What do you think about ability to pass some arguments to query emitted 
> with selectinload
>
> poniedziałek, 30 sierpnia 2021 o 17:50:49 UTC+2 Mike Bayer napisał(a):
>
>> yes, you would use bindparam("some_name") in conjunction with the 
>> "and_()" feature, like  options(selectinload(MyClass.foo.and_(MyClass.name 
>> == bindparam("some_name"
>>
>>
>>
>> On Mon, Aug 30, 2021, at 10:55 AM, Tomas Pavlovsky wrote:
>>
>> Hello, 
>>
>> is it possible in sqlalchemy arguments from db.execute(query, args) to be 
>> passed to query emitted by selectinload?
>>
>> Thanks,
>> Tomas
>>
>>
>> -- 
>> 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/7cbbfb10-1c4f-4369-a30d-e83cee262d16n%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/493a25e1-6e76-4ab3-a1ce-94f3ec8f206bn%40googlegroups.com.

Re: [sqlalchemy] Selectinload and bindparam

[Tomas Pavlovsky]

Hello Mike,

Thank you very much for your response. 
I ended up with that, but i wanna ask you about this solution.

class A(Base):
id: int = Column('a_id', Integer, primary_key=True, autoincrement=True)

#descr
class B(Base):
id: int = Column('b_id', Integer, primary_key=True, autoincrement=True)
a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
c_relationship = relationship("C", secondary='b_rel_c', lazy='joined', 
uselist=False)

#descr_cat
class C(Base):
id: int = Column('c_id', Integer, primary_key=True, autoincrement=True)
c_attr1: int = Column(Integer, nullable=False)
c_global_attr_1: int = Column(Integer, nullable=False)

class B_rel_C(Base):
b_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))
c_id: int = Column('a_id', Integer, ForeignKey('c.c_id'))
some_kw: int = Column(Integer)

class CustomSelectClass(Base):
__table__ = select(B, C.c_global_attr_1, C.c_attr1).join(B_rel_C, 
B_rel_C.b_id == B.id). \
join(C, C.id == B_rel_C.c_id).where(
B.a_id == A.id,
C.c_global_attr_1 == bindparam('c_global_attr_1')). \
limit(100).subquery().lateral()

A.b_filtered_rel = relationship(CustomSelectClass, lazy='joined')

class X(Base):
id: int = Column('x_id', Integer, primary_key=True, autoincrement=True)
a_id: int = Column('a_id', Integer, ForeignKey('a.a_id'))

I use this aproach also with another schema like this:  X -> A -> A_REL_B 
<- B -> C 

s = select(A).where(A.id == 1)
s = s.options(joinedload(A.b_filtered_rel))
or
s = select(X).options(joinedload(A).options(joinedload(B))) 
result = session.execute(s, dict(c_global_attr_1=1))

s = s.options(*selectionload*(A.b_filtered_rel))
or
s = select(X).options(*selectionload*(A).options(joinedload(B))) 
or
s = select(X).options(joinedload(A).options(*selectionload*(B))) 
result = session.execute(s, dict(c_global_attr_1=1))

What do you think about ability to pass some arguments to query emitted 
with selectinload

poniedziałek, 30 sierpnia 2021 o 17:50:49 UTC+2 Mike Bayer napisał(a):

> yes, you would use bindparam("some_name") in conjunction with the "and_()" 
> feature, like  options(selectinload(MyClass.foo.and_(MyClass.name == 
> bindparam("some_name"
>
>
>
> On Mon, Aug 30, 2021, at 10:55 AM, Tomas Pavlovsky wrote:
>
> Hello, 
>
> is it possible in sqlalchemy arguments from db.execute(query, args) to be 
> passed to query emitted by selectinload?
>
> Thanks,
> Tomas
>
>
> -- 
> 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/7cbbfb10-1c4f-4369-a30d-e83cee262d16n%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/9ee6cacf-14e6-4e3a-9a93-f01850b9c91fn%40googlegroups.com.

[sqlalchemy] SqlAlchemy 1.4 and Sequences

[Francesca L]

 Hi group, 

I am tring to migrate from version *1.3.24* to *1.4.23* of SqlAlchemy, 
using *PostgreSQL 10*.

I found that the following code example works with 1.3, but triggers a 
traceback with 1.4.

import sqlalchemy

session = ...
metadata = sqlalchemy.MetaData()
s_items = sqlalchemy.Sequence('s_items', start=1, increment=1, 
metadata=metadata)
t_items = sqlalchemy.Table('t_items', metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, s_items, primary_key = 
True),
)
metadata.drop_all(bind=session.bind)
metadata.create_all(bind=session.bind)

class Item(object):
pass

sqlalchemy.orm.mapper(Item, t_items)

item1 = Item()
item2 = Item()
session.add_all([item1, item2])
session.flush()

This code, with 1.4 only, triggers the following traceback:

Traceback (most recent call last):
  File "C:\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 
1672, in _execute_context
dialect, self, conn, execution_options, *args, **kw
  File "C:\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 
999, in _init_compiled
self._process_executemany_defaults()
  File "C:\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 
1838, in _process_executemany_defaults
if c.default and c.default.is_scalar:
AttributeError: 'Sequence' object has no attribute 'is_scalar'

The only way I found to work around this with version 1.4, is to call *flush 
*after each single *add*:

item1 = Item()
session.add(item1)
session.flush()
item2 = Item()
session.add(item2)
session.flush()

But this seems weird, to me. 
Am I doing anything wrong?
Thank you very much for any suggestion.

Francesca Leon 

-- 
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/c049bcf1-ff1c-4d78-b38f-6f2dba1a87e1n%40googlegroups.com.