[sqlalchemy] Filtering by an attribute of a related class (abusing generative filters...)
Hi all,
I've got a requirement that seems like it should be fairly
straightforward, but my own attempts to work out the necessary code
have just become more and more contrived, and still don't work :( so
I'm hoping someone more familiar with SQLA's design can suggest a
better approach!
Background:
I have a function which creates a query 'q' on a mapped class (Foo),
then applies one or more filter criteria, before finally executing the
query and returning a list of results. In general the individual
criteria might apply to any attribute of the mapped class; I have the
attribute name (at this point anyway) as a string 'attr'. The criteria
may also use various modes; I convert these into an SQL operator
'sqlop' (so for example 'exact' becomes '=', 'exclude' becomes ' NOT
LIKE ', etc.)
So the generative filter() call is basically just:
q = q.filter(attr + sqlop + :val).params(val=test)
Now the problem:
That all works fine until I want to filter on attributes that are
relational. Say Foo has a one-to-many or many-to-one relation to Bar
('rBar'). When I try to filter Foo on rBar, I want those Foos whose
rBar collection includes at least one Bar whose Title attribute
matches the criterion if it's one-to-many, or those Foos whose rBar
points to a Bar whose Title matches the criterion if it's many-to-
one.
I can do this using select_from(some_join) followed by a filter() call
similar to the above, but I can't figure out how to combine that with
generative, since it replaces the underlying table. (I need to stay
generative, because I might subsequently want to filter the same
attribute again using another criterion.) What should I be doing
instead?
Huge bonus points if your solution also works for a self-referential
many-to-many relation that uses an association table (there are two,
'rFooPrecedes' and 'rFooFollows', which are Foo-Foo links via the
FooPrecedence mapped class; here I'd want to filter based on the
titles of the attached Foos, not on the titles of the FooPrecedence
entries that link them).
Cheers,
--
Ben
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Inspecting DDL of a Table object, without executing the code
I suspect the answer to this is obvious, but it eludes me. I have
defined some SQlAlchemy table classes, using declarative_base, to
interact with some tables in an SQL Server database. I'm unsure that
the types I've chosen are correct.
I want to print the CREATE TABLE ddl, without executing it. How can I
do this?
With thanks, Alex
CONN_STR = 'mssql://user:p...@host/database'
engine = sqlalchemy.create_engine(CONN_STR)
Base = declarative_base()
class LVService(Base):
'''Map service for LocalView Intranet.
'''
__tablename__ = 'localview_Services'
ServiceId = Column(types.Integer,
Sequence('service_id', 1, 1),
primary_key=True,
)
ServiceServer = Column(types.String(50)) #nvarchar
ServiceType = Column(types.String(50)) #nvarchar
ServiceDataFrame = Column(types.String(50)) #nvarchar
ServiceUsername = Column(types.String(50)) #nvarchar
ServicePassword = Column(types.String(50)) #nvarchar
ServiceDomain = Column(types.String(50)) #nvarchar
ServiceLayerManager = \
Column(types.String(50)) #nvarchar
IsLive =Column(types.Integer, nullable=False,
default=1)
ServiceDisplayName = \
Column(types.String(50)) #nvarchar
...
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] foreignkey and relation synchronization
Hello, There's a behaviour in SA that is not clear to me: if we look at the example (User and Address) from the docs, we can change either the related object or the foreign key For example: ad = Address(email_address='j...@google.com') ad.user = jack OR ad.user_id = 1 What is the prefered behaviour ? And what happens if the two fields are not in synch ? (ie, what if jack.id=1 and we set explicetly ad.user = 2) Thanks for clarifying . --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting with Windows Auth?
On Feb 25, 4:16 pm, Adam Pletcher adam.pletc...@gmail.com wrote: Is it possible to connect to a server using Windows Authentication using sqlalchemy? Yes it is, the syntax is: 'mssql://hostname/database?trusted_connection=yes' If you're connecting to a SQLEXPRESS instance, this becomes: 'mssql://hostname\\SQLEXPRESS/database?trusted_connection=yes' I'm using pyodbc as the underlying DBAPI provider. Alex --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: export and import JSON from database (JSON type)
On Feb 25, 12:17 pm, Roger Demetrescu roger.demetre...@gmail.com
wrote:
Note that this implementation is very simple. Depending of your use
case, you probably should take a look at MutableType [1] and
types.TypeEngine.is_mutable().
[1] -http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html#sql...
On Wed, Feb 25, 2009 at 10:27 AM, Roger Demetrescu
roger.demetre...@gmail.com wrote:
I did something like that recently:
-
from sqlalchemy import types
import simplejson
class JsonString(types.TypeDecorator):
impl = types.String
def process_result_value(self, value, dialect):
if value is None:
return None
else:
return simplejson.loads(value)
def process_bind_param(self, value, dialect):
if value is None:
return None
else:
return simplejson.dumps(value)
-
This looks like something I should be using, but I'm not sure. I've
been playing around with simplejson and loads but not getting very
far when it comes to getting my json object's properties into my
python SA object's (model's) fields.
I have a simple model/class named Comment with columns id (int),
comment (text), and postdate (datetime).
Below are the main bits of the python code I'm using to test how I
should handle this:
# json from the client's browser
json = r'{id:1,postdate:Sat Jan 31 2009 22:18:15 GMT-0500
(Eastern Standard Time),comment:Comment text.}'
# create comment instance
comment = model.Comment()
# decode json to python object (dict)
json_obj = simplejson.loads(json)
# ???
So now I have json_obj as a python dict object with key/value
pairs... and I need to somehow get this into:
comment.id
comment.comment
comment.postdate
I've searched and searched and have only found one good example piece
of code that I may be able to use to at least handle the parsing/
generation of the datetime type from a javascript Date string; but,
I'm still stuck on how to iterate over the dict keys and do an
assignment to the properties in the comment object instance in a way
that's reusable for other model classes.
Thanks for your time, -e
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Inspecting DDL of a Table object, without executing the code
see the recipe in
http://www.sqlalchemy.org/trac/wiki/FAQ#HowcanIgettheCREATETABLEDROPTABLEoutputasastring
.
On Feb 27, 2009, at 4:32 AM, Alex Willmer wrote:
I suspect the answer to this is obvious, but it eludes me. I have
defined some SQlAlchemy table classes, using declarative_base, to
interact with some tables in an SQL Server database. I'm unsure that
the types I've chosen are correct.
I want to print the CREATE TABLE ddl, without executing it. How can I
do this?
With thanks, Alex
CONN_STR = 'mssql://user:p...@host/database'
engine = sqlalchemy.create_engine(CONN_STR)
Base = declarative_base()
class LVService(Base):
'''Map service for LocalView Intranet.
'''
__tablename__ = 'localview_Services'
ServiceId = Column(types.Integer,
Sequence('service_id', 1, 1),
primary_key=True,
)
ServiceServer = Column(types.String(50)) #nvarchar
ServiceType = Column(types.String(50)) #nvarchar
ServiceDataFrame = Column(types.String(50)) #nvarchar
ServiceUsername = Column(types.String(50)) #nvarchar
ServicePassword = Column(types.String(50)) #nvarchar
ServiceDomain = Column(types.String(50)) #nvarchar
ServiceLayerManager = \
Column(types.String(50)) #nvarchar
IsLive =Column(types.Integer, nullable=False,
default=1)
ServiceDisplayName = \
Column(types.String(50)) #nvarchar
...
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Filtering by an attribute of a related class (abusing generative filters...)
On Feb 27, 2009, at 3:10 PM, Ben Zealley wrote:
Hi all,
I've got a requirement that seems like it should be fairly
straightforward, but my own attempts to work out the necessary code
have just become more and more contrived, and still don't work :( so
I'm hoping someone more familiar with SQLA's design can suggest a
better approach!
Background:
I have a function which creates a query 'q' on a mapped class (Foo),
then applies one or more filter criteria, before finally executing the
query and returning a list of results. In general the individual
criteria might apply to any attribute of the mapped class; I have the
attribute name (at this point anyway) as a string 'attr'. The criteria
may also use various modes; I convert these into an SQL operator
'sqlop' (so for example 'exact' becomes '=', 'exclude' becomes ' NOT
LIKE ', etc.)
So the generative filter() call is basically just:
q = q.filter(attr + sqlop + :val).params(val=test)
you might want to look into a functional approach here, i.e.
sqlop(MyClass.attr, test). More extensible, less typing (no need for
params()), etc.. For example:
from operator import eq
def exact(x, y):
return eq(x,y)
You can also create custom comparison operations on any mapped
attribute using the comparator argument to column_property() or
relation().
http://www.sqlalchemy.org/docs/05/mappers.html#id2
note that you can add any kind of method to a Comparator in the most
recent release of SQLAlchemy (such as your exact() method).
Now the problem:
That all works fine until I want to filter on attributes that are
relational. Say Foo has a one-to-many or many-to-one relation to Bar
('rBar'). When I try to filter Foo on rBar, I want those Foos whose
rBar collection includes at least one Bar whose Title attribute
matches the criterion if it's one-to-many, or those Foos whose rBar
points to a Bar whose Title matches the criterion if it's many-to-
one.
SQLA has operators has() and any() which support this directly. the
joins are created via correlation within an EXISTS clause.
http://www.sqlalchemy.org/docs/05/ormtutorial.html#using-exists
Huge bonus points if your solution also works for a self-referential
many-to-many relation that uses an association table (there are two,
'rFooPrecedes' and 'rFooFollows', which are Foo-Foo links via the
FooPrecedence mapped class; here I'd want to filter based on the
titles of the attached Foos, not on the titles of the FooPrecedence
entries that link them).
has() and any() all handle this automatically.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: foreignkey and relation synchronization
On Feb 27, 2009, at 3:51 PM, laurent wrote: Hello, There's a behaviour in SA that is not clear to me: if we look at the example (User and Address) from the docs, we can change either the related object or the foreign key For example: ad = Address(email_address='j...@google.com') ad.user = jack OR ad.user_id = 1 What is the prefered behaviour ? And what happens if the two fields are not in synch ? (ie, what if jack.id=1 and we set explicetly ad.user = 2) Thanks for clarifying . upon flush, the ad.user association event is translated into a dependency rule that will place the primary key of user onto the user_id attribute of ad. So the object association wins. the primary use case with the ORM is to deal with object connections and collections, without worrying about any primary or foreign key attributes. the FAQ talks about this - you can manipulate foreign key attributes if you want, but the connections they represent wont show up until those connections have been persisted (i.e. via flush, explicit or not) and loaded back (i.e. via lazyload, or after a commit() operation when all attributes are expired). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: export and import JSON from database (JSON type)
On Feb 27, 2009, at 11:24 PM, eLuke wrote:
I have a simple model/class named Comment with columns id (int),
comment (text), and postdate (datetime).
Below are the main bits of the python code I'm using to test how I
should handle this:
# json from the client's browser
json = r'{id:1,postdate:Sat Jan 31 2009 22:18:15 GMT-0500
(Eastern Standard Time),comment:Comment text.}'
# create comment instance
comment = model.Comment()
# decode json to python object (dict)
json_obj = simplejson.loads(json)
# ???
So now I have json_obj as a python dict object with key/value
pairs... and I need to somehow get this into:
comment.id
comment.comment
comment.postdate
I've searched and searched and have only found one good example piece
of code that I may be able to use to at least handle the parsing/
generation of the datetime type from a javascript Date string; but,
I'm still stuck on how to iterate over the dict keys and do an
assignment to the properties in the comment object instance in a way
that's reusable for other model classes.
A method like this would work:
def from_json(self, json):
json_obj = simplejson.loads(json)
for k, v in json_obj.values():
setattr(self, k, v)
if you're concerned about dates you can use a date-based TypeDecorator
which can receive a fully qualified datestring as an argument. Or
use descriptors on your mapped classes (i.e. date = property(def
get_date()/def set_date()))
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Filtering by an attribute of a related class (abusing generative filters...)
Hi Michael, Thanks for the quick response! I had no luck with has()/any() mostly because I didn't have the attribute per se, just its (string) name - but I've rather belatedly realised I can just use modelClass.__dict__[attr].any(), which works like a charm. I'll consider the functional approach you suggested, it does look much cleaner - thanks for the tip. A related question; I'm sure there must be a straightforward way, given an attribute 'attr' (which is a relation) of a mapped class 'Foo', to extract a reference to the mapped class to which the relation points. I currently have the following: tC = orm.class_mapper(Foo).get_property(attr)._get_target().class_ Or, broken down M = orm.class_mapper(Foo)# Mapper for the class p = M.get_property(attr) # The property Mt = p._get_target() # Mapper for the target tC = Mt.class_ # Target class. But I'm fairly sure any sequence of calls that involve methods from someone else's code which begin with an underscore counts as bad form ;) is there a more direct way of getting this? Cheers, -- Ben --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Filtering by an attribute of a related class (abusing generative filters...)
On Feb 28, 2009, at 12:26 PM, Ben Zealley wrote: Hi Michael, Thanks for the quick response! I had no luck with has()/any() mostly because I didn't have the attribute per se, just its (string) name - but I've rather belatedly realised I can just use modelClass.__dict__[attr].any(), which works like a charm. I'll consider the functional approach you suggested, it does look much cleaner - thanks for the tip. A related question; I'm sure there must be a straightforward way, given an attribute 'attr' (which is a relation) of a mapped class 'Foo', to extract a reference to the mapped class to which the relation points. I currently have the following: tC = orm.class_mapper(Foo).get_property(attr)._get_target().class_ id say Foo.someattr.property.mapper.class_ --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
