[sqlalchemy] Re: Ascii codec instead of unicode ?
Whooops. the problem in coming on the box which is running mysqldb1.2.0. Actually it's a non-upgraded server with mysqldb 1.2.0. I was looking at the code on my box which contains 1.2.2. But I am sure that even with mysqldb 1.2.0 the existing SA version was working fine. and that's why init_command is being used there as charset is not there in 1.2.0 as connect kwargs. Any clue? On 8/16/07, jason kirtland [EMAIL PROTECTED] wrote: Ok you need to get tArun wrote: I am using mysqldb-1.2.2. I am passing 'SET NAMES' to connect method as a value for init_command parameter. All tables have utf8 charset. And I pass convert_unicode=True to engine. Let me know if anything else is required. Ok, you need to get that charset to the driver. Try removing SET NAMES from your init_command, and instead pass charset=utf8 and use_unicode=0 in your database connection URL. -- Cheers, - A --~--~-~--~~~---~--~~ 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: mapping a joined table and ForeignKey functionality for session.save()
On Aug 16, 2007, at 12:23 AM, Boris Duek wrote: Hi, I am using sqlalchemy like this: class Entry: pass table1 = sqa.Table('table1', meta, autoload=True) # this table has primary key 'id' table2 = sqa.Table('table2', meta, sqa.Column('table1_id', sqa.Integer, sqa.ForeignKey('table1.id'), autoload=True) table1 = table1.join(table2) # gets joined on the ForeignKey, a.k.a. table2.table1_id == table1.id sqa_orm.mapper(Entry, table1) session = sqa_orm.create_session() entry = Entry() entry.a = 1 ... # working with entry, setting values for all columns except id and table1_id session.save(entry) session.flush() But now, the entry in table2 gets stored, but with NULL value in table1_id column. I would expect sqlalchemy to be the same smart as it is already with the join, and set table2.table1_id to the corresponding table1.id, because it knows about the foreign key. if you were using joined table inheritance with two mappers, it would figure this out. but when mapping to just an arbitrary selectable, it doesnt make any assumptions. in this particular case, you have to give it an explicit hint that the two columns are the same: j = table1.join(table2) mapper(Entry, j, properties={ 'id':[table1.c.id, table2.c.table1_id] }) described at http://www.sqlalchemy.org/docs/04/ mappers.html#advdatamapping_mapper_joins --~--~-~--~~~---~--~~ 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: sqlite unicode/datetime issue in 0.4
Is there a different way to do this that's compatible with both versions, or is there a new 0.4 way of doing this, or have I just been doing something wrong all along and only 0.4 is catching it? DateTime objects in sqlite currently expect a datetime.datetime object, and not a string. previous versions accepted both. a lot of people are getting this issue so i think im going to see what psycopg2 and mysqldb do, and emulate them. historically ive been against hardcoding a particular string format. What's odd here is that I'm not setting the value myself -- it's getting set in pre_exec by the column default, which (I believe) is running the func in sqlite and returning the results, which I'd think should wind up with a datetime.datetime. I'll try to put together a minimal test case that shows what's happening. JP --~--~-~--~~~---~--~~ 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: sqlite unicode/datetime issue in 0.4
On Aug 16, 10:06 am, JP [EMAIL PROTECTED] wrote: What's odd here is that I'm not setting the value myself -- it's getting set in pre_exec by the column default, which (I believe) is running the func in sqlite and returning the results, which I'd think should wind up with a datetime.datetime. I'll try to put together a minimal test case that shows what's happening. right...youd have to say default=func.current_timestamp(type_=DateTime) for now. so yeah accepting the date format that sqlite actually produces is probably reasonable here. --~--~-~--~~~---~--~~ 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: sqlite unicode/datetime issue in 0.4
ok thats in r3322 --~--~-~--~~~---~--~~ 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: sqlite unicode/datetime issue in 0.4
Yup, that fixed things. Thanks! On Aug 16, 10:49 am, Michael Bayer [EMAIL PROTECTED] wrote: ok thats in r3322 --~--~-~--~~~---~--~~ 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: ondelete cascade with turbogears/SA
just to make sure I'm understanding this: even though mysql is configured to cascade deletes for this relationship, SA needs to be told about it so that it doesn't prevent it from cascading by first nulling the foreign key? I tried this: class Account(ActiveMapper): class mapping: __table__ = account id = column(Integer, primary_key=True) account_name= column(Unicode(50), unique=True) number = column(Unicode(50), unique=True) users = one_to_many('User', passive_deletes=True, cascade='delete') but got this: TypeError: __init__() got an unexpected keyword argument 'passive_deletes' I think the problem is the TG layer is getting in the way. I may need to use SA directly here to do this. --~--~-~--~~~---~--~~ 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 with turbogears and mapper
Hello, I am setting up a website with existing database using turbogears. Database is in mysql. I am using sqlalchemy but I'm having problems with choosing the mapper? There is around 20 tables with a lot of foreign key, compound keys and alternative keys. Which mapper do I use? Can you point me to the documentation for that as well? Thanks, Lucas --~--~-~--~~~---~--~~ 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: ondelete cascade with turbogears/SA
On Aug 16, 11:48 am, James [EMAIL PROTECTED] wrote: just to make sure I'm understanding this: even though mysql is configured to cascade deletes for this relationship, SA needs to be told about it so that it doesn't prevent it from cascading by first nulling the foreign key? yes more or less...whatever is currently loaded in the session, its still going to process explicitly regardless of CASCADE rules in your database. so the cascade=all, delete makes sure those two sides are in agreement with what needs to be done. I think the problem is the TG layer is getting in the way. I may need to use SA directly here to do this. yes ActiveMapper probably does not support that argument. Elixir is a better way to go if you want the declarative pattern. --~--~-~--~~~---~--~~ 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: ondelete cascade with turbogears/SA
I've seen some of the Elixir info and thought it was too new to start using just yet. How hard is it to transition from ActiveMapper to Elixir, given a moderately sized model and a moderate amount of access by controllers? If I go with direct access to SA API in model, where do the cascade='delete' and passive_deletes=True items belong? thanks, James --~--~-~--~~~---~--~~ 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] query.select vs .filter + .from_statement
i have a premade filtering clause and give it to a query.select at runtime. Sometimes its a simple x == 13 expression, another time it is a full sql-construct like polymorphic_union(). in 0.3 all went into .select(), but in 0.4 these 2 kinds seems split between .from_statement and .filter. so i have either to check at runtime which one to call, or make the simple expression into sql construct. why filter() does not accept everything as before? IMO the types are distinct enough to check/switch... or, let filter() be, why not have another single method that accepts everything? All that is a filter anyway --~--~-~--~~~---~--~~ 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: query.select vs .filter + .from_statement
because we are going for more explicitness and less magic. when you start having methods that accept five different things, then people get confused which methods to use for what, it gets harder to check for correct arguments, etc. also, select_from() and from_statement() are not really filters. particularly from_statment(), which currently has no interaction with filter() ( i know you want a feature where you do, and we may add that eventually though its a little involved. id like it too since i hate non_primary mappers). --~--~-~--~~~---~--~~ 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: sqlalchemy with turbogears and mapper
hello - you generally use mapper() and relation() to set up how you'd like your classes to correspond to your table relationships. as far as compound keys, if they are defined with a primary key constraint you shouldn't have to worry about them. if you need to tell a mapper about some other columns that compose its primary key other than what the actual table has set up as its primary key, you can use the primary_key keyword option on mapper(), such as mapper(SomeClass, sometable, primary_key=[mytable.c.col1]) As for foreign key relations, these are usually represented at the ORM level using the relation() function. the easiest ones are the one-to-many, many-to-one, and many-to-many relationships, which most of your foreign keys are probably defining. for 0.3 these are described here: http://www.sqlalchemy.org/docs/03/datamapping.html#datamapping_relations_onetomany http://www.sqlalchemy.org/docs/03/datamapping.html#datamapping_morerelations a more complex relationship is a self-referential relationship, otherwise known as adjacency list. use this when a single table contains a foreign key relationship pointing to itself: http://www.sqlalchemy.org/docs/03/adv_datamapping.html#advdatamapping_selfreferential finally, the other common mapping is inheritance, usually joined table inheritance, where a set of rows are referenced by a base table, but each row also contains additional properties that are added by one of several joined tables, but only one of those tables for each parent row: http://www.sqlalchemy.org/docs/03/adv_datamapping.html#advdatamapping_inheritance_joined good luck ! - mike On Aug 16, 11:59 am, Lukasz Szybalski [EMAIL PROTECTED] wrote: Hello, I am setting up a website with existing database using turbogears. Database is in mysql. I am using sqlalchemy but I'm having problems with choosing the mapper? There is around 20 tables with a lot of foreign key, compound keys and alternative keys. Which mapper do I use? Can you point me to the documentation for that as well? Thanks, Lucas --~--~-~--~~~---~--~~ 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] empty unicode strings are not encoded
Hi, I have a strange behavior (well, a blocking issue) on both sqlite and mssql with unicode strings. It inserting a empty unicode string, it's not encoded before being sent, while a non-empty string get encoded correctly. With sqlite it's not a problem, but with mssql it leads to a Invalid data type (0) The following program illustrate it : from sqlalchemy import * e = create_engine(sqlite:///, echo=True) m = MetaData(e) t = Table(test, m, Column('test', String(convert_unicode=True))) t.create() m.bind.execute(t.insert(), dict(test=u'hello')) m.bind.execute(t.insert(), dict(test=u'')) Running it output this : 2007-08-16 19:21:22,624 INFO sqlalchemy.engine.base.Engine.0x..34 CREATE TABLE test ( test TEXT ) 2007-08-16 19:21:22,625 INFO sqlalchemy.engine.base.Engine.0x..34 None 2007-08-16 19:21:22,626 INFO sqlalchemy.engine.base.Engine.0x..34 COMMIT 2007-08-16 19:21:22,628 INFO sqlalchemy.engine.base.Engine.0x..34 INSERT INTO test (test) VALUES (?) 2007-08-16 19:21:22,629 INFO sqlalchemy.engine.base.Engine.0x..34 ['hello'] 2007-08-16 19:21:22,629 INFO sqlalchemy.engine.base.Engine.0x..34 COMMIT 2007-08-16 19:21:22,631 INFO sqlalchemy.engine.base.Engine.0x..34 INSERT INTO test (test) VALUES (?) 2007-08-16 19:21:22,632 INFO sqlalchemy.engine.base.Engine.0x..34 [u''] 2007-08-16 19:21:22,632 INFO sqlalchemy.engine.base.Engine.0x..34 COMMIT I had a quick look at the code and I cannot see anything leading to this behavior. Any idea ? Regards, Christophe --~--~-~--~~~---~--~~ 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: empty unicode strings are not encoded
I forgot to mention that I'm using the latest svn trunk version, and that yesterday I was not having the problem, and although I did some upgrade of my system, I don't think they are related (yet?). 2007/8/16, Christophe de VIENNE [EMAIL PROTECTED]: Hi, I have a strange behavior (well, a blocking issue) on both sqlite and mssql with unicode strings. It inserting a empty unicode string, it's not encoded before being sent, while a non-empty string get encoded correctly. [...] --~--~-~--~~~---~--~~ 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: empty unicode strings are not encoded
2007/8/16, Michael Bayer [EMAIL PROTECTED]: ok thats fixed in r3334. That was fast ! Thanks a lot. pushes my nice speed test back over the 1M function call mark :( Sorry about that... ;-) Cheers Christophe --~--~-~--~~~---~--~~ 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: sqlalchemy 0.4 beta3 released
0.4 has been in the trunk for a couple of weeks now (it was announced here and is mentioned on the site). so thats why the 0.4 branch hasnt changed. 0.3 is in /sqlalchemy/branches/rel_0_3. On Aug 16, 2007, at 4:42 PM, Boris Duek wrote: Any idea when approximately can we expect the rel_0_4 branch be used for 0.4 and trunk for 0.5 or 0.6 (and when 0.4 will have RCs, i.e. considered done)? I would like to switch in my svn to the rel_0_4 branch, but that got last update sometime 3 weeks ago (and is not release-ready at the moment anyway, I guess). From the number of betas released recently, I hope it might be soon :-). Thanks for the answer, Boris On Aug 16, 8:38 am, Michael Bayer [EMAIL PROTECTED] wrote: I've put up beta3 with the latest round of tweaks. most important in we're getting there ! --~--~-~--~~~---~--~~ 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] Does the SQL construction language support 'INSERT INTO' from a SELECT?
I have an insert query in SQL where the records to be inserted are computed from a SELECT. Can I build this query in the SqlAlchemy query construction language? INSERT INTO product_current_promotion (promotion_id, product_id) SELECT promo_promotion.id, promo_promotion_items.product_id FROM promo_promotion_items, promo_promotion WHERE promo_promotion.id = promo_promotion_items.promotion_id AND promo_promotion.week_id = 3 One thought is to build the SELECT in SqlAlchemy, loop over that in Python and insert the records that way, but I'd like to make the database do all the work. Barry Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=listsid=396545433 --~--~-~--~~~---~--~~ 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] Joining multiple tables
Hi, I'm reading the documentation on SQL Alchemy, but perhaps I'm missing something here ... I need to join multiple tables, table1 with table2 and table1 again. So, in SQL, I'd do something like: select * from t1 join t2 on t1.b = t2.c join t1 t3 on t1.a = t3.a; Yeah, it doesn't make much sense, but it's an example. Any ideas on how I'd do that in SA? I'm having trouble figuring it out. :) --~--~-~--~~~---~--~~ 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: Does the SQL construction language support 'INSERT INTO' from a SELECT?
we've never added this particular construct since its usually used in migration scripts that are written as hand-coded SQL. ticket #722 was recently added for this feature add. for now, you can enter it using text() and theres also a workaround mentioned in that ticket. --~--~-~--~~~---~--~~ 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: Joining multiple tables
hi Vetle - the Table object supports a join() method, which can be called in a chain, i.e. table1.join(table2).join(table3)... if you need to specify the ON part of the join, its the second argument to join() (below illustrated with two joins back to table1): table1.join(table2, table1.c.id==table2.c.someid).join(table3, table1.c.id==table3.c.id) hope this helps... --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---