Hello, I've been trying to insert a row based on a correlated subquery that returns a UUID. The insert query itself returns a long, which somehow SQLAlchemy tries to process as a UUID/GUID.
I'm using SQLAlchemy 0.9.8, Psycopg2 2.5.4 and PostgreSQL 9.1. Here is an example that demonstrates the problem: #!/usr/bin/env python # -*- coding: utf-8 -*- import logging import uuid from sqlalchemy import create_engine, MetaData, Table, Column, ForeignKey, BigInteger, Text, select from sqlalchemy.types import TypeDecorator, CHAR from sqlalchemy.dialects.postgresql import UUID # This GUID class comes from the SQLAlchemy documentation. # http://docs.sqlalchemy.org/en/latest/core/types.html#backend-agnostic-guid-type class GUID(TypeDecorator): """Platform-independent GUID type. Uses Postgresql's UUID type, otherwise uses CHAR(32), storing as stringified hex values. """ impl = CHAR def load_dialect_impl(self, dialect): if dialect.name == 'postgresql': return dialect.type_descriptor(UUID()) else: return dialect.type_descriptor(CHAR(32)) def process_bind_param(self, value, dialect): if value is None: return value elif dialect.name == 'postgresql': return str(value) else: if not isinstance(value, uuid.UUID): return "%.32x" % uuid.UUID(value) else: # hexstring return "%.32x" % value def process_result_value(self, value, dialect): if value is None: return value else: return uuid.UUID(value) logging.basicConfig() logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) metadata = MetaData() table_a = Table('table_a',metadata, Column('id', GUID(), primary_key=True), ) table_b = Table('table_b', metadata, Column('id', BigInteger, primary_key=True), Column('ref_a', ForeignKey('table_a.id', onupdate='CASCADE', ondelete='SET NULL')), Column('value', Text) ) engine = create_engine('postgresql:///test') metadata.create_all(engine) test_uuid = str(uuid.uuid4()) conn = engine.connect() # INSERT INTO table_a (id) VALUES (%(id)s) conn.execute(table_a.insert().values(id=test_uuid)) # INSERT INTO table_b (ref_a, value) VALUES (%(ref_a)s, %(value)s) RETURNING table_b.id conn.execute(table_b.insert().values(ref_a=test_uuid, value='X')) # INSERT INTO table_b (ref_a, value) # VALUES ((SELECT table_a.id FROM table_a WHERE table_a.id = %(id_1)s LIMIT %(param_1)s), %(value)s) find_a_stmt = select([table_a.c.id]).where(table_a.c.id == test_uuid).limit( 1) conn.execute(table_b.insert(inline=True).values(ref_a=find_a_stmt, value='Y' )) # INSERT INTO table_b (ref_a, value) # VALUES ((SELECT table_a.id FROM table_a WHERE table_a.id = %(id_1)s LIMIT %(param_1)s), %(value)s) # RETURNING table_b.id find_a_stmt = select([table_a.c.id]).where(table_a.c.id == test_uuid).limit( 1) conn.execute(table_b.insert().values(ref_a=find_a_stmt, value='Z')) conn.close() The last insert causes this error: Traceback (most recent call last): File "dbtest.py", line 82, in <module> conn.execute(table_b.insert().values(ref_a=find_a_stmt, value='Z')) File "/tmp/dbtest/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute return meth(self, multiparams, params) File "/tmp/dbtest/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/tmp/dbtest/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement compiled_sql, distilled_params File "/tmp/dbtest/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 978, in _execute_context context._fetch_implicit_returning(result) File "/tmp/dbtest/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py" , line 815, in _fetch_implicit_returning ipk.append(row[c]) File "/tmp/dbtest/local/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py", line 915, in process return process_value(value, dialect) File "dbtest.py", line 44, in process_result_value return uuid.UUID(value) File "/usr/lib/python2.7/uuid.py", line 131, in __init__ hex = hex.replace('urn:', '').replace('uuid:', '') AttributeError: 'long' object has no attribute 'replace' It appears that it is trying to process the result of "RETURNING table_b.id" as a GUID, as if it was expecting the type of the result of the subquery statement. The workaround I've found relies on using insert(inline=True), which is fine when the returned value is not needed, but the default is inline=False. I'm just wondering whether I'm not using the type decorator correctly and this behaviour is expected, or whether this is a due to a bug. (This problem doesn't seem to happen if table_a.id is of type Text instead of GUID.) Best wishes, Bruno. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
