[sqlalchemy] Re: Unsupported Type
Hey Wichert, Thanks for the excellent advice! this appears to work perfectly! Thanks, Rob On 2 Nov, 14:19, Wichert Akkerman wich...@wiggy.net wrote: On 11/2/09 14:04 , Sir Rawlins wrote: Hello Guys, I'm getting an exception thrown when trying to save an entity into a SQLite database, this is a database/app which has been ported over from a MySQL backend. The exception looks like this: InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type. u'INSERT INTO bluetooth_session (bluetooth_session_id, result, address, message_id, campaign_id, created, modified) VALUES (?, ?, ?, ?, ?, ?, ?)' [UUID ('ed6ce6a6-4918-421e-9fa5-b41972931713'), dbus.String (u'org.openobex.Error.LinkError'), dbus.String(u'00:1F:6B:58:4A:F5'), 1686, 274, '2009-11-02 12:59:03.521275', '2009-11-02 12:59:03.521275'] ) Now, my first instincts tell me that the issue here is probably the UUID which I'm using as the PK for the table, the column datatype is set to varchar(50) and the definition in the mapper for the class looks like this: bluetooth_session_id = Column(String, primary_key=True, default=uuid.uuid4) Now, do I have to make a change somewhere here? do I perhaps have to change that default statement somehow to format the UUID object as a string? Try this: bluetooth_session_id = Column(String, primary_key=True, default = lambda: str(uuid.uuid4())) Wichert. --~--~-~--~~~---~--~~ 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] Unsupported Type
Hello Guys, I'm getting an exception thrown when trying to save an entity into a SQLite database, this is a database/app which has been ported over from a MySQL backend. The exception looks like this: InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type. u'INSERT INTO bluetooth_session (bluetooth_session_id, result, address, message_id, campaign_id, created, modified) VALUES (?, ?, ?, ?, ?, ?, ?)' [UUID ('ed6ce6a6-4918-421e-9fa5-b41972931713'), dbus.String (u'org.openobex.Error.LinkError'), dbus.String(u'00:1F:6B:58:4A:F5'), 1686, 274, '2009-11-02 12:59:03.521275', '2009-11-02 12:59:03.521275'] ) Now, my first instincts tell me that the issue here is probably the UUID which I'm using as the PK for the table, the column datatype is set to varchar(50) and the definition in the mapper for the class looks like this: bluetooth_session_id = Column(String, primary_key=True, default=uuid.uuid4) Now, do I have to make a change somewhere here? do I perhaps have to change that default statement somehow to format the UUID object as a string? I'd appreciate your suggestions. Thanks, Rob --~--~-~--~~~---~--~~ 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: Help Writing Portable Query
Hello Guys, Thanks to your both for your suggestion, they're of a great help. Michael, I see your point about this, seems there isn't going to be any way to create a truely portable version of the query, I'll just opt for a custom one for each build for now I think :-) Conor, your suggestion solutions seem to work nicely, I've placed those into a query an upon my initial tests it appears to be returning the data that I would expect. Cheers guys, Rob On 27 Oct, 15:35, Michael Bayer mike...@zzzcomputing.com wrote: Sir Rawlins wrote: Hello Guys, I've got a query which I'm currently running as literal SQL against a MySQL database. I'm looking to create a ported version of the query for SQLite but am totally new to that platform so am looking for a little help with my date/time functions, I'm hoping someone here will have a little more experience with SQLite and be able to help out. In addition to making these changes to the literal SQL I'm wondering if the query can be rewritten in a more SQLAlchemy style using functions rather than literal SQL so that it is more portable in future? Or is that going to be tricky? The query, or at least the WHERE clause can be found in this pastebin snippet. http://pastebin.com/m24c39a4f I appreciate any help you guys can offer to get me started. I understand I can get the current date within SQLite by running date ('now') however some of the more tricky modifiers for getting DayOfWeek and DayOfMonth are beyond my understanding at the moment. sqlite date functions are dramatically different and are documented here: http://sqlite.org/lang_datefunc.html To make a query that is generic across MySQL and SQLite3, using date functions which are pretty much specific to each of those databases, I'd construct each date function you need using a custom ClauseElement subclass. Then I'd use the sqlalchemy.ext.compiler to generate output for MySQL or SQLite as needed. Here's a timestamp() function I use that is agnostic across PG and SQLite: from sqlalchemy.sql.expression import ColumnElement, select from sqlalchemy.types import TIMESTAMP from sqlalchemy.ext import compiler from sqlalchemy import create_engine class timestamp(ColumnElement): type = TIMESTAMP() @compiler.compiles(timestamp, 'postgres') def gen_timestamp(element, compiler, **kw): return timezone('utc', CURRENT_TIMESTAMP) @compiler.compiles(timestamp) def gen_timestamp(element, compiler, **kw): return datetime('now', 'localtime') stmt = select([timestamp()]) print stmt.compile(bind=create_engine('sqlite://')) print stmt.compile(bind=create_engine('postgres://')) Thanks, Rob --~--~-~--~~~---~--~~ 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] Help Writing Portable Query
Hello Guys, I've got a query which I'm currently running as literal SQL against a MySQL database. I'm looking to create a ported version of the query for SQLite but am totally new to that platform so am looking for a little help with my date/time functions, I'm hoping someone here will have a little more experience with SQLite and be able to help out. In addition to making these changes to the literal SQL I'm wondering if the query can be rewritten in a more SQLAlchemy style using functions rather than literal SQL so that it is more portable in future? Or is that going to be tricky? The query, or at least the WHERE clause can be found in this pastebin snippet. http://pastebin.com/m24c39a4f I appreciate any help you guys can offer to get me started. I understand I can get the current date within SQLite by running date ('now') however some of the more tricky modifiers for getting DayOfWeek and DayOfMonth are beyond my understanding at the moment. Thanks, Rob --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---