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]
> <javascript:>> 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] <javascript:>> 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]
>> <javascript:>>
>> > 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] <javascript:>> 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] <javascript:>.
>> >> > To post to this group, send email to [email protected]
>> <javascript:>.
>> >> > 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] <javascript:>.
>> >> To post to this group, send email to [email protected]
>> <javascript:>.
>> >> 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] <javascript:>.
>> > To post to this group, send email to [email protected]
>> <javascript:>.
>> > 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.
