[sqlalchemy] Re: reflection unavailable for mysql temporary tables?
On Oct 11, 1:29 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Oct 11, 2008, at 12:49 PM, AndyDavidoffwrote: This fixes the first part of this problem, but unfortunately the `show create table` is performed in the connection, not the session in which the temporary table was created. MySQL doesn't expose temporary tables between sessions, so the `show create table` raises a MySQL exception due to a non-existent table. you can reflect any table on a specific connection using autoload_with=someconnection. if by Session you mean ORM session, get the current connection using session.connection(). Thanks, but MySQL's temporary tables are invisible to connection objects; the reflection would need to occur via queries issued in the actual Session (ORM session) in which the tables were created. I doubt this'll be easy to elegantly hack into SQLA, though. --~--~-~--~~~---~--~~ 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: reflection unavailable for mysql temporary tables?
Andy Davidoff wrote: On Oct 11, 1:29 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Oct 11, 2008, at 12:49 PM, AndyDavidoffwrote: This fixes the first part of this problem, but unfortunately the `show create table` is performed in the connection, not the session in which the temporary table was created. MySQL doesn't expose temporary tables between sessions, so the `show create table` raises a MySQL exception due to a non-existent table. you can reflect any table on a specific connection using autoload_with=someconnection. if by Session you mean ORM session, get the current connection using session.connection(). Thanks, but MySQL's temporary tables are invisible to connection objects; the reflection would need to occur via queries issued in the actual Session (ORM session) in which the tables were created. I doubt this'll be easy to elegantly hack into SQLA, though. No hacking needed, it works just as Mike described. from sqlalchemy import * from sqlalchemy.orm import create_session session = create_session() session.bind = create_engine('mysql:///test') session.begin() session.execute('CREATE TEMPORARY TABLE foo (x INT)') session.execute('INSERT INTO foo VALUES (1)') m = MetaData() tt = Table('foo', m, autoload=True, autoload_with=session.connection()) print session.execute(tt.select()).fetchall() session.commit() --~--~-~--~~~---~--~~ 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: reflection unavailable for mysql temporary tables?
On Oct 13, 2008, at 12:29 PM, Andy Davidoff wrote: On Oct 11, 1:29 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Oct 11, 2008, at 12:49 PM, AndyDavidoffwrote: This fixes the first part of this problem, but unfortunately the `show create table` is performed in the connection, not the session in which the temporary table was created. MySQL doesn't expose temporary tables between sessions, so the `show create table` raises a MySQL exception due to a non-existent table. you can reflect any table on a specific connection using autoload_with=someconnection. if by Session you mean ORM session, get the current connection using session.connection(). Thanks, but MySQL's temporary tables are invisible to connection objects; the reflection would need to occur via queries issued in the actual Session (ORM session) in which the tables were created. I doubt this'll be easy to elegantly hack into SQLA, though. I mean this: sess = sessionmaker(autocommit=False) sess.execute(do something that creates a temporary table) my_temporary_table = Table(my_temporary_table, MetaData(), autoload=True, autoload_with=sess.connection()) i.e., use the Connection object referenced by the Session. as long as you are autocommit=False it will persist until you say commit() or rollback(). --~--~-~--~~~---~--~~ 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: reflection unavailable for mysql temporary tables?
Andy Davidoff wrote: Reflection of temporary tables under MySQL works around revision 4000 and probably as recently as May but doesn't seem to work under revision 5000 or later; I get a NoSuchTableError. Is this change intentional? If the only change I make to my code is to create and reflect a normal table versus a temporary table, then SQLA works as expected. Unfortunately, temporary tables offer me an easy concurrency crutch, so I deeply regret this missing functionality. I could dig deeper into when and where this stopped working, but perhaps I'm the one missing something. What is it? :-) Give it a try with the trunk, r5129. -j --~--~-~--~~~---~--~~ 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: reflection unavailable for mysql temporary tables?
Perfect, thank you! On Sep 27, 2:28 pm, jason kirtland [EMAIL PROTECTED] wrote: Andy Davidoff wrote: Reflection of temporary tables under MySQL works around revision 4000 and probably as recently as May but doesn't seem to work under revision 5000 or later; I get a NoSuchTableError. Is this change intentional? If the only change I make to my code is to create and reflect a normal table versus a temporary table, then SQLA works as expected. Unfortunately, temporary tables offer me an easy concurrency crutch, so I deeply regret this missing functionality. I could dig deeper into when and where this stopped working, but perhaps I'm the one missing something. What is it? :-) Give it a try with the trunk, r5129. -j --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---