Re: [sqlalchemy] .count() hangs indefinitely
Thanks Mike for your response. The query is run against a staging db and the table only contains some 500 records. But I will check the query as you have suggested to see what is going on. Cheers > > -- 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] .count() hangs indefinitely
On 11/04/2016 04:13 AM, James Burke wrote:
Hi All,
I've run into a odd problem, where calling the count function hangs my
code indefinitely. The odd thing is it was working until recently, so
I'm a little confused.
|
customer
=session.query(Customer).filter(Customer.phone_number.contains([message['metadata']['MIN']]))
logger.debug(len(customer.all()))
logger.debug('Works perfectly fine')
logger.debug(customer.count())
logger.debug('I will never see this')
|
Has anybody run into this problem before?
long running queries are a common problem when working with relational
databases. The "working until recently" part is also very common
because a slow query will usually only become slow once the data size
grows too large.
You need to first identify the queries running when this hang occurs,
e.g. that there's no lock contention, then you need to analyze it.
1. run the program so it hangs.
2. go to the psql monitor elsewhere and run "SELECT * FROM
pg_stat_activity". If your query is the only one there, then it's just
a slow query. If there's lock contention, you'd see that it's
"waiting" and you'd also see the other process that's doing something.
3. figure out why the query is slow using EXPLAIN ANALYZE .
Make sure you use the identical SQL that the database is actually
running, which you can see either in the pg_stat_activity or in the
echo=True output of SQLAlchemy. You will need to manually substitute
literal values where bound parameters are.
From EXPLAIN, you'd see what's either structurally wrong with the
query, such as if you're doing a cartesian product (e.g. selecting from
multiple tables without correlating rows to each other) or if something
is wrong with indexes, which you'd see in the form of table scan activity.
--
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 -
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] Update join in core
you're using left outer join to locate rows in A for which there is missing a row in B. the more idiomatic SQL for this is: UPDATE a SET a.y=1 WHERE NOT EXISTS (SELECT * FROM b WHERE a.x = b.x) works on any DB and performs better too. On 11/05/2016 09:38 PM, T Johnson wrote: How do I do an update join using SQLAlchemy core? In MySQL, it'd be something like this: update a left outer join b on a.x = b.x set a.y = 1 where b.x is null In other words, I want to modify a but only for the rows that do not exist in b according to my join criterion. The naive attempt that calls update() on the join object fails since Join objects don't have an update attribute. As a workaround, I'm using text(). A similar question on stackoverflow used an innerjoin and so it could be rewritten without needing an explicit join: http://stackoverflow.com/q/28274133/1843746 -- 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] Disable relationship deassociation
On 11/05/2016 08:45 PM, Yegor Roganov wrote:
Thank, I haven't yet figured out how to make your code more general
(without having to specify 'user_id'), but I think it can be done.
In the meantime I came up with a somewhat gross solution of my own.
The idea is to declare relation as `viewonly=True` and to delegate sets
to an injected write-only relationship (injection is the gross part).
I'm curious to hear your opinion on this approach, if possible.
it looks like recipes I wrote before we had decent events in place, it's
fine.
Here is the code:
|
from sqlalchemy import Column, Integer, String, ForeignKey, Boolean
from sqlalchemy import create_engine, and_
from sqlalchemy import event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, mapper
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
def soft_deleting_relationship(target, primaryjoin):
assert issubclass(target, Base)
rel = relationship(target,
primaryjoin=primaryjoin,
viewonly=True,
uselist=False,
)
@event.listens_for(mapper, 'after_configured', once=True)
def configure():
key = '_all_child_' + rel.key
parent = rel.parent.class_
if not hasattr(parent, key):
setattr(parent, key, relationship(target, lazy="noload"))
@event.listens_for(rel, 'set', active_history=True)
def set(target, value, oldvalue, initiator):
if oldvalue is not None:
oldvalue.delete()
getattr(target, key).append(value)
return rel
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email = Column(String, nullable=False)
deleted = Column(Boolean, nullable=False, default=False)
user_id = Column('user_id', Integer, ForeignKey('users.id'))
def delete(self):
self.deleted = True
User.address = soft_deleting_relationship(
Address,
primaryjoin=lambda: and_(Address.user_id == User.id,
~Address.deleted),
)
def test():
Base.metadata.create_all(engine)
sess = Session()
a1 = Address(email='foo')
u = User(id=1, address=a1)
sess.add_all([u, a1])
sess.commit()
a2 = Address(email='bar')
u.address = a2
sess.commit()
assert a1.user_id == 1, a1.user_id
assert u.address.id == a2.id
assert a2.id is not None
assert a1.deleted
test()
|
--
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 -
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] .count() hangs indefinitely
> > > Thanks for your reply Simon. - I am using Postgresql database - Running the SQL generated by SQL Alchemy in Postgres also hangs. - There is no traceback. -- 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.
