[sqlalchemy] How are connections managed?
Afternoon Guys, I have a suspicion that I'm leaving MySQL database connections open when I shouldn't be and I'm trying to understand how they are managed by SQLAlchemy. I currently create an engine instance and bind my session maker too it like this: # Create the engine to the database. engine = create_engine(connection_string, echo=False) # Connect the session. Session = sessionmaker(bind=engine) I then create sessions around my application by using: # Create a new session. session = Session() and once finished with it closing the session like this: # Close the session. session.close() When are new connections established to the database when using this method? And when are they closed again? The only reason I ask is that I've seen a few errors recently on high load instance of my application which struggle to connect to the database, I'm also seeing a few table corruptions and I think they're all related issues from me perhaps creating too many connections. Thanks for any information you can share chaps, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Error writing file
Guys, Got an unusual error this morning when running a query, I keep getting: Error writing file '/tmp/MYHo980S' (Errcode: 28) Thrown at me, any ideas what this is all about? What is it trying to write to the FS? Cheers, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Is this a transaction?
Quick question I hope guys. If I have an object which contains a bunch of children and cascade is set on the relationships. When I add the parent object to the session and commit it, are the children saved as part of a transaction by default? Or do I have to do something special? If I catch any exceptions and use Rollback() on the committal, will all objects be rolled back? Of only the parent? Many thanks guys, sorry if that seems like a dumb question but I just want to be sure. Cheers, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: object_session(remote_device_object) returns noneType
Michael, Thanks for confirming that, seems it was a strong reference to the session which was a local function variable, if was being destroyed and unreferencing my object. I'll look at ways to combat that ;-) Cheers, Heston From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Michael Bayer Sent: 10 October 2008 15:25 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: object_session(remote_device_object) returns noneType On Oct 10, 2008, at 8:03 AM, Heston James - Cold Beans wrote: Morning guys, When calling object_session(my_object), it returns a NoneType object so I'm unable to call commit() and close() on the session, what does this mean? Does it mean my object is not attached to a session? My understanding is that if I did something like: my_object = session.query(object).get(object_id) then pass my_object around the application and wish to commit its session I should be able to use the object_session() method to do that? Is that not correct? that is all correct, however if you remove all strong references to the session, it gets garbage collected and the objects are detached. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Can't connect to local MySQL server
Hello Guys, I'm receiving errors in my application on a fairly regular basis now and I'm not sure how to begin solving it. Please find attached a backtrace for the error. It seems that its struggling to connect to the MySQL server, however I get this after the application has been running and querying the database for some time. Any ideas what might be causing this? I'd appreciate your thoughts. The code which throws the error is a very simple query(some_object).get(id) Cheers all, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 414, in get return self._get(key, ident) File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 1211, in _get return q.all()[0] File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 985, in all return list(self) File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 1073, in __iter__ return self._execute_and_instances(context) File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 1076, in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none(), _state=self._refresh_state) File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line 750, in execute return self.__connection(engine, close_with_result=True).execute( File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line 717, in __connection return self.transaction._connection_for_bind(engine) File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line 326, in _connection_for_bind conn = bind.contextual_connect() File /var/lib/python-support/python2.5/sqlalchemy/engine/base.py, line 1247, in contextual_connect return self.Connection(self, self.pool.connect(), close_with_result=close_with_result, **kwargs) File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 161, in connect return _ConnectionFairy(self).checkout() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 321, in __init__ rec = self._connection_record = pool.get() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 180, in get return self.do_get() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 618, in do_get con = self.create_connection() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 141, in create_connection return _ConnectionRecord(self) File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 217, in __init__ self.connection = self.__connect() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 280, in __connect connection = self.__pool._creator() File /var/lib/python-support/python2.5/sqlalchemy/engine/strategies.py, line 80, in connect raise exc.DBAPIError.instance(None, None, e) OperationalError: (OperationalError) (2002, Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)) None None
[sqlalchemy] Limit to 500 records after particular date.
Morning Guys, I hope this'll be a fairly simple question. I have a query which looks something like this: the_objects = session.query(myobject.myobject).filter(myobject.created :lastrecord).params(lastrecord=time.strftime(%Y-%m-%d %H:%M:%S, from_date)).all() This grabs all the records after a particular date and time. What I WANT it to achieve though is to grab the first 1000 records that happen after a period in time. How would you suggest that I handle my limits and order_by's on this? It's important that no records get missed out you see so I need to be totally sure that the 1000 records returned are the first 1000 records in chronological order after 'from_date'. I'm using SQLAclchemy 0.5 and MySQL 5 I appreciate any advice you can offer. Cheers, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Storing UTC Dates
Hello Guys, This might seem like a bit of a naive question but I'm looking for your advice. Being from the UK we operate on Daylight Savings Time which gives us a one hour offset on times for a few months of the year. I currently have a DateTime column which is declared like so: created = Column(DateTime, default=func.now()) modified = Column(DateTime, default=func.now(), onupdate=func.now()) Which generally works very well, when I create a record it inserts the current locale time into the column, however, it stores the datetime with DST applied too it. As I use the datetime at a later point for posting over web services I really need to store the UTC version of now() in the database, without DST applied to it. How can I modify the above column definition to do this? Can I simply use something instead of func.now()? I was given the advise to use func.now() by someone but not really sure what it returns, is it a datetime.datetime object? Or a time tuple? Or is there a parameter I can pass to Column() or DateTime() which will ensure it uses the UTC format of the date when creating and modifying records? Many thanks guys, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Storing UTC Dates
Hi Werner, IIUC func.now is a database function. Ah, ok, that makes fair sense. You should be able to use datetime instead i.e.: created = Column(DateTime, default=datetime.datetime.utcnow) modified = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) Yes, this worked just great Werner, I've used that and it seems to have done the job! I hoped it would be that simple :-) Thanks again, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] DateTime Column as Python time tuple.
Hello Guys, I'm looking to send an object from SQLAlchemy across a ZSI web service as a complex type. To do this ZSI requires that datetime's in the objects be in Python Time Tuples as documented in the 'time' module. It looks as if by default SQLAlchemy uses datetime.datetime objects for columns defined like: created = Column(DateTime, default=func.now()) Is there any way in which I can configure SQLAlchemy to return me time tuples instead? Or perhaps a cheeky way I can convert from datetime.datetime to time.time? Cheers all, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapper extensions in declerative.
Hi Kyle, Thanks for the really thorough response, it seems you know what you're on about :-) I agree with you that it would likely be a foolish decision to rely on undocumented behaviour, this will likely come back to bite me at some point in the future. I'm going to take all these ideas away with me now, I've also upgraded to version 0.5 this morning to get the best out of the ORM, I had been holding off as it was only in the experimental Debian repos but I'm sure its stable enough, I've seen a few recent posts about it being almost production ready. I'm going to heavily assess the way I'm currently using my session, it is a complete and utter mess at the moment and I'm quite sure that it needs to be refactored, its only a small application so won't take me long. Once I'm comfortable with the way in which that is behaving I'll start to look at implementing these hooks for the file saves and see how it gets along. It's quite clear in my mind now and seems like a fairly safe approach to it, I certainly can't see any downsides to it just yet anyway. I'll keep you posted over the next few days as to my progress and we can then perhaps review the approach as needed. Thanks again mate, I really appreciate you taking the time to be so thorough. Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Run basic query
Guys, I want to run a query which doesn't return any objects, just simply modifies all records in the table, like so: UPDATE foo SET bar = 0 How can I do this using SQLAlchemy? Is it possible and 'proper' for me to just pass this query as a string to be executed? Or is there a better 'sqlalchemy' style of doing this? Should I be pulling all the records from the db, modifying them and then resaving them? Seems like a heavy workload. Cheers, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Save file to FS along with object database commit.
Afternoon All, Hello Guys, I have an object which I save to the database using SQLAlchemy, the class is defined using declarative and has a whole bunch of properties. This object has one property though which isn't saved to the database, but to the file system. It is basically a binary string of a files contents. I essentially want to write this binary content to the file system when the record is created in the database, and likewise, delete the file from the FS when the record is removed from the database. I understand all the basics of writing a file, it's just a case of how to implement this into the ORMified object. How would you handle this? Presumably it'll involve creating a couple of methods in the class which handle the file save/delete process, but how do I tie these to the save/delete methods of the ORM? And furthermore, are there any smart ways of making this transactional so if either the database write fails or the file write fails then the they are both rolledback so I don't end up with db records without files and files without dbrecords? I'd really appreciate your thoughts on the cleanest way to implement this. Heston I wanted to bump this topic as I'm still a little confused as to how it might implement. I've been doing a little reading this afternoon about mapper extensions, would this be a decent use of that do you think for after_create and after_delete? I really appreciate your input guys, being new to the ORM scene and a lonely programmer with no one to bounce ideas off this has got me baffled. Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Save file to FS along with object database commit.
Hello Guys, I have an object which I save to the database using SQLAlchemy, the class is defined using declarative and has a whole bunch of properties. This object has one property though which isn't saved to the database, but to the file system. It is basically a binary string of a files contents. I essentially want to write this binary content to the file system when the record is created in the database, and likewise, delete the file from the FS when the record is removed from the database. I understand all the basics of writing a file, it's just a case of how to implement this into the ORMified object. How would you handle this? Presumably it'll involve creating a couple of methods in the class which handle the file save/delete process, but how do I tie these to the save/delete methods of the ORM? And furthermore, are there any smart ways of making this transactional so if either the database write fails or the file write fails then the they are both rolledback so I don't end up with db records without files and files without dbrecords? I'd really appreciate your thoughts on the cleanest way to implement this. Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Boolean, Declerative, MySQL 5.2
Hello Guys, I'm looking to store a Boolean value in a MySQL 5.2 database. I'm then going to describe a class for the table using declarative and have a couple of questions on this: What Datatype should my table column be set to in MySQL? And likewise, when declaring the column using declarative, which data type should I use? Column(Boolean)? Cheers guys, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Boolean, Declerative, MySQL 5.2
I am using Column(Boolean) with declarative and MySQL and it is working fine. In MySQL itself the type is 'tinyint(1)' but they provide 'bool' and 'boolean' as synonyms if you prefer. Bobby, Thank you for this, I went with the tinyint(1) and it seems to be working great! Thanks, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Injecting business objects into constructor
Hi Rick, I'm not sure where this is going with the 0.5 version, but I believe that MappedClass.__int__ is still not called when objects are loaded from the DB. If that's the case, and there isn't some alternate that SA provides like MappedClass.__onload__, You can look into Mapper Extensions to provide this. Check out the 'create_instance' method. That sounds ok to me, I'll to take a look into those options and see which best suits me. Are you aware of any IoC frameworks which have been adapted to inject/autowire things into SQLAlchemy transient business objects? Cheers Rick, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Ensure Get() always returns a result.
Morning Guys, I'm looking to build a uniform method for getting/creating instance of my objects from the database. At the moment I've been using query(SomeObject).get(object_id) to return the objects from the DB, however, it would be really great if there were a method which always returned a result, so, if I pass in an id of '354' to the get() method and no record with that ID exists in the database, then it just returns a new empty instance of the SomeObject class with the id set to 354. Does such a method exist on the ORM? If not then how would you go about doing so? I came about to this way of thinking when I've been having problems instantiating my own instances of classes which are decoratively mapped to the database. So if I try to create a new instance of a class like so: Import SomeObject New_insatnce = SomeObject.SomeObject(354) Then I get an error thrown about how I'm not able to assign a str value to attributes which are expecting a mapped instance, presumably a relationship. Am I thinking straight here? Or does SQLAlchemy have a separate method for creating new clean instances of its objects? Cheers, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Joined Query
Afternoon Chaps, I've got a query here which I've been looking to reconstruct from the standard SQL into a SQLAlchemy statement which will return a list of objects but I'm really struggling to make any headway on it, I'm hoping you'll be able to offer me some help. I have two objects in my application, 'event' and 'message' and they have a M21 relationship defined between them in the event class using declarative. Below is the standard SQL for the query in question: SELECT message.message_id FROM event INNER JOIN message ON event.message_id = message.message_id WHERE CURRENT_DATE = Date_Started AND CURRENT_DATE = COALESCE(date_ended,CURRENT_DATE) AND (Is_All_Day = 1 OR CURRENT_TIME BETWEEN Time_Started AND Time_Ended) AND ( Repeat_Type = 0 OR Repeat_Type = 1 OR Repeat_Type = 2 AND MOD( TO_DAYS(CURRENT_DATE) - TO_DAYS(Date_Started),7) = 0 OR Repeat_Type = 3 AND MOD( TO_DAYS(CURRENT_DATE) - TO_DAYS(Date_Started),14) = 0 OR Repeat_Type = 4 AND DAYOFMONTH(CURRENT_DATE) = DAYOFMONTH(Date_Started) OR Repeat_Type = 5 AND DAYOFYEAR(CURRENT_DATE) = DAYOFYEAR(Date_Started) OR Repeat_Type = 6 AND DAYOFWEEK(CURRENT_DATE) IN (2,3,4,5,6) OR Repeat_Type = 7 AND DAYOFWEEK(CURRENT_DATE) IN (1,7) ) AND NOT EXISTS (SELECT 1 FROM event_exempt WHERE event_id = event.event_ID AND event_date = CURRENT_DATE) The standard SQL there just returns the message.message_id, but obviously, now we're using an ORM I'm looking to adapt this so that it returns a list of 'message' objects to me. Can anyone offer some sound advice and help on this? I've been playing around with this using query().join() but keep hitting up against all kinds of brick walls as this is all so new to me. Thanks guys, I really appreciate the help you've been putting forward the past few days. Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Injecting business objects into constructor
Hi Michael, theres some experiments in IoC for Python if you google around for dependency injection python, but the Python way is usually focused around not really needing thick layers of abstraction like that. Thanks for that. I did do some googling around a while back when first starting out with python and found it was slightly slim pickings, if this isn't really the 'pytonic' way of doing things then that would likely explain why :-) It's no great challenge to me at the moment so I'll find some other more proven methods to get my services injected. Thanks, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: M2M relationship
Hi Michael, create a file called something like globals.py, and in all other modules that use SQLAlchemy, say import globals. A primer on modules, packages and such is at http://www.python.org/doc/tut/node8.html Excellent! This seems to have done the job, I am now successfully saving and pulling these objects from the database, very cool! Thank you again. Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Filter by optional attributes.
Good morning guys, I'm looking for a way in which I can query my database for records which meet multiple, optional arguments. I'm looking to encapsulate access to this using a service layer, I want to create a method like this: def get_foos(self, foo_id=, foo_firstname=, foo_lastname=): foos = session.query(Foo).filter_by(foo_id=foo_id, foo_firstname=foo_firstname, foo_lastname=foo_lastname) return foos Now, the thing I'm struggling with is how to make all those attributes optional. Because at the moment I call that method like so: get_foos(foo_id=2) I get no results returned because SQLAlchemy is looking for records that not only have an id of 2 but also have a first and last name which is an empty string. What I would ideally like to do is only have it filter by the arguments for getfoos() which are not empty strings. This would allow me to build a more reusable query method. Is this possible? And how would one achieve it? Cheers, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Filter by optional attributes.
Hi Svil:
use keywordargs as dictionary, i.e.
...query.filter( **kwargs)
where the kwargs are a dict made by u containing only the required
fields. e.g.
kwargs={}
if foo_id: kwargs['fooid']=fooid
That sounds like a fair enough solution to me, seems safer than the more
generic version.
Thanks for the tip mate, sounds really great. I'll play around with that
concept.
Heston
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Filter by optional attributes.
Hello Again Svil: That sounds like a fair enough solution to me, seems safer than the more generic version. Thanks for the tip mate, sounds really great. I'll play around with that concept. Heston I've tested this little concept and it works really nicely :-D thanks. One quick question I'd like to pick your brain on. With filter(), is that filter applied at the SQL level? Or are _all_ records returned from the database and then SQLAlchemy applies the filter? I'm just trying to get an idea of performance when we have many records, I'd hate to be pulling them _all_ from the database with each query. Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Filter by optional attributes.
Hi, pass an echo=True to the create_engine() (or whereever else u could pass that) and u'll see the sql. Ok, I see! Perfect! I've just configured logging on this so I can keep track, looks excellent. Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Injecting business objects into constructor
Afternoon Guys, In my classic non-orm based applications I would usually inject other business object instances into my classes for such things as logging, emailing and all manner of other things. For instance: class foo: def __init__(self, logger, email_service, foo_id=, foo_firstname=): self.logger = logger self.email_service = email_service self.foo_id = foo_id self.foo_firstname = foo_firstname Now that I'm using the ORM I ask the ORM for these objects, however, it obviously just hands me back an instance with all its properties loaded from the database, what is essentially a glorified dict or 'dumb' object. How would you go about getting these business objects into the objects returned by the ORM? Would you have your service layer set them before returning the object? Or can we somehow configure the ORM to do it for us? Thanks guys, I'm really enjoying this. Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: M2M relationship
Hi Michael, what I see immediately is that you're declaring mutliple declarative_bases and multiple MetaData objects. All of the Table objects which relate to one another need to share the same underlying MetaData object, and the declarative_base() function also uses a MetaData object which it creates for you, unless one is passed. So you need a global module everyone works from which starts with something like: Thank you for pointing this out, it certainly sounds like it could be the problem, it would explain why none of the tables seem to know about one another :-D I have a singleton module which is a factory for my database connections which gets passed around into the beans, I will place these metadata and declarative base classes into that and see how that helps. I'll give this a try in the next few hours and let you know how I get on. Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: M2M relationship
Hello Michael, what I see immediately is that you're declaring mutliple declarative_bases and multiple MetaData objects. All of the Table objects which relate to one another need to share the same underlying MetaData object, and the declarative_base() function also uses a MetaData object which it creates for you, unless one is passed. So you need a global module everyone works from which starts with something like: meta = MetaData() Base = declarative_base(metadata=meta) then every Table uses the above meta as its metadata argument, every declared mapped class inherits from Base. Thank you kindly for this concept, I really appreciate your advice thus far. I'm still struggling with this same challenge though, I've got a horrible dose of n00bitus I'm afraid. Above you talk about a global module in the application which creates the Base and metadata, but I don't understand how these can then be accessed by other classes around the application? Do you have any good sample code or a link to a decent tutorial? Seems all I can find are examples which are based on the idea that all the classes are defined in the same module, which isn't practical. I really appreciate the help Michael, thanks. Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: M2M relationship
Hi Michael, declarative places a convenience __init__ that installs keywords as attributes, but you're free to override this constructor with anything you'd like. Thank you for confirming this for me, I'd hoped I'd be able to override the class constructor, I often use it for considerably more than basic property setting and it would be a shame if declarative had upset that. I'm still yet to solve this problem, don't have any ideas what I'm doing wrong do you? Did you see the code examples I attached? Am I approaching this in the correct manor? Thanks, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: M2M relationship
i'm sorry for my misleading reply;( i was kind of too sleepy last night;P No problem my man. Heston. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to MySQL
Column('created', DateTime, default=func.now()),
Column('updated', DateTime, onupdate=func.now()))
You can set both default= and onupdate= on the same Column if you want
'updated' to be non-NULL on insert.
That sounds like a nice clean way of doing this Jason, I'm more than happy with
that, it seems the most logical way of implementing it.
Cheers for the advice,
Heston
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to MySQL
Hello Rick, These mapper extensions look very good, I've used a similar concept in other ORM's in the past for all manner of things and have a couple of decent ways to utilize them in this current application. Cheers, Heston From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Rick Morrison Sent: 10 July 2008 17:37 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Connecting to MySQL That's exactly what the problem was :-) Is there any reason I should avoid using 0.5? I'm running python 2.4 at the moment, are they compatible? 0.5 is still in beta, and I don't have much experience with it myself, but if were just starting out, I would probably be using that, otherwise you'll need to migrate later; it's easier to just start out with the new API. Next quick question: I have a habbit of using 'created' and 'modified' columns on my tables, is there any way in which I can have the ORM update the dates for me when creating and modifying rows? Yes, check out mapper extensions in the docs, you're going to want after_insert and after_update extensions. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] M2M relationship
Good morning all, So, this morning's challenge has been learning many-to-many relationships, after reading through the tutorial I understand most of the core concepts of how it should work but I'm struggling to actually make it do so, I thought I would come and rely on you good people to help me in the right direction. I have 3 tables configured in my MySQL database, for arguments sake let's say they're called 'post', 'keyword' and 'post_keyword'. I'm declaring my 'post' class like so in a module called post.py: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey from sqlalchemy.orm import relation, backref # Configure the delarative base for SQL Alchemy. Base = declarative_base() # Define the Remote Device class. class post(Base): # Define the table for SQL Aclchemy __tablename__ = post # Define the class properties for SQL Alchemy id = Column(String, primary_key=True) content = Column(String) keywords = relation(keyword, secondary=post_keyword, backref='keywords') I then have a pretty much identical class declaration for 'post' but with the obvious changes to its name and property. However, when trying to use this class I get an exception thrown by SQLAlchemy saying: NameError: name 'remote_device_message' is not defined Which is fair enough, as it isn't, I wonder if I'm meant to import it somehow into that post class? All the examples I've found seem to focus on the idea of A) having these two classes defined in the same file and B) using an in memory database where you 'create' the association table in the script, whereas with mine it already exists in the database. It's making me a little confused I think. I'd really appreciate your help on showing me how this implements. Cheers, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: M2M relationship
NameError's are thrown usualy by import'ing or similar mechanisms. have a look on your code. eventualy post the whole traceback? Hello Mate, I think you're right, but the problem is that I don't know what I 'should' be importing into the class. See, I have two files; Post.py and Keyword.py, both of which contain a single class of the same name. I want to form a M2M relationship between them. If I want to declare a relationship in Post.py do I have to import Keyword.py? and what about the joining/association table? Do I need to create a class which represents that too? Cheers, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: M2M relationship
the association table is an instance of Table, and does not need its own class. It's easiest to declare the association table in the same module as that which it is used, in this case post.py. Ok this sounds fine, I've done this now, declaring the table in the post.py module. When you create post.py, that creates Post, and in order to create the relation either needs to import keyword.py directly, or, when a third module uses post.py it would have to also import keyword.py before using the Post class. That's fine too, I've now imports keyword into post. I'm now getting a new error thrown at me though: File /var/lib/python-support/python2.5/sqlalchemy/schema.py, line 788, in column foreign key % tname) sqlalchemy.exceptions.NoReferencedTableError: Could not find table 'post' with which to generate a foreign key This is unusual as the table post does exist in the database, and is also declared as the class in the post.py module. Any ideas? Thanks Michael, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: M2M relationship
if u look up the stacktrace/traceback, u'll see which statement in
your own code triggered the error. is it in the mapping-part or is
still in table-declaration part?
do all 3 tables use same metadata?
Thank you for your comments so far, I appreciate you helping me out on this.
The entire stack trace is below:
Traceback (most recent call last):
File test.py, line 9, in module
my_device = post.post(3F8ADE52-4F63-11DD-9AF0-90BB55D89593, Title,
Content)
File /var/lib/python-support/python2.5/sqlalchemy/orm/attributes.py,
line 1211, in init
extra_init(class_, oldinit, instance, args, kwargs)
File /var/lib/python-support/python2.5/sqlalchemy/orm/mapper.py, line
798, in extra_init
self.compile()
File /var/lib/python-support/python2.5/sqlalchemy/orm/mapper.py, line
350, in compile
mapper.__initialize_properties()
File /var/lib/python-support/python2.5/sqlalchemy/orm/mapper.py, line
371, in __initialize_properties
prop.init(key, self)
File /var/lib/python-support/python2.5/sqlalchemy/orm/interfaces.py,
line 374, in init
self.do_init()
File /var/lib/python-support/python2.5/sqlalchemy/orm/properties.py,
line 467, in do_init
self.__determine_joins()
File /var/lib/python-support/python2.5/sqlalchemy/orm/properties.py,
line 521, in __determine_joins
self.secondaryjoin = _search_for_join(self.mapper,
self.secondary).onclause
File /var/lib/python-support/python2.5/sqlalchemy/orm/properties.py,
line 514, in _search_for_join
return sql.join(mapper.local_table, table)
File /var/lib/python-support/python2.5/sqlalchemy/sql/expression.py,
line 116, in join
return Join(left, right, onclause, isouter)
File /var/lib/python-support/python2.5/sqlalchemy/sql/expression.py,
line 2275, in __init__
self.onclause = self.__match_primaries(self.left, self.right)
File /var/lib/python-support/python2.5/sqlalchemy/sql/expression.py,
line 2317, in __match_primaries
return sql_util.join_condition(primary, secondary)
File /var/lib/python-support/python2.5/sqlalchemy/sql/util.py, line 74,
in join_condition
col = fk.get_referent(a)
File /var/lib/python-support/python2.5/sqlalchemy/schema.py, line 755,
in get_referent
return table.corresponding_column(self.column)
File /var/lib/python-support/python2.5/sqlalchemy/schema.py, line 788,
in column
foreign key % tname)
sqlalchemy.exceptions.NoReferencedTableError: Could not find table 'post'
with which to generate a foreign key
I've also attached the two modules and the test script I'm trying to use, if
you wouldn't mind taking a look an letting me know what I'm doing wrong, I
would really appreciate it.
Cheers,
Heston
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
# Class Imports
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
# Configure the delarative base for SQL Alchemy.
Base = declarative_base()
# Define the Remote Device class.
class keyword(Base):
# Define the table for SQL Aclchemy
__tablename__ = keyword
# Define the class properties for SQL Alchemy
keyword_id = Column(Integer, primary_key=True)
word = Column(String)
# I'm the class constructor method.
def __init__(self, keyword_id=, word=):
self.keyword_id = keyword
self.word = word
# Class Imports
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import relation, backref
import keyword
# Configure the delarative base for SQL Alchemy.
Base = declarative_base()
metadata = MetaData()
post_keyword = Table(post_keyword, metadata,
Column(post_id, String, ForeignKey('post.post_id')),
Column('keyword_id', Integer,
ForeignKey('keyword.keyword_id'))
)
# Define the Remote Device class.
class post(Base):
# Define the table for SQL Aclchemy
__tablename__ = post
# Define the class properties for SQL Alchemy
post_id = Column(String, primary_key=True)
title = Column(String)
content = Column(String)
# many to many BlogPost-Keyword
keywords = relation(keyword.keyword, secondary=post_keyword, backref=post)
# I'm the class constructor method.
def __init__(self, post_id=, title=, content=):
self.post_id = post_id
self.title = title
self.content = contentimport post
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine(connectionstring, echo=False)
Session =
[sqlalchemy] Re: Connecting to MySQL
Session.add is a version 0.5 method, you're maybe running 0.4.6? In the 0.4.x series, it's going to be: Session.save() for objects that are to be newly added to the session Session.update() for objects that are already in the session, or Session.save_or_update() to have the library figure it out as it does for Session.add in v0.5.x Hi Rick, That's exactly what the problem was :-) Is there any reason I should avoid using 0.5? I'm running python 2.4 at the moment, are they compatible? Next quick question: I have a habbit of using 'created' and 'modified' columns on my tables, is there any way in which I can have the ORM update the dates for me when creating and modifying rows? Cheers, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
