Re: [sqlalchemy] Issue with cx_oracle
Am 18.02.2014 23:20, schrieb Michael Bayer: > good catch, I've rewritten the docs here: > http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#unicode. > The more common approach of using text() is included. Thanks a lot for the quick reponse and explanations. Your recipe of specifying columns explicitly as Unicode worked well for me. The only gotcha was that I needed to explicitly specify the encoding as argument to create_engine(), while that had not been necessary when the unicode outputtypehandler was installed (I guess cx_Oracle derived the proper encoding automatically from NLS_LANG). I'll try to create a pullreq for the coerce_to_unicode (turned off by default) feature over the weekend anyway. -- Christoph -- 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/groups/opt_out.
[sqlalchemy] Issue with cx_oracle
The docstring for the cx-Oracle dialog says: "SQLAlchemy will pass all unicode strings directly to cx_oracle, and additionally uses an output handler so that all string based result values are returned as unicode as well." The latter does no longer seem to be true; the handler was recently removed with ticket 2911. So now when I have varchar2 columns and do a simple query like this one, I get encoded strings instead of unicode as before (in Python 2): engine = create_engine('oracle+cx_oracle://..') con = engine.connect() for row in con.execute("select username from users"): print row Is this really intended? What am I supposed to do when I want to always get unicode back? -- Christoph -- 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/groups/opt_out.
Re: [sqlalchemy] Enum example
It might be still worthwhile to revisit that example now that http://www.python.org/dev/peps/pep-0435/ has been accepted. -- Christoph -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] PostgreSQL range types
Am 23.11.2012 14:34, schrieb philipt: > Psycopg 2.4.6 will support them > (http://initd.org/psycopg/docs/extras.html#range-data-types). > Ranges are quite useful and play well with exclusion constraints. Yepp. I also often have ranges (not only time ranges) in my databases, currently implemented as foo_min, foo_max columns. Using a range type would have been much more natural and probably much easier to handle. -- Christoph -- 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.
Re: [sqlalchemy] Named tuples in SQLAlchemy
Am 09.11.2012 23:54, schrieb Michael Bayer: > NamedTuple is a tough one - because with our result sets we need to > create a new NamedTuple for every call to execute(), meaning it has > to be performant not just on creating new instances of the tuple, > but on creating new tuple types as well. > > If you look at the source to NamedTuple, it is going through some > very elaborate hoops ... Yes, that's true. Of course this is done for good reasons, namely to give you all the goodness of zero overhead per instance in terms of memory and creation time, a telling type name etc. Raymond Hettinger explains the design principles very well at http://blip.tv/pycon-us-videos-2009-2010-2011/pycon-2011-fun-with-python-s-newer-tools-4901215 (minutes 11 to 27). But you're right, it also comes at a cost, namely creation time for the type itself. A quick test with timeit showed that this time overhead only amortizes when you create at least about 175 instances. The memory advantage is of course always there, but it's not interesting for smaller datasets either. And then it will depend on how large your data values are compared to the names of the columns. There is also not much benefit in creating a custom type name for the tuple, since query results usually don't have an obvious name anyway. So maybe it's better to keep the current implementation and just make it a bit more similar to Python's named tuples, e.g. renaming _labels to _fields and adding _as_dict. By the way, the underscore has been only added here to minimize the possibility of name clashes with tuple fields, they shall not indicate that these are private attributes. As another aside, the _as_dict method should not return a normal dict, but an OrderedDict which can also be taken from collections nowadays. -- Christoph -- 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] Named tuples in SQLAlchemy
Query results in SQLAlchemy are returned as named tuples, but SQLAlchemy uses its own flavor of named tuples which is not fully compatible with collections.namedtuple in the standard lib. For instance, "_fields" is called "_labels" in SQLAlchemy, and the method "_asdict()" which could be helpful to convert query results to JSON is missing in SQLAlchemy. Wouldn't it be better to use the standard collections.namedtuples. I know it's only available since Py 2.6, but SQLAlchemy will eventually only work with Py 2.6 anyway, and could just use a fallback implementation for Py 2.5 for the time being. -- Christoph -- 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.
Re: [sqlalchemy] How to implement conditional foreign key relationships?
Thanks again for the link to the "Polymorphic Associations with SQLAlchemy" article. You're right, that's the same problem I had been facing. Your generic solution is not applicable for me, because I can't change the database design, but the elementary solution is good enough. For the curious, here is my application to the example I had posted: from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Boolean, String engine = create_engine('sqlite:///:memory:') Session = sessionmaker(bind=engine) session = Session() Base = declarative_base() class CountryNat(Base): __tablename__ = 'country_nat' country_id = Column(String(2), primary_key=True) name = Column(String(40)) def __init__(self, country_id, name): self.country_id = country_id self.name = name class CountryInt(Base): __tablename__ = 'country_int' country_id = Column(String(2), primary_key=True) name = Column(String(40)) def __init__(self, country_id, name): self.country_id = country_id self.name = name class Contact(Base): __tablename__ = 'contact' name = Column(String(80), primary_key=True) native = Column(Boolean()) country_id = Column(String(2)) country_nat = relationship(CountryNat, primaryjoin=country_id==CountryNat.country_id, foreign_keys=[country_id]) country_int = relationship(CountryInt, primaryjoin=country_id==CountryInt.country_id, foreign_keys=[country_id]) country = property(lambda self: getattr(self, 'country_%s' % ('nat' if self.native else 'int'))) def __init__(self, name, native, country_id): self.name = name self.native = native self.country_id = country_id Base.metadata.create_all(engine) if not session.query(Contact).first(): session.add_all([ CountryNat('IL', 'Illinois'), CountryNat('NY', 'New York'), CountryInt('KZ', 'Kazakhstan'), CountryInt('NL', 'Netherlands'), Contact('Flozell Adams', True, 'IL'), Contact('Vince Lombardi', True, 'NY'), Contact('Borat Sagdiyev', False, 'KZ'), Contact('Ryan Donk', False, 'NL')]) session.commit() for contact in session.query(Contact): print "%s, %s" % (contact.name, contact.country.name) -- 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.
Re: [sqlalchemy] How to implement conditional foreign key relationships?
Am 31.08.2012 16:07, schrieb Michael Bayer: right, this is a really common (and IMHO incorrect) design that is referred to as a "generic foreign key" or a "polymorphic foreign key". It was popularized by frameworks such as Rails and Django. We have a recipe that implements this exact schema with SQLAlchemy which is introduced here: http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/ Thanks for the quick reply, will have a look at this. Your blog is a real treasure trove :) -- Christoph -- 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] How to implement conditional foreign key relationships?
I'm trying to use SQLAlchemy with a silly legacy database the design of which I cannot change. In this database, there are parent tables with foreign key columns which can point to two different tables, depending on another column in the parent table. Think for example of a user table with a column "country_id" which can either mean a US country id like IL=Illinois or an international country id, like NL=Netherlands, depending on whether another column "native" is set to true (native user) or not (foreigner). The US countries and international countries are stored in different tables. Of course this foreign key relationship is not (and probably cannot) be enforced by SQL foreign key constraints in the database (it does not use SQL based constraints at all). But would it be possible to create such a "conditional" relationship configuration using the SQLAlchemy relationship API? I solved the problem by using a manually written property, but maybe I'm overlooking something? -- Christoph -- 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.
Re: [sqlalchemy] Issue with `filter_by`?
Am 25.07.2012 07:17, schrieb Amos: >>> [obj.code for obj in Session.query(Model).filter_by(my_column=123).all()] [u'123ad', u'123lpb', u'123xd8', u'123za0'] I would expect no results as no column exactly matches the string representation of my number Your result will depend on how your database engine compares strings to integers. I can imagine that on some databases you will get no results, on some you get an Error, because they refuse to make automatic type conversions, and on some you will get what you got because they convert the string '123ad' to the integer 123 (this is how e.g. the C function atoi works). I haven't tested it, but I can imagine that Postgres will throw an error and MySQL will give your result. That's one of the many reasons I prefer Postgres. -- Christoph -- 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.
Re: [sqlalchemy] Order by ordinal position
Am 28.07.2011 00:41 schrieb Michael Bayer: order_by() is accepting SQL expressions, not values, so it is consistent that values should be coerced to SQL first in this case literal_column("1"), etc. My thought was that if order_by automatically coerces strings, i.e. order_by('1') works, then it should also coerce integers automatically since these have a well-defined meaning here. Of course this coercion should be done for order_by and group_by only, not everywhere. I'm not actually understanding what "ORDER BY 1" is getting you here in any case or how this relates to AJAX. Just a practical example for when ordering by ordinal position can be useful. If you have many AJAX autosuggest fields, fed by the results of various queries, you could postprocess all of these queries with the simple common code query.order_by(1).limit(10) instead of ordering all queries individually. -- Christoph -- 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] Order by ordinal position
Most databases allow ordinal numbers as expressions in order by clauses, some even in group by clauses. And in earlier versions of SQLAlchemy it had in fact been possible to express these as integers, e.g. query.order_by(1, 3, desc(2)). However, in version 0.7.1 this yields an "SQL expression object or string expected" error. To make use of this feature you now need to write query.order_by('1', '3', desc('2')) which is not so readable and convenient as the above. Has this been changed by intent? I know, using ordinals has some drawbacks and may be considered a bad habit, but they can still be useful in some situations. E.g. it makes it possible to decorate arbitrary queries of the same structure for use in an autosuggest AJAX controller with an "order_by(1)". -- Christoph -- 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] Small docs problem
Just noticed that the 0.6.6 docs show a "name" parameter of subquery(), but it does not yet seem to be available in 0.6.6. There should be a note that it can only be used in 0.6.7 or 0.7. -- Christoph -- 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.
Re: [sqlalchemy] Infinite recursion in 0.6beta3
Am 06.04.2010 17:42 schrieb Michael Bayer: the "0 +" seems to be some artifact of how sum() works: Yes; it seems a start value is always used, and if you do not specify one, it's assumed to be 0. But that's not really a problem. Thanks for the quick fix. -- Christoph -- 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] Infinite recursion in 0.6beta3
I think I just found a regression in 0.6. When evaluating the following SQL expression sum(2*(func.length('bla'),)) with SQLAlchemy 0.6beta3 I get a RuntimeError: maximum recursion depth exceeded With SQLAlchemy 0.5 this used to work, except that it was translated to 0 + length('bla') + length('bla') instead of simply length('bla') + length('bla') -- Christoph -- 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: Synchronization problem at backref of a one-to-many relation to the same class.
Michael Bayer schrieb: > I had the idea that since "a1" appears to be in the collections of both u1 > and u2, it would be arbitrary where "a1" ended up after the flush > completed. But that is likely wrong, in that the flush() is going to look > at change events to determine what state changes to persist to the > database. I'm not sure if there's a more complex series of moves between > u1 and u2 which would make predicting the final destination of "a1" > difficult, though. I still don't really understand why/how a flush operation would change the .addresses attribute of any object as a side effect. Maybe I just understand SA's unit-of-work implementation not good enough. -- Christoph --~--~-~--~~~---~--~~ 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: Synchronization problem at backref of a one-to-many relation to the same class.
Tefnet Developers schrieb: > The funny thing is that it works on two separate classes. I don't think so. As Michael already pointed out, these things are tested in test/orm/test_backref_mutations.py. The situation in your example is covered by test_collection_move_preloaded(), except that it uses two different classes instead of a self-referencing relation, and the test presumes the same behavior you were experiencing. You can translate the test objects to the objects in your example: user -> manager, addresses -> subordinates, u1 -> BillGates, u2 -> SteveBallmer, a1 -> CraigMundie The test explicitely checks a1 in u1.addresses i.e. in your example CraigMundie in BillGates.subordinates So this behavior is in fact intended by SQLAlchemy. As Mike explained, this is because propagating events further than the 2 objects directly involved would become too complex, possibly leading to recursion and performance issues. @Mike: The test code says in a comment "flushing at this point means its anyone's guess". Can you elaborate what is meant by this comment? -- Christoph --~--~-~--~~~---~--~~ 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: Synchronization problem at backref of a one-to-many relation to the same class.
Tefnet Developers schrieb: > I am having a problem here - at one point a one-to-many relation > attribute is not in sync with a backref one (http://dpaste.com/104225/): I can reproduce this and also like to know whether this is a bug or simply not supported by the default list instrumentation. By the way, you don't need to use a meta class in your example; you can simply set manager = sqlalchemy.orm.relation( 'Employee', backref='subordinates', remote_side=Id) -- Christoph --~--~-~--~~~---~--~~ 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: Session.mapper
Michael Bayer schrieb: > its not coming out anytime soon. A whole lot of info on how to create it > yourself is at > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SessionAwareMapper . Thanks for the info. We'll probably add an emulation based on that recipe to TurboGears 1.1. -- Chris --~--~-~--~~~---~--~~ 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] Session.mapper
The Session.mapper feature has been marked as deprecated since SA 0.5.5, but it's still available in SA 0.6b1. Does this mean that it will continue to exist in SA 0.6 final and other 0.6.x releases? I'm asking because TurboGears 1.x is using this feature. We want to document migration issues properly and provide our own emulation when it's gone, since we want to be backward compatible in TG 1.x and since it was pretty convenient and not too confusing. --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
Michael Bayer schrieb: > ArgumentError: Mapper 'Mapper|User|None' does not have a mapped_table > specified. (Are you using the return value of table.create()? It no > longer has a return value.) > > That's a really old error message, and I can see how its less than > perfect so I've just changed it and added a declarative specific > message which supercedes it, so the issue is resolved. But I don't > see how the old message is *confusing*. It seems obvious that its > saying "no table is present to be mapped", what else could it possibly > mean ? Thanks. Well, the message was not really confusing, only misleading in that it says you need to specify "mapped_table" where you actually need to specify "__table__" or "__tablename___". -- Christoph --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
Michael Bayer schrieb: > if Bar inherits from Foo, Foo is mapped to "foo_table", Bar has no > table, Bar will be mapped to foo_table as well. In that case, no implicit name should be set or course. It should only be set if a name cannot be figured out otherwise. > oh, this is entirely news to me that you can send column positions to > ORDER BY. SQLA has no awareness of that concept right now. seems > like "more than one way to do it" at the moment...(i.e. order by > columns *or* position, times use desc or negation == 4 ways). Using column positions is a standard SQL feature AFAIK (i.e. positive indices; using negative indices for reverse order was my idea only) and it also seems to works perfectly well with SQLAlchemy :) Since the "only one way" principle is broken here anyway, I thought adding another shortcut will not harm... -- Christoph --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
Michael Bayer schrieb: > As far as implicit tablename, it breaks the single inheritance > scenario. but also besides that I made a comment on that here: > > http://www.sqlalchemy.org/trac/ticket/1270#comment:2 Thanks, I hadn't seen that. Not quite sure what you mean with single inheritance scenario, though. >> Another idea (not sure if it really makes sense): In the order_by >> clause, negative integers could be used for descending order, i.e. -2 >> would work like desc(2). > > im not actually familiar with that technique ? does that mean ORDER > BY 2 DESC or ORDER BY somecolumn DESC(2) ? Very simple, the former: session.query(Foo).order_by(-2) should work the same as from sqlalchemy import desc session.query(Foo).order_by(desc(2)) The idea is that it's somewhat shorter and intuitive since for names of numerical columns, you also get reverse order if you add a minus sign. -- Christoph --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
Andreas Jung schrieb: > Please no implicit magic under the hood. You can have multiple mappers > for the same table. Citing Guido: explicit is better than implicit. You wouldn't be forced to use that magic, you could still set __tablename__ explictly. I understand your argument, but otoh "beautiful is better than ugly" outplays "explicit is better than implicit" ;-) -- Christoph --~--~-~--~~~---~--~~ 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: SQLAlchemy usage in TurboGears
Michael Bayer schrieb: > your current approach of using classical mappings with Session.mapper > is entirely supported. ... > So without reading the thread, I think declarative would probably be > good, you can use it with Session.mapper if you want, though I view > Session.mapper as somewhat legacy. Thank you for these clarifications. So we will probably keep the old contextual session and mapper, but not propgate that usage any longer, and rewrite the templates for the declarative usage. -- Christoph --~--~-~--~~~---~--~~ 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 usage in TurboGears
Hi Michael, we're just discussing some issues with SQLAlchemy over at the TurboGears mailing list which appeared because the new SQLAlchemy usage as promoted in the 0.5 docs differs from the classical usage. We are unsure how to solve these issues, so I thought it will be best to get some input and suggestions from you. In the TurboGears project, our standard application templates for SQLAlchemy create models in the classical (non-declarative) way. To make this a bit more convenient, TurboGears creates a scoped session and maps the model classes with this session's mapper (i.e. with a contextual, session-aware mapper that is made available to the TurboGears application). This worked very well for us, but now with SA 0.5, the declarative usage becomes more popular, which uses a normal (not contextual) mapper. The existence of contextual mappers is not even mentioned any more in the SA 0.5 docs. Is this an omission or is my impression right that you want to phase out this kind of usage? Another point is that our default scoped_session(create_session) in TurboGears creates sessions with autoflush=False and autocommit=True, while defaults sessionmaker() sessions in SQLAlchemy now have the opposite, autoflush=True and autocommit=False. So maybe our usage is outdated in that regard, too? What kind of usage do you suggest to provide as default in our TurboGears app templates that will be as convenient for the average user and as future-safe as possible? -- Christoph --~--~-~--~~~---~--~~ 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: DateTime bug probably
mraer schrieb: > This simple test fails with error: > DataError: (DataError) unable to parse time None None Which version are you using? Your test works for me (except I needed to change "Mapper" to "mapper") with SA 0.5rc1 and psycopg 2.0.8, Py 2.5, Postgres 8.3.1 on WinXP. As an aside, you should not name tables or columns "date" because it's a reserved keyword in SQL (though not in PG), and you should avoid usiung the current time as in datetime.now() in your tests because that makes them not reproducible. -- Christoph --~--~-~--~~~---~--~~ 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: Queries very slow for parsing Wikipedia dump -- any ideas for speeding it up?
[EMAIL PROTECTED] schrieb: > if it's still slow - or hard to knit the links - u could generate some > DB-specific dump/replication-format and import from there. In the case of Postgres, you should use "copy" for such mass inserts. -- Christoph --~--~-~--~~~---~--~~ 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: Comparing tuples
Michael Bayer schrieb: > just use the plain string and not text(), and use :paramname as the > bind param format. example is here: > http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_querying_using Excellent, that works: print session.query(User).filter( "(last_name, first_name) < (:last_name, :first_name)", ).params(first_name='Joe', last_name='Doe').all() I had looked into the 0.4 docs only... -- Christoph --~--~-~--~~~---~--~~ 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: Comparing tuples
This does not work either: print session.query(User).filter( text("(last_name, first_name) < (%(last_name)s, %(first_name)s)", )).params(first_name='Joe', last_name='Doe').all() Running out of ideas... -- Christoph --~--~-~--~~~---~--~~ 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: Comparing tuples
> Is there a simple way of comparing tuples in the SQLAlchemy query > language, like (User.last_name, User.first_name) < ('Joe', 'Doe')? > SQL-wise it is possible (at least with PostgreSQL), but you cannot write > this as a filter expression. Tried the following, but it does not work: print session.query(User).filter( text("(id, name) < (%(fist_name)s, %(last_name)s)", bindparams=[ bindparam('first_name', 'Joe'), bindparam('last_name', 'Doe')] )).all() What's wrong with that? -- Christoph --~--~-~--~~~---~--~~ 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] Comparing tuples
Is there a simple way of comparing tuples in the SQLAlchemy query language, like (User.last_name, User.first_name) < ('Joe', 'Doe')? SQL-wise it is possible (at least with PostgreSQL), but you cannot write this as a filter expression. -- Christoph --~--~-~--~~~---~--~~ 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: Newbie question
jrpfinch schrieb: > mapper(Recurring_task, recurring_tasks, properties = > {"raiser":relation(Employee_status, > primaryjoin=(recurring_tasks.c.rt_raiser_id==Employee_status.c.emp_id)}) > > ^ > SyntaxError: invalid syntax There is one missing closing bracket. You should use an editor that helps you with these kind of errors... -- Christoph --~--~-~--~~~---~--~~ 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: Py 2.3 supported in SA 0.5?
Michael Bayer schrieb: > 2.3 is definitely not supported in 0.5 unless you can make the case > for it ASAP. nobody has made any case for 2.3 as of yet. TurboGears 1.0 still supports Py 2.3, but TG 1.0 favors SQLObject anyway. TG 1.1 which favors SQLAlchemy has also ceased Py 2.3 support. So what concerns TurboGears, I see no problems. -- Christoph --~--~-~--~~~---~--~~ 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] Py 2.3 supported in SA 0.5?
Will Python 2.3 still be supported by SA 0.5? I noticed that sqlalchemy.ext.orderinglist uses the new decorator syntax. -- Christoph --~--~-~--~~~---~--~~ 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: select + "is not"
Jonathan Vanasco schrieb: > this should be straightforward, I think... > > I'm trying to do > > "select email_address from user where is_verified is not true" > ( or "is not null" or "is not false" ) How about sqlalchemy.not_(User.is_verified)? -- Christoph --~--~-~--~~~---~--~~ 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: Ordering null dates
Michael Bayer wrote: > I tend to use a CASE statement for this: CASE WHEN x IS NULL THEN 0 > ELSE x . We have case() construct for that. Yes, but that still leaves me with having to code "infinity" some way. > For your version, use func.coalesce(start, literal_column("timestamp '- > infinity'")) . Thank you. literal_column was exactly what I was looking for. Though I still would like to have "nulls first/last" as well, this construct is also useful when checking date ranges with "between". Thanks again for your quick and helpful answer, as always... -- Christoph --~--~-~--~~~---~--~~ 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] Ordering null dates
I need to order a table by start and end dates, where null values should be interpreted as "prior to all values" for start dates and "later than all values" for end dates. This could be realized with "nulls first", "nulls last", but it seems this did not make it into SQLAlchemy yet (ticket #723). A workaround (for PostgreSQL) would be something like this: order_by coalesce(start, timestamp '-infinity'), coalesce(end_date, timestamp 'infinity') But I don't find how to construct this with SQLAlchemy. Any ideas? Any better solutions? --~--~-~--~~~---~--~~ 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: Creating column comments on the database
Lukasz Szybalski schrieb: > I think I prefer "info" dictionary rather then a string. Dict "info" > which I use already have something like this: > > sqlalchemy.Column('DRIVE_TRAIN', sqlalchemy.Unicode(4) > ,info={"description":"DRIVE TRAIN TYPE [AWD,4WD,FWD,RWD]"}), > sqlalchemy.Column('FUEL_SYS', sqlalchemy.Unicode(4) > ,info={"description":"FUEL SYSTEM CODE", > "FI":"FUEL INJECTION", > "TB":"TURBO"}), I think you're mixing different things here. What I suggested was support of the database comment statement/clause supported by all major databases (except maybe SQL Server). What you're looking for seems to be something similar to the "Domain" feature of the old Oracle Designer, http://marceloverdijk.blogspot.com/2008/05/ref-code-plugin.html http://its.unm.edu/ais/docs/oradomains.htm This would be an interesting extension as well. Should be possible with a custom TypeDecorator or TypeEngine. -- Christoph --~--~-~--~~~---~--~~ 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: Creating column comments on the database
Lukasz Szybalski schrieb: > I started using info dict on a column names in sa file definitions. It > would be nice to save it to db if once could. What db supports > comments and what table name is it? Oracle and PostgreSQL have the "comment on" SQL statement. MySQL has a comment clause for column definitions. SA could abstract away these differences, the syntax would be something like this: users = Table('users', metadata, Column('id', Integer, primary_key=True), Column('name', String(40), comment="The Unix user name"), Column('fullname', String(100), comment="Full name with title")) This way, the table definition in Python itself would be commented, and at the same time, comments on the database would be created that help you if you operate directly on the database with some admin tool. -- Christoph --~--~-~--~~~---~--~~ 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] Creating column comments on the database
Does SQLAlchemy support "comment on column" or comments on other database objects? I expected Column to have a comment keyword, but found nothing of that kind. Maybe I am missing something? I find column comments often useful, and though they may not be SQL standard, they are supported by all major databases. -- Christoph --~--~-~--~~~---~--~~ 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: Checking active transactions
Michael Bayer schrieb: > thats fine...but also why cant you just say try: session.commit() > except: session.rollback(); raise ? This would raise an error when the session is already inactive. I don't want an error in this case. On the other hand, if I remove the raise statement, then errors on commit would be swallowed. I thought about catching only InvalidRequestError, but this might also catch too much. -- Christoph --~--~-~--~~~---~--~~ 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] Checking active transactions
In TurboGears 1, requests are encapsulated between a session.begin() and session.commit() call (or session.rollback(), if there was an error). Starting with SA 0.4.3, the commit() raises an exception if the transaction has been already rolled back in the request (explicitly or due to an error). So TurboGears needs to check whether the transaction is active before the session.commit() call. We currently do this by storing the transaction as the return value of session.begin() and then checking transaction.is_active (or transaction.session.transaction for earlier SA versions). This gets complicated since the transaction may be restarted during a request and can change. So I think it would be handy to tell directly from the session whether the transaction is active or not. We are using *scoped* sessions in TruboGears, and the problem is that the transaction attribute is not available in ScopedSessions, so I can not check for session.transaction.is_active. I can get the original Session by calling session, so session().transaction.is_active would work, but it does not feel right. I guess it is by intent that the session attributes are not visible in scoped sessions. So how about adding a property is_active to the Session that would call session.transaction.is_active? Then you could do: if session.is_active: session.commit() -- Christoph --~--~-~--~~~---~--~~ 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: Getting all distinct attribute values of a certain class attribute
Michael Bayer wrote: > The basic request to get cities as strings by themselves should be > easy enough as: > > select([Customer.city]).distinct().order_by( > Customer.city).execute().fetchall() That's exactly what I wanted - and yes, that's really easy. > we will eventually have a database-neutral "ilike" comparator but that > hasn't been implemented yet. Ok, I'll use the approach with func.lower().like("a%") until then. I noticed func.lower().startswith("a") is also possible, a bit nicer. Thanks a lot for your quick support! -- Chris --~--~-~--~~~---~--~~ 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] Getting all distinct attribute values of a certain class attribute
Assume I have a class Customers mapped with a contextual mapper to a table "customers" and assume the customers have an attribute city. Now I want to get all different cities of my customers, starting with 'A', ordered alphabetically. In plain SQL this would be: select distict city from customers where city like 'A%' order by 1 How would I do this with SQLAlchemy, ideally without knowing the name of the table, i.e. referring to the class name only? The following works, but is not very efficient because it loads all the customers: sorted(set(cust.city for cust in Customer.query.filter( Customer.city.startswith('A%' So instead, I thought of something like the following: Customer.query.from_statement(select([Customer.city], Customer.city.startswith('A%'), distinct=True, order_by=[1])).all() But this does not work, since the select statement does not return the attribute set of the Customer class. However, I feel something along these lines should be possible. Is there something I'm missing? Bonus question: How can I make the comparison case insensitive, i.e. create a where clause like that: ... where city ilike 'A%' ... or ... where lower(city) like 'a%' Any help appreciated. -- Christoph --~--~-~--~~~---~--~~ 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: Can I have an integer primary keys that is not a sequence?
Paul Johnston wrote: >> Is there a way to tell SQLAlchemy to create an ordinary integer primary >> key, without any associated sequence? > > Sure... autoincrement=False Thanks a lot. Found it now in the API docs. I propose that this (and the fact that it is True by default) be also mentioned somewhere in the main docs. -- Chris --~--~-~--~~~---~--~~ 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] Can I have an integer primary keys that is not a sequence?
If I define a table like that: Table('t', metadata, Column('c', Integer, primary_key=True)) then SQLAlchemy creates the column c as a SERIAL on PostgreSQL. Is there a way to tell SQLAlchemy to create an ordinary integer primary key, without any associated sequence? -- Chris --~--~-~--~~~---~--~~ 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] column and table functions from sqlalchemy.sql
Just noticed that "from sqlalchemy import *" imports all functions from sqlalchemy.sql.expression, except "column" and "table" - is this by intent or have these only be forgotten? -- Chris --~--~-~--~~~---~--~~ 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: How to issue DDL statements?
Michael Bayer wrote: > there is a capability within the dialect to determine the "quotability" > of SQL identifiers, via the "preparer()" function. check out its > implementation in ansisql.py. i can add the"preparer()" function and a > base class for the preparer itself to the base Dialect class which will > make this capability more public. The preparer contains a method __generic_obj_format(self, obj, ident) which does what I have in mind. But for one, it's private, and second, it takes an obj parameter which I don't need. I want to process only the identifiert (e.g. for a user, who has no representation in SA anyway), like that: def _format_identifier(self, ident): if self.requires_quotes(ident, ident == ident.lower()): return self._quote_identifier(ident) else: return ident Then I could insert user names like that: 'alter user %s set password=:pwd' % prep._format_identifier(username) Alternatively, I could simply _always_ quote identifiers: 'alter user %s set password=:pwd' % prep._quote_identifier(username) _That_ method is actually so simple that I could do it myself: 'alter user "%s" set password=:pwd' % username.replace('"', '""') Anyway, maybe you can consider adding the _format_identifier method. -- Chris --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to issue DDL statements?
Michael Bayer wrote: > Christoph Zwerschke wrote: >> Michael Bayer wrote: >>> >>> once you have your connection, you can just execute: >>> connection.execute("alter user foo set password='hoho'") >> >> how can I validate they have no illegal characters and escape >> such chars so they cannot do anything evil on the database (the >> string "hoho" must not contain an apostrophe; the string "foo" >> must not contain a semicolon etc.)... > > you can use text() clauses with bind parameters encoded as > ":somename"...and then feed those into connection.execute() along > with a dict of values. check out the "sql construction" docs for > examples. Ok, thanks. "Text Blocks" were the cue I was missing when I looked into the docs, but now I found it: http://www.sqlalchemy.org/docs/sqlconstruction.myt#sql_textual I think the problem was that the whole chapter has the heading "Constructing SQL *Queries* via Python Expressions" Since an SQL *query* usually only means "select" statements, I had not suspected to find a solution for DDL in that chapter. Maybe you can find a better chapter title here, because in the end of the chapter you also talk about DML (Inserts, Updates, Deletes). Maybe you should also extend that chapter with a subsection about DDL, since text() blocks are only mentioned as a kind of "general select statement", not as a way to formulate arbitrary DDL commands. Or you split the chapter into two chapters, one for Queries, and an extra chapter for DML and DDL. One question is still left. I can use a parameter for the password, like that: db = create_engine(...) alter_pwd = db.text("alter user foo with password :pwd", bindparams=[bindparam('pwd')]) alter_pwd.execute(pwd="secret") But I can't do the same for the user name( "foo"). I can't pass it as a string parameter, because it would be enclosed in single quotes which is the wrong syntax for SQL identifier. Actually, using parameters for SQL identifiers is not supported by the DB API. And I also don't want to insert it directly because that can be dangerous. But maybe there is some SQLAlchemy mechanism that allows to inject a variable SQL identifier into the query? Such a function would need to check whether it is an ordinary SQL identifier (only chars, digits, underscore etc.) or a delimited identifier (can contain other chars). In the latter case, when the identifier has mixed case or contains other chars, then it should be automatically enclosed in double quotes, and quotes themselves need to be escaped (doubled). I suppose there is already something like that somewhere in SQLAlchemy that could be used here. -- Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to issue DDL statements?
Michael Bayer wrote: > you can execute DDL statements as literal text strings straight from an > engine or connection. the Session object has a connect() method on it > which will also use an underlying engine (if the session is bound to > one), or you can pass it a mapper which it can relate to the underlying > engine. > > once you have your connection, you can just execute: > > connection.execute("alter user foo set password='hoho'") Thanks; that works very well. Next question ;-) If "foo" and "hoho" are variable, how can I validate they have no illegal characters and escape such chars so they cannot do anything evil on the database (the string "hoho" must not contain an apostrophe; the string "foo" must not contain a semicolon etc.)... -- Chris --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] How to issue DDL statements?
What's the supposed way to send DDL statements (e.g. "alter user ..." or "truncate ...") using given SQLAlchemy session and metadata objects (e.g. from TurboGears)? -- Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Michael Bayer wrote: > the question is, do you ever want that whitespace in the application > space ? you might want to just create a custom String subclass that > converts the whitespace in and out at the Table level. that would be > cleaner. That's really the best way to do it. I probably should have read the documentation more carefully, it's actually all in there. Kudos again for all of this. Is this the correct implementation? import sqlalchemy.types as types class Name(types.TypeDecorator): """Right trimmed, lowercased Strings.""" impl = types.String def convert_bind_param(self, value, engine): return value.rstrip(' ').lower() def convert_result_value(self, value, engine): return value.rstrip(' ').lower() user = Table('user', metadata, Column('name', Name, primary_key=True), Column('pwd', Name, primary_key=True), ) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Michael Bayer wrote: > the easiest way to have two properties point to the same thing is to > just use a "property" on your class: > > class Foo(object): >def _get_uname(self): > return self.user_name >def _set_uname(self, value): > self.user_name = value >username = property(_get_uname, _set_uname) > > although one of the points of "synonym" was to also allow the name to > be used in select_by() so ill look into restoring it. That would be good. However, I still have a problem here: Usually, you don't want a simple synonym. For instance, in a legacy database I have tables like the following: CREATE TABLE user ( name character(8) NOT NULL, pwd character(8), CONSTRAINT user_pkey PRIMARY KEY (name) ) Whenever I request the name, it is right padded with blanks which do not matter at all, but lead to all sorts of problems. Moreover, the name is also case insensitive. So I'd like to have a property that transparently gets me a trimmed and lowercased version of the name instead of the real name stored in the database. I want to get 'fred' instead of 'Fred', 'FRED' etc. So what I do is the following, as suggested: class User(object): def _get_name(self): return self._name.rstrip(' ').lower() def _set_name(self, value): self._name = value name = property(_get_name, _set_name) mapper(User, user, properties = { '_name': user.c.name, 'name': synonym('_name') } ) The problem is that when I request the user 'fred' with get_by(name='fred'), and it is stored as 'Fred' in the database, it will still not be found, because name is a synonym for _name which is the original column. Any suggestion how to solve this? -- Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Martin Kaffanke schrieb: > Am Dienstag, den 17.10.2006, 14:48 +0200 schrieb Christoph Zwerschke: >> mapper(User, pg_user, properties={ >> 'user_name': pg_user.c.usename, >> 'usename' : synonym('user_name')}) > > Thats the solution. > >> However, this results in the following error: >> >> NameError: global name 'SynonymProperty' is not defined > > Try to do that in a single test condition. Seems to be a problem of the current trunk (rev2001). I'll create a ticket for that. -- Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: retrieving and updating using mappers
[EMAIL PROTECTED] wrote: > Firstly when retrieving information using mappers, I have not been very > successful at all in this If someone could offer a 2 line example of > this problem. > > Retrieving Email.address if User.name == 'jack' > > ... > > I have the same problem with locating a row and replacing one value in > that row. So in the above example if I wanted to change the > Email.address value for user.name == jack . Since I'm also trying to learn SA, I wrote a commented example code that guides you through all necessary steps from creating the tables and the mappers to adding users and addresses. Most of it is already contained in the tutorial. # import everything you need: from sqlalchemy import * # connect to your database: db = create_engine(...) metadata = BoundMetaData(db) # define the tables: user_table = Table('user', metadata, Column('id', Integer, primary_key=True), Column('name', String(40)), Column('password', String(10)), Column('age', Integer)) email_table = Table('mail_address', metadata, Column('id', Integer, primary_key=True), Column('address', String(100), nullable=False), Column('user_id', Integer, ForeignKey('user.id'))) # create the tables: email_table.drop() user_table.drop() user_table.create() email_table.create() # define your Mapper objects: class User(object): def __init__(self, name, age, password): self.name = name self.age = age self.password = password def __str__(self): return self.name class Email(object): def __init__(self, address, user_id=None): self.address = address self.user_id = user_id def __str__(self): return self.address # create the mappings: user_mapper = mapper(User, user_table) email_mapper = mapper(Email, email_table) user_mapper.add_property('addresses', relation(Email)) # obtain a session: session = create_session() # now you can start to play: # create users: user1 = User('Stephen', 42, 'Joshua') user2 = User('Willy', 21, 'free') user3 = User('Jack', 33, 'forgot') # save the users: session.save(user1) session.save(user2) session.save(user3) # create email addresses: user1.addresses.append(Email('[EMAIL PROTECTED]', user1.id)) user1.addresses.append(Email('[EMAIL PROTECTED]', user1.id)) user2.addresses.append(Email('[EMAIL PROTECTED]', user2.id)) user3.addresses.append(Email('[EMAIL PROTECTED]')) user3.addresses.append(Email('[EMAIL PROTECTED]')) # save everything to the database: session.flush() # read user Jack anew from the database: del user3 # forget about Jack user = session.query(User).get_by(name='Jack') # print all email addresses of Jack: print print user, 'is', user.age, 'years old' print 'and has these email addresses:' for adr in user.addresses: print '\t', adr # Jack gets one year older: user.age += 1 # delete Jacl's aol email addresses: user.addresses = [adr for adr in user.addresses if not adr.address.endswith('@aol.com')] # Jack has got a new email address: user.addresses.append(Email('[EMAIL PROTECTED]')) session.flush() # store changes # read user Jack anew from the database: del user user = session.query(User).get_by(name='Jack') # again, print all email addresses of Jack: print print user, 'is now', user.age, 'years old' print 'and has these email addresses:' for adr in user.addresses: print '\t', adr --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Martin Kaffanke wr9te: > This should normally be done, that all other properties are still there. > > Try to make an example script where you have problems, which we can run > if you have troubles here. You're right. What confused me is that when you do mapper(User, pg_user, properties={ 'user_name': pg_user.c.usename}) Then the usename column is missing, all the *other* columns are still there. I had expected that if properties are added, then this would add user_name as an *alias* for usename. This feature probably needs better documentation. Now I start to understand what the synonym function is for: mapper(User, pg_user, properties={ 'user_name': pg_user.c.usename, 'usename' : synonym('user_name')}) However, this results in the following error: NameError: global name 'SynonymProperty' is not defined -- Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] How to add properties?
If I understand it correctly, when I do mapper(User, users_table) then User will have properties corresponding to all columns of the users_table. When I want to add an addresses property, I do: mapper(User, users_table, properties = { 'addresses' : relation(Address)}) But then, all the other columns are not mapped any more. Is it possible to only *add* the 'addresses' property, keeping all the table columns as properties? -- Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to map pg_user and pg_group?
Michael Bayer wrote: > Christoph Zwerschke wrote: >> mapper(User, pg_user, properties={ >> 'user_id': pg_user.c.usesysid, >> 'user_name': pg_user.c.usename, >> 'is_super': pg_user.c.usesuper, >> 'groups': relation(Group, viewonly=True, >> primaryjoin=pg_user.c.usesysid==func.any >> (pg_group.c.grolist))}) >> >> I get this error: >> >> sqlalchemy.exceptions.ArgumentError: On relation 'groups', can't >> figure out which side is the foreign key for join condition >> 'pg_user.usesysid = any(pg_group.grolist)'. Specify the >> 'foreignkey' argument to the relation. >> >> Shouldn't it be clear what the foreign key is in this situation? > > to a human, maybe. to a python interpreter the right side of the > binary '==' expression is just a sqlalchemy.sql.Function, which looks > nothing like the sqlalchemy.schema.Column type which it expects to > locate as a foreign key. But that expression needs to be parsed to SQL anyway, and doing so all involved columns could be tracked. Another clue is that the class for the relation object has been stated as 'Group' which is mapped to pg_group. Why do you actually need a Foreign*Key*, not a Foreign*Table*? > for the "lazy clause" generation, which is when it takes "x=y" and > converts it into "x=?", this is the same issue as the foreign key. > but for this, i have committed in rev 2001 a more thorough search for > a "Column" in each side of the clause so that it can identify which > side of a "=" operation it can apply a bind parameter to, so a test > program can now generate: > > SELECT pg_group.groname AS pg_group_groname, pg_group.grolist AS > pg_group_grolist, pg_group.grosysid AS pg_group_grosysid > FROM pg_catalog.pg_group > WHERE %(lazy_bcba)s = any(pg_group.grolist) ORDER BY pg_group.grosysid Thanks, this works great now! -- Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to map pg_user and pg_group?
Michael Bayer schrieb: > in theory you should be able to do this: > > 'groups':relation(Group, primaryjoin=pg_user.c.usesysid==func.any > (pg_group.c.grolist), viewonly=True) > > notice the "viewonly" flag which is in the trunk only, which will > tell SA not to try persisting that mapping (since it cant)...i would > gather that was the problem you had if you had tried it this way before. Without the "viewonly" flag, I got "No syncrules generated" errors. These have disappeared, but there are still problems here. Again, assume the following setup: pg_user = Table('pg_user', metadata, Column('usesysid', Integer, primary_key=True), Column('usename', String, unique=True), Column('usesuper', Boolean)) pg_group = Table('pg_group', metadata, Column('grosysid', Integer, primary_key=True), Column('groname', String, unique=True), Column('grolist', String)) class User(object): pass class Group(object): pass Now when I map as follows: mapper(User, pg_user, properties={ 'user_id': pg_user.c.usesysid, 'user_name': pg_user.c.usename, 'is_super': pg_user.c.usesuper, 'groups': relation(Group, viewonly=True, primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist))}) I get this error: sqlalchemy.exceptions.ArgumentError: On relation 'groups', can't figure out which side is the foreign key for join condition 'pg_user.usesysid = any(pg_group.grolist)'. Specify the 'foreignkey' argument to the relation. Shouldn't it be clear what the foreign key is in this situation? Now when I explicitly specify the foreign key (as pg_group.grosysid or pg_group.grolist, doesn't matter), mapper(User, pg_user, properties={ 'user_id': pg_user.c.usesysid, 'user_name': pg_user.c.usename, 'is_super': pg_user.c.usesuper, 'groups': relation(Group, viewonly=True, primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist), foreignkey=pg_group.c.grosysid)}) Then the groups property returns all existing groups, not the groups of the corresponding user (the following query is echoed by the engine): SELECT pg_group.grolist, pg_group.grosysid, pg_group.groname FROM pg_user, pg_group WHERE pg_user.usesysid = any(pg_group.grolist) ORDER BY pg_group.grosysid -- Christoph --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] How to map pg_user and pg_group?
I'm trying to tweak the TurboGears identity management to use the PostgreSQL users and groups instead of its own user and group tables. The users/groups must be mapped to a "User"/"Group" class with standard attribute names and additional properties "groups"/"users" to determine the groups of which a user is a member / the members of a group. I have realized it as follows, but the artificial association table pg_groupusers feels a bit awkward. Is there an easier way? pg_user = Table('pg_user', metadata, Column('usesysid', Integer, primary_key=True), Column('usename', String, unique=True), Column('usesuper', Boolean)) pg_group = Table('pg_group', metadata, Column('grosysid', Integer, primary_key=True), Column('groname', String, unique=True), Column('grolist', String)) pg_groupusers = select( [pg_user.c.usesysid, pg_group.c.grosysid], pg_user.c.usesysid==func.any(pg_group.c.grolist) ).alias('pg_groupusers') class User(object): def __init__(self, user_id, user_name, is_super): self.user_id = user_id self.user_name = user_name self.is_super = is_super class Group(object): def __init__(self, group_id, group_name, group_list): self.group_id = group_id self.group_name = group_name self.group_list = group_list mapper(User, pg_user, properties={ 'user_id': pg_user.c.usesysid, 'user_name': pg_user.c.usename, 'is_super': pg_user.c.usesuper, 'groups': relation(Group, secondary=pg_groupusers, primaryjoin=pg_user.c.usesysid==pg_groupusers.c.usesysid, secondaryjoin=pg_group.c.grosysid==pg_groupusers.c.grosysid)}) mapper(Group, pg_group, properties={ 'group_id': pg_group.c.grosysid, 'group_name': pg_group.c.groname, 'users': relation(User, secondary=pg_groupusers, primaryjoin=pg_group.c.grosysid==pg_groupusers.c.grosysid, secondaryjoin=pg_user.c.usesysid==pg_groupusers.c.usesysid)}) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---