Re: [sqlalchemy] Re: Join SQL not optimal with inheritance
On Fri Jan 30 2015 at 4:42:42 PM Jonathan Vanasco jonat...@findmeon.com wrote: This should generate your second query: q = s.query(Foo)\ .join( Boo, Foo.id == Boo.id )\ .join( Bar, Boo.id == Bar.id )\ .first() But I already have relationships set up so I'd like to not have to manually write out these joins, but also have the relevant columns loaded into my relationship: q = s.query(Foo).options(joinedload(Foo.boo)) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: confused on avoiding sql injections using ORM
Think about it this way: There's two kinds of strings when you're dealing with SQL: 1) SQL language, 2) your data input. Don't ever include (2) in (1) –– let the API do it. \malthe On 4 July 2011 21:41, Krishnakant Mane krm...@gmail.com wrote: Hello all. I use Pylons 0.9.7 and sqlalchemy. I use the Object Relational Mapper with declarative syntax in a few of my modules. I was reading chapter 7 of the Pylons book and I understood that sql injections can be avoided using the expression api. But can this be also done using ORM? I tryed on my software and sql injections do work. Is it possible to avoide it with ORM or will i have to totally avoide using an ORM layer of sqlalchemy and only use the expression api? Happy hacking. Krishnakant. -- You received this message because you are subscribed to the Google Groups pylons-discuss group. To post to this group, send email to pylons-disc...@googlegroups.com. To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/pylons-discuss?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@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: 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] 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] 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: 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. This is good news indeed. Excellent! \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: Overriding table columns with Python-property
Michael Bayer wrote:
works for me:
I tried adapting your example, which admittedly works :-), to a scenario
that better resembles mine, but now the property is overriden simply,
even when I use ``exclude_properties``.
Note that the setup is overly complex, but this should be seen in the
light of a larger setup (as you've previously guided me towards,
incidentally).
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)),
)
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
\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] Overriding table columns with Python-property
I have a table 'test' that defines a column 'col'. I map this table on:
class Mapper(object):
@property
def col(self):
return uSome read-only value.
passing exclude_properties=('col',).
However, when I save and commit an instance of Mapper, I get:
[snip]
UnmappedColumnError: No column test.col is configured on mapper Mapper...
This is on SQLAlchemy 0.4.6.
\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: Absurd operational error in SQLite
Michael Bayer wrote: oh. how are you getting it to join from soup- (album join vinyl) ? soup has a relation to album join vinyl and you're using query.join() ? it should be creating an aliased subquery for the right side of the join in that case. I thought 0.4 was able to do this; 0.5 definitely can. Attached is the example script from my previous thread, adapted to show the present issue. The setup is basically this: ratable_record = records.join( ratings, onclause=(ratings.c.id==records.c.id)) orm.mapper(RatableRecord, ratable_record, inherits=Soup, inherit_condition=(records.c.id==soup.c.id)) \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 -~--~~~~--~~--~--~--- example.tar.gz Description: GNU Zip compressed data
[sqlalchemy] Absurd operational error in SQLite
When executing a query on some joined SQLA-mapper, SQLite throws the following exception (unlike Postgres, which handles it just fine): OperationalError: (OperationalError) no such column: album.id Here's the query: SELECT album.id AS album_id FROM soup JOIN (album JOIN vinyl ON vinyl.id = album.id) ON vinyl.id = soup.id How would you interpret this? Help much appreciated. \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: Absurd operational error in SQLite
Michael Bayer wrote: sqlite doesn't like the parenthesis. when making the joins with a SQLA join() construct, you need to make the joins from left to right, i.e.: soup.join(album, ...).join(vinyl, ...) as opposed to: soup.join(album.join(vinyl, ...), ...) Actually, we are sort of doing this already --except-- due to your previous advice, we're now using the ``inherits``-option to automatically have SQLA figure out the correct unit-of-work order. With this option, the above join results in this query: SELECT album.id AS album_id FROM soup JOIN (album JOIN vinyl ON vinyl.id = album.id) ON vinyl.id = soup.id --instead of-- SELECT album.id AS album_id FROM soup JOIN album on soup.id = album.id JOIN vinyl ON vinyl.id = soup.id That is, SQLA seems to make a left join (or whatever it is) by itself. How can tell it do this differently? just a little taste of my world ! :) :-) \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: Insertion order not respecting FK relation
Michael Bayer wrote: The most crucial, although not the issue in this specific example, is that the relations table is used both as the secondary table in a relation(), and is also mapped directly to the Relation class. SQLA does not track this fact and even in a working mapping will attempt to insert multiple, redundant rows into the table if you had, for example, appended to the records collection and also created a Relation object. Right; this did seem wrong in the first place. The next issue which is the specific cause of the problem here is that SQLA's topological sort is based off of the relationships between classes and objects, and not directly the foreign key relationships between tables. Specifically, there is no stated relationship between the Record class and the Soup/Collection classes - yet you append a Record object to the records collection which is only meant to store Soup objects. SQLA sees no dependency between the Collection and Record mappers in this case, and the order of table insertion is undefined. This collection append is only possible due to the enable_typechecks=False setting which essentially causes SQLA to operate in a slightly broken mode to allow very specific use cases to work (which are not this one- hence SQLA's behavior is still undefined). enable_typechecks , as the initial error message implied when it mentioned polymorphic mapping, is meant to be used only with inheritance scenarios, and only with objects that are subclasses of the collected object. It suggests that a certain degree of typechecking should remain even if enable_typechecks is set to False (something for me to consider in 0.5). Thank you for clarifying this; at a certain point it was clear to us that SQLA was not equipped to understand what we were doing. I think we somehow expected it to look at the FKs. I've considered someday doing a rewrite of UOW that ultimately bases topological off of ForeignKey and the actual rows to be inserted, and that's it. It's nothing that will happen anytime soon as its a huge job and our current UOW is extremely stable and does a spectacular job for almost two years at this point. But even then, while such an approach might prevent this specific symptom with this specific mapping, it seems like a bad idea in any case to support placing arbitrary, unrelated types into collections that have been defined as storing a certain type. I'm not sure at all if that approach to UOW wouldn't ultmately have all the same constraints as our current approach anyway. Certainly stable is good; strictly looking at FKs only might ultimately make for a simpler implementation though. Fortunately, the solution here is very simple as your table setup is a pure classic joined table inheritance configuration. The attached script (just one script; sorry, all the buildout stuff seemed a little superfluous here) illustrates a straightforward mapping against these tables which only requires that Record and Collection subclass Soup (which is the nature of the joins on those tables). The joins themselves are generated automatically by SQLA so theres no need to spell those out. The enable_typechecks flag is still in use here in its stated use case; that you have a collection which can flush subtypes of Soup, but when queried later, will only return Soup objects. You can improve upon that by using a polymorphic discriminator (see the docs for info on that). Hmm, this solution hadn't occured to me; but it makes a lot of sense. This is great. For what it's worth, we do have a polymorphic rebuilder function in place to bring back to life these soup items. With regards to buildout---it's a habit acquired from the Zope community; it really is a lot less overhead that you might think :-) The script illustrates using the secondary table in the records collection; this is what seems reasonable considering that there is no other meaningful data in the relations table (the surrogate PK in that table is also superfluous). If there are meaningful columns in your actual application's version of the table, then you'd want to do away with secondary and use the association object pattern. We did start out without the secondary table, manually setting up relations, because in fact, we're trying to do an ordered list, which requires a ``position`` column. I'll try to adapt all this into our existing package* and see how it works. Your help is much appreciated. \malthe *) http://pypi.python.org/pypi/z3c.dobbin --~--~-~--~~~---~--~~ 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
[sqlalchemy] Re: Insertion order not respecting FK relation
I can add to this that the issue occurs only on consequent appends. Here's the excerpt that leads to the IntegrityError, demonstrating this. collection = Collection() session.save(collection) session.flush() vinyl = Vinyl() colletion.records.append(vinyl) session.flush() vinyl = Vinyl() colletion.records.append(vinyl) session.flush() The last two flushes are based on the following units-of-work: First append and flush: (Pdb) pp self.tasks.values() [UOWTask(0x27a5f90) Mapper: 'Mapper/Join object on soup(40067600) and __builtin__:ICollection(41259088)', UOWTask(0x27a5ff0) Mapper: 'Mapper/Join object on Join object on soup(40067600) and __builtin__:IAlbum(40262960)(41171024) and __builtin__:IVinyl(41171728)', UOWTask(0x27a5fd0) Mapper: 'Relation/relation'] Second append and flush: (Pdb) pp self.tasks.values() [UOWTask(0x2799fd0) Mapper: 'Mapper/Join object on Join object on soup(40067600) and __builtin__:IAlbum(40262960)(41171024) and __builtin__:IVinyl(41171728)', UOWTask(0x27993b0) Mapper: 'Relation/relation'] For some reason, on the first append, there's a save task defined on the collection-object; I'm not sure what this means, since it should already be saved and flushed at this point. \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: Insertion order not respecting FK relation
Michael Bayer wrote: you'd have to work this into a full self-contained script which I can run locally since it seems theres some specific usage pattern creating the issue. (i.e. its very difficult for me to piece together snippets and guess where the issue might be occuring). This is reasonably self-contained; I've tried to make it as short as possible. src/example/tables.py: All tables and mappers src/example/README.txt: Short demonstration which leads to error You can run the example using: $ python bootstrap.py $ bin/buildout $ bin/test Note that the example requires a Python with a working psycopg2; the testrunner expects a database called test to be available on a running postgres. \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 -~--~~~~--~~--~--~--- example.tar.gz Description: GNU Zip compressed data
[sqlalchemy] Re: Insertion order not respecting FK relation
Michael Bayer wrote: A self-referential relationship, when configured as many-to-one, requires the remote_side argument to indicate this, as described in http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_selfreferential . Otherwise it defaults to one-to-many. That sounds correct, but this was not about a self-referential relationship. The Relations table maps a one-to-many relationship from some object to a number of objects (ordered). Or am I missing something? \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: Insertion order not respecting FK relation
Michael Bayer wrote:
would need to see mappings.
First, let me mention that this issue only occurs on Postgres; I can't
replicate it on SQLite.
This is the many-to-many relation table (posted previously):
table = rdb.Table(
'relation',
metadata,
rdb.Column('id', rdb.Integer, primary_key=True, autoincrement=True),
rdb.Column('left', rdb.String(length=32),
rdb.ForeignKey(soup.uuid), index=True),
rdb.Column('right', rdb.String(length=32),
rdb.ForeignKey(soup.uuid)),
rdb.Column('order', rdb.Integer, nullable=False))
The soup table:
table = rdb.Table(
'soup',
metadata,
rdb.Column('id', rdb.Integer, primary_key=True, autoincrement=True),
rdb.Column('uuid', rdb.String(length=32), unique=True, index=True),
rdb.Column('spec', rdb.String, index=True),
)
The relation property that should behave like an ordered list:
-
orm.relation(
bootstrap.Relation,
primaryjoin=soup_table.c.uuid==relation_table.c.left,
collection_class=RelationList,
enable_typechecks=False)
I reproduce the problem like so:
1) Append some new item to the list, save and commit.
2) Repeat (1); an ``IntegrityError`` is raised:
IntegrityError: (IntegrityError) insert or update on table relation
violates foreign key constraint relation_right_fkey
DETAIL: Key (right)=(tcbb53226374211dd8a730017f2d1db9) is not
present in table soup.
'INSERT INTO relation (id, left, right, order) VALUES
(%(id)s, %(left)s, %(right)s, %(order)s)' {'left':
'tcbb31e28374211dd8a730017f2d1db9', 'right':
'tcbb53226374211dd8a730017f2d1db9', 'order': 1, 'id': 2L}
Any clues are greatly appreciated.
\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
-~--~~~~--~~--~--~---
