[sqlalchemy] object dirtiness from the session's perspective?
Hi All, I'm working on a variation of this recipe: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedRows ...and I've got a couple of questions about changed objects: - do objects end up in session.dirty as a result of attributes being set or changed? For example: class Example(Versioned, Base): __tablename__ = 'example' id = Column(Integer, primary_key=True) data = Column(String) obj = session.query(Example).get(1) print obj.data 'something' obj.data = 'something' Is obj now considered dirty? Hopefully not, hopefully it'll only be considered dirty if the following was done: obj.data = 'something else' Would both of the above result in obj being dirty or just the latter? If both, are there any hooks for affecting this behaviour? - in a SessionExtension's before_flush method, is there any way I can tell which attributes have changed? Or, almost the same, can I check some specific attributes to see if they've changed? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] blocking on Base.metadata.drop_all() in test
zende wrote: Base.metadata.drop_all() completely blocks in the tearDown method of some of my tests. What database backend are you using *in the tests*? Where is the code blocking? (ie: when you hit ctrl-c, you should get a traceback, it would be handy to see that...) cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Comparable properties
Oliver Beattie wrote: @property def is_visible(self): return (self.enabled and not self.is_deleted) This can clearly be mapped quite easily to SQL expression `Klass.enabled == True Klass.is_deleted == False` You could always add a class-level attribute that stored this... @property def is_visible(self): return (self.enabled and not self.is_deleted) visible = enabled==True is_deleted==False You may need to wrap that into a method with the classproperty decorator... But, it'd be nice to have one attribute of the object fulfil both roles, and I don't know how to do that :-S Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Using the declarative base across projects
thatsanicehatyouh...@mac.com wrote: This is a bit tricky to explain. Imagine I have one database, and I create a project (1) to work with that database (connections, table class definitions, etc.). That is standalone (to me). I have another completely separate database (2) on another host where I do the same thing. Using replication I then create a read-only copy of database 1 in database 2, and join some of the tables. Project 2 needs to generate the classes, but use SA's Base class that is dynamically generated. Since it's dynamic, I have to create it at run time... but now I can't pass that to the definition of project 1's classes. It's that communication that I'm struggling with. Difficult to know what you're after so two guesses: - if you have multiple python packages that need to share a declarative Base, either have one master package that defines the Base and import from that or, if you prefer completely normalised dependencies, move the Base definition out its own package (where stuff shared between your packages such as mixins, session setup functions, etc can live) and have both packages import from there. - if you have different python packages on two different projects running on two different machines or in different processes, then just have each create their own Base. The Base is merely a collection of references to the SA mapped classes, much like the MetaData object, so it's fine to have different ones in different processes, even if they're both accessing the same tables in the same databases... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] problems with multi-table mapping on IronPython
Here's the source code of my test - let me know if I'm doing anything wrong
here
from sqlalchemy import create_engine
from sqlalchemy.orm import mapper
from sqlalchemy.sql.expression import join
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import MetaData
import traceback
try:
import clr
import os
import sys
sys.path.append(os.path.join(os.path.abspath(os.path.curdir),'sqlite'))
clr.AddReference('Ironpython.Sqlite') #need this for sqlite to work on
ironpython. refers to a dll in zip file.
except:
#not ipy
pass
#from sqlalchemy.ext.sqlsoup import SqlSoup #sqlsoup also errors
engine = create_engine('sqlite:///moviedemo_simple.db3') #moviedemo file
also in zip file.
Session = sessionmaker(bind=engine)
class Movies(object):
pass
class Directors(object):
pass
class Genres(object):
pass
class MoviesAndDirectors(object):
pass
meta = MetaData()
meta.reflect(bind=engine)
all_tables = meta.tables
movies_table = all_tables['movies']
genres_table = all_tables['genres']
directors_table = all_tables['directors']
md_table = all_tables['movie_directors']
mapper(Movies,movies_table)
mapper(Directors,directors_table)
mapper(Genres,genres_table)
session = Session()
print session.query(Movies).all()[0]
print session.query(Directors).all()[0]
j = join(movies_table,md_table).join(directors_table)
try:
mapper(MoviesAndDirectors,j)#ipy errors here
mad1 = session.query(MoviesAndDirectors).all()[0]
print mad1
except Exception, e:
print 'caught exception',e
last_error = e
traceback.print_exc()
how can i run the sqlalchemy test suite? I see it needs nose, i've
installed that. but i'm not clear what command to run to launch tests.
rgds,
harry
On Tue, Jul 6, 2010 at 6:40 PM, Harry Percival harry.perci...@gmail.comwrote:
Hi Michael,
thanks for replying - the reason I attached a zipfile is because sqlite
isn't supported natively on ironpython, so I've had to include the source
and a dll for it. So, if you did have time to open it up and take a peek,
I'd very much appreciate it.
Alternatively, how can I run the sqla unit tests?
On Tue, Jul 6, 2010 at 3:56 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Jul 6, 2010, at 4:18 AM, Harry Percival wrote:
Hi,
I've got an error which occurs in ironpython but not in cpython. can
anyone replicate? See attached. I'm using IPY 2.6.
*string:1: DeprecationWarning: object.__init__() takes no parameters
for type _keyed_weakref
string:1: DeprecationWarning: object.__init__() takes no parameters for
type KeyedRef
Movies object at 0x0034
Directors object at 0x0038
caught exception 'NoneType' object has no attribute 'set'
Traceback (most recent call last):
File D:\workspace\resolver\test_multitable.py, line 54, in module
mapper(MoviesAndDirectors,j)#ipy errors here
File D:\workspace\resolver\sqlalchemy\orm\__init__.py, line 818, in
mapper
return Mapper(class_, local_table, *args, **params)
File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 210, in
__init__
self._configure_properties()
File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 563, in
_configure_properties
self._configure_property(column_key,
File D:\workspace\resolver\sqlalchemy\orm\mapper.py, line 755, in
_configure_property
prop.instrument_class(self)
File D:\workspace\resolver\sqlalchemy\orm\properties.py, line 87, in
instrument_class
attributes.register_descriptor(
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1424, in
register_descriptor
manager.instrument_attribute(key, descriptor)
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1012, in
instrument_attribute
self.install_descriptor(key, inst)
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 1054, in
install_descriptor
setattr(self.class_, key, inst)
File D:\workspace\resolver\sqlalchemy\orm\attributes.py, line 151, in
__set__
self.impl.set(instance_state(instance), instance_dict(instance),
value, None)
AttributeError: 'NoneType' object has no attribute 'set'
*
does this look like a bug with ironpython? if so, I'll report it to the
developers, but i need a little more help tracking down exactly what's going
wrong...
its likely some slightly different behavior in ironpython regarding
descriptors. If you don't have a lot of SQLA experience, it would be
extremely difficult to get SQLA running with a new Python interpreter.
Getting it to run on Jython took a huge amount of effort and weeks/months of
bughunting, both in SQLA and Jython itself. We currently don't have any
resources to get it to work on IronPython as well.
For bonus points: In the attached database, there's a many-to-many
relationship between 'movies' and 'directors' via a simple joining table.
How come SQLA isn't able to figure this out on its own and let me just
join(movies_table, directors_table)? It seems
RE: [sqlalchemy] Using the declarative base across projects
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of
thatsanicehatyouh...@mac.com
Sent: 07 July 2010 20:33
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Using the declarative base across projects
Hi Lance,
Thanks for your comments.
On Jul 7, 2010, at 12:28 PM, Lance Edgar wrote:
Why not just do this in project2 ?
import project.DatabaseConnection as db
Base = declarative_base(bind=db.engine)
# ... etc.
The DatabaseConnection class contains the particulars of the
connection (i.e. host, username, password) which can be
different. I don't want to create dependencies between the
projects, I just want to reuse the class definitions. I want
to create the DatabaseConnection once and pass it into the
definition of the classes.
Another approach I tried was to make ModelClasses an object
and define the classes in a method there (so I could just
pass the Base class to it), but the class definitions were in
the wrong namespace.
The python way seems to be to create a config class,
but project2.ModelClasses won't know anything about it if
it's defined in the first project. As to the reason why there
are two separate projects, consider the case where one set of
tables is one logical group, and the second is a replicated
copy from another server. I can't merge all of these projects
since they really are independent units, but sometimes I
will link them (as above).
I don't understand why project2 wouldn't know anything
about it if defined in (first) project. All it needs to do
is import the connection info from the project (as in above
example). If the database configuration really transcends
both project and project2 though, then yes it probably could
be wrapped in a config module of some sort in another
project; depending on the scope that may be a bit overkill.
If you can consider either project or project2 to be
slightly more default than the other then the db config could
stay there I'd think.
This is a bit tricky to explain. Imagine I have one database,
and I create a project (1) to work with that database
(connections, table class definitions, etc.). That is
standalone (to me). I have another completely separate
database (2) on another host where I do the same thing. Using
replication I then create a read-only copy of database 1 in
database 2, and join some of the tables. Project 2 needs to
generate the classes, but use SA's Base class that is
dynamically generated. Since it's dynamic, I have to create
it at run time... but now I can't pass that to the definition
of project 1's classes. It's that communication that I'm
struggling with.
Cheers,
Demitri
In general, you don't need a database connection just to define your
tables and mappers. The 'bind' parameter to DeclarativeBase is optional,
and only necessary if you are using autoloading. So one solution to your
problem would be not to use autoloading, and bind to a database at the
Session level rather than the Mapper level. That would be the usual way
to use the same set of classes against multiple databases.
If you really need to use autoloading, you could move all your class
definitions into a function that accepts a database engine as a
parameter. For example:
#
# ModelClasses.py
class Namespace(object):
def __init__(self, **kwargs):
self.__dict__.update(kwargs)
def initdb(connection_string):
engine = create_engine(connection_string)
Base = declarative_base(bind=engine)
class Table1(Base):
__tablename__ = 'table1'
__table_args__ = {'autoload': True}
return Namespace(Base=Base,
Table1=Table1)
# or, you could be lazy:
# return Namespace(**locals())
# MainScript1.py
import ModelClasses
db = ModelClasses.initdb(my_connection_string)
# access db.Table1, db.Base etc.
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
RE: [sqlalchemy] Comparable properties
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Chris Withers Sent: 08 July 2010 09:28 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Comparable properties Oliver Beattie wrote: @property def is_visible(self): return (self.enabled and not self.is_deleted) This can clearly be mapped quite easily to SQL expression `Klass.enabled == True Klass.is_deleted == False` You could always add a class-level attribute that stored this... @property def is_visible(self): return (self.enabled and not self.is_deleted) visible = enabled==True is_deleted==False You may need to wrap that into a method with the classproperty decorator... But, it'd be nice to have one attribute of the object fulfil both roles, and I don't know how to do that :-S Chris I think the 'Derived Attributes' example does what you want: http://www.sqlalchemy.org/docs/examples.html#module-derived_attributes http://www.sqlalchemy.org/trac/browser/examples/derived_attributes/attri butes.py As far as I can tell, it uses some Python descriptor magic to allow your property to work both at the instance and at the class level (so 'self' will either be the instance or the class). Accessing Klass.is_visible returns the SQL expression construct, but instance.is_visible works as normal. You'd be more restricted in what you can write inside your property definition though. For example, you can't use plain Python 'and', or assume that 'self.enabled' evaluates to True or False. I think something like this would work though: @hybrid def is_visible(self): return (self.enabled == True) (self.is_deleted == False) Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Comparable properties
King Simon-NFHD78 wrote: @hybrid def is_visible(self): return (self.enabled == True) (self.is_deleted == False) Yeah, having to write something that works as both plain python and a sql layer construct seems a little brittle. I wonder if a decorator could be knocked up which would let you do: def _python_is_visible(self): return (self.enabled and not self.is_deleted) def _sql_is_visible(self): return (self.enabled == True) (self.is_deleted == False) is_visible = some_magic(_python_is_visible,_sql_is_visible) cheers, Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] c-extensions have to be explicitly requested?!
Hi All, I'm a bit surprised to find that you have to ask for the new C extensions in 0.6 to be explicitly compiled. It also appears that the way of requesting this isn't compatible with build tools like a pip requirements file or buildout. What was the reason for that? If it was purely in case the extensions wouldn't compile on python, then why not take the optimistic-but-safe approach of extensions like zope.interface: http://svn.zope.org/zope.interface/trunk/setup.py?view=auto cheers, Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] docs for History object?
Hi All, Where can I find documentation of the History objects used by the orm layer? In particular, how come each of .added, .unchanged and .deleted is a sequence? Are history objects always around or is there a performance hit for using them? cheers, Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] make_transient vs expunge
Hi All, My flow of questions continues ;-) What's the difference beween make_transient(instance), where I guess make_transient comes from sqlalchemy.org, and session.expunge(instance)? cheers, Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] make_transient vs expunge (correction)
Chris Withers wrote: Hi All, My flow of questions continues ;-) What's the difference beween make_transient(instance), where I guess make_transient comes from sqlalchemy.org, ...er, that's sqlachemy.orm... Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Class defining time significance
'''
For some reason, when I define my class seems to impact sqlalchemy
Below is code where if the class Formation(Library): pass line is
moved down,
it works, but as is, I get the error at the end of this file, raised
when the last line runs
This seems very odd (and bug like) to me, and I'm searching for an
explanation, as well as a way to avoid the problem
other than carefully shuffling the order of my code around (which can
be impossible in a larger project)
I'm very new to sqlalchemy, so its likely I'm missing something
conceptually;
but I don't know where to look for docs about such an issue.
This is the minimized version of my project messily crammed into a
single file.
Any insight would be greatly appreciated.
Thanks,
-Craig
sqlalchemy 0.6.1 Mac osx 10.5.8, python 2.6.5, intel CPU
'''
from sqlalchemy import create_engine
engine = create_engine('sqlite:///test.db', echo=False)
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey
from sqlalchemy.orm import mapper, relation, create_session,
sessionmaker
metadata = MetaData()
tree_table = Table('tree', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', Integer, ForeignKey('tree.id')),
Column('name', String),
Column('type', String(30), nullable=False),
)
class Tree(object):
def __init__(self):self.children=[]
class Library(Tree): pass
lib = Table(Library, metadata,Column('tree_id', Integer,
ForeignKey('tree.id'), primary_key=True))
metadata.create_all(engine)
mapper(Tree, tree_table, polymorphic_on=tree_table.c.type,
polymorphic_identity='Tree',
properties={'children':relation(Tree, cascade=all)})
# Crash position
class Formation(Library): pass
mapper(Library, lib, inherits=Tree, polymorphic_identity='Library')
No Crash position
# class Formation(Library): pass
masterLib=Library()
sessionmaker(bind=engine)().add(masterLib)
masterLib.children.append(Formation())
'''
Traceback (most recent call last):
File main.py, line 38, in module
masterLib.children.append(x())
File /Library/Frameworks/Python.framework/Versions/2.6/lib/
python2.6/site-packages/sqlalchemy/orm/collections.py, line 930, in
append
item = __set(self, item, _sa_initiator)
File /Library/Frameworks/Python.framework/Versions/2.6/lib/
python2.6/site-packages/sqlalchemy/orm/collections.py, line 905, in
__set
item = getattr(executor, 'fire_append_event')(item, _sa_initiator)
File /Library/Frameworks/Python.framework/Versions/2.6/lib/
python2.6/site-packages/sqlalchemy/orm/collections.py, line 596, in
fire_append_event
item, initiator)
File /Library/Frameworks/Python.framework/Versions/2.6/lib/
python2.6/site-packages/sqlalchemy/orm/attributes.py, line 662, in
fire_append_event
value = ext.append(state, value, initiator or self)
File /Library/Frameworks/Python.framework/Versions/2.6/lib/
python2.6/site-packages/sqlalchemy/orm/unitofwork.py, line 40, in
append
sess.add(item)
File /Library/Frameworks/Python.framework/Versions/2.6/lib/
python2.6/site-packages/sqlalchemy/orm/session.py, line 1058, in add
self._save_or_update_state(state)
File /Library/Frameworks/Python.framework/Versions/2.6/lib/
python2.6/site-packages/sqlalchemy/orm/session.py, line 1068, in
_save_or_update_state
self._cascade_save_or_update(state)
File /Library/Frameworks/Python.framework/Versions/2.6/lib/
python2.6/site-packages/sqlalchemy/orm/session.py, line 1072, in
_cascade_save_or_update
'save-update', state, halt_on=self.__contains__):
File /Library/Frameworks/Python.framework/Versions/2.6/lib/
python2.6/site-packages/sqlalchemy/orm/session.py, line 1556, in
_cascade_unknown_state_iterator
for (o, m) in mapper.cascade_iterator(cascade, state, **kwargs):
AttributeError: 'NoneType' object has no attribute 'cascade_iterator'
'''
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] c-extensions have to be explicitly requested?!
On Jul 8, 2010, at 6:30 AM, Chris Withers wrote: Hi All, I'm a bit surprised to find that you have to ask for the new C extensions in 0.6 to be explicitly compiled. It also appears that the way of requesting this isn't compatible with build tools like a pip requirements file or buildout. What was the reason for that? If it was purely in case the extensions wouldn't compile on python, then why not take the optimistic-but-safe approach of extensions like zope.interface: http://svn.zope.org/zope.interface/trunk/setup.py?view=auto we wanted to annoy buildout/setuptools/Glyph as much as possible, and I am happy to say we succeeded. the approach is taken directly from that of Genshi templates, except we reversed the default , so that the C extensions are not built by default. The issue of them not building by default wasn't that they might not build, just that they have not been widely tested, and its C code, and we really didn't want people complaining that SQLAlchemy makes their application core dump. It was overly cautious. Not sure if we'd want to change this default midway through 0.6 or wait til 0.7. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] object dirtiness from the session's perspective?
On 7/8/2010 3:23 AM, Chris Withers wrote: Hi All, I'm working on a variation of this recipe: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedRows ...and I've got a couple of questions about changed objects: - do objects end up in session.dirty as a result of attributes being set or changed? Setting an attribute is enough for the instance to wind up in Session.dirty, according to http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.session.Session.dirty For example: class Example(Versioned, Base): __tablename__ = 'example' id = Column(Integer, primary_key=True) data = Column(String) obj = session.query(Example).get(1) print obj.data 'something' obj.data = 'something' Is obj now considered dirty? Hopefully not, hopefully it'll only be considered dirty if the following was done: So, per the docs, yes it would be dirty. I've tested this a little in the past and I believe my experience corroborated this. obj.data = 'something else' Would both of the above result in obj being dirty or just the latter? If both, are there any hooks for affecting this behaviour? - in a SessionExtension's before_flush method, is there any way I can tell which attributes have changed? Or, almost the same, can I check some specific attributes to see if they've changed? For instances that wind up in Session.dirty, you can check each for truly dirty attributes with Session.is_modified (http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.session.Session.is_modified). That won't tell you which attributes have been modified though, only if the instance can be ignored even though it's in Session.dirty. I'm assuming if you need to know which attributes have changed then you'll have to examine the instance's state a little closer yourself, looking at the instrumented history for each attribute, etc. I've not done this though so I'm afraid that's a guess. Lance -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Class defining time significance
On Jul 7, 2010, at 10:52 PM, Craig Macomber wrote: ''' For some reason, when I define my class seems to impact sqlalchemy Below is code where if the class Formation(Library): pass line is moved down, it works, but as is, I get the error at the end of this file, raised when the last line runs This seems very odd (and bug like) to me, and I'm searching for an explanation, as well as a way to avoid the problem other than carefully shuffling the order of my code around (which can be impossible in a larger project) I'm very new to sqlalchemy, so its likely I'm missing something conceptually; but I don't know where to look for docs about such an issue. This is the minimized version of my project messily crammed into a single file. Any insight would be greatly appreciated. you have to map all classes that you intend to use with the Session. The bug here is that you've managed to sneak past the usual assertions that classes passed in are mapped. Ticket #1846 has been added. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] object dirtiness from the session's perspective?
Lance Edgar wrote: - do objects end up in session.dirty as a result of attributes being set or changed? Setting an attribute is enough for the instance to wind up in Session.dirty, according to http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.session.Session.dirty Indeed. I'm assuming if you need to know which attributes have changed then you'll have to examine the instance's state a little closer yourself, looking at the instrumented history for each attribute, etc. I've not done this though so I'm afraid that's a guess. This turns out to be my friend: http://www.sqlalchemy.org/docs/reference/orm/mapping.html?highlight=instancestate#sqlalchemy.orm.attributes.get_history ...hence the later question about History obejcts ;-) Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] c-extensions have to be explicitly requested?!
Michael Bayer wrote: http://svn.zope.org/zope.interface/trunk/setup.py?view=auto we wanted to annoy buildout/setuptools/Glyph as much as possible, and I am happy to say we succeeded. I hope you're joking :-S The issue of them not building by default wasn't that they might not build, just that they have not been widely tested, and its C code, and we really didn't want people complaining that SQLAlchemy makes their application core dump. But that's what we have a wonderful and shiny set of unit tests for, right? :-) Not sure if we'd want to change this default midway through 0.6 or wait til 0.7. Fair enough, but this is sadly one of those things where you're only likely to find the edge cases when you do make it on by default... Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] c-extensions have to be explicitly requested?!
On Jul 8, 2010, at 10:43 AM, Chris Withers wrote: Michael Bayer wrote: http://svn.zope.org/zope.interface/trunk/setup.py?view=auto we wanted to annoy buildout/setuptools/Glyph as much as possible, and I am happy to say we succeeded. I hope you're joking :-S The issue of them not building by default wasn't that they might not build, just that they have not been widely tested, and its C code, and we really didn't want people complaining that SQLAlchemy makes their application core dump. But that's what we have a wonderful and shiny set of unit tests for, right? :-) unit tests in Python can't really test the wider variety of mishaps that native code can produce, i.e. subtle memory leaks, weird race conditions, etc. Not that our C code is subject to any of that, but again, wanted to be very cautious. We wanted to get 0.6 out without having to worry about the C code having untested problems (and there were a few late in the game fixes too). Not sure if we'd want to change this default midway through 0.6 or wait til 0.7. Fair enough, but this is sadly one of those things where you're only likely to find the edge cases when you do make it on by default... The C exts also don't improve performance that much for most users. It would be nicer if we could get people to write a lot more C extensions for us, then it would really be something worth flipping on. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] c-extensions have to be explicitly requested?!
Michael Bayer wrote: Not sure if we'd want to change this default midway through 0.6 or wait til 0.7. Fair enough, but this is sadly one of those things where you're only likely to find the edge cases when you do make it on by default... The C exts also don't improve performance that much for most users. It would be nicer if we could get people to write a lot more C extensions for us, then it would really be something worth flipping on. Fair enough, I wonder whether they could be wired in as an option such that people who wantd to test could do: easy_install sqlalchemy[c] I use this for installing my packages with their test dependencies, I'm not sure how the optional C extension stuff in the Zope packages works, so I don't know if we could wire the two together... cheers, Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Class defining time significance
Thank you. I was hoping it was something like that as I couldn't see anything else that would make sense. On Jul 8, 6:52 am, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 7, 2010, at 10:52 PM, Craig Macomber wrote: ''' For some reason, when I define my class seems to impact sqlalchemy Below is code where if the class Formation(Library): pass line is moved down, it works, but as is, I get the error at the end of this file, raised when the last line runs This seems very odd (and bug like) to me, and I'm searching for an explanation, as well as a way to avoid the problem other than carefully shuffling the order of my code around (which can be impossible in a larger project) I'm very new to sqlalchemy, so its likely I'm missing something conceptually; but I don't know where to look for docs about such an issue. This is the minimized version of my project messily crammed into a single file. Any insight would be greatly appreciated. you have to map all classes that you intend to use with the Session. The bug here is that you've managed to sneak past the usual assertions that classes passed in are mapped. Ticket #1846 has been added. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: blocking on Base.metadata.drop_all() in test
Excerpts from zende's message of Thu Jul 08 13:27:27 -0300 2010: I reproduced the issue the script below: http://gist.github.com/468199 Sorry for the weak explanation before. This has little to do with being in tests except that's the only code that drops and creates the db for any reason. Ctrl-C does nothing when it blocks. Chris, try running the script in the link, and let me know if you are able to reproduce the issue I'm on a similar situation. For lack of time I couldn't investigate it yet but I have a drop_all when running nosetests in my pylons project and it get stuck while dropping the tables. I have to kill the process, because ^C doesn't help. I will check with pudb this afternoon to see if I can bring more data. I'm using sqlalchemy 0.6.1 Mariano -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: blocking on Base.metadata.drop_all() in test
Mariano, What db? postgres? On Jul 8, 9:41 am, Mariano Mara mariano.m...@gmail.com wrote: I'm on a similar situation. For lack of time I couldn't investigate it yet but I have a drop_all when running nosetests in my pylons project and it get stuck while dropping the tables. I have to kill the process, because ^C doesn't help. I will check with pudb this afternoon to see if I can bring more data. I'm using sqlalchemy 0.6.1 Mariano -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: blocking on Base.metadata.drop_all() in test
Your scoped session still has an active connection, which is blocking the drop. Call session.remove() before the drop, or configure the session with expires_on_commit=False to not issue SELECTs to fetch object state after the final commit(). On Thu, Jul 8, 2010 at 9:27 AM, zende mtam...@gmail.com wrote: I reproduced the issue the script below: http://gist.github.com/468199 Sorry for the weak explanation before. This has little to do with being in tests except that's the only code that drops and creates the db for any reason. Ctrl-C does nothing when it blocks. Chris, try running the script in the link, and let me know if you are able to reproduce the issue -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: blocking on Base.metadata.drop_all() in test
Excerpts from zende's message of Thu Jul 08 14:01:32 -0300 2010: Mariano, What db? postgres? On Jul 8, 9:41 am, Mariano Mara mariano.m...@gmail.com wrote: I'm on a similar situation. For lack of time I couldn't investigate it yet but I have a drop_all when running nosetests in my pylons project and it get stuck while dropping the tables. I have to kill the process, because ^C doesn't help. I will check with pudb this afternoon to see if I can bring more data. I'm using sqlalchemy 0.6.1 Mariano Zende, that's right: psql 8.4.4 Mariano -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: blocking on Base.metadata.drop_all() in test
Jason, in the contrived example I came up with and posted, your solution worked but not in my tests. I tried both expires_on_commit=False and Session.remove() before the drop with no luck. Thanks for the suggestion On Jul 8, 10:03 am, jason kirtland j...@discorporate.us wrote: Your scoped session still has an active connection, which is blocking the drop. Call session.remove() before the drop, or configure the session with expires_on_commit=False to not issue SELECTs to fetch object state after the final commit(). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: WITH ... SELECT queries in postgres
I'd be interested in prototyping a WithClause or something similar if you think it might be useful. I imagine it would have similar semantics to a FromClause but would be prepended to the query. Currently, I'm not too interested in prototyping the RECURSIVE part and only care about Postgres. For me to implement this would it be possible to do this in a non- intrusive manner (outside of modifying core SA code?) I'd guess it would have similar semantics to how select() will automatically include FromClauses that for columns that reference them. Also, it would probably chain the WITHs automatically too. Any thoughts? Thanks, Mike On Jul 6, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote: this is the current status of that: http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f... WITHand OVER seem to be the upcoming things we'll have to work on (PG, MSSQL, DB2 supportWITH). Oracle makesWITHdifficult. Its also getting to be time to do a DB2 dialect. On Jul 6, 2010, at 7:07 PM, Mike Lewis wrote: Does SA support this syntax? http://www.postgresql.org/docs/9.0/static/queries-with.html Thanks, Mike -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] mapping objects to arbitary selects, read and write
Hi All, Say we have the following model: class Price(Base): __tablename__ = 'price' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36, scale=12)) class Instrument(Base): __tablename__ = 'instrument' id = Column(Integer, primary_key=True) ticker = Column(String(50)) class Allocation(Base): __tablename__ = 'data' id = Column(Integer, primary_key=True) trade_id = Column(Integer, index=True) instrument_id = Column(ForeignKey(Instrument.id)) instrument = relationship(Instrument) quantity = Column(Integer) price_id = Column(ForeignKey(Price.id)) price = relationship(Price) I now want to map the following class to the 'data' table: class Trade(object): __slots__ = ( 'id', # Allocation.trade_id 'intrument_id', # Allocation.instrument_id 'quantity', # func.sum(Allocation.quantity) 'average_price',# see next line... # (func.sum(Allocation.price)/func.sum(Allocation.quantity) # Trades are mapped from Allocations by group_by(Allocation.trade_id) ) ...if that sort of makes sense? I'm not quote sure how to map this, although I guess something along the lines of http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects would be in order? However, what that example doesn't show, and what I'm keen to make work, is if someone changes attributes of the mapped Trade, what I'd like to have happen depends on the attribute: id,instrument_id, - all underlying rows are updated quantity,average_price - an error is raised Are there any examples of this? Any recommendations? ;-) cheers, Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: WITH ... SELECT queries in postgres
I'd really be interested in defining a system that covers WITH / RECURSIVE entirely, and makes sense both with PG / SQL server as well as Oracle.The work here is figuring out what is common about those two approaches and what an API that is meaningful for both would look like.Implementation and tests are just the grunt work here. On Jul 8, 2010, at 2:03 PM, Mike Lewis wrote: I'd be interested in prototyping a WithClause or something similar if you think it might be useful. I imagine it would have similar semantics to a FromClause but would be prepended to the query. Currently, I'm not too interested in prototyping the RECURSIVE part and only care about Postgres. For me to implement this would it be possible to do this in a non- intrusive manner (outside of modifying core SA code?) I'd guess it would have similar semantics to how select() will automatically include FromClauses that for columns that reference them. Also, it would probably chain the WITHs automatically too. Any thoughts? Thanks, Mike On Jul 6, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote: this is the current status of that: http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f... WITHand OVER seem to be the upcoming things we'll have to work on (PG, MSSQL, DB2 supportWITH). Oracle makesWITHdifficult.Its also getting to be time to do a DB2 dialect. On Jul 6, 2010, at 7:07 PM, Mike Lewis wrote: Does SA support this syntax? http://www.postgresql.org/docs/9.0/static/queries-with.html Thanks, Mike -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Composite column with a relationship
On Jul 7, 2010, at 3:19 PM, Nikolaj wrote:
Hi there,
I'd like to create a column composite for amounts of money and their
currency. However, the difficulty comes in keeping the currencies in a
separate table and enabling my composite to find this relationship.
Is there some way to set the Currency on my Money type implementation
automatically?
seems like your MoneyComposite is the point at which the currency is known and
the Money is recieved. I think you'd want to instrument up MoneyComposite to
intercept set events.
Apologies for the amount of code here:
from sqlalchemy import create_engine, Column, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, composite,
relationship, joinedload
from sqlalchemy.types import Integer, String, Numeric, TypeDecorator
from decimal import Decimal
import warnings
warnings.simplefilter('ignore')
class MoneyComposite(object):
def __init__(self, amount, currency):
self.amount = amount
self.currency = currency
# Set the currency on the Money type here
def __composite_values__(self):
return (self.amount, self.currency)
class MoneyType(TypeDecorator):
impl = Numeric
def __init__(self, *args, **kwargs):
super(MoneyType, self).__init__(*args, precision=11, scale=2,
**kwargs)
def process_bind_param(self, value, dialect):
if isinstance(value, Money):
value = value.value
return value
def process_result_value(self, value, dialect):
return Money(value)
class Money(object):
def __init__(self, value):
self.value = value
self.currency = Currency(symbol='USD', rate=Decimal('1.5'))
def __str__(self):
return '%s %s' % (self.currency.symbol, self.value *
self.currency.rate)
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Currency(Base):
__tablename__ = 'currencies'
symbol = Column(String, primary_key=True)
rate = Column(Numeric)
class Item(Base):
__tablename__ = 'items'
id = Column(Integer, primary_key=True)
name = Column(String(50))
price = composite(
MoneyComposite,
Column('amount', MoneyType),
Column('currency', String, ForeignKey(Currency.symbol))
)
currency = relationship(Currency)
Base.metadata.create_all()
c = Currency(symbol='USD', rate=Decimal('1.5'))
Session.add(c)
price = MoneyComposite(Money('5'), c.symbol)
i = Item(name='foobar', price=price)
Session.add(i)
Session.commit()
i = Session.query(Item).options(joinedload(Item.currency,
innerjoin=True)).first()
print i.price.amount
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] mapping objects to arbitary selects, read and write
On Jul 8, 2010, at 2:42 PM, Chris Withers wrote: Hi All, Say we have the following model: class Price(Base): __tablename__ = 'price' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36, scale=12)) class Instrument(Base): __tablename__ = 'instrument' id = Column(Integer, primary_key=True) ticker = Column(String(50)) class Allocation(Base): __tablename__ = 'data' id = Column(Integer, primary_key=True) trade_id = Column(Integer, index=True) instrument_id = Column(ForeignKey(Instrument.id)) instrument = relationship(Instrument) quantity = Column(Integer) price_id = Column(ForeignKey(Price.id)) price = relationship(Price) I now want to map the following class to the 'data' table: class Trade(object): __slots__ = ( 'id', # Allocation.trade_id 'intrument_id', # Allocation.instrument_id 'quantity', # func.sum(Allocation.quantity) 'average_price',# see next line... # (func.sum(Allocation.price)/func.sum(Allocation.quantity) # Trades are mapped from Allocations by group_by(Allocation.trade_id) ) ...if that sort of makes sense? I'm not quote sure how to map this, although I guess something along the lines of http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects would be in order? maybe. I'd probably use a view, actually, but mapping to a select is the same idea. However, what that example doesn't show, and what I'm keen to make work, is if someone changes attributes of the mapped Trade, what I'd like to have happen depends on the attribute: id,instrument_id, - all underlying rows are updated quantity,average_price - an error is raised Are there any examples of this? Any recommendations? ;-) right. that pattern seems a little crazy to me though I haven't been presented with your problemspace to fully appreciate it. You'd have to conjure up the magic from scratch on this one, intercepting set events and/or checking historical stuff inside of before_flush() like we've done before. You'd probably be calling expire() on any Trade objects encountered in the dirty list. this model doesn't give you the best query capability. if you wanted all trades with quantity 100, its the awkward select * from (select .. from data group by trade_id) where quantity 100, instead of being able to just put a having inside your subquery. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: WITH ... SELECT queries in postgres
That's a fair point. I think one interface might be casting a FromClause into a WithClause similar to how one would alias something. With postgres it seams like when going from a WITH to WITH recursive is adding a UNION ALL and the recursive term. Throwing out an idea for an interface (for postgres at least): Say you want to make WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) First: included_parts = with_([parts.c.subpart, parts.c.part, parts.c.quantity], whereclause=parts.c.part=='our_product') # could also cast a SelectClause (or maybe even selectable) to a with_ by using selectable.with_() included_parts would compile to WITH include_parts AS (SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product') then we do: included_parts = base.recursive( [included_parts.c.sub_part, included_parts.c.part, included_parts.c.quantity], whereclause=included_parts.c.part==parts.c.part, all=True) Which would compile to the desired with clause. then a select(included_parts.c.part) would give you: WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) SELECT included_parts.part, Where can I find information on the Oracle syntax and other ones you'd like to support? On Jul 8, 11:46 am, Michael Bayer mike...@zzzcomputing.com wrote: I'd really be interested in defining a system that covers WITH / RECURSIVE entirely, and makes sense both with PG / SQL server as well as Oracle. The work here is figuring out what is common about those two approaches and what an API that is meaningful for both would look like. Implementation and tests are just the grunt work here. On Jul 8, 2010, at 2:03 PM, Mike Lewis wrote: I'd be interested in prototyping a WithClause or something similar if you think it might be useful. I imagine it would have similar semantics to a FromClause but would be prepended to the query. Currently, I'm not too interested in prototyping the RECURSIVE part and only care about Postgres. For me to implement this would it be possible to do this in a non- intrusive manner (outside of modifying core SA code?) I'd guess it would have similar semantics to how select() will automatically include FromClauses that for columns that reference them. Also, it would probably chain the WITHs automatically too. Any thoughts? Thanks, Mike On Jul 6, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote: this is the current status of that: http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f... WITHand OVER seem to be the upcoming things we'll have to work on (PG, MSSQL, DB2 supportWITH). Oracle makesWITHdifficult. Its also getting to be time to do a DB2 dialect. On Jul 6, 2010, at 7:07 PM, Mike Lewis wrote: Does SA support this syntax? http://www.postgresql.org/docs/9.0/static/queries-with.html Thanks, Mike -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: WITH ... SELECT queries in postgres
Sorry, google groups formatted that *really* poorly. On Jul 8, 12:28 pm, Mike Lewis mikelikes...@gmail.com wrote: That's a fair point. I think one interface might be casting a FromClause into a WithClause similar to how one would alias something. With postgres it seams like when going from a WITH to WITH recursive is adding a UNION ALL and the recursive term. Throwing out an idea for an interface (for postgres at least): Say you want to make WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) First: included_parts = with_([parts.c.subpart, parts.c.part, parts.c.quantity], whereclause=parts.c.part=='our_product') # could also cast a SelectClause (or maybe even selectable) to a with_ by using selectable.with_() included_parts would compile to WITH include_parts AS (SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product') then we do: included_parts = base.recursive( [included_parts.c.sub_part, included_parts.c.part, included_parts.c.quantity], whereclause=included_parts.c.part==parts.c.part, all=True) Which would compile to the desired with clause. then a select(included_parts.c.part) would give you: WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) SELECT included_parts.part, Where can I find information on the Oracle syntax and other ones you'd like to support? On Jul 8, 11:46 am, Michael Bayer mike...@zzzcomputing.com wrote: I'd really be interested in defining a system that covers WITH / RECURSIVE entirely, and makes sense both with PG / SQL server as well as Oracle. The work here is figuring out what is common about those two approaches and what an API that is meaningful for both would look like. Implementation and tests are just the grunt work here. On Jul 8, 2010, at 2:03 PM, Mike Lewis wrote: I'd be interested in prototyping a WithClause or something similar if you think it might be useful. I imagine it would have similar semantics to a FromClause but would be prepended to the query. Currently, I'm not too interested in prototyping the RECURSIVE part and only care about Postgres. For me to implement this would it be possible to do this in a non- intrusive manner (outside of modifying core SA code?) I'd guess it would have similar semantics to how select() will automatically include FromClauses that for columns that reference them. Also, it would probably chain the WITHs automatically too. Any thoughts? Thanks, Mike On Jul 6, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote: this is the current status of that: http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f... WITHand OVER seem to be the upcoming things we'll have to work on (PG, MSSQL, DB2 supportWITH). Oracle makesWITHdifficult. Its also getting to be time to do a DB2 dialect. On Jul 6, 2010, at 7:07 PM, Mike Lewis wrote: Does SA support this syntax? http://www.postgresql.org/docs/9.0/static/queries-with.html Thanks, Mike -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: WITH ... SELECT queries in postgres
On Jul 8, 2010, at 3:28 PM, Mike Lewis wrote: That's a fair point. I think one interface might be casting a FromClause into a WithClause similar to how one would alias something. With postgres it seams like when going from a WITH to WITH recursive is adding a UNION ALL and the recursive term. Throwing out an idea for an interface (for postgres at least): Say you want to make WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) First: included_parts = with_([parts.c.subpart, parts.c.part, parts.c.quantity], whereclause=parts.c.part=='our_product') # could also cast a SelectClause (or maybe even selectable) to a with_ by using selectable.with_() included_parts would compile to WITH include_parts AS (SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product') then we do: included_parts = base.recursive( [included_parts.c.sub_part, included_parts.c.part, included_parts.c.quantity], whereclause=included_parts.c.part==parts.c.part, all=True) Which would compile to the desired with clause. then a select(included_parts.c.part) would give you: WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) SELECT included_parts.part, Where can I find information on the Oracle syntax and other ones you'd like to support? Oracle seems to be beginning to have WITH support: http://www.dba-oracle.com/t_with_clause.htm but their recursive functionality is CONNECT BY: http://www.dba-oracle.com/t_sql_patterns_recursive.htm I used connect by just a little bit years ago, and thats as much as I've gotten my head around recursive queries. I havent yet taken the time to work up a modernized mental picture of them. On Jul 8, 11:46 am, Michael Bayer mike...@zzzcomputing.com wrote: I'd really be interested in defining a system that covers WITH / RECURSIVE entirely, and makes sense both with PG / SQL server as well as Oracle. The work here is figuring out what is common about those two approaches and what an API that is meaningful for both would look like.Implementation and tests are just the grunt work here. On Jul 8, 2010, at 2:03 PM, Mike Lewis wrote: I'd be interested in prototyping a WithClause or something similar if you think it might be useful. I imagine it would have similar semantics to a FromClause but would be prepended to the query. Currently, I'm not too interested in prototyping the RECURSIVE part and only care about Postgres. For me to implement this would it be possible to do this in a non- intrusive manner (outside of modifying core SA code?) I'd guess it would have similar semantics to how select() will automatically include FromClauses that for columns that reference them. Also, it would probably chain the WITHs automatically too. Any thoughts? Thanks, Mike On Jul 6, 4:31 pm, Michael Bayer mike...@zzzcomputing.com wrote: this is the current status of that: http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f... WITHand OVER seem to be the upcoming things we'll have to work on (PG, MSSQL, DB2 supportWITH). Oracle makesWITHdifficult.Its also getting to be time to do a DB2 dialect. On Jul 6, 2010, at 7:07 PM, Mike Lewis wrote: Does SA support this syntax? http://www.postgresql.org/docs/9.0/static/queries-with.html Thanks, Mike -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to
[sqlalchemy] Re: blocking on Base.metadata.drop_all() in test
nope. Jason was right :D. I needed to add Session.remove() to my threads as well; though, I'm not sure why adding expires_on_commit=False alone didn't solve it. Oh well, works now Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] mapping objects to arbitary selects, read and write
Michael Bayer wrote: class Allocation(Base): __tablename__ = 'data' id = Column(Integer, primary_key=True) trade_id = Column(Integer, index=True) instrument_id = Column(ForeignKey(Instrument.id)) instrument = relationship(Instrument) quantity = Column(Integer) price_id = Column(ForeignKey(Price.id)) price = relationship(Price) I'm not quote sure how to map this, although I guess something along the lines of http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects would be in order? maybe. I'd probably use a view, actually, but mapping to a select is the same idea. Good point. Do SQLite and/or MySQL do views? However, what that example doesn't show, and what I'm keen to make work, is if someone changes attributes of the mapped Trade, what I'd like to have happen depends on the attribute: id,instrument_id, - all underlying rows are updated quantity,average_price - an error is raised Are there any examples of this? Any recommendations? ;-) right. that pattern seems a little crazy to me It just might be, in which case only the base rows in the data table will be writeable... this model doesn't give you the best query capability. Actually the opposite, the above is a little simplified. The data table also has columns for things like fill_id, account_id, fund_id, strategry_id. These would all be similar to trade_id. This is deliberately de-normalized such that it becomes trivial to answer queries such as: what position does fund x hold in instrument y what position does account a hold in instrument y what is he average price of the trade made up by fills with fill_id x These all just become group-by and sum as opposed to heinous joins ;-) It also means that slicing and dicing by another (as yet unknown) criteria becomes a case of altering the table to add a new column defaulting to None, rather than re-architecting the whole model. So, definitely interested in modeling things this way, even if only o prove why it's a bad idea ;-) Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] negative implications of using multiple declarative Base classes
I have been, naively it seems, using multiple declarative Base classes in my webapp. They all share the same metadata object. I have found one negative ramification of this, which is that string references (like what can be used in relation()) won't find the object if they are not using the same Base. Are there others? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: negative implications of using multiple declarative Base classes
I'm glad you brought this up. It seems to me that the the declarative instrumentation keys classes by their unqualified class name, precluding using the same class name for different declarative subclasses (ie, in different modules). On Jul 9, 12:01 pm, Randy Syring ra...@rcs-comp.com wrote: I have been, naively it seems, using multiple declarative Base classes in my webapp. They all share the same metadata object. I have found one negative ramification of this, which is that string references (like what can be used in relation()) won't find the object if they are not using the same Base. Are there others? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
