[sqlalchemy] Empty inserts and objects with no attributes
Hi, A class of mine has no attributes besides its primary key. This key is a surrogate, so I'd like to assign values automatically via autoincrement. Unfortunately, it's not possible to persist objects of this class to the database, as the SQL compiler complains about empty inserts. Is there a common workaround for this? Thanks, Nick --~--~-~--~~~---~--~~ 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: Empty inserts and objects with no attributes
A class of mine has no attributes besides its primary key. This key is a surrogate, so I'd like to assign values automatically via autoincrement. Unfortunately, it's not possible to persist objects of this class to the database, as the SQL compiler complains about empty inserts. Is there a common workaround for this? Sure http://www.sqlalchemy.org/docs/05/metadata.html#ddl-level-defaults http://www.sqlalchemy.org/docs/05/metadata.html#defining-sequences -- Jazz is not dead, it just smells funny --~--~-~--~~~---~--~~ 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] Portable Enum Columns
Greetings Alchemists,
I want to define a column that will only accept a handful of possible
values. Sure enough, I can to that with a check constraint or with a
lookup table an a foreign key. However, for some reason, I really like
the semantic of an enum column. That is, I like to read a definition
that looks like that:
class Order(DeclarativeBase):
__tablename__ = 'order'
id = Column(Integer, primary_key=True)
code = Column(Enum('CMTP'), nullable=False, default='C')
# ...
There is the low level MySQL enum type and there is a somewhat dated
recipe on the wiki:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Enum
Is there another way to do it? Something that would be portable and
to both MySQL and Postgres would be great.
Regards,
--
Yannick Gingras
http://ygingras.net
signature.asc
Description: This is a digitally signed message part.
[sqlalchemy] order by a field of related object
Hi all, I have a model Project which has a reference to another model Country which has a field named `name`. I want to list projects ordered by the names of the countries. However I didn't achieve. Any thoughts? Haldun. --~--~-~--~~~---~--~~ 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] Two Table Entity In Declarative Style
I'm using SQLAlchemy 0.5.4p2 with Python 2.6.0. So far all of my model
has been in the declarative style -
class Task(Base):
An OpenGroupare Task object
__tablename__ = 'job'
object_id = Column(job_id,
Sequence('key_generator'),
primary_key=True)
version = Column(object_version, Integer)
parent_id = Column(parent_job_id, Integer,
ForeignKey('job.job_id'),
nullable=True)
But I have one 1:1 relation in my database that would be much easier to
model as just one object.
job_history job_history_info
---
job_history_id (PK) -1:1- job_history_id
object_version job_history_info_id (PK)
job_id comment
actor_iddb_status
action
action_date
job_status
db_status
I've found at least one essay about doing this using the traditional
style of mapping @
http://parijatmishra.wordpress.com/2009/01/18/sqlalchemy-one-classes-two-tables/
Is this possible using the declarative style? If so are there any examples
someone can point me to?
Is there, in general, a way to specify that a join is 1:1 so that the
mapper property returns the entity on the other side of the join rather
than a single element array?
--~--~-~--~~~---~--~~
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: Empty inserts and objects with no attributes
Nick Murphy wrote: Hi, A class of mine has no attributes besides its primary key. This key is a surrogate, so I'd like to assign values automatically via autoincrement. Unfortunately, it's not possible to persist objects of this class to the database, as the SQL compiler complains about empty inserts. Is there a common workaround for this? empty inserts are supported at least by mysql, pg, oracle, and newer versions of SQLite (via INSERT...DEFAULT VALUES which SQLA invokes automatically). Its a database dependent issue so let us know what DB you're using. Thanks, Nick --~--~-~--~~~---~--~~ 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: Two Table Entity In Declarative Style
Adam Tauno Williams wrote: But I have one 1:1 relation in my database that would be much easier to model as just one object. job_history job_history_info --- job_history_id (PK) -1:1- job_history_id object_version job_history_info_id (PK) job_id comment actor_iddb_status action action_date job_status db_status if you create Table objects for job_history and job_history_info, you can create a join via job_history.join(job_history_info), and then specify that to a declarative class using __table__ = myjoin instead of __tablename__. you will also want to equate job_history_id in both tables to a single attribute, as in http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables , which is accomplished with declarative in a similar way, i..e. id = [job_history.c.job_history_id, job_history_info.c.job_history_id. Is there, in general, a way to specify that a join is 1:1 so that the mapper property returns the entity on the other side of the join rather than a single element array? a map to a join is always 1:1 from the object perspective, but if there are multiple job_history_info rows for one job_history row, those would typically be expressed as different identities within the mapping. the primary key of your mapping defaults to [job_history.job_history_id, job_history_info.job_history_info_id]. --~--~-~--~~~---~--~~ 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] Update primary key with inheritance
Hello there,
When I try to modify/update a primary key attribute in an inheritance
case, I always get a ConcurrentModificationError. Below is a
demonstration of the scenario based on the hierarchies paragraph from
the sqlalchemy documentation.
Can someone help me to get this right?
Many thanks in advance,
Marble
base = declarative_base()
class Employee(base):
employee_id = Column('employee_id', Integer, primary_key=True,
autoincrement=True)
name = Column('name', String(50),primary_key=True)
type = Column('type', String(30), nullable=False)
__tablename__ = 'employees'
__table_args__ = {'mysql_engine':'InnoDB'}
__mapper_args__ = {'polymorphic_on':type,
'polymorphic_identity':'employee'}
def __init__(self, name):
self.name = name
def __repr__(self):
return self.__class__.__name__ + + self.name
class Engineer(Employee):
employee_id = Column('employee_id', Integer, unique=True)
name = Column('name', String(50),primary_key=True)
engineer_info = Column('engineer_info', String(50), primary_key
=True)
__tablename__ = 'engineers'
__table_args__ = (ForeignKeyConstraint(['employee_id', 'name'],
['employees.employee_id',
'employees.name'],
onupdate=CASCADE,
ondelete=CASCADE),
{'mysql_engine':'InnoDB'})
__mapper_args__ = {'inherits':Employee,
'inherit_condition': Employee.employee_id ==
employee_id,
'polymorphic_identity':'engineer'}
def __init__(self, name, engineer_info):
self.name = name
self.engineer_info = engineer_info
def __repr__(self):
return self.__class__.__name__ + + self.name + +
self.engineer_info
#=
joe = Engineer('Joe','Engineer of the month')
session.add(joe)
session.commit()
joe.name = 'Joey' #Here I redefine the primary key
session.commit() #Now the Error is raised
--~--~-~--~~~---~--~~
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: Update primary key with inheritance
Marble wrote: Hello there, When I try to modify/update a primary key attribute in an inheritance case, I always get a ConcurrentModificationError. Below is a demonstration of the scenario based on the hierarchies paragraph from the sqlalchemy documentation. Can someone help me to get this right? this is ticket 1362 and is a TODO: http://www.sqlalchemy.org/trac/ticket/1362 . For now you need to issue update() statements for the tables manually, then reload your objects. The operation will also will likely require that you enable ON UPDATE CASCADE on the foreign key columns if you're on a database such as Postgresql (oh I see you have that already, yup). --~--~-~--~~~---~--~~ 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: relations with additional criteria
Hey, Thanks very much for looking into this. I'm sorry I couldn't offer more specific suggestions earlier - my goal was just to start the thought process leading to such suggestions (also in myself), and at this point you're still a lot more familiar with this codebase than I am. :) I'm happy the discussion ended up leading to this improvement. The backref story indeed looks like an improvement as less knowledge about this has to be in different areas of the code. I do still have some questions about your suggested code: Michael Bayer wrote: class MyRelation(RelationProperty): def _determine_joins(self): self.primaryjoin = join_condition(self.parent.local_table, self.target) (self.parent.local_table.c.id0) The code in the baseclass version of _determine_joins isn't used at all in your example. But this code appears to handle various cases: * raising an exception if secondary is None when self.secondaryjoin isn't * finding a join condition to the mapped_table in the case of some inheritance conditions * handling the cases where secondary is provided in the case of a MANY to MANY relationship * raise an error if join conditions couldn't be determined. In order to handle all these cases I think in my subclass I'd need to handle them too. In addition I'm curious what _orm_deannotate does and why deannotating the extra clause isn't needed in this case. Regards, Martijn --~--~-~--~~~---~--~~ 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: order by a field of related object
Assuming you have the foreign keys defined, it should be fairly easy. session.query(Project).join(Country).order_by(Country.name) if you don't have the keys defined, you will need to add the join condition to the .join() -- Mike Conley --~--~-~--~~~---~--~~ 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: Empty inserts and objects with no attributes
Hi Mike, I'm using SQLite, and I'm tracking svn trunk (I'm at revision 6390). Is the newer sqlite backend you mentioned somewhere else? Thanks, Nick On Oct 6, 8:49 am, Michael Bayer mike...@zzzcomputing.com wrote: Nick Murphy wrote: Hi, A class of mine has no attributes besides its primary key. This key is a surrogate, so I'd like to assign values automatically via autoincrement. Unfortunately, it's not possible to persist objects of this class to the database, as the SQL compiler complains about empty inserts. Is there a common workaround for this? empty inserts are supported at least by mysql, pg, oracle, and newer versions of SQLite (via INSERT...DEFAULT VALUES which SQLA invokes automatically). Its a database dependent issue so let us know what DB you're using. Thanks, Nick --~--~-~--~~~---~--~~ 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: Empty inserts and objects with no attributes
On Oct 6, 2009, at 3:07 PM, Nick Murphy wrote: Hi Mike, I'm using SQLite, and I'm tracking svn trunk (I'm at revision 6390). Is the newer sqlite backend you mentioned somewhere else? the sqlite3 included with Python 2.6 handles DEFAULT VALUES, so that would be a place to start. Any recent sqlite from the sqlite.org website supports it too. Thanks, Nick On Oct 6, 8:49 am, Michael Bayer mike...@zzzcomputing.com wrote: Nick Murphy wrote: Hi, A class of mine has no attributes besides its primary key. This key is a surrogate, so I'd like to assign values automatically via autoincrement. Unfortunately, it's not possible to persist objects of this class to the database, as the SQL compiler complains about empty inserts. Is there a common workaround for this? empty inserts are supported at least by mysql, pg, oracle, and newer versions of SQLite (via INSERT...DEFAULT VALUES which SQLA invokes automatically). Its a database dependent issue so let us know what DB you're using. Thanks, Nick --~--~-~--~~~---~--~~ 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: relations with additional criteria
On Oct 6, 2009, at 12:53 PM, Martijn Faassen wrote: Hey, Thanks very much for looking into this. I'm sorry I couldn't offer more specific suggestions earlier - my goal was just to start the thought process leading to such suggestions (also in myself), and at this point you're still a lot more familiar with this codebase than I am. :) I'm happy the discussion ended up leading to this improvement. The backref story indeed looks like an improvement as less knowledge about this has to be in different areas of the code. I do still have some questions about your suggested code: Michael Bayer wrote: class MyRelation(RelationProperty): def _determine_joins(self): self.primaryjoin = join_condition(self.parent.local_table, self.target) (self.parent.local_table.c.id0) The code in the baseclass version of _determine_joins isn't used at all in your example. But this code appears to handle various cases: * raising an exception if secondary is None when self.secondaryjoin isn't * finding a join condition to the mapped_table in the case of some inheritance conditions * handling the cases where secondary is provided in the case of a MANY to MANY relationship * raise an error if join conditions couldn't be determined. In order to handle all these cases I think in my subclass I'd need to handle them too. how about def _determine_joins(self): RelationProperty._determine_joins(self) self.primaryjoin = self.primaryjoin whatever if theres some sensitivity to secondary being present, you'd add that too. I don't know what exactly it is you'd like to do to the primary join condition in the first place so the steps to take will vary. In addition I'm curious what _orm_deannotate does and why deannotating the extra clause isn't needed in this case. _orm_deannotate() has to do with clauses that are formed using the declarative style. If you said Foo.bar == 3, that is different than foo_table.c.bar == 3 - the former has additional information about the ORM relationship embedded within it.I haven't checked to see if this requirement can be loosened or simplified these days, but originally, sending in the ORM aware clauses as the primaryjoin and secondaryjoin produced confusion when the internals would be combining those clauses with userland clauses that may have ORM annotations applied (which then signified various translations to be applied to them). I'd have to step through that code to give you more specifics.If you are using table bound columns to create your join condition then the deannotate step would not be needed. For the next go-around here, assuming this is all still not working for you can you please provide an example of what the additional filter does exactly ? at this point you're looking for a subclass hook that is basically augment_primary_join(self, existing_primary_join), since you're looking to have the figure out the joins stuff done for you in exactly the same way it is now, but you want to have a say in changing the final product. that's a pretty arbitrary looking hook. --~--~-~--~~~---~--~~ 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: Portable Enum Columns
On Tuesday 06 October 2009 14.45:33 Yannick Gingras wrote: [...] Is there another way to do it? Something that would be portable and to both MySQL and Postgres would be great. Since both pg and mysql hava a native enum type, it's only a matter of writing the appropriate code in the SQL dialects. This came up just recently, search the list archive. I can't remember if the answer last time was it would be quite easy to do or somebody is working on it, though. cheers -- vbi -- featured product: PostgreSQL - http://postgresql.org signature.asc Description: This is a digitally signed message part.
