you need to look at the SQL output and the results and additionally
log in to the psql command line and check individual elements of what
you are doing. like what the current time is from now(), what are
the rows in the table without any WHERE criterion, etc.
On Fri, Jun 29, 2018 at 11:41 AM, Danila Ganchar
<danila.ganc...@gmail.com> wrote:
> Thank you for your feedback. I apologize for perseverance.
> I understand the difference between python now() and db now().
> Why I didn't use sleep() in previous example? Because when I run the script
> again a few seconds has already passed. And I should see at least one or two
> record.
>
> I checked your script. In my case I got the error:
>
> Traceback (most recent call last):
> File "path_to_test.py", line 45, in <module>
> assert len(with_error.all()) == 10
> AssertionError
>
> I checked one more time. My docker-compose:
>
> version: '2'
> services:
> db:
> image: postgres
> ports:
> - "5432:5432"
> environment:
> POSTGRES_DB: test
> POSTGRES_USER: postgres
> POSTGRES_PASSWORD: postgres
> restart: always
>
>
> My script:
>
> import datetime
>
> from sqlalchemy import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import *
>
> Base = declarative_base()
>
>
> class User(Base):
> __tablename__ = 'test_user'
>
> id = Column(Integer, primary_key=True)
> timeout = Column(Integer)
> last_receive_time = Column(TIMESTAMP)
>
> e = create_engine("postgresql://postgres:postgres@localhost:5432/test",
> echo='debug')
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
> session = Session(e)
> for i in range(10):
> session.add( User( timeout=1, last_receive_time=datetime.datetime.now()
> ))
>
> session.commit()
>
> import time
> time.sleep(1)
>
> correct = session.query(User)\
> .from_statement(text("""
> SELECT *
> FROM test_user
> WHERE (last_receive_time + INTERVAL '1 second' * timeout) < :now
> """)).params(now=datetime.datetime.now())
>
> assert len(correct.all()) == 10
>
> with_error = session.query(User)\
> .from_statement(text("""
> SELECT *
> FROM test_user
> WHERE (last_receive_time + INTERVAL '1 second' * timeout) < NOW()
> """))
>
> assert len(with_error.all()) == 10
>
>
> What I doing wrong? Sorry but it's really strange.
>
> On Wednesday, June 27, 2018 at 7:06:04 PM UTC+3, Mike Bayer wrote:
>>
>> I apologize for my inpatient tone in my previous email. I would hope
>> that everyone in this mailing list feels welcome.
>>
>> On Wed, Jun 27, 2018, 10:59 AM Mike Bayer <mik...@zzzcomputing.com> wrote:
>>>
>>> this is a very simple issue to debug with basic programming
>>> techniques, first off, put echo='debug' in your create_engine so you
>>> can see all SQL and results:
>>>
>>> e = create_engine("postgresql://scott:tiger@localhost/test",
>>> echo='debug')
>>>
>>>
>>> next, if you are running over to a SQL command line vs. running a
>>> program, the big difference is that *time has passed*. Put a sleep in
>>> your program and there is your row:
>>>
>>> from sqlalchemy import *
>>> from sqlalchemy.orm import *
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy.ext.declarative import declared_attr
>>> import datetime
>>>
>>> Base = declarative_base()
>>>
>>> class User(Base):
>>> __tablename__ = 'test_user'
>>>
>>> id = Column(Integer, primary_key=True)
>>> timeout = Column(Integer)
>>> last_receive_time = Column(TIMESTAMP)
>>>
>>> e = create_engine("postgresql://scott:tiger@localhost/test",
>>> echo='debug')
>>> Base.metadata.drop_all(e)
>>> Base.metadata.create_all(e)
>>>
>>> session = Session(e)
>>> for i in range(10):
>>> session.add( User( timeout=1,
>>> last_receive_time=datetime.datetime.now() ))
>>>
>>> session.commit()
>>>
>>> import time
>>> time.sleep(1)
>>>
>>> query = session.query(User)\
>>> .from_statement(text("""
>>> SELECT *
>>> FROM test_user
>>> WHERE (last_receive_time + INTERVAL '1 second' * timeout) < NOW()
>>> """))
>>>
>>> assert len(query.all()) == 10
>>>
>>>
>>>
>>> On Wed, Jun 27, 2018 at 6:30 AM, Danila Ganchar
>>> <danila....@gmail.com> wrote:
>>> > Ok. I ran the script 10 times on a clean table.
>>> > Now I run raw sql:
>>> >
>>> > SELECT *
>>> > FROM test_user
>>> > WHERE (last_receive_time + INTERVAL '1 second' * timeout) < NOW();
>>> >
>>> > The result: 10 records.
>>> > Now I run script one more time. Still 0 records using from_statement.
>>> >
>>> > amount records using NOW() = 0
>>> >
>>> > As I understand it, is this correct? This is just unclear because
>>> > from_statement + text works not like raw query.
>>> >
>>> > On Tue, Jun 26, 2018 at 7:42 PM, Mike Bayer <mik...@zzzcomputing.com>
>>> > wrote:
>>> >>
>>> >> definitely, triyng to match up python .now() with database .now() is
>>> >> not going to work, in particular since .now() in the database is often
>>> >> the transaction start time, not the actual time.
>>> >>
>>> >> On Tue, Jun 26, 2018 at 12:28 PM, Jonathan Vanasco
>>> >> <jona...@findmeon.com> wrote:
>>> >> > the difference is possibly because this is calculated in Python,
>>> >> > each
>>> >> > time
>>> >> > it is executed:
>>> >> >
>>> >> > datetime.datetime.now()
>>> >> >
>>> >> > this is calculated in Postgres, and refers to the beginning of the
>>> >> > transaction; it does not change across the transaction.
>>> >> >
>>> >> > NOW()
>>> >> >
>>> >> > --
>>> >> > 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+...@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+...@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 -
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.
