Re: [sqlalchemy] custom __init__ methods not being invoked
See http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#constructors-and-object-initialization On Mon, Jan 7, 2013 at 4:47 AM, RM ryan.mckil...@gmail.com wrote: I have a class which inherits from Base. My class has a metaclass which inherits from DeclarativeMeta. Among other things, the metaclass adds an __init__ method to the class dictionary. When I instantiate an instance of my class directly, my __init__ method is invoked, but if I use the ORM to retrieve an instance, my __init__ method is not invoked. A metaclass serves better than a mixin for what I am trying to accomplish. However, I did experiment with a mixin and saw the same behavior as described above. Any ideas? Many thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/oDj_bHNvP7EJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] custom __init__ methods not being invoked
Worked like a charm. Thanks. — RM On Mon, Jan 7, 2013 at 6:26 PM, Michael van Tellingen michaelvantellin...@gmail.com wrote: See http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#constructors-and-object-initialization On Mon, Jan 7, 2013 at 4:47 AM, RM ryan.mckil...@gmail.com wrote: I have a class which inherits from Base. My class has a metaclass which inherits from DeclarativeMeta. Among other things, the metaclass adds an __init__ method to the class dictionary. When I instantiate an instance of my class directly, my __init__ method is invoked, but if I use the ORM to retrieve an instance, my __init__ method is not invoked. A metaclass serves better than a mixin for what I am trying to accomplish. However, I did experiment with a mixin and saw the same behavior as described above. Any ideas? Many thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/oDj_bHNvP7EJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
I am looking to adapt this code for a related array/type issue. The code from https://gist.github.com/4433940 works just fine for me (as expected) when building/executing the stmt directly, but not when using the ORM. When row is created using ORM, like this: # snip s = Session(bind=engine) e = Example() e.timestamp=datetime.datetime.utcnow(), e.num=2, e.guids = [uuid.uuid4(), uuid.uuid4()] s.add(e) s.commit() I get an error like this: snip return getter(visitor)(self, **kw) File /home/hans/workspace/providence/env/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py, line 760, in visit_bindparam bind_expression = bindparam.type.bind_expression(bindparam) File test_array.py, line 38, in bind_expression for uuid_val in val TypeError: 'object' object is not iterable (I can dump in full stack if that would be helpful.) Indeed, inspecting that reveals that it is simply an object() instance. I'm not sure where that is being set or whether there is an obvious workaround here. I'm sure I'm simply missing obvious when it comes to dealing with native array types and ORM entity instances. Thanks, Hans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZwxFp2iasq0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
this is ticket http://www.sqlalchemy.org/trac/ticket/2648 and cannot be worked around at this time. If you're working with arrays of UUID I'd recommend using psycopg2 type processors, as the previous poster has had success with. On Jan 7, 2013, at 9:39 AM, Hans Lellelid wrote: I am looking to adapt this code for a related array/type issue. The code from https://gist.github.com/4433940 works just fine for me (as expected) when building/executing the stmt directly, but not when using the ORM. When row is created using ORM, like this: # snip s = Session(bind=engine) e = Example() e.timestamp=datetime.datetime.utcnow(), e.num=2, e.guids = [uuid.uuid4(), uuid.uuid4()] s.add(e) s.commit() I get an error like this: snip return getter(visitor)(self, **kw) File /home/hans/workspace/providence/env/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py, line 760, in visit_bindparam bind_expression = bindparam.type.bind_expression(bindparam) File test_array.py, line 38, in bind_expression for uuid_val in val TypeError: 'object' object is not iterable (I can dump in full stack if that would be helpful.) Indeed, inspecting that reveals that it is simply an object() instance. I'm not sure where that is being set or whether there is an obvious workaround here. I'm sure I'm simply missing obvious when it comes to dealing with native array types and ORM entity instances. Thanks, Hans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZwxFp2iasq0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
correction, this seems to work, though will try to improve: class UUID_ARRAY(TypeDecorator): impl = ARRAY(UUID, dimensions=1) def bind_expression(self, bindvalue): if bindvalue.callable: val = bindvalue.callable() else: val = bindvalue.value if val is None: val = [] elif not hasattr(val, '__iter__'): return bindvalue return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) On Jan 7, 2013, at 9:55 AM, Michael Bayer wrote: this is ticket http://www.sqlalchemy.org/trac/ticket/2648 and cannot be worked around at this time. If you're working with arrays of UUID I'd recommend using psycopg2 type processors, as the previous poster has had success with. On Jan 7, 2013, at 9:39 AM, Hans Lellelid wrote: I am looking to adapt this code for a related array/type issue. The code from https://gist.github.com/4433940 works just fine for me (as expected) when building/executing the stmt directly, but not when using the ORM. When row is created using ORM, like this: # snip s = Session(bind=engine) e = Example() e.timestamp=datetime.datetime.utcnow(), e.num=2, e.guids = [uuid.uuid4(), uuid.uuid4()] s.add(e) s.commit() I get an error like this: snip return getter(visitor)(self, **kw) File /home/hans/workspace/providence/env/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py, line 760, in visit_bindparam bind_expression = bindparam.type.bind_expression(bindparam) File test_array.py, line 38, in bind_expression for uuid_val in val TypeError: 'object' object is not iterable (I can dump in full stack if that would be helpful.) Indeed, inspecting that reveals that it is simply an object() instance. I'm not sure where that is being set or whether there is an obvious workaround here. I'm sure I'm simply missing obvious when it comes to dealing with native array types and ORM entity instances. Thanks, Hans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZwxFp2iasq0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
change again, that doesn't work. Upon reflection, I think the case here is that there's no alternative but to make sure psycopg2 can properly format the contents of the ARRAY itself. This is because SQLAlchemy is producing a completed INSERT statement for preparation, without the parameters actually filled in: INSERT INTO table (x) VALUES (%(x)s) At that point, SQLAlchemy is done producing strings, and sends it off to psycopg2 along with an array value for x. So there is no opportunity here for a user-defined bind expression generator to further modify the expression above.More fundamentally, the above statement can be called using DBAPI executemany(); each value for x can be an array of a *different* length. So it's totally up to psycopg2 here to handle this case, and it even suggests that prepared statements can't be used very effectively with Postgresql ARRAY types (psycopg2 doesn't use prepared statements AFAIK). On Jan 7, 2013, at 9:58 AM, Michael Bayer wrote: correction, this seems to work, though will try to improve: class UUID_ARRAY(TypeDecorator): impl = ARRAY(UUID, dimensions=1) def bind_expression(self, bindvalue): if bindvalue.callable: val = bindvalue.callable() else: val = bindvalue.value if val is None: val = [] elif not hasattr(val, '__iter__'): return bindvalue return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) On Jan 7, 2013, at 9:55 AM, Michael Bayer wrote: this is ticket http://www.sqlalchemy.org/trac/ticket/2648 and cannot be worked around at this time. If you're working with arrays of UUID I'd recommend using psycopg2 type processors, as the previous poster has had success with. On Jan 7, 2013, at 9:39 AM, Hans Lellelid wrote: I am looking to adapt this code for a related array/type issue. The code from https://gist.github.com/4433940 works just fine for me (as expected) when building/executing the stmt directly, but not when using the ORM. When row is created using ORM, like this: # snip s = Session(bind=engine) e = Example() e.timestamp=datetime.datetime.utcnow(), e.num=2, e.guids = [uuid.uuid4(), uuid.uuid4()] s.add(e) s.commit() I get an error like this: snip return getter(visitor)(self, **kw) File /home/hans/workspace/providence/env/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py, line 760, in visit_bindparam bind_expression = bindparam.type.bind_expression(bindparam) File test_array.py, line 38, in bind_expression for uuid_val in val TypeError: 'object' object is not iterable (I can dump in full stack if that would be helpful.) Indeed, inspecting that reveals that it is simply an object() instance. I'm not sure where that is being set or whether there is an obvious workaround here. I'm sure I'm simply missing obvious when it comes to dealing with native array types and ORM entity instances. Thanks, Hans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZwxFp2iasq0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
Thanks for the follow up. Upon further reading/reflection, I discovered that wrapping my string IP address values in the psycopg2.extras.Inet object and then passing that in -- and executing psycopg2.extras.register_inet() as with the UUID example -- seems to work fine for both ORM and non-ORM. So, it sounds like I can get the data into the right form without too many hoops. I will probably create a custom TypeDecorator that simply wraps incoming values in the Inet type (or convert from python netaddr objects into pg's Inet), but that seems like a relatively straightforward customization. Thanks again! Hans On Monday, January 7, 2013 10:09:53 AM UTC-5, Michael Bayer wrote: change again, that doesn't work. Upon reflection, I think the case here is that there's no alternative but to make sure psycopg2 can properly format the contents of the ARRAY itself. This is because SQLAlchemy is producing a completed INSERT statement for preparation, without the parameters actually filled in: INSERT INTO table (x) VALUES (%(x)s) At that point, SQLAlchemy is done producing strings, and sends it off to psycopg2 along with an array value for x. So there is no opportunity here for a user-defined bind expression generator to further modify the expression above.More fundamentally, the above statement can be called using DBAPI executemany(); each value for x can be an array of a *different* length. So it's totally up to psycopg2 here to handle this case, and it even suggests that prepared statements can't be used very effectively with Postgresql ARRAY types (psycopg2 doesn't use prepared statements AFAIK). On Jan 7, 2013, at 9:58 AM, Michael Bayer wrote: correction, this seems to work, though will try to improve: class UUID_ARRAY(TypeDecorator): impl = ARRAY(UUID, dimensions=1) def bind_expression(self, bindvalue): if bindvalue.callable: val = bindvalue.callable() else: val = bindvalue.value if val is None: val = [] elif not hasattr(val, '__iter__'): return bindvalue return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) On Jan 7, 2013, at 9:55 AM, Michael Bayer wrote: this is ticket http://www.sqlalchemy.org/trac/ticket/2648 and cannot be worked around at this time. If you're working with arrays of UUID I'd recommend using psycopg2 type processors, as the previous poster has had success with. On Jan 7, 2013, at 9:39 AM, Hans Lellelid wrote: I am looking to adapt this code for a related array/type issue. The code from https://gist.github.com/4433940 works just fine for me (as expected) when building/executing the stmt directly, but not when using the ORM. When row is created using ORM, like this: # snip s = Session(bind=engine) e = Example() e.timestamp=datetime.datetime.utcnow(), e.num=2, e.guids = [uuid.uuid4(), uuid.uuid4()] s.add(e) s.commit() I get an error like this: snip return getter(visitor)(self, **kw) File /home/hans/workspace/providence/env/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py, line 760, in visit_bindparam bind_expression = bindparam.type.bind_expression(bindparam) File test_array.py, line 38, in bind_expression for uuid_val in val TypeError: 'object' object is not iterable (I can dump in full stack if that would be helpful.) Indeed, inspecting that reveals that it is simply an object() instance. I'm not sure where that is being set or whether there is an obvious workaround here. I'm sure I'm simply missing obvious when it comes to dealing with native array types and ORM entity instances. Thanks, Hans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZwxFp2iasq0J. To post to this group, send email to sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. 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 sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. 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 sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. For more options, visit this
Re: [sqlalchemy] How to handle 'sub-commits'?
On Jan 7, 2013, at 1:11 AM, Ken Lareau wrote: Okay, this is what I suspected and feared. :) Creating new sessions isn't much of an issue, and I came up with a class to manage this for me before realizing my problem is going to end up being much deeper... My current library that uses SQLAlchemy was based off a single session... so in a file called meta.py I had the following line: Session = scoped_session(sessionmaker()) (swiped from an old Pylons example). In my __init__.py file, I did: from tagopsdb.database.meta import Session and then in when initializing my connection to the database, simply did: Session.configure(bind=engine) From all other areas of the library, I simple re-used the aforementioned import, then actually directly used Session, such as: Session.add(obj) or Session.commit() Now... this may be very poor usage of it, though I'm trying to improve my under- standing and utilize SQLAlchemy better. that's pretty much the usage we've encouraged for a long time, the Session is basically the main Session, I think its OK. Of course, with the sudden need for more than one session, I'm finding that I'm running into an issue. Specifically: 1) To make it easy to find the correct session, I'm using a dictionary which I pass around instead of Session... but referencing a given session is a bit clunky - 'sessions.current[name]' is quite a bit less succinct than the use of just 'Session', and while I could just assign a given entry to a shorter name, that just seems to add to the mistake. :) Depending on the usage pattern here, if the need for the extra transaction is localized, then I'd be using an explicit passing pattern for this second Session - that is, not using a global registry. If the case is more like large amounts of code are split 50/50 between these two Sessions, then I'd possibly use a second Session registry. Since this Session is more intended as an ad-hoc commit Session though I might stick to keeping it as a non-global object. 2) All the methods in my library currently expect the session to be 'Session'; that changes with the need for multiple sessions, and it means either I will now need to explicitly pass the session into every method, or find a way to have the session automatically determined... which may not be possible or reasonable. OK, well there's another path here, which is that you can affix a second Session object to your registry temporarily. existing = Session.registry() # current Session Session.registry.set(my_temporary_session) # set a different Session ... call functions ... # restore the original Session.registry.set(existing) Depending on the database in use, using low isolation levels can have the effect that other transactions can view dirty reads as the transaction proceeds, but this is obviously an all-or-nothing thing.When I need certain resources exposed during a long running transaction, I transfer that data to a different Session and commit() those changes distinctly. My current needs would tend to use the short transactions for things that are mostly isolated from anything going on in the longer running (main) transaction, though I do suspect I might need what you mention in your last sentence, but might you be able to refer me to an example of how it would work, perchance? I'd advise against going this route, you'd pretty much need to use MySQL MyISAM tables to get guaranteed dirty reads, that is, there's no transaction at all, and it's not really how transactions were meant to be used.Lowering the isolation level is usually just a means to get more transaction throughput. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] how to create mysql database in sqlalchemy?
I ran below code as a root user:
import sqlalchemy
engine = sqlalchemy.create_engine('mysql://user:password@server') # connect to
server
engine.execute(CREATE DATABASE mydb) #create db
engine.execute(USE mydb) # select new db
However the database couldn't be created. The error I got is:
File
/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py, line
80, in connect
return dialect.connect(*cargs, **cparams)
File /usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py,
line 279, in connect
return self.dbapi.connect(*cargs, **cparams)
File /usr/lib/python2.7/dist-packages/MySQLdb/__init__.py, line 81, in
Connect
return Connection(*args, **kwargs)
File /usr/lib/python2.7/dist-packages/MySQLdb/connections.py, line 187, in
__init__
super(Connection, self).__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (OperationalError) (1049, Unknown database
'mydb') None None
Any idea?
Thank you very much!
LYH
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/VCaVuIagRW8J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Strange behaviour while trying to swap related records, or am I doing it wrong?
Hi all!
I'm trying to use sqlalchemy.ext.orderinglist as per instructions here
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/orderinglist.html ,
and but I've encountered a loss of data while trying to swap positions of
related records inside a related property list. I thought it was related to
sqlalchemy.ext.orderinglist, but when I excluded it the situation remained
the same. Here is the full not-so-working example:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Text, ForeignKey
sa_engine = create_engine(sqlite:///:memory:)
Session = sessionmaker(bind=sa_engine)
session = Session()
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
name = Column(Text)
children = relationship('Child', backref='parent')
def __repr__(self):
return self.name
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
name = Column(Text)
parent_id = Column(Integer, ForeignKey('parent.id'))
def __repr__(self):
return self.name
Base.metadata.create_all(sa_engine)
p = Parent(name='Thomas')
session.add(p)
c1 = Child(name=Mary)
c2 = Child(name=John)
c3 = Child(name=Kenny)
p.children.append(c1)
p.children.append(c2)
p.children.append(c3)
session.commit()
p = session.query(Parent).get(1)
print(p.children) #prints [Mary, John, Kenny]
p.children[1], p.children[2] = p.children[2], p.children[1]
print(p.children) #prints [Mary, Kenny, John]
session.commit()
print(p.children) #prints [Mary, John]. Oh my God! They killed Kenny!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] MySQL has gone away
On Thursday, December 27, 2012 10:22:08 PM UTC-3, Michael Bayer wrote: On Dec 27, 2012, at 6:28 PM, Diego Woitasen wrote: Hi, I know that this was discussed several times in the past but I can't solve the problem with the tip that I read in this list. Every morning my application dies with the msg MySQL has gone away. My app has different modules (which are process) and this errors appears in the simplest one and in the complex one too. There is no activity usually at night, specially in one of the modules that has the issue more frequently. This lines setup the session: engine = create_engine(mysql_uri, pool_recycle=config.db_pool_recycle) # Configure in 300 seconds right now. DBSession = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) THe simplest module only do this with the DB: try: delivery = db.DBSession.query(db.Delivery).\ filter(db.Delivery.id == delivery_cmsg.delivery_id).one() except NoResultFound: print 'WARN: invalid delivery ID: ' + int(delivery_cmsg.delivery_id) return finally: db.DBSession.commit() print delivery.name, delivery.start, delivery.status And it has the issue every morning. I'm using SA 0.7.9 and MySQLdb 1.2.4c1. Is there a checklist of things to check that could cause this problem? so you've got pool_recycle, which will make sure the connection is refreshed when checked out from the pool. The other part is to make sure that when the app is idle, you have actually checked all connections back in. So this means every Session has been committed, rolled back, or closed, every Connection closed, every ResultProxy you might have gotten from executing a statement is fully exhausted of its rows and/or closed, and if you happen to be using the very old thread local engine system (which I don't recommend) you'd also have committed/rolled back any transaction there. I'm back to this :) My problem was that I'm not closing the session properly. My new question is... is there a way to autoclose the session? My example, at the end of the scope? What's the recommend way to do this? I've read the FAQ, but it's not very clear for me. Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/NXOOxkeCEWMJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Strange behaviour while trying to swap related records, or am I doing it wrong?
On Jan 7, 2013, at 7:05 PM, Alexey Vihorev wrote: p.children[1], p.children[2] = p.children[2], p.children[1] print(p.children) #prints [Mary, Kenny, John] yeah, without looking too deeply I'm fairly certain this is this trac ticket: http://www.sqlalchemy.org/trac/ticket/1103 basically would add a good chunk of complexity and overhead to the list instrumentation. this is a blue sky ticket for that reason. For now you'd need to just assign to an intermediary variable and do one assignment at a time. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] MySQL has gone away
On Jan 7, 2013, at 7:20 PM, Diego Woitasen wrote: I'm back to this :) My problem was that I'm not closing the session properly. My new question is... is there a way to autoclose the session? My example, at the end of the scope? What's the recommend way to do this? I've read the FAQ, but it's not very clear for me. you need to structure your application such that database operations occur within a consistent framework. This is the simplest when using web frameworks, as all frameworks include some kind of request end hook - that's where the close of the Session would go. Looking at your example again, I see you have a print delivery.name, delivery.status after you've done your commit(). That would be a likely source for the new connection here, as accessing those attributes means the Session needs to go back to the database post-commit to get their most recent value. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] MySQL has gone away
On Jan 7, 2013, at 7:20 PM, Diego Woitasen wrote: I'm back to this :) My problem was that I'm not closing the session properly. My new question is... is there a way to autoclose the session? My example, at the end of the scope? What's the recommend way to do this? I've read the FAQ, but it's not very clear for me. you need to structure your application such that database operations occur within a consistent framework. This is the simplest when using web frameworks, as all frameworks include some kind of request end hook - that's where the close of the Session would go. Looking at your example again, I see you have a print delivery.name, delivery.status after you've done your commit(). That would be a likely source for the new connection here, as accessing those attributes means the Session needs to go back to the database post-commit to get their most recent value. Hi Michael / Diego - I also have a couple of questions in this area, so I though I would jump on this thread. So, if you close down a Session with Session.close() is the Engine associated with that session (e.g. Session(bind=e) ) then freed up as far as the pool etc is concerned or is there something else I need to do to the Engine? If I have a result proxy and it simply gets garbage collected - is that 'freed' as far as SQLA is concerned or do I need to specifically do something to clean it up (other than exhausting it)? The issue I'm having is that 'sometimes' my app gets handed a dead connection to the MySQL server which tends to make things unhappy. It is a home grown framework, however it is well structured and has clear Entry and Exit points for closing down any Session objects etc. *Somewhere* I am leaving something dangling and it's getting closed down by MySQL with a connection timeout. Finally, I'm using multi Session objects (potentially) within a threaded environment. i.e. Each thread may or may not have one or more Sessions created using sessionmaker(). (A second Session would typically be because of some nested requirement) This appears to work fine, or am I missing something? Each new Session is bound to the engine directly - and it *could* be a different Engine to other Sessions. Thanks for all your help. Cheers Warwick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] MySQL has gone away
On Jan 7, 2013, at 7:47 PM, Warwick Prince wrote: On Jan 7, 2013, at 7:20 PM, Diego Woitasen wrote: I'm back to this :) My problem was that I'm not closing the session properly. My new question is... is there a way to autoclose the session? My example, at the end of the scope? What's the recommend way to do this? I've read the FAQ, but it's not very clear for me. you need to structure your application such that database operations occur within a consistent framework. This is the simplest when using web frameworks, as all frameworks include some kind of request end hook - that's where the close of the Session would go. Looking at your example again, I see you have a print delivery.name, delivery.status after you've done your commit(). That would be a likely source for the new connection here, as accessing those attributes means the Session needs to go back to the database post-commit to get their most recent value. Hi Michael / Diego - I also have a couple of questions in this area, so I though I would jump on this thread. So, if you close down a Session with Session.close() is the Engine associated with that session (e.g. Session(bind=e) ) then freed up as far as the pool etc is concerned or is there something else I need to do to the Engine? the Session is a user of an Engine. it does basically this: connection = engine.connect() # check out from the pool connection.close() # return to the pool the connection.close() you see up there happens at three places: - session.rollback() - session.commit() - session.close() the Engine itself has a set of pooled connections, these are TCP/IP connections to your database. These stay there until the pool and/or engine is disposed, using engine.dispose(). However, the Engine wasn't really intended to be disposed() very often. The vast majority of applications that talk to a database need to continue talking to that DB through the lifespan of the app - hence the connection pool stays open so it can quickly start new conversations. if you want your app to not have any pooled connections when it's idle, then just turn off pooling. Use NullPool. Engine.dispose() is not something you typically need to call explicitly. If I have a result proxy and it simply gets garbage collected - is that 'freed' as far as SQLA is concerned or do I need to specifically do something to clean it up (other than exhausting it)? a ResultProxy is an object returned by a Connection (which is the thing you get from engine.connect()). This object refers to a DBAPI cursor also. The ResultProxy holds onto that cursor/connection as you read results from it. It then closes the connection/cursor when it has no more use for them. The ResultProxy does this close when: - all rows are exhausted - fetchall(), or enough calls to fetchone()/fetchmany(), first(), scalar(). - immediately, if there are no rows. the result you get from insert(), update(), delete() etc. assuming theres no RETURNING, that result is closed. - in all cases when you call result.close(). - when the garbage collector collects it. but its bad form to rely upon this. The only way you can get here is if the result returns rows, and you don't read them all. The issue I'm having is that 'sometimes' my app gets handed a dead connection to the MySQL server which tends to make things unhappy. It is a home grown framework, however it is well structured and has clear Entry and Exit points for closing down any Session objects etc. *Somewhere* I am leaving something dangling and it's getting closed down by MySQL with a connection timeout. there's a lot of ways to trace connection activity, there's echo_pool=debug, there's the AssertionPool which can be helpful in some situations (it even stores the stack trace where the connection was checked out, read its source and perhaps emulate this approach for a more involved debugging approach), there's a whole event system which you can use to make sure no Connection is older than a certain time (put a timestamp in connection.info and check it), so I'd look into these methods to figure out where this dead connection is coming from. Finally, I'm using multi Session objects (potentially) within a threaded environment. i.e. Each thread may or may not have one or more Sessions created using sessionmaker(). (A second Session would typically be because of some nested requirement) This appears to work fine, or am I missing something? its fine, just don't open up a Session then leave it hanging open. Each new Session is bound to the engine directly - and it *could* be a different Engine to other Sessions. shouldn't matter... -- 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
RE: [sqlalchemy] Strange behaviour while trying to swap related records, or am I doing it wrong?
Tried to replace this with this, but results are the same temp1 = p.children[1] temp2 = p.children[2] p.children[2] = temp1 p.children[1] = temp2 From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Tuesday, January 08, 2013 2:23 AM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Strange behaviour while trying to swap related records, or am I doing it wrong? On Jan 7, 2013, at 7:05 PM, Alexey Vihorev wrote: p.children[1], p.children[2] = p.children[2], p.children[1] print(p.children) #prints [Mary, Kenny, John] yeah, without looking too deeply I'm fairly certain this is this trac ticket: http://www.sqlalchemy.org/trac/ticket/1103 basically would add a good chunk of complexity and overhead to the list instrumentation. this is a blue sky ticket for that reason. For now you'd need to just assign to an intermediary variable and do one assignment at a time. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] How to Use Alias in ORDER BY Caluse?
Hi,
My SQL is like select host, count(*) as cnt from tbl group by host order
by cnt desc
How to achieve this using ORM?
session.query(Tbl.host,
func.count('*').label('cnt')).group_by(Tbl.host).order_by(???)
I don't wanna type again func.count... in order_by().
Thanks.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/niCXY5RHyL8J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Strange behaviour while trying to swap related records, or am I doing it wrong?
assuming you can try 0.8 which provides inspect(), this will show what is happening: from sqlalchemy import inspect print p.children[2] = c2 print p.children print inspect(c2).attrs.parent.history print inspect(c3).attrs.parent.history print p.children[1] = c3 print p.children print inspect(c2).attrs.parent.history print inspect(c3).attrs.parent.history print we see: [Mary, John, John] History(added=(), unchanged=[Thomas], deleted=()) History(added=[None], unchanged=(), deleted=[Thomas]) [Mary, Kenny, John] History(added=[None], unchanged=(), deleted=[Thomas]) History(added=(), unchanged=[Thomas], deleted=()) basically, somecollection[n] = someobject will also fire a backref event. The object already at somecollection[n] fires a replace event, which sends a remove to the backref at somecollection[n]. The confusion arises because p.children[2] = c2 means that c2 is now present in the list twice, which is not a condition the collection mechanics support. The assignment back of c3 leads the system to believe that c2 is being removed from the collection, hence the backref sets c2.parent to None, yet c2 is in the list twice so it isn't actually being removed. It's the backref event being fired off inappropriately that's the core of the issue. Tracking this count like the ticket states would add an expensive id() + dictionary storage for all objects in all collections everywhere, for a pretty infrequent use case, hitting us both in memory and time spent. It's a bad situation. i can see having this as perhaps an option on relationship(), track_dupes=True, we'd have to recommend it in conjunction with ordering_list. Some ways to work around include, assigning a slice, so that both items are removed first: p.children[1:2] = [c2, c1] or just doing the second set event again: p.children[1], p.children[2] = p.children[2], p.children[1] p.children[1] = p.children[1] I'll add these workarounds to the ticket. On Jan 7, 2013, at 9:09 PM, Alexey Vihorev wrote: Tried to replace this with this, but results are the same temp1 = p.children[1] temp2 = p.children[2] p.children[2] = temp1 p.children[1] = temp2 From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Tuesday, January 08, 2013 2:23 AM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Strange behaviour while trying to swap related records, or am I doing it wrong? On Jan 7, 2013, at 7:05 PM, Alexey Vihorev wrote: p.children[1], p.children[2] = p.children[2], p.children[1] print(p.children) #prints [Mary, Kenny, John] yeah, without looking too deeply I'm fairly certain this is this trac ticket: http://www.sqlalchemy.org/trac/ticket/1103 basically would add a good chunk of complexity and overhead to the list instrumentation. this is a blue sky ticket for that reason. For now you'd need to just assign to an intermediary variable and do one assignment at a time. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to Use Alias in ORDER BY Caluse?
On Jan 7, 2013, at 9:19 PM, Ji Zhang wrote:
Hi,
My SQL is like select host, count(*) as cnt from tbl group by host order by
cnt desc
How to achieve this using ORM?
session.query(Tbl.host,
func.count('*').label('cnt')).group_by(Tbl.host).order_by(???)
I don't wanna type again func.count... in order_by().
count = func.count('*').label('cnt')
query(Tbl.host, count).group_by(Tbl.host).order_by(count.desc())
or
query(Tbl.host,
func.count('*').label('cnt').group_by(Tbl.host).order_by(desc('cnt'))
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to Use Alias in ORDER BY Caluse?
The second one is neat. Thanks~
On Tuesday, January 8, 2013 11:28:34 AM UTC+8, Michael Bayer wrote:
On Jan 7, 2013, at 9:19 PM, Ji Zhang wrote:
Hi,
My SQL is like select host, count(*) as cnt from tbl group by host
order by cnt desc
How to achieve this using ORM?
session.query(Tbl.host,
func.count('*').label('cnt')).group_by(Tbl.host).order_by(???)
I don't wanna type again func.count... in order_by().
count = func.count('*').label('cnt')
query(Tbl.host, count).group_by(Tbl.host).order_by(count.desc())
or
query(Tbl.host,
func.count('*').label('cnt').group_by(Tbl.host).order_by(desc('cnt'))
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/d4TnhNsMPLsJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
