[sqlalchemy] Re: Column metadata from mapped class
On Jul 20, 11:28 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 20, 2008, at 7:48 AM, Sean Davis wrote: I have been playing with 0.5 and have a very simple question. If I have a mapped class, User, how can I get at the columns of User? I see lots of constructs like User.c, but User has no 'c' attribute now. I am looking at finding the column types, names (to loop over), etc. theres a large section regarding this in the upgrade notes athttp://www.sqlalchemy.org/trac/wiki/05Migration , though its focused on rationale . The short answer for columns is to use the Table (i.e. sometable.c.somecolumn, for col in sometable.c:, etc). If you want to get the table for a mapped class if you don't have it already, say class_mapper(cls).mapped_table. Thanks, and sorry I didn't look at that doc before asking. That is exactly what I needed. Sean --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Overriding table columns with Python-property
Michael Bayer wrote: well, i can support this in 0.5 trunk. in rev 4965, If a descriptor is present on a class, or if the name is excluded via the include/ exclude lists, the attribute will not be instrumented via the inherited mapper or via the mapped Table. So your example works with just the @property alone. The r4965 changeset has the side-effect that any previously instrumented attribute will be excluded, too (since ``InstrumentedAttribute`` obviously has the __get__-property). But actually, while I think it's good that any descriptor will be found (not only property-derived ones), this changeset does not solve my particular issue (the property I wanted to exclude was always excluded by ``_should_exclude``). I'll try to put together an example that correctly demonstrates the issue I'm having. \malthe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Elixir 0.6.0 released!
I am very pleased to announce that version 0.6.0 of Elixir (http://elixir.ematia.de) is now available. As always, feedback is very welcome, preferably on Elixir mailing list. Please look at: http://elixir.ematia.de/trac/wiki/Migrate05to06 for detailed upgrade notes. Here are the highlights for this release: - Added support for SQLAlchemy 0.5 - Better support for entities spread across several modules: in relationship definitions, you don't have to use the full path to the other entity anymore. - Changed the default session characteristics to be more inline with SQLAlchemy defaults (if you were using the default session, please look at those upgrade notes!). - New methods on the base entity to update entities from or dump entities to a hierarchical (JSON-like) dictionary structure. It also features a bunch of bugfixes, mostly related to non-default schema and autoloaded entities. The full list of changes can be seen at: http://elixir.ematia.de/trac/browser/elixir/tags/0.6.0/CHANGES What is Elixir? - Elixir is a declarative layer on top of the SQLAlchemy library. It is a fairly thin wrapper, which provides the ability to create simple Python classes that map directly to relational database tables (this pattern is often referred to as the Active Record design pattern), providing many of the benefits of traditional databases without losing the convenience of Python objects. Elixir is intended to replace the ActiveMapper SQLAlchemy extension, and the TurboEntity project but does not intend to replace SQLAlchemy's core features, and instead focuses on providing a simpler syntax for defining model objects when you do not need the full expressiveness of SQLAlchemy's manual mapper definitions. Mailing list http://groups.google.com/group/sqlelixir/about -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Overriding table columns with Python-property
Michael Bayer wrote:
well, i can support this in 0.5 trunk. in rev 4965, If a descriptor
is present on a class, or if the name is excluded via the include/
exclude lists, the attribute will not be instrumented via the
inherited mapper or via the mapped Table. So your example works with
just the @property alone.
I've managed to demonstrate the issue in an isolated test (see below).
The only change from the previous is that I've set a default value.
This causes SQLAlchemy to *prefetch* the 'col' column, but this throws
an exception since the column is not mapped.
from sqlalchemy import *
from sqlalchemy.orm import *
e = create_engine('sqlite://')
m = MetaData(e)
t1= Table(
't1', m,
Column('id', Integer, primary_key=True),
Column('col', String(50), default=u),
)
t1.create()
t2= Table(
't2', m,
Column('id', Integer, ForeignKey(t1.id), primary_key=True),
Column('data', String(50)),
)
t2.create()
class T1(object):
pass
class T2(T1):
@property
def col(self):
return uSome read-only value.
polymorphic = (
[T2], t1.join(t2))
mapper(T1, t1)
mapper(
T2, t2,
exclude_properties=('col',),
with_polymorphic=polymorphic,
inherits=T1,
inherit_condition=(t1.c.id==t2.c.id),
)
sess = sessionmaker()()
x = T2()
assert type(T2.col) is property
x.data = some data
sess.save(x)
sess.commit()
sess.clear()
assert sess.query(T2).one().data == some data
assert sess.query(T2).one().col == uSome read-only value.
x = sess.query(T2).one()
x.data = some new data
sess.commit()
assert sess.query(T2).one().data == some new data
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Help - Two relations to same table
Hi,
I apologize in advance if this is a newbie question, but this is
pretty wierd and I couldn't find an answer in the docs.
I have these two tables:
class Node(Base):
__tablename__ = 'nodes'
id = Column(Integer, primary_key=True)
name = Column(String)
class Link(Base):
__tablename__ = 'links'
node_id = Column(Integer, ForeignKey('nodes.id'))
id = Column(Integer, primary_key=True)
type = Column(String)
fro = relation(Node, order_by=Node.id, backref=links_out)
to = relation(Node, order_by=Node.id, backref=links_in)
Just to clarify, I want each link to appear in the links_out of its
from-node, and in the links_in of it's to-node.
This works just fine when I create the classes, but once I commit the
changes into a session, everything gets messed up (maybe the links_in
and links_out aren't seperated as I would expect).
A quick example:
sqlalchemy.__version__
'0.5.0beta2'
cat = Node()
cat.name = cat
animal = grm.Node()
animal.name = animal
link = Link()
link.type = is a
link.fro = cat
link.to = animal
link
#cat is a #animal
session.add(cat)
session.add(animal)
session.add(link)
session.commit()
link
#animal is a #animal
Any ideas?
Thanks, Erez.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Overriding table columns with Python-property
this issue is not specific to the recent changes; this would happen
even with the old behavior (since exclude_properties was meant to
mean, I dont want SQLA to know about this column at all typically in
a reflection scenario). its fixed in r4966.
On Jul 22, 2008, at 7:27 AM, Malthe Borch wrote:
Michael Bayer wrote:
well, i can support this in 0.5 trunk. in rev 4965, If a descriptor
is present on a class, or if the name is excluded via the include/
exclude lists, the attribute will not be instrumented via the
inherited mapper or via the mapped Table. So your example works with
just the @property alone.
I've managed to demonstrate the issue in an isolated test (see below).
The only change from the previous is that I've set a default value.
This causes SQLAlchemy to *prefetch* the 'col' column, but this throws
an exception since the column is not mapped.
from sqlalchemy import *
from sqlalchemy.orm import *
e = create_engine('sqlite://')
m = MetaData(e)
t1= Table(
't1', m,
Column('id', Integer, primary_key=True),
Column('col', String(50), default=u),
)
t1.create()
t2= Table(
't2', m,
Column('id', Integer, ForeignKey(t1.id), primary_key=True),
Column('data', String(50)),
)
t2.create()
class T1(object):
pass
class T2(T1):
@property
def col(self):
return uSome read-only value.
polymorphic = (
[T2], t1.join(t2))
mapper(T1, t1)
mapper(
T2, t2,
exclude_properties=('col',),
with_polymorphic=polymorphic,
inherits=T1,
inherit_condition=(t1.c.id==t2.c.id),
)
sess = sessionmaker()()
x = T2()
assert type(T2.col) is property
x.data = some data
sess.save(x)
sess.commit()
sess.clear()
assert sess.query(T2).one().data == some data
assert sess.query(T2).one().col == uSome read-only value.
x = sess.query(T2).one()
x.data = some new data
sess.commit()
assert sess.query(T2).one().data == some new data
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Logger problem
The SQLAlchemy default logging seems to use StreamHandler to write to sys.stdout. This cause a problem when writing Python CGI scripts I (unfortunately) have to. The problem is that the SA logger writes to sys.stdout before HTTP headers get writen by my homegrown web cgi framework. See snippet from SA log.py below: [[ default_enabled = False def default_logging(name): global default_enabled if logging.getLogger(name).getEffectiveLevel() logging.WARN: default_enabled = True if not default_enabled: default_enabled = True handler = logging.StreamHandler(sys.stdout) handler.setFormatter(logging.Formatter( '%(asctime)s %(levelname)s %(name)s %(message)s')) rootlogger.addHandler(handler) ]] To get my cgi app to work, I can either: 1.) set Echo = False or 2.) I comment out the handler lines in log.py above and from there my own logger directs the SA log to my app's log file. Both of the above ways do the trick. Anyone with any idea how to do this in a more cleaner way? If not, should this perhaps be suggested as change to current SA source? Kind regards, -Alen Ribic --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Logger problem
On Jul 22, 10:22 am, Alen Ribic [EMAIL PROTECTED] wrote: To get my cgi app to work, I can either: 1.) set Echo = False or 2.) I comment out the handler lines in log.py above and from there my own logger directs the SA log to my app's log file. Both of the above ways do the trick. Anyone with any idea how to do this in a more cleaner way? If not, should this perhaps be suggested as change to current SA source? dont use echo at all, configure logging through Python logging. echo corresponds to sqlalchemy.engine/INFO. http://www.sqlalchemy.org/docs/05/dbengine.html#dbengine_logging --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Support for old-style classes in inheritance tree
Currently, classes that inherit from old-style classes are not supported on two accounts: 1) They do not provide the __subclasses__-method 2) It's not possible to make a weak reference to them Below is a patch that effectively ignores them: Index: lib/sqlalchemy/util.py === --- lib/sqlalchemy/util.py (revision 4964) +++ lib/sqlalchemy/util.py (working copy) @@ -401,6 +401,8 @@ while process: c = process.pop() for b in [_ for _ in c.__bases__ if _ not in hier]: +if isinstance(b, types.ClassType): +continue process.append(b) hier.add(b) if c.__module__ == '__builtin__': Would it be reasonable to support legacy code this way? \malthe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Column metadata from mapped class
On Jul 22, 1:33 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 21, 2008, at 9:45 PM, huy do wrote: Because a mapped class can have an arbitary select as it's source i.e columns from any table theoretically, it would still be nice to know exactly which columns were used to map the properties of a given class. The .c on the model class use to give us the metadata (i.e either the select or table aka relation) which was used to populate the class. Can we get an extension to get this feature back (please) ? If you just want the Table, its just class_mapper(class).mapped_table. Theres lots of ways to build your own .c. class attirbute and such, including: MyClass.c = class_mapper(MyClass).columns cool. the .c. really had to be removedits entirely different now if you say query.filter(MyClass.c.foo=='bar') in 0.5 since no adaptation will take place. I don't mind getting rid of MyClass.c.foo == 'bar' with MyClass.foo == 'bar', but is it possible to add MyClass.foo.c to get at the metadata behind the column itself. Thanks huy --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Default logging writes to sys.stdout, before HTTP headers are set in CGI script
The SQLAlchemy default logging seems to use StreamHandler to write to sys.stdout. This cause a problem when writing Python CGI scripts I (unfortunately) have to. The problem is that the SA logger writes to sys.stdout before HTTP headers get writen by my homegrown web cgi framework. See snippet from SA log.py below: [code] default_enabled = False def default_logging(name): global default_enabled if logging.getLogger(name).getEffectiveLevel() logging.WARN: default_enabled = True if not default_enabled: default_enabled = True handler = logging.StreamHandler(sys.stdout) handler.setFormatter(logging.Formatter( '%(asctime)s %(levelname)s %(name)s %(message)s')) rootlogger.addHandler(handler) [/code] To get my cgi app to work, I can either: 1.) set Echo = False or 2.) I comment out the handler lines in log.py above and from there my own logger directs the SA log to my app's log file. Both of the above ways do the trick. Anyone with any idea how to do this in a more cleaner way? If not, should this perhaps be suggested as change to current SA source? Kind regards, -Alen Ribic --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Column metadata from mapped class
On Jul 22, 8:04 am, huy do [EMAIL PROTECTED] wrote: On Jul 22, 1:33 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 21, 2008, at 9:45 PM, huy do wrote: Because a mapped class can have an arbitary select as it's source i.e columns from any table theoretically, it would still be nice to know exactly which columns were used to map the properties of a given class. The .c on the model class use to give us the metadata (i.e either the select or table aka relation) which was used to populate the class. Can we get an extension to get this feature back (please) ? If you just want the Table, its just class_mapper(class).mapped_table. Theres lots of ways to build your own .c. class attirbute and such, including: MyClass.c = class_mapper(MyClass).columns cool. the .c. really had to be removedits entirely different now if you say query.filter(MyClass.c.foo=='bar') in 0.5 since no adaptation will take place. I don't mind getting rid of MyClass.c.foo == 'bar' with MyClass.foo == 'bar', but is it possible to add MyClass.foo.c to get at the metadata behind the column itself. Thanks huy that you have, MyClass.foo.property.columns. its a list since multiple cols can be associated with one attribute. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SavePoint question
Michael, Michael Bayer wrote: ... if FB didn't raise an error when you said begin_nested() then i think SAVEPOINT is working. Any number of SAVEPOINTS are still all contained within the larger transaction, though. If you want u3 to be committed regardless of the transaction, you'd have to use a different Session on its own transaction. Thanks for the quick reply. I initially had this but that caused me problems when I wanted to access data from that other session. Would I use merging for this? I.e. something along these lines? session1 do whatever open a wxPython dialog - uses session2 does whatever on dialog close: newobject = session1.merge(anObjectFromSession2) Werner --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Logger problem
dont use echo at all, configure logging through Python logging. echo corresponds to sqlalchemy.engine/INFO. http://www.sqlalchemy.org/docs/05/dbengine.html#dbengine_logging Thanks for the ref Michael. That will work perfectly. -Al --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Save file to FS along with object database commit.
Afternoon All, Hello Guys, I have an object which I save to the database using SQLAlchemy, the class is defined using declarative and has a whole bunch of properties. This object has one property though which isn't saved to the database, but to the file system. It is basically a binary string of a files contents. I essentially want to write this binary content to the file system when the record is created in the database, and likewise, delete the file from the FS when the record is removed from the database. I understand all the basics of writing a file, it's just a case of how to implement this into the ORMified object. How would you handle this? Presumably it'll involve creating a couple of methods in the class which handle the file save/delete process, but how do I tie these to the save/delete methods of the ORM? And furthermore, are there any smart ways of making this transactional so if either the database write fails or the file write fails then the they are both rolledback so I don't end up with db records without files and files without dbrecords? I'd really appreciate your thoughts on the cleanest way to implement this. Heston I wanted to bump this topic as I'm still a little confused as to how it might implement. I've been doing a little reading this afternoon about mapper extensions, would this be a decent use of that do you think for after_create and after_delete? I really appreciate your input guys, being new to the ORM scene and a lonely programmer with no one to bounce ideas off this has got me baffled. Heston --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Run basic query
The sqlalchemy update statement is documented here:
http://www.sqlalchemy.org/docs/05/sqlexpression.html#sql_update
Basically, you want something like
conn.execute(foo.update(values={foo.c.bar: 0 }))
On Tue, Jul 22, 2008 at 10:49 AM, Heston James - Cold Beans
[EMAIL PROTECTED] wrote:
Guys,
I want to run a query which doesn't return any objects, just simply modifies
all records in the table, like so:
UPDATE foo
SET bar = 0
How can I do this using SQLAlchemy? Is it possible and 'proper' for me to
just pass this query as a string to be executed? Or is there a better
'sqlalchemy' style of doing this? Should I be pulling all the records from
the db, modifying them and then resaving them? Seems like a heavy workload.
Cheers,
Heston
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] cloning tables from object model
greetings, in my app i'm processing rows in a mysql table and i've trying to use SA in ORM-like manner and got stuck at something presumably totally simple. i have a class Orders and mapped the oder_table to Order and now i'm processing each row in order_table. one column in the table is order_quantity and if quantity 1, i need to take create a temp table with num rows = quantity and change the unique id modifier. easy in bare sql; many ways to do it. in trying to better understand SA, i've been trying to generate the temp_order_table from the already existing object model, Orders ... and totally failed. i got as far as order_schema = (order_table.select().execute()).fetchone() and my table structure by row.order_schema() but got stuck in the next step to get SA to create the temp_table. is there a OO best practice of doing this? how? thx, bb --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Help - Two relations to same table
On Jul 22, 2008, at 1:48 PM, Erez wrote:
So how would you solve it?
I tried defining two foriegn keys:
class Link(Base):
__tablename__ = 'links'
node_id = Column(Integer, ForeignKey('nodes.id'))
node_id2 = Column(Integer, ForeignKey('nodes.id'))
id = Column(Integer, primary_key=True)
type = Column(String)
fro = relation(Node, order_by=node_id, backref=links_out)
to = relation(Node, order_by=node_id2, backref=links_in)
but got:
sqlalchemy.exc.ArgumentError: Could not determine join condition
between parent/
child tables on relation Link.fro. Specify a 'primaryjoin'
expression. If this
is a many-to-many relation, 'secondaryjoin' is needed as well.
Is a primaryjoin necessary then?
yup
I've tried to look-up the solution, but couldn't find any.
here
http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relation_customjoin
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Implementing saved searches
On Jul 22, 2:23 pm, Philip Semanchuk [EMAIL PROTECTED] wrote: I'm new to SqlAlchemy. I'm trying to implement saved searches, like a mail program folder that says, Show me all emails received yesterday, or All emails with 'grail' in the subject. One wrinkle is that my application permits user-supplied extensions, so I need to be able to define saved searches that involve arbitrary objects/tables that I didn't code. This is where I think SqlAlchemy can come to my rescue. Extension modules will be required to define their own mappers and I'll be able to use those maps to query the objects without knowing much about them in advance. Assuming I have code that creates a sqlalchemy.orm.query.Query object that describes the search I want to save, I can (almost) create a saved search via str(the_query.statement). I don't know where to find the parameters to that query, though. Can someone point me in the right direction? Obviously once I have the SQL + parameters it's not hard to save them a table somewhere. If anyone has done this type of thing before and has a better solution, I'm be happy to hear it. Storing SQL in the database seems inelegant, but if I'm to support searches on arbitrary objects/tables, I don't see a better solution. the binds for any SQL expression are present if you say statement.compile().params. There's a little bit on this in the tutorial at http://www.sqlalchemy.org/docs/05/sqlexpression.html#sql_insert . The downside of storing SQL in the DB is that you're bound to an exact SQL dialect as well as table structure. A higher level concept of stored filters and such would alleviate that issue but is more complicated to implement. Might be worth thinking about though. User- defined extensions would also have to provide information regarding their filters too. (user-defined extension is a vague term so its not clear at what level these extensions are created, how strict of an API/sandbox they have, etc). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Implementing saved searches
On Jul 22, 2008, at 2:42 PM, Michael Bayer wrote: On Jul 22, 2:23 pm, Philip Semanchuk [EMAIL PROTECTED] wrote: I'm new to SqlAlchemy. I'm trying to implement saved searches, like a mail program folder that says, Show me all emails received yesterday, or All emails with 'grail' in the subject. One wrinkle is that my application permits user-supplied extensions, so I need to be able to define saved searches that involve arbitrary objects/tables that I didn't code. This is where I think SqlAlchemy can come to my rescue. Extension modules will be required to define their own mappers and I'll be able to use those maps to query the objects without knowing much about them in advance. Assuming I have code that creates a sqlalchemy.orm.query.Query object that describes the search I want to save, I can (almost) create a saved search via str(the_query.statement). I don't know where to find the parameters to that query, though. Can someone point me in the right direction? Obviously once I have the SQL + parameters it's not hard to save them a table somewhere. If anyone has done this type of thing before and has a better solution, I'm be happy to hear it. Storing SQL in the database seems inelegant, but if I'm to support searches on arbitrary objects/ tables, I don't see a better solution. the binds for any SQL expression are present if you say statement.compile().params. There's a little bit on this in the tutorial at http://www.sqlalchemy.org/docs/05/sqlexpression.html#sql_insert Got it, thanks. The downside of storing SQL in the DB is that you're bound to an exact SQL dialect as well as table structure. A higher level concept of stored filters and such would alleviate that issue but is more complicated to implement. Might be worth thinking about though. User- defined extensions would also have to provide information regarding their filters too. (user-defined extension is a vague term so its not clear at what level these extensions are created, how strict of an API/sandbox they have, etc). Thanks for your thoughts, and for SqlAlchemy. In our app, SQLite has big advantages over other databases so I don't mind deepening our ties to it. It won't be going away anytime soon. Being bound to a specific table structure is indeed less appealing and that's my main objection to my proposed solution. As you point out, a higher level filter concept would provide a layer of abstraction and insulation against schema changes. To that end, I tried pickling a Query object but it didn't seem to like it (Can't pickle class 'sqlalchemy.orm.properties.ColumnComparator': it's not found as sqlalchemy.orm.properties.ColumnComparator) which is OK. That was a shot in the dark, and I'm not sure it would be any wiser than just storing raw SQL. bye Philip --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Implementing saved searches
On Jul 22, 2008, at 3:55 PM, Philip Semanchuk wrote: In our app, SQLite has big advantages over other databases so I don't mind deepening our ties to it. It won't be going away anytime soon. Being bound to a specific table structure is indeed less appealing and that's my main objection to my proposed solution. As you point out, a higher level filter concept would provide a layer of abstraction and insulation against schema changes. To that end, I tried pickling a Query object but it didn't seem to like it (Can't pickle class 'sqlalchemy.orm.properties.ColumnComparator': it's not found as sqlalchemy.orm.properties.ColumnComparator) which is OK. That was a shot in the dark, and I'm not sure it would be any wiser than just storing raw SQL. you can pickle clause expressions. On Query, its available as query.whereclause. Though if you dont greatly restrict and parse the expressions you're storing then you still have dependencies on table structure. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Query is checking for transaction status
Hi all,
I am trying to do a count on a particular table :
session.query(User).count()
To get the number of User objects in the database.
I am getting the exception below :
return self.session.query(
File c:\python24\lib\site-packages\SQLAlchemy-0.4.6-py2.4.egg
\sqlalchemy\orm\
query.py, line 1075, in count
return q._count()
File c:\python24\lib\site-packages\SQLAlchemy-0.4.6-py2.4.egg
\sqlalchemy\orm\
query.py, line 1084, in _count
return self._col_aggregate(sql.literal_column('1'),
sql.func.count, nested_c
ols=list(self.mapper.primary_key))
File c:\python24\lib\site-packages\SQLAlchemy-0.4.6-py2.4.egg
\sqlalchemy\orm\
query.py, line 1103, in _col_aggregate
return self.session.scalar(s, params=self._params,
mapper=self.mapper)
File c:\python24\lib\site-packages\SQLAlchemy-0.4.6-py2.4.egg
\sqlalchemy\orm\
session.py, line 632, in scalar
return self.__connection(engine,
close_with_result=True).scalar(clause, para
ms or {})
File c:\python24\lib\site-packages\SQLAlchemy-0.4.6-py2.4.egg
\sqlalchemy\orm\
session.py, line 597, in __connection
return self.transaction.get_or_add(engine)
File c:\python24\lib\site-packages\SQLAlchemy-0.4.6-py2.4.egg
\sqlalchemy\orm\
session.py, line 199, in get_or_add
self._assert_is_active()
File c:\python24\lib\site-packages\SQLAlchemy-0.4.6-py2.4.egg
\sqlalchemy\orm\
session.py, line 166, in _assert_is_active
raise exceptions.InvalidRequestError(The transaction is inactive
due to a r
ollback in a subtransaction and should be closed)
sqlalchemy.exceptions.InvalidRequestError: The transaction is inactive
due to a
rollback in a subtransaction and should be closed
I am running this test in a multi threaded environment. Each of them
has a separate session object. Two of the threads (one is the Main
Thread itself) add users to the db, and the other two modify the
objects added to the database.
The Main Thread prints a reporting line after adding 50 objects which
contains the info of how many User objects are there in the DB. The
above query is executed in this context.
The exception I get above is not consistent, that is, sometimes it
comes after the first 50 records, sometimes after 200 and sometimes
after 250.
The session is transactional with autoflush=False. Why is a query
trying to find out the status of session? Is there anything that could
have been done wrong?
Thanks in advance for the help!
Cheers,
Harish
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ODBC connection URI
On 2008-05-27 17:28, Michael Bayer wrote: On May 27, 2008, at 11:19 AM, Rick Morrison wrote: To be honest I thought this was how we were doing it, but (incredulously) looking at the source I see theres a dsn keyword argument in there ?!? ... What's the argument for DSN as the official keeper of the host part as opposed to an actual host ? Would it be because in ODBC, DSN is *supposed* to be the primary identifier for how to get to a host ? That's a fairly strong argument for me. Yes. DSNs refer to logical database connections in the ODBC manager configuration. They don't necessarily map to hosts on the network - indeed, it's well possible that the database runs on the same machine and you connect to it via shared memory or pipes. OTOH, it's also possible to have to connection setups for the same host, e.g. one setup as read-only connection and the other as read-write connection. With the ADO module, there isnt a TCP host option, is my recollection. The source seems to be sending host along to be interpreted as Data Source, so this appears to be consistent with the host=DSN idea (and I believe this is the MS module I actually played with on the occasion that I actually tried out the MS-SQL dialect). We are at the 0.5 stage and I haven't yet put out an alpha, so we'd make this switch right there, if we decide upon it. I don't see any way to make this a graceful switch, except that we provide the server keyword attribute in 0.4 so that people can migrate their URLs to a forwards-compatible keyword-based format. -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Source (#1, Jul 23 2008) Python/Zope Consulting and Support ...http://www.egenix.com/ mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ mxODBC, mxDateTime, mxTextTools ...http://python.egenix.com/ Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg Registered at Amtsgericht Duesseldorf: HRB 46611 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Help - Two relations to same table
On Jul 22, 4:51 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Jul 22, 2008, at 6:50 AM, Erez wrote:
Hi,
I apologize in advance if this is a newbie question, but this is
pretty wierd and I couldn't find an answer in the docs.
I have these two tables:
class Node(Base):
__tablename__ = 'nodes'
id = Column(Integer, primary_key=True)
name = Column(String)
class Link(Base):
__tablename__ = 'links'
node_id = Column(Integer, ForeignKey('nodes.id'))
id = Column(Integer, primary_key=True)
type = Column(String)
fro = relation(Node, order_by=Node.id, backref=links_out)
to = relation(Node, order_by=Node.id, backref=links_in)
Just to clarify, I want each link to appear in the links_out of its
from-node, and in the links_in of it's to-node.
This works just fine when I create the classes, but once I commit the
changes into a session, everything gets messed up (maybe the links_in
and links_out aren't seperated as I would expect).
A quick example:
sqlalchemy.__version__
'0.5.0beta2'
cat = Node()
cat.name = cat
animal = grm.Node()
animal.name = animal
link = Link()
link.type = is a
link.fro = cat
link.to = animal
link
#cat is a #animal
session.add(cat)
session.add(animal)
session.add(link)
session.commit()
link
#animal is a #animal
you have only one foreign key to the nodes table, but two
relations. How can a single row in links maintain two separate
references to both fro and to ?
So how would you solve it?
I tried defining two foriegn keys:
class Link(Base):
__tablename__ = 'links'
node_id = Column(Integer, ForeignKey('nodes.id'))
node_id2 = Column(Integer, ForeignKey('nodes.id'))
id = Column(Integer, primary_key=True)
type = Column(String)
fro = relation(Node, order_by=node_id, backref=links_out)
to = relation(Node, order_by=node_id2, backref=links_in)
but got:
sqlalchemy.exc.ArgumentError: Could not determine join condition
between parent/
child tables on relation Link.fro. Specify a 'primaryjoin'
expression. If this
is a many-to-many relation, 'secondaryjoin' is needed as well.
Is a primaryjoin necessary then?
I've tried to look-up the solution, but couldn't find any.
Thanks!
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Query is checking for transaction status
On Jul 22, 5:33 pm, Harish [EMAIL PROTECTED] wrote: The session is transactional with autoflush=False. Why is a query trying to find out the status of session? Is there anything that could have been done wrong? Thanks in advance for the help! that error is usually when flush() raises an error yet further operations are continued upon the underlying connection. Since the error occurs randomly, this very strongly suggests unsynchronized access by multiple threads to either a single Session or a single Connection underlying it. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Help - Two relations to same table
On Jul 22, 2008, at 6:50 AM, Erez wrote:
Hi,
I apologize in advance if this is a newbie question, but this is
pretty wierd and I couldn't find an answer in the docs.
I have these two tables:
class Node(Base):
__tablename__ = 'nodes'
id = Column(Integer, primary_key=True)
name = Column(String)
class Link(Base):
__tablename__ = 'links'
node_id = Column(Integer, ForeignKey('nodes.id'))
id = Column(Integer, primary_key=True)
type = Column(String)
fro = relation(Node, order_by=Node.id, backref=links_out)
to = relation(Node, order_by=Node.id, backref=links_in)
Just to clarify, I want each link to appear in the links_out of its
from-node, and in the links_in of it's to-node.
This works just fine when I create the classes, but once I commit the
changes into a session, everything gets messed up (maybe the links_in
and links_out aren't seperated as I would expect).
A quick example:
sqlalchemy.__version__
'0.5.0beta2'
cat = Node()
cat.name = cat
animal = grm.Node()
animal.name = animal
link = Link()
link.type = is a
link.fro = cat
link.to = animal
link
#cat is a #animal
session.add(cat)
session.add(animal)
session.add(link)
session.commit()
link
#animal is a #animal
you have only one foreign key to the nodes table, but two
relations. How can a single row in links maintain two separate
references to both fro and to ?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] SavePoint question
I have a similar case to what is shown in the doc, here my slightly different usecase, see the commit for u3 and a final rollback. Session = sessionmaker() sess = Session() sess.add(u1) sess.add(u2) sess.begin_nested() # establish a savepoint sess.add(u3) # in my case this is data I would like to keep regardless if later a rollback is done. sess.commit() # or rollback affecting u3, does not affect u1 or u2 sess.rollback() # I guessed/hoped that this would only affect u1 and u2, however it rolls back also u3 I am on Firebird SQL 2.1. Should this work as I am hoping it is (in which case I will need to track it down further in my program), or is SAVEPOINT not supported by FB? Best regards Werner --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
