[sqlalchemy] Befuddled with FK, ondelete, and delete cascade

[Jens Troeger]

Hello,

I have a parent and a child class defined like so:

# Parent table.
class User(Base):
__tablename__ = "users"
id = Column(Integer)

# Child table.
class Stat(Base):
__tablename__ = "stats"
id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), 
nullable=False)
user_id = Column(Integer)

# Useful ORM relationship.
user = relationship("User", backref=backref("stats"))

When I deleted a user, I got the following error:

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1048, "Column 
'user_id' cannot be null")
[SQL: UPDATE stats SET user_id=%(user_id)s WHERE stats.id = %(stats_id)s]
[parameters: {'user_id': None, 'stats_id': '13'}]

Good, because I don’t want orphaned stats when the user is being deleted 
and the `nullable=False` for the `stats.id` column caught the issue.

However, that means that the foreign key cascade didn’t work. From the docs 
it looks like the default cascade for a relationship is "safe-update, 
merge" (link 
)
 
and that seems to ignore the fk cascade and instead attempts to set the 
foreign keys to NULL. Next, as per this SO discussion 
 I 
added `passive_deletes=True` and next `passive_deletes="all"` (docs 
)
 
in the hopes that the foreign key cascade works — to no avail.

When I the set the parameter `cascade="all, delete-orphan"` on the 
backref() then deleting worked. However, that’s on the ORM level and still 
avoids the foreign key cascade.

What am I missing here? How do I get the foreign key cascade to work?

Much thanks,
Jens

-- 
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/c9e846e5-ba6e-43b5-bbb1-34a1f612ab60o%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jens Troeger]

Thanks Jonathan! Yes, all classes derive from the declarative base:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(metadata=MetaData(naming_convention={...}))

class Parent(Base):
...

class Child(Base):
...

If I understand you correctly, then the solution above is as good as it 
gets and SQLA doesn’t provide a builtin solution for what I’m trying to do?

I’m just curious if there is a better solution to this, or if perhaps my 
design could be be improved in general 🤔

Much thanks,
Jens

>

-- 
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/1bcd7db4-e8f2-4bd1-b087-e51d110c9cf5%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jens Troeger]

So, here’s what I’m experimenting with and it seems to work:

@property
def children(self):
children = (getattr(self, r.key) for r in 
self.__mapper__.relationships if r.target.name == "child")
return [c for c in children if c is not None]

I’m not sure if this is the proper way to work with SQLA’s internals, but 
it gives me a list of children 🤓

-- 
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/f9bc3585-d907-4058-8b87-31f6b56f3d9f%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jens Troeger]

Thank you, Jonathan.

I’ve used SQLA’s association proxies before, I’ll take a look again.

You bring up a good point, though:

Ok, so this isn't a one-to-one relationship, but a many-to-many 
> relationship.
>

That’s something I’ve been debating with myself for a while before I posted 
here: the kind of relationship here. A Child can have only a single Parent, 
but a Parent has multiple Children. At first is looks like a 1-to-many, but 
the oddity is the “type” of the Child expressed through a named foreign key 
constraint: “youngest_child” and “oldest_child” are the examples here. The 
reason why it’s done this way is because a Child should not have knowledge 
of its type and how the Parent views the Child.

It’s always possible to explicitly enumerate the Child objects on the 
Parent:

children = [youngest_child, oldest_child]

but I am curious if there is a better way to do that, one that involves 
less typing and would pick changes (e.g. adding a “shortest_child” or some 
such) transparently.

Cheers,
Jens

-- 
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/26803606-3d2e-48f1-b10a-2ac07cd23e94%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jens Troeger]

Hi Jonathan,

The line you commented out from the example was either:
>
> children = relationship("Child")
>
> children = relationship("Child", back_populates="parent")
>
>
> both of those lines create an iterable list of all the Child objects on 
> the `children`
>

Neither of them would work, because

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join 
condition between parent/child tables on relationship Parent.children - 
there are multiple foreign key paths linking the tables.  Specify the 
'foreign_keys' argument, providing a list of those columns which should be 
counted as containing a foreign key reference to the parent table.

Unfortunately, the following didn’t work either:

children = relationship("Child", foreign_keys=[oldest_child_id, 
youngest_child_id])

(Oddly enough, specifying a list with a single element *does* work.)

Off the top of my head, the simplest way to accomplish this would be to add 
> a "parent_id" column on the child table, and then create a relationship for 
> "children" that correlates the `Parent.id` to `Child.parent_id`.  
>
> That change might not work with your data model if a Child can have 
> multiple parents. 
>

 Indeed, Child does have multiple parents…

-- 
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/f548656e-75aa-4041-a505-4a272c28f9b4%40googlegroups.com.

[sqlalchemy] Consolidate multiple one-to-one into a list

[Jens Troeger]

Hello,

Taking the relationship examples from the documentation 
, suppose I 
have the following:

class Parent(Base):
__tablename__ = "parent"
id = Column(Integer, primary_key=True)

oldest_child_id = Column(Integer, ForeignKey("child.id"))
oldest_child = relationship("Child", foreign_keys=oldest_child_id)

youngest_child_id = Column(Integer, ForeignKey("child.id"))
youngest_child = relationship("Child", foreign_keys=oldest_child_id)

# children = ... 

class Child(Base):
__tablename__ = "child"
id = Column(Integer, primary_key=True)

For the sake of argument, we care for exactly two children per parent. Now 
my question is: how can I introduce a set/list of all children on the 
parent?

The naive approach would be something like

@property
def children(self):
return [self.oldest_child, self.youngest_child] # Or set(), or tuple().

In my particular case, the Child is actually a File table, and different 
other tables may have one or more Files associated. But it would be nice if 
these tables had a “files” property which is a consolidation of all their 
explicitly associated files.

Thank you!
Jens

-- 
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/b13c7494-ab23-44ba-a878-19fd7c0d1f63%40googlegroups.com.

Re: [sqlalchemy] Confusion over session.dirty, query, and flush

[jens . troeger]

I’d like to pick up this topic once more briefly.

Suppose I get the “new”, “dirty”, and “deleted” sets as per discussion 
below, and I’m especially interested in the “dirty” set: is there a way to 
find out which properties of an object were modified, or only that the 
object was modified?

Thanks!
Jens



On Wednesday, November 22, 2017 at 9:46:54 AM UTC+10, jens.t...@gmail.com 
wrote:
>
> Thank you, the event worked like a charm :-) Though I think that I don't 
> need the commit events, because the application terminates anyway.
>
> I modified your approach to gather which objects were flushed so that in 
> the end I can give the user more precise information:
>
> dbsession.info["new"] = set()
>
> dbsession.info["dirty"] = set()  
>
> dbsession.info["deleted"] = set()
>
>   
>   
> def update_session_info(session):  
>  
> new = session.info["new"]
>
> new |= set(session.new)
>  
> dirty = session.info["dirty"]
>
> dirty |= set(session.dirty)
>  
> deleted = session.info["deleted"]
>
> deleted |= set(session.deleted)
>  
> return new, dirty, deleted
>   
>   
>   
> @event.listens_for(dbsession, "before_flush")  
>  
> def on_before_flush(session, _, _):
>  
> update_session_info(session)
>
> ...
> code.interact(local=locals())  
>  
> ...
>
> new, dirty, deleted = update_session_info(dbsession)  
>   
> if new or dirty or deleted:
>  
> if new:
>  
> print("The following objects were created: ", new)
>
> if dirty:  
>  
> print("The following objects were modified: ", dirty)  
> 
> if deleted:
>  
> print("The following objects were deleted: ", deleted)
>
> yesno = input("Would you like to commit this transaction? 
> [y/N] ") 
> if yesno == "y":  
>   
> print("Committing transaction...")
>  
> else:  
>  
> print("Rolling back transaction...")  
>  
> raise _SessionRollbackException()  
>  
>
> # ...this is where the context closes and the transaction commits 
> and the dbsession ends.
>
> Cheers,
> Jens
>
>
>
> On Saturday, November 18, 2017 at 12:03:05 AM UTC+10, Simon King wrote:
>>
>> OK, I think tracking session events seems reasonable. You could do 
>> something like this (completely untested): 
>>
>> from sqalchemy.event import event 
>>
>> @event.listens_for(YourSessionOrSessionMaker, 'before_flush') 
>> def on_before_flush(session, flush_context, instances): 
>> session.info['flushed'] = True 
>>
>>
>> # You'd probably also want to reset the 'flushed' flag 
>> # after a commit or rollback 
>> @event.listens_for(YourSessionOrSessionMaker, 'after_commit') 
>> @event.listens_for(YourSessionOrSessionMaker, 'after_rollback') 
>> def on_session_reset(session): 
>> session.info['flushed'] = False 
>>
>>
>> # when user exits interactive session: 
>> modified = ( 
>> session.info.get('flushed', False) 
>> or session.deleted 
>> or session.new 
>> or session.dirty 
>> ) 
>> if modified: 
>> raw_input('do you want to

Re: [sqlalchemy] Bulk update & performance question

[jens . troeger]

Thank you Simon, that helps!

Jens


 

> I think the idea of processing the rows in chunks is to limit the 
> memory usage of the Python process. Until you call session.flush(), 
> all your modifications are held in memory. For small numbers of rows 
> this isn't a problem, but for huge numbers, it might be. 
>
> In practice, it may not actually make a difference for this example, 
> because the default session configuration is to autoflush whenever you 
> call session.query(). 
>
> As to whether this is preferred over Table.update(), it really depends 
> on your use case. Table.update() will usually be faster, because all 
> the work will be done by the database itself. If the changes you are 
> trying to make can be expressed in SQL (ie. they don't have 
> complicated dependencies or require outside information), and you are 
> happy to resynchronise your session afterwards if necessary, then 
> Table.update() is fine. 
>
> Hope that helps, 
>
> Simon 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+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] Bulk update & performance question

[jens . troeger]

Hello,

While looking for a good way to implement a bulk update to numerous rows of 
the same table, I came across this performance example 

:

session = Session(bind=engine)
for chunk in range(0, n, 1000):
customers = session.query(Customer).\
filter(Customer.id.between(chunk, chunk + 1000)).all()
for customer in customers:
customer.description += "updated"
session.flush()
session.commit()

I noticed that the flush() happens with every iteration over the chunks, 
instead of the end, right along with commit(). Why is that?

And is the above the recommended way of a bulk update, instead of e.g. 
calling Table.update() 

 
as recommended in this SO answer 

?

Thank you!
Jens

-- 
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 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] Delete, bulk delete, and Sessions—some clarification needed.

[jens . troeger]

Hello,

I’d like to get some clarification on the following two code examples. 
First, bulk delete 

 
an object:

>>> u = dbsession.query(User).first()
>>> u.id
'0078ccdf7db046179c59bff01199c25e'
>>> dbsession.query(User).filter(User.id == 
"0078ccdf7db046179c59bff01199c25e").delete()
>>> dbsession.deleted
IdentitySet([])

Note how the `deleted` set of the Session object is empty; the objects, 
however, aren’t yet deleted but will when the Session commits. The other 
way of deleting would be to delete the objects from the session 

:

>>> dbsession.delete(u)
>>> dbsession.deleted
IdentitySet([, …])

There is a warning in the documentation of “bulk delete” which says that 
“[it] is a “bulk” operation, which bypasses ORM unit-of-work automation in 
favor of greater performance”. I think this refers to the above observation 
regarding the Session’s `deleted` set, but I don’t know what “unit-of-work 
automation” refers to.

Can somebody please shed some light on the above observation?

Also, is there a way to detect objects in a Session that are queued to be 
bulk-deleted? (This would a continuation of a previous discussion “Confusion 
over session.dirty, query, and flush” 
.)

Thanks!
Jens

-- 
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 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] Poll of interest: add @properties to mapped objects or use functions instead?

[jens . troeger]

Hi,

Suppose I have the following ORM class:

class User(Base):

__tablename__= 'users'

first_name = Column(String(64), nullable=False)
last_name = Column(String(64), nullable=False)
email = Column(String(128), nullable=False)

In our project we now need the full name of a User, as well as her long 
email address (and other data derived from the ORM’s mapped/stored data). 
There are two ways to implement this:

   1. Add @property to the User object.
   2. Add helper functions to a utility module.

The discussion is now going forth and back between these two approaches.

One view is to use @property for the User class:

@property
def name(self):
return self.first_name + " " + self.last_name

@property
def long_email(self):
return "{0} <{1}>".format(self.name, self.email)

because semantically these properties “belong” to a user, keep other scopes 
clean by avoiding unnecessary imports, and they are stateless and 
read-only, and do not affect the underlying data. (Even if they did, the 
ORM should then handle these state changes consistently: e.g. assigning a 
full name to a User object.)

The other view is to use helper functions:

from orm.model import User

def get_name(user: User) -> str:
return user.first_name + " " + user.last_name

def get_email(user: User) -> str:
return "{0} <{1}>".format(self.name, self.email)

because @property add responsibility to the User object and an ORM should 
not *create* new data based from its model.

So… what are the opinions of the wider audience here? 🙃

Cheers,
Jens

-- 
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 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] QueuePool limit overflow and Pyramid sub-requests

[jens . troeger]

Thank you, Mike!  What you say makes sense, and I took this question into 
the Pylons group 
 to get 
their views and recommendations. (Unfortunately I have no control over the 
number of subrequests, and thus connections, as that’s a result of user’s 
request data.)

Jens


On Thursday, April 26, 2018 at 11:10:03 PM UTC+10, Mike Bayer wrote:
>
> I'm not familiar with this concept but it seems to me that your web 
> server can have at most N concurrent requests and that there would 
> need to be some system that also sets a limit on the number of 
> subrequests.   If you are planning to have thousands of concurrent 
> subrequests at a time and you'd like them to all use independent 
> database connections then you'd need to configure your pool to allow 
> thousands of overflow connections, however, now you need to look at 
> how many processes you will be running and how many connections your 
> database itself allows. 
>
> The point is that there are hard limits on how many connections you 
> can have to your database, which is a good thing.   Any system that 
> generates lots of database connections similarly needs to work within 
> these limits, so you'd need to plan for 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 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] QueuePool limit overflow and Pyramid sub-requests

[jens . troeger]

Hello,

I would like to understand the interplay between a SQLA session and a 
Pyramid’s subrequest 
.
 
When a request is handled, a new session is created for that request as per 
the Pyramid/SQLA cookiecutter 
, and it looks to 
me like subrequests create a new session too.

When I set the pool_size 

 of 
the engine to *N* and max_overflow 

 
to *M* then I can issue only a max of *N+M* subrequests, after which I get 
an exception:

Traceback (most recent call last):
  File "/…/site-packages/sqlalchemy/pool.py", line 1122, in _do_get
return self._pool.get(wait, self._timeout)
  File "/…/site-packages/sqlalchemy/util/queue.py", line 156, in get
raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

[…]
  File "/…/site-packages/sqlalchemy/engine/base.py", line 2147, in 
_wrap_pool_connect
return fn()
  File "/…/site-packages/sqlalchemy/pool.py", line 387, in connect
return _ConnectionFairy._checkout(self)
  File "/…/site-packages/sqlalchemy/pool.py", line 766, in _checkout
fairy = _ConnectionRecord.checkout(pool)
  File "/…/site-packages/sqlalchemy/pool.py", line 516, in checkout
rec = pool._do_get()
  File "/…/site-packages/sqlalchemy/pool.py", line 1131, in _do_get
(self.size(), self.overflow(), self._timeout))
sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 0 reached, 
connection timed out, timeout 30

for 

sqlalchemy.pool_size = 5
sqlalchemy.max_overflow = 0

When I up the pool size to fit all subrequests, then everything works fine 
and the SQLA log shows me a ROLLBACK for each subrequest and one COMMIT at 
the end which I think is the main request.

Now I could set pool size to 0 to indicate no pool size limit, but I’m not 
sure if that would be the correct solution here.

What’s the recommended approach here?

Thanks!
Jens

-- 
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 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] MySQL “charset introducer” how-to?

[jens . troeger]

Confirmed: adding `binary_prefix=true` to the URL gets rid of the warning.

I’ll also keep an eye out for SA1.3, and will remove that string then.

Thanks Mike!


On Tuesday, March 13, 2018 at 11:57:52 PM UTC+10, Mike Bayer wrote:
>
> OK I have better news.pymysql already did the thing I wanted him to 
> do, apparently, so connect like this:
>
> e = 
> create_engine("mysql+pymysql://scott:tiger@mysql57/test?charset=utf8mb4&binary_prefix=true",
>  
> echo=True)
>
> warning should be gone.
>
> However, I'm going to implement the _binary prefix for all drivers in 1.3 
> as part of 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4216/mysql-specific-binary-warnings-1300
>  
> .
>

-- 
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 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] MySQL “charset introducer” how-to?

[jens . troeger]

Well that’s good news then, thanks Mike 🤓


On Monday, March 12, 2018 at 11:08:28 AM UTC+10, Mike Bayer wrote:
>
> the issue is Python 3 only and reproduces with PyMySQL 0.8.0 and not 
> with 0.7.1.Hopefully this is a PyMySQL bug that can be resolved 
> quickly (note I might recall seeing this recently but could not find 
> any discussion about it).   Posted up as 
> https://github.com/PyMySQL/PyMySQL/issues/644 . 
>

-- 
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 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] MySQL “charset introducer” how-to?

[jens . troeger]

Thank you Mike!

I hoped that we can do without all the details, but I guess not. This 
warning has been (quite literally) bugging me for days. I’m very tempted to 
ignore it because the warning seems to be a recent regression (see below) 
and also because we’re moving to PostgreSQL very soon…

First off, I *do* pass a byte string and have from the beginning of our 
project. Here is SA’s debug output of the offending statement (I’ve 
shortened it somewhat to remove clutter):

1:17:19 INFO  [sqlalchemy.engine.base.Engine][MainThread] INSERT INTO 
profiles (id, …, address1, address2, city, state, zipcode, country) VALUES 
(%(id)s, …, %(address1)s, %(address2)s, %(city)s, %(state)s, %(zipcode)s, 
%(country)s)
21:17:19 INFO  [sqlalchemy.engine.base.Engine][MainThread] {…, 'state': 
None, 'address1': None, 'city': 
b'z\xf9\x87jS?\xd4i\xa5\xa3\r\xa7\x1e\xed\x16\xe0\xb5\x05R\xa4\xec\x16\x8f\x06\xb5\xea+\xaf<\x00\\\x94I9A\xe0\x82\xa7\x13\x0c\x8c',
 
…}

In that particular statement, five `blob` columns are updated which results 
in five warnings one of which is:

/…/lib/python3.5/site-packages/pymysql/cursors.py:165: Warning: (1300, 
"Invalid utf8mb4 character string: 'F9876A'")

The character string 'F9876A' are bytes 1-3 b'\xf9\x87j' passed to the 
`city` column. The column and table are defined like so:

mysql> show create table profiles;
| profiles | CREATE TABLE `profiles` (
  `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  …
  `address1` blob,
  `address2` blob,
  `city` blob NOT NULL,
  `state` blob,
  `zipcode` tinyblob,
  `country` blob NOT NULL,
  …
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

My db connect string is:

mysql+pymysql://jens@localhost/test?charset=utf8mb4&unix_socket=/opt/local/var/run/mysql56/mysqld.sock

This happens with Python 3.5.5 and 3.6.3. In a venv update one week ago I 
updated from the following packet versions:

pymysql==0.7.11 → 0.8.0
sqlalchemy==1.1.13 → 1.2.4
zope.sqlalchemy==0.7.7 → 1.0

These warnings started to appear since that update; and I wonder if perhaps 
they are a result of a regression from that update.

Please let me know if I can add more details,
Jens




On Monday, March 12, 2018 at 12:11:35 AM UTC+10, Mike Bayer wrote:
>
> first off I have a feeling this could be avoided if you passed a 
> bytestring to the driver and not a unicode object, however I would 
> need complete details on python version, driver in use, an example 
> string, and the specific column type you are working with. 
>
> I've not seen an "introducer" before and not sure that is applicable 
> to a bound parameter passed by the driver.   COLLATE is supported by 
> the method: 
>
> conn.execute( 
> 
> table.insert().values(foobar=bindparam("value").collate("some_collation"), 
> {"value": "myvalue"} 
> ) 
>
> but that's with Core, not ORM, and as usual, I really need to see 
> completely what you are doing.  This is likely resolvable by just 
> passing a python bytestring and not a unicode object. 
>

-- 
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 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] MySQL “charset introducer” how-to?

[jens . troeger]

Hello,

I ran into a warning (1300, "Invalid utf8mb4 character string: 'C3D545'") 
when inserting or updating a `blob` and apparently that’s expected behavior 
as per MySQL bug 79317 . However, 
according to one of the comments 
 the solution would be to 
use the “*_binary* character-set introducer 
”.

How would I specify that in SQLAlchemy for `blob` columns?

Thanks!
Jens

-- 
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 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] sqlalchemy_utils.TimezoneType problems with pytz and UTC

[jens . troeger]

Thank you Mike! I also filed an issue with 
sqlalchemy_utils: https://github.com/kvesteri/sqlalchemy-utils/issues/315


On Thursday, March 8, 2018 at 11:27:20 PM UTC+10, Mike Bayer wrote:
>
> just FYI I don't do sqlalchemy-utils though other people here may have 
> insight into your question. 
>
>

-- 
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 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] sqlalchemy_utils.TimezoneType problems with pytz and UTC

[jens . troeger]

Hi,

I just noticed an oddity using the TimezoneType as a column type. Suppose 
the following statement:

conn.execute(t_user.update() \
   .where(t_user.c.id == id_) \
   .values({
   'timezone': pytz.timezone(tzname),
   }))

then tzname="America/Los_Angeles" works, but tzname="UTC" does not. What 
happens is that the _coerce() method 

 
contains a type check which has different results for the two:

>>> import pytz
>>> pytz.__version__
'2018.3'
>>> utc = pytz.timezone('UTC')
>>> la = pytz.timezone('America/Los_Angeles')
>>> isinstance(utc, pytz.tzinfo.BaseTzInfo)
False
>>> isinstance(la, pytz.tzinfo.BaseTzInfo)
True

Because of that, _coerce() attempts to coerce/convert the utc timezone 
object and fails in this line of code 
:

  File "/…/lib/python3.5/site-packages/sqlalchemy/sql/type_api.py", line 
1156, in process
return impl_processor(process_param(value, dialect))
  File "/…/lib/python3.5/site-packages/sqlalchemy_utils/types/timezone.py", 
line 83, in process_bind_param
return self._from(self._coerce(value)) if value else None
  File "/…/lib/python3.5/site-packages/sqlalchemy_utils/types/timezone.py", 
line 76, in _coerce
obj = self._to(value)
  File "/…/lib/python3.5/site-packages/pytz/__init__.py", line 162, in 
timezone
if zone.upper() == 'UTC':
AttributeError: 'UTC' object has no attribute 'upper'

I am not sure if the above difference with isinstance() is intended or a 
bug on the pytz side (I filed a question/issue here 
), or if this is a problem on the 
TimezoneType side. Or am I missing something here?

Thanks!
Jens

-- 
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 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] How to properly handle bidirectional many-to-many and their double entries

[jens . troeger]

Thank you, Mike!

the above table is relationally incorrect - you should never have 
> duplicate data inside of a table like that, and the student_id and 
> instructor_id columns should normally form the primary key, or at 
> least a unique constraint of some kind.


I agree. I didn’t want to add constraints yet because I wanted to see if 
the duplication could be solved in another way (e.g. an INSERT … IF NOT 
EXISTS).
 

> But beyond that, is a 
> "favorite" inherently bidirectional?   usually "favorites" have a 
> direction,   A might be B's favorite, but B is not A's favorite. 
>

As for the “favorite” relationship, well, in this case it’s an implied 
bi-directional one…
 

> if you *do* want to add on both ends but it's "unique" anyway, try 
> using collection_class=set with your relationship.  that will 
> eliminate dupes on the Python side and you can set up the table rows 
> as unique.
>

Ah, nice: documentation in section Customizing Collection Access 
. 
The “favs” are now an InstrumentedSet 

 instead 
of a standard Python list but I guess that is alright. Adding and deleting 
worked using sets.

The one thing I noticed, however, was that I have to use back_populates 

 
on both sides (to get the set on both sides) instead of backref 

 on 
just one (to get a set on the defining side and default list on the backref 
side).

Cheers,
Jens





-- 
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 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] How to properly handle bidirectional many-to-many and their double entries

[jens . troeger]

Hi,

I've been playing with the Many-to-Many relationship 

 from 
the documentation. Suppose I have a student and teacher and would like to 
define a bidirectional “favorite” relationship between the two, i.e. if a 
student is a favorite of a teacher then the teacher is also a favorite of 
that student:

favorite_association_table = Table("favorite_associations", Base.metadata,  
   
Column("student_id", Integer, ForeignKey("students.id"), 
nullable=False),  
Column("teacher_id", Integer, ForeignKey("teachers.id"), 
nullable=False),
)  
   

and in the Teacher class:

favs = relationship("Student", secondary=favorite_association_table, 
backref="favs")

Now I observe the following:

>>> teacher.favs
[]
>>> student.favs
[]
>>> student.favs.append(teacher)
>>> teacher.favs.append(student)

which creates two entries in the association table:

mysql> select * from favorite_associations;
+--+--+
| student_id   | instructor_id|
+--+--+
| 030ced9060d2460fa30936cffd2e0a0a | 08315ae48d574bc3ac29526c675e67fc |
| 030ced9060d2460fa30936cffd2e0a0a | 08315ae48d574bc3ac29526c675e67fc |
+--+--+
2 rows in set (0.00 sec)

That is probably alright, but not really desirable. Now, when I attempt to 
delete either the student or the teacher from the other, I get an error:

>>> student.favs.remove(teacher)
[…]
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 
'favorite_association' expected to delete 1 row(s); Only 2 were matched.

I guess I could avoid the list.remove() and build a query like: 

delete from favorite_associations where student_id='...'

but that seems clumsy in the context of the ORM.

So my question is: what is the proper and recommended way of implementing 
and handling such a bidirectional relationship?

Thank you!
Jens








-- 
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 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] Performance of ORDER BY vs. list.sort() vs. sorted()

[jens . troeger]

Hello,

I’m curious about your experience with sorting the results of all() 

 
queries which return a list. It seems to me that there are three ways of 
sorting such result lists:

   1. Use an ORDER BY 
   

 
   in the query and let the database do the sorting.
   2. Use Python’s list.sort() 
    and sort the 
   result list in-place.
   3. Use Python’s sorted() 
    function and 
   construct a new and sorted list from the result list.

Is there one preferable over the other, particularly wrt. performance? Is 
sorting on the db side more advantageous than sorting the result list 
later? (For list.sort() vs. sorted() see here 

.)

Thanks!
Jens

-- 
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 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] Backref and backref arguments

[jens . troeger]

Hi,

I’d like to follow up on the Backref Arguments 
 
example from the documentation. 

First, to make sure I understand this correctly, are these two lines 
equivalent? I would assume so, considering this comment 
:
 
“By default, this value [primaryjoin] is computed based on the foreign key 
relationships of the parent and child tables (or association table).”

addresses = relationship("Address",
 backref="user")

# Is like saying…

addresses = relationship("Address",
 primaryjoin="User.id==Address.user_id",
 backref="user")

Now for the backref with an argument. In the example, the email’s local 
part `tony` is hardcoded. Is there a way to parameterize that local part 
elegantly (i.e. pass arguments to that relationship), or is the following 
function the recommended way to go:

def user_addresses(self, name):
return [a for a in self.addresses if a.email.startswith(name)]

Thanks!
Jens

-- 
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 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] Session's dialect's server_version_info may be None? (Execution order dependency)

[jens . troeger]

Simon, thank you for the reference! That makes sense…

Jens


On Wednesday, February 21, 2018 at 7:15:20 PM UTC+10, Simon King wrote:
>
> SQLAlchemy doesn't connect to the database until it needs to. Creating 
> a Session by itself does not cause it to connect. This is mentioned in 
> the docs: 
>
> http://docs.sqlalchemy.org/en/latest/orm/session_basics.html 
>
> """ 
> The Session begins in an essentially stateless form. Once queries are 
> issued or other objects are persisted with it, it requests a 
> connection resource from an Engine that is associated either with the 
> Session itself or with the mapped Table objects being operated upon. 
> This connection represents an ongoing transaction, which remains in 
> effect until the Session is instructed to commit or roll back its 
> pending state. 
> """ 
>
> Hope that helps, 
>
> Simon 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+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] Session's dialect's server_version_info may be None? (Execution order dependency)

[jens . troeger]

Hello,
Suppose I create a db session the normal way:

>>> engine = engine_from_config({"sqlalchemy.url":"mysql+pymysql://…"})
>>> Session = sessionmaker(bind=engine)
>>> session = Session()

I noticed that there is now an order dependency between:

>>> session.bind.dialect.server_version_info  # None
>>> session.query(Alembic.version_num).scalar()
'cb13f97d30c6'
>>> session.bind.dialect.server_version_info  # Now contains a tuple
(5, 6, 34)

I can't quite make sense of this behavior, can somebody please shed some 
light on it?

Thank you!
Jens

-- 
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 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] MySQL's sql_mode (ORM)

[jens . troeger]

If I were to go into my MySQL DB and

mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES';

would that have the same effect? I find the MySQL documentation 
 somewhat lacking on 
that topic. What are the scope and lifetime of the above vs. using a 
listener as suggested by Michael?



On Wednesday, February 5, 2014 at 2:08:00 PM UTC-8, Michael Bayer wrote:
>
> here’s a recipe for emitting that SQL on every connection, as well as 
> right up front on first connect which is optional, though if you plan on 
> changing ANSI_QUOTES would need to happen before the dialect checks on 
> sql_mode: 
>
> from sqlalchemy import create_engine, event 
>
> eng = create_engine("mysql://scott:tiger@localhost/test", echo='debug') 
>
> @event.listens_for(eng, "first_connect", insert=True)  # make sure we're 
> the very first thing 
> @event.listens_for(eng, "connect") 
> def connect(dbapi_connection, connection_record): 
> cursor = dbapi_connection.cursor() 
> cursor.execute("SET sql_mode = 'STRICT_ALL_TABLES'") 
>
> conn = eng.connect() 
>
>
>
> On Feb 5, 2014, at 3:20 PM, Staszek  > wrote: 
>
> > Hi 
> > 
> > How do you set sql_mode when using SQLAlchemy ORM with MySQL? 
> > 
> > For example, I would like to be able to do something equivalent to this: 
> > 
> > SET sql_mode = 'STRICT_ALL_TABLES'; 
> > 
> > so as to get an error (instead of a warning) when string length exceeds 
> > column size on INSERT. 
> > 
> > Ideally I would like to be able to combine several SQL modes together. 
> > List of available MySQL modes: 
> > 
> http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode
>  
> > . 
> > 
> > Thanks! 
> > 
> > -- 
> > http://people.eisenbits.com/~stf/ 
> > http://www.eisenbits.com/ 
> > 
> > OpenPGP: 80FC 1824 2EA4 9223 A986  DB4E 934E FEA0 F492 A63B 
> > 
> > -- 
> > 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 http://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/groups/opt_out. 
>
>

-- 
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 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] Re: changing polymorphic identity on the fly

[jens . troeger]

Thank you Mike!

So suppose I have an a Manager object *manager* and I wanted to promote 
that to Engineer:

# Modify the persisted data of the manager object in the db directly.
session = object_session(manager)
session.execute(manager.__table__.update() \
 .where(manager.__table__.c.id == manager.id
) \
 .values({'type': 'engineer'}))
# session.flush() ?

# Expunge the object.
session.expunge(manager)

# Subsequent reads would then return Engineer objects…

I suspect that modifying the object directly would not work with expunge(). 
However, if I wouldn't expunge() and all I do is modify the object then 
perhaps that's ok?

# Modify the object's type directly, then manager's class would not be 
accurate anymore.
manager.type = "engineer"

Also, if for some reason the session were to rollback() then the above 
execute() would not be persisted and manager remain a manager. Correct?

Thanks!
Jens


On Thursday, August 10, 2017 at 5:25:37 PM UTC+2, Mike Bayer wrote:
>
> > Talking about the example in the documentation, do I understand you 
> > correctly that changing from Manager to Engineer is as simple as 
> updating 
> > the type field? 
>
> yes.   also locally you want to clear out those objects from your 
> Session and reload (cleanest would be to expunge() totally). 
>
>

-- 
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 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] How to implement a conditional insert (MySQL)

[jens . troeger]

> is that session using "autocommit" mode?  
>

print("--> autocommit", dbsession.autocommit) gives a False.
 

> it looks like the error is raised on the UNLOCK ? 


 When I comment out the UNLOCK, the exception still raises. Here is SQLA’s 
verbose logging:

--> autocommit False
2017-12-07 11:23:52,101 INFO  [sqlalchemy.engine.base.Engine][MainThread] 
LOCK TABLES tokens WRITE
2017-12-07 11:23:52,101 INFO  [sqlalchemy.engine.base.Engine][MainThread] {}
2017-12-07 11:23:52,102 INFO  [sqlalchemy.engine.base.Engine][MainThread] 
INSERT INTO tokens (id, user_id, client_sig) SELECT '84…20', '39…30', 
'0b…87' 
FROM dual 
WHERE NOT (EXISTS (SELECT * 
FROM tokens 
WHERE tokens.user_id = %(user_id_1)s AND tokens.client_sig = 
%(client_sig_1)s))
2017-12-07 11:23:52,102 INFO  [sqlalchemy.engine.base.Engine][MainThread] 
{'user_id_1': '39…30', 'client_sig_1': '0b…87'}
2017-12-07 11:23:52,103 ERROR [srv.views.exceptions][MainThread] Internal 
server error detected, stacktrace follows.


-- 
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 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] How to implement a conditional insert (MySQL)

[jens . troeger]

Once again I'm at a loss.

I tried this:

dbsession.execute("LOCK TABLES tokens WRITE")
dbsession.execute(cond_insert) # As per above discussion.
dbsession.execute("UNLOCK TABLES")

which raises an error: 1100, "Table 'tokens' was not locked with LOCK 
TABLES".

Then I read that the temporary table created by an inner select needs to be 
locked as well, but it doesn't exist at the time when LOCK TABLES executes. 
So using stmt.alias("foo") for the INSERT, and adding "foo" to the LOCK 
TABLES doesn't work. The "dual" table can't be locked either, as it doesn't 
actually exist. No other table is being used by the conditional INSERT as 
far as I can see.

I can't quite make sense of the error message, and how to resolve it.

-- 
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 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] How to implement a conditional insert (MySQL)

[jens . troeger]

Thank you!

I found this elderly 
thread:  
https://groups.google.com/forum/?fromgroups=#!topic/sqlalchemy/8WLhbsp2nls

If injecting the SQL statements directly is still the way to go, then I’ll 
wrap the conditional insert with a table lock.

Jens


On Tuesday, December 5, 2017 at 6:00:16 AM UTC+10, Mike Bayer wrote:
>
> On Mon, Dec 4, 2017 at 2:56 PM,  > 
> wrote: 
> > I am now thoroughly confused. 
> > 
> > My understanding of the above conditional insert statement was that it 
> won’t 
> > persist a token if there is already a token with the same user_id and 
> > client_sig in the table. Alas, today once again I see an exception 
> > “MultipleResultsFound: Multiple rows were found for one_or_none()” and 
> two 
> > token were in the table. To requests arrived 70ms apart and it seems 
> that 
> > both were successful in creating tokens. 
> > 
> > I expected that one would “outrace” the other and one would succeed to 
> > persist the token which the other would see when its insert runs. 
> > 
> > What am I missing here? 
>
> You would need to use serializable isolation or table locks to ensure 
> two conflicting INSERT operations don't overlap, if your operation 
> depends upon SELECTing those rows after the fact. 
>
>
> > 
> > -- 
> > 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 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. 
>

-- 
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 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] How to implement a conditional insert (MySQL)

[jens . troeger]

I am now thoroughly confused.

My understanding of the above conditional insert statement was that it 
won’t persist a token if there is already a token with the same user_id and 
client_sig in the table. Alas, today once again I see an exception 
“MultipleResultsFound: Multiple rows were found for one_or_none()” and two 
token were in the table. To requests arrived 70ms apart and it seems that 
both were successful in creating tokens.

I expected that one would “outrace” the other and one would succeed to 
persist the token which the other would see when its insert runs.

What am I missing here?

-- 
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 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] How to implement a conditional insert (MySQL)

[jens . troeger]

Hah 🤔 Boy this is (not really) funny. Thank you for digging into this, 
Mike!

I had to make two minor import adjustments

from sqlalchemy.sql.elements import quoted_name
 
from sqlalchemy.sql.expression import literal_column


but otherwise the code works now. I still think it’s a poor solution to my 
problem. The proper solution, I think, would be a CHECK constraint 
 across the columns and a 
simple INSERT which can fail the constraint.

Alas, MySQL doesn’t do CHECK constraints—another reason to migrate to 
PostgreSQL as soon as possible. 

Jens


On Tuesday, November 28, 2017 at 9:23:46 AM UTC+10, Mike Bayer wrote:
>
> On Mon, Nov 27, 2017 at 4:02 PM,  > 
> wrote: 
> > 
> > 
> > No problem, here it is. To work with your initial code example... 
> > 
>  e = 
>  
> create_engine("mysql+pymysql://jens@localhost/test?charset=utf8&unix_socket=/opt/local/var/run/mysql56/mysqld.sock",
>  
>
>  echo=True) 
>  Base.metadata.drop_all(e) 
> > 2017-11-28 06:47:21,171 INFO sqlalchemy.engine.base.Engine SHOW 
> VARIABLES 
> > LIKE 'sql_mode' 
> > 2017-11-28 06:47:21,171 INFO sqlalchemy.engine.base.Engine {} 
> > 2017-11-28 06:47:21,173 INFO sqlalchemy.engine.base.Engine SELECT 
> DATABASE() 
> > 2017-11-28 06:47:21,173 INFO sqlalchemy.engine.base.Engine {} 
> > 2017-11-28 06:47:21,174 INFO sqlalchemy.engine.base.Engine show 
> collation 
> > where `Charset` = 'utf8' and `Collation` = 'utf8_bin' 
> > 2017-11-28 06:47:21,174 INFO sqlalchemy.engine.base.Engine {} 
> > 2017-11-28 06:47:21,175 INFO sqlalchemy.engine.base.Engine SELECT 
> CAST('test 
> > plain returns' AS CHAR(60)) AS anon_1 
> > 2017-11-28 06:47:21,175 INFO sqlalchemy.engine.base.Engine {} 
> > 2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine SELECT 
> CAST('test 
> > unicode returns' AS CHAR(60)) AS anon_1 
> > 2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine {} 
> > 2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine SELECT 
> CAST('test 
> > collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1 
> > 2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine {} 
> > 2017-11-28 06:47:21,177 INFO sqlalchemy.engine.base.Engine DESCRIBE 
> `tokens` 
> > 2017-11-28 06:47:21,177 INFO sqlalchemy.engine.base.Engine {} 
> > 2017-11-28 06:47:21,179 INFO sqlalchemy.engine.base.Engine ROLLBACK 
>  Base.metadata.create_all(e) 
> > 2017-11-28 06:47:23,490 INFO sqlalchemy.engine.base.Engine DESCRIBE 
> `tokens` 
> > 2017-11-28 06:47:23,490 INFO sqlalchemy.engine.base.Engine {} 
> > 2017-11-28 06:47:23,495 INFO sqlalchemy.engine.base.Engine ROLLBACK 
> > 2017-11-28 06:47:23,496 INFO sqlalchemy.engine.base.Engine 
> > CREATE TABLE tokens ( 
> > id VARCHAR(50) NOT NULL, 
> > user_id VARCHAR(50), 
> > client_sig VARCHAR(50), 
> > PRIMARY KEY (id) 
> > ) 
> > 2017-11-28 06:47:23,496 INFO sqlalchemy.engine.base.Engine {} 
> > 2017-11-28 06:47:23,507 INFO sqlalchemy.engine.base.Engine COMMIT 
>  stmt = select([ 
> > ... literal_column("'abc'"), 
> > ... literal_column("'def'"), 
> > ... literal_column("'ghi'"), 
> > ... ]).where( 
> > ... ~exists().where(and_(Token.user_id == 'def', Token.client_sig == 
> > 'ghi')) 
> > ... ) 
>  
>  print(stmt) 
> > SELECT 'abc', 'def', 'ghi' 
> > WHERE NOT (EXISTS (SELECT * 
> > FROM tokens 
> > WHERE tokens.user_id = :user_id_1 AND tokens.client_sig = 
> :client_sig_1)) 
> > 
> > 
> >> OK I don't recall what conditions MySQL needs "FROM dual" can you share 
> >> with me: 
> >> 
> >> 1. the full version of MySQL 
>
> Well in standard MySQL / MariaDB fashion, they have made this as fun 
> as possible (well, more fun would be one database *rejects* FROM DUAL, 
> at least it isn't that bad): 
>
> 1. MySQL 5.6 requires "FROM DUAL" 
>
> 2. MySQL 5.7 does not require FROM DUAL but accepts it. 
>
> 3. MariaDB 10.1 does not require FROM DUAL but accepts it. 
>
> 4. MariaDB 10.2 *does* require FROM DUAL. The two vendors have 
> **flip-flopped** on their preference of this issue. 
>
> Anyway, here's your dual, as is typical, to make it work completely we 
> need an esoteric trick to avoid quoting the "dual" word: 
>
> from sqlalchemy.sql import quoted_name 
> dual = table(quoted_name("dual", quote=False)) 
>
> then your statement: 
>
> stmt = select([ 
> literal_column("'abc'"), 
> literal_column("'def'"), 
> literal_column("'ghi'"), 
> ]).select_from(dual).where( 
> ~exists().where(and_(Token.user_id == 'def', Token.client_sig == 
> 'ghi')) 
> ) 
>
>
>
>
>
> > 
> > 
>  e.dialect.name, e.dialect.driver, e.dialect.server_version_info 
> > ('mysql', 'pymysql', (5, 6, 34)) 
> > 
> >> 2. the output of "SHOW VARIABLES LIKE '%SQL_MODE%' 
> >> 
> > 
> > mysql> show variables like '%sql_mode%'; 
> > +---++ 
> > | Variable_name | Val

Re: [sqlalchemy] How to implement a conditional insert (MySQL)

[jens . troeger]

No problem, here it is. To work with your initial code example...

>>> e = 
create_engine("mysql+pymysql://jens@localhost/test?charset=utf8&unix_socket=/opt/local/var/run/mysql56/mysqld.sock",
 
echo=True)
>>> Base.metadata.drop_all(e) 
2017-11-28 06:47:21,171 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES 
LIKE 'sql_mode'
2017-11-28 06:47:21,171 INFO sqlalchemy.engine.base.Engine {}
2017-11-28 06:47:21,173 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2017-11-28 06:47:21,173 INFO sqlalchemy.engine.base.Engine {}
2017-11-28 06:47:21,174 INFO sqlalchemy.engine.base.Engine show collation 
where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2017-11-28 06:47:21,174 INFO sqlalchemy.engine.base.Engine {}
2017-11-28 06:47:21,175 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test plain returns' AS CHAR(60)) AS anon_1
2017-11-28 06:47:21,175 INFO sqlalchemy.engine.base.Engine {}
2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test unicode returns' AS CHAR(60)) AS anon_1
2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine {}
2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin 
AS anon_1
2017-11-28 06:47:21,176 INFO sqlalchemy.engine.base.Engine {}
2017-11-28 06:47:21,177 INFO sqlalchemy.engine.base.Engine DESCRIBE `tokens`
2017-11-28 06:47:21,177 INFO sqlalchemy.engine.base.Engine {}
2017-11-28 06:47:21,179 INFO sqlalchemy.engine.base.Engine ROLLBACK
>>> Base.metadata.create_all(e) 
2017-11-28 06:47:23,490 INFO sqlalchemy.engine.base.Engine DESCRIBE `tokens`
2017-11-28 06:47:23,490 INFO sqlalchemy.engine.base.Engine {}
2017-11-28 06:47:23,495 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-11-28 06:47:23,496 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE tokens (
id VARCHAR(50) NOT NULL, 
user_id VARCHAR(50), 
client_sig VARCHAR(50), 
PRIMARY KEY (id)
)
2017-11-28 06:47:23,496 INFO sqlalchemy.engine.base.Engine {}
2017-11-28 06:47:23,507 INFO sqlalchemy.engine.base.Engine COMMIT
>>> stmt = select([ 
... literal_column("'abc'"), 
... literal_column("'def'"), 
... literal_column("'ghi'"), 
... ]).where( 
... ~exists().where(and_(Token.user_id == 'def', Token.client_sig == 
'ghi')) 
... ) 
>>> 
>>> print(stmt)
SELECT 'abc', 'def', 'ghi' 
WHERE NOT (EXISTS (SELECT * 
FROM tokens 
WHERE tokens.user_id = :user_id_1 AND tokens.client_sig = :client_sig_1))


OK I don't recall what conditions MySQL needs "FROM dual" can you share 
> with me: 
>
> 1. the full version of MySQL 
>
 
>>> e.dialect.name, e.dialect.driver, e.dialect.server_version_info
('mysql', 'pymysql', (5, 6, 34))

2. the output of "SHOW VARIABLES LIKE '%SQL_MODE%' 
>
>
mysql> show variables like '%sql_mode%';
+---++
| Variable_name | Value  |
+---++
| sql_mode  | NO_ENGINE_SUBSTITUTION |
+---++
1 row in set (0.00 sec)


3. stack trace + error message 
>

>>> e.execute( 
... insert(Token).from_select(['id', 'user_id', 'client_sig'], stmt) 
... ) 
2017-11-28 06:47:49,489 INFO sqlalchemy.engine.base.Engine INSERT INTO 
tokens (id, user_id, client_sig) SELECT 'abc', 'def', 'ghi' 
WHERE NOT (EXISTS (SELECT * 
FROM tokens 
WHERE tokens.user_id = %(user_id_1)s AND tokens.client_sig = 
%(client_sig_1)s))
2017-11-28 06:47:49,489 INFO sqlalchemy.engine.base.Engine {'client_sig_1': 
'ghi', 'user_id_1': 'def'}
2017-11-28 06:47:49,491 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "/…/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 
1182, in _execute_context
context)
  File "/…/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 
470, in do_execute
cursor.execute(statement, parameters)
  File "/…/lib/python3.5/site-packages/pymysql/cursors.py", line 166, in 
execute
result = self._query(query)
  File "/…/lib/python3.5/site-packages/pymysql/cursors.py", line 322, in 
_query
conn.query(q)
  File "/…/lib/python3.5/site-packages/pymysql/connections.py", line 856, 
in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/…/lib/python3.5/site-packages/pymysql/connections.py", line 1057, 
in _read_query_result
result.read()
  File "/…/lib/python3.5/site-packages/pymysql/connections.py", line 1340, 
in read
first_packet = self.connection._read_packet()
  File "/…/lib/python3.5/site-packages/pymysql/connections.py", line 1014, 
in _read_packet
packet.check_error()
  File "/…/lib/python3.5/site-packages/pymysql/connections.py", line 393, 
in check_error
err.raise_mysql_exception(self._data)
  File "/…/lib/python3.5/site-packages/pymysql/err.py", line 107, in 
raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the 
right sy

Re: [sqlalchemy] How to implement a conditional insert (MySQL)

[jens . troeger]

Thank you, Mike!

I’ve tried to implement what we talked about above, but had a few problems. 
First, I was unable to use MySQLdb 
 because of compilation 
errors; instead I’ve used pymysql  for 
a while now. When I tried to run the test code from your previous answer 
 I 
received a SQL syntax error near WHERE. It looks like the FROM is missing.

As for “fails” I was mistaken. The conditional insert should always 
succeed, but may not insert a row into the table. While this maintains data 
consistency, it’s not quite what I need. It’ll do for now though.

My current working solution is the following hack which I need to revisit 
again later:

# Create a proxy object to return to the caller.
token = Token(
id=uuid4(),
user=user_id,
client_sig=sigstring,
)
# If I don't expunge then the token is automatically added/inserted?
# I thought I'd always have to dbsession.add().
dbsession.expunge(token)

# Now issue the SQL statement much like the on from my initial question.
sql = (
"INSERT INTO tokens(id, user_id, client_sig) "  
 
"SELECT '" + token.id + "', '" + token.user_id + "', '" + 
token.client_sig + "' "
"FROM dual "
 
"WHERE NOT EXISTS "
 
"(SELECT * FROM tokens WHERE user_id='" + token.user_id + "' AND 
client_sig='" + token.client_sig + "')"
)
dbsession.execute(sql)

# Return the proxy object to the Pyramid view function.
return token

I realize that this is a poor solution because the returned proxy object 
may in rare cases not represent what's committed to the db. However, that's 
easier to handle than committing a duplicate. I would also prefer SQLA 
functions over raw SQL code.

Jens

-- 
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 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] How to implement a conditional insert (MySQL)

[jens . troeger]

Thanks Mike!

that's the first red flag here, why can't you put a unique constraint here? 
>

Ordinarily I’d agree. In this case, there’s an additional column called 
“deleted” which is NULL for active Tokens and contains utcnow() for deleted 
Tokens. That makes for deleted and active tokens which can have the same 
user_id/client_sig (one active, many deleted).

I doubt MySQL requires the "Dual" part, here is a demo 
>
> from sqlalchemy import * 
> from sqlalchemy.orm import * 
> from sqlalchemy.ext.declarative import declarative_base 
>
> Base = declarative_base() 
>
> class Token(Base): 
> __tablename__ = 'tokens' 
> id = Column(String(50), primary_key=True) 
> user_id = Column(String(50)) 
> client_sig = Column(String(50)) 
>
> e = create_engine("mysql://scott:tiger@localhost/test", echo=True) 
> Base.metadata.drop_all(e) 
> Base.metadata.create_all(e) 
>
> stmt = select([ 
> literal_column("'abc'"), 
> literal_column("'def'"), 
> literal_column("'ghi'"), 
> ]).where( 
> ~exists().where(and_(Token.user_id == 'def', Token.client_sig == 
> 'ghi')) 
> ) 
>
> e.execute( 
> insert(Token).from_select(['id', 'user_id', 'client_sig'], stmt) 
> ) 


 Ah ok, so from_select() would indeed be the correct approach here. Thank 
you, I’ll tinker with this :-)

One more question in this context though (without having tried the above 
yet). The INSERT fails if the SELECT returns an existing row (i.e. an 
active Token exists already in my example above). I suspect that the 
failure happens upon commit() of the transaction, not upon flush()?

Because the transaction and session are bound to a Pyramid request and the 
commit happens outside of the view handler function, I’ll catch that 
failure in a generic exception view 

 
which I’ll have to register?

Cheers,
Jens

-- 
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 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] How to implement a conditional insert (MySQL)

[jens . troeger]

Hi,

My question is based on this answer 
 
on Stackoverflow to the question *MySQL Conditional Insert*. In my current 
SA implementation I have the following code:

token = Token(  
  
user=user,  
  
client_sig=sigstring,  
  
)  
 
session.add(token)  
  
# session.flush()  
 

Now I would like to prevent duplicate client_sig entries to prevent the 
same user having the same client_sig, but I can’t set that column to 
unique. It looks like the following SQL statement achieves what I want:

INSERT INTO tokens(id, user_id, client_sig)
SELECT '51…bb', 'fd…b3', 'some string'
FROM dual
WHERE NOT EXISTS (
  SELECT * 
  FROM tokens
  WHERE user_id='fd…b3'
  AND client_sig='some string'
  );

I found some documentation on insert … from select 

 
but can not quite put the two together (I don't think they're the same). 
How would I go about implementing the above SQL statement using SA, or will 
I have to issue raw SQL in this case?

Thank you!
Jens

-- 
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 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] Confusion over session.dirty, query, and flush

[jens . troeger]

Thank you, the event worked like a charm :-) Though I think that I don't 
need the commit events, because the application terminates anyway.

I modified your approach to gather which objects were flushed so that in 
the end I can give the user more precise information:

dbsession.info["new"] = set()  
 
dbsession.info["dirty"] = set()
 
dbsession.info["deleted"] = set()  
 


def update_session_info(session):  
 
new = session.info["new"]  
 
new |= set(session.new)
 
dirty = session.info["dirty"]  
 
dirty |= set(session.dirty)
 
deleted = session.info["deleted"]  
 
deleted |= set(session.deleted)
 
return new, dirty, deleted  



@event.listens_for(dbsession, "before_flush")  
 
def on_before_flush(session, _, _):
 
update_session_info(session)

...
code.interact(local=locals())  
 
...
   
new, dirty, deleted = update_session_info(dbsession)

if new or dirty or deleted:
 
if new:
 
print("The following objects were created: ", new)  
 
if dirty:  
 
print("The following objects were modified: ", dirty)  

if deleted:
 
print("The following objects were deleted: ", deleted)  
 
yesno = input("Would you like to commit this transaction? [y/N] 
") 
if yesno == "y":

print("Committing transaction...")  
   
else:  
 
print("Rolling back transaction...")
   
raise _SessionRollbackException()  
 

# ...this is where the context closes and the transaction commits 
and the dbsession ends.

Cheers,
Jens



On Saturday, November 18, 2017 at 12:03:05 AM UTC+10, Simon King wrote:
>
> OK, I think tracking session events seems reasonable. You could do 
> something like this (completely untested): 
>
> from sqalchemy.event import event 
>
> @event.listens_for(YourSessionOrSessionMaker, 'before_flush') 
> def on_before_flush(session, flush_context, instances): 
> session.info['flushed'] = True 
>
>
> # You'd probably also want to reset the 'flushed' flag 
> # after a commit or rollback 
> @event.listens_for(YourSessionOrSessionMaker, 'after_commit') 
> @event.listens_for(YourSessionOrSessionMaker, 'after_rollback') 
> def on_session_reset(session): 
> session.info['flushed'] = False 
>
>
> # when user exits interactive session: 
> modified = ( 
> session.info.get('flushed', False) 
> or session.deleted 
> or session.new 
> or session.dirty 
> ) 
> if modified: 
> raw_input('do you want to commit?') 
>
>
> ...but note that if you ever execute raw SQL (ie. 
> session.execute('UPDATE x WHERE y')), that will not be noticed by 
> those events. 
>
> Hope that helps, 
>
> Simon 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group

Re: [sqlalchemy] Confusion over session.dirty, query, and flush

[jens . troeger]

Sure.

I'm working with two Pyramid/SQLAlchemy web servers, and in order to have a 
more convenient way of looking at the db data I wrote a small tool which 
essentially creates a db session, loads the server orm helper functions and 
sets up an environment much like a view handler functions has. Then the 
tools calls code.interact() 
 and I have a 
terminal.

>From that terminal I can look at tables, use the server's helper functions 
to read data, but also to write objects.

When I exit interactive mode, I just rolled back the transaction and ended 
the session. However, I'd now like to check: if during that terminal 
session some objects were modified, give the user the choice to either 
commit() or rollback().

To do that, I checked with session.dirty/deleted/new and that's when the 
initial questions arose.

If there are better ways of checking, curious to learn :-)
Thank you!


On Thursday, November 16, 2017 at 11:39:14 PM UTC+10, Simon King wrote:
>
> Can you explain why you actually want to do this? There might be 
> better options than before_flush, but we'd need to know exactly what 
> you're trying to do first. 
>
> Simon 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+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] Confusion over session.dirty, query, and flush

[jens . troeger]

That makes sense, thank you, Simon!

Regarding the events: you suggest to use a before_flush() to examine 
session.dirty whenever a session.query() executes?

Also, is there a way to get the list of objects that have been flushed, or 
should I track them myself whenever a before_flush() event occurs?

Jens


On Thursday, November 16, 2017 at 7:49:54 PM UTC+10, Simon King wrote:
>
> On Thu, Nov 16, 2017 at 7:45 AM,  > 
> wrote: 
> > Hello, 
> > 
> > I've been exploring some of the session functionality that handles 
> object 
> > states, and I'm quite confused. Here is what I see: 
> > 
>  engine = engine_from_config({'sqlalchemy.url': 'mysql+pymysql://…'}) 
>  session_factory = sessionmaker(bind=engine) # No autoflush 
>  session = session_factory() 
>  # Now query a table to get an object. 
>  p = session.query(Table).filter(Table.id == '0f4…ed6').one_or_none() 
>  p.name 
> > "Some Name" 
>  p.name = "Other Name" 
>  session.dirty 
> > IdentitySet([]) 
>  session.is_modified(p) 
> > True 
>  session._is_clean() 
> > False 
> > 
> > This all makes sense. If I now create a new query, then the above change 
> > seems to be gone, but isn't? 
> > 
>  p2 = session.query(Table).filter(Table.id == 
> '384…a05c').one_or_none() 
>  session.dirty 
> > IdentitySet([]) 
>  session.is_modified(p) 
> > False # p is not modified according to this. 
>  session._is_clean() 
> > True 
>  p.name 
> > "Other Name" # p still has the modified name. 
> > 
> > The new query seems to set the session to "clean", but the object p 
> still 
> > contains its change. I can't quite find documentation for the behavior. 
> What 
> > am I missing? 
> > 
> > What I would like to do is: in one session, select a few objects 
> (multiple 
> > queries), inspect, perhaps modify them. Then I'd like to query if there 
> were 
> > any modifications/deletes and if so choose to commit or rollback. 
> Initially 
> > I thought to use the Session.dirty/deleted/new properties, but then the 
> > above showed. 
> > 
> > If I was to set autoflush, how could I inspect the flushed code buffer? 
> > 
> > Thanks! 
> > Jens 
>
> The results you are seeing are due to autoflush, which is on by 
> default. When you run your second query, the session flushes any 
> pending changes to the database before running the query. After the 
> flush, the session is considered clean. The methods you were using 
> (dirty, is_modified etc.) indicate whether the session contains 
> changes that haven't been flushed to the database. They don't tell you 
> if the database has received changes that haven't yet been committed. 
>
> There are various ways to do what you want, depending on how your code 
> is structured. One possibility is to use a Session event such as 
> before_flush to set a flag saying that there are uncomitted changes in 
> the database. 
>
> http://docs.sqlalchemy.org/en/latest/orm/session_events.html 
>
> Hope that helps, 
>
> Simon 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+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] Confusion over session.dirty, query, and flush

[jens . troeger]

Hello,

I've been exploring some of the session functionality that handles object 
states, and I'm quite confused. Here is what I see:

>>> engine = engine_from_config({'sqlalchemy.url': 'mysql+pymysql://…'})
>>> session_factory = sessionmaker(bind=engine) # No autoflush
>>> session = session_factory()
>>> # Now query a table to get an object.
>>> p = session.query(Table).filter(Table.id == '0f4…ed6').one_or_none()
>>> p.name
"Some Name"
>>> p.name = "Other Name"
>>> session.dirty
IdentitySet([])
>>> session.is_modified(p)
True
>>> session._is_clean()
False

This all makes sense. If I now create a new query, then the above change 
*seems* to be gone, but isn't?

>>> p2 = session.query(Table).filter(Table.id == '384…a05c').one_or_none()
>>> session.dirty
IdentitySet([])
>>> session.is_modified(p)
False # p is not modified according to this.
>>> session._is_clean()
True
>>> p.name
"Other Name" # p still has the modified name.

The new query seems to set the session to "clean", but the object p still 
contains its change. I can't quite find documentation for the behavior. 
What am I missing?

What I would like to do is: in one session, select a few objects (multiple 
queries), inspect, perhaps modify them. Then I'd like to query if there 
were any modifications/deletes and if so choose to commit or rollback. 
Initially I thought to use the Session.dirty/deleted/new properties, but 
then the above showed.

If I was to set autoflush, how could I inspect the flushed code buffer? 

Thanks!
Jens


-- 
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 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] Handle anticipated data truncation before transaction commits

[jens . troeger]

Hm. I enabled strict mode, and sure enough the warning turns into an 
exception. 

Alas, it seems that the flush() in the above example does not cause the 
exception, it happens later when the query executes. I was looking for a 
way to preempt that exception: the code runs within a view function of a 
Pyramid view handler which commits the transaction outside of the view 
function.

Looks like a manual check is needed :-)


On Friday, November 3, 2017 at 12:04:54 PM UTC+10, Mike Bayer wrote:
>
> the most appropriate way is to set strict SQL mode on your MySQL database: 
>
> https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict 
>   
>

-- 
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 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] Handle anticipated data truncation before transaction commits

[jens . troeger]

Thanks Mike!  Considering we're planning to migrate to PostgreSQL in a 
month or two, how would I go about that (considering there's no strict mode 
there). Would the exception be raised on PostgreSQL?

Jens


On Friday, November 3, 2017 at 12:04:54 PM UTC+10, Mike Bayer wrote:
>
> > What is the proper way of handling this situation, other than comparing 
> the 
> > string length against the column size explicitly? 
>
> the most appropriate way is to set strict SQL mode on your MySQL database: 
>
> https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict 
>
>

-- 
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 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] Handle anticipated data truncation before transaction commits

[jens . troeger]

Hi,

I've got a db object with a string property that may grow larger than its 
column specifies (because it's being appended to). I tried:

old_string = obj.string
try:

obj.string = "abc" * 1  
   
dbsession.flush()  
 
except Exception as e:  

obj.string = old_string
 

but the expected warning wasn't caught here:

/…/pymysql/cursors.py:166: Warning: (1265, "Data truncated for column 
'chat' at row 1")
  result = self._query(query)

What is the proper way of handling this situation, other than comparing the 
string length against the column size explicitly?

Thanks!
Jens

-- 
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 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] Working with versioned objects and inheritance hierarchies

[jens . troeger]

I need to reread your code a few more times Mike.

But I just realized that the versioning is an example (almost a recipe), 
but it's not something that ships with the SA package, yes? Meaning that 
I'll have to copy much of the history_meta.py 

 
code over into my own meta.py file?

Jens


On Saturday, October 28, 2017 at 9:25:22 AM UTC+10, Mike Bayer wrote:
>
>
>
> On Fri, Oct 27, 2017 at 4:39 PM, > 
> wrote:
>
>> Thank you Mike!
>>
>> Yes, indeed you are right and I referred to Single Table Inheritance 
>> !
>>  
>> I would like to version just one subclass, i.e. a subset of that single 
>> table, and it sounds like that will work (source code link 
>> ,
>>  
>> though that example versions the base class). Great 👌
>>
>> And if I wanted to walk the versions of a particular row, how would I do 
>> that? Suppose versioning in the example 
>> 
>>  
>> on Single Table Inheritance:
>>
>> # Versioned subclass.
>> class Engineer(Versioned, Employee):
>> engineer_info = Column(String(50))
>>
>> # Retrieve a single Engineer object/row.
>> eng = session.query(Engineer).filter(Engineer.id==N).one_or_none()
>> # eng.version would give me the current version of the object/row.
>> # How can I iterate over previous versions of the object/row?
>>
>> If I read the history_meta.py 
>> 
>>  in 
>> the example correctly, then versioned objects have a version 
>> 
>>  and 
>> a changed 
>> 
>>  property, 
>> but no property that is an iterator over previous versions. I'd have to 
>> query a history table manually?
>>
>
>
> the history table is mapped to a class attached to the versioned class:
>
> SomeClassHistory = SomeClass.__history_mapper__.class_
>
> eq_(
> sess.query(SomeClassHistory).filter(
> SomeClassHistory.version == 1).all(),
> [SomeClassHistory(version=1, name='sc1')]
> )
>
>
> you can add any kind of accessor on the versioned class to return that for 
> you, like:
>
> def history_cls(cls):
>
>  return cls.__history_mapper__.class_
>
>
> hist = sess.query(MyClass.history_cls)
>
> or whatever you'd like.Or you can set this up within the history_meta 
> routine 
> itself, I'm not sure why I made it attach the "mapper" like that and not the 
> class directly.
>
>  
>
>> Would that be something like 
>>
>> SELECT * FROM EngineerHistory WHERE id=N SORT BY version;  -- 
>> EmployeeHistory??
>>
>> Cheers!
>>
>>
>> On Saturday, October 28, 2017 at 3:50:08 AM UTC+10, Mike Bayer wrote:
>>>
>>> On Thu, Oct 26, 2017 at 9:35 PM,   wrote: 
>>> > Hello, 
>>> > 
>>> > I'm looking for a way track changes to table rows, very much like 
>>> described 
>>> > in this Stackoverflow question. 
>>> > 
>>> > SA supports versioning objects as described in the documentation: 
>>> > 
>>> http://docs.sqlalchemy.org/en/latest/orm/examples.html#versioning-objects 
>>> > Does this approach work for inherited classes as well? 
>>>
>>> yes it does, there should be some coverage for joined inheritance in the 
>>> tests. 
>>>
>>>
>>> > I mean: can I version 
>>> > a class which doesn't have a table but is a subclass of a joined table 
>>> > inheritance? 
>>>
>>> that would be single table inheritance if it doesn't have a table. 
>>> the test suite has both a multilevel-joined test and a single 
>>> inheritance test, doesn't seem to have tested joined inh to single but 
>>> it's likely it will just work if you try it out. 
>>>
>>> > Or will I need to version the base class which has the table 
>>> > associated (something I'd like to avoid because I'd version much of 
>>> what 
>>> > would not need versioning). 
>>>
>>> I'm not sure if the versioning example tries this but the Versioned 
>>> mixin can be placed just on the class you care about versioning, and 
>>> it's likely it will just take effect when that class is present.   The 
>>> Versioned mixin should be able to deal with each class independently 
>>> of all the rest.   You'd need to try it out and see what it does. 

Re: [sqlalchemy] Working with versioned objects and inheritance hierarchies

[jens . troeger]

Thank you Mike!

Yes, indeed you are right and I referred to Single Table Inheritance 
!
 
I would like to version just one subclass, i.e. a subset of that single 
table, and it sounds like that will work (source code link 
,
 
though that example versions the base class). Great 👌

And if I wanted to walk the versions of a particular row, how would I do 
that? Suppose versioning in the example 

 
on Single Table Inheritance:

# Versioned subclass.
class Engineer(Versioned, Employee):
engineer_info = Column(String(50))

# Retrieve a single Engineer object/row.
eng = session.query(Engineer).filter(Engineer.id==N).one_or_none()
# eng.version would give me the current version of the object/row.
# How can I iterate over previous versions of the object/row?

If I read the history_meta.py 

 in 
the example correctly, then versioned objects have a version 

 and 
a changed 

 property, 
but no property that is an iterator over previous versions. I'd have to 
query a history table manually? Would that be something like 

SELECT * FROM EngineerHistory WHERE id=N SORT BY version;  -- 
EmployeeHistory??

Cheers!


On Saturday, October 28, 2017 at 3:50:08 AM UTC+10, Mike Bayer wrote:
>
> On Thu, Oct 26, 2017 at 9:35 PM,  > 
> wrote: 
> > Hello, 
> > 
> > I'm looking for a way track changes to table rows, very much like 
> described 
> > in this Stackoverflow question. 
> > 
> > SA supports versioning objects as described in the documentation: 
> > 
> http://docs.sqlalchemy.org/en/latest/orm/examples.html#versioning-objects 
> > Does this approach work for inherited classes as well? 
>
> yes it does, there should be some coverage for joined inheritance in the 
> tests. 
>
>
> > I mean: can I version 
> > a class which doesn't have a table but is a subclass of a joined table 
> > inheritance? 
>
> that would be single table inheritance if it doesn't have a table. 
> the test suite has both a multilevel-joined test and a single 
> inheritance test, doesn't seem to have tested joined inh to single but 
> it's likely it will just work if you try it out. 
>
> > Or will I need to version the base class which has the table 
> > associated (something I'd like to avoid because I'd version much of what 
> > would not need versioning). 
>
> I'm not sure if the versioning example tries this but the Versioned 
> mixin can be placed just on the class you care about versioning, and 
> it's likely it will just take effect when that class is present.   The 
> Versioned mixin should be able to deal with each class independently 
> of all the rest.   You'd need to try it out and see what it does. 
>
>
>
> > 
> > Then there is SA Continuum, which seems more flexible but perhaps a 
> little 
> > too much for my needs. 
>
> It's possible that Continuum was based on this example in the first 
> place but I would never remember if that detail is real or just made 
> up by my imagination. 
>
>
> > With projects like Continuum I'm always a bit worried 
> > about support and maintenance—any experiences with it? Does that support 
> > table inheritance? 
>
> You could reach out to its maintainer (the username that posts it to 
> pypi, and /or the user that seems to have the most commits).  If you 
> don't get a reply, that's a clue :) 
>
>
>
>
> > 
> > Thanks! 
> > Jens 
> > 
> > -- 
> > 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 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. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Rela

[sqlalchemy] Working with versioned objects and inheritance hierarchies

[jens . troeger]

Hello,

I'm looking for a way track changes to table rows, very much like described 
in this Stackoverflow question 

. 

SA supports versioning objects as described in the 
documentation: 
http://docs.sqlalchemy.org/en/latest/orm/examples.html#versioning-objects  
Does this approach work for inherited classes as well? I mean: can I 
version a class which doesn't have a table but is a subclass of a joined 
table inheritance 
?
 
Or will I need to version the base class which has the table associated 
(something I'd like to avoid because I'd version much of what would not 
need versioning).

Then there is SA Continuum 
, which seems more 
flexible but perhaps a little too much for my needs. With projects like 
Continuum I'm always a bit worried about support and maintenance—any 
experiences with it? Does that support table inheritance?

Thanks!
Jens

-- 
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 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] Curious: SAWarning: DELETE statement on table '…' expected to delete 1 row(s); 0 were matched.

[jens . troeger]

Thanks Mike!

My code actually only uses session.delete(obj), at no place does it issue 
statements directly. Since this is running in the context of requests for a 
web server (as per this Pyramid cookiecutter 
), could it be that 
two requests attempt to delete the same resource, with one succeeding and 
the other not?

Cheers,
Jens


On Thursday, October 26, 2017 at 11:24:54 PM UTC+10, Mike Bayer wrote:
>
>
> it means the ORM emitted a statement like: 
>
> DELETE FROM your_table WHERE primary_key= 
>
> and then there was no row with "X".   This is not supposed to happen, 
> and is usually an indicator that ORM-level code is causing a delete of 
> the same row more than once.   Such as, emitting a DELETE statement 
> and then also saying session.delete(some_object).You'd want to 
> understand why this is happening and then if you identify it as an 
> unavoidable situation, set up that flag. 
>

>

-- 
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 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] Curious: SAWarning: DELETE statement on table '…' expected to delete 1 row(s); 0 were matched.

[jens . troeger]

Hi,

I am curious about this warning:

/…/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py:964: 
SAWarning: DELETE statement on table '…' expected to delete 1 row(s); 0 
were matched.  Please set confirm_deleted_rows=False within the mapper 
configuration to prevent this warning.
  (table.description, expected, rows_matched)

I found this Stackoverflow thread 

 
but it didn't give away mach information.

The warning appears seemingly random, and I'm curious about its details. 
Could somebody please shed light on it? I'm hesitant about silencing a 
warning without understanding its cause(s).

Thank you!
Jens

-- 
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 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] Packet sequence number wrong (PyMySQL driver or…?)

[jens . troeger]

I don't know, Mike, perhaps a good question to ask the mailing list?


On Monday, October 16, 2017 at 10:52:25 AM UTC+10, Mike Bayer wrote:
>
> oh, a "pre fork" problem, does that mean TCP connections to mysql are 
> being copied from the main process to forked child processes?   I 
> didn't know uwsgi was that kind of server.   That would have because 
> multiple processes sending data on the same filehandle and would also 
> cause this problem, though I'd think it would be more severe in its 
> symptoms. 
>
>
> On Sun, Oct 15, 2017 at 7:55 PM,  > 
> wrote: 
> > After asking at the uWSGI mailing list (see this thread) the suggested 
> > solution was to enable the lazy-apps flags. That seems to have worked... 
> > 
> > 
> > On Friday, September 29, 2017 at 10:46:47 AM UTC+10, jens.t...@gmail.com 
> > wrote: 
> >> 
> >> Again, thank you Mike! 
> >> 
> >> I never see these issues happening locally, which is where I use 
> gunicorn 
> >> as the server. Your theory might explain that. The trace comes from our 
> beta 
> >> server online, which runs nginx/uwsgi. I'll poke around the 
> configuration, 
> >> maybe that'll show something. 
> >> 
> >> Eventually though (in the next one-two months) I would like to move to 
> >> gunicorn as the sole server, because handling API requests is the only 
> thing 
> >> it needs to do. 
> >> 
> >> Cheers, 
> >> Jens 
> >> 
> >> 
> >> On Friday, September 29, 2017 at 9:55:30 AM UTC+10, Mike Bayer wrote: 
> >>> 
> >>> On Thu, Sep 28, 2017 at 7:17 PM,   wrote: 
> >>> > Thank you, Mike! 
> >>> > 
> >>> > I would have never extracted your interpretation from the trace! (I 
> >>> > don’t 
> >>> > know the code…) 
> >>> > 
> >>> > The two questions that arise now, though, are 
> >>> > 
> >>> >  - Why would connections drop out of the pool, is this a 
> configuration 
> >>> > problem that I should worry about? 
> >>> 
> >>> something was already wrong with the connection when the pool got it 
> >>> back, so it had to be discarded. as far as what was wrong, it had to 
> >>> do with the request itself, and it was very possibly the web request's 
> >>> client dropped the connection, e.g. user hit the stop button. Not 
> >>> sure what uswgi does but it might have called a thread.exit() / 
> >>> SystemExit that interrupted PyMySQL's work on the socket.   This is 
> >>> something we definitely see in the eventlet world at least. 
> >>> 
> >>> >  - The request still failed with a 502 although, as you said, it’s a 
> >>> > harmless exception. What is the proper way of handling these 
> >>> > situations? 
> >>> 
> >>> if uwsgi is killing off threads when a connection is cut, it might 
> >>> want to be more graceful about that and at least log that the thread 
> >>> was killed in the middle.   If this is in fact what's going on.I 
> >>> stick with apache/mod_wsgi for reasons like these. 
> >>> 
> >>> 
> >>> > 
> >>> > Still learning new things here, and I’m curious about your 
> suggestions 
> >>> > :-) 
> >>> > Jens 
> >>> > 
> >>> > 
> >>> > On Friday, September 29, 2017 at 6:59:48 AM UTC+10, Mike Bayer 
> wrote: 
> >>> >> 
> >>> >> On Thu, Sep 28, 2017 at 2:48 PM,   wrote: 
> >>> >> > Hello, 
> >>> >> > 
> >>> >> > I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in 
> the 
> >>> >> > context 
> >>> >> > of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, 
> and 
> >>> >> > mysql 
> >>> >> > 5.6.3. I followed the Pyramid/Alchemy Cookiecutter 
> implementation. 
> >>> >> > 
> >>> >> > On seemingly random requests (although all seem to be OPTIONS) I 
> see 
> >>> >> > the 
> >>> >> > following exception in the server logs (see also this PyMySQL 
> >>> >> > issue): 
> >>> >> > 
> >>> >> > 2017-09-28 18:13:17,765 ERROR 
> >>> >> > [sqlalchemy.pool.QueuePool][b'uWSGIWorker1Core0'] Exception 
> during 
> >>> >> > reset 
> >>> >> > or 
> >>> >> > similar 
> >>> >> > Traceback (most recent call last): 
> >>> >> >   File 
> >>> >> > 
> >>> >> > 
> "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", 
> >>> >> > line 1182, in _execute_context 
> >>> >> > context) 
> >>> >> >   File 
> >>> >> > 
> >>> >> > 
> "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", 
> >>> >> > line 470, in do_execute 
> >>> >> > cursor.execute(statement, parameters) 
> >>> >> >   File 
> >>> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
> >>> >> > line 
> >>> >> > 166, in execute 
> >>> >> > result = self._query(query) 
> >>> >> >   File 
> >>> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
> >>> >> > line 
> >>> >> > 322, in _query 
> >>> >> > conn.query(q) 
> >>> >> >   File 
> >>> >> > 
> "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> >>> >> > line 856, in query 
> >>> >> > self._affected_rows = 
> >>> >> > self._read_query_result(unbuffered=unbuffered) 
> >>> >> >   File 
> >>> >> > 
> "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> >>> >> 

Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

[jens . troeger]

After asking at the uWSGI mailing list (see this thread 
) the 
suggested solution was to enable the lazy-apps 
 flags. 
That seems to have worked...


On Friday, September 29, 2017 at 10:46:47 AM UTC+10, jens.t...@gmail.com 
wrote:
>
> Again, thank you Mike!
>
> I never see these issues happening locally, which is where I use gunicorn 
>  as the server. Your theory might 
> explain that. The trace comes from our beta server online, which runs 
> nginx/uwsgi . I'll 
> poke around the configuration, maybe that'll show something.
>
> Eventually though (in the next one-two months) I would like to move to 
> gunicorn as the sole server, because handling API requests is the only 
> thing it needs to do.
>
> Cheers,
> Jens
>
>
> On Friday, September 29, 2017 at 9:55:30 AM UTC+10, Mike Bayer wrote:
>>
>> On Thu, Sep 28, 2017 at 7:17 PM,   wrote: 
>> > Thank you, Mike! 
>> > 
>> > I would have never extracted your interpretation from the trace! (I 
>> don’t 
>> > know the code…) 
>> > 
>> > The two questions that arise now, though, are 
>> > 
>> >  - Why would connections drop out of the pool, is this a configuration 
>> > problem that I should worry about? 
>>
>> something was already wrong with the connection when the pool got it 
>> back, so it had to be discarded. as far as what was wrong, it had to 
>> do with the request itself, and it was very possibly the web request's 
>> client dropped the connection, e.g. user hit the stop button. Not 
>> sure what uswgi does but it might have called a thread.exit() / 
>> SystemExit that interrupted PyMySQL's work on the socket.   This is 
>> something we definitely see in the eventlet world at least. 
>>
>> >  - The request still failed with a 502 although, as you said, it’s a 
>> > harmless exception. What is the proper way of handling these 
>> situations? 
>>
>> if uwsgi is killing off threads when a connection is cut, it might 
>> want to be more graceful about that and at least log that the thread 
>> was killed in the middle.   If this is in fact what's going on.I 
>> stick with apache/mod_wsgi for reasons like these. 
>>
>>
>> > 
>> > Still learning new things here, and I’m curious about your suggestions 
>> :-) 
>> > Jens 
>> > 
>> > 
>> > On Friday, September 29, 2017 at 6:59:48 AM UTC+10, Mike Bayer wrote: 
>> >> 
>> >> On Thu, Sep 28, 2017 at 2:48 PM,   wrote: 
>> >> > Hello, 
>> >> > 
>> >> > I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in the 
>> >> > context 
>> >> > of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, and 
>> >> > mysql 
>> >> > 5.6.3. I followed the Pyramid/Alchemy Cookiecutter implementation. 
>> >> > 
>> >> > On seemingly random requests (although all seem to be OPTIONS) I see 
>> the 
>> >> > following exception in the server logs (see also this PyMySQL 
>> issue): 
>> >> > 
>> >> > 2017-09-28 18:13:17,765 ERROR 
>> >> > [sqlalchemy.pool.QueuePool][b'uWSGIWorker1Core0'] Exception during 
>> reset 
>> >> > or 
>> >> > similar 
>> >> > Traceback (most recent call last): 
>> >> >   File 
>> >> > 
>> "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", 
>> >> > line 1182, in _execute_context 
>> >> > context) 
>> >> >   File 
>> >> > 
>> "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", 
>> >> > line 470, in do_execute 
>> >> > cursor.execute(statement, parameters) 
>> >> >   File 
>> "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
>> >> > line 
>> >> > 166, in execute 
>> >> > result = self._query(query) 
>> >> >   File 
>> "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
>> >> > line 
>> >> > 322, in _query 
>> >> > conn.query(q) 
>> >> >   File 
>> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
>> >> > line 856, in query 
>> >> > self._affected_rows = 
>> self._read_query_result(unbuffered=unbuffered) 
>> >> >   File 
>> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
>> >> > line 1057, in _read_query_result 
>> >> > result.read() 
>> >> >   File 
>> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
>> >> > line 1340, in read 
>> >> > first_packet = self.connection._read_packet() 
>> >> >   File 
>> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
>> >> > line 1001, in _read_packet 
>> >> > % (packet_number, self._next_seq_id)) 
>> >> > pymysql.err.InternalError: Packet sequence number wrong - got 102 
>> >> > expected 8 
>> >> > 
>> >> > 
>> >> > I see this error in different variations for the packet sequence. 
>> Any 
>> >> > hint 
>> >> > or tip to explain this issue is appreciated! 
>> >> 
>> >> the log seems to indicate the connection pool has gotten the 
>> >> connection back a

Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

[jens . troeger]

Again, thank you Mike!

I never see these issues happening locally, which is where I use gunicorn 
 as the server. Your theory might 
explain that. The trace comes from our beta server online, which runs 
nginx/uwsgi . I'll 
poke around the configuration, maybe that'll show something.

Eventually though (in the next one-two months) I would like to move to 
gunicorn as the sole server, because handling API requests is the only 
thing it needs to do.

Cheers,
Jens


On Friday, September 29, 2017 at 9:55:30 AM UTC+10, Mike Bayer wrote:
>
> On Thu, Sep 28, 2017 at 7:17 PM,  > 
> wrote: 
> > Thank you, Mike! 
> > 
> > I would have never extracted your interpretation from the trace! (I 
> don’t 
> > know the code…) 
> > 
> > The two questions that arise now, though, are 
> > 
> >  - Why would connections drop out of the pool, is this a configuration 
> > problem that I should worry about? 
>
> something was already wrong with the connection when the pool got it 
> back, so it had to be discarded. as far as what was wrong, it had to 
> do with the request itself, and it was very possibly the web request's 
> client dropped the connection, e.g. user hit the stop button. Not 
> sure what uswgi does but it might have called a thread.exit() / 
> SystemExit that interrupted PyMySQL's work on the socket.   This is 
> something we definitely see in the eventlet world at least. 
>
> >  - The request still failed with a 502 although, as you said, it’s a 
> > harmless exception. What is the proper way of handling these situations? 
>
> if uwsgi is killing off threads when a connection is cut, it might 
> want to be more graceful about that and at least log that the thread 
> was killed in the middle.   If this is in fact what's going on.I 
> stick with apache/mod_wsgi for reasons like these. 
>
>
> > 
> > Still learning new things here, and I’m curious about your suggestions 
> :-) 
> > Jens 
> > 
> > 
> > On Friday, September 29, 2017 at 6:59:48 AM UTC+10, Mike Bayer wrote: 
> >> 
> >> On Thu, Sep 28, 2017 at 2:48 PM,   wrote: 
> >> > Hello, 
> >> > 
> >> > I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in the 
> >> > context 
> >> > of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, and 
> >> > mysql 
> >> > 5.6.3. I followed the Pyramid/Alchemy Cookiecutter implementation. 
> >> > 
> >> > On seemingly random requests (although all seem to be OPTIONS) I see 
> the 
> >> > following exception in the server logs (see also this PyMySQL issue): 
> >> > 
> >> > 2017-09-28 18:13:17,765 ERROR 
> >> > [sqlalchemy.pool.QueuePool][b'uWSGIWorker1Core0'] Exception during 
> reset 
> >> > or 
> >> > similar 
> >> > Traceback (most recent call last): 
> >> >   File 
> >> > "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", 
> >> > line 1182, in _execute_context 
> >> > context) 
> >> >   File 
> >> > 
> "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", 
> >> > line 470, in do_execute 
> >> > cursor.execute(statement, parameters) 
> >> >   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
> >> > line 
> >> > 166, in execute 
> >> > result = self._query(query) 
> >> >   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
> >> > line 
> >> > 322, in _query 
> >> > conn.query(q) 
> >> >   File 
> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> >> > line 856, in query 
> >> > self._affected_rows = 
> self._read_query_result(unbuffered=unbuffered) 
> >> >   File 
> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> >> > line 1057, in _read_query_result 
> >> > result.read() 
> >> >   File 
> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> >> > line 1340, in read 
> >> > first_packet = self.connection._read_packet() 
> >> >   File 
> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> >> > line 1001, in _read_packet 
> >> > % (packet_number, self._next_seq_id)) 
> >> > pymysql.err.InternalError: Packet sequence number wrong - got 102 
> >> > expected 8 
> >> > 
> >> > 
> >> > I see this error in different variations for the packet sequence. Any 
> >> > hint 
> >> > or tip to explain this issue is appreciated! 
> >> 
> >> the log seems to indicate the connection pool has gotten the 
> >> connection back and is attempting to emit a rollback() upon it.  These 
> >> can fail if the connection has already had some varieties of error 
> >> occur upon it, most often when the work on the connection was 
> >> interrupted, such as when receiving a greenlet or thread exit.   It's 
> >> likely when a WSGI request is interrupted, the cleanup logic here 
> >> fails because the connection has been thrown into an invalid state. 
> >> The connection is invalidated (e.g. discarded) in this case and is 
> >> harmless. 
> >> 
> >

Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

[jens . troeger]

Thank you, Mike! 

I would have never extracted your interpretation from the trace! (I don’t 
know the code…) 

The two questions that arise now, though, are

 - Why would connections drop out of the pool, is this a configuration 
problem that I should worry about?
 - The request still failed with a 502 although, as you said, it’s a 
harmless exception. What is the proper way of handling these situations?

Still learning new things here, and I’m curious about your suggestions :-)
Jens


On Friday, September 29, 2017 at 6:59:48 AM UTC+10, Mike Bayer wrote:
>
> On Thu, Sep 28, 2017 at 2:48 PM,  > 
> wrote: 
> > Hello, 
> > 
> > I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in the 
> context 
> > of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, and 
> mysql 
> > 5.6.3. I followed the Pyramid/Alchemy Cookiecutter implementation. 
> > 
> > On seemingly random requests (although all seem to be OPTIONS) I see the 
> > following exception in the server logs (see also this PyMySQL issue): 
> > 
> > 2017-09-28 18:13:17,765 ERROR 
> > [sqlalchemy.pool.QueuePool][b'uWSGIWorker1Core0'] Exception during reset 
> or 
> > similar 
> > Traceback (most recent call last): 
> >   File 
> "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", 
> > line 1182, in _execute_context 
> > context) 
> >   File 
> > "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", 
> > line 470, in do_execute 
> > cursor.execute(statement, parameters) 
> >   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
> line 
> > 166, in execute 
> > result = self._query(query) 
> >   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
> line 
> > 322, in _query 
> > conn.query(q) 
> >   File 
> "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> > line 856, in query 
> > self._affected_rows = self._read_query_result(unbuffered=unbuffered) 
> >   File 
> "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> > line 1057, in _read_query_result 
> > result.read() 
> >   File 
> "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> > line 1340, in read 
> > first_packet = self.connection._read_packet() 
> >   File 
> "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> > line 1001, in _read_packet 
> > % (packet_number, self._next_seq_id)) 
> > pymysql.err.InternalError: Packet sequence number wrong - got 102 
> expected 8 
> > 
> > 
> > I see this error in different variations for the packet sequence. Any 
> hint 
> > or tip to explain this issue is appreciated! 
>
> the log seems to indicate the connection pool has gotten the 
> connection back and is attempting to emit a rollback() upon it.  These 
> can fail if the connection has already had some varieties of error 
> occur upon it, most often when the work on the connection was 
> interrupted, such as when receiving a greenlet or thread exit.   It's 
> likely when a WSGI request is interrupted, the cleanup logic here 
> fails because the connection has been thrown into an invalid state. 
> The connection is invalidated (e.g. discarded) in this case and is 
> harmless. 
>
>
> > 
> > Thank you! 
> > Jens 
> > 
> > -- 
> > 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 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. 
>

-- 
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 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] Packet sequence number wrong (PyMySQL driver or…?)

[jens . troeger]

Hello,

I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in the context 
of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, and mysql 
5.6.3. I followed the Pyramid/Alchemy Cookiecutter 
 implementation.

On seemingly random requests (although all seem to be OPTIONS) I see the 
following exception in the server logs (see also this PyMySQL issue 
):

2017-09-28 18:13:17,765 ERROR [sqlalchemy.pool.QueuePool][b'uWSGIWorker1Core0'] 
Exception during reset or similar
Traceback (most recent call last):
  File "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", 
line 1182, in _execute_context
context)
  File "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", 
line 470, in do_execute
cursor.execute(statement, parameters)
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", line 166, 
in execute
result = self._query(query)
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", line 322, 
in _query
conn.query(q)
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", line 
856, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", line 
1057, in _read_query_result
result.read()
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", line 
1340, in read
first_packet = self.connection._read_packet()
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", line 
1001, in _read_packet
% (packet_number, self._next_seq_id))
pymysql.err.InternalError: Packet sequence number wrong - got 102 expected 8


I see this error in different variations for the packet sequence. Any hint 
or tip to explain this issue is appreciated!

Thank you!
Jens

-- 
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 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] Re: changing polymorphic identity on the fly

[jens . troeger]

Thanks, Mike!

My implementation uses the single table inheritance 

 
model and I would like to (i.e. *need to*) keep the id the same.

Talking about the example in the documentation, do I understand you 
correctly that changing from Manager to Engineer is as simple as updating 
the type field?

Cheers,
Jens


On Thursday, August 10, 2017 at 9:18:50 AM UTC+10, Mike Bayer wrote:
>
> On Wed, Aug 9, 2017 at 6:53 PM,  > 
> wrote: 
> > Hi, 
> > 
> > I came upon this thread because I've got almost the exact same question. 
> > Several years on, though, how would I go about promoting polymorphic 
> objects 
> > across siblings today? 
>
> UPDATE the discriminator name, perform INSERT / DELETE of joined 
> inheritance tables as needed 
>
> > Thank you! 
> > Jens 
>

-- 
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 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] Re: changing polymorphic identity on the fly

[jens . troeger]

Hi,

I came upon this thread because I've got almost the exact same question. 
Several years on, though, how would I go about promoting polymorphic 
objects across siblings today?

Thank you!
Jens


On Tuesday, February 9, 2010 at 4:09:23 AM UTC+10, Michael Bayer wrote:
>
> Pavel Andreev wrote:
> > Another quick question: if all I need to do is change the
> > discriminator column, that is, if I'm sure no other tables are
> > affected, is there a way to do this without manual table updates?
> > Simply assigning the attribute doesn't seem to trigger table update.
>
> the discriminator is hardwired to the class.  so as long as thats what it
> sees its going to use that discriminator value.  you'd need to change this
> over in the manual thing you're doing.
>
> >
> > Pavel
> >
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlal...@googlegroups.com 
> .
> > To unsubscribe from this group, send email to
> > sqlalchemy+...@googlegroups.com .
> > For more options, visit this group at
> > http://groups.google.com/group/sqlalchemy?hl=en.
> >
> >
>
>

-- 
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 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] Adding filters to association (secondary) relationships

[jens . troeger]

Thank you, that worked.

Alas, I might have to use an explicit association object 

 
after all if I want to set the *is_manager* value in the association table. 
Simply assigning a new User to the Team.managers list (as defined by the 
relationship()) does not translate into *is_manager* to be set to True.

Jens


On Thursday, June 15, 2017 at 6:22:09 AM UTC-7, Simon King wrote:
>
> Actually I think in this case you could probably leave the "secondary" 
> argument as the association table itself, but change the join 
> condition via the "primaryjoin" parameter. Something like this 
> (completely untested): 
>
> class User(Base): 
> id = Column(Integer, primary_key=True) 
> managed_teams = relationship( 
> 'Team', 
> secondary=user_team_association_table, 
> primaryjoin=sa.and_(user_team_association_table.c.user_id == id, 
>
> user_team_association_table.c.is_manager==sa.true()), 
> ) 
>
> Simon 
>
> On Thu, Jun 15, 2017 at 12:26 PM,  > 
> wrote: 
> > Thanks Simon. While this seems to have worked, I only run into the next 
> > error. Mind you, I’m somewhat new to Alchemy and my SQL is rather rusty 
> at 
> > the moment. My current approach 
> > 
> > managed_teams = relationship("Team", 
> >  secondary="join(user_team_association, 
> > user_team_association.c.is_manager==true)", 
> >  backref="managers") 
> > 
> > seems to be an incomplete join. I’ll look into this tomorrow… 
> > Jens 
> > 
> > 
> > On Thursday, June 15, 2017 at 6:25:00 PM UTC+10, Simon King wrote: 
> >> 
> >> Table objects put columns under the ".c" attribute, so you probably 
> >> need "user_team_association.c.is_manager". 
> >> 
> >> Simon 
> >> 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > You received this message because you are subscribed to the Google 
> Groups 
> > "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an 
> > email to sqlalchemy+...@googlegroups.com . 
> > To 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. 
>

-- 
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 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] Adding filters to association (secondary) relationships

[jens . troeger]

Thanks Simon. While this seems to have worked, I only run into the next 
error. Mind you, I’m somewhat new to Alchemy and my SQL is rather rusty at 
the moment. My current approach

managed_teams = relationship("Team",   
 
 secondary="join(user_team_association, 
user_team_association.c.is_manager==true)",
 backref="managers")   
 

seems to be an incomplete join. I’ll look into this tomorrow…
Jens


On Thursday, June 15, 2017 at 6:25:00 PM UTC+10, Simon King wrote:
>
> Table objects put columns under the ".c" attribute, so you probably 
> need "user_team_association.c.is_manager". 
>
> Simon 
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+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] Are mapped objects uniqued (in a session) ?

[jens . troeger]

Hello,

In the scope of a single session, are objects representing the same row of 
data uniqued? I ran multiple selects and received the same object back, so 
I suspect that they are uniqued. With that, is it safe to compare object 
references, e.g. when searching through a collection?

For example, a one-to-many relationship 
 
maps to a list of many objects. Can I iterate over that list and safely 
compare object references instead of their content?

Thanks!

-- 
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 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] Adding filters to association (secondary) relationships

[jens . troeger]

Thank you Mike, the composite joins look like something close to what I 
need. However, it seems that they too require a mapped class for the 
association table? 

I tried

managed_teams = relationship("Team",   
 
 secondary=
"join(user_team_association.is_manager==true)",   
 backref="managers")   
 

using the table name directly, but this gives me an error:

AttributeError: 'Table' object has no attribute 'is_manager'

which I suspect indicates that the join expected a mapped class rather than 
a table name?

Thanks!



On Thursday, June 15, 2017 at 7:05:45 AM UTC+10, Mike Bayer wrote:
>
>
> On 06/14/2017 04:54 PM, jens.t...@gmail.com  wrote: 
> > […] 
> >
> > # Filtered association that lists only managed teams. The tuple maps 
> > # the column to the value and would translate to a WHERE. 
> >  managed_teams =relationship("Team", 
> >   
> secondary=user_team_association_table, 
> >   backref="managers", 
> >   filter=("is_manager",True)) 
>
>
> you use a custom primaryjoin / secondaryjoin for this 
>
> see  http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html 
>
>
> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#composite-secondary-joins
>  
>
> etc 
>
>

-- 
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 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] Adding filters to association (secondary) relationships

[jens . troeger]

Thank you Mike, the composite joins look like something close to what I 
need. However, it seems that they too require a mapped class for the 
association table? 

I tried

managed_teams = relationship("Team",   
 
 secondary=
"join(student_team_association.is_manager==true)",   
 backref="managers")   
 

using the table name directly, but this gives me an error:

AttributeError: 'Table' object has no attribute 'is_manager'

which I suspect indicates that the join expected a mapped class rather than 
a table name?

Thanks!


On Thursday, June 15, 2017 at 7:05:45 AM UTC+10, Mike Bayer wrote:
>
>
> On 06/14/2017 04:54 PM, jens.t...@gmail.com  wrote: 
> > […]
> > 
> > # Filtered association that lists only managed teams. The tuple maps 
> > # the column to the value and would translate to a WHERE. 
> >  managed_teams =relationship("Team", 
> >   
> secondary=user_team_association_table, 
> >   backref="managers", 
> >   filter=("is_manager",True)) 
>
>
> you use a custom primaryjoin / secondaryjoin for this 
>
> see  http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html 
>
>
> http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#composite-secondary-joins
>  
>
> etc 
>
>

-- 
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 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] Adding filters to association (secondary) relationships

[jens . troeger]

Hello,

I’ve been thinking about modeling an *attributed* many-to-many relationship 
for a few days now, and can’t find a solution that I liked. Somehow 
association objects feel clumsy because I really would like to avoid 
creating a class for that association.

See more details in this Stackoverflow post 

.

I guess what I’m asking, and perhaps proposing as a feature, is a filter 
for the association similar to Query filters:

user_team_association_table = Table("user_team_association",
Base.metadata,
Column("user_id", Integer, ForeignKey("users.id"), nullable=False),
Column("team_id", Integer, ForeignKey("teams.id"), nullable=False),
Column("is_manager", Boolean, nullable=False, default=False),
)   


class User(Base):
id = Column(Integer, default=…, primary_key=True)

# Make references available as list properties on both sides.
teams = relationship("Team",   
 
 secondary=user_team_association_table, 
 
 backref="users")

# Filtered association that lists only managed teams. The tuple maps
# the column to the value and would translate to a WHERE.
managed_teams = relationship("Team",
 secondary=user_team_association_table,
 backref="managers",
 filter=("is_manager", True))

class Team(Base):
id = Column(Integer, default=…, primary_key=True)


Any thoughts?

Thank you!
Jens

-- 
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 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.