Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
I am looking to adapt this code for a related array/type issue. The code from https://gist.github.com/4433940 works just fine for me (as expected) when building/executing the stmt directly, but not when using the ORM. When row is created using ORM, like this: # snip s = Session(bind=engine) e = Example() e.timestamp=datetime.datetime.utcnow(), e.num=2, e.guids = [uuid.uuid4(), uuid.uuid4()] s.add(e) s.commit() I get an error like this: snip return getter(visitor)(self, **kw) File /home/hans/workspace/providence/env/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py, line 760, in visit_bindparam bind_expression = bindparam.type.bind_expression(bindparam) File test_array.py, line 38, in bind_expression for uuid_val in val TypeError: 'object' object is not iterable (I can dump in full stack if that would be helpful.) Indeed, inspecting that reveals that it is simply an object() instance. I'm not sure where that is being set or whether there is an obvious workaround here. I'm sure I'm simply missing obvious when it comes to dealing with native array types and ORM entity instances. Thanks, Hans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZwxFp2iasq0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
this is ticket http://www.sqlalchemy.org/trac/ticket/2648 and cannot be worked around at this time. If you're working with arrays of UUID I'd recommend using psycopg2 type processors, as the previous poster has had success with. On Jan 7, 2013, at 9:39 AM, Hans Lellelid wrote: I am looking to adapt this code for a related array/type issue. The code from https://gist.github.com/4433940 works just fine for me (as expected) when building/executing the stmt directly, but not when using the ORM. When row is created using ORM, like this: # snip s = Session(bind=engine) e = Example() e.timestamp=datetime.datetime.utcnow(), e.num=2, e.guids = [uuid.uuid4(), uuid.uuid4()] s.add(e) s.commit() I get an error like this: snip return getter(visitor)(self, **kw) File /home/hans/workspace/providence/env/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py, line 760, in visit_bindparam bind_expression = bindparam.type.bind_expression(bindparam) File test_array.py, line 38, in bind_expression for uuid_val in val TypeError: 'object' object is not iterable (I can dump in full stack if that would be helpful.) Indeed, inspecting that reveals that it is simply an object() instance. I'm not sure where that is being set or whether there is an obvious workaround here. I'm sure I'm simply missing obvious when it comes to dealing with native array types and ORM entity instances. Thanks, Hans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZwxFp2iasq0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
correction, this seems to work, though will try to improve: class UUID_ARRAY(TypeDecorator): impl = ARRAY(UUID, dimensions=1) def bind_expression(self, bindvalue): if bindvalue.callable: val = bindvalue.callable() else: val = bindvalue.value if val is None: val = [] elif not hasattr(val, '__iter__'): return bindvalue return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) On Jan 7, 2013, at 9:55 AM, Michael Bayer wrote: this is ticket http://www.sqlalchemy.org/trac/ticket/2648 and cannot be worked around at this time. If you're working with arrays of UUID I'd recommend using psycopg2 type processors, as the previous poster has had success with. On Jan 7, 2013, at 9:39 AM, Hans Lellelid wrote: I am looking to adapt this code for a related array/type issue. The code from https://gist.github.com/4433940 works just fine for me (as expected) when building/executing the stmt directly, but not when using the ORM. When row is created using ORM, like this: # snip s = Session(bind=engine) e = Example() e.timestamp=datetime.datetime.utcnow(), e.num=2, e.guids = [uuid.uuid4(), uuid.uuid4()] s.add(e) s.commit() I get an error like this: snip return getter(visitor)(self, **kw) File /home/hans/workspace/providence/env/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py, line 760, in visit_bindparam bind_expression = bindparam.type.bind_expression(bindparam) File test_array.py, line 38, in bind_expression for uuid_val in val TypeError: 'object' object is not iterable (I can dump in full stack if that would be helpful.) Indeed, inspecting that reveals that it is simply an object() instance. I'm not sure where that is being set or whether there is an obvious workaround here. I'm sure I'm simply missing obvious when it comes to dealing with native array types and ORM entity instances. Thanks, Hans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZwxFp2iasq0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
change again, that doesn't work. Upon reflection, I think the case here is that there's no alternative but to make sure psycopg2 can properly format the contents of the ARRAY itself. This is because SQLAlchemy is producing a completed INSERT statement for preparation, without the parameters actually filled in: INSERT INTO table (x) VALUES (%(x)s) At that point, SQLAlchemy is done producing strings, and sends it off to psycopg2 along with an array value for x. So there is no opportunity here for a user-defined bind expression generator to further modify the expression above.More fundamentally, the above statement can be called using DBAPI executemany(); each value for x can be an array of a *different* length. So it's totally up to psycopg2 here to handle this case, and it even suggests that prepared statements can't be used very effectively with Postgresql ARRAY types (psycopg2 doesn't use prepared statements AFAIK). On Jan 7, 2013, at 9:58 AM, Michael Bayer wrote: correction, this seems to work, though will try to improve: class UUID_ARRAY(TypeDecorator): impl = ARRAY(UUID, dimensions=1) def bind_expression(self, bindvalue): if bindvalue.callable: val = bindvalue.callable() else: val = bindvalue.value if val is None: val = [] elif not hasattr(val, '__iter__'): return bindvalue return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) On Jan 7, 2013, at 9:55 AM, Michael Bayer wrote: this is ticket http://www.sqlalchemy.org/trac/ticket/2648 and cannot be worked around at this time. If you're working with arrays of UUID I'd recommend using psycopg2 type processors, as the previous poster has had success with. On Jan 7, 2013, at 9:39 AM, Hans Lellelid wrote: I am looking to adapt this code for a related array/type issue. The code from https://gist.github.com/4433940 works just fine for me (as expected) when building/executing the stmt directly, but not when using the ORM. When row is created using ORM, like this: # snip s = Session(bind=engine) e = Example() e.timestamp=datetime.datetime.utcnow(), e.num=2, e.guids = [uuid.uuid4(), uuid.uuid4()] s.add(e) s.commit() I get an error like this: snip return getter(visitor)(self, **kw) File /home/hans/workspace/providence/env/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py, line 760, in visit_bindparam bind_expression = bindparam.type.bind_expression(bindparam) File test_array.py, line 38, in bind_expression for uuid_val in val TypeError: 'object' object is not iterable (I can dump in full stack if that would be helpful.) Indeed, inspecting that reveals that it is simply an object() instance. I'm not sure where that is being set or whether there is an obvious workaround here. I'm sure I'm simply missing obvious when it comes to dealing with native array types and ORM entity instances. Thanks, Hans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZwxFp2iasq0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
Thanks for the follow up. Upon further reading/reflection, I discovered that wrapping my string IP address values in the psycopg2.extras.Inet object and then passing that in -- and executing psycopg2.extras.register_inet() as with the UUID example -- seems to work fine for both ORM and non-ORM. So, it sounds like I can get the data into the right form without too many hoops. I will probably create a custom TypeDecorator that simply wraps incoming values in the Inet type (or convert from python netaddr objects into pg's Inet), but that seems like a relatively straightforward customization. Thanks again! Hans On Monday, January 7, 2013 10:09:53 AM UTC-5, Michael Bayer wrote: change again, that doesn't work. Upon reflection, I think the case here is that there's no alternative but to make sure psycopg2 can properly format the contents of the ARRAY itself. This is because SQLAlchemy is producing a completed INSERT statement for preparation, without the parameters actually filled in: INSERT INTO table (x) VALUES (%(x)s) At that point, SQLAlchemy is done producing strings, and sends it off to psycopg2 along with an array value for x. So there is no opportunity here for a user-defined bind expression generator to further modify the expression above.More fundamentally, the above statement can be called using DBAPI executemany(); each value for x can be an array of a *different* length. So it's totally up to psycopg2 here to handle this case, and it even suggests that prepared statements can't be used very effectively with Postgresql ARRAY types (psycopg2 doesn't use prepared statements AFAIK). On Jan 7, 2013, at 9:58 AM, Michael Bayer wrote: correction, this seems to work, though will try to improve: class UUID_ARRAY(TypeDecorator): impl = ARRAY(UUID, dimensions=1) def bind_expression(self, bindvalue): if bindvalue.callable: val = bindvalue.callable() else: val = bindvalue.value if val is None: val = [] elif not hasattr(val, '__iter__'): return bindvalue return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) On Jan 7, 2013, at 9:55 AM, Michael Bayer wrote: this is ticket http://www.sqlalchemy.org/trac/ticket/2648 and cannot be worked around at this time. If you're working with arrays of UUID I'd recommend using psycopg2 type processors, as the previous poster has had success with. On Jan 7, 2013, at 9:39 AM, Hans Lellelid wrote: I am looking to adapt this code for a related array/type issue. The code from https://gist.github.com/4433940 works just fine for me (as expected) when building/executing the stmt directly, but not when using the ORM. When row is created using ORM, like this: # snip s = Session(bind=engine) e = Example() e.timestamp=datetime.datetime.utcnow(), e.num=2, e.guids = [uuid.uuid4(), uuid.uuid4()] s.add(e) s.commit() I get an error like this: snip return getter(visitor)(self, **kw) File /home/hans/workspace/providence/env/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-linux-x86_64.egg/sqlalchemy/sql/compiler.py, line 760, in visit_bindparam bind_expression = bindparam.type.bind_expression(bindparam) File test_array.py, line 38, in bind_expression for uuid_val in val TypeError: 'object' object is not iterable (I can dump in full stack if that would be helpful.) Indeed, inspecting that reveals that it is simply an object() instance. I'm not sure where that is being set or whether there is an obvious workaround here. I'm sure I'm simply missing obvious when it comes to dealing with native array types and ORM entity instances. Thanks, Hans -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ZwxFp2iasq0J. To post to this group, send email to sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. For more options, visit this
[sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
Hi all, I'm experimenting a bit with postgresql arrays of uuid's. Unfortunately I'm running into a bug or I'm not really understanding it :-) My schema definition is as follow: table = Table('example', metadata, Column('timestamp', DateTime(timezone=False), primary_key=True), Column('guids', ARRAY(GUID, dimensions=1) ) Where GUID is taken from http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type Then I try to insert a row with the following code: stmt = table.insert().values( timestamp=datetime.datetime.utcnow(), guids=[uuid.uuid4()] ) engine.execute(stmt) This results in an ProgrammingError (column guids is of type uuid[] but expression is of type text[] at character 97). When using guids=array([uuid.uuid4()], type_=GUID) i receive the following exception: TypeError: self_group() takes exactly 2 arguments (1 given) The complete runnable code is located at https://gist.github.com/4433940 Am I missing something or do I need to register a custom type via psycopg2? Thanks, Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/23G5PmB2sYcJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
Thanks for the detailed response! Inserting the values seems to work fine now. Only retrieving them again doesn't work. I've updated the gist at https://gist.github.com/4433940 The problem seems that psycopg2 now returns the array as a string ({uuid1, uuid2}) and sqlalchemy iterates over the string instead of the containing elements. Bug in sqlalchemy or am i'm doing something stupid? :-) Thanks again! On Wed, Jan 2, 2013 at 5:23 PM, Michael Bayer mike...@zzzcomputing.com wrote: OK well there's a lot of unfortunate turns on this one, at the core is that psycopg2 by default doesn't know how to accept a list of UUIDs.The postgresql.UUID() type in SQLAlchemy takes the easy approach and just converts the value to a string, provided you pass the as_uuid=True flag to it.That works for scalar UUID columns in Postgresql, however if we're dealing with an array, then more casting is required as the data is passed to the DB. Creating type processors on the psycopg2 side would be one way to do this, though we can also do it on the SQLAlchemy side. Compounding issues here is that the array() construct in 0.8 has a tiny bug, that's the self_group() error you got, but if we make that work, we still have to cast each individual uuid() value inside the array which is the part PG isn't doing here. If we fix the ARRAY.self_group() method, this approach works: def cast_uuid(value): return cast(literal(uuid.uuid4(), UUID(as_uuid=True)), UUID) stmt = table.insert().values( timestamp=datetime.datetime.utcnow(), guids=array([cast_uuid(uuid.uuid4()), cast_uuid(uuid.uuid4())]) ) you get a statement like this: INSERT INTO example (timestamp, guids) VALUES (%(timestamp)s, ARRAY[CAST(%(param_1)s AS UUID), CAST(%(param_2)s AS UUID)]) 2013-01-02 11:07:47,850 INFO sqlalchemy.engine.base.Engine {'timestamp': datetime.datetime(2013, 1, 2, 16, 7, 47, 849197), 'param_1': 'cdeec87a-4915-4bc7-a688-8557cc760fe4', 'param_2': '7215808a-64ff-43fa-98af-1d3fc07e75d8'} But there's an even better approach here which is to use SQL bind processing, which also doesn't trip the array() bug at the moment and is more transparent: class UUID_ARRAY(TypeDecorator): impl = ARRAY(UUID(as_uuid=True), dimensions=1) def bind_expression(self, bindvalue): val = bindvalue.value if val is None: val = [] return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) table = Table('example', metadata, Column('timestamp', DateTime(timezone=False), primary_key=True), Column('guids', UUID_ARRAY()) ) stmt = table.insert().values( timestamp=datetime.datetime.utcnow(), guids=[uuid.uuid4(), uuid.uuid4()]) engine.execute(stmt) the SQL output is the same. On Jan 2, 2013, at 8:02 AM, Michael van Tellingen wrote: Hi all, I'm experimenting a bit with postgresql arrays of uuid's. Unfortunately I'm running into a bug or I'm not really understanding it :-) My schema definition is as follow: table = Table('example', metadata, Column('timestamp', DateTime(timezone=False), primary_key=True), Column('guids', ARRAY(GUID, dimensions=1) ) Where GUID is taken from http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type Then I try to insert a row with the following code: stmt = table.insert().values( timestamp=datetime.datetime.utcnow(), guids=[uuid.uuid4()] ) engine.execute(stmt) This results in an ProgrammingError (column guids is of type uuid[] but expression is of type text[] at character 97). When using guids=array([uuid.uuid4()], type_=GUID) i receive the following exception: TypeError: self_group() takes exactly 2 arguments (1 given) The complete runnable code is located at https://gist.github.com/4433940 Am I missing something or do I need to register a custom type via psycopg2? Thanks, Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/23G5PmB2sYcJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com.
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
psycopg2 does have some adapters for this: http://initd.org/psycopg/docs/extras.html?highlight=uuid#uuid-data-type ideally using those would work transparently with ARRAY types, but its not clear if they do or not. They should, or if not you'd probably need to build a more comprehensive type adapter. Or maybe that would be a psycopg2 bug. If you email their list, make sure the code you illustrate for them uses only psycopg2 and nothing else. We can of course continue to work around this on the SQLAlchemy side, though I'd rather not make a workaround of this degree a default behavior of the SQLAlchemy dialect. In this case, the _PGArray type needs to be totally subverted to intercept psycopg's non-handling of the data: from sqlalchemy.dialects.postgresql import psycopg2 class UuidArrayFixer(psycopg2._PGArray): def result_processor(self, dialect, coltype): def fix(value): return [ uuid.UUID(val) for val in re.findall(r'[a-f0-9\-]+', value) ] return fix class UUID_ARRAY(TypeDecorator): impl = UuidArrayFixer(UUID(as_uuid=True), dimensions=1) def bind_expression(self, bindvalue): val = bindvalue.value if val is None: val = [] return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) On Jan 2, 2013, at 12:55 PM, Michael van Tellingen wrote: Thanks for the detailed response! Inserting the values seems to work fine now. Only retrieving them again doesn't work. I've updated the gist at https://gist.github.com/4433940 The problem seems that psycopg2 now returns the array as a string ({uuid1, uuid2}) and sqlalchemy iterates over the string instead of the containing elements. Bug in sqlalchemy or am i'm doing something stupid? :-) Thanks again! On Wed, Jan 2, 2013 at 5:23 PM, Michael Bayer mike...@zzzcomputing.com wrote: OK well there's a lot of unfortunate turns on this one, at the core is that psycopg2 by default doesn't know how to accept a list of UUIDs.The postgresql.UUID() type in SQLAlchemy takes the easy approach and just converts the value to a string, provided you pass the as_uuid=True flag to it.That works for scalar UUID columns in Postgresql, however if we're dealing with an array, then more casting is required as the data is passed to the DB. Creating type processors on the psycopg2 side would be one way to do this, though we can also do it on the SQLAlchemy side. Compounding issues here is that the array() construct in 0.8 has a tiny bug, that's the self_group() error you got, but if we make that work, we still have to cast each individual uuid() value inside the array which is the part PG isn't doing here. If we fix the ARRAY.self_group() method, this approach works: def cast_uuid(value): return cast(literal(uuid.uuid4(), UUID(as_uuid=True)), UUID) stmt = table.insert().values( timestamp=datetime.datetime.utcnow(), guids=array([cast_uuid(uuid.uuid4()), cast_uuid(uuid.uuid4())]) ) you get a statement like this: INSERT INTO example (timestamp, guids) VALUES (%(timestamp)s, ARRAY[CAST(%(param_1)s AS UUID), CAST(%(param_2)s AS UUID)]) 2013-01-02 11:07:47,850 INFO sqlalchemy.engine.base.Engine {'timestamp': datetime.datetime(2013, 1, 2, 16, 7, 47, 849197), 'param_1': 'cdeec87a-4915-4bc7-a688-8557cc760fe4', 'param_2': '7215808a-64ff-43fa-98af-1d3fc07e75d8'} But there's an even better approach here which is to use SQL bind processing, which also doesn't trip the array() bug at the moment and is more transparent: class UUID_ARRAY(TypeDecorator): impl = ARRAY(UUID(as_uuid=True), dimensions=1) def bind_expression(self, bindvalue): val = bindvalue.value if val is None: val = [] return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) table = Table('example', metadata, Column('timestamp', DateTime(timezone=False), primary_key=True), Column('guids', UUID_ARRAY()) ) stmt = table.insert().values( timestamp=datetime.datetime.utcnow(), guids=[uuid.uuid4(), uuid.uuid4()]) engine.execute(stmt) the SQL output is the same. On Jan 2, 2013, at 8:02 AM, Michael van Tellingen wrote: Hi all, I'm experimenting a bit with postgresql arrays of uuid's. Unfortunately I'm running into a bug or I'm not really understanding it :-) My schema definition is as follow: table = Table('example', metadata, Column('timestamp', DateTime(timezone=False), primary_key=True), Column('guids', ARRAY(GUID, dimensions=1) ) Where GUID is taken from http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type Then I try to insert a row with the following code: stmt = table.insert().values(
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
Again, thanks for the detailed response! I went with the psycopg2.extras.register_uuid() method and that works without problems for now. I did have to remove the as_uuid=True since psycopg2 already returned it as an uuid type. For future references the working test code is here https://gist.github.com/4433940 Thanks again, Michael On Wed, Jan 2, 2013 at 7:21 PM, Michael Bayer mike...@zzzcomputing.com wrote: psycopg2 does have some adapters for this: http://initd.org/psycopg/docs/extras.html?highlight=uuid#uuid-data-type ideally using those would work transparently with ARRAY types, but its not clear if they do or not. They should, or if not you'd probably need to build a more comprehensive type adapter. Or maybe that would be a psycopg2 bug. If you email their list, make sure the code you illustrate for them uses only psycopg2 and nothing else. We can of course continue to work around this on the SQLAlchemy side, though I'd rather not make a workaround of this degree a default behavior of the SQLAlchemy dialect. In this case, the _PGArray type needs to be totally subverted to intercept psycopg's non-handling of the data: from sqlalchemy.dialects.postgresql import psycopg2 class UuidArrayFixer(psycopg2._PGArray): def result_processor(self, dialect, coltype): def fix(value): return [ uuid.UUID(val) for val in re.findall(r'[a-f0-9\-]+', value) ] return fix class UUID_ARRAY(TypeDecorator): impl = UuidArrayFixer(UUID(as_uuid=True), dimensions=1) def bind_expression(self, bindvalue): val = bindvalue.value if val is None: val = [] return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) On Jan 2, 2013, at 12:55 PM, Michael van Tellingen wrote: Thanks for the detailed response! Inserting the values seems to work fine now. Only retrieving them again doesn't work. I've updated the gist at https://gist.github.com/4433940 The problem seems that psycopg2 now returns the array as a string ({uuid1, uuid2}) and sqlalchemy iterates over the string instead of the containing elements. Bug in sqlalchemy or am i'm doing something stupid? :-) Thanks again! On Wed, Jan 2, 2013 at 5:23 PM, Michael Bayer mike...@zzzcomputing.com wrote: OK well there's a lot of unfortunate turns on this one, at the core is that psycopg2 by default doesn't know how to accept a list of UUIDs.The postgresql.UUID() type in SQLAlchemy takes the easy approach and just converts the value to a string, provided you pass the as_uuid=True flag to it.That works for scalar UUID columns in Postgresql, however if we're dealing with an array, then more casting is required as the data is passed to the DB. Creating type processors on the psycopg2 side would be one way to do this, though we can also do it on the SQLAlchemy side. Compounding issues here is that the array() construct in 0.8 has a tiny bug, that's the self_group() error you got, but if we make that work, we still have to cast each individual uuid() value inside the array which is the part PG isn't doing here. If we fix the ARRAY.self_group() method, this approach works: def cast_uuid(value): return cast(literal(uuid.uuid4(), UUID(as_uuid=True)), UUID) stmt = table.insert().values( timestamp=datetime.datetime.utcnow(), guids=array([cast_uuid(uuid.uuid4()), cast_uuid(uuid.uuid4())]) ) you get a statement like this: INSERT INTO example (timestamp, guids) VALUES (%(timestamp)s, ARRAY[CAST(%(param_1)s AS UUID), CAST(%(param_2)s AS UUID)]) 2013-01-02 11:07:47,850 INFO sqlalchemy.engine.base.Engine {'timestamp': datetime.datetime(2013, 1, 2, 16, 7, 47, 849197), 'param_1': 'cdeec87a-4915-4bc7-a688-8557cc760fe4', 'param_2': '7215808a-64ff-43fa-98af-1d3fc07e75d8'} But there's an even better approach here which is to use SQL bind processing, which also doesn't trip the array() bug at the moment and is more transparent: class UUID_ARRAY(TypeDecorator): impl = ARRAY(UUID(as_uuid=True), dimensions=1) def bind_expression(self, bindvalue): val = bindvalue.value if val is None: val = [] return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) table = Table('example', metadata, Column('timestamp', DateTime(timezone=False), primary_key=True), Column('guids', UUID_ARRAY()) ) stmt = table.insert().values( timestamp=datetime.datetime.utcnow(), guids=[uuid.uuid4(), uuid.uuid4()]) engine.execute(stmt) the SQL output is the same. On Jan 2, 2013, at 8:02 AM, Michael van Tellingen wrote: Hi all, I'm experimenting a bit with postgresql arrays of uuid's. Unfortunately I'm running into a bug or I'm not really understanding it :-) My schema definition is as follow:
Re: [sqlalchemy] Array with custom types in SqlAlchemy 0.8b2
that adapter works both ways, so you can drop the custom type completely: table = Table('example_2', metadata, Column('timestamp', DateTime(timezone=False), primary_key=True), Column('num', Integer), Column('guids', ARRAY(UUID, dimensions=1)) ) On Jan 2, 2013, at 2:10 PM, Michael van Tellingen wrote: Again, thanks for the detailed response! I went with the psycopg2.extras.register_uuid() method and that works without problems for now. I did have to remove the as_uuid=True since psycopg2 already returned it as an uuid type. For future references the working test code is here https://gist.github.com/4433940 Thanks again, Michael On Wed, Jan 2, 2013 at 7:21 PM, Michael Bayer mike...@zzzcomputing.com wrote: psycopg2 does have some adapters for this: http://initd.org/psycopg/docs/extras.html?highlight=uuid#uuid-data-type ideally using those would work transparently with ARRAY types, but its not clear if they do or not. They should, or if not you'd probably need to build a more comprehensive type adapter. Or maybe that would be a psycopg2 bug. If you email their list, make sure the code you illustrate for them uses only psycopg2 and nothing else. We can of course continue to work around this on the SQLAlchemy side, though I'd rather not make a workaround of this degree a default behavior of the SQLAlchemy dialect. In this case, the _PGArray type needs to be totally subverted to intercept psycopg's non-handling of the data: from sqlalchemy.dialects.postgresql import psycopg2 class UuidArrayFixer(psycopg2._PGArray): def result_processor(self, dialect, coltype): def fix(value): return [ uuid.UUID(val) for val in re.findall(r'[a-f0-9\-]+', value) ] return fix class UUID_ARRAY(TypeDecorator): impl = UuidArrayFixer(UUID(as_uuid=True), dimensions=1) def bind_expression(self, bindvalue): val = bindvalue.value if val is None: val = [] return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) On Jan 2, 2013, at 12:55 PM, Michael van Tellingen wrote: Thanks for the detailed response! Inserting the values seems to work fine now. Only retrieving them again doesn't work. I've updated the gist at https://gist.github.com/4433940 The problem seems that psycopg2 now returns the array as a string ({uuid1, uuid2}) and sqlalchemy iterates over the string instead of the containing elements. Bug in sqlalchemy or am i'm doing something stupid? :-) Thanks again! On Wed, Jan 2, 2013 at 5:23 PM, Michael Bayer mike...@zzzcomputing.com wrote: OK well there's a lot of unfortunate turns on this one, at the core is that psycopg2 by default doesn't know how to accept a list of UUIDs.The postgresql.UUID() type in SQLAlchemy takes the easy approach and just converts the value to a string, provided you pass the as_uuid=True flag to it.That works for scalar UUID columns in Postgresql, however if we're dealing with an array, then more casting is required as the data is passed to the DB. Creating type processors on the psycopg2 side would be one way to do this, though we can also do it on the SQLAlchemy side. Compounding issues here is that the array() construct in 0.8 has a tiny bug, that's the self_group() error you got, but if we make that work, we still have to cast each individual uuid() value inside the array which is the part PG isn't doing here. If we fix the ARRAY.self_group() method, this approach works: def cast_uuid(value): return cast(literal(uuid.uuid4(), UUID(as_uuid=True)), UUID) stmt = table.insert().values( timestamp=datetime.datetime.utcnow(), guids=array([cast_uuid(uuid.uuid4()), cast_uuid(uuid.uuid4())]) ) you get a statement like this: INSERT INTO example (timestamp, guids) VALUES (%(timestamp)s, ARRAY[CAST(%(param_1)s AS UUID), CAST(%(param_2)s AS UUID)]) 2013-01-02 11:07:47,850 INFO sqlalchemy.engine.base.Engine {'timestamp': datetime.datetime(2013, 1, 2, 16, 7, 47, 849197), 'param_1': 'cdeec87a-4915-4bc7-a688-8557cc760fe4', 'param_2': '7215808a-64ff-43fa-98af-1d3fc07e75d8'} But there's an even better approach here which is to use SQL bind processing, which also doesn't trip the array() bug at the moment and is more transparent: class UUID_ARRAY(TypeDecorator): impl = ARRAY(UUID(as_uuid=True), dimensions=1) def bind_expression(self, bindvalue): val = bindvalue.value if val is None: val = [] return array( cast(literal(str(uuid_val)), UUID()) for uuid_val in val ) table = Table('example', metadata, Column('timestamp', DateTime(timezone=False), primary_key=True), Column('guids', UUID_ARRAY()) ) stmt = table.insert().values( timestamp=datetime.datetime.utcnow(),