Re: [sqlalchemy] Re: AuditLog/History logging
You can place triggers in your class definitions, there are decorators for this, see @afterUpdate, @beforeUpdate, @afterDelete, @beforeDelete, @afterInsert, @beforeInsert. You can search for these in the documentation, I am not sure of the exact spelling tho. Cheers, Y.chaouche - Original Message From: Gopalakrishnan Subramani gopalakrishnan.subram...@gmail.com To: sqlalchemy sqlalchemy@googlegroups.com Sent: Wed, November 3, 2010 6:21:01 PM Subject: [sqlalchemy] Re: AuditLog/History logging Chaouche, Thank you for information. At the instance level, how will I know whether the instance going to be deleted or updated or insert. How do I get the property? And during update, how to identify which field has been modified? Regards, Krish On Nov 3, 6:30 pm, chaouche yacine yacinechaou...@yahoo.com wrote: http://www.sqlalchemy.org/docs/orm/examples.html?highlight=versioning... You can find other interesting recipes in : http://www.sqlalchemy.org/trac/wiki/UsageRecipes Cheers, Y.Chaouche - Original Message From: Gopalakrishnan Subramani gopalakrishnan.subram...@gmail.com To: sqlalchemy sqlalchemy@googlegroups.com Sent: Wed, November 3, 2010 5:21:32 AM Subject: [sqlalchemy] AuditLog/History logging We have around 10 different tables in the database and we use sqlalchemy's declarative base style table definition. We are happy about it. Now we need to have auditlog/history of changes made to database records. For example, the entry could be new recorded added or existing record updated or deleted. These information should be reflected in the database table called history. So we end up writing very simular code for every tables in our database for each crud operation. I am thinking whether we can have simplified solution like 1. We define our own custom class derived from declarative_base instance 2. We derive the rest of our classes from our own custom class mentioned in the step 1 3. Whenever there is changes in the record, Sqlalchemy to give callback on post/pre operation and we capture those changes in the derived class and use the history class to write the changes to the database. So we can derive all our future classes from this model and I will be free to handle the rest of the information. Can you please help me to implement this? Regards, Krisj -- 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. -- 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] AuditLog/History logging
I know that elixir has an extension that does just this. You don't have to subclass, you just use a acts_as_versioned statement in your class definition and that's it. In addition, it will let you get the object at any of its verison (you can revert back changes, a sort of undo funcitonnality that spans over time) via the get_as_of method http://elixir.ematia.de/apidocs/elixir.ext.versioned.html I wonder if sqlalchemy has an equivalent extension ? Cheers, Y.Chaouche - Original Message From: Gopalakrishnan Subramani gopalakrishnan.subram...@gmail.com To: sqlalchemy sqlalchemy@googlegroups.com Sent: Wed, November 3, 2010 5:21:32 AM Subject: [sqlalchemy] AuditLog/History logging We have around 10 different tables in the database and we use sqlalchemy's declarative base style table definition. We are happy about it. Now we need to have auditlog/history of changes made to database records. For example, the entry could be new recorded added or existing record updated or deleted. These information should be reflected in the database table called history. So we end up writing very simular code for every tables in our database for each crud operation. I am thinking whether we can have simplified solution like 1. We define our own custom class derived from declarative_base instance 2. We derive the rest of our classes from our own custom class mentioned in the step 1 3. Whenever there is changes in the record, Sqlalchemy to give callback on post/pre operation and we capture those changes in the derived class and use the history class to write the changes to the database. So we can derive all our future classes from this model and I will be free to handle the rest of the information. Can you please help me to implement this? Regards, Krisj -- 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. -- 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] AuditLog/History logging
http://www.sqlalchemy.org/docs/orm/examples.html?highlight=versioning#versioning You can find other interesting recipes in : http://www.sqlalchemy.org/trac/wiki/UsageRecipes Cheers, Y.Chaouche - Original Message From: Gopalakrishnan Subramani gopalakrishnan.subram...@gmail.com To: sqlalchemy sqlalchemy@googlegroups.com Sent: Wed, November 3, 2010 5:21:32 AM Subject: [sqlalchemy] AuditLog/History logging We have around 10 different tables in the database and we use sqlalchemy's declarative base style table definition. We are happy about it. Now we need to have auditlog/history of changes made to database records. For example, the entry could be new recorded added or existing record updated or deleted. These information should be reflected in the database table called history. So we end up writing very simular code for every tables in our database for each crud operation. I am thinking whether we can have simplified solution like 1. We define our own custom class derived from declarative_base instance 2. We derive the rest of our classes from our own custom class mentioned in the step 1 3. Whenever there is changes in the record, Sqlalchemy to give callback on post/pre operation and we capture those changes in the derived class and use the history class to write the changes to the database. So we can derive all our future classes from this model and I will be free to handle the rest of the information. Can you please help me to implement this? Regards, Krisj -- 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. -- 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] Checking the availablity of a booked Item
Did you try : qry = qry.outerjoin(Appointment.person).filter(Appointment.id == None) ? I have the intuition that sqlalchemy can guess on what foreign keys the join should operate based on how you declared your relations. What about (if Person.appointments exists): qry = qry.outerjoin(Person.appointments).filter(Appointment.id == None) ? I don't think you'd have the same results (join order matters). Y.Chaouche --- On Wed, 10/20/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 20, 2010, 8:27 AM Here is an example of how I use outerjoin to perform a similar query. class Survey, def get_apps(self): Get all persons who are over 18 and do not have an appointment but have this survey. qry = Person.query.filter(Person.age 18) qry = qry.outerjoin((Appointment, (Appointment.id_person == Person.id) (Appointment.id_survey == self.id))). \ filter(Appointment.id == None) #: Or != None, depending on the type of join. #: I go and append a few more outer joins here as well,# but they look exactly the same just different tables. The syntax of the outerjoin arguments is a tuple containing (Table to join to, whereclause to join on). Hopefully this helps you. Basically, I just look at the coding horror site for the kind of join I want to perform, and modify the syntax above to make it match the SQL. -- Thadeus On Mon, Oct 18, 2010 at 10:38 AM, chaouche yacine yacinechaou...@yahoo.com wrote: Hello, I think outerjoin is just a join with an isouter = True, and above all the problem was not there anyway. The problem was only about ordering the joins. The correct python code was : Face.query.outerjoin(Face.bookings).filter(cond).all() instead of Face.query.outerjoin(Booking.faces).filter(cond).all() Which looks more intuitive to me, because I'm doing joins between Face and Booking (thus putting Booking in the outerjoin, not Face again as in the first code). I can't understand the logic of the (correct) first one, but it works ! Or is it two errors cancelling each other ? Y.Chaouche --- On Thu, 10/14/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Thursday, October 14, 2010, 8:42 AM http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join isouter = True -- Thadeus On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.com wrote: Here's the SQL I got : SELECT face.id AS face_id FROM face LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 ON face.id = face_bookings__booking_faces_1.face_id LEFT OUTER JOIN booking ON booking.id = face_bookings__booking_faces_1.booking_id JOIN time_period ON booking.time_period_id = time_period.id WHERE time_period.start_date %(start_date_1)s OR time_period.end_date %(end_date_1)s With the following code : class Booking(BaseModel): using_options(tablename=booking) reprattr = time_period faces = ManyToMany(Face) # A client has one and only one booking per time period time_period = ManyToOne(TimePeriod) @classmethod def get_available_faces(self,time_period): Return faces that are not booked during the given time_period. from timeperiod import TimePeriod from face import Face start_date_cond = TimePeriod.start_date time_period.end_date end_date_cond = TimePeriod.end_date time_period.start_date unbooked = or_(start_date_cond,end_date_cond) # query = Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked))) # return query.all() query = Face.query.filter(unbooked) #return query.all() query = query.outerjoin(Face.bookings) #return query.all() query = query.join(Booking.time_period) return query.all() And still not the expected results (it should return faces with no bookings at all but it doesen't). Thanks for any help. Y.Chaouche --- On Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 5:25 AM Thank you Thadeus, I believe Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full outerjoin, or is there another way to do it ? Y.Chaouche --- On Wed, 10/13/10, Thadeus
Re: [sqlalchemy] Checking the availablity of a booked Item
Hello, I think outerjoin is just a join with an isouter = True, and above all the problem was not there anyway. The problem was only about ordering the joins. The correct python code was : Face.query.outerjoin(Face.bookings).filter(cond).all() instead of Face.query.outerjoin(Booking.faces).filter(cond).all() Which looks more intuitive to me, because I'm doing joins between Face and Booking (thus putting Booking in the outerjoin, not Face again as in the first code). I can't understand the logic of the (correct) first one, but it works ! Or is it two errors cancelling each other ? Y.Chaouche --- On Thu, 10/14/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Thursday, October 14, 2010, 8:42 AM http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.orm.join isouter = True -- Thadeus On Thu, Oct 14, 2010 at 10:26 AM, chaouche yacine yacinechaou...@yahoo.com wrote: Here's the SQL I got : SELECT face.id AS face_id FROM face LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 ON face.id = face_bookings__booking_faces_1.face_id LEFT OUTER JOIN booking ON booking.id = face_bookings__booking_faces_1.booking_id JOIN time_period ON booking.time_period_id = time_period.id WHERE time_period.start_date %(start_date_1)s OR time_period.end_date %(end_date_1)s With the following code : class Booking(BaseModel): using_options(tablename=booking) reprattr = time_period faces = ManyToMany(Face) # A client has one and only one booking per time period time_period = ManyToOne(TimePeriod) @classmethod def get_available_faces(self,time_period): Return faces that are not booked during the given time_period. from timeperiod import TimePeriod from face import Face start_date_cond = TimePeriod.start_date time_period.end_date end_date_cond = TimePeriod.end_date time_period.start_date unbooked = or_(start_date_cond,end_date_cond) # query = Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked))) # return query.all() query = Face.query.filter(unbooked) #return query.all() query = query.outerjoin(Face.bookings) #return query.all() query = query.join(Booking.time_period) return query.all() And still not the expected results (it should return faces with no bookings at all but it doesen't). Thanks for any help. Y.Chaouche --- On Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 5:25 AM Thank you Thadeus, I believe Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full outerjoin, or is there another way to do it ? Y.Chaouche --- On Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 12:04 AM For outer joins you need a where clause on the joined tables. http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html Using a full outer join should return the expected results. -- Thadeus On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com wrote: Hello, Here's my simple model (For simplification, consider Face as a Billboard) : +-+ +---+ +--+ |Face |.. |Campaign | ...|TimePeriod| +-+ . +---+ . +--+ |code | . |time_period| |start_time| +-+ . +---+ +--+ |faces | |end_time | +---+ +--+ One way to read this model is : A campaign can book multiple faces during a certain period of time. What I want to do is get all the available Faces for a given period of time, to see what faces can I book for a new campaign that longs for that particular period of time. I would typically have a Face.get_available(time_period) class method that does the job. This method would look for all the faces that don't have an ongoing booking. My question is : how to write such a method ? Here's how I figured it out (couldn't get it to work) : class Face(Entity): using_options(tablename=faces) �...@classmethod def get_available(self,time_period): Return faces that are not booked (not in any campaign
Re: [sqlalchemy] Checking the availablity of a booked Item
Here's the SQL I got : SELECT face.id AS face_id FROM face LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 ON face.id = face_bookings__booking_faces_1.face_id LEFT OUTER JOIN booking ON booking.id = face_bookings__booking_faces_1.booking_id JOIN time_period ON booking.time_period_id = time_period.id WHERE time_period.start_date %(start_date_1)s OR time_period.end_date %(end_date_1)s With the following code : class Booking(BaseModel): using_options(tablename=booking) reprattr = time_period faces = ManyToMany(Face) # A client has one and only one booking per time period time_period = ManyToOne(TimePeriod) @classmethod def get_available_faces(self,time_period): Return faces that are not booked during the given time_period. from timeperiod import TimePeriod from face import Face start_date_cond = TimePeriod.start_date time_period.end_date end_date_cond = TimePeriod.end_date time_period.start_date unbooked = or_(start_date_cond,end_date_cond) # query = Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked))) # return query.all() query = Face.query.filter(unbooked) #return query.all() query = query.outerjoin(Face.bookings) #return query.all() query = query.join(Booking.time_period) return query.all() And still not the expected results (it should return faces with no bookings at all but it doesen't). Thanks for any help. Y.Chaouche --- On Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 5:25 AM Thank you Thadeus, I believe Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full outerjoin, or is there another way to do it ? Y.Chaouche --- On Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 12:04 AM For outer joins you need a where clause on the joined tables. http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html Using a full outer join should return the expected results. -- Thadeus On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com wrote: Hello, Here's my simple model (For simplification, consider Face as a Billboard) : +-+ +---+ +--+ |Face |.. |Campaign | ...|TimePeriod| +-+ . +---+ . +--+ |code | . |time_period| |start_time| +-+ . +---+ +--+ |faces | |end_time | +---+ +--+ One way to read this model is : A campaign can book multiple faces during a certain period of time. What I want to do is get all the available Faces for a given period of time, to see what faces can I book for a new campaign that longs for that particular period of time. I would typically have a Face.get_available(time_period) class method that does the job. This method would look for all the faces that don't have an ongoing booking. My question is : how to write such a method ? Here's how I figured it out (couldn't get it to work) : class Face(Entity): using_options(tablename=faces) �...@classmethod def get_available(self,time_period): Return faces that are not booked (not in any campaign) during the given time_period. # start_date_cond = TimePeriod.start_date time_period.end_date # end_date_cond = TimePeriod.end_date time_period.start_date # available_periods = Campaign.time_period.has(or_(start_date_cond,end_date_cond)) # unavailable_periods = not(available_periods) # I am pretty sure that the time conditions are good. # Here's a good way to convince yourself (read from bottom to top) : # L1 0--- # L2 --| # L3 0[]-- # L3 represents the desired period (passed as argument) going from [ to ] # place the start date of the booked face anywhere on L2 # place the end date of the booked face anywhere on L1 # of course, end date must be after start date... # Anyway you do it, your face isn't available for the period of time in L3. start_date_cond
Re: [sqlalchemy] Checking the availablity of a booked Item
Here's the SQL I got : SELECT face.id AS face_id FROM face LEFT OUTER JOIN face_bookings__booking_faces AS face_bookings__booking_faces_1 ON face.id = face_bookings__booking_faces_1.face_id LEFT OUTER JOIN booking ON booking.id = face_bookings__booking_faces_1.booking_id JOIN time_period ON booking.time_period_id = time_period.id WHERE time_period.start_date %(start_date_1)s OR time_period.end_date %(end_date_1)s With the following code : class Booking(BaseModel): using_options(tablename=booking) reprattr = time_period faces = ManyToMany(Face) # A client has one and only one booking per time period time_period = ManyToOne(TimePeriod) @classmethod def get_available_faces(self,time_period): Return faces that are not booked during the given time_period. from timeperiod import TimePeriod from face import Face start_date_cond = TimePeriod.start_date time_period.end_date end_date_cond = TimePeriod.end_date time_period.start_date unbooked = or_(start_date_cond,end_date_cond) # query = Face.query.filter(Face.bookings.any(Booking.time_period.has(unbooked))) # return query.all() query = Face.query.filter(unbooked) #return query.all() query = query.outerjoin(Face.bookings) #return query.all() query = query.join(Booking.time_period) return query.all() And still not the expected results (it should return faces with no bookings at all but it doesen't). Thanks for any help. Y.Chaouche --- On Wed, 10/13/10, chaouche yacine yacinechaou...@yahoo.com wrote: From: chaouche yacine yacinechaou...@yahoo.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 5:25 AM Thank you Thadeus, I believe Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full outerjoin, or is there another way to do it ? Y.Chaouche --- On Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com wrote: From: Thadeus Burgess thade...@thadeusb.com Subject: Re: [sqlalchemy] Checking the availablity of a booked Item To: sqlalchemy@googlegroups.com Date: Wednesday, October 13, 2010, 12:04 AM For outer joins you need a where clause on the joined tables. http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html Using a full outer join should return the expected results. -- Thadeus On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com wrote: Hello, Here's my simple model (For simplification, consider Face as a Billboard) : +-+ +---+ +--+ |Face |.. |Campaign | ...|TimePeriod| +-+ . +---+ . +--+ |code | . |time_period| |start_time| +-+ . +---+ +--+ |faces | |end_time | +---+ +--+ One way to read this model is : A campaign can book multiple faces during a certain period of time. What I want to do is get all the available Faces for a given period of time, to see what faces can I book for a new campaign that longs for that particular period of time. I would typically have a Face.get_available(time_period) class method that does the job. This method would look for all the faces that don't have an ongoing booking. My question is : how to write such a method ? Here's how I figured it out (couldn't get it to work) : class Face(Entity): using_options(tablename=faces) �...@classmethod def get_available(self,time_period): Return faces that are not booked (not in any campaign) during the given time_period. # start_date_cond = TimePeriod.start_date time_period.end_date # end_date_cond = TimePeriod.end_date time_period.start_date # available_periods = Campaign.time_period.has(or_(start_date_cond,end_date_cond)) # unavailable_periods = not(available_periods) # I am pretty sure that the time conditions are good. # Here's a good way to convince yourself (read from bottom to top) : # L1 0--- # L2 --| # L3 0[]-- # L3 represents the desired period (passed as argument) going from [ to ] # place the start date of the booked face anywhere on L2 # place the end date of the booked face anywhere on L1 # of course, end date must be after start date... # Anyway you do it, your face isn't available for the period of time in L3. start_date_cond
Re: [sqlalchemy] Checking the availablity of a booked Item
Thank you Thadeus, I believe
Face.query.filter(filter_cond).outerjoin(join_clause).all() does a full
outerjoin, or is there another way to do it ?
Y.Chaouche
--- On Wed, 10/13/10, Thadeus Burgess thade...@thadeusb.com wrote:
From: Thadeus Burgess thade...@thadeusb.com
Subject: Re: [sqlalchemy] Checking the availablity of a booked Item
To: sqlalchemy@googlegroups.com
Date: Wednesday, October 13, 2010, 12:04 AM
For outer joins you need a where clause on the joined tables.
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
Using a full outer join should return the expected results.
--
Thadeus
On Tue, Oct 12, 2010 at 1:41 PM, chaouche yacine yacinechaou...@yahoo.com
wrote:
Hello,
Here's my simple model (For simplification, consider Face as a Billboard) :
+-+ +---+ +--+
|Face |.. |Campaign | ...|TimePeriod|
+-+ . +---+ . +--+
|code | . |time_period| |start_time|
+-+ . +---+ +--+
|faces | |end_time |
+---+ +--+
One way to read this model is : A campaign can book multiple faces during a
certain period of time.
What I want to do is get all the available Faces for a given period of time, to
see what faces can I book for a new campaign that longs for that particular
period of time. I would typically have a Face.get_available(time_period) class
method that does the job. This method would look for all the faces that don't
have an ongoing booking. My question is : how to write such a method ?
Here's how I figured it out (couldn't get it to work) :
class Face(Entity):
using_options(tablename=faces)
�...@classmethod
def get_available(self,time_period):
Return faces that are not booked (not in any campaign) during the given
time_period.
# start_date_cond = TimePeriod.start_date time_period.end_date
# end_date_cond = TimePeriod.end_date time_period.start_date
# available_periods =
Campaign.time_period.has(or_(start_date_cond,end_date_cond))
# unavailable_periods = not(available_periods)
# I am pretty sure that the time conditions are good.
# Here's a good way to convince yourself (read from bottom to top) :
# L1
0---
# L2 --|
# L3
0[]--
# L3 represents the desired period (passed as argument) going from [
to ]
# place the start date of the booked face anywhere on L2
# place the end date of the booked face anywhere on L1
# of course, end date must be after start date...
# Anyway you do it, your face isn't available for the period of time in
L3.
start_date_cond = TimePeriod.start_date = time_period.end_date
end_date_cond = TimePeriod.end_date = time_period.start_date
unavailable_periods =
Campaign.time_period.has(and_(start_date_cond,end_date_cond))
# I am not sure about what follows...
filter_cond = not_(unavailable_periods)
join_clause = Campaign.faces
return Face.query.filter(filter_cond).outerjoin(join_clause).all()
This code returns only faces that have already been booked before or have a
future booking, and are free for the moment. But faces with no bookings at all
are not returned. This may be due to an incorrect outerjoin ? (I also tried a
simple join with no success)
Here's the generated sql for one query :
2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec
SELECT faces.id AS faces_id
FROM campaigns LEFT OUTER JOIN campaigns_faces__faces AS
campaigns_faces__faces_1 ON campaigns.id = campaigns_faces__faces_1.campaigns_id
LEFT OUTER JOIN faces ON faces.id = campaigns_faces__faces_1.faces_id
WHERE NOT (EXISTS (SELECT 1
FROM time_periods
WHERE campaigns.time_period_id = time_periods.id
AND time_periods.start_date = %(start_date_1)s
AND time_periods.end_date = %(end_date_1)s))
2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec
{'start_date_1': datetime.date(2010, 10, 30), 'end_date_1': datetime.date(2010,
10, 20)}
[Face id=1 at 0x932218c ]
Any help would be very appreciated.
Y.Chaouche
PS : and please, don't give me that lame it's elixir excuse. The question is
about how to construct the proper query for the desired operation in a
sqlalchemy way. Elixir is only another Declarative approach + mapping, that's
it.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send
[sqlalchemy] Checking the availablity of a booked Item
Hello,
Here's my simple model (For simplification, consider Face as a Billboard) :
+-+ +---+ +--+
|Face |.. |Campaign | ...|TimePeriod|
+-+ . +---+ . +--+
|code | . |time_period| |start_time|
+-+ . +---+ +--+
|faces | |end_time |
+---+ +--+
One way to read this model is : A campaign can book multiple faces during a
certain period of time.
What I want to do is get all the available Faces for a given period of time, to
see what faces can I book for a new campaign that longs for that particular
period of time. I would typically have a Face.get_available(time_period) class
method that does the job. This method would look for all the faces that don't
have an ongoing booking. My question is : how to write such a method ?
Here's how I figured it out (couldn't get it to work) :
class Face(Entity):
using_options(tablename=faces)
@classmethod
def get_available(self,time_period):
Return faces that are not booked (not in any campaign) during the given
time_period.
# start_date_cond = TimePeriod.start_date time_period.end_date
# end_date_cond = TimePeriod.end_datetime_period.start_date
# available_periods =
Campaign.time_period.has(or_(start_date_cond,end_date_cond))
# unavailable_periods = not(available_periods)
# I am pretty sure that the time conditions are good.
# Here's a good way to convince yourself (read from bottom to top) :
# L1
0---
# L2 --|
# L3
0[]--
# L3 represents the desired period (passed as argument) going from [
to ]
# place the start date of the booked face anywhere on L2
# place the end date of the booked face anywhere on L1
# of course, end date must be after start date...
# Anyway you do it, your face isn't available for the period of time in
L3.
start_date_cond = TimePeriod.start_date = time_period.end_date
end_date_cond = TimePeriod.end_date = time_period.start_date
unavailable_periods =
Campaign.time_period.has(and_(start_date_cond,end_date_cond))
# I am not sure about what follows...
filter_cond = not_(unavailable_periods)
join_clause = Campaign.faces
return Face.query.filter(filter_cond).outerjoin(join_clause).all()
This code returns only faces that have already been booked before or have a
future booking, and are free for the moment. But faces with no bookings at all
are not returned. This may be due to an incorrect outerjoin ? (I also tried a
simple join with no success)
Here's the generated sql for one query :
2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec
SELECT faces.id AS faces_id
FROM campaigns LEFT OUTER JOIN campaigns_faces__faces AS
campaigns_faces__faces_1 ON campaigns.id =
campaigns_faces__faces_1.campaigns_id
LEFT OUTER JOIN faces ON faces.id = campaigns_faces__faces_1.faces_id
WHERE NOT (EXISTS (SELECT 1
FROM time_periods
WHERE campaigns.time_period_id = time_periods.id
AND time_periods.start_date = %(start_date_1)s
AND time_periods.end_date = %(end_date_1)s))
2010-10-12 19:34:22,837 INFO sqlalchemy.engine.base.Engine.0x...f4ec
{'start_date_1': datetime.date(2010, 10, 30), 'end_date_1': datetime.date(2010,
10, 20)}
[Face id=1 at 0x932218c ]
Any help would be very appreciated.
Y.Chaouche
PS : and please, don't give me that lame it's elixir excuse. The question is
about how to construct the proper query for the desired operation in a
sqlalchemy way. Elixir is only another Declarative approach + mapping, that's
it.
--
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.
# ORM
from sqlalchemy.sql.expression import *
from sqlalchemy.ormimport reconstructor
from elixirimport *
# stdlib
from datetime import date
import sys
class TimePeriod(Entity):
using_options(tablename=time_periods)
start_date = Field(Date())
end_date = Field(Date())
name = Field(Unicode())
class Campaign(Entity):
using_options(tablename=campaigns)
time_period = ManyToOne(TimePeriod)
faces = ManyToMany(Face)
class Face(Entity):
Re: [sqlalchemy] Copying instances from old to new schema DB
The two instances don't have the same ID, the uniqueness is tested on two other columns. session.query(cls).filter(cls.the_unique_column==new_object.the_unique_column).first() Is there a way to get the tuple (usually a couple) of columns that are involved in a unique constraint ? if there is such a way, than the line of code above could be generically applied to all models, which would be sweet :) something like : for row in csv: new_object = make_an_object_from_csv_row(row) existing_object = get_existing_object(new_object) if existing_object is not None: new_object.id = existing_object.id new_object = session.merge(new_object) session.commit() def get_existing_object(new_object): unique_constraints = [constraint.columns for constraint in cls.get_constraints() if constraint.get_type() == UniqueConstraint] u_c_columns = [constraint.columns for constraint in unique_constraints] query = session.query(cls) for column_tuples in u_c_columns : for column in column_tuples : query = query.filter(cls.get_attr(column) == new_object.get_attr(column)) return query.first() --- On Thu, 9/9/10, Michael Bayer mike...@zzzcomputing.com wrote: From: Michael Bayer mike...@zzzcomputing.com Subject: Re: [sqlalchemy] Copying instances from old to new schema DB To: sqlalchemy@googlegroups.com Date: Thursday, September 9, 2010, 9:54 AM On Sep 9, 2010, at 11:31 AM, chaouche yacine wrote: Thank you Michael, that's for the catch-the-error part. How about the set-relations-right part, if I decided to go with my fix-as-you go recipe ? do you have any idea ? the dictionary approach seems good, but I still am curious about how to set relations generically on models. I think I'll use it somewhere else in my code. You don't need to set any relations. If your new B has a fully populated primary key, you should just be using session.merge() for the whole thing. This can work with your integrity error scheme, or more simply with the select first schemes below: for row in csv: new_object = make_an_object_from_csv_row(row) new_object = session.merge(new_object) session.commit() if new_object does *not* have the correct primary key, then: for row in csv: new_object = make_an_object_from_csv_row(row) existing_object = session.query(cls).filter(cls.the_unique_column==new_object.the_unique_column).first() if existing_object is not None: new_object.id = existing_object.id new_object = session.merge(new_object) session.commit() if you don't like the many indvidual SELECT statements, then # load a dictionary of (unique attr, primary key) lookup = dict( session.query(cls.the_unique_column, cls.id) ) for row in csv: new_object = make_an_object_from_csv_row(row) if new_object.the_unique_column in lookup: new_object.id = lookup[new_object.the_unique_column] new_object = session.merge(new_object) session.commit() or if most of your rows are replacement rows, quicker to preload everything: # load a dictionary of (unique attr, instance) lookup = dict( session.query(cls.the_unique_column, cls) ) for row in csv: new_object = make_an_object_from_csv_row(row) if new_object.the_unique_column in lookup: new_object.id = lookup[new_object.the_unique_column].id new_object = session.merge(new_object) session.commit() By the way, I think the pseudo should have been : instance = next_instance_from_csv() try: session.commit() except IntegrityError,e : session.rollback() if e.orig == UniqueConstraintError : original_instance = ModelClass.get(instance.id) for relation_name in instance.get_relation_names() : # Is this correct ? path_to_attribute= mapper.+ relation +.inverse # I don't know where to find this ? instance.set_attribute(path_to_attribute,original_instance) session.commit() class BaseModel (DeclarativeBase): ... def set_attribute(self,path,value): nodes = path.explode(.) current_node = self for next_node in nodes : current_node = getattr(current_node,next_node,None) if not current_node: raise InvalidAttribute set_attribute(current_node,value) # sqla's set_attribute, for whatever reason... Y.Chaouche --- On Thu, 9/9/10, Michael Bayer mike...@zzzcomputing.com wrote: From: Michael Bayer mike...@zzzcomputing.com Subject: Re: [sqlalchemy] Copying instances from old to new schema DB To: sqlalchemy@googlegroups.com Date: Thursday, September 9, 2010, 5:59 AM On Sep 9, 2010, at 6:31 AM, chaouche yacine wrote: Hello list, My schema has changed, and now I want to retrieve my old data (of the old schema
Re: [sqlalchemy] Copying instances from old to new schema DB
Thank you Michael, that's for the catch-the-error part. How about the set-relations-right part, if I decided to go with my fix-as-you go recipe ? do you have any idea ? the dictionary approach seems good, but I still am curious about how to set relations generically on models. I think I'll use it somewhere else in my code. By the way, I think the pseudo should have been : instance = next_instance_from_csv() try: session.commit() except IntegrityError,e : session.rollback() if e.orig == UniqueConstraintError : original_instance = ModelClass.get(instance.id) for relation_name in instance.get_relation_names() : # Is this correct ? path_to_attribute= mapper.+ relation +.inverse # I don't know where to find this ? instance.set_attribute(path_to_attribute,original_instance) session.commit() class BaseModel (DeclarativeBase): ... def set_attribute(self,path,value): nodes= path.explode(.) current_node = self for next_node in nodes : current_node = getattr(current_node,next_node,None) if not current_node: raise InvalidAttribute set_attribute(current_node,value) # sqla's set_attribute, for whatever reason... Y.Chaouche --- On Thu, 9/9/10, Michael Bayer mike...@zzzcomputing.com wrote: From: Michael Bayer mike...@zzzcomputing.com Subject: Re: [sqlalchemy] Copying instances from old to new schema DB To: sqlalchemy@googlegroups.com Date: Thursday, September 9, 2010, 5:59 AM On Sep 9, 2010, at 6:31 AM, chaouche yacine wrote: Hello list, My schema has changed, and now I want to retrieve my old data (of the old schema) to the new database (with the new schema) from csv files (I export the tables of the old database to csv files and then load those csv files to the new database with some column mapping and some gap filling for new columns) The problem is that my new schema have a UniqueConstraint for some tables (like : the name column of the city table should be unique within a country). This constraint was not present in the old schema and data is corrupt (two cities with the same name in the same country). So when I try to insert them in the new database, I have IntegrityErrors. The solution I thought of was : * Catch the IntegrityError exception * If it's a problem on a UniqueConstraint, then the exception was raised because I tried to insert instance B that has the same key as instance A that was inserted before. * So for all children of B (B's relations), set their parent to A. For example, for all citizens of B, set their city to A, beause A and B ought to be the same. * Then, safely ignore B and move on to the next instance. Here's what has been done so far (that works, I just use psuedo code for illustration purpose. If necessary, you can look at the actual attached source files): line = csvloader.next_row() ModelClass = get_current_model() instance = ModelClass.create_instance(**(to_dict(line))) session.add(instance) I wish I could do something like this : try: session.commit() except IntegrityError,e : session.rollback() errror = get_error() if type_of(error) == UniqueConstraintError : original_instance = ModelClass.get(instance.id) for relation in instance.get_relations() : # Is this correct ? instance.relation.inverse = original_instance session.commit() My questions are : how to write get_error, type_of, where to get UniqueContraintError, how to write get_relations, how to set the inverse of a relation (is instance.realtion.inverse the right thing to set ?) and is this approach correct ? There's no portable way to detect unique constraint errors across DBAPIs, you'd have to catch the specific error that you've observed your DBAPI emits as well as the message/codes contained within it, and code against that. SQLAlchemy wraps DBAPI exceptions in its own same-named wrapper and the object emitted by the DBAPI is available under the .orig member. One improvement to the recipe would be if you used savepoints, if supported by your database, so that when you issue rollback() inside a savepoint block, the whole transaction isn't rolled back and you can maintain the whole operation in one transaction. The session can start a savepoint using begin_nested(). When I do csv loads like these however I usually load the full list of identifiers to be checked into memory ahead of time. If the csv is less than 100K rows and represents the full table of data, I just load the whole thing into a dictionary, formatted according to whatever these are the fields that are unique I'm dealing with, and consult the dictionary as I go along. Otherwise, I load individual blocks of data in as I scan through portions of the csv (like, give me all
[sqlalchemy] update a relation from its id
Hello group, Suppose A has a ManyToOne relation to B (A is a child of B). I want to perform something like : a.b_id = b.id assert a.b == b How do I do this in sqlalchemy ? The following attempt failed --- from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base session= sessionmaker()() Base = declarative_base() metadata = Base.metadata metadata.bind = postgres://somedb class SQLModel: reprattr = name def __repr__(self): Will a return string of the form SomeModel fieldn=filedn value... id = 1 if type(self.reprattr) != list : self.reprattr = [self.reprattr] attrstr = .join([%s=\%s\ % (attr, getattr(self,attr)) for attr in self.reprattr if hasattr(self,attr) ] ) reprstr = %s id=%s at 0x%x %s % (self.__class__.__name__, self.id,id(self),attrstr) return reprstr class Country (Base, SQLModel): Creates a Country Object. __tablename__ = countries id= Column(Integer, primary_key=True) name = Column(String, nullable=False) class City (Base, SQLModel): Creates a City Object. __tablename__ = cities id = Column(Integer, primary_key=True) country_id = Column(Integer, ForeignKey(countries.id, ondelete=RESTRICT)) name = Column(String, nullable=False) country= relation(Country, backref=cities) metadata.drop_all() metadata.create_all() algiers = City(name=Algiers) algeria = Country(name=Algeria) session.add_all([algiers,algeria]) session.flush() algiers.country_id = algeria session.flush() print algiers.country # None Any help appreciated. Y.Chaouche -- 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] how to change a database
Why not create a different engine for that database ?
--- On Thu, 12/3/09, Peter vm...@mycircuit.org wrote:
From: Peter vm...@mycircuit.org
Subject: [sqlalchemy] how to change a database
To: sqlalchemy@googlegroups.com
Date: Thursday, December 3, 2009, 4:37 AM
Hi
Lets suppose I created an engine on database 'foo' and I
want to create
a database 'bar' and then do my work on 'bar'.
What is the recommended way to do this ?
connection.execute('CREATE DATABASE IF NOT EXISTS bar')
connection.execute('USE bar')
The former command succeeds with a warning ( already
discussed on this
list ) but the latter seems to be the wrong approach:
...
2009-12-03 13:28:39,221 INFO
sqlalchemy.engine.base.Engine.0x...b0ec COMMIT
TypeError: 'NoneType' object is not callable in
function lambda at
0x8821bc4 ignored
Thanks a lot for your advice
Peter
--
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.
--
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] get_or_create(**kwargs) ?
--- On Sun, 11/22/09, Conor conor.edward.da...@gmail.com wrote: There is a problem with your code when the tag is in the cache: if the tag is added to the session via session.add or a relation add cascade, SQLAlchemy will try to INSERT the tag into the database on the next flush. -Conor I don't know, it seems not. I created a new empty Tags table, I create a new Pylons tag, and it creates it only on the first flush. If I add it a second time to the session, and reflush it, it won't try to re-insert it in the db. Here's my ipython session : In [1]: from someproject.model.tag import * In [2]: setup_all() In [3]: create_all() 12:59:34,878 INFO [sqlalchemy.engine.base.Engine.0x...6fec] SHOW VARIABLES LIKE 'sql_mode' 12:59:34,882 INFO [sqlalchemy.engine.base.Engine.0x...6fec] () 12:59:34,886 INFO [sqlalchemy.engine.base.Engine.0x...6fec] DESCRIBE `Tags` 12:59:34,886 INFO [sqlalchemy.engine.base.Engine.0x...6fec] () 12:59:34,888 INFO [sqlalchemy.engine.base.Engine.0x...6fec] ROLLBACK 12:59:34,889 INFO [sqlalchemy.engine.base.Engine.0x...6fec] CREATE TABLE `Tags` ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(64), PRIMARY KEY (id) ) 12:59:34,907 INFO [sqlalchemy.engine.base.Engine.0x...6fec] () 12:59:34,959 INFO [sqlalchemy.engine.base.Engine.0x...6fec] COMMIT In [4]: pylons = Tag(Pylons) 12:59:59,171 INFO [sqlalchemy.engine.base.Engine.0x...6fec] BEGIN /home/chaouche/PYTHONENV/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/sqlalchemy/engine/default.py:230: SAWarning: Unicode type received non-unicode bind param value 'Pylons' param.append(processors[key](compiled_params[key])) 12:59:59,172 INFO [sqlalchemy.engine.base.Engine.0x...6fec] SELECT `Tags`.id AS `Tags_id`, `Tags`.name AS `Tags_name` FROM `Tags` WHERE `Tags`.name = %s LIMIT 0, 1 12:59:59,172 INFO [sqlalchemy.engine.base.Engine.0x...6fec] ['Pylons'] not in the database In [5]: pylons = Tag(Pylons) In [6]: session.add(pylons) In [7]: session.flush Out[7]: bound method ScopedSession.do of sqlalchemy.orm.scoping.ScopedSession object at 0x98572ac In [8]: session.flush() 13:00:14,116 INFO [sqlalchemy.engine.base.Engine.0x...6fec] INSERT INTO `Tags` (name) VALUES (%s) 13:00:14,116 INFO [sqlalchemy.engine.base.Engine.0x...6fec] ['Pylons'] In [9]: session.add(pylons) In [10]: session.flush() In [11]: pylons = Tag(Pylons) In [12]: pylons Out[12]: Tag Pylons In [13]: session.add session.add session.add_all In [13]: session.add(pylons) In [14]: session.flush() In [15]: -- 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=.
Re: [sqlalchemy] get_or_create(**kwargs) ?
--- On Fri, 11/20/09, Conor conor.edward.da...@gmail.com wrote: Also, there is a recipe that looks for a matching object in the session before querying the database: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject -Conor Thank you Conor for you useful pointer. I have used this recipe and changed a little bit to use beaker as a caching mechanism with a memcached backend. So here's how it looks like : from elixir import Entity, EntityMeta,setup_all,create_all,metadata from pylons import cache class MetaTag(EntityMeta): cache = cache.get_cache(tags,type=memory) def __call__(cls,name): If it's in the cache, return the cached version If not in the cache : If it's in the database, retrieve it, cache it and return it If it's not there, create it, cache it and return it theTag = MetaTag.cache.get_value(key=name,createfunc=lambda:None) if not theTag : #not in the cache theTag = cls.query.filter_by(name=name).first() if not theTag: #not in the database either print not in the database theTag = type.__call__(cls,name) session.add(theTag) #Adding it to the cache, after creating it in the database if it wasn't there MetaTag.cache.set_value(key=name,value=theTag) return theTag class Tag(Entity): __metaclass__ = MetaTag using_options (tablename=Tags) name = Field(Unicode(64)) def __init__(self,name,*args,**kw): Entity.__init__(self,*args,**kw) self.name = name def __repr__(self): return Tag %s % self.name metadata.bind = mysql://username:passw...@localhost:3306/db metadata.bind.echo = True setup_all() create_all() And here's how it plays on the interpreter (ipython) : Alger is already in the database, Constantine is not. I find the metaclass approach very API-friendly. ... In [4]: Tag(Alger) 21:38:21,328 INFO [sqlalchemy.engine.base.Engine.0x...2fec] BEGIN /home/chaouche/PYTHONENV/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/sqlalchemy/engine/default.py:230: SAWarning: Unicode type received non-unicode bind param value 'Alger' param.append(processors[key](compiled_params[key])) 21:38:21,382 INFO [sqlalchemy.engine.base.Engine.0x...2fec] SELECT `Tags`.id AS `Tags_id`, `Tags`.name AS `Tags_name` FROM `Tags` WHERE `Tags`.name = %s LIMIT 0, 1 21:38:21,382 INFO [sqlalchemy.engine.base.Engine.0x...2fec] ['Alger'] Out[4]: Tag Alger In [5]: Tag(Alger) Out[5]: Tag Alger In [8]: Tag(Constantine) /home/chaouche/PYTHONENV/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/sqlalchemy/engine/default.py:230: SAWarning: Unicode type received non-unicode bind param value 'Constantine' param.append(processors[key](compiled_params[key])) 21:39:17,487 INFO [sqlalchemy.engine.base.Engine.0x...2fec] SELECT `Tags`.id AS `Tags_id`, `Tags`.name AS `Tags_name` FROM `Tags` WHERE `Tags`.name = %s LIMIT 0, 1 21:39:17,487 INFO [sqlalchemy.engine.base.Engine.0x...2fec] ['Constantine'] not in the database Out[8]: Tag Constantine In [9]: Tag(Constantine) Out[9]: Tag Constantine Any comments are very appreciated. Y.Chaouche -- 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=.
[sqlalchemy] get_or_create(**kwargs) ?
Hello, This is the first time I try an ORM, and I chose SQLAlchemy, which is popular amongst pythonistas. Since I am a complete beginner, here's my beginner's two cents question : Suppose you have a very basic model like this (pseudo code) : User(firstname,lastname,nick,ManyToOne(city)) City(name) Suppose I want to add a new user: user = User(John,Doe,jd, WhatToPutHere ?) I whish I could write something like this : user = User(John,Doe,jd, City(New Jersey)) Where City will first fetch for a city named New Jersey in the database and return it if it exists, or create a new one (in the database) and return it. So I thought maybe I could have a helper function like this : def city(name): return City.query.filter_by(name=name).first() or City(name=name) and then : user = User(John,Doe,jd,city(New Jersey)) But how can I be sure that the city of New Jersey will be inserted before the user in the database so that the new user row will get the proper city id ? So I figured to rewrite the city helper function like this : def city(name): theCity = City.query.filter_by(name=name).first() if not theCity : theCity = City(name=name) session.commit() # to be sure it will be inserted before the user return theCity In the videostore example I saw an interresting use of the @classmethod decorator, which may be useful to define city as a static method of the City class. I need your advice. Is this a good way to tackle to problem ? how does your code look like ? Y.Chaouche -- 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=.
