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.
[sqlalchemy] bug in sqllite dialect?
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', age='42.1')
session.add(peterb)
session.commit()
peterb2 = session.query(PersonWITH).filter_by(name='PeterB').first()
self.assertEquals(peterb, peterb2)
self.assertEquals(peterb.age,Decimal('42.1'))
self.assertEquals(peterb2.age,Decimal('42.1'))
self.assertTrue(isinstance(peterb2.age,Decimal))
finally:
session.close()
def test_without_default(self):
Base = declarative_base()
class PersonWO(Base):
__tablename__ = 'person_wo'
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)
session = self.create_session(Base)
try:
peterb = PersonWO(name='PeterB', age='42.1')
session.add(peterb)
session.commit()
peterb2 = session.query(PersonWO).filter_by(name='PeterB').first()
self.assertEquals(peterb, peterb2)
self.assertEquals(peterb.age,Decimal('42.1'))
self.assertEquals(peterb2.age,Decimal('42.1'))
self.assertTrue(isinstance(peterb2.age,Decimal))
finally:
session.close()
if __name__ == __main__:
unittest.main()
--
You received
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',
