[sqlalchemy] Re: Question about the other property of backref

[尤立宇]

Thanks for your response.

Do you consider using `backref` only on one of the class bad practice?

I'm curious because automatically creating descriptors seems possible to me, 
and I'm wondering when it happens.

As documentation states so:

Remember, when the backref keyword is used on a single relationship, it’s 
exactly the same as if the above two relationships were created individually 
using back_populates on each.

ref: http://docs.sqlalchemy.org/en/latest/orm/backref.html

There is also a half-a-year-old stackoverflow question about it:

http://stackoverflow.com/questions/32617371/how-to-force-creation-of-backref-instrumentedattributes-using-sqlalchemy

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Interaction between joined table inheritance and subquery correlate

[Andrew Wansley]

Hey y'all,

I'm trying to understand the interaction between joined table inheritance 
and a correlated subquery.

The query seems to work as I expect if the same table doesn't have a joined 
table subclass, or if I circumvent the polymorphic code by querying 
Parent.__table__ directly, but not if I query the declarative class 
directly.

Roughly, we want to query some kind of user action, joining in the most 
immediately preceding event for the user who performed that action. We 
select the event with a correlated subquery.

See the attached file for an isolated test case.

(Tested on SqlAlchemy 1.0.12 with Postgres 9.3.5 as well as Sqlite 2.6.0)

I'm just as happy to hear "You're doing it wrong! Here's how to re-write 
your query" as I am to a clear explanation of why this difference exists..

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import Column, Integer, ForeignKey, String, DateTime
from sqlalchemy import and_, func
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import arrow

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()


class Parent(Base):
  __tablename__ = 'parent_objs'

  id = Column(Integer, primary_key=True)
  obj_type = Column(String, nullable=False)
  user_id = Column(Integer, ForeignKey('user_objs.id'))
  time = Column(DateTime)

  __mapper_args__ = {
'polymorphic_on': obj_type,
'with_polymorphic': '*'
  }


class Child(Parent):
  __tablename__ = 'child_objs'

  id = Column(Integer, ForeignKey('parent_objs.id'), primary_key=True)

  __mapper_args__ = {
'polymorphic_identity': 'child',
  }


class NonInherited(Base):
  __tablename__ = "not_inherited_objs"

  id = Column(Integer, primary_key=True)
  user_id = Column(Integer, ForeignKey('user_objs.id'))
  time = Column(DateTime)


class User(Base):
  __tablename__ = 'user_objs'

  id = Column(Integer, primary_key=True)


class UserEvent(Base):
  __tablename__ = 'user_events'

  id = Column(Integer, primary_key=True)
  time = Column(DateTime)
  user_id = Column(Integer, ForeignKey('user_objs.id'))


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

session = Session()
twenty_hours_ago = arrow.utcnow().replace(hours=-20).naive
ten_hours_ago = arrow.utcnow().replace(hours=-10).naive
just_after_ten_hours_ago = arrow.utcnow().replace(hours=-10, seconds=+1).naive
five_hours_ago = arrow.utcnow().replace(hours=-5).naive
just_after_five_hours_ago = arrow.utcnow().replace(hours=-5, seconds=+1).naive
now = arrow.utcnow().naive


u = User(id=1)
session.add(u)
session.commit()

ue_1 = UserEvent(id=1, user_id=u.id, time=twenty_hours_ago)
ue_2 = UserEvent(id=2, user_id=u.id, time=ten_hours_ago)
ue_3 = UserEvent(id=3, user_id=u.id, time=five_hours_ago)
ue_4 = UserEvent(id=4, user_id=u.id, time=now)

child_1 = Child(id=1, time=just_after_ten_hours_ago, user_id=u.id)
child_2 = Child(id=2, time=just_after_five_hours_ago, user_id=u.id)

non_inherited_1 = NonInherited(id=1, time=just_after_ten_hours_ago, user_id=u.id)
non_inherited_2 = NonInherited(id=2, time=just_after_five_hours_ago, user_id=u.id)

map(session.add, [ue_1, ue_2, ue_3, ue_4, child_1, child_2, non_inherited_1, non_inherited_2])
session.commit()

parent_table = Parent.__table__

q_with_raw_table = (
  session.query(parent_table.c.id, UserEvent.id)
  .join(User, parent_table.c.user_id == User.id)
  .join(UserEvent, and_(
UserEvent.user_id == User.id,
UserEvent.time == session.query(
  func.max(UserEvent.time)
).filter(UserEvent.user_id == User.id)
 .filter(UserEvent.time <= parent_table.c.time)
 .correlate(User)
 .correlate(parent_table)
 .as_scalar()
  ))
)

q_with_declarative = (
  session.query(Parent.id, UserEvent.id)
  .join(User, Parent.user_id == User.id)
  .join(UserEvent, and_(
UserEvent.user_id == User.id,
UserEvent.time == session.query(
  func.max(UserEvent.time)
).filter(UserEvent.user_id == User.id)
 .filter(UserEvent.time <= Parent.time)
 .correlate(User)
 .correlate(Parent)
 .as_scalar()
  ))
)


q_with_non_inherited_table = (
  session.query(NonInherited.id, UserEvent.id)
  .join(User, NonInherited.user_id == User.id)
  .join(UserEvent, and_(
UserEvent.user_id == User.id,

UserEvent.time == session.query(
  func.max(UserEvent.time)
).filter(UserEvent.user_id == User.id)
 .filter(UserEvent.time <= NonInherited.time)
 .correlate(User)
 .correlate(NonInherited)
 .as_scalar()

  ))
)

print q_with_raw_table.all()
print 

[sqlalchemy] update instance.relation.attr in instance.attr "set event listener"

[sector119]

Hello!

I have two models, Product and ProductFlavor with one-to-many relationship
And I have a listener, which I want to update Product.quantity on 
ProductFlavor.quantity change:

@event.listens_for(ProductFlavor.quantity, 'set')
def quantity_set(target, value, oldvalue, initiator):
if value != oldvalue:
target.product.quantity += (value - oldvalue)


But I get the following error:


ProgrammingError: (raised as a result of Query-invoked autoflush; consider 
using a session.no_autoflush block if this flush is occurring prematurely) 

(psycopg2.ProgrammingError) can't adapt type 'instance' [SQL: 'UPDATE 
product_flavor SET label=%(label)s WHERE product_flavor.id = 
%(product_flavor_id)s'] [parameters: {'product_flavor_id': 4, 'label': 
FieldStorage('label', u'42bbebd1f7ba46b58d3d4b794b4b890e.png')}]

What I'm doing wrong?

Thank You!

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Relationship between two databases

[Mehdi]

>
> I don't think that the solution can come from SQLAlchemy.
> You may have to create a database link between your two databases to be 
> able to query both of them with a single instruction...
>

Thanks for the tip. i'm working on dblink and it seems promising.
 

> I'm not sure it's doable either... but I wanted to point out something:
>
> class WeatherStation(MainBase):
> class EvaporationData(Base):
>
> They are inheriting from different bases, and `Base` is not associated to 
> the same metadata as `MainBase`
>

Sorry it was a typo.
 

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Dependency rule tried to blank-out primary key column when trying to update using association proxy

[Piotr Dobrogost]

On Wednesday, February 24, 2016 at 4:07:07 PM UTC+1, Mike Bayer wrote:
>
>
> There might be an old issue in bitbucket that the way associationproxy 
> insists on calling clear() without any hook to override, the AP has a 
> lot of old override hooks that are complicated and difficult to use, and 
> then don't even cover this case.  So the AP is just not great in this 
> area and I'd prefer subclassing is not the idiomatic solution to this. 
>

I found this one – "try to add a real "replace collection" to association 
proxy" (https://bitbucket.org/zzzeek/sqlalchemy/issues/2642/)

Regards,
Piotr

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] MSSQL ProgrammingError with aggregate functions

[Alex Lowe]

Thank you! That worked, with one minor modification.

It looks like MSSQL requires an alias for an anonymous table (even if 
you're not joining it with anything). As such, I needed to add .alias() to 
the query.

In the end, it looked like this:

q3 = sqlalchemy.select([partial.label('left_string'), 
different_column]).alias()
q4 = sqlalchemy.select([q3.c.left_string, 
func.sum(q3.c.different_column)]).group_by(q3.c.left_string)
sql_engine.execute(q4).fetchall()

On Wednesday, 24 February 2016 09:26:59 UTC-6, Mike Bayer wrote:
>
>
>
> On 02/24/2016 10:13 AM, Alex Lowe wrote: 
> > Hi there, 
> > 
> > I'm receiving a ProgrammingError with certain types of query to MSSQL 
> > (they seem to work fine when querying SQLite though). Either my 
> > Google-fu is weak or there hasn't been a solution posted publicly, since 
> > the two most useful-looking pages were these two StackOverflow threads 
> > with no useful responses: 
> > 
> http://stackoverflow.com/questions/18307466/group-by-case-in-sql-server-sqlalchemy
>  
> > 
> http://stackoverflow.com/questions/21742713/need-a-query-in-sqlalchemy-with-group-by-case
>  
> > 
> > I'm also very new to SQLAlchemy and have mostly picked it up through a 
> > combination of web searches and following the examples of my coworkers 
> > (who picked it up by doing web searches for what they needed), so advice 
> > on how to make my example code better are welcome. 
>
> I've certainly had to work around this problem but the news that the raw 
> string works is new to me, but I would assume it has something to do 
> with the removal of bound parameters.  ODBC actually has two different 
> execution APIs internally that interpret the given statement 
> differently, one is much more picky about being able to infer the type 
> of bound parameters, so that might be part of what's going on. 
>
> If i recall correctly the workaround is to make a subquery like this: 
>
> SELECT left_1, sum(different_column) FROM 
> ( 
>SELECT left(some_string, ?) AS left_1, different_column 
>FROM [DEV].dbo.[AML_Test] 
> ) GROUP BY left_1 
>
>
> so, paraphrasing 
>
> stmt = select([func.left(table.c.some_string, 5).label('left'), 
> table.c.different_column]) 
>
> stmt = select([stmt.c.left, 
> func.sum(stmt.c.different_column]).group_by(stmt.c.left) 
>
>
>
> > 
> > I've got a table that contains a string column and an integer column, 
> > and I'm trying to group by substrings. In so doing, it brings up an 
> > error message about aggregate functions in the group by clause. 
> > Specifically, if I write this code: 
> > 
> > test_table = sqlalchemy.Table('AML_Test', dev_schema) 
> > some_string = sqlalchemy.Column('some_string', 
> > sqlalchemy.VARCHAR(length=50)) 
> > different_column = sqlalchemy.Column('different_column', 
> sqlalchemy.INT()) 
> > partial = func.left(some_string, 3) 
> > aggregate = func.sum(different_column) 
> > qq = 
> test_table.select().group_by(partial).column(partial).column(aggregate) 
> > 
> > and then run qq.execute(), pyodbc gives me the follow error message: 
> > 
> > ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] 
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Column 
> > 'DEV.dbo.AML_Test.some_string' is invalid in the select list because it 
> > is not contained in either an aggregate function or the GROUP BY clause. 
> > (8120) (SQLExecDirectW)") [SQL: 'SELECT left(some_string, ?) AS left_1, 
> > sum(different_column) AS sum_1 \nFROM [DEV].dbo.[AML_Test] GROUP BY 
> > left(some_string, ?)'] [parameters: (3, 3)] 
> > 
> > 
> > My workaround for the moment is to cast the compiled statement to a 
> > string and execute that string, but it's unclear to me why that would do 
> > anything different (despite the fact that it does). 
> > 
> > 
> > c.session.execute( 
> > str(qq.selectable.compile(compile_kwargs={'literal_binds': True})) 
> > ).fetchall() 
> > 
> > 
> > If anyone can explain to me what I'm doing wrong and how to fix it, I'd 
> > be extremely grateful. 
> > 
> > 
> > Thanks, 
> > 
> > 
> > Alex 
> > 
> > -- 
> > 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 post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit 

[sqlalchemy] Re: Question about the other property of backref

[Jonathan Vanasco]

So in your example (simplified below)

class User(Base):
...

class Address(Base):
...
  user = relationship('User', backref='addresses')

when you create `Address.user` the `backref` command automatically creates 
the "other" side of the relationship -- `User.addresses`.

Another way to generate the same relationships would be:

class User(Base):
...
  user = relationship('Address', back_populates='user')

class Address(Base):
...
  user = relationship('User', back_populates ='addresses')


Many people "start out" with the first form, because automatically creating 
the relationship is very convenient.  As projects grow, it becomes much 
more convenient to use back_populates so you can see - and ensure - each 
side of the relationship on the appropriate class.  (Using `backref`, your 
relationship is only documented/appears in one class) 

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Question about the other property of backref

[尤立宇]

Following the ORM tutorial of `User` and `Address`,
if I configure a `user` attribute on `Address`:

class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))

user = relationship('User', backref='addresses')

`User.addresses` is not available until I initiate a `User` or `Address` 
object.

According to the documentation of backref 

:

indicates the string name of a property to be placed on the related 
mapper’s class that will handle this relationship in the other direction. *The 
other property will be created automatically when the mappers are 
configured.*


What does it mean?

I'm using version 1.0.12 on Python 3.5.1

Thanks

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] MSSQL ProgrammingError with aggregate functions

[Mike Bayer]

On 02/24/2016 10:13 AM, Alex Lowe wrote:

Hi there,

I'm receiving a ProgrammingError with certain types of query to MSSQL
(they seem to work fine when querying SQLite though). Either my
Google-fu is weak or there hasn't been a solution posted publicly, since
the two most useful-looking pages were these two StackOverflow threads
with no useful responses:
http://stackoverflow.com/questions/18307466/group-by-case-in-sql-server-sqlalchemy
http://stackoverflow.com/questions/21742713/need-a-query-in-sqlalchemy-with-group-by-case

I'm also very new to SQLAlchemy and have mostly picked it up through a
combination of web searches and following the examples of my coworkers
(who picked it up by doing web searches for what they needed), so advice
on how to make my example code better are welcome.


I've certainly had to work around this problem but the news that the raw 
string works is new to me, but I would assume it has something to do 
with the removal of bound parameters.  ODBC actually has two different 
execution APIs internally that interpret the given statement 
differently, one is much more picky about being able to infer the type 
of bound parameters, so that might be part of what's going on.


If i recall correctly the workaround is to make a subquery like this:

SELECT left_1, sum(different_column) FROM
(
  SELECT left(some_string, ?) AS left_1, different_column
  FROM [DEV].dbo.[AML_Test]
) GROUP BY left_1


so, paraphrasing

stmt = select([func.left(table.c.some_string, 5).label('left'), 
table.c.different_column])


stmt = select([stmt.c.left, 
func.sum(stmt.c.different_column]).group_by(stmt.c.left)






I've got a table that contains a string column and an integer column,
and I'm trying to group by substrings. In so doing, it brings up an
error message about aggregate functions in the group by clause.
Specifically, if I write this code:

test_table = sqlalchemy.Table('AML_Test', dev_schema)
some_string = sqlalchemy.Column('some_string',
sqlalchemy.VARCHAR(length=50))
different_column = sqlalchemy.Column('different_column', sqlalchemy.INT())
partial = func.left(some_string, 3)
aggregate = func.sum(different_column)
qq = test_table.select().group_by(partial).column(partial).column(aggregate)

and then run qq.execute(), pyodbc gives me the follow error message:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Column
'DEV.dbo.AML_Test.some_string' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY clause.
(8120) (SQLExecDirectW)") [SQL: 'SELECT left(some_string, ?) AS left_1,
sum(different_column) AS sum_1 \nFROM [DEV].dbo.[AML_Test] GROUP BY
left(some_string, ?)'] [parameters: (3, 3)]


My workaround for the moment is to cast the compiled statement to a
string and execute that string, but it's unclear to me why that would do
anything different (despite the fact that it does).


c.session.execute(
str(qq.selectable.compile(compile_kwargs={'literal_binds': True}))
).fetchall()


If anyone can explain to me what I'm doing wrong and how to fix it, I'd
be extremely grateful.


Thanks,


Alex

--
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 post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Asymmetry between engine.begin() and connection.begin()

[Jonathan Beluch]

Wonderful, thanks.

On Wednesday, February 24, 2016 at 7:50:53 AM UTC-7, Mike Bayer wrote:
>
> get the connectable first: 
>
> connection = engine_or_connection.connect() 
>
> then do your context manager from that. 
>
> the connection returned, if engine_or_connection is already a 
> Connection, is "branched", meaning it is safe to call close() on it 
> without affecting the original. 
>
> So fully: 
>
> with engine_or_connection.connect() as conn: 
>  with conn.begin() as trans: 
>  # etc. 
>
>
> On 02/23/2016 11:05 PM, Jonathan Beluch wrote: 
> > Is there a better way of doing this? Basically I have a function that 
> > takes a connectable (engine or connection) and I want to start a 
> > transaction. However I can't just call .begin() because it could return 
> > a Transaction or a Connection depending on what was passed in and I need 
> > a connection. 
> > 
> > @contextlib.contextmanager 
> > def _transaction(connectable): 
> >  if hasattr(connectable, 'in_transaction') and 
> > connectable.in_transaction(): 
> >  yield connectable 
> >  else: 
> >  with connectable.begin() as conn: 
> >  yield conn 
> > 
> > -- 
> > 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 post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] MSSQL ProgrammingError with aggregate functions

[Alex Lowe]

Hi there,

I'm receiving a ProgrammingError with certain types of query to MSSQL (they 
seem to work fine when querying SQLite though). Either my Google-fu is weak 
or there hasn't been a solution posted publicly, since the two most 
useful-looking pages were these two StackOverflow threads with no useful 
responses:
http://stackoverflow.com/questions/18307466/group-by-case-in-sql-server-sqlalchemy
http://stackoverflow.com/questions/21742713/need-a-query-in-sqlalchemy-with-group-by-case

I'm also very new to SQLAlchemy and have mostly picked it up through a 
combination of web searches and following the examples of my coworkers (who 
picked it up by doing web searches for what they needed), so advice on how 
to make my example code better are welcome.

I've got a table that contains a string column and an integer column, and 
I'm trying to group by substrings. In so doing, it brings up an error 
message about aggregate functions in the group by clause. Specifically, if 
I write this code:

test_table = sqlalchemy.Table('AML_Test', dev_schema)
some_string = sqlalchemy.Column('some_string', 
sqlalchemy.VARCHAR(length=50))
different_column = sqlalchemy.Column('different_column', sqlalchemy.INT())
partial = func.left(some_string, 3)
aggregate = func.sum(different_column)
qq = test_table.select().group_by(partial).column(partial).column(aggregate)

and then run qq.execute(), pyodbc gives me the follow error message:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC 
SQL Server Driver][SQL Server]Column 'DEV.dbo.AML_Test.some_string' is invalid 
in the select list because it is not contained in either an aggregate function 
or the GROUP BY clause. (8120) (SQLExecDirectW)") [SQL: 'SELECT 
left(some_string, ?) AS left_1, sum(different_column) AS sum_1 \nFROM 
[DEV].dbo.[AML_Test] GROUP BY left(some_string, ?)'] [parameters: (3, 3)]


My workaround for the moment is to cast the compiled statement to a string and 
execute that string, but it's unclear to me why that would do anything 
different (despite the fact that it does).


c.session.execute(
str(qq.selectable.compile(compile_kwargs={'literal_binds': True}))
).fetchall()


If anyone can explain to me what I'm doing wrong and how to fix it, I'd be 
extremely grateful.


Thanks,


Alex

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Dependency rule tried to blank-out primary key column when trying to update using association proxy

[Mike Bayer]

On 02/24/2016 09:56 AM, Piotr Dobrogost wrote:

On Wednesday, February 24, 2016 at 4:21:58 AM UTC+1, Mike Bayer wrote:


in that way you can control exactly what __set__() does and it will
never remove a TextValue object where the same identity is coming in on
assignment.


One more question; is this described somewhere in the docs? I don't see
it
at http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html



There might be an old issue in bitbucket that the way associationproxy 
insists on calling clear() without any hook to override, the AP has a 
lot of old override hooks that are complicated and difficult to use, and 
then don't even cover this case.  So the AP is just not great in this 
area and I'd prefer subclassing is not the idiomatic solution to this.







Regards,
Piotr

--
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 post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Dependency rule tried to blank-out primary key column when trying to update using association proxy

[Piotr Dobrogost]

On Wednesday, February 24, 2016 at 4:21:58 AM UTC+1, Mike Bayer wrote:


in that way you can control exactly what __set__() does and it will 
> never remove a TextValue object where the same identity is coming in on 
> assignment. 
>

One more question; is this described somewhere in the docs? I don't see it 
at http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html


Regards,
Piotr

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Asymmetry between engine.begin() and connection.begin()

[Mike Bayer]

get the connectable first:

connection = engine_or_connection.connect()

then do your context manager from that.

the connection returned, if engine_or_connection is already a 
Connection, is "branched", meaning it is safe to call close() on it 
without affecting the original.


So fully:

with engine_or_connection.connect() as conn:
with conn.begin() as trans:
# etc.


On 02/23/2016 11:05 PM, Jonathan Beluch wrote:

Is there a better way of doing this? Basically I have a function that
takes a connectable (engine or connection) and I want to start a
transaction. However I can't just call .begin() because it could return
a Transaction or a Connection depending on what was passed in and I need
a connection.

@contextlib.contextmanager
def _transaction(connectable):
 if hasattr(connectable, 'in_transaction') and
connectable.in_transaction():
 yield connectable
 else:
 with connectable.begin() as conn:
 yield conn

--
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 post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Custom collection: nested dicts

[Sergey Mozgovoy]

Thanks Mike, I'll contact the sqlalchemy-utils folks.


Sergey

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Can I make bulk update through association proxy?

[Piotr Dobrogost]

Hi!

Let's say I have a model Text with attribute "values" which is association 
proxy.
I can update single object like this:
text = session.query(Text).one()
text.values.update(...)

How can I update multiple objects with the same value without manually 
looping over result of a query?
texts = session.query(Text).all()
texts???values???.update(...)


Regards,
Piotr Dobrogost

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Dependency rule tried to blank-out primary key column when trying to update using association proxy

[Piotr Dobrogost]

On Wednesday, February 24, 2016 at 4:21:58 AM UTC+1, Mike Bayer wrote:

in that way you can control exactly what __set__() does and it will 
> never remove a TextValue object where the same identity is coming in on 
> assignment. 
>

Mike, thank you very much for your help.
 
Regards,
Piotr

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: SQLAlchemy dynamic & customized Collection Class

[Edouard BERTHE]

Thank you very much ! It's perfectly working :)

Ed

Le mardi 23 février 2016 16:41:32 UTC+10, Edouard BERTHE a écrit :
>
> Hello everybody !
>
> This is a question I have already posted on Stack Overflow 
> ,
>  
> but as none is answering I decided to post it here too :) However I invite 
> you to go to the Stack Overflow page, as it is maybe more clearly explained 
> than here.
>
> I have a One-To-Many relation between an Article entity and a Post entity 
> (which are the comments on the article).
> Each Post has a date attribute.
> What I would like to do is getting the posts related to an article between 
> two dates directly from this article, using the following syntax :
>
> article.posts[start_date:end_date]
>
> With the "lazy" argument or "relationship", I can do :
>
> posts = relationship('Post', back_populates='article', lazy='dynamic')
>
> After that, the "posts" attribute isn't a list any longer, but a Query 
> Object, which allows us to do:
>
> article.posts.filter(Post.date >= start_date, Post.date < 
> end_date).all()
>
> Which is still not exactly what I'm looking for.
>
> I think I have to use the "collection_class" attribute of the 
> relationship, by using a custom Class in which I would override the 
> "__getitem__" method, but I don't know what to write in this function, 
> because we don't have access to the query given by the `relationship` 
> Object !
>
> Does someone have an idea which could help me ?
>
> Thank you !
> Edouard
>

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.