[sqlalchemy] Re: SQLAlchemy 0.3.7 released
On 4/29/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > > 0.3.7 is out and has a huge number of improvements and fixes. some > of the highlights: > > - server side cursor support for Postgres > - much improved auto-reconnect support > - informix support > - long identifier name support > - support for unicode table/column/identifier names and SQL statements > - deterministic label name generation > - new query features, like with_parent() > - improvements to custom collection mapping > - lots more docstrings > - major refactoring of sqlalchemy.engine internals, featuring clearer > structural relationships, fewer codepaths, better result handling Impressive, as usual! --~--~-~--~~~---~--~~ 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] SQLAlchemy 0.3.7 released
0.3.7 is out and has a huge number of improvements and fixes. some of the highlights: - server side cursor support for Postgres - much improved auto-reconnect support - informix support - long identifier name support - support for unicode table/column/identifier names and SQL statements - deterministic label name generation - new query features, like with_parent() - improvements to custom collection mapping - lots more docstrings - major refactoring of sqlalchemy.engine internals, featuring clearer structural relationships, fewer codepaths, better result handling Get the latest version of SQLAlchemy via setuptools or at: http://www.sqlalchemy.org/download.html changelog: - engines - warnings module used for issuing warnings (instead of logging) - cleanup of DBAPI import strategies across all engines [ticket:480] - refactoring of engine internals which reduces complexity, number of codepaths; places more state inside of ExecutionContext to allow more dialect control of cursor handling, result sets. ResultProxy totally refactored and also has two versions of "buffered" result sets used for different purposes. - server side cursor support fully functional in postgres [ticket:514]. - improved framework for auto-invalidation of connections that have lost their underlying database, via dialect-specific detection of exceptions corresponding to that database's disconnect related error messages. Additionally, when a "connection no longer open" condition is detected, the entire connection pool is discarded and replaced with a new instance. #516 - the dialects within sqlalchemy.databases become a setuptools entry points. loading the built-in database dialects works the same as always, but if none found will fall back to trying pkg_resources to load an external module [ticket:521] - Engine contains a "url" attribute referencing the url.URL object used by create_engine(). - sql: - keys() of result set columns are not lowercased, come back exactly as they're expressed in cursor.description. note this causes colnames to be all caps in oracle. - preliminary support for unicode table names, column names and SQL statements added, for databases which can support them. Works with sqlite and postgres so far. Mysql *mostly* works except the has_table() function does not work. Reflection works too. - the Unicode type is now a direct subclass of String, which now contains all the "convert_unicode" logic. This helps the variety of unicode situations that occur in db's such as MS-SQL to be better handled and allows subclassing of the Unicode datatype. [ticket:522] - ClauseElements can be used in in_() clauses now, such as bind parameters, etc. #476 - reverse operators implemented for `CompareMixin` elements, allows expressions like "5 + somecolumn" etc. #474 - the "where" criterion of an update() and delete() now correlates embedded select() statements against the table being updated or deleted. this works the same as nested select() statement correlation, and can be disabled via the correlate=False flag on the embedded select(). - column labels are now generated in the compilation phase, which means their lengths are dialect-dependent. So on oracle a label that gets truncated to 30 chars will go out to 63 characters on postgres. Also, the true labelname is always attached as the accessor on the parent Selectable so theres no need to be aware of the "truncated" label names [ticket:512]. - column label and bind param "truncation" also generate deterministic names now, based on their ordering within the full statement being compiled. this means the same statement will produce the same string across application restarts and allowing DB query plan caching to work better. - the "mini" column labels generated when using subqueries, which are to work around glitchy SQLite behavior that doesnt understand "foo.id" as equivalent to "id", are now only generated in the case that those named columns are selected from (part of [ticket:513]) - the label() method on ColumnElement will properly propigate the TypeEngine of the base element out to the label, including a label() created from a scalar=True select() statement. - MS-SQL better detects when a query is a subquery and knows not to generate ORDER BY phrases for those [ticket:513] - fix for fetchmany() "size" argument being positional in most dbapis [ticket:505] - sending None as an argument to func. will produce an argument of NULL - query strings in unicode URLs get keys encoded to ascii for **kwargs compat - slight tweak to raw execute() change to also support tuples for positional parameters, not just lists [ticket:523] - fix to case() construct to propigate the type of the first WHEN condition as the return type of the case statement - orm: - fixed critical issue when, after options(eagerload()) is used, the mapp
[sqlalchemy] Re: Bug with order_by and certain versions of mysql
On Apr 29, 2007, at 8:11 PM, Ram Yalamanchili wrote:
> objects i believe. So, still trying to see how to fit what you said
> into my query like:
>
>
> q = Query(cls).join('parent').group_by(cls.c.user_id).order_by
> (func.count(cls.c.user_id)).limit(limit).offset(offset)
>
thatll work, have you tried ?
--~--~-~--~~~---~--~~
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: Bug with order_by and certain versions of mysql
Hi,
But, how do i insert the func.count(x).label('whatever') into a
Query(User) ?
User is an assign_mapper based obj. I dont think i can use
Select([func.count(x)]).query() since that wont return a list User
objects i believe. So, still trying to see how to fit what you said
into my query like:
q =
Query(cls).join('parent').group_by(cls.c.user_id).order_by(func.count(cls.c.user_id)).limit(limit).offset(offset)
thanks
On 4/29/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
> On Apr 29, 2007, at 1:07 AM, Ram Yalamanchili wrote:
>
> >
> > 1) select a func.count() column
> > 2) Name an alias to it
> > 3) use this alias with a .order_by
> >
>
>
> func.count(x).label('whatever')
>
>
> then
>
> order_by('whatever')
>
> (or order_by=['whatever'], depending on context)
>
> >
>
--~--~-~--~~~---~--~~
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: distinct doesn't work with join
On Apr 29, 2007, at 6:33 PM, ml wrote: > > Thanks! > > How stable is this revision? Is it suited for production? probably. i keep wanting to release it all week but get bogged down with more tweaks. --~--~-~--~~~---~--~~ 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: Selectable/subquery for a (scalar) column?
turns out scalar columns already worksince they are
just...columns ! which ColumnProperty already handles. any old SA
version will allow the test script to work, if you use ColumnProperty
explicitly along with two small fixes in sql.py. in the trunk ive
added those fixes, as well as reinstated the function "column_property
()" as a synonym for ColumnProperty and adapted your "eager" handling
logic (also aliases the labels) so the columns work out in an eager
relation, so mappings:
mapper(Tag, tags_table, properties={
'query_score': column_property((tags_table.c.score1 *
tags_table.c.score2).label('tag_score'))
})
user_score = select([func.sum(tags_table.c.score1 *
tags_table.c.score2)],
tags_table.c.user_id == users_table.c.id,
scalar=True).label('user_score')
mapper(User, users_table, properties={
'tags': relation(Tag, backref='user'),
'query_score': column_property(user_score),
})
though it still wont work with every kind of eager load, such as an
eager load from Tag to Userpostgres will see the Tag table
referenced in the subquery and demand the GROUP BY expression as ive
mentioned. i dont really see any way around that scenario.
im usually up for adding features to SA if they dont require actually
adding any featuresthis one just needed two bug fixes and a
little bit of extra grease in the eager load setup.
On Apr 25, 2007, at 3:52 PM, Gaetan de Menten wrote:
> It's better with the attachments (the actual patch and a small
> demonstration/test file)...
>
> On 4/25/07, Gaetan de Menten <[EMAIL PROTECTED]> wrote:
>> Ok, I'm quite a bit stubborn at times, so I implemented this the
>> way I
>> thought because I think it makes much more sense this way.
>>
>> Attached is an experimental (as usual) patch to add a
>> StatementProperty, so that you can define stuff like:
>>
>> mapper(Tag, tags_table, properties={
>> 'query_score': StatementProperty((tags_table.c.score1 *
>> tags_table.c.score2).label('tag_score'), Float()),
>> })
>>
>> or even:
>>
>> user_score = select([func.sum(tags_table.c.score1 *
>> tags_table.c.score2)],
>> tags_table.c.user_id == users_table.c.id,
>> scalar=True).label('user_score')
>>
>> mapper(User, users_table, properties={
>> 'tags': relation(Tag, backref='user', lazy=False),
>> 'query_score': StatementProperty(user_score, Float()),
>> })
>>
>> I don't see what's wrong with this approach so far. As always, I
>> might
>> not see the big picture... I just hope this will be useful in some
>> way, even if it's not what you envisioned.
>>
>> Some random remarks:
>> - the statement you give must have a label (that seem pretty logical
>> this way though)
>> - you need to manually provide the type of the property you create
>> (seem logical too). In a final patch, we'd probably want to also
>> accept types in their class form (Float and Float()).
>> - it works both for lazy and eagerloads (I struggled quite a bit to
>> get those to work)
>> - subselects pulled from a lazyload don't work though. But I think it
>> should be quite easily fixable.
>> - As always, I'm not attached to the names I've given.
>>
>> On 4/24/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>>>
>>>
>>> On Apr 24, 2007, at 4:44 AM, Gaetan de Menten wrote:
>>>
> the next way is to do it almost the same as Jonathan's blog
> says to
> do it, except youd map the relation to some intermediary class
> like
> "Score", and then use AssociationProxy to apply the "scalar"
> property
> to the class.
I thought about something like this but it felt sooo hacky I
disregarded it quickly.
>>>
>>> this is probably the least hacky as like i said im probably going to
>>> implement a feature that works just like this.
>>>
>>
>>
>>
>> --
>> Gaëtan de Menten
>> http://openhex.org
>>
>
>
> --
> Gaëtan de Menten
> http://openhex.org
>
> >
> from sqlalchemy import *
> from sqlalchemy.orm.properties import StatementProperty
>
> metadata = MetaData()
>
> users_table = Table('users', metadata,
> Column('id', Integer, primary_key=True),
> Column('name', String(16)),
> )
>
> tags_table = Table('tags', metadata,
> Column('id', Integer, primary_key=True),
> Column('user_id', Integer, ForeignKey("users.id")),
> Column('score1', Float),
> Column('score2', Float),
> )
>
> metadata.connect('sqlite:///')
> metadata.create_all()
>
> class User(object):
> def __init__(self, name):
> self.name = name
>
> @property
> def prop_score(self):
> return sum(tag.prop_score for tag in self.tags)
>
> class Tag(object):
> def __init__(self, score1, score2):
> self.score1 = score1
> self.score2 = score2
>
> @property
> def prop_score(self):
> return self.score1 * self.score2
>
> # this doesn't work
> tag_score
[sqlalchemy] Re: distinct doesn't work with join
Thanks! How stable is this revision? Is it suited for production? Michael Bayer napsal(a): > > OK r2579 > --~--~-~--~~~---~--~~ 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: distinct doesn't work with join
On Apr 29, 2007, at 11:22 AM, ml wrote: > Stripped version attached. OK r2579 --~--~-~--~~~---~--~~ 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: Proposal: Make pyodbc the preferred DB-API for MSSQL
Done in rev #2577 On 4/16/07, Rick Morrison <[EMAIL PROTECTED]> wrote: > > Unless there's any objections, I think it's time to make pyodbc the > preferred access method for MSSQL. > > Currently the MSSQL module checks for DBAPI interfaces in this order if > one isn't specified explicitly: >adodbapi >pymssql >pyodbc > > I'd like to change it to the exact opposite: >pyodbc >pymssql >adodbapi > > Thanks once again to Paul Johnston for all his work getting pyodbc > integrated, and getting a lot of MSSQL unit tests to pass. > > Rick > --~--~-~--~~~---~--~~ 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: Map one property as a function of two others? (one via a secondary table)
On Apr 29, 2007, at 1:10 PM, Eric Ongerth wrote: > > Aha, I should have realized it was just the default cascade=save- > update. > > Thanks for the response; I went and read some more as recommended. > > So one strategy would be to just define physical_size as a method > inside of my Widget class, which goes and loads the physical_size of a > Widget instance from a widget_size_table. Was I correct in thinking > the strategy I mentioned already (mapping the property to a join) > should work? I would rather have the property mapped in, not > expressed as a class method, just so the syntax doesn't look different > for this property than for the other Widget properties. the usage syntax should be the same whether you use a relation() or a class-level property. the "does this make my mapper code look pretty" part of it i dont think should be a huge driving factoryou can always define your class-level properties separately from the class itself and place them along with your mapper defs, i.e. mapper(SomeClass, etc). SomeClass.accessor1 = property(lambda self: ) etc. this "how do i make relationships to wacky things using the relation () function" issue has been hot lately so maybe in a few weeks/months ill come up with some enhancements. > >> a relation like this cant be persisted > > Does that mean that I could not, for example, assign a physical_size > to a bunch of Widgets and expect the system to figure out what updates > should therefore be made in the widget_size_table? If so, that's OK > since that's the exact opposite of the information flow I'm looking > for. I suppose setting viewonly=True can be seen as a way of > informing SQLAlchemy of exactly that fact so that it doesn't have to > try and figure out the reverse. the primary design of a relation() is intended to support a single pattern: that of the primary table containing a direct foreign key relationship to the related table, with optionally a single association table used in between those two tables. you can also have extra join criterion between the primary and related table for the purpose of limiting the results of a select across those two tables, provided that those criterion dont provide any information about the relationship itself that the foreign key relationship doesnt already define. anything more complex than that, specifically any relationship that doesnt include the basic X->Y foreign key relationship, is not going to work on the INSERT/UPDATE/ DELETE side of things. --~--~-~--~~~---~--~~ 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: Modifying objects after session.save() but before session.flush()
On Apr 29, 2007, at 12:11 PM, Martin Aspeli wrote: > > When the user saves a form, each form field is saved one-by-one. That > is, the storage layer is told "save falue foo" and then later "save > value bar". These calls are isolated, so the storage layer doesn't > know when the form submit handler is finished saving values. > there is a pattern to this, which is that you use a single Session to represent the full transaction, i.e. all the form fields being modified. you then wrap the whole submit cycle using this single Session, and then you flush() at the end. thats the entire point of the unit of work pattern. since all the actors within the submit cycle share the same session, they all will see the same, consistent set of object instances, whether they are persisted or not (although a flush() will ensure that the persistent objects thus far will come back from query operations too). an extension to this pattern is to wrap the whole submit cycle within SessionTransaction boundaries, so that multiple flush()es all participate in the same database transaction which is commited at the end. > Therefore, my idea is to construct a mapper object lazily (on the > first form field) and attach it to a session immediately (with > session.save(), I presume). mappers are intended to be module-level constructs, corresponding to table and class design. unless youre generating classes themselves dynamically, i see no reason to construct mappers lazily. > As the other save-field events come in, > I'll update the object property-by-property. At the request boundary, > I can make sure the session is flushed. yes, just update properties whenever you want, and forget that they are even persisted. the flush() at the end takes care of it for you. --~--~-~--~~~---~--~~ 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: Map one property as a function of two others? (one via a secondary table)
Aha, I should have realized it was just the default cascade=save- update. Thanks for the response; I went and read some more as recommended. So one strategy would be to just define physical_size as a method inside of my Widget class, which goes and loads the physical_size of a Widget instance from a widget_size_table. Was I correct in thinking the strategy I mentioned already (mapping the property to a join) should work? I would rather have the property mapped in, not expressed as a class method, just so the syntax doesn't look different for this property than for the other Widget properties. > a relation like this cant be persisted Does that mean that I could not, for example, assign a physical_size to a bunch of Widgets and expect the system to figure out what updates should therefore be made in the widget_size_table? If so, that's OK since that's the exact opposite of the information flow I'm looking for. I suppose setting viewonly=True can be seen as a way of informing SQLAlchemy of exactly that fact so that it doesn't have to try and figure out the reverse. Thanks again! On Apr 29, 7:44 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Apr 29, 2007, at 3:04 AM, sqAlembic wrote: > > > > > > > Again, I want to make physical_size respond as a property of Widget. > > I want to be able to initialize a new Widget instance, give it only a > > nominal_size, associate it with a WidgetModel, and thereby it acquires > > a physical_size. I'd like to be able to do the following: > > wm1 = WidgetModel(mfr='FrobozzCo', name='GasketronXL') > ws1 = WidgetSize(wm1, 10, 300) > w1 = Widget(nominal_size=10) > print '%s %s size %d measures: %d millimeters.' % > > (w1.model.mfr, w1.model.name, w1.nominal_size, > > w1.physical_size) > > > FrobozzCo GasketronXL size 10 measures: 300 millimeters. > > > My attempts at the proper mapper to do this have, so far, resulted in > > error messages asking me to specify primary (and perhaps secondary) > > joins. Should I be joining widget_size_table to widget_model_table > > and then mapping to that join? > > a relation like this cant be persisted so youd have to put the > viewonly=True flag on it. see the docs on the viewonly flag, as well > as the "handling large collections" section for some other strategies > on properties that load special things. > > > > > Secondary question, regarding session.save(): apparently if I save at > > least one of my newly instantiated Widgets then my test code will show > > both as a result of the test query at the end; if I don't save either > > then neither one will result at the end. Why does saving only one of > > the instances result in both printing out at the end? Is it because > > of the widget<>model relation with backref? > > relationships have "save-update" cascade as the default setting. you > can turn off all cascade rules via "cascade=None". see the docs on > "cascade rules". --~--~-~--~~~---~--~~ 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: GRANT weirdness.
Yep that's it.
If anyone has the same just do this:
engine.execute('BEGIN;GRANT SELECT ON TABLE checkout_info TO
testuser;COMMIT;')
Ciao
Koen
On Apr 29, 4:51 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Apr 29, 2007, at 9:28 AM, Koen Bok wrote:
>
>
>
> > I want to grant permissions through sqlalchemy, but for some reason if
> > I let an engine execute the sql it does not work:
>
> > Code:
>
> > from sqlalchemy import *
>
> > engine = create_engine('postgres://localhost/Test')
> > engine.echo = True
>
> > engine.execute('CREATE USER testuser;')
> > engine.execute('GRANT SELECT ON TABLE testtable TO testuser;')
>
> my only guess is that the GRANT needs a "COMMIT" issued afterwards
> (which is a postgres weirdness in itself, that schema changes are
> part of transactions). SA's autocommit logic is based on the
> statement text containing INSERT, UPDATE, ALTER etc. but i dont think
> GRANT is in there. so use an explicit connection/transaction for now.
--~--~-~--~~~---~--~~
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: Modifying objects after session.save() but before session.flush()
On Apr 29, 3:48 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Apr 29, 2007, at 7:10 AM, Martin Aspeli wrote: > > > Essentially, I'm dealing with a framework that has a storage layer > > abstraction which is a little broken. It asks the storage to save > > values > > one-by-one, and there is no well-defined way to know when it's done > > saving values. Previous attempts at SQL integration fell down on > > things > > like NOT NULL constraints, because they would essentially issue one > > UPDATE statement for each column (well, an INSERT on the first one). > > im not sure what you mean by "it asks the storage to save one by > one", if its just a matter of the schema design having circular-row > relationships (i.e. cant just INSERT x and then INSERT y because they > have foreign key constraints to each other), there is a relationship > flag called "post_update" which is intended to make these possible. Sorry, I probably didn't explain myself very well. The problem is not in the database. I have a web application framework which generates forms, and which has a storage abstraction layer which descides where the form values are saved after the form has been submitted and validated. I'd like to use SQLAlchemy to write such a storage layer which stores the value in any SQL database. When the user saves a form, each form field is saved one-by-one. That is, the storage layer is told "save falue foo" and then later "save value bar". These calls are isolated, so the storage layer doesn't know when the form submit handler is finished saving values. Therefore, my idea is to construct a mapper object lazily (on the first form field) and attach it to a session immediately (with session.save(), I presume). As the other save-field events come in, I'll update the object property-by-property. At the request boundary, I can make sure the session is flushed. > > I guess what I'm after is a way to attach a "partial" object to a > > session, and them modify it. I can see that working if SA doesn't > > clone > > objects in the session, i.e. my object references still point to the > > thing in the session and SA doesn't attempt to validate the object as > > soon as it's saved in the session. > > theres no cloning going on, you can flush() away any number of times. I'd only flush once, I think. However, if I can save once, the modify the object I just saved any number of times, and then flush at some later point, that'd work I think. Martin --~--~-~--~~~---~--~~ 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: GRANT weirdness.
On Apr 29, 2007, at 9:28 AM, Koen Bok wrote:
>
> I want to grant permissions through sqlalchemy, but for some reason if
> I let an engine execute the sql it does not work:
>
> Code:
>
> from sqlalchemy import *
>
> engine = create_engine('postgres://localhost/Test')
> engine.echo = True
>
> engine.execute('CREATE USER testuser;')
> engine.execute('GRANT SELECT ON TABLE testtable TO testuser;')
my only guess is that the GRANT needs a "COMMIT" issued afterwards
(which is a postgres weirdness in itself, that schema changes are
part of transactions). SA's autocommit logic is based on the
statement text containing INSERT, UPDATE, ALTER etc. but i dont think
GRANT is in there. so use an explicit connection/transaction for now.
--~--~-~--~~~---~--~~
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: Modifying objects after session.save() but before session.flush()
On Apr 29, 2007, at 7:10 AM, Martin Aspeli wrote: > Essentially, I'm dealing with a framework that has a storage layer > abstraction which is a little broken. It asks the storage to save > values > one-by-one, and there is no well-defined way to know when it's done > saving values. Previous attempts at SQL integration fell down on > things > like NOT NULL constraints, because they would essentially issue one > UPDATE statement for each column (well, an INSERT on the first one). im not sure what you mean by "it asks the storage to save one by one", if its just a matter of the schema design having circular-row relationships (i.e. cant just INSERT x and then INSERT y because they have foreign key constraints to each other), there is a relationship flag called "post_update" which is intended to make these possible. > > I guess what I'm after is a way to attach a "partial" object to a > session, and them modify it. I can see that working if SA doesn't > clone > objects in the session, i.e. my object references still point to the > thing in the session and SA doesn't attempt to validate the object as > soon as it's saved in the session. theres no cloning going on, you can flush() away any number of times. --~--~-~--~~~---~--~~ 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: Map one property as a function of two others? (one via a secondary table)
On Apr 29, 2007, at 3:04 AM, sqAlembic wrote: > > > Again, I want to make physical_size respond as a property of Widget. > I want to be able to initialize a new Widget instance, give it only a > nominal_size, associate it with a WidgetModel, and thereby it acquires > a physical_size. I'd like to be able to do the following: > wm1 = WidgetModel(mfr='FrobozzCo', name='GasketronXL') ws1 = WidgetSize(wm1, 10, 300) w1 = Widget(nominal_size=10) print '%s %s size %d measures: %d millimeters.' % > (w1.model.mfr, w1.model.name, w1.nominal_size, > w1.physical_size) > > FrobozzCo GasketronXL size 10 measures: 300 millimeters. > > > My attempts at the proper mapper to do this have, so far, resulted in > error messages asking me to specify primary (and perhaps secondary) > joins. Should I be joining widget_size_table to widget_model_table > and then mapping to that join? a relation like this cant be persisted so youd have to put the viewonly=True flag on it. see the docs on the viewonly flag, as well as the "handling large collections" section for some other strategies on properties that load special things. > > Secondary question, regarding session.save(): apparently if I save at > least one of my newly instantiated Widgets then my test code will show > both as a result of the test query at the end; if I don't save either > then neither one will result at the end. Why does saving only one of > the instances result in both printing out at the end? Is it because > of the widget<>model relation with backref? relationships have "save-update" cascade as the default setting. you can turn off all cascade rules via "cascade=None". see the docs on "cascade rules". --~--~-~--~~~---~--~~ 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: Bug with order_by and certain versions of mysql
On Apr 29, 2007, at 1:07 AM, Ram Yalamanchili wrote:
>
> 1) select a func.count() column
> 2) Name an alias to it
> 3) use this alias with a .order_by
>
func.count(x).label('whatever')
then
order_by('whatever')
(or order_by=['whatever'], depending on context)
--~--~-~--~~~---~--~~
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: GRANT weirdness.
And ofcourse the first result should be this in the above mail:
> Test=# \z testtable
>Access privileges for database "Test"
> Schema | Name | Type | Access privileges
> +---+---+
> public | testtable | table | {koen=arwdxt/koen}
> (1 row)
--~--~-~--~~~---~--~~
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] GRANT weirdness.
I want to grant permissions through sqlalchemy, but for some reason if
I let an engine execute the sql it does not work:
Code:
from sqlalchemy import *
engine = create_engine('postgres://localhost/Test')
engine.echo = True
engine.execute('CREATE USER testuser;')
engine.execute('GRANT SELECT ON TABLE testtable TO testuser;')
Result:
Test=# \z testtable
Access privileges for database "Test"
Schema | Name | Type | Access privileges
+---+---+
public | checkout_info | table | {koen=arwdxt/koen}
(1 row)
And when I do the exact same sql in psql
Test=# CREATE USER testuser;
CREATE ROLE
Test=# GRANT SELECT ON TABLE testtable TO testuser;
GRANT
Test=# \z testtable
Access privileges for database "Test"
Schema | Name | Type | Access privileges
+---+---+
public | Test | table | {koen=arwdxt/koen,testuser=r/koen}
(1 row)
I never had any problems doing any other sql stuff like creating
triggers etc with this. Anyone got a hint?
Thanks,
Koen
--~--~-~--~~~---~--~~
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: Get URL from engine
That would be very nice. You have my vote. Koen On Apr 29, 5:13 am, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Apr 28, 2007, at 10:34 AM, Koen Bok wrote: > > > > > Is there a nice way to get the URL from a given engine? > > not at the moment...the url goes into a create function and gets > thrown away. have been meaning to just connect "url" to "engine" > when the engine is created so its available as engine.url. --~--~-~--~~~---~--~~ 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] Modifying objects after session.save() but before session.flush()
Hi guys, I've been saying this quite a few times in the past couple of days, to various people, but SQLAlchemy is intensely good. Well done. :) Anyway, I have a slightly insane use case which may require me to do something like this: - Define a mapper for some table - Instantiate an object of the mapper class with default values only - Save this instance in the current session - Set values on the mapper class instance one-by-one, in response to various event - Flush at a request boundary Essentially, I'm dealing with a framework that has a storage layer abstraction which is a little broken. It asks the storage to save values one-by-one, and there is no well-defined way to know when it's done saving values. Previous attempts at SQL integration fell down on things like NOT NULL constraints, because they would essentially issue one UPDATE statement for each column (well, an INSERT on the first one). I'd basically be something like: >>> entity = MappedEntity() >>> session.save(entity) >>> entity.field1 = "some value" >>> entity.field2 = "another value" >>> session.flush() Except that each of those lines would be called from different event handlers and not in such an orderly sequence. The main problem is that I don't know when the events stop coming, i.e. when the object is "complete". I know that it will complete before the request boundary, and I have a way to flush at a request boundary automatically. I guess what I'm after is a way to attach a "partial" object to a session, and them modify it. I can see that working if SA doesn't clone objects in the session, i.e. my object references still point to the thing in the session and SA doesn't attempt to validate the object as soon as it's saved in the session. Or is this just a really bad idea? :) Martin --~--~-~--~~~---~--~~ 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] Map one property as a function of two others? (one via a secondary table)
Hello Alchemists,
See working minimal example below. Here we have Widgets, each
associated with a WidgetModel and each having a nominal_size. I wish
to add a physical_size property to Widgets, a property which should be
a function of both Widget_Model and nominal_size. I'm hung up
somewhere in getting this new property mapped in correctly.
# widgetminexample.py
from sqlalchemy import *
metadata = BoundMetaData('sqlite:///:memory:')
widget_table = Table('widget_table', metadata,
Column('id', Integer, primary_key=True),
Column('nominal_size', Integer),
Column('widget_model_id', Integer,
ForeignKey('widget_model_table.id')))
widget_model_table = Table('widget_model_table', metadata,
Column('id', Integer, primary_key=True),
Column('mfr', String(20)),
Column('name', String(20)))
class Widget(object):
def __init__(self, nominal_size=0):
self.nominal_size=nominal_size
def __repr__(self):
return "%s %d" % (self.model, self.nominal_size)
class WidgetModel(object):
def __init__(self, mfr='mfr', name='name'):
self.mfr=mfr
self.name=name
def __repr__(self):
return "%s %s" % (self.mfr, self.name)
widget_mapper = mapper(Widget, widget_table, properties = {
'model': relation(WidgetModel, backref='widgets',
uselist=False)})
widget_model_mapper = mapper(WidgetModel, widget_model_table)
if __name__ == "__main__":
metadata.create_all()
session=create_session()
wm1 = WidgetModel( mfr='FrobozzCo', name='GasketronXL' )
w1 = Widget( nominal_size=10 )
w2 = Widget( nominal_size=8 )
w1.model = wm1
w2.model = wm1
session.save(w1)
session.save(w2)
session.flush()
print 'widgets in database:'
for w in session.query(Widget):
print w
metadata.drop_all()
A Widget has the attribute nominal_size. I want each Widget to also
have a physical_size. But physical_size needs to be a function of
both the nominal_size and the model. Once I've created a table (for
each WidgetModel) specifying the relationship of nominal_size to
physical_size for that WidgetModel, I don't want to have to know
physical_size for each new Widget introduced into the system.
Associating a Widget with a WidgetModel needs to result in the Widget
getting a physical_size associated with it (looked up via its model
and nominal_size).
My problem: I'm at a loss to phrase this in SQLAlchemy so far, though
it's probably staring me in the face.
I considered the following, but I have not been able to come up with a
(nominal_size, model)->physical_size mapper that would accomplish the
task.
widget_size_table = Table( 'widget_size_table', metadata,
Column('nominal_size', Integer, primary_key=True),
COlumn('widget_model_id', Integer,
primary_key=True, ForeignKey('widget_model_table.id')),
Column('physical_size', Integer))
Class WidgetSize(object):
def __repr__ (self):
...
mapper(widget_size_table, WidgetSize, ...) #
Again, I want to make physical_size respond as a property of Widget.
I want to be able to initialize a new Widget instance, give it only a
nominal_size, associate it with a WidgetModel, and thereby it acquires
a physical_size. I'd like to be able to do the following:
>>> wm1 = WidgetModel(mfr='FrobozzCo', name='GasketronXL')
>>> ws1 = WidgetSize(wm1, 10, 300)
>>> w1 = Widget(nominal_size=10)
>>> print '%s %s size %d measures: %d millimeters.' %
(w1.model.mfr, w1.model.name, w1.nominal_size,
w1.physical_size)
FrobozzCo GasketronXL size 10 measures: 300 millimeters.
My attempts at the proper mapper to do this have, so far, resulted in
error messages asking me to specify primary (and perhaps secondary)
joins. Should I be joining widget_size_table to widget_model_table
and then mapping to that join?
Thanks in advance for any advice.
Secondary question, regarding session.save(): apparently if I save at
least one of my newly instantiated Widgets then my test code will show
both as a result of the test query at the end; if I don't save either
then neither one will result at the end. Why does saving only one of
the instances result in both printing out at the end? Is it because
of the widget<>model relation with backref?
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
