Re: [sqlalchemy] bug in sqllite dialect?
Chris Withers wrote: Michael Bayer wrote: Chris Withers wrote: Michael Bayer wrote: Has anyone (hi, list, talking to you too!) already done a custom type for this specific problem? people do custom types for all sorts of things. In the case of the Decimal here I'd likely subclass sqlalchemy.databases.sqlite.SLNumeric which should ensure that your own bind_processor() and result_processor() methods can be called. Okay, but how do I make sure this is only used when sqlite this the engine? You can use a TypeDecorator to switch between implementations. I had a plan to add a pre-fab type for this to core that allows easy switching of implementations. Okay, this is what I've ended up with for this one: from decimal import Decimal from sqlalchemy import types from sqlalchemy.databases.sqlite import SQLiteDialect class Numeric(types.TypeDecorator): A numeric type that respects precision with SQLite - always returns Decimals - always rounds as necessary as a result impl = types.Numeric def bind_processor(self, dialect): if isinstance(dialect,SQLiteDialect): def process(value): if value is None: return None else: return float(value) return process else: return super(Numeric,self).bind_processor(dialect) def result_processor(self, dialect): if isinstance(dialect,SQLiteDialect): fstring = %%.%df % self.impl.scale def process(value): if value is None: return None else: return Decimal(fstring % value) return process else: return super(Numeric,self).result_processor(dialect) What should I have done differently and why? cheers, Chris PS: While looking into how to do this for 0.5.8, I saw this dire warning in 0.6: util.warn(Dialect %s+%s does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage. % (dialect.name, dialect.driver)) ...which I also saw applies to SQLite. What are the rounding errors and others issues that you allude to? What is the integer/string recommendation saying? Use a String column? Use an Integer column? Is this something I could work into a TypeDecorator? Should I? the whole ugly discussion is at http://www.sqlalchemy.org/trac/ticket/1759 cheers, Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bug in sqllite dialect?
Michael Bayer wrote: the whole ugly discussion is at http://www.sqlalchemy.org/trac/ticket/1759 Speshul... *sigh* I'm glad I don't have your responsibilities ;-) Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bug in sqllite dialect?
Michael Bayer wrote: Has anyone (hi, list, talking to you too!) already done a custom type for this specific problem? people do custom types for all sorts of things. In the case of the Decimal here I'd likely subclass sqlalchemy.databases.sqlite.SLNumeric which should ensure that your own bind_processor() and result_processor() methods can be called. Okay, but how do I make sure this is only used when sqlite this the engine? Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bug in sqllite dialect?
Chris Withers wrote: Michael Bayer wrote: Has anyone (hi, list, talking to you too!) already done a custom type for this specific problem? people do custom types for all sorts of things. In the case of the Decimal here I'd likely subclass sqlalchemy.databases.sqlite.SLNumeric which should ensure that your own bind_processor() and result_processor() methods can be called. Okay, but how do I make sure this is only used when sqlite this the engine? You can use a TypeDecorator to switch between implementations. I had a plan to add a pre-fab type for this to core that allows easy switching of implementations. Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bug in sqllite dialect?
Hi Michael, Thanks for this, I thought I asked this separately but I can't find the mail now... How would you recommend I work this now in 0.5.8 until I can move to 0.6.0? (which will take some months :-S) I seem to remember you suggesting a custom type. Where can I find examples of those to work against? Has anyone (hi, list, talking to you too!) already done a custom type for this specific problem? cheers, Chris Michael Bayer wrote: fixed in r6859. please don't use those crappy pysqlite converters. On Feb 26, 2010, at 8:50 AM, Chris Withers wrote: from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.session import Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.schema import Column from sqlalchemy.types import String, Numeric, Integer import unittest from decimal import Decimal class Test(unittest.TestCase): def test_truncate(self): # setup engine = create_engine(sqlite://) self.Session = sessionmaker( bind=engine, autoflush=True, autocommit=False ) Base = declarative_base(bind=engine) class MyModel(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36,scale=12)) Base.metadata.create_all() session = self.Session() # precision=36 scale=12 should mean this can handle 12 decimal places # and this has 12 decimal places. session.add(MyModel(value=152.737826714556)) session.commit() obj = session.query(MyModel).one() # this will fail with the output, it shouldn't # Decimal(152.737826715) != Decimal(152.737826714556) self.assertEqual(obj.value, Decimal(152.737826714556)) -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bug in sqllite dialect?
Chris Withers wrote: Hi Michael, Thanks for this, I thought I asked this separately but I can't find the mail now... How would you recommend I work this now in 0.5.8 until I can move to 0.6.0? (which will take some months :-S) I seem to remember you suggesting a custom type. Where can I find examples of those to work against? Has anyone (hi, list, talking to you too!) already done a custom type for this specific problem? people do custom types for all sorts of things. In the case of the Decimal here I'd likely subclass sqlalchemy.databases.sqlite.SLNumeric which should ensure that your own bind_processor() and result_processor() methods can be called. Or as in the doc below you can subclass TypeEngine directly. http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html#custom-types cheers, Chris Michael Bayer wrote: fixed in r6859. please don't use those crappy pysqlite converters. On Feb 26, 2010, at 8:50 AM, Chris Withers wrote: from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.session import Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.schema import Column from sqlalchemy.types import String, Numeric, Integer import unittest from decimal import Decimal class Test(unittest.TestCase): def test_truncate(self): # setup engine = create_engine(sqlite://) self.Session = sessionmaker( bind=engine, autoflush=True, autocommit=False ) Base = declarative_base(bind=engine) class MyModel(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36,scale=12)) Base.metadata.create_all() session = self.Session() # precision=36 scale=12 should mean this can handle 12 decimal places # and this has 12 decimal places. session.add(MyModel(value=152.737826714556)) session.commit() obj = session.query(MyModel).one() # this will fail with the output, it shouldn't # Decimal(152.737826715) != Decimal(152.737826714556) self.assertEqual(obj.value, Decimal(152.737826714556)) -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bug in sqllite dialect?
Michael Bayer wrote: not sure if this is obvious, its the sqlite3.PARSE_DECLTYPES. The SQLite date types don't expect this to be turned on. That is a handy feature which I'm not sure was available in such a simple form when I first wrote against the pysqlite dialect in Python 2.3. Indeed, the dates are the problem here. As we talked about at PyCon, the dates thing is a side effect of the thing my colleagues were trying to solve. I've attached a test case which demonstrates the problem. The test can be made to parse by adding the following: import sqlite3 sqlite3.register_converter('NUMERIC',Decimal) ...and creating the engine as follows: engine = create_engine( sqlite://, connect_args = {'detect_types':sqlite3.PARSE_DECLTYPES} ) ..but then we have the problem that my original mail was about. Any other solutions or explanations on the truncating Decimals front? A workaround is to use a dummy Date type that returns None for bind_processor() and result_processor(). Not gonna fly here, there's too many projects and developers this would touch :'( I don't see any accessor on the SQLite connection that could tell us if this flag is enabled. We don't want to do an isinstance() because those are quite expensive. So what we can do here is utilize 0.6's test the connection trick, to issue a select current_timestamp() from the SQLite connection, and if it comes back as datetime we'd assume PARSE_DECLTYPES is on, or at least some kind of date-based processor has been added. then the SQLite date types would consult this flag. I added #1685 for this which is tentatively targeted at 0.6.0 just so I dont lose track of it. It sounds a bit icky, but I guess if there's no other way? We might want to look into having 0.6 set a default handler for date types in any case, would need to ensure its completely compatible with what we're doing now. I dunno what this means... Also not sure if you're aware, pool_recycle is not advisable with a :memory: database. it would zap out your DB. sqlite also doesnt require any encoding since it only accepts unicode strings - the param is unused by SQLalchemy with sqlite. Yeah, both of these are there 'cos we swap out testing engine between MySQL and SQLite, I'll make sure they're only passed when we're really using MySQL... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.session import Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.schema import Column from sqlalchemy.types import String, Numeric, Integer import unittest from decimal import Decimal class Test(unittest.TestCase): def test_truncate(self): # setup engine = create_engine(sqlite://) self.Session = sessionmaker( bind=engine, autoflush=True, autocommit=False ) Base = declarative_base(bind=engine) class MyModel(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36,scale=12)) Base.metadata.create_all() session = self.Session() # precision=36 scale=12 should mean this can handle 12 decimal places # and this has 12 decimal places. session.add(MyModel(value=152.737826714556)) session.commit() obj = session.query(MyModel).one() # this will fail with the output, it shouldn't # Decimal(152.737826715) != Decimal(152.737826714556) self.assertEqual(obj.value, Decimal(152.737826714556)) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bug in sqllite dialect?
On Feb 26, 2010, at 8:50 AM, Chris Withers wrote: Michael Bayer wrote: not sure if this is obvious, its the sqlite3.PARSE_DECLTYPES. The SQLite date types don't expect this to be turned on. That is a handy feature which I'm not sure was available in such a simple form when I first wrote against the pysqlite dialect in Python 2.3. Indeed, the dates are the problem here. As we talked about at PyCon, the dates thing is a side effect of the thing my colleagues were trying to solve. I've attached a test case which demonstrates the problem. The test can be made to parse by adding the following: import sqlite3 sqlite3.register_converter('NUMERIC',Decimal) ...and creating the engine as follows: engine = create_engine( sqlite://, connect_args = {'detect_types':sqlite3.PARSE_DECLTYPES} ) ..but then we have the problem that my original mail was about. Any other solutions or explanations on the truncating Decimals front? The short answer is that Pysqlite's functionality is not fine grained enough (cant do it just for decimals, it forces itself in for dates) and it is simply not compatible with SQLAlchemy's system - it is seriously flawed in that it doesn't even provide its typing information in cursor.description so its impossible for us to smoothly work around it and detect when it has kicked in and when it has not (see the doc below for details). If you'd like to use it, add the native_datetime flag as described here: http://www.sqlalchemy.org/docs/reference/dialects/sqlite.html#compatibility-with-sqlite3-native-date-and-datetime-types . If you'd like to stay on planet earth with us and not try to use Pysqlite's not very useful behavior, I still don't have any confirming test of what the issue with Decimals is. I'd like a test case that uses no special SQLite flags whatsoever. Not gonna fly here, there's too many projects and developers this would touch :'( its just an import. Python is pretty handy like that. A workaround is to use a dummy Date type that returns None for bind_processor() and result_processor(). I don't see any accessor on the SQLite connection that could tell us if this flag is enabled. We don't want to do an isinstance() because those are quite expensive. So what we can do here is utilize 0.6's test the connection trick, to issue a select current_timestamp() from the SQLite connection, and if it comes back as datetime we'd assume PARSE_DECLTYPES is on, or at least some kind of date-based processor has been added. then the SQLite date types would consult this flag. I added #1685 for this which is tentatively targeted at 0.6.0 just so I dont lose track of it. It sounds a bit icky, but I guess if there's no other way? We might want to look into having 0.6 set a default handler for date types in any case, would need to ensure its completely compatible with what we're doing now. I dunno what this means... Also not sure if you're aware, pool_recycle is not advisable with a :memory: database. it would zap out your DB. sqlite also doesnt require any encoding since it only accepts unicode strings - the param is unused by SQLalchemy with sqlite. Yeah, both of these are there 'cos we swap out testing engine between MySQL and SQLite, I'll make sure they're only passed when we're really using MySQL... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.session import Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.schema import Column from sqlalchemy.types import String, Numeric, Integer import unittest from decimal import Decimal class Test(unittest.TestCase): def test_truncate(self): # setup engine = create_engine(sqlite://) self.Session = sessionmaker( bind=engine, autoflush=True, autocommit=False ) Base = declarative_base(bind=engine) class MyModel(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36,scale=12)) Base.metadata.create_all() session = self.Session() # precision=36 scale=12 should mean this can handle 12 decimal places # and this has 12 decimal places. session.add(MyModel(value=152.737826714556)) session.commit() obj = session.query(MyModel).one() # this will fail with the output, it shouldn't # Decimal(152.737826715) != Decimal(152.737826714556) self.assertEqual(obj.value, Decimal(152.737826714556)) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe
Re: [sqlalchemy] bug in sqllite dialect?
On Feb 26, 2010, at 8:50 AM, Chris Withers wrote: Michael Bayer wrote: not sure if this is obvious, its the sqlite3.PARSE_DECLTYPES. The SQLite date types don't expect this to be turned on. That is a handy feature which I'm not sure was available in such a simple form when I first wrote against the pysqlite dialect in Python 2.3. Indeed, the dates are the problem here. As we talked about at PyCon, the dates thing is a side effect of the thing my colleagues were trying to solve. I've attached a test case which demonstrates the problem. ah sorry, didnt see the test case.you don't need SQLite converters for this, you need a custom type for now until we fix that issue. ticket is forthcoming. The test can be made to parse by adding the following: import sqlite3 sqlite3.register_converter('NUMERIC',Decimal) ...and creating the engine as follows: engine = create_engine( sqlite://, connect_args = {'detect_types':sqlite3.PARSE_DECLTYPES} ) ..but then we have the problem that my original mail was about. Any other solutions or explanations on the truncating Decimals front? A workaround is to use a dummy Date type that returns None for bind_processor() and result_processor(). Not gonna fly here, there's too many projects and developers this would touch :'( I don't see any accessor on the SQLite connection that could tell us if this flag is enabled. We don't want to do an isinstance() because those are quite expensive. So what we can do here is utilize 0.6's test the connection trick, to issue a select current_timestamp() from the SQLite connection, and if it comes back as datetime we'd assume PARSE_DECLTYPES is on, or at least some kind of date-based processor has been added. then the SQLite date types would consult this flag. I added #1685 for this which is tentatively targeted at 0.6.0 just so I dont lose track of it. It sounds a bit icky, but I guess if there's no other way? We might want to look into having 0.6 set a default handler for date types in any case, would need to ensure its completely compatible with what we're doing now. I dunno what this means... Also not sure if you're aware, pool_recycle is not advisable with a :memory: database. it would zap out your DB. sqlite also doesnt require any encoding since it only accepts unicode strings - the param is unused by SQLalchemy with sqlite. Yeah, both of these are there 'cos we swap out testing engine between MySQL and SQLite, I'll make sure they're only passed when we're really using MySQL... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.session import Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.schema import Column from sqlalchemy.types import String, Numeric, Integer import unittest from decimal import Decimal class Test(unittest.TestCase): def test_truncate(self): # setup engine = create_engine(sqlite://) self.Session = sessionmaker( bind=engine, autoflush=True, autocommit=False ) Base = declarative_base(bind=engine) class MyModel(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36,scale=12)) Base.metadata.create_all() session = self.Session() # precision=36 scale=12 should mean this can handle 12 decimal places # and this has 12 decimal places. session.add(MyModel(value=152.737826714556)) session.commit() obj = session.query(MyModel).one() # this will fail with the output, it shouldn't # Decimal(152.737826715) != Decimal(152.737826714556) self.assertEqual(obj.value, Decimal(152.737826714556)) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bug in sqllite dialect?
ticket #1717 On Feb 26, 2010, at 10:28 AM, Michael Bayer wrote: On Feb 26, 2010, at 8:50 AM, Chris Withers wrote: Michael Bayer wrote: not sure if this is obvious, its the sqlite3.PARSE_DECLTYPES. The SQLite date types don't expect this to be turned on. That is a handy feature which I'm not sure was available in such a simple form when I first wrote against the pysqlite dialect in Python 2.3. Indeed, the dates are the problem here. As we talked about at PyCon, the dates thing is a side effect of the thing my colleagues were trying to solve. I've attached a test case which demonstrates the problem. The test can be made to parse by adding the following: import sqlite3 sqlite3.register_converter('NUMERIC',Decimal) ...and creating the engine as follows: engine = create_engine( sqlite://, connect_args = {'detect_types':sqlite3.PARSE_DECLTYPES} ) ..but then we have the problem that my original mail was about. Any other solutions or explanations on the truncating Decimals front? The short answer is that Pysqlite's functionality is not fine grained enough (cant do it just for decimals, it forces itself in for dates) and it is simply not compatible with SQLAlchemy's system - it is seriously flawed in that it doesn't even provide its typing information in cursor.description so its impossible for us to smoothly work around it and detect when it has kicked in and when it has not (see the doc below for details). If you'd like to use it, add the native_datetime flag as described here: http://www.sqlalchemy.org/docs/reference/dialects/sqlite.html#compatibility-with-sqlite3-native-date-and-datetime-types . If you'd like to stay on planet earth with us and not try to use Pysqlite's not very useful behavior, I still don't have any confirming test of what the issue with Decimals is. I'd like a test case that uses no special SQLite flags whatsoever. Not gonna fly here, there's too many projects and developers this would touch :'( its just an import. Python is pretty handy like that. A workaround is to use a dummy Date type that returns None for bind_processor() and result_processor(). I don't see any accessor on the SQLite connection that could tell us if this flag is enabled. We don't want to do an isinstance() because those are quite expensive. So what we can do here is utilize 0.6's test the connection trick, to issue a select current_timestamp() from the SQLite connection, and if it comes back as datetime we'd assume PARSE_DECLTYPES is on, or at least some kind of date-based processor has been added. then the SQLite date types would consult this flag. I added #1685 for this which is tentatively targeted at 0.6.0 just so I dont lose track of it. It sounds a bit icky, but I guess if there's no other way? We might want to look into having 0.6 set a default handler for date types in any case, would need to ensure its completely compatible with what we're doing now. I dunno what this means... Also not sure if you're aware, pool_recycle is not advisable with a :memory: database. it would zap out your DB. sqlite also doesnt require any encoding since it only accepts unicode strings - the param is unused by SQLalchemy with sqlite. Yeah, both of these are there 'cos we swap out testing engine between MySQL and SQLite, I'll make sure they're only passed when we're really using MySQL... cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.session import Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.schema import Column from sqlalchemy.types import String, Numeric, Integer import unittest from decimal import Decimal class Test(unittest.TestCase): def test_truncate(self): # setup engine = create_engine(sqlite://) self.Session = sessionmaker( bind=engine, autoflush=True, autocommit=False ) Base = declarative_base(bind=engine) class MyModel(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36,scale=12)) Base.metadata.create_all() session = self.Session() # precision=36 scale=12 should mean this can handle 12 decimal places # and this has 12 decimal places. session.add(MyModel(value=152.737826714556)) session.commit() obj = session.query(MyModel).one() # this will fail with the output, it shouldn't # Decimal(152.737826715) != Decimal(152.737826714556) self.assertEqual(obj.value, Decimal(152.737826714556)) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this
Re: [sqlalchemy] bug in sqllite dialect?
fixed in r6859. please don't use those crappy pysqlite converters. On Feb 26, 2010, at 8:50 AM, Chris Withers wrote: from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.session import Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.schema import Column from sqlalchemy.types import String, Numeric, Integer import unittest from decimal import Decimal class Test(unittest.TestCase): def test_truncate(self): # setup engine = create_engine(sqlite://) self.Session = sessionmaker( bind=engine, autoflush=True, autocommit=False ) Base = declarative_base(bind=engine) class MyModel(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36,scale=12)) Base.metadata.create_all() session = self.Session() # precision=36 scale=12 should mean this can handle 12 decimal places # and this has 12 decimal places. session.add(MyModel(value=152.737826714556)) session.commit() obj = session.query(MyModel).one() # this will fail with the output, it shouldn't # Decimal(152.737826715) != Decimal(152.737826714556) self.assertEqual(obj.value, Decimal(152.737826714556)) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bug in sqllite dialect?
Michael Bayer wrote: On Feb 26, 2010, at 8:50 AM, Chris Withers wrote: Michael Bayer wrote: not sure if this is obvious, its the sqlite3.PARSE_DECLTYPES. The SQLite date types don't expect this to be turned on. That is a handy feature which I'm not sure was available in such a simple form when I first wrote against the pysqlite dialect in Python 2.3. Indeed, the dates are the problem here. As we talked about at PyCon, the dates thing is a side effect of the thing my colleagues were trying to solve. I've attached a test case which demonstrates the problem. ah sorry, didnt see the test case.you don't need SQLite converters for this, you need a custom type for now Are examples of one of them out in the wild? Do I need them on all fields or just Numeric columns? (which is most of them :-S) its just an import. Python is pretty handy like that. Not sure what you mean here...At a guess: from sqlalchemy.types import Numeric ...becomes... from mylib.types import CorrectlyRounding as Numeric ? Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] bug in sqllite dialect?
not sure if this is obvious, its the sqlite3.PARSE_DECLTYPES. The SQLite date types don't expect this to be turned on. That is a handy feature which I'm not sure was available in such a simple form when I first wrote against the pysqlite dialect in Python 2.3. A workaround is to use a dummy Date type that returns None for bind_processor() and result_processor(). I don't see any accessor on the SQLite connection that could tell us if this flag is enabled. We don't want to do an isinstance() because those are quite expensive. So what we can do here is utilize 0.6's test the connection trick, to issue a select current_timestamp() from the SQLite connection, and if it comes back as datetime we'd assume PARSE_DECLTYPES is on, or at least some kind of date-based processor has been added. then the SQLite date types would consult this flag. I added #1685 for this which is tentatively targeted at 0.6.0 just so I dont lose track of it. We might want to look into having 0.6 set a default handler for date types in any case, would need to ensure its completely compatible with what we're doing now. Also not sure if you're aware, pool_recycle is not advisable with a :memory: database. it would zap out your DB. sqlite also doesnt require any encoding since it only accepts unicode strings - the param is unused by SQLalchemy with sqlite. On Feb 10, 2010, at 12:12 PM, Chris Withers wrote: Hi All, With SA 0.5.8 on Python 2.5, the attached test_with_default blows up with: Traceback (most recent call last): File test_default_arg_sqlite.py, line 46, in test_with_default peterb2 = session.query(PersonWITH).filter_by(name='PeterB').first() File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py, line 1300, in first ret = list(self[0:1]) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py, line 1221, in __getitem__ return list(res) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py, line 1422, in instances rows = [process[0](context, row) for row in fetch] File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py, line 2032, in main return _instance(row, None) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/mapper.py, line 1748, in _instance populate_state(state, dict_, row, isnew, attrs, instancekey=identitykey) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/mapper.py, line 1618, in populate_state populator(state, dict_, row, isnew=isnew, **flags) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/strategies.py, line 120, in new_execute dict_[key] = row[col] File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, line 1348, in __getitem__ return self.__parent._get_col(self.__row, key) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, line 1620, in _get_col return processor(row[index]) File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/databases/sqlite.py, line 183, in process return fn(*[int(x or 0) for x in regexp.match(value).groups()]) TypeError: expected string or buffer The problem is the default on the birthday column. Is this a bug in the sqllite dialect or does the problem lie elsewhere? cheers, Chris from decimal import Decimal from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker from sqlalchemy.schema import Column from sqlalchemy.types import Integer, String, Numeric, Date import datetime import unittest import sqlite3 sqlite3.register_adapter(Decimal, str) sqlite3.register_converter('NUMERIC', Decimal) class Test(unittest.TestCase): def create_session(self,Base): engine = create_engine('sqlite://', echo=False, encoding='utf-8', pool_recycle=3600, connect_args={'detect_types': sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES}) Base.metadata.create_all(engine) return sessionmaker(bind=engine, autoflush=True, autocommit=False)() def test_with_default(self): Base = declarative_base() class PersonWITH(Base): __tablename__ = 'person_wi' id = Column(Integer, primary_key=True) name = Column(String(80), unique=True) age = Column(Numeric(precision=36, scale=12)) money = Column(Numeric(precision=36, scale=12), index=True) birthday = Column(Date, default=datetime.date.today) session = self.create_session(Base) try: peterb = PersonWITH(name='PeterB',