[sqlalchemy] Re: TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30
You need to make sure that you are closing your sessions--otherwise, they keep your connections open, and are never returned to the pool. Make sure to read up on sessions here: http://www.sqlalchemy.org/docs/05/session.html Also, read up on logging: http://www.sqlalchemy.org/docs/05/dbengine.html#configuring-logging It's nice to be able to log your pool status at some points, so that you can see how many connections are open. -Jeff On May 22, 4:41 am, Marcin Krol mrk...@gmail.com wrote: Hello everyone, After some time of operation (a day or so), from time to time I'm getting this error: TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 I have to stress that not much activity is taking place on that OS as it is development installation. Does anybody know what could be causing this? Restarting apache or postgres eliminates that problem, but then it reappears. I'm getting sick of this. Does anybody know what could be the root cause? How to fix this? My app uses mod_python / SQLA 5.3. The backend is Postgres 8.1 on RH 5.3. Regards, mk --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: django middleware or signals for sqlalchemy Session
Definitely go with middleware--it's very clean and simple. Also, make sure to use sqlalchemy.orm.scoped_session()--it makes using sessions in Django pretty much transparent; any time you need to work with a session, you call Session(), and it either uses your current one, or creates a new one if necessary. Coincidentally, I actually wrote in that first thread you linked to. :-) -Jeff On Apr 23, 5:30 pm, Michael Trier mtr...@gmail.com wrote: Hi, On Thu, Apr 23, 2009 at 4:05 PM, davidlmontgomery davidlmontgom...@gmail.com wrote: I would like to know if there is a consensus on the best way to set up and remove sqlalchemy Sessions in django. I figure I'm either going to use middleware, something like this thread: http://groups.google.com/group/django-users/browse_thread/thread/e674... or I'm going to use signals, something like this post: http://translate.google.com/translate?hl=ensl=jau=http://d.hatena.n... Any clear advantages or disadvantages for the two approaches? Personally I find the Middleware approach cleaner and allows you to handle exceptions with rollbacks as indicated in the django-users thread. There was a project Tranquil (http://code.google.com/p/tranquil/) that expanded on this idea to inject more stuff into the request automatically. It's pretty much dead at this point from what I understand. Additionally I'll point out that I have a project called Django-SQLAlchemy (http://gitorious.org/projects/django-sqlalchemy) that has the aim of automatically making SQLAlchemy accessible through Django applications. Good luck. -- Michael Trierhttp://michaeltrier.com/http://thisweekindjango.com/ --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using sqlalchemy in twisted.
Pedro, I don't really have much of anything special as far as the connection goes--SQLAlchemy already does a pretty awesome job of handling all that for you. I just keep a module-level variable that I can import as needed, and call a function in that module to set up the connection with settings from a config file. As far as the models/mapping goes, I have all of the tables, classes, and mapping between then defined in one module, and I can just import from there whenever needed. Nothing about sessions/connections in there at all--that way there's no need to worry about them. It's really a pretty ordinary set-up. Unfortunately, all this code is for work, so I can't share it. I'll happily answer your questions, though. -Jeff On Mar 15, 2:42 pm, Pedro Algarvio, aka, s0undt3ch u...@ufsoft.org wrote: On Mar 11, 2:13 pm, 一首诗 newpt...@gmail.com wrote: Hi Jeff, In my project I use the *model* to indicate an instance of Database. I don't really need multiple instances of Database. But when I wrote unit test, I always create an new one database in setup, which create a new sqlite in memory database to avoid conflicts between test cases. About the trick to make *sess* a keywork parameter, that's really clever! Thanks a lot! On Mar 11, 9:05 pm, Jeff FW jeff...@gmail.com wrote: Logging SA objects *after* the session is gone will always be a problem, unless you make sure to detach all of them from the session. I'd just log the original request, instead. In my case, I have to convert all of my SA objects to something Perspective Broker understands, so I actually log those *after* that, as they're no longer part of the session--but I'm not sure if you can do that in your case. As for the decorator--I got a little confused with your names--you call it model in your decorator, but it's really an instance of Database when it gets passed in as self. One way to get rid of that parameter would be to make sess a keyword argument, like so: def wrapper(*args, **kw): sess = model.Session() try: return f(sess=sess, *args, **kw) and then change your method: def _getObjectById(self, klass, id, sess=None): return sess.query(klass).get(id) That way, self will get passed in *args with no problem. Are you planning to have multiple instances of your Database class? If not, I'd suggest changing everything in it into class methods, so that way you can call it *without* an instance at all, and don't have to worry about connecting to the database multiple times by accident. Just a thought. -Jeff On Mar 10, 10:38 am, 一首诗 newpt...@gmail.com wrote: Hi Jeff, Thanks for your kind suggestion. I first add some log decorators, but i found when it might cause to print sqalchemy objects which has not been bound to any session. And I am not quite sure about how to make the decorator mor genreal. Actually, I think I must use model as the first parameter because as a instance method, _getObjectById require the first parameter to be self. Can you write a few lines of code to show your suggestion? On Mar 8, 5:06 am, Jeff FW jeff...@gmail.com wrote: That's pretty similar to what I do, actually, if a bit simpler (but that's good!) One suggestion would be to throw an except (maybe for the base SQLAlchemy exception class) in your try block, otherwise you run the risk of things dying in an ugly way. I'm not familiar with pyamf, so I don't know how it would handle errors, but twisted usually carries on as if nothing happens. Also, I'd make the decorator a bit more general--don't put the model argument in wrapper(). Put sess first, then take *args and **kwargs, and pass those right to the inner function f(). That way you can reuse it for anything that requires a DB session. Other things you could add (if so inclined) are decorators for logging and other types of error handling (like catching IntegrityErros thrown by duplicates.) I do those things, but I might be a bit OCD :-) -Jeff On Mar 7, 1:41 am, 一首诗 newpt...@gmail.com wrote: Hi, Thanks for your reply. I'm using it the way like you. The only difference is that I am using pyamf instead of PB. On every request, I delegate required db operations to a class called Database, similar to these code below. I used to use scope_session instead of create and close session every time. But as I said in my earlier mails, they don't work. These code below seems to work right now. But if you have more suggestion, I will be very thankful. #= def require_session(f): '''create and close session for each synchronous method''' def wrapper(model, *args, **kw): sess
[sqlalchemy] Re: Using sqlalchemy in twisted.
Logging SA objects *after* the session is gone will always be a problem, unless you make sure to detach all of them from the session. I'd just log the original request, instead. In my case, I have to convert all of my SA objects to something Perspective Broker understands, so I actually log those *after* that, as they're no longer part of the session--but I'm not sure if you can do that in your case. As for the decorator--I got a little confused with your names--you call it model in your decorator, but it's really an instance of Database when it gets passed in as self. One way to get rid of that parameter would be to make sess a keyword argument, like so: def wrapper(*args, **kw): sess = model.Session() try: return f(sess=sess, *args, **kw) and then change your method: def _getObjectById(self, klass, id, sess=None): return sess.query(klass).get(id) That way, self will get passed in *args with no problem. Are you planning to have multiple instances of your Database class? If not, I'd suggest changing everything in it into class methods, so that way you can call it *without* an instance at all, and don't have to worry about connecting to the database multiple times by accident. Just a thought. -Jeff On Mar 10, 10:38 am, 一首诗 newpt...@gmail.com wrote: Hi Jeff, Thanks for your kind suggestion. I first add some log decorators, but i found when it might cause to print sqalchemy objects which has not been bound to any session. And I am not quite sure about how to make the decorator mor genreal. Actually, I think I must use model as the first parameter because as a instance method, _getObjectById require the first parameter to be self. Can you write a few lines of code to show your suggestion? On Mar 8, 5:06 am, Jeff FW jeff...@gmail.com wrote: That's pretty similar to what I do, actually, if a bit simpler (but that's good!) One suggestion would be to throw an except (maybe for the base SQLAlchemy exception class) in your try block, otherwise you run the risk of things dying in an ugly way. I'm not familiar with pyamf, so I don't know how it would handle errors, but twisted usually carries on as if nothing happens. Also, I'd make the decorator a bit more general--don't put the model argument in wrapper(). Put sess first, then take *args and **kwargs, and pass those right to the inner function f(). That way you can reuse it for anything that requires a DB session. Other things you could add (if so inclined) are decorators for logging and other types of error handling (like catching IntegrityErros thrown by duplicates.) I do those things, but I might be a bit OCD :-) -Jeff On Mar 7, 1:41 am, 一首诗 newpt...@gmail.com wrote: Hi, Thanks for your reply. I'm using it the way like you. The only difference is that I am using pyamf instead of PB. On every request, I delegate required db operations to a class called Database, similar to these code below. I used to use scope_session instead of create and close session every time. But as I said in my earlier mails, they don't work. These code below seems to work right now. But if you have more suggestion, I will be very thankful. #= def require_session(f): '''create and close session for each synchronous method''' def wrapper(model, *args, **kw): sess = model.Session() try: return f(model, sess, *args, **kw) finally: sess.close() return wrapper class Database() def __init__(self, conn_str): self.conn_str = conn_str self.engine = create_engine(self.conn_str, echo=False) self.Session = sessionmaker(bind = self.engine, expire_on_commit=False) def getObjectById(self, klass, id): return threads.deferToThread(self._getObjectById, klass, id) @require_session def _getObjectById(self, sess, klass, id): return sess.query(klass).get(id) #= On Mar 6, 5:44 am, Jeff FW jeff...@gmail.com wrote: Don't use scoped_session--you'll run into problems no matter what you do. I'm using Perspective Broker from Twisted with SQLAlchemy. I make sure to create and commit/rollback a session for *every* PB request. It works perfectly, and that's the only way I was really able to get it to work in all cases. Assuming you're using Twisted in a similar way, you could write a simple decorator to wrap any functions that need a database session in the begin/commit stuff as necessary. If you can give more details of how you're using Twisted, I might be able to offer some more insight. -Jeff On Mar 5, 12:33 am, 一首诗 newpt...@gmail.com wrote: I'm not quite sure, but I think I'm pretty careful of sharing objects between threads. 1st, I
[sqlalchemy] Re: Using sqlalchemy in twisted.
Don't use scoped_session--you'll run into problems no matter what you do. I'm using Perspective Broker from Twisted with SQLAlchemy. I make sure to create and commit/rollback a session for *every* PB request. It works perfectly, and that's the only way I was really able to get it to work in all cases. Assuming you're using Twisted in a similar way, you could write a simple decorator to wrap any functions that need a database session in the begin/commit stuff as necessary. If you can give more details of how you're using Twisted, I might be able to offer some more insight. -Jeff On Mar 5, 12:33 am, 一首诗 newpt...@gmail.com wrote: I'm not quite sure, but I think I'm pretty careful of sharing objects between threads. 1st, I only cached as few as possible orm objects. I tried to detach them, but I found that if I detach them, I can't access any of their fields any more. 2nd, I create new orm objects based on client request, pass them to class Database and then merge them to scoped sessions, change, commit and then discard these objects. 3rd, I switch to sqlite frequently to check if there is any database operation outside Database, because sqlite doesn't allow multi-thread access. Actually it seems to work until 2 or 3 days ago suddenly cases hang the server. Ah, as I've already written lots of code in ORM, I think maybe I should try to change Database to use a dedicated thread to handle all database operations. That might be a bottle neck of my application, but I really can't give up orm as these mapper classes are used everywhere in my application. On Mar 4, 7:26 pm, 一首诗 newpt...@gmail.com wrote: Hi, all I am using sqlalchemy in twisted in my project in the way below. Defer any database operation so the twisted's main thread won't be blocked. And I use scoped_session, so that sessions won't have to be created again and again. == class Database() def __init__(self, conn_str): self.conn_str = conn_str self.engine = create_engine(self.conn_str, echo=False) self.Session = scoped_session(sessionmaker(bind = self.engine, expire_on_commit=False)) def getObjectById(self, klass, id): return threads.deferToThread(self._getObjectById, klass, id) def _getObjectById(self, klass, id): sess = self.Session() return sess.query(klass).get(id) == The code doesn't work. When I limit the thread numbers to 1 reactor.suggestThreadPoolSize(1) Everything goes fine. Other wise the server would be blocked and must be killed by kill 9 The result conflicts with my understanding of sqlalchemy. Since I don't share any object between threads, there should be no problem! Ah It always have risk to use something you haven't tried before --~--~-~--~~~---~--~~ 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] 0.5 + pyodbc + Custom Types
Hi guys,
I have an application running under 0.4.7p1 It access a MSSQL server
using pymssql. Now I am trying to see if I can port it to 05 and use
pyodbc (2.1.4).
I am experiencing a strange problem.
I have defined some custom column types (see below) to deal with some
legacy database. Essentially I have to deal with columns defined as
text but that are essentially integer.
So I defined my table with those types, classes and created mappers
Now, when I run the code, I don't see the custom type conversion being
run and the type of attribute corresponding to the column is not what
I expect. In this case it is a string instead of the expected integer
Python 2.5.2 (r252, Sep 15 2008, 11:30:53)
[GCC 4.1.2 (Gentoo 4.1.2 p1.0.1)] on linux2
Type help, copyright, credits or license for more information.
import database as db
sess=db.getDBSession()
stock=sess.query(db.Stock).get(3)
stock.id
'03'
If I run the same code under 0.4.7p1 and pymssql I get
Python 2.5.2 (r252, Sep 15 2008, 11:30:53)
[GCC 4.1.2 (Gentoo 4.1.2 p1.0.1)] on linux2
Type help, copyright, credits or license for more information.
import database as db
sess=db.getDBSession()
stock=sess.query(db.Stock).get(3)
Converting 3 to string
Returning 03 as int
Returning 03 as int
stock.id
3
So the int is converted to padded string on the query (get) and the
string is converted (twice?) to int when the object is created. This
is very much what I expected.
Note that the query succeeds and return the right object in both
cases.
I looked at the doc but there seem to be nothing wrong with what I do.
Is it me or have I stumbled onto something?
Cheers,
Francois
Here is some code snippet
% = %==
class IntString(sa.types.TypeDecorator):
A string type converted between string and integer
impl = sa.types.String
def convert_bind_param(self, value, engine):
Convert from int to string
if value is None:
return None
return str(value)
def convert_result_value(self, value, engine):
Convert from string to int
#return unicode(value,utf8)
if value is None:
return None
return int(value.strip())
class PaddedIntString(IntString):
A string type converted between string and integer with some
padding
def __init__(self, length=None, convert_unicode=False,padding='0'):
if length is None:
raise Exception(Use IntString instead)
self.pat=%%%s%dd%(padding,length)
self.padding=padding
IntString.__init__(self,length,convert_unicode)
def convert_bind_param(self, value, engine):
Convert from int to string
if value is None:
return None
print Converting %d to string%value
return self.pat%(value)
def convert_result_value(self, value, engine):
Convert from string to int
#return unicode(value,utf8)
if value is None:
return None
print Returning %s as int%value
return int(value.strip())
tblStore=sa.Table(Store,SomeMetadata,
sa.Column(StoreNo,PaddedIntString(2), primary_key=True),
sa.Column(StoreEnglishName,StripString(50)),
sa.Column(StoreType,sa.types.String(2)),
sa.Column(StoreLocation,sa.types.String(2)),
sa.Column(StoreStatus,sa.types.String(1)),
sa.Column(Shop,sa.types.Integer),
sa.Column(LastDate,sa.types.DateTime),
sa.Column(LastUser,sa.types.String(8)))
class Stock(DBObject):
One of our Real or virtual shops
def __repr__(self):
if self.id in self.FGStocks.keys():
return self.FGStocks[self.id]
if self.id in self.VirtualStocks.keys():
return self.VirtualStocks[self.id]
if self.id in self.OtherStocks.keys():
return self.OtherStocks[self.id]
return Unknown Stock %d%(self.id)
def _Unposted(self):
return [ x for x in self.Inventory if x.Level!=x.Balance ]
Unposted=property(_Unposted)
Name=property(__repr__)
FGStocks={1:Stock 1, 2:Stock 2}
VirtualStocks={3:V Stock 1, 4:V Stock 2}
OtherStocks={5:O Stock 1, 6:O Stock 2}
Stock.Pmapper=mapper(Stock, tblStore, properties={
'Inventory':relation(StockItem,lazy=True, cascade=all, delete-
orphan),
'id' : tblStore.c.StoreNo,
}
)
% = %==
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to
[sqlalchemy] Re: 0.5 + pyodbc + Custom Types
Hi Michael, Thanks for the reply. And sorry for the dumb question Sometimes I can't see what is just in front of my nose. Regards, François On Jan 13, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote: you're using methods which have been removed (which were also deprecated throughout 0.4). Check out the third bulletpoint here: http://www.sqlalchemy.org/trac/wiki/05Migration#SchemaTypes On Jan 12, 2009, at 10:29 PM, fw wrote: class IntString(sa.types.TypeDecorator): A string type converted between string and integer impl = sa.types.String def convert_bind_param(self, value, engine): Convert from int to string if value is None: return None return str(value) def convert_result_value(self, value, engine): Convert from string to int #return unicode(value,utf8) if value is None: return None return int(value.strip()) class PaddedIntString(IntString): A string type converted between string and integer with some padding def __init__(self, length=None, convert_unicode=False,padding='0'): if length is None: raise Exception(Use IntString instead) self.pat=%%%s%dd%(padding,length) self.padding=padding IntString.__init__(self,length,convert_unicode) def convert_bind_param(self, value, engine): Convert from int to string if value is None: return None print Converting %d to string%value return self.pat%(value) def convert_result_value(self, value, engine): Convert from string to int #return unicode(value,utf8) if value is None: return None print Returning %s as int%value return int(value.strip()) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: pymssql delete problem
Thanks, That seems to solve the problem. Cheers, François Rick Morrison wrote: Thanks for your continuing interest in my silly problem It's not a silly problem, it's a important fundamental operation that ought to work correctly! Try the attached patch against pymssql 0.8.0. --~--~-~--~~~---~--~~ 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: pymssql delete problem
Hi Rick, Thanks for your continuing interest in my silly problem Rick Morrison wrote: How are you deleting the rows? Is this via Session.flush(), or an SQL expression statement? Via a session commit/flush Please post some code as to how you're trying this... The code goes something like this (not actual code!) for x in listofproducts: contlist=sess.query(Container).filter_by(Content=x).all() for y in contlist: quantity[x]-=y.Take(quantity[x]) if y.quantity==0: sess.delete(y) if quantity[x]==0: break sess.begin() try: sess.commit() print OK except: sess.rollback() print Ooops! If only one container needs to be deleted, it works. More than one and it fails. It all works when using MySQL. Cheers, François --~--~-~--~~~---~--~~ 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] pymssql delete problem
Hi Guys, I have an application that staddles 2 databases, MySQL and MS-SQL. It is working but could be faster. To that end, I am trying to use MS- SQL with the pymssql module (so I can eagerload a number of things). The problem is that DELETE fail when more than one record is deleted. It complains that only one record was deleted instead of the requested number. Any idea how I could solve that? I am using SQLAlchemy 0.4.2p3 TIA François --~--~-~--~~~---~--~~ 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: SA 0.4 weird behaviour
Thanks for the answer.
The trace is
Traceback (most recent call last):
File test.py, line 227, in ?
StockItem.mapper[Primary]=mapper(StockItem, tblStock,
properties={
File .../sqlalchemy/orm/__init__.py, line 518, in mapper
return Mapper(class_, local_table, *args, **params)
File .../sqlalchemy/orm/mapper.py, line 152, in __init__
self._compile_tables()
File .../sqlalchemy/orm/mapper.py, line 464, in _compile_tables
self.__log(Identified primary key columns: + str(primary_key))
File sqlalchemy/util.py, line 481, in __repr__
return '%s(%r)' % (self.__class__.__name__, self._list)
File sqlalchemy/schema.py, line 496, in __repr__
return Column(%s) % ', '.join(
File sqlalchemy/types.py, line 136, in __repr__
return %s(%s) % (self.__class__.__name__, ,.join([%s=%s %
(k, getattr(self, k)) for k in inspect.getargspec(self.__init__)[0]
[1:]]))
File sqlalchemy/types.py, line 208, in __getattr__
return getattr(self.impl, key)
AttributeError: 'String' object has no attribute 'padding'
Cheers,
François
On 13 nov, 22:17, Michael Bayer [EMAIL PROTECTED] wrote:
On Nov 13, 2007, at 2:51 AM, fw wrote:
Hi,
I am having a weird problem. I am dealing with some legacy database,
so I subclass TypeDecorator to help clean things up a bit.
This worked fine in 0.3 but I am now trying to use 0.4 and things
break in the strangest of way.
When I run the file below, Python complains about
AttributeError: 'String' object has no attribute 'padding'
a full stack trace would be needed to pinpoint who is calling for that
attribute.
--~--~-~--~~~---~--~~
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: SA 0.4 weird behaviour
Thanks for the answer
It looks indeed like a log operation.
When adding __repr__ no error message.
Spot on.
Cheers,
François
File test.py, line 230, in ?
StockItem.mapper[Primary]=mapper(StockItem, tblStock,
properties={
AttributeError: type object 'StockItem' has no attribute 'mapper'
On 13 nov, 23:23, jason kirtland [EMAIL PROTECTED] wrote:
fw wrote:
Hi,
I am having a weird problem. I am dealing with some legacy database,
so I subclass TypeDecorator to help clean things up a bit.
This worked fine in 0.3 but I am now trying to use 0.4 and things
break in the strangest of way.
When I run the file below, Python complains about
AttributeError: 'String' object has no attribute 'padding'
Now, padding is a parameter of the constructor of PaddedIntString.
So it is not an attribute.
If I make padding an attribute of PaddedIntString, the error
dissappears.
Am I doing something wrong or is this a feature of 0.4???
That sounds like the __repr__ method of AbstractType trying to be
clever, probably called in a logging operation. Try providing a
__repr__ in your type class.
The __repr__ behavior hasn't changed with 0.4, but there might be more
logging than 0.3.
--~--~-~--~~~---~--~~
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] SA 0.4 weird behaviour
Hi,
I am having a weird problem. I am dealing with some legacy database,
so I subclass TypeDecorator to help clean things up a bit.
This worked fine in 0.3 but I am now trying to use 0.4 and things
break in the strangest of way.
When I run the file below, Python complains about
AttributeError: 'String' object has no attribute 'padding'
Now, padding is a parameter of the constructor of PaddedIntString.
So it is not an attribute.
If I make padding an attribute of PaddedIntString, the error
dissappears.
Am I doing something wrong or is this a feature of 0.4???
TIA
François
Here is a file that triggers the problem
PaddedIntString allows me to use integer in Python but to have strings
like 02,10 in the DB.
% ##
!/usr/bin/env python
###
#
#
###
from sqlalchemy.types import TypeDecorator
from sqlalchemy import *
from sqlalchemy.orm import *
class Enum(Unicode):
An Enum is simply a field where the value can only be
chosen from a limited list of values
def __init__(self, values):
'''
construct an Enum type
values : a list of values that are valid for this column
'''
if values is None or len(values) is 0:
raise exceptions.AssertionError('Enum requires a list
of values')
self.values = values
# the length of the string/unicode column should be the longest
string
# in values
super(Enum, self).__init__(max(map(len,values)))
def convert_bind_param(self, value, engine):
if value is None or value == '':
value = None
elif value not in self.values:
raise exceptions.AssertionError('%s not in Enum.values'
%value)
return super(Enum, self).convert_bind_param(value, engine)
def convert_result_value(self, value, engine):
if value is not None and value not in self.values:
raise exceptions.AssertionError('%s not in Enum.values'
%value)
return super(Enum, self).convert_result_value(value, engine)
class CP874String(TypeDecorator):
A string type converted between unicode and cp874
impl = String
def convert_bind_param(self, value, engine):
Convert from unicode to cp874
if value is None:
return None
return value.encode('cp874')
def convert_result_value(self, value, engine):
Convert from cp874 to unicode
#return unicode(value,utf8)
if value is None:
return None
return value.decode('cp874')
class IntString(TypeDecorator):
A string type converted between unicode and integer
impl = String
def convert_bind_param(self, value, engine):
Convert from int to string
if value is None:
return None
return str(value)
def convert_result_value(self, value, engine):
Convert from string to int
#return unicode(value,utf8)
if value is None:
return None
return int(value.strip())
class PaddedIntString(IntString):
A string type converted between unicode and integer
def __init__(self, length=None, convert_unicode=False,padding='0'):
if length is None:
raise Exception(Use IntString instead)
self.pat=%%%s%dd%(padding,length)
IntString.__init__(self,length,convert_unicode)
def convert_bind_param(self, value, engine):
Convert from int to string
if value is None:
return None
return self.pat%(value)
def convert_result_value(self, value, engine):
Convert from string to int
#return unicode(value,utf8)
if value is None:
return None
return int(value.strip())
class myBoolean(TypeDecorator):
A string type converted between unicode and integer
impl = Integer
def convert_bind_param(self, value, engine):
Convert from bool to int
if value is None or not value:
return 0
return 1
def convert_result_value(self, value, engine):
Convert from int to bool
#return unicode(value,utf8)
if value is None or value==0 or value=='0':
return False
return True
class
[sqlalchemy] MSSQL Inser problem
Hi,
I have the weirdest of problem. When using mssql, INSERT (via a
session flush) in one of my table fail, but there is no error
description in the exception (None)
The program seems to be alright since when I associate that table (and
the Inventory table) with a mysql connection, things work
When I get the connected mssql engine and execute the insert, it
works too
I am using SA 0.3.8 and before that 0.3.6dev_r2492
Thanks for any help.
François
Here is the table definition (InventoryItem is the problem), the
Inventory table works fine
blInventory=Table(Inventory,
Column(id, Integer, primary_key=True),
Column(Stock,PaddedIntString(2),ForeignKey(Store.StoreNo)),
Column(Start, Date, nullable=False),
Column(End, Date, nullable=True),
Column(is_Spotcheck, Boolean, nullable=False),
Column(Leader, String(32), nullable=False))
tblInventoryItem=Table(InventoryItem,
Column(id, Integer, primary_key=True),
Column(Inventory,Integer,ForeignKey(Inventory.id),
nullable=False),
Column(Product,String(20),ForeignKey(Product.ProductCode),
nullable=False),
Column(ExpectedQ, Integer, nullable=False),
Column(CountedQ, Integer, default=0))
class InventoryItem(DBObject):
The result of an inventory for a given product
pass
class Inventory(DBObject):
An inventory either full or spotcheck
def __repr__(self):
if self.is_Spotcheck:
return %s inventory spot check on %s%(str(self.Stock),
self.Start.strftime(%d/%m/%Y))
else:
return %s inventory on %s%(str(self.Stock),
self.Start.strftime(%d/%m/%Y))
InventoryItem.mapper[Primary]=mapper(InventoryItem,
tblInventoryItem, properties={
'_Inventory':tblInventoryItem.c.Inventory,
'_Product': tblInventoryItem.c.Product,
'Product': relation(Product)
}
)
Inventory.mapper[Primary]=mapper(Inventory, tblInventory,
properties={
_Stock:tblInventory.c.Stock,
Stock:relation(Stock),
Inventory:relation(InventoryItem, lazy=True,
backref=Inventory, cascade=all, delete-orphan)
}
)
Note that I have no problem inserting records in the Inventory
table
The log is
(Model-MakerCost)
(Product-InventoryItem)
(Model-Product)
(Stock-Inventory)
(Inventory-InventoryItem)
(Stock-StockItem)
(Colour-Product)
2007-06-28 17:35:06,981 DEBUG Dependency sort:
Mapper|Stock|Store
Mapper|Inventory|Inventory
Mapper|Model|Model
Mapper|Colour|Color
Mapper|Product|Product
Mapper|InventoryItem|InventoryItem
Mapper|MakerCost|MakerCost
Mapper|StockItem|Stkdetl
2007-06-28 17:35:06,984 INFO Task dump:
UOWTask(0x1446650, Stock/Store/None) (save/update phase)
| |- Process [EMAIL PROTECTED]
|
|- UOWTask(0x1446510, Inventory/Inventory/None) (save/update phase)
| |- Save [EMAIL PROTECTED]
| | |- Process [EMAIL PROTECTED]
| | |- Process [EMAIL PROTECTED]
| |
| |- UOWTask(0x1446350, Model/Model/None) (save/update phase)
| | |
| | |- UOWTask(0x1446210, Colour/Color/None) (save/update
phase)
| | | |
| | | |- UOWTask(0x14460d0, Product/Product/None) (save/
update phase)
| | | | | |- Process [EMAIL PROTECTED]
| | | | |
| | | | |- UOWTask(0x1446110, InventoryItem/InventoryItem/
None) (save/update phase)
| | | | | |- Save [EMAIL PROTECTED]
| | | | | |
| | | | |
| | | | |
| | | |
| | | |
| | |
| | |
| | |- UOWTask(0x1446c90, MakerCost/MakerCost/None) (save/
update phase)
| | | |- (empty task)
| | |
| | |
| |
| |
|
|
|- UOWTask(0x14467d0, StockItem/Stkdetl/None) (save/update phase)
| |- (empty task)
|
|
|- UOWTask(0x1446510, Inventory/Inventory/None) (delete phase)
| |
| |- UOWTask(0x1446350, Model/Model/None) (delete phase)
| | |
| | |- UOWTask(0x1446210, Colour/Color/None) (delete phase)
| | | |
| | | |- UOWTask(0x14460d0, Product/Product/None) (delete
phase)
| | | | |
| | | | |- UOWTask(0x1446110, InventoryItem/InventoryItem/
None) (delete phase)
| | | | | |
| | | | |
| | | | |
| | | |
| | | |
| | |
| | |
| | |- UOWTask(0x1446c90, MakerCost/MakerCost/None) (delete
phase)
| | | |- (empty task)
| | |
| | |
| |
| |
|
|
|- UOWTask(0x14467d0, StockItem/Stkdetl/None) (delete phase)
| |- (empty task)
|
|
2007-06-28 17:35:06,991 INFO INSERT INTO [InventoryItem] ([Inventory],
[Product], [ExpectedQ], [CountedQ]) VALUES (%(Inventory)s, %
(Product)s, %(ExpectedQ)s, %(CountedQ)s)
2007-06-28 17:35:06,991 INFO {'ExpectedQ': 0, 'Product':
'KK##-0045-13-3405', 'Inventory': 52, 'CountedQ': 10}
2007-06-28 17:35:06,992 INFO ROLLBACK
[sqlalchemy] Re: 0.3.6 problem, was working with 0.3.4
Thanks Rick,
I updated to the latest SVN rev and things do work now.
Cheers,
François
On Apr 4, 10:08 pm, Rick Morrison [EMAIL PROTECTED] wrote:
Hi fw,
The explicit zero for the
float column was a bug that should be fixed in rev #2491.
MSSQL can store low-resolution datetimes in DATE columns, and the format for
MSSQL Date columns was expanded to allow storing those (note the missing
seconds in the format).
That date change should not affect your queries -- does it?
On 4/4/07, fw [EMAIL PROTECTED] wrote:
Hi list,
I am using a mssql database.
A mapper that was working in 0.3.4, no longer works in 0.3.6,
The query built is apparently the same, but the values used as
parameters are not the same.
Under 0.3.4 it was
INFO sqlalchemy.engine.base.Engine.0x..d0 {'lazy_fa2e': 'x',
'MakerCost_MakerCost': '0', 'MakerCost_Effective_Date': '2007-04-04'}
Under 03.6 it has become
INFO sqlalchemy.engine.base.Engine.0x..d0
{'MakerCost_MakerCost': None, 'lazy_e4bf': 'x',
'MakerCost_Effective_Date': '2007-04-04 00:00'}
As you can see MakerCost has become None instead of 0 (zero) and the
Date has turned into a Datetime. Note that the 0 (zero) is explicitly
specified in a query.
What is going on? Any pointer appreciated.
TIA
François
Here are the gruesome details
The MakerCost table associate the Maker and the Model tables. It also
has a Cost and a Date fields.
The mapped Cost property retrieves only the current cost. It is
when using the Cost property that the problem occurs (In the info
above, the Cost property on a Maker object)
tblMaker=Table(Maker,
Column(MAKERCODE,StripString, primary_key = True),
Column(NAME , CP874String,nullable=False),
Column(ADDR0,String(40),nullable=True),
Column(ADDR1,String(40),nullable=True),
Column(ADDR2,String(40),nullable=True),
Column(ADDR3,String(40),nullable=True),
Column(TBCODE,String,nullable=False),
Column(CONTACT,String,nullable=False),
Column(COMMENTS,String),
Column(DSTAMP,DateTime),
Column(USERNAME,String(8)))
tblModel=Table(Model,
Column(ModelCode,CodeString(20), primary_key = True),
Column(ModelGroup,String(12),nullable=False),
Column(ModelThaiName,CP874String(50),nullable=False),
Column(ModelEnglishName,StripString(50),nullable=False),
Column(ModelWeight,Float),
Column(UnitCode,String(5)),
#Column(ProductGroupCode,ForeignKey(... TODO
Column(ModelRemark,CP874String(50)),
Column(EffectDate,Date),
Column(LastDate,DateTime),
Column(LastUser,String(8)))
tblMakerCost=Table(MakerCost,
Column(RowOrder,Integer, primary_key = True),
Column(MakerCode , StripString(50),ForeignKey(Maker.MAKERCODE
)),
Column(ModelCode , CodeString,ForeignKey(Model.ModelCode)),
Column(MakerCost,Float,nullable=False),
Column(Remark,CP874String(100)),
Column(Reference,CP874String()),
Column(Effective_Date,Date),
Column(LastUpdate,DateTime),
Column(LastUser,String(8)))
MakerCost.mapper[Primary]=mapper(MakerCost,tblMakerCost,
properties={
'Cost': tblMakerCost.c.MakerCost,
'id':tblMakerCost.c.RowOrder,
}
)
#Getting only the latest costs
sCCSub=select([tblMakerCost.c.MakerCode, tblMakerCost.c.ModelCode,
func.max(tblMakerCost.c.Effective_Date).label('Effective_Date')],
tblMakerCost.c.Effective_Date=datetime.date.today(),group_by=[
tblMakerCost.c.MakerCode,
tblMakerCost.c.ModelCode]).alias('sCCSub')
sCurrentCost=select([c for c in
tblMakerCost.c],tblMakerCost.c.MakerCost
0,from_obj=[join(tblMakerCost,sCCSub,and_(
sCCSub.c.MakerCode==tblMakerCost.c.MakerCode,and_(
sCCSub.c.ModelCode==tblMakerCost.c.ModelCode,
sCCSub.c.Effective_Date==tblMakerCost.c.Effective_Date
)))]).alias('sCurrentCost')
Maker.mapper[Primary]=mapper(Maker, tblMaker, properties={
'AllCosts':relation(MakerCost, backref='Maker',lazy=True,
cascade=all, delete-orphan),
'Costs': relation(mapper(MakerCost,sCurrentCost,
non_primary=True),
uselist=True, viewonly=True),
#Models':
relation(Model,secondary=tblMakerCost,secondaryjoin=
tblModel.c.ModelCode==tblMakerCost.c.ModelCode,
#primaryjoin=tblMaker.c.MAKERCODE==tblMakerCost.c.MakerCode,uselist=True,
viewonly=True),
'Name':tblMaker.c.NAME,
'Code':tblMaker.c.MAKERCODE,
'Contact': tblMaker.c.CONTACT,
'Comment': tblMaker.c.COMMENTS,
'LastUpdate':tblMaker.c.DSTAMP,
'LastUser':tblMaker.c.USERNAME
}
)
Model.mapper[Primary]=mapper(Model,tblModel, properties={
'AllCosts':relation(MakerCost,backref='Model',lazy=True,
cascade=all, delete-orphan),
'Costs': relation(mapper(MakerCost
[sqlalchemy] Re: 0.3.6 problem, was working with 0.3.4
Hi Rick,
I am afraid I reported success a bit too fast.
The problem has changed though.
I am using rev 2492.
If I use the MakerCost mapper as it is defined in my previous email,
I get a strange behaviour:
If I query a MakerCost directly (e.g.
session.query(MakerCost).select_by(..)) things work fine.
If I get the MakerCost objects indirectly, first by getting a
Maker and then going through the Costs list, all the MakerCost
objects I get have a Cost attribute that is None.
If I change the mapper to
MakerCost.mapper[Primary]=mapper(MakerCost,tblMakerCost)
and use MakerCost instead of Cost as the attribute, it
works fine
It seems to me that the renaming of attributes vs. column name has
some issue.
It is easy to work around it in any case.
Cheers,
François
On Apr 4, 10:08 pm, Rick Morrison [EMAIL PROTECTED] wrote:
Hi fw,
The explicit zero for the
float column was a bug that should be fixed in rev #2491.
MSSQL can store low-resolution datetimes in DATE columns, and the format for
MSSQL Date columns was expanded to allow storing those (note the missing
seconds in the format).
That date change should not affect your queries -- does it?
On 4/4/07, fw [EMAIL PROTECTED] wrote:
Hi list,
I am using a mssql database.
A mapper that was working in 0.3.4, no longer works in 0.3.6,
The query built is apparently the same, but the values used as
parameters are not the same.
Under 0.3.4 it was
INFO sqlalchemy.engine.base.Engine.0x..d0 {'lazy_fa2e': 'x',
'MakerCost_MakerCost': '0', 'MakerCost_Effective_Date': '2007-04-04'}
Under 03.6 it has become
INFO sqlalchemy.engine.base.Engine.0x..d0
{'MakerCost_MakerCost': None, 'lazy_e4bf': 'x',
'MakerCost_Effective_Date': '2007-04-04 00:00'}
As you can see MakerCost has become None instead of 0 (zero) and the
Date has turned into a Datetime. Note that the 0 (zero) is explicitly
specified in a query.
What is going on? Any pointer appreciated.
TIA
François
Here are the gruesome details
The MakerCost table associate the Maker and the Model tables. It also
has a Cost and a Date fields.
The mapped Cost property retrieves only the current cost. It is
when using the Cost property that the problem occurs (In the info
above, the Cost property on a Maker object)
tblMaker=Table(Maker,
Column(MAKERCODE,StripString, primary_key = True),
Column(NAME , CP874String,nullable=False),
Column(ADDR0,String(40),nullable=True),
Column(ADDR1,String(40),nullable=True),
Column(ADDR2,String(40),nullable=True),
Column(ADDR3,String(40),nullable=True),
Column(TBCODE,String,nullable=False),
Column(CONTACT,String,nullable=False),
Column(COMMENTS,String),
Column(DSTAMP,DateTime),
Column(USERNAME,String(8)))
tblModel=Table(Model,
Column(ModelCode,CodeString(20), primary_key = True),
Column(ModelGroup,String(12),nullable=False),
Column(ModelThaiName,CP874String(50),nullable=False),
Column(ModelEnglishName,StripString(50),nullable=False),
Column(ModelWeight,Float),
Column(UnitCode,String(5)),
#Column(ProductGroupCode,ForeignKey(... TODO
Column(ModelRemark,CP874String(50)),
Column(EffectDate,Date),
Column(LastDate,DateTime),
Column(LastUser,String(8)))
tblMakerCost=Table(MakerCost,
Column(RowOrder,Integer, primary_key = True),
Column(MakerCode , StripString(50),ForeignKey(Maker.MAKERCODE
)),
Column(ModelCode , CodeString,ForeignKey(Model.ModelCode)),
Column(MakerCost,Float,nullable=False),
Column(Remark,CP874String(100)),
Column(Reference,CP874String()),
Column(Effective_Date,Date),
Column(LastUpdate,DateTime),
Column(LastUser,String(8)))
MakerCost.mapper[Primary]=mapper(MakerCost,tblMakerCost,
properties={
'Cost': tblMakerCost.c.MakerCost,
'id':tblMakerCost.c.RowOrder,
}
)
#Getting only the latest costs
sCCSub=select([tblMakerCost.c.MakerCode, tblMakerCost.c.ModelCode,
func.max(tblMakerCost.c.Effective_Date).label('Effective_Date')],
tblMakerCost.c.Effective_Date=datetime.date.today(),group_by=[
tblMakerCost.c.MakerCode,
tblMakerCost.c.ModelCode]).alias('sCCSub')
sCurrentCost=select([c for c in
tblMakerCost.c],tblMakerCost.c.MakerCost
0,from_obj=[join(tblMakerCost,sCCSub,and_(
sCCSub.c.MakerCode==tblMakerCost.c.MakerCode,and_(
sCCSub.c.ModelCode==tblMakerCost.c.ModelCode,
sCCSub.c.Effective_Date==tblMakerCost.c.Effective_Date
)))]).alias('sCurrentCost')
Maker.mapper[Primary]=mapper(Maker, tblMaker, properties={
'AllCosts':relation(MakerCost, backref='Maker',lazy=True,
cascade=all, delete-orphan),
'Costs': relation(mapper(MakerCost,sCurrentCost
[sqlalchemy] session.flush() closing connection
Hi,
I am using version 0.2.8 with Python 2.4.3 and MySQL 4.1.21 on an
up-to-date Linux Gentoo box
I am having a problem with session.flush(). It seems that every time I
issue a session
flush the DB connection is closed. If I do something like this
eng =
create_engine('mysql://test:[EMAIL PROTECTED]/test',strategy='threadlocal')
conn=eng.connect()
session = create_session(bind_to=conn)
query=session.query(dbPeople)
query=query.select_by_Lastname
listofpeople=query(Doe)
oneguy=listofpeople[0]
oneguy.Country=Namibia
session.flush()
listofpeople=query(Smith)
The flush works alright and the database is updated, but the last line
result
in an error message:
sqlalchemy.exceptions.InvalidRequestError: This Connection is closed
Is that the normal behaviour? I would have expected the session to
query the DB and return a new list of dbPeople adding them to its list
of persistent object. Am I doing something wrong? Misunderstanding
something?
Cheers,
François
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
