Re: [sqlalchemy] need help with query

[sector119]

With that query I get:
InvalidRequestError: Could not find a FROM clause to join from.  Tried 
joining to , but got: Can't find any 
foreign key relationships between 'category' and 'product'.

Product and category model has many to many relationship

-- 
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] need help with query

[Simon King]

http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#querying-with-joins

You want something like:

DBSession.query(Category).join(‘products’, 
‘brand’).filter(Brand.slug==brand_slug)

Hope that helps,

Simon


> On 1 Mar 2016, at 20:11, sector119  wrote:
> 
> It works, but it's not pretty )) 
> 
>brand = DBSession.query(Brand).filter_by(slug=brand_slug).one()
> return 
> DBSession.query(Category).filter(Category.overviews.any(brand_id=brand.id)).order_by(Category.name)
> 
> вторник, 1 марта 2016 г., 21:52:47 UTC+2 пользователь sector119 написал:
> Hello,
> 
> I need your help with one query, I want to get Category items that some 
> Product's of some Brand have.
> 
> I try DBSession.query(Category).filter(Category.overviews.any(Brand.slug == 
> brand_slug)).order_by(Category.name)
> But it doesn't work as expected because there is no relation between Brand 
> and other tables
> 
> 
> class Brand(Base):
> __tablename__ = 'brand'
> 
> id = Column(Integer, primary_key=True)
> name = Column(UnicodeText, nullable=False)
> slug = Column(UnicodeText, nullable=False, unique=True)
> 
> 
> class Category(Base):
> __tablename__ = 'category'
> 
> id = Column(Integer, primary_key=True)
> name = Column(UnicodeText, nullable=False)
> 
> 
> class Product(Base):
> __tablename__ = 'product'
> 
> id = Column(Integer, primary_key=True)
> brand_id = Column(Integer, ForeignKey('brand.id'), nullable=False)
> 
> name = Column(UnicodeText, nullable=False)
> 
> brand = relationship('Brand', backref='products')
> categories = relationship('Category', backref='products', 
> secondary=product_category)   # many-to-many relationship
> 
> 
> 
> 
> -- 
> 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.

-- 
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] Date range query problem

[Nana Okyere]

Ok. How do you guys suggest I make the query to capture results for the end 
point date?

-- 
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] Date range query problem

[Simon King]

On Tue, Mar 1, 2016 at 4:17 PM, Nana Okyere  wrote:

> Mike, thanks for your response. I turned on the echo to see the values.
> Looks like it is passing a datetime.date objects. So I don't see a time
> part of the date. Here's the relevant part of the echo output.
>
> I selected between two days ago and yesterday. It includes results for two
> days ago but not results that match yesterday's date.
>
> WHERE work_force_planning_tab.last_updated_timestamp BETWEEN
> :last_updated_times
> tamp_1 AND :last_updated_timestamp_2
> 2016-03-01 10:12:36,229 INFO sqlalchemy.engine.base.Engine
> {'last_updated_timest
> amp_1': datetime.date(2016, 2, 28), 'last_updated_timestamp_2':
> datetime.date(20
> 16, 2, 29)}
>
>
I think Mike meant that the DATE type in the database itself stores the
time as well:

https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i1847

Presumably you'll get the same result if you run something like this
directly against the database:

SELECT *
FROM work_force_planning_tab
WHERE last_updated_timestamp BETWEEN TO_DATE('2016/02/28', '/mm/dd')
AND TO_DATE('2016/02/29', '/mm/dd')

(I may have the syntax wrong - I don't use Oracle)

Simon

-- 
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] Date range query problem

[Nana Okyere]

Mike, thanks for your response. I turned on the echo to see the values. 
Looks like it is passing a datetime.date objects. So I don't see a time 
part of the date. Here's the relevant part of the echo output.

I selected between two days ago and yesterday. It includes results for two 
days ago but not results that match yesterday's date.

WHERE work_force_planning_tab.last_updated_timestamp BETWEEN 
:last_updated_times
tamp_1 AND :last_updated_timestamp_2
2016-03-01 10:12:36,229 INFO sqlalchemy.engine.base.Engine 
{'last_updated_timest
amp_1': datetime.date(2016, 2, 28), 'last_updated_timestamp_2': 
datetime.date(20
16, 2, 29)}

-- 
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 flush trigger misunderstanding or unexpected behaviour

[Bastien Sevajol]

Hi,

I'm confronted to an misunderstanding or unexpected behavior of session 
trigger flush. I want to manage revision of a document, like in this 

 
doc example.
But the behavior is unexpected: I want to dot something like this:

1. create a document with it's first revision
2. flush -> creation of document + revision id database
3. modify revision of dicument
4. flush -> session *before_flush* 

 
hook create a new revision instead update it

But the following example code make a unexpected flush in point "3." 
Example test.py:

from sqlalchemy import create_engine, Column, Integer, Text, ForeignKey, 
inspect, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, SessionExtension, relationship

DeclarativeBase = declarative_base()


class ContentRevision(DeclarativeBase):
__tablename__ = 'content_revision'
revision_id = Column(Integer, primary_key=True)
content_id = Column(Integer, ForeignKey('content.id'))
description = Column(Text())
title = Column(String(32))
node = relationship("Content", foreign_keys=[content_id], back_populates
="revisions")

@classmethod
def new_from(cls, revision):
columns = (column.key for column in inspect(cls).attrs if column.key 
!= 'revision_id')
new_revision = cls()

for column_name in columns:
column_value = getattr(revision, column_name)
setattr(new_revision, column_name, column_value)

return new_revision


class Content(DeclarativeBase):
__tablename__ = 'content'
id = Column(Integer, primary_key=True)
revisions = relationship("ContentRevision",
 foreign_keys=[ContentRevision.content_id],
 back_populates="node")


class VersionExtension(SessionExtension):
def before_flush(self, session, flush_context, instances):
print('EVENT: before_flush')
for instance in session.dirty:
if isinstance(instance, ContentRevision):
print('EVENT: before_flush: ContentRevision found in dirty')
if session.is_modified(instance, passive=True):
print('EVENT: before_flush: ContentRevision has been 
modified')
if inspect(instance).has_identity:
print('EVENT: before_flush: revision updated, 
create new instead')
previous_revision = instance
new_revision = ContentRevision.new_from(instance)
session.expunge(previous_revision)
session.add(new_revision)
else:
print('EVENT: before_flush: revision is a new 
revision, no change')

# Prepare database and session

engine = create_engine('sqlite://', echo=False)
DeclarativeBase.metadata.create_all(engine)
session_maker = sessionmaker(engine, extension=[VersionExtension()])
session = session_maker()

# Start example scenario

print('SCENARIO: Create new content')
content1 = Content(revisions=[ContentRevision(description='rev1', title=
'title1')])
print('SCENARIO: Add content to session')
session.add(content1)
print('SCENARIO: flush session')
session.flush()

assert session.query(ContentRevision.revision_id,
 ContentRevision.title,
 ContentRevision.description).order_by(ContentRevision.
revision_id).all() == \
[(1, 'title1', 'rev1')]

print('SCENARIO: update revision title')
content1.revisions[0].title = 'title2'


assert session.query(ContentRevision.revision_id,
 ContentRevision.title,
 ContentRevision.description).order_by(ContentRevision.
revision_id).all() == \
[(1, 'title1', 'rev1'), (2, 'title2', 'rev1')]  # Why session 
flushed here ?


print('SCENARIO: update revision description')
content1.revisions[0].description = 'rev2'

assert session.query(ContentRevision.revision_id,
 ContentRevision.title,
 ContentRevision.description).order_by(ContentRevision.
revision_id).all() == \
[(1, 'title1', 'rev1'), (2, 'title2', 'rev1')]  # Why session don't 
flushed here ?

print('SCENARIO: flush session')
session.flush()

assert session.query(ContentRevision.revision_id,
 ContentRevision.title,
 ContentRevision.description).order_by(ContentRevision.
revision_id).all() == \
[(1, 'title1', 'rev1'), (2, 'title2', 'rev1')]  # Why session don't 
flushed here ?

print('SCENARIO: end')


Output is (for python3.4 test.py):

SCENARIO: Create new content
SCENARIO: Add content to session
SCENARIO: flush session
EVENT: before_flush
SCENARIO: update revision title
EVENT: before_flush
EVENT: before_flush: ContentRevision found in