https://bitbucket.org/zzzeek/sqlalchemy/issue/3248/populate_result_map-gets-set-for-select <https://bitbucket.org/zzzeek/sqlalchemy/issue/3248/populate_result_map-gets-set-for-select> , will be most likely fixed today.
> On Nov 11, 2014, at 10:18 AM, Bruno <[email protected]> wrote: > > 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 [email protected] > <mailto:[email protected]>. > To post to this group, send email to [email protected] > <mailto:[email protected]>. > Visit this group at http://groups.google.com/group/sqlalchemy > <http://groups.google.com/group/sqlalchemy>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
