[sqlalchemy] JSONB double casting
I'm having some difficulty using SQLAlchemy's jsonb operators to produce my
desired SQL.
Intended SQL:
SELECT *
FROM foo
WHERE foo.data-'key1' ? 'a'
...where `foo.data` is formatted like this:
{
'key1': ['a', 'b', 'c'],
'key2': ['d', 'e', 'f']
}
So, I'm trying to find records where the array associated with `key1`
contains some value, 'a' in this case.
I thought it'd be a straightforward query, like:
sess.query(Foo).filter(Foo.data['key1'].has_key('a')).all()
But this is yielding:
AttributeError: Neither 'JSONElement' object nor 'Comparator' object
has an attribute 'has_key'
So I changed the query to:
sess.query(Foo).filter(Foo.data['key1'].cast(JSONB).has_key('a')).all()
But this query produces the following SQL statement:
SELECT *
FROM foo
WHERE (foo.data-'key1')::JSONB ? 'a'
Here, the `-` operator is casting the jsonb value associated with key
`key1` to text, which I then have to cast back to jsonb in order to use the
`?` operator (jsonb-specific) to check if a string is contained in the
first value.
Any ideas?
Thanks.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: JSONB double casting
Test data attached. Perhaps I'm doing something else wrong?
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import Session
from sqlalchemy.schema import Column
from sqlalchemy.types import Integer
from sqlalchemy.dialects.postgresql import JSONB
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
data = Column(JSONB)
database_url = postgresql://brian@10.0.1.10:5432/test
e = create_engine(database_url, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
# Insert data
user1 = Foo(id=1, data={'key1': ['a', 'b', 'c'], 'key2': ['d', 'e', 'f']})
user2 = Foo(id=2, data={'key1': ['g', 'h', 'i'], 'key2': ['j', 'k', 'l']})
user3 = Foo(id=3, data={'key1': ['m', 'n', 'o'], 'key2': ['p', 'q', 'r']})
sess.add_all([user1, user2, user3])
sess.commit()
# Tests
# stmt1 = sess.query(Foo).filter(Foo.data['key1'].has_key('a')).all()
# stmt2 = sess.query(Foo).filter(Foo.data['key1'].cast(JSONB).has_key('a')).all()
Re: [sqlalchemy] JSONB double casting
`type_coerce()` did the trick. Thanks, Mike! On Wednesday, March 18, 2015 at 12:55:57 PM UTC-4, Michael Bayer wrote: try using the type_coerce() function instead of cast, it should give you the has_key() but won’t change the SQL. (type_cast(Foo.data[‘key’], JSONB).has_key()) just a guess. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: When to use .cte(), .alias(), or .subquery() in a UNION of CTEs
Thanks, Jonathan. I must have read your earlier post a dozen times, and it certainly pointed me in the right direction -- at least, enough to get my query working. I'm going to study your test a bit -- thanks for posting it. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Bound parameter on Postgres/psycopg2 Range data types
With the following model, I can currently set postgres range data types in
2 ways:
*Model:*
from sqlalchemy.dialects.postgresql import INT4RANGE
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
bar = Column(INT4RANGE)
*Method #1 - as string data type:*
foo.bar = '[{lower},{upper}]'.format(min=baz, max=qux)
*Results in the following being committed:*
INFO [sqlalchemy.engine.base.Engine][Dummy-3] {'bar': *'[1, 10]'*,
'foo_id': 1}
With the corresponding range being *'[1, 10]'*, as desired.
*Method #2 - as NumericRange data type:*
from psycopg2.extras import NumericRange
foo.bar = NumericRange(lower=baz, upper=qux, bounds='[]')
*Results in the following being committed:*
INFO [sqlalchemy.engine.base.Engine][Dummy-4] {'bar': *NumericRange(1, 10,
'[]')*, 'foo_id': 1}
With the corresponding range being *'[1, 11)'*.
The string method works as expected, but if I use it to set a range in the
controller (say, from a form submission) and then return that range to the
template engine, the `lower` and `upper` methods don't work because it's
currently stored as a string type instead of a NumericRange type.
Is there a better way to do this (while still using the Range data types)?
Thanks.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Bound parameter on Postgres/psycopg2 Range data types
Sqlalchemy seems to be coercing the upper boundary to be exclusive. See below tests (will need to change postgres db if you want to run them). from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.dialects.postgresql import INT4RANGE from psycopg2.extras import NumericRange Base = declarative_base() class Foo(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) range = Column(INT4RANGE) e = create_engine(postgresql://brian@10.0.1.10:5432/test, echo=True) Base.metadata.create_all(e) sess = Session(e) foo_one = Foo(id=1, range='[1, 10]') foo_two = Foo(id=2, range=NumericRange(lower=1, upper=10, bounds='[]')) sess.add_all([foo_one, foo_two]) sess.commit() #foo_one = sess.query(Foo).filter_by(id=1).first() #foo_two = sess.query(Foo).filter_by(id=2).first() # These pass assert foo_one.range == foo_two.range assert foo_one.range.lower == foo_two.range.lower assert foo_one.range.upper == foo_two.range.upper # These fail assert foo_one.range == NumericRange(lower=1, upper=10, bounds='[]') assert foo_two.range == NumericRange(lower=1, upper=10, bounds='[]') # But this passes assert foo_two.range == NumericRange(lower=1, upper=11, bounds='[)') -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Bound parameter on Postgres/psycopg2 Range data types
Actually, looks like the problem is with psycopg2's handling of range
types, specifically with integers. Test attached. Will forward to psycopg2
maintainers.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import (
INT4RANGE,
NUMRANGE
)
import psycopg2
from psycopg2.extras import NumericRange
# Sqlalchemy db interactions
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
intrange = Column(INT4RANGE)
numrange = Column(NUMRANGE)
e = create_engine(postgresql://brian@10.0.1.10:5432/app, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
# Insert via string type
foo_one = Foo(id=1, intrange='[1, 10]', numrange='[1.0, 10.0]')
# Insert via NumericRange type
foo_two = Foo(id=2, intrange=NumericRange(lower=1, upper=10, bounds='[]'), numrange=NumericRange(lower=1.0, upper=10.0, bounds='[]'))
sess.add_all([foo_one, foo_two])
sess.commit()
foo_one = sess.query(Foo).filter_by(id=1).first()
foo_two = sess.query(Foo).filter_by(id=2).first()
# Psycopg2 db interactions
conn = psycopg2.connect('dbname=app user=brian')
cur = conn.cursor()
foo_three = Foo(id=3, intrange=NumericRange(lower=1, upper=10, bounds='[]'), numrange=NumericRange(lower=1.0, upper=10.0, bounds='[]'))
cur.execute(INSERT INTO foo (id, intrange, numrange) VALUES (%s, %s, %s), ((foo_three.id,), (foo_three.intrange,), (foo_three.numrange,)))
conn.commit()
cur.execute('SELECT intrange FROM foo WHERE id=3;')
foo_three.intrange = cur.fetchone()
cur.execute('SELECT numrange FROM foo WHERE id=3;')
foo_three.numrange = cur.fetchone()
cur.close()
conn.close()
# Tests
# These pass: string and NumericRange types the same when committed
assert foo_one.intrange == foo_two.intrange
assert foo_one.numrange == foo_two.numrange
# These pass: Sqlalchemy and psycopg2 the same
assert foo_three.intrange == (foo_one.intrange,)
assert foo_three.numrange == (foo_one.numrange,)
# These pass, but should not?
assert foo_one.intrange == NumericRange(lower=1, upper=11, bounds='[)')
assert foo_two.intrange == NumericRange(lower=1, upper=11, bounds='[)')
assert foo_three.intrange == (NumericRange(lower=1, upper=11, bounds='[)'),)
# These pass: numeric types work fine
assert foo_one.numrange == NumericRange(lower=1.0, upper=10.0, bounds='[]')
assert foo_two.numrange == NumericRange(lower=1.0, upper=10.0, bounds='[]')
assert foo_three.numrange == (NumericRange(lower=1.0, upper=10.0, bounds='[]'),)
# These fail, but should pass?
assert foo_one.intrange == NumericRange(lower=1, upper=10, bounds='[]')
assert foo_two.intrange == NumericRange(lower=1, upper=10, bounds='[]')
assert foo_three.intrange == (NumericRange(lower=1, upper=10, bounds='[]'),)
[sqlalchemy] Modified mapping in composite association proxy causes conflict with persistent instance
Hi Mike,
I'm using your variant on the 'unique object' recipe (see previous
posting http://goo.gl/I1buRz) with some composite association proxies.
Recently, the data I've been working with introduced a duplicate in the
property I've been using with attribute_mapped_collection(), so I'm trying
to modify the collection class such that the key is based on a column in
the association object instead of a column in the 'right' table. My
modified mapping results in a FlushError when attempting to update a
UserCourse object because it conflicts with a persistent instance.
*Basic model:*
class User(Base):
id = Column(Integer, primary_key=True)
name = Column(Text)
class Course(Base):
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
class UserCourse(Base):
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
*Original use:*
user.courses['math'] = 100# user.courses[course.name] = grade
*Desired use:*
user.courses['1'] = 100# user.courses[course.id] = grade
*Original model:*
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Text)
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_title=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
class UserCourse(Base):
__tablename__ = 'user_courses'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_title'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_title, grade):
self._course_title = course_title
self.grade = grade
@property
def course_title(self):
if self.course is not None:
return self.course.title
else:
return self._course_title
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
if isinstance(instance, UserCourse):
with session.no_autoflush:
course =
session.query(Course).filter_by(title=instance._course_title).first()
if course is None:
course = Course(title=instance._course_title)
instance.course = course
*Error-producing model modified for desired use:*
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Text)
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_id=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
class UserCourse(Base):
__tablename__ = 'user_courses'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_id'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_id, grade):
self._course_id = course_id
self.grade = grade
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
if isinstance(instance, UserCourse):
with session.no_autoflush:
course = session.query(Course).filter_by
(id=instance._course_id).first()
# no way to create to Course object by id alone, but I
don't need that capability
# new UserCourse objects are limited to existing courses
instance.course = course
Seems like there's a simple way to accomplish this as the dictionary
collection is now coming directly from the association object instead of
having to hop across it to the 'courses' table. Could you point me in the
right direction? Thanks.
--
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
Re: [sqlalchemy] Modified mapping in composite association proxy causes conflict with persistent instance
Hmmm, must be a problem elsewhere. Sorry for wasting your time, Mike, but
thanks for the test case. Donation enroute.
On Tuesday, July 8, 2014 12:02:52 PM UTC-4, Michael Bayer wrote:
a test case is attached, show me the failure please, thanks.
On 7/8/14, 10:59 AM, Brian Findlay wrote:
Hi Mike,
I'm using your variant on the 'unique object' recipe (see previous
posting http://goo.gl/I1buRz) with some composite association proxies.
Recently, the data I've been working with introduced a duplicate in the
property I've been using with attribute_mapped_collection(), so I'm trying
to modify the collection class such that the key is based on a column in
the association object instead of a column in the 'right' table. My
modified mapping results in a FlushError when attempting to update a
UserCourse object because it conflicts with a persistent instance.
*Basic model:*
class User(Base):
id = Column(Integer, primary_key=True)
name = Column(Text)
class Course(Base):
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
class UserCourse(Base):
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
*Original use:*
user.courses['math'] = 100# user.courses[course.name] = grade
*Desired use:*
user.courses['1'] = 100# user.courses[course.id] = grade
*Original model:*
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Text)
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_title=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
class UserCourse(Base):
__tablename__ = 'user_courses'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_title'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_title, grade):
self._course_title = course_title
self.grade = grade
@property
def course_title(self):
if self.course is not None:
return self.course.title
else:
return self._course_title
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
if isinstance(instance, UserCourse):
with session.no_autoflush:
course =
session.query(Course).filter_by(title=instance._course_title).first()
if course is None:
course = Course(title=instance._course_title)
instance.course = course
*Error-producing model modified for desired use:*
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Text)
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_id=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
class UserCourse(Base):
__tablename__ = 'user_courses'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_id'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_id, grade):
self._course_id = course_id
self.grade = grade
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
if isinstance(instance, UserCourse):
with session.no_autoflush:
course = session.query(Course).filter_by
(id=instance._course_id).first()
# no way to create to Course object by id alone, but I
don't need that capability
# new UserCourse objects are limited to existing courses
instance.course = course
Seems like there's a simple way to accomplish this as the dictionary
collection is now coming
Re: [sqlalchemy] defaultdict functionality for association proxies
Ok, Mike. Thanks. Ended up removing the trigger from the model and pushing that code to the controller, so that foo updates the bar attribute after form data with UserCourse objects is handled. On Wednesday, July 2, 2014 9:42:11 PM UTC-4, Michael Bayer wrote: On 7/2/14, 2:59 PM, Brian Findlay wrote: Mike, thanks for the response. (1) foo updates a particular User attribute based on a calculation performed on the user.courses collection. I'm listening for the set event on UserCourse objects to trigger foo to update that User attribute, but that isn't working with new users because -- as you say -- the set event is called before the actual attribute association occurs. What is the first event I could listen for that would recognize the new attribute association? in that event I only see grade being set so it's not clear to me what the bigger picture is. If this is all within the association proxy setup and within when a new UserCourse is created, I'd have to step through w/ pdb to see when things happen, but often with these assoc proxy cases, building out a custom proxier that does the things you want is often necessary if you really want sophisticated behaviors. we don't really have a solution to the attribute events being before the thing is set. adding all new after set events isn't possible without adding even more latency, and attribute mutation operations are already a huge performance bottleneck. Association proxies and attribute events are both handy but they only go so far in their capabilities. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] defaultdict functionality for association proxies
Hi Mike (et al.),
I'm searching for a way to achieve defaultdict-like functionality for
association proxies, so that a function that refers to a collection (or key
within that collection) before it exists can create the collection/key with
a default value.
In a previous post
(https://groups.google.com/forum/#!msg/sqlalchemy/kxU-FaDGO2Q/b8ScnTXvPyIJ)
you helped me to set up a composite association proxy, where I had a User
object, a Course object, and a UserCourse object with keys to the User and
Course objects as well as users' grades for each course.
class User(Base):
__tablename__ = 'users'
# Columns
id = Column(Integer, primary_key=True)
name = Column(Text)
# Relations
courses = association_proxy(
'user_courses',
'course',
creator=lambda k, v: UserCourse(course=k, grade=v)
)
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
# Columns
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
# Composite association proxy linking users and courses with grade
class UserCourse(Base):
__tablename__ = 'user_courses'
# Columns
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
# Relations
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_title'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_title, grade):
self._course_title = course_title # temporary, will turn into a
# Course when we
attach to a Session
self.grade = grade
@property
def course_title(self):
if self.course is not None:
return self.course.title
else:
return self._course_title
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
# when UserCourse objects are attached to a Session,
# figure out what Course in the database it should point to,
# or create a new one.
if isinstance(instance, UserCourse):
with session.no _autoflush:
course = session.query(Course).filter_by(
title=instance._course_title).first()
if course is None:
course = Course(title=instance._course_title)
instance.course = course
I've since added an event listener to perform a calculation each time a
UserCourse object is set:
# Recalculate 'bar' after updating UserCourse
@event.listens_for(UserCourse.grade, 'set')
def foo(target, value, oldvalue, initiator):
courses = DBSession.query(Course).all()
user = User.from_id(target.user_id)
bar = 0
for course in courses:
bar += user.courses[course.title]
user.bar = bar
Here, 'bar' is some calculation involving a user's grade for each course.
This is a somewhat contrived model (my application isn't really about
courses and grades), but I thought it'd help to simplify my use case.
There are no issues when a user, the courses, and the user's grades already
exist in the database. However, when a new user submits a form with course
grades in it, the 'foo' function is triggered and I get
AttributeError: 'NoneType' object has no attribute 'courses'
with the traceback pointing to the line in the 'foo' function that refers
to user.courses[course.title]. I understand that columns default to the
NoneType type when the type is None or omitted, so is this a
timing/sequencing issue with my listener? Should I be using something other
than 'set' (or add another listener that is triggered first)?
If I manually enter some course grades into the database with psql, I get a
KeyError on the first course I didn't manually input, hence the request for
defaultdict-like functionality. That would at least help with the KeyError.
How would you recommend tackling these problems?
Thanks,
Brian
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] defaultdict functionality for association proxies
Mike, thanks for the response. (1) foo updates a particular User attribute based on a calculation performed on the user.courses collection. I'm listening for the set event on UserCourse objects to trigger foo to update that User attribute, but that isn't working with new users because -- as you say -- the set event is called before the actual attribute association occurs. What is the first event I could listen for that would recognize the new attribute association? (2) Re: defaultdict capabilities... There are other patterns I could use to first test if a key exists in the collection, but I was looking for a Mike Bayer implementation because it'd probably be better...grin. Sidenote: In my application, foo is actually set_user_max_interest_distance (http://pastebin.com/SMH1n9Fp), which calculates a value used to normalize some other values, but I thought it'd be easier to take the focus off the function itself in order to troubleshoot the event sequencing. -Brian On Wednesday, July 2, 2014 1:43:59 PM UTC-4, Michael Bayer wrote: On 7/2/14, 11:15 AM, Brian Findlay wrote: I've since added an event listener to perform a calculation each time a UserCourse object is set: # Recalculate 'bar' after updating UserCourse @event.listens_for(UserCourse.grade, 'set') def foo(target, value, oldvalue, initiator): courses = DBSession.query(Course).all() user = User.from_id(target.user_id) bar = 0 for course in courses: bar += user.courses[course.title] user.bar = bar Here, 'bar' is some calculation involving a user's grade for each course. This is a somewhat contrived model (my application isn't really about courses and grades), but I thought it'd help to simplify my use case. There are no issues when a user, the courses, and the user's grades already exist in the database. However, when a new user submits a form with course grades in it, the 'foo' function is triggered and I get AttributeError: 'NoneType' object has no attribute 'courses' well it's probably related to the fact that the set event is called before the actual attribute association occurs, perhaps some reentrant attribute case, not sure. I'm not sure what the purpose of foo is or how it relates to the problem stated. If the desired feature is defaultdict capabilities, that means, you want to have the get feature of the association proxy to have special behavior.It seems like you'd want to subclass AssociationDict to add that feature. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Binding base classes to different engines
My project requires querying an externally-managed database as well as a
project-specific database. What I've been doing to date is copying the
external database (which changes very infrequently) into the
project-specific database so I only need one engine and one dbsession. I'm
now trying to correct this monstrosity by binding the external-specific
classes to the external engine, still using a single session.
Per agronholm's suggestion on IRC, I'm attempting to do this via a base
class for each database in order to avoid having to individually bind each
class to the correct engine. Not quite sure how to proceed, though, because
'binds' isn't accepting my keys when those keys are empty base classes.
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
scoped_session,
sessionmaker
)
from zope.sqlalchemy import ZopeTransactionExtension
Base = declarative_base()
DBSession =
scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
class DB1(Base):
__abstract__ = True
class DB2(Base):
__abstract__ = True
class SomeInternalClass(DB1):
__tablename__ = 'table_in_db1'
class SomeExternalClass(DB2):
__tablename__ = 'table_in_db2'
db1_engine = engine_from_config(settings, 'sqlalchemy.db1.')
db2_engine = engine_from_config(settings, 'sqlalchemy.db2.')
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
This raises an exception: sqlalchemy.exc.NoInspectionAvailable: No
inspection system is available for object of type class
'sqlalchemy.ext.declarative.api.DeclarativeMeta'
But when I keep the same class structure and switch back to the original
bind, it works:
DBSession.configure(bind=db1_engine)
Of course, that means I'm back to using the monster database again. So
what's wrong this?
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Binding base classes to different engines
Not sure if __abstract__ is the way to go. Should I instead be creating
mixins?
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#mixin-and-custom-base-classes
On Saturday, April 26, 2014 5:07:23 PM UTC-4, Brian Findlay wrote:
My project requires querying an externally-managed database as well as a
project-specific database. What I've been doing to date is copying the
external database (which changes very infrequently) into the
project-specific database so I only need one engine and one dbsession. I'm
now trying to correct this monstrosity by binding the external-specific
classes to the external engine, still using a single session.
Per agronholm's suggestion on IRC, I'm attempting to do this via a base
class for each database in order to avoid having to individually bind each
class to the correct engine. Not quite sure how to proceed, though, because
'binds' isn't accepting my keys when those keys are empty base classes.
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
scoped_session,
sessionmaker
)
from zope.sqlalchemy import ZopeTransactionExtension
Base = declarative_base()
DBSession =
scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
class DB1(Base):
__abstract__ = True
class DB2(Base):
__abstract__ = True
class SomeInternalClass(DB1):
__tablename__ = 'table_in_db1'
class SomeExternalClass(DB2):
__tablename__ = 'table_in_db2'
db1_engine = engine_from_config(settings, 'sqlalchemy.db1.')
db2_engine = engine_from_config(settings, 'sqlalchemy.db2.')
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
This raises an exception: sqlalchemy.exc.NoInspectionAvailable: No
inspection system is available for object of type class
'sqlalchemy.ext.declarative.api.DeclarativeMeta'
But when I keep the same class structure and switch back to the original
bind, it works:
DBSession.configure(bind=db1_engine)
Of course, that means I'm back to using the monster database again. So
what's wrong this?
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Binding base classes to different engines
Continuing to troubleshoot. This produces the same exception:
DBSession.configure(binds={DB1: db1_engine, DB2: db1_engine})
Note that I'm binding both classes to the original engine. I thought it
would be the same as the working config:
DBSession.configure(bind=db1_engine)
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Binding base classes to different engines
Thanks, Mike. Will check this out. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Binding base classes to different engines
I'm almost certainly exposing my level of ignorance here, but does this
mean I could just replace
DBSession.configure(binds={DB1:db1_engine, DB2:db2_engine})
with
DBSession.configure(class_=MySession)
?
I suppose I could even use
DBSession = scoped_session(sessionmaker(class_=MySession,
extension=ZopeTransactionExtension()))
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Problem with list comprehension and composite association proxies
Hi,
Running python 3.3.4, pyramid 1.5b1, sqlalchemy 0.9.3.
A couple of months ago Mike helped to set up composite association proxies
in my model (
https://groups.google.com/forum/#!searchin/sqlalchemy/composite$20association$20object/sqlalchemy/kxU-FaDGO2Q/b8ScnTXvPyIJ).
I'm applying this pattern to a different project now, but running into
problems when using a list comprehension to create a list of dict objects
from the results of a query. I suspect the problem stems from (a) two
association proxies using the same attribute mapped collection or (b)
garbage collection creating stale association proxies...
The basic model looks something like this:
class User(Base):
__tablename__ = 'users'
# Columns
id = Column(Integer, primary_key=True)
name = Column(VARCHAR(50), nullable=False)
# Relationships
interests = association_proxy(
'user_interests',
'value',
creator=lambda k, v: UserInterest(interest_name=k, value=v)
)
...
class Occupation(Base):
__tablename__ = 'occupations'
# Columns
id = Column(Numeric(8, 2), primary_key=True)
title = Column(VARCHAR(150), nullable=False)
# Relationships
interests = association_proxy(
'occupation_interests',
'value',
creator=lambda k, v: OccupationInterest(interest_name=k, value=v)
)
...
class Interest(Base):
__tablename__ = 'interests'
# Columns
id = Column(Integer, primary_key=True)
name = Column(String, unique=True)
# Composite association proxies linking the tables
class UserInterest(Base):
__tablename__ = 'user_interests'
# Columns
user_id = Column(ForeignKey('users.id'), primary_key=True)
interest_id = Column(ForeignKey('interests.id'), primary_key=True)
value = Column(Numeric(3, 2))
# Relationships
user = relationship(
'User',
backref=backref(
'user_interests',
collection_class=attribute_mapped_collection('interest_name'),
cascade='all, delete-orphan'
)
)
interest = relationship('Interest')
def __init__(self, interest_name, value):
self._interest_name = interest_name
self.value = value
@property
def interest_name(self):
if self.interest is not None:
return self.interest.name
else:
return self._interest_name
...
class OccupationInterest(Base):
__tablename__ = 'occupation_interests'
# Columns
occupation_id = Column(ForeignKey('occupations.id'), primary_key=True)
interest_id = Column(ForeignKey('interests.id'), primary_key=True)
value = Column(Numeric(3, 2))
# Relationships
occupation = relationship(
'Occupation',
backref=backref(
'occupation_interests',
collection_class=attribute_mapped_collection('interest_name'),
cascade='all, delete-orphan'
)
)
interest = relationship('Interest')
def __init__(self, interest_name, value):
self._interest_name = interest_name
self.value = value
@property
def interest_name(self):
if self.interest is not None:
return self.interest.name
else:
return self._interest_name
...
This works fine. Objects of the Occupation class are rated against the same
interest scale as objects of the User class, and I can get the objects'
interest values with user.interests['Social'] or
occupation.interests['Social']. If I query my database for a list of
Occupation objects, I can use a list comprehension to create a list of dict
objects without issues.
However, when I added a method to calculate the euclidean distance between
a User object and an Occupation object, I ran into KeyErrors.
user = User.from_request(request)
interests = DBSession.query(Interest).order_by(Interest.id).all()
occupations = DBSession.query(Occupation).limit(10)
def get_distance(occupation, user):
d_max = Decimal(math.sqrt(6 ** 3))
d_squared = 0
for i in interests:
d_squared += (user.interests[i.name] -
occupation.interests[i.name]) ** 2
d = Decimal(math.sqrt(d_squared))
return (d_max - d) / d_max
distances = [
dict(
name=o.title,
distance=get_distance(o, user),
) for o in occupations
Traceback (most recent call last):
File /lib/python3.3/site-packages/pyramid_debugtoolbar/toolbar.py, line
172, in toolbar_tween
response = _handler(request)
File
/lib/python3.3/site-packages/pyramid_debugtoolbar/panels/performance.py,
line 55, in resource_timer_handler
result = handler(request)
File /lib/python3.3/site-packages/pyramid/tweens.py, line 21, in
excview_tween
response = handler(request)
File /lib/python3.3/site-packages/pyramid_tm/__init__.py, line 82, in
tm_tween
reraise(*exc_info)
File /lib/python3.3/site-packages/pyramid_tm/compat.py, line 13, in
reraise
raise value
File
Re: [sqlalchemy] Problem with list comprehension and composite association proxies
Thanks, Mike. I found some records in the occupations table without interest values in the occupations_interests table, explaining the KeyErrors. Facepalm. -Brian -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Inverse the mapping in a composite association proxy
Hi, all. I've been trying to modify the example of a composite association
proxy
(http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html#composite-association-proxies)
to fit my needs.
In the documentation example, there is a User object, a Keyword object, and
a UserKeyword association object that stores a 'special_key' for each of a
user's keywords. In the provided example, the result is a collection of
dictionaries where the 'special_key' is the key and the 'keyword' is the
value. I'm trying to inverse that mapping.
In my particular use case (which I've simplified so as to make it as clear
as possible...I hope), I have a User object (a student), a Course object
(an academic course), and a UserCourse association object that stores each
user's grade for each course. My goal is to be able to set a student's
grade something like this:
user.course['math'] = 100
This is what I've come up with, but it (obviously) isn't working yet.
from sqlalchemy import Column, Integer, Text, ForeignKey
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
backref
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
# Columns
id = Column(Integer, primary_key=True)
name = Column(Text)
# Relations
courses = association_proxy(
'user_courses',
'course',
creator=lambda k, v: UserCourse(course=k, grade=v)
)
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
# Columns
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
# Composite association proxies linking users and preferences
class UserCourse(Base):
__tablename__ = 'user_courses'
# Columns
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
# Relations
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('grade'),
cascade='all, delete-orphan'
)
)
c = relationship('Course')
course = association_proxy('c', 'title')
I'd really appreciate anyone's help here, even if it's just showing me how
to modify the example in the documentation.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Inverse the mapping in a composite association proxy
Well, if it isn't the man himself. Mike, you're awesome -- thanks for the hand-holding. Thanks for reading into my use case and providing the second example. Also, thanks for the thorough documentation (on SQLAlchemy and Mako). This would be infinitely more difficult without it. On another note (Mako-related), I'm sure I'm not the only one who would enjoy a follow-up to Better Form Generation with Mako and Pylons (http://techspot.zzzeek.org/2008/07/01/better-form-generation-with-mako-and-pylons/) for Pyramid users...grin. On Wednesday, January 8, 2014 7:44:00 PM UTC-5, Michael Bayer wrote: OK well to do it exactly the way the example does it, each time we create a UserCourse, it will also create a Course. That’s pretty simple, we use two association proxies, one for User.courses and the other for UserCourse.course, mappings are like this: -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Inverse the mapping in a composite association proxy
Mike,
It took a few hours to wrap my head around your work and adapt it to my
actual use case, but it's working great now...except for a particular case
when used with templates.
Basically, I'm querying for relevant courses and then iterating over the
results to construct a form for grade entry, similar to this:
% for course in courses:
label${course}/label
input name=${course} value=${user.courses[course]}/
% endfor
This works well when I've already created a record linking a student and
course in the 'user_courses' association table (i.e.,
user.courses['somecourse'] exists), but I can't use this construct *in my
template* to set a grade for a course that I haven't already associated
with the student (because user.courses['newcourse'] doesn't at the time the
user object is passed to Mako).
This is precisely the scenario you used for testing, so I know it's not an
issue with the SQLAlchemy schema. Hard-coding it works, also (i.e. in the
controller, setting user.courses['newcourse'] to the value captured from a
form submission).
How would you recommend tackling this? Should it be addressed in the
controller, the template, or perhaps in the schema (__init__ or a
listener?)?
Thanks again.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
