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.
[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] 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.
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] 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.
[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: 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] 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: 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] 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: 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
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: 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: 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] 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: 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] 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
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: 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: 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] 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] 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: 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] 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: 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] 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: 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] 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] 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] 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 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] 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
-~--~~~~--~~--~--~---
