check that the timezones are matching up, e.g. that your python times aren't X hours off from the PG one, stuff like that
On Fri, Jun 29, 2018 at 11:44 AM, Mike Bayer <[email protected]> wrote: > 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 > <[email protected]> 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 <[email protected]> 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 >>>> <[email protected]> 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 <[email protected]> >>>> > 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 >>>> >> <[email protected]> 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 [email protected]. >>>> >> > To post to this group, send email to [email protected]. >>>> >> > 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 [email protected]. >>>> >> To post to this group, send email to [email protected]. >>>> >> 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 [email protected]. >>>> > To post to this group, send email to [email protected]. >>>> > 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 [email protected]. >> To post to this group, send email to [email protected]. >> 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
