[sqlalchemy] Using TWO PHASE commit with SQLite database? NotImplementedError?
Hi there, I have seen some basic examples online showing that SQLAlchemy supports twophase = True for some databases. My question is I am needing to use this sort of functionality with 2 SQLite databases, does SQLAlchemy support SQLite two phase commits? So here is some basic code engine_1 = create_engine('sqlite:///queue_1.db') engine_2 = create_engine('sqlite:///queue_2.db') metadata_1 = MetaData(engine_1) queue_one = Table('Queue_1', metadata_1, autoload=True) mapper(Queue_1, queue_one) metadata_2 = MetaData(engine_2) queue_two = Table('Queue_2', metadata_2, autoload=True) mapper(Queue_2, queue_two) Session = sessionmaker(twophase=True) Session.configure(binds={Queue_1:engine_1, Queue_2:engine_2}) session = Session() #Work with the session... session.commit() Also, can someone please let me know how I can work with the session properly here when using a two phase commit using SQLite? If I run the following: message = session.query(Queue_1).filter(queue_one.c.ID == 1234).first() The above line works fine without a two phase commit, why when I run it now does it raise a NotImplementedError in base.py? def do_begin_twophase(self, connection, xid): Begin a two phase transaction on the given connection. raise NotImplementedError() Any help will be greatly appreciated ;-) Kind regards Lynton -- 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.
Re: [sqlalchemy] flush(), save(), delete()
King Simon-NFHD78 wrote: Jo wrote: [SNIP] and- In [13]: aa=Anagrafica.get(111) In [14]: aa.delete() In [15]: aa.flush() - but in version 0.6 I can't find flush(), save(), delete(). Where are them? thank you j These methods were added to your objects by the old assign_mapper extension. This extension no longer exists, and the methods on the Session should be used instead. For example, instead of aa.delete(), you would say session.delete(aa). If you want to preserve your old API, you could create a base class for your mapped objects that implements each of the old methods. A delete method might look like this (untested): class Base(object): def _get_session(self): return sqlalchemy.orm.object_session(self) def delete(self): session = self._get_session() session.delete(self) The flush method would correspond to session.flush([self]), but you should read the deprecation warning about passing a list of objects at http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.or m.session.Session.flush. Assuming that the save() method adds the object to the current contextual (scoped) session, it would be as simple as: def save(self): session = Session() session.add(self) However, I personally wouldn't add that one, as it ties your class to the scoped session mechanism which may not always be what you want. Hope that helps, Simon thank you for your help, Simon. j -- Jose Soares Sferacarta Net Via Bazzanese 69 40033 Casalecchio di Reno Bologna - Italy Ph +39051591054 fax +390516131537 web:www.sferacarta.com Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie. This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you. -- 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.
Re: [sqlalchemy] flush() again
Problems with flush() again... I thougth I could avoid the use of flush() by nesting the mappers, but... I have two mappers: mapper(CatalogazioneModulistica, tbl['catalogazione_modulistica'], column_prefix = 'catalogazione_modulistica_', ) and: mapper(GestioneDocFile, tbl['gestione_doc_file'], column_prefix = 'gestione_doc_file_', properties = dict(catalogazione = relation(CatalogazioneModulistica), ) fk = GestioneDocFile.numero_catalogo_catalogazione REFERENCES catalogazioneModulistica.numero_catalogo I tried an INSERT in this way: GestioneDocFile( catalogazione = CatalogazioneModulistica( catalogazione_modulistica_numero_catalogo = 1, catalogazione_modulistica_nome = 'pippo', catalogazione_modulistica_cod_classe_titolario='VETB', catalogazione_modulistica_cod_tipologia = 'VB' ), gestione_doc_file_nomefile='nome', gestione_doc_file_id_operatore=1, gestione_doc_file_tipo_documento='M') but... SQLError: (IntegrityError) insert or update on table "gestione_doc_file" violates foreign key constraint "gestione_doc_file_numero_catalogo_catalogazione_fkey" DETAIL: Key (numero_catalogo_catalogazione)=(1) is not present in table "catalogazione_modulistica". 'INSERT INTO gestione_doc_file (id, nomefile, nome_visualizzato, descrizione, protocollo, data_protocollo, tipo_file, id_operatore, numero_catalogo_catalogazione, tipo_documento, nome_tabella_proven, chiave_tabella_proven, id_unita_aziendale, cod_argomento, password) VALUES (%(id)s, %(nomefile)s, %(nome_visualizzato)s, %(descrizione)s, %(protocollo)s, %(data_protocollo)s, %(tipo_file)s, %(id_operatore)s, %(numero_catalogo_catalogazione)s, %(tipo_documento)s, %(nome_tabella_proven)s, %(chiave_tabella_proven)s, %(id_unita_aziendale)s, %(cod_argomento)s, %(password)s)' {'password': None, 'chiave_tabella_proven': None, 'nome_tabella_proven': None, 'cod_argomento': None, 'nomefile': 'nome', 'descrizione': None, 'id': 85L, 'id_operatore': 1, 'numero_catalogo_catalogazione': 1, 'id_unita_aziendale': None, 'nome_visualizzato': None, 'data_protocollo': None, 'protocollo': None, 'tipo_file': None, 'tipo_documento': 'M'} How can I do this operation without insert before CatalogazioneModulistica and flushing it and then insert GestioneDocFile assigning CatalogazioneModulistica.numero_catalogo to GestioneDocFile.numero_catalogo_catalogazione ? j jo wrote: King Simon-NFHD78 wrote: Jo wrote: [SNIP] and- In [13]: aa=Anagrafica.get(111) In [14]: aa.delete() In [15]: aa.flush() - but in version 0.6 I can't find flush(), save(), delete(). Where are them? thank you j These methods were added to your objects by the old assign_mapper extension. This extension no longer exists, and the methods on the Session should be used instead. For example, instead of aa.delete(), you would say session.delete(aa). If you want to preserve your old API, you could create a base class for your mapped objects that implements each of the old methods. A delete method might look like this (untested): class Base(object): def _get_session(self): return sqlalchemy.orm.object_session(self) def delete(self): session = self._get_session() session.delete(self) The flush method would correspond to session.flush([self]), but you should read the deprecation warning about passing a list of objects at http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.or m.session.Session.flush. Assuming that the save() method adds the object to the current contextual (scoped) session, it would be as simple as: def save(self): session = Session() session.add(self) However, I personally wouldn't add that one, as it ties your class to the scoped session mechanism which may not always be what you want. Hope that helps, Simon thank you for your help, Simon. j -- Jose Soares Sferacarta Net Via Bazzanese 69 40033 Casalecchio di Reno Bologna - Italy Ph +39051591054 fax +390516131537 web:www.sferacarta.com Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la
Re: [sqlalchemy] Using TWO PHASE commit with SQLite database? NotImplementedError?
Lynton Grice wrote: Hi there, I have seen some basic examples online showing that SQLAlchemy supports twophase = True for some databases. My question is I am needing to use this sort of functionality with 2 SQLite databases, does SQLAlchemy support SQLite two phase commits? SQLite does not support two phase commit. -- 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: Using TWO PHASE commit with SQLite database? NotImplementedError?
Hi Michael, Thanks for the feedback, much appreciated. Seems I had to do some fancy foodwork using the Python SQLite3 library.and using the ATTACH DATABASEseems to work of for what I need Thanks conn = sqlite3.connect('db_1.db') c = conn.cursor() result = c.execute(ATTACH DATABASE 'db_2.db' AS 'DB_2';) error = False result = c.execute(DELETE FROM DB_1 WHERE IDX = 3;) result = c.execute(DELETE FROM DB_2 WHERE IDX = 1;) #Check for errors #conn.rollback() conn.commit() On Apr 22, 5:02 pm, Michael Bayer mike...@zzzcomputing.com wrote: Lynton Grice wrote: Hi there, I have seen some basic examples online showing that SQLAlchemy supports twophase = True for some databases. My question is I am needing to use this sort of functionality with 2 SQLite databases, does SQLAlchemy support SQLite two phase commits? SQLite does not support two phase commit. -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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] Mysql dialects comparison
Hi, everybody! Looks like mysql_python is getting some competition lately. Not that there's a lot to complain about it, but having options never heart anybody. I'd be nice to get a grasp of what's the experience people had with official connector and the our_sql (pure python sounds like not a good way to go when we're talking speed, even SA is going C extentions). If some of you guys have made any test/comparison between them, I'd be just great. Speed of course being the main factor here. Wiil appreciate any feedback, especially from Mike, since he's the one to be dealing with them first hand. (Congrats on the new release by the way! Moving to 0.6 shortly), -- 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.