[sqlalchemy] Re: Missing UPDATE on Parent
I found that setting: def scan(self): if self.isAlive(): self.scantime = datetime.now() Explicit call to update the record It works and update the record field. --~--~-~--~~~---~--~~ 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: BUG: autoload for DateTime in SQL Server 2000 fails
Ah, serves me right for not checking for the latest version first. Thanks! On Feb 9, 8:00 pm, Michael Trier mtr...@gmail.com wrote: On Mon, Feb 9, 2009 at 7:56 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Feb 9, 2009, at 5:41 PM, Eric R. Palakovich Carr wrote: Is this a real bug or am I doing something wrong? there is ! you're reporting a bug against an 0.5 version prior to the current 0.5 release (I know this since you're referencing a line of code only present in prior versions of 0.5). this has been fixed. Yeah, I just double checked with your test case and it works in the recent version. -- Michael Trierhttp://blog.michaeltrier.com/http://thisweekindjango.com/ --~--~-~--~~~---~--~~ 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] join or filter, i could't join this two queries!
join or filter, i could't join this two queries! mevcutDonem = session.query( Donem ).filter( Donem.donem_id == func.max( Donem.donem_id ).select() ).one() konuBirimleriIds = session.query ( DonemBirimKonuMaxSayi.birim_id ).filter( and_ (DonemBirimKonuMaxSayi.konu_id == evrak.sayi.konu_id, DonemBirimKonuMaxSayi.donem_id == mevcutDonem.donem_id ) ) konuBirimIds = [] for konuBirim in konuBirimleriIds: konuBirimIds.append( konuBirim.birim_id ) konuBirimleri= session.query( Birim ).filter( and_ (Birim.parent_birim_id == 1, Birim.birim_id.in_( konuBirimIds )) ) i could't join konuBirimleriIds and konuBirimleri so i used Birim.birim_id.in_ , how can i do it without in_ --~--~-~--~~~---~--~~ 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: join or filter, i could't join this two queries!
call subquery() on konuBirimleriIds and use the .c. attribute on the resulting selectable to locate columns with which to join from. technique is identical to the example here: http://www.sqlalchemy.org/docs/05/ormtutorial.html#using-subqueries ibrahim.z.hi...@gmail.com wrote: join or filter, i could't join this two queries! mevcutDonem = session.query( Donem ).filter( Donem.donem_id == func.max( Donem.donem_id ).select() ).one() konuBirimleriIds = session.query ( DonemBirimKonuMaxSayi.birim_id ).filter( and_ (DonemBirimKonuMaxSayi.konu_id == evrak.sayi.konu_id, DonemBirimKonuMaxSayi.donem_id == mevcutDonem.donem_id ) ) konuBirimIds = [] for konuBirim in konuBirimleriIds: konuBirimIds.append( konuBirim.birim_id ) konuBirimleri= session.query( Birim ).filter( and_ (Birim.parent_birim_id == 1, Birim.birim_id.in_( konuBirimIds )) ) i could't join konuBirimleriIds and konuBirimleri so i used Birim.birim_id.in_ , how can i do it without in_ --~--~-~--~~~---~--~~ 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: join or filter, i could't join this two queries!
konuBirimleriIds = session.query ( DonemBirimKonuMaxSayi.birim_id ).filter( and_ (DonemBirimKonuMaxSayi.konu_id == evrak.sayi.konu_id, DonemBirimKonuMaxSayi.donem_id == mevcutDonem.donem_id ) ).subquery() konuBirimleri= session.query( Birim ).filter( and_ (Birim.parent_birim_id == 1, Birim.birim_id == konuBirimleriIds.c.birim_id) ) Thanks --~--~-~--~~~---~--~~ 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] Determining postgres sequence name for a table
Hi there, I'm accessing a postgres database and would like to use the session.execute(Sequence('sequence_name')) syntax to get the next id for a table without actually saving the table. This all works fine if I know the sequence_name, but I was hoping to write generic code. Is it possible, using sqlalchemy, to discover the name of the sequence on a particular table. Basically, I'm looking for the equivalent of the postgres expression select pg_get_serial_sequence('table_name', 'column_name'); The best I've come up with so far is pretty ugly!: rs = engine.execute(select nextval(pg_get_serial_sequence(' + self.orm_table._sa_class_manager.mapper.local_table.name + ', 'id'))) new_id = rs.fetchone()['nextval'] Is there a more alchemized way of getting the next value, or barring that, is there a way I can get the table from the orm object a bit more elegantly? Thanks, Dusty --~--~-~--~~~---~--~~ 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: Determining postgres sequence name for a table
table reflection does get a value for sequence defaults in PG, and SQLA then knows how to execute the sequence. there is an issue specifically when the sequence name has been changed in that PG no longer provides consistent access to the sequence name (theres a trac ticket for that issue), but it works for the typical use case. Dusty Phillips wrote: Hi there, I'm accessing a postgres database and would like to use the session.execute(Sequence('sequence_name')) syntax to get the next id for a table without actually saving the table. This all works fine if I know the sequence_name, but I was hoping to write generic code. Is it possible, using sqlalchemy, to discover the name of the sequence on a particular table. Basically, I'm looking for the equivalent of the postgres expression select pg_get_serial_sequence('table_name', 'column_name'); The best I've come up with so far is pretty ugly!: rs = engine.execute(select nextval(pg_get_serial_sequence(' + self.orm_table._sa_class_manager.mapper.local_table.name + ', 'id'))) new_id = rs.fetchone()['nextval'] Is there a more alchemized way of getting the next value, or barring that, is there a way I can get the table from the orm object a bit more elegantly? Thanks, Dusty --~--~-~--~~~---~--~~ 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] altering tables
Let's say I create a table with metadata using something like the following: metadata = MetaData() self.address_table = Table('address', metadata, Column('id', Integer, primary_key=True), Column('email', String (255),unique=True)) let us then suppose that I use this table in a MySQL DB for some time, accumulating rows. At some time later, I want to add a new column to that Table, so I modify the above code to look like: self.address_table = Table('address', metadata, Column('id', Integer, primary_key=True), Column('email', String (255),unique=True), Column('name',String(255))) I'd like to update the database (do the equivalent of an 'ALTER TABLE' SQL command) without destroying all of the data I already have. How do I do this? I have already tried using: metadata.create_all(engine,checkfirst=True) which can handle _new_ tables being added, it seems, but doesn't seem to update the tables for which I have altered the definitions using the Table object. I'm using SQLAlchemy '0.5.0beta3', with Python 2.5 on Mac OS X. Thanks, Mike --~--~-~--~~~---~--~~ 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] resultset print keys values
Hello, Could somebody tell me how can I print the object data in my result set without knowing the column names? myresult=session.query(...).all() for i in myresult: print I need to debug some data and its hard to print the object keys and values (column names and its values) . i.keys() ? i.items()? some dictionary like functions would be nice. sqlalchemy.__version__ '0.5.0rc1' Thanks, Lucas -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar --~--~-~--~~~---~--~~ 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: resultset print keys values
object-query or plain query? - objects are .. whatever class it is; print the i.__dict__ or str(i) or whatever - plain-sql-query ones are RowProxy, they have i.keys() i.items() i.values() On Tuesday 10 February 2009 21:27:09 Lukasz Szybalski wrote: Hello, Could somebody tell me how can I print the object data in my result set without knowing the column names? myresult=session.query(...).all() for i in myresult: print I need to debug some data and its hard to print the object keys and values (column names and its values) . i.keys() ? i.items()? some dictionary like functions would be nice. --~--~-~--~~~---~--~~ 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: resultset print keys values
On Tue, Feb 10, 2009 at 1:32 PM, a...@svilendobrev.com wrote: object-query or plain query? - objects are .. whatever class it is; print the i.__dict__ or str(i) or whatever - plain-sql-query ones are RowProxy, they have i.keys() i.items() i.values() i.__dict__ it is... Thanks a lot... Lucas On Tuesday 10 February 2009 21:27:09 Lukasz Szybalski wrote: Hello, Could somebody tell me how can I print the object data in my result set without knowing the column names? myresult=session.query(...).all() for i in myresult: print I need to debug some data and its hard to print the object keys and values (column names and its values) . i.keys() ? i.items()? some dictionary like functions would be nice. -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar --~--~-~--~~~---~--~~ 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: resultset print keys values
dir(instance) is preferable to __dict__.keys() - the latter will not give you deferred attributes, unloaded collections, or the expired version of each of those. dir() respects descriptors basically. Lukasz Szybalski wrote: Hello, Could somebody tell me how can I print the object data in my result set without knowing the column names? myresult=session.query(...).all() for i in myresult: print I need to debug some data and its hard to print the object keys and values (column names and its values) . i.keys() ? i.items()? some dictionary like functions would be nice. sqlalchemy.__version__ '0.5.0rc1' Thanks, Lucas -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar --~--~-~--~~~---~--~~ 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: resultset print keys values
On Tue, Feb 10, 2009 at 1:52 PM, Michael Bayer mike...@zzzcomputing.com wrote: dir(instance) is preferable to __dict__.keys() - the latter will not give you deferred attributes, unloaded collections, or the expired version of each of those. dir() respects descriptors basically. but then dir() includes stuff like: '__class__', '__delattr__', '__dict__', '__doc__', '__getattribute__', '__hash__', '__init__', '__module__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__str__', '__weakref__', '_sa_class_manager', '_sa_instance_state'] Which need to be filtered. Then for the remaining items need to loop through to get the value?! I figured there was a uniform function that would return dictionary of key/value pairs that is available on all the possible return objects. __dict__ is good enough for visual inspection for now. Thanks, Lucas Lukasz Szybalski wrote: Hello, Could somebody tell me how can I print the object data in my result set without knowing the column names? myresult=session.query(...).all() for i in myresult: print I need to debug some data and its hard to print the object keys and values (column names and its values) . i.keys() ? i.items()? some dictionary like functions would be nice. sqlalchemy.__version__ '0.5.0rc1' Thanks, Lucas -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar --~--~-~--~~~---~--~~ 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] referencing table in other databases inside a MSSQL server
Hi all! I have a MSSQL SERVER I want to use sqlalchemy on, because there are some old databases in there and legacy applications and we can't migrate the data for now. (although a migration is planned and SQLAlchemy is helping a lot) The server has two Databases (table collections) lets call them DataA and DataB. Both are on the same MSSQL SERVER. If i create a MSSQL engine like this: import sqlalchemy as sa e = sa.create_engine('mssql://myuser:sec...@myserver/DataA', echo=True) e.execute('SELECT * FROM TableInA') The engine points to the 'DataA' database, so that works. I get a result object with all rows from TableInA; But I still can access/join with a table in DataB by running: e.execute('SELECT * FROM DataB.dbo.TableInB') That works too, so I get rows from TableInB which is in DataB, a different database from the one I used when creating the engine object. I can even JOIN tables from DataB with tables in DataA, no problem. Question is: How can I do that in a sa.Table constructor? I have tried: sa.Table('DataB.dbo.TableInB', metadata, ) sa.Table('TableInB', metadata, , schema='DataB.dbo') And both failed. Is there a way to map tables from both databases using the same engine? So I can do a JOIN between the tables? Thanks in advance nosklo --~--~-~--~~~---~--~~ 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] Unable to model cross relations (using ext.declarative)
Hi, this is probably extremely easy but I've only recently started using SQLAlchemy and I simply cannot manage to model the following relations: +--+ ++ | NodeRevision | | Node | +--+ 1 ++ | vid |--- || | |\ 1 || | | 1..* --\--| nid| | nid |-/ \ || | | \ 1 || +--+-| vid| ++ The diagram depicts the following relations: * A Node has a latest_revision (Node.vid -- NodeRevision.vid) * A Node has many revisions (Node.nid -- NodeRevision.nid) * A NodeRevision belongs to a Node (NodeRevision.nid -- Node.nid). So far I've managed to get the following: class Node(Base): __tablename__ = node nid = Column(Integer, primary_key=True) vid = Column(Integer, ForeignKey(node_revisions.vid)) latest_revision = relation(NodeRevision) class NodeRevision(Base): __tablename__ = node_revisions vid = Column(Integer, primary_key=True) nid = Column(Integer) As you can see the only relation modeled here is the Node.last_revision relation. Note how I've left out NodeRevision.nid = Column(Integer, ForeignKey(Node)) because if I add it SQLAlchemy will immediately complain that it needs a primaryjoin argument (and whatever I try nothing works). I've searched the mailing list and there were a couple of discussions about circular dependencies but I didn't quite understand what was being said and how to adapt the examples to my situation. The relations most likely also need to take into account that Node eventually needs to have a valid vid and NodeRevision needs to have a valid nid. The documentation for the post_update argument on a relation describes this situation perfectly.[1] However, as you can tell, I haven't even gotten that far. Any help would be greatly appreciated. Thanks. References: [1] http://tinyurl.com/cqhk8y Best regards, Bruce signature.asc Description: OpenPGP digital signature
[sqlalchemy] Re: Unable to model cross relations (using ext.declarative)
since you are modeling a parent object with many children, but also with a separate many-to-one from the parent to exactly one of those children, you need to have a foreign key in both the node and noderevision table, each referencing the other table's primary key. You then build individual relation()s based on each of those foreign keys. The relation() representing the many revisions of a Node will also have a backref on it indicating the NodeRevision belonging to a Node. Then, on the latest_revision relation(), place the flag post_update=True which will indicate that a Node object should be added to the database using an INSERT, and then a second UPDATE after the child NodeRevision object has been added. This flag should be described in the API documentation for relation(). both relation()s will require a primaryjoin argument since it is ambiguous in each case which foreign key should be used to assemble the join. if you wish to issue CREATE TABLE statements for this setup, at least one of the ForeignKey objects must have the flag use_alter=True combined with a name field so that the constraint is created after both tables exist. On Feb 10, 2009, at 5:48 AM, Bruce van der Kooij wrote: Hi, this is probably extremely easy but I've only recently started using SQLAlchemy and I simply cannot manage to model the following relations: +--+ ++ | NodeRevision | | Node | +--+ 1 ++ | vid |--- || | |\ 1 || | | 1..* --\--| nid| | nid |-/ \ || | | \ 1 || +--+-| vid| ++ The diagram depicts the following relations: * A Node has a latest_revision (Node.vid -- NodeRevision.vid) * A Node has many revisions (Node.nid -- NodeRevision.nid) * A NodeRevision belongs to a Node (NodeRevision.nid -- Node.nid). So far I've managed to get the following: class Node(Base): __tablename__ = node nid = Column(Integer, primary_key=True) vid = Column(Integer, ForeignKey(node_revisions.vid)) latest_revision = relation(NodeRevision) class NodeRevision(Base): __tablename__ = node_revisions vid = Column(Integer, primary_key=True) nid = Column(Integer) As you can see the only relation modeled here is the Node.last_revision relation. Note how I've left out NodeRevision.nid = Column(Integer, ForeignKey(Node)) because if I add it SQLAlchemy will immediately complain that it needs a primaryjoin argument (and whatever I try nothing works). I've searched the mailing list and there were a couple of discussions about circular dependencies but I didn't quite understand what was being said and how to adapt the examples to my situation. The relations most likely also need to take into account that Node eventually needs to have a valid vid and NodeRevision needs to have a valid nid. The documentation for the post_update argument on a relation describes this situation perfectly.[1] However, as you can tell, I haven't even gotten that far. Any help would be greatly appreciated. Thanks. References: [1] http://tinyurl.com/cqhk8y Best regards, Bruce --~--~-~--~~~---~--~~ 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: referencing table in other databases inside a MSSQL server
On Feb 10, 2009, at 3:20 PM, nosklo wrote: Hi all! I have a MSSQL SERVER I want to use sqlalchemy on, because there are some old databases in there and legacy applications and we can't migrate the data for now. (although a migration is planned and SQLAlchemy is helping a lot) The server has two Databases (table collections) lets call them DataA and DataB. Both are on the same MSSQL SERVER. If i create a MSSQL engine like this: import sqlalchemy as sa e = sa.create_engine('mssql://myuser:sec...@myserver/DataA', echo=True) e.execute('SELECT * FROM TableInA') The engine points to the 'DataA' database, so that works. I get a result object with all rows from TableInA; But I still can access/join with a table in DataB by running: e.execute('SELECT * FROM DataB.dbo.TableInB') That works too, so I get rows from TableInB which is in DataB, a different database from the one I used when creating the engine object. I can even JOIN tables from DataB with tables in DataA, no problem. Question is: How can I do that in a sa.Table constructor? I have tried: sa.Table('DataB.dbo.TableInB', metadata, ) sa.Table('TableInB', metadata, , schema='DataB.dbo') And both failed. Is there a way to map tables from both databases using the same engine? So I can do a JOIN between the tables? you should use the schema argument. Define failed ? --~--~-~--~~~---~--~~ 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: resultset print keys values
Hi, On Tue, Feb 10, 2009 at 3:18 PM, Lukasz Szybalski szybal...@gmail.comwrote: On Tue, Feb 10, 2009 at 1:52 PM, Michael Bayer mike...@zzzcomputing.com wrote: dir(instance) is preferable to __dict__.keys() - the latter will not give you deferred attributes, unloaded collections, or the expired version of each of those. dir() respects descriptors basically. but then dir() includes stuff like: '__class__', '__delattr__', '__dict__', '__doc__', '__getattribute__', '__hash__', '__init__', '__module__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__str__', '__weakref__', '_sa_class_manager', '_sa_instance_state'] Which need to be filtered. Then for the remaining items need to loop through to get the value?! I figured there was a uniform function that would return dictionary of key/value pairs that is available on all the possible return objects. __dict__ is good enough for visual inspection for now. Wouldn't this do what you need: http://www.sqlalchemy.org/trac/wiki/FAQ#Whatsthebestwaytofigureoutwhichattributesarecolumnsgivenaclass -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.com/ --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---