[sqlalchemy] senseless warning messages escape python logging
Dear SQLAchemistas, this is an issue, that my apps choke on from time to time, _related_ to SQLA. Although, logging is set up correctly, some operations spit out senseless warning messages like this: /usr/lib/python2.6/site-packages/sqlalchemy/engine/default.py:324: Warning: Data truncated for column 'sendungref1' at row 1 cursor.execute(statement, parameters) FYI, this is the solely message, catched by cron, from code that is exercised heavily. Sure, I understand, that with some probability, this is triggered from MySql- python-1.2.3 under the covers, and I know, that the value given for this column was too big, but without any context, it doesn't help in any way. So strictly speaking, I'm barking up the wrong tree, but the question is, have you figured out a way to either catch or suppress those warnings? Is there a mysql adapter, that cooperates better in this respect? Thanks for your insights, Pete -- 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] how to query one-to-many relationships, in a complicated way?
Hi list,
For this problem I am even having trouble think of a proper subject for it.
I try my best to express as clear as possible and sorry for any confusions.
Say there are three classes with relationship defined as below:
class User(Base):
__tablename__ = 'users'
id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
class Feedback(Base):
__tablename__ = 'feedbacks'
id = Column(Integer, Sequence('feedback_id_seq'), primary_key=True)
service_id = Column(Integer, ForeignKey('services.id'))
score=Column(Integer)
service=relationship('Service', backref=backref(feedbacks))
class Service(Base):
__tablename__ = 'services'
id = Column(Integer, Sequence('service_id_seq'), primary_key=True)
provider_id = Column(Integer, ForeignKey('users.id'))
requester_id = Column(Integer, ForeignKey('users.id'))
provider = relationship('User', foreign_keys=provider_id, backref='
services_received')
requester = relationship('User', foreign_keys=requester_id, backref='
services_sent')
*User* and *Service* is a one to many relationship, and a user can be
a Service provider or requester.
For *Service* and *Feedback, *it is a one to many relationship too. A
requester can give a score to the Service.
*The question is, how can I get the sum(scores) of all services for a user?*
I thought I could do sth like:
#provider is a given object
*provider.services_recieved.feedbacks*
but it threw error:
*AttributeError: 'InstrumentedList' object has no attribute 'feedbacks'*
and I thought I also could :
*provider.services_recieved.query(Feedback)*
apparently it didn't have a query function.
I think by adding a user_id to the Feedback class will make this task
easier, however, that field is just a duplication for only this purpose.
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.
Re: [sqlalchemy] senseless warning messages escape python logging
we use the warnings filter to turn warnings into exceptions, either from the Python command line or programatically: https://docs.python.org/2/library/warnings.html https://docs.python.org/2/using/cmdline.html#cmdoption-W On 7/2/14, 3:26 AM, Hans-Peter Jansen wrote: Dear SQLAchemistas, this is an issue, that my apps choke on from time to time, _related_ to SQLA. Although, logging is set up correctly, some operations spit out senseless warning messages like this: /usr/lib/python2.6/site-packages/sqlalchemy/engine/default.py:324: Warning: Data truncated for column 'sendungref1' at row 1 cursor.execute(statement, parameters) FYI, this is the solely message, catched by cron, from code that is exercised heavily. Sure, I understand, that with some probability, this is triggered from MySql- python-1.2.3 under the covers, and I know, that the value given for this column was too big, but without any context, it doesn't help in any way. So strictly speaking, I'm barking up the wrong tree, but the question is, have you figured out a way to either catch or suppress those warnings? Is there a mysql adapter, that cooperates better in this respect? Thanks for your insights, Pete -- 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] failed to locate a name error when using model with relationship
Hi Michael, thank you for the answer.
Both classes are in the same file so I don't see how it could be possible
that one class is used while other is not imported.
Could you help me with that ?
Andrey
вторник, 1 июля 2014 г., 21:05:11 UTC+3 пользователь Michael Bayer написал:
On 7/1/14, 1:17 PM, trust...@gmail.com javascript: wrote:
I have two classes, Artwork and Person. Artwork has a relationship to Person.
However, when I try to use them, I get an error thrown:
InvalidRequestError: When initializing mapper Mapper|Artwork|artwork,
expression 'Person' failed to locate a name (name 'Person' is not
defined). If this is a class name, consider adding this relationship() to
the class 'model.Artwork' class after both dependent classes have been
defined.
Here are the classes themselves, defined in model/__init__.py
class Artwork(db.Model, SimpleSerializeMixin):
id = db.Column(db.Integer, primary_key=True)
artist_id = db.Column(db.String(256), db.ForeignKey('person.sub'))
artist = db.relationship('Person', backref='artworks')
class Person(db.Model, SimpleSerializeMixin):
sub = db.Column(db.String(256), primary_key=True)
I checked with debugger and in sqlalchemy/ext/declarative/clsregistry.py
(_class_resolver.__call__())
there is a line:
x = eval(self.arg, globals(), self._dict)
No Person or Artwork or any other class defined in the file are
present in globals(). self._dict is empty
So it fails with an NameError exception.
What could be the issue ?
it's usually that the Person code wasn't run, e.g. that the module in
which it is located was not imported, before you tried to use the Artwork
class. All the tables/classes can be introduced to the Python interpreter
in any order, but once you try to use the mapping, e.g. make an object or
run a query, it resolves all the links and everything has to be present.
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] failed to locate a name error when using model with relationship
On 7/2/14, 10:21 AM, trusted...@gmail.com wrote:
Hi Michael, thank you for the answer.
Both classes are in the same file so I don't see how it could be
possible that one class is used while other is not imported.
Could you help me with that ?
that would mean you're doing something that is invoking Artwork as a
mapped class and causing the mapper config step to occur before it gets
down to Person. The stack trace here would show exactly where that
originates.
Andrey
вторник, 1 июля 2014 г., 21:05:11 UTC+3 пользователь Michael Bayer
написал:
On 7/1/14, 1:17 PM, trust...@gmail.com javascript: wrote:
I have two classes, Artwork and Person. Artwork has a relationship to
Person.
However, when I try to use them, I get an error thrown:
InvalidRequestError: When initializing mapper
Mapper|Artwork|artwork, expression 'Person' failed to locate
a name (name 'Person' is not defined). If this is a class
name, consider adding this relationship() to the class
'model.Artwork' class after both dependent classes have been
defined.
Here are the classes themselves, defined in model/__init__.py
class Artwork(db.Model, SimpleSerializeMixin):
id = db.Column(db.Integer, primary_key=True)
artist_id = db.Column(db.String(256), db.ForeignKey('person.sub'))
artist = db.relationship('Person', backref='artworks')
class Person(db.Model, SimpleSerializeMixin):
sub = db.Column(db.String(256), primary_key=True)
I checked with debugger and in
sqlalchemy/ext/declarative/clsregistry.py
(_class_resolver.__call__())
there is a line:
x = eval(self.arg, globals(), self._dict)
No Person or Artwork or any other class defined in the file
are present in globals(). self._dict is empty
So it fails with an NameError exception.
What could be the issue ?
it's usually that the Person code wasn't run, e.g. that the module
in which it is located was not imported, before you tried to use
the Artwork class. All the tables/classes can be introduced to
the Python interpreter in any order, but once you try to use the
mapping, e.g. make an object or run a query, it resolves all the
links and everything has to be present.
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy
http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://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 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] Postgresql - Index on a json field
This worked as described. Thanks again. I have a followup question. It
doesn't seem like there's an analog to table.create(checkfirst=True) for an
Index. I found this issue
https://bitbucket.org/zzzeek/sqlalchemy/issue/527/indexcreate-should-take-checkfirst
that seems to mention having this functionality but it doesn't look like
it's been implemented? Is there a normal workaround for this?
On Tuesday, July 1, 2014 10:03:40 AM UTC-7, Phillip Aquilina wrote:
Ah! I'll give that a try. Thanks Mike.
On Monday, June 30, 2014 10:23:13 PM UTC-7, Michael Bayer wrote:
per the SO answer, you're looking for CREATE INDEX ON
publishers((info-'name'));. Either you can emit this directly as a
string, or use Index, just as it states:
from sqlalchemy import create_engine, Integer, Index, Table, Column,
MetaData
from sqlalchemy.dialects.postgresql import JSON
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
m = MetaData()
publishers = Table('publishers', m, Column('id', Integer), Column('info',
JSON))
Index(foo, publishers.c.info['name'].astext)
m.create_all(e)
output:
CREATE TABLE publishers (
id INTEGER,
info JSON
)
CREATE INDEX foo ON publishers ((info - 'name'))
On 7/1/14, 1:14 AM, Mike Bayer wrote:
I'm not familiar with any other style of index for this column type. If
you can show me at
http://www.postgresql.org/docs/9.4/static/datatype-json.html or wherever
what specific DDL you're looking for, you can simply emit it using
engine.execute(ddl).
On 6/30/14, 11:02 PM, Phillip Aquilina wrote:
Thanks for replying. I've read through that doc and I still don't see how
that addresses my question. Is there somewhere in there that describes how
to create an index on a json field? It seems like to me it's simple to
create an index on a column but this would be creating an index on nested
data inside the column.
- Phil
On Monday, June 30, 2014 6:07:51 PM UTC-7, Michael Bayer wrote:
SQLAlchemy's API allows CREATE INDEX via the Index construct:
http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes
On 6/30/14, 6:21 PM, Phillip Aquilina wrote:
Using postgresql, I have a JSON type column. My understanding from their
docs was that only jsonb columns could have an index created on them (a
feature of postgresql 9.4) but then I found an SO answer
http://stackoverflow.com/questions/17807030/how-to-create-index-on-json-field-in-postgres-9-3
that
said otherwise. I haven't had the chance to test it since I'm away from my
dev environment, but the sqlalchemy docs seem to support this idea
http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON,
mentioning the availability of Index operations.
Unless I'm missing something obvious (very possible), it seems like
this can be done through sql, but is there a way to create an index on a
json field through the sqlalchemy api? I can't seem to find a way to do
this.
Thanks,
Phil
--
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 http://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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Postgresql - Index on a json field
On 7/2/14, 11:38 AM, Phillip Aquilina wrote:
This worked as described. Thanks again. I have a followup question.
It doesn't seem like there's an analog to
table.create(checkfirst=True) for an Index. I found this issue
https://bitbucket.org/zzzeek/sqlalchemy/issue/527/indexcreate-should-take-checkfirst
that seems to mention having this functionality but it doesn't look
like it's been implemented? Is there a normal workaround for this?
typically Index is bundled with its parent Table, and the conditional
aspect of it comes from the Table being created conditionally.
Otherwise, if the Index is added after the fact, typically people are
using migration tools to get that so that's where the conditional aspect
comes in. So the case where Index.create() really needs conditional
behavior is slim.You can for now use inspector:
from sqlalchemy import inspect
insp = inspect(engine)
for idx in insp.get_indexes('tablename'):
if idx['name'] == 'myname':
break
else:
Index('myname', x, y, z).create(engine)
On Tuesday, July 1, 2014 10:03:40 AM UTC-7, Phillip Aquilina wrote:
Ah! I'll give that a try. Thanks Mike.
On Monday, June 30, 2014 10:23:13 PM UTC-7, Michael Bayer wrote:
per the SO answer, you're looking for CREATE INDEX ON
publishers((info-'name'));. Either you can emit this
directly as a string, or use Index, just as it states:
from sqlalchemy import create_engine, Integer, Index, Table,
Column, MetaData
from sqlalchemy.dialects.postgresql import JSON
e = create_engine(postgresql://scott:tiger@localhost/test,
echo=True)
m = MetaData()
publishers = Table('publishers', m, Column('id', Integer),
Column('info', JSON))
Index(foo, publishers.c.info
http://publishers.c.info['name'].astext)
m.create_all(e)
output:
CREATE TABLE publishers (
id INTEGER,
info JSON
)
CREATE INDEX foo ON publishers ((info - 'name'))
On 7/1/14, 1:14 AM, Mike Bayer wrote:
I'm not familiar with any other style of index for this
column type. If you can show me at
http://www.postgresql.org/docs/9.4/static/datatype-json.html
http://www.postgresql.org/docs/9.4/static/datatype-json.html or
wherever what specific DDL you're looking for, you can simply
emit it using engine.execute(ddl).
On 6/30/14, 11:02 PM, Phillip Aquilina wrote:
Thanks for replying. I've read through that doc and I still
don't see how that addresses my question. Is there somewhere
in there that describes how to create an index on a json
field? It seems like to me it's simple to create an index
on a column but this would be creating an index on nested
data inside the column.
- Phil
On Monday, June 30, 2014 6:07:51 PM UTC-7, Michael Bayer wrote:
SQLAlchemy's API allows CREATE INDEX via the Index
construct:
http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes
http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes
On 6/30/14, 6:21 PM, Phillip Aquilina wrote:
Using postgresql, I have a JSON type column. My
understanding from their docs was that only jsonb
columns could have an index created on them (a feature
of postgresql 9.4) but then I found an SO answer
http://stackoverflow.com/questions/17807030/how-to-create-index-on-json-field-in-postgres-9-3
that
said otherwise. I haven't had the chance to test it
since I'm away from my dev environment, but the
sqlalchemy docs seem to support this idea
http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON,
mentioning the availability of Index operations.
Unless I'm missing something obvious (very possible),
it seems like this can be done through sql, but is
there a way to create an index on a json field through
the sqlalchemy api? I can't seem to find a way to do this.
Thanks,
Phil
--
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
http://groups.google.com/group/sqlalchemy
http://groups.google.com/group/sqlalchemy.
For more options, visit
Re: [sqlalchemy] Postgresql - Index on a json field
Perfect thanks Mike.
On Wednesday, July 2, 2014 10:17:17 AM UTC-7, Michael Bayer wrote:
On 7/2/14, 11:38 AM, Phillip Aquilina wrote:
This worked as described. Thanks again. I have a followup question. It
doesn't seem like there's an analog to table.create(checkfirst=True) for an
Index. I found this issue
https://bitbucket.org/zzzeek/sqlalchemy/issue/527/indexcreate-should-take-checkfirst
that seems to mention having this functionality but it doesn't look like
it's been implemented? Is there a normal workaround for this?
typically Index is bundled with its parent Table, and the conditional
aspect of it comes from the Table being created conditionally.
Otherwise, if the Index is added after the fact, typically people are using
migration tools to get that so that's where the conditional aspect comes
in. So the case where Index.create() really needs conditional behavior is
slim.You can for now use inspector:
from sqlalchemy import inspect
insp = inspect(engine)
for idx in insp.get_indexes('tablename'):
if idx['name'] == 'myname':
break
else:
Index('myname', x, y, z).create(engine)
On Tuesday, July 1, 2014 10:03:40 AM UTC-7, Phillip Aquilina wrote:
Ah! I'll give that a try. Thanks Mike.
On Monday, June 30, 2014 10:23:13 PM UTC-7, Michael Bayer wrote:
per the SO answer, you're looking for CREATE INDEX ON
publishers((info-'name'));. Either you can emit this directly as a
string, or use Index, just as it states:
from sqlalchemy import create_engine, Integer, Index, Table, Column,
MetaData
from sqlalchemy.dialects.postgresql import JSON
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
m = MetaData()
publishers = Table('publishers', m, Column('id', Integer),
Column('info', JSON))
Index(foo, publishers.c.info['name'].astext)
m.create_all(e)
output:
CREATE TABLE publishers (
id INTEGER,
info JSON
)
CREATE INDEX foo ON publishers ((info - 'name'))
On 7/1/14, 1:14 AM, Mike Bayer wrote:
I'm not familiar with any other style of index for this column type.
If you can show me at
http://www.postgresql.org/docs/9.4/static/datatype-json.html or
wherever what specific DDL you're looking for, you can simply emit it using
engine.execute(ddl).
On 6/30/14, 11:02 PM, Phillip Aquilina wrote:
Thanks for replying. I've read through that doc and I still don't see
how that addresses my question. Is there somewhere in there that describes
how to create an index on a json field? It seems like to me it's simple to
create an index on a column but this would be creating an index on nested
data inside the column.
- Phil
On Monday, June 30, 2014 6:07:51 PM UTC-7, Michael Bayer wrote:
SQLAlchemy's API allows CREATE INDEX via the Index construct:
http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes
On 6/30/14, 6:21 PM, Phillip Aquilina wrote:
Using postgresql, I have a JSON type column. My understanding from
their docs was that only jsonb columns could have an index created on them
(a feature of postgresql 9.4) but then I found an SO answer
http://stackoverflow.com/questions/17807030/how-to-create-index-on-json-field-in-postgres-9-3
that
said otherwise. I haven't had the chance to test it since I'm away from my
dev environment, but the sqlalchemy docs seem to support this idea
http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON,
mentioning the availability of Index operations.
Unless I'm missing something obvious (very possible), it seems like
this can be done through sql, but is there a way to create an index on a
json field through the sqlalchemy api? I can't seem to find a way to do
this.
Thanks,
Phil
--
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 http://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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at
Re: [sqlalchemy] defaultdict functionality for association proxies
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.
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] (Semi-)automated way to adjust constraint names via Alembic?
So, in my ongoing quest to make my team's operations database far more sane than it currently is, I want to fix all the constraint naming in the database to match the naming convention setting I have added to my SQLAlchemy configuration for the database. I could of course go through each table and determine each by hand, but I was wondering if there was a less manual (and error-prone) way to approach this, possibly via the autogeneration feature? In case it matters, the database server is MySQL. -- - Ken Lareau -- 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
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.
Re: [sqlalchemy] (Semi-)automated way to adjust constraint names via Alembic?
Well you can get at the names that were used in the DB (using Inspector, or reflection) as well as the names that are in your metadata ([constraint for constraint in table.constraints for table in metadata.tables.values()], but as far as matching them up I'm not sure, it depends on what patterns you can find in the existing DB that you can use. maybe you can write a script that guesses, then it spits out a list of oldname-newname, then you can manually correct it. On 7/2/14, 6:08 PM, Ken Lareau wrote: So, in my ongoing quest to make my team's operations database far more sane than it currently is, I want to fix all the constraint naming in the database to match the naming convention setting I have added to my SQLAlchemy configuration for the database. I could of course go through each table and determine each by hand, but I was wondering if there was a less manual (and error-prone) way to approach this, possibly via the autogeneration feature? In case it matters, the database server is MySQL. -- - Ken Lareau -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] (Semi-)automated way to adjust constraint names via Alembic?
On Wed, Jul 2, 2014 at 6:44 PM, Mike Bayer mike...@zzzcomputing.com wrote: Well you can get at the names that were used in the DB (using Inspector, or reflection) as well as the names that are in your metadata ([constraint for constraint in table.constraints for table in metadata.tables.values()], but as far as matching them up I'm not sure, it depends on what patterns you can find in the existing DB that you can use. maybe you can write a script that guesses, then it spits out a list of oldname-newname, then you can manually correct it. Heh, very few patterns to be found, sadly. I could easily create a tabular set of data that would allow me to map names to the type of constraint and hope- fully that would be enough for me to run through them all; the biggest issues I suspect will be the multi-column constraints along with the primary keys (since it seems that MySQL uses 'PRIMARY' for the name of the constraint and I'm not even sure that's changeable (been putting together a test data- base to try it out on)). But I'll see what I can hack together. :) - Ken On 7/2/14, 6:08 PM, Ken Lareau wrote: So, in my ongoing quest to make my team's operations database far more sane than it currently is, I want to fix all the constraint naming in the database to match the naming convention setting I have added to my SQLAlchemy configuration for the database. I could of course go through each table and determine each by hand, but I was wondering if there was a less manual (and error-prone) way to approach this, possibly via the autogeneration feature? In case it matters, the database server is MySQL. -- - Ken Lareau -- 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. -- 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. -- - Ken Lareau -- 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] Automatically set primary key to None when deleted?
Suppose I have a super simple table like this: class Dinosaur(Base): __tablename__ = 'dinosaurs' id = Column(Integer, primary_key=True) name = Column(String(255)) We assume that the id is set up in such a way that by default it always gets a unique value - ie, it uses autoincrement in MySQL, or a sequence in postgres, etc. Now, suppose I get an instance of this class, and then delete it: steggy = session.query(Dinosaur).filter_by(name='Steggy').one() print steggy.id session.delete(steggy) session.commit() print steggy.id What I'd ideally like to see is that it first print the id of the row that it pulled from the database, and then print 'None': 30 None Is there any way that I can configure the id column / property so that it is automatically cleared on delete like this? If not, as a consolation prize, I'd also be interested in the easiest way to query if a given instance exists in the database - ie, I could do something like: session.exists(steggy) OR steggy.exists() ...which, in this case, would simply run a query to see if any dinosaurs exist with the name Steggy. Needing to set up some extra parameters to make this possible - such as adding a unique constraint on the name column - would be potentially possible. And yes, I know I can always fall back on just manually constructing a query against the name field myself... -- 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] Automatically set primary key to None when deleted?
On 7/2/14, 10:05 PM, Paul Molodowitch wrote:
Suppose I have a super simple table like this:
class Dinosaur(Base):
__tablename__ = 'dinosaurs'
id = Column(Integer, primary_key=True)
name = Column(String(255))
We assume that the id is set up in such a way that by default it
always gets a unique value - ie, it uses autoincrement in MySQL, or a
sequence in postgres, etc.
Now, suppose I get an instance of this class, and then delete it:
steggy = session.query(Dinosaur).filter_by(name='Steggy').one()
print steggy.id
session.delete(steggy)
session.commit()
print steggy.id
What I'd ideally like to see is that it first print the id of the row
that it pulled from the database, and then print 'None':
30
None
Is there any way that I can configure the id column / property so that
it is automatically cleared on delete like this?
the steggy object is a proxy for a database row. when you delete that
row, then commit the transaction, the object is detached from the
session, and everything on it is expired. there is no row. check
inspect(steggy).deleted, it will say True - that means in your system,
the object is meaningless. ideally no part of your program would be
looking at that proxy any more, you should throw it away. it means nothing.
as far as setting everything to None, you could try a handler like this:
@event.listens_for(Session, 'after_flush')
def after_flush(sess, ctx):
for obj in sess.deleted:
mapper = inspect(obj)
for key in mapper.attrs.keys():
obj.__dict__[key] = None
If not, as a consolation prize, I'd also be interested in the easiest
way to query if a given instance exists in the database - ie, I could do
something like:
session.exists(steggy)
OR
steggy.exists()
from sqlalchemy import inspect
def exists(session, obj):
state = inspect(obj)
return session.query(state.mapper).get(state.identity) is None
print exists(sess, a1)
...which, in this case, would simply run a query to see if any
dinosaurs exist with the name Steggy.
that's totally different. That's a WHERE criterion on the name field,
which is not the primary key. that's something specific to your class
there.
Needing to set up some extra parameters to make this possible - such
as adding a unique constraint on the name column -
OK, so you want a function that a. receives an object b. looks for
UNIQUE constraints on it c. queries by those unique constraints (I guess
you want the first one present? not clear. a table can have a lot of
unique constraints on it) that would be:
from sqlalchemy import inspect, UniqueConstraint
def exists(session, obj):
state = inspect(obj)
table = state.mapper.local_table
for const in table.constraints:
if isinstance(const, UniqueConstraint):
crit = and_(*[col == getattr(obj, col.key) for col in const])
return session.query(state.mapper).filter(crit).count() 0
else:
return False
the unique constraints are a set though. not necessarily deterministic
which one it would locate first. I'd use more of some kind of declared
system on the class:
class X(Base):
lookup_class_via = ('name',)
id = Column(Integer, primary_key=True)
name = Column(String)
from sqlalchemy import inspect, UniqueConstraint
def exists(session, obj):
crit = and_(*[col == getattr(obj, col.key) for col in
obj.__class__.lookup_class_via])
return session.query(state.mapper).filter(crit).count() 0
--
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.
