this is fixed for 0.9.9 which you can get from git right now.
> On Nov 11, 2014, at 10:53 AM, Michael Bayer <mike...@zzzcomputing.com> wrote: > > 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 <br...@distributedmatter.net >> <mailto:br...@distributedmatter.net>> 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 >> >> <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' <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 >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> 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 sqlalchemy+unsubscr...@googlegroups.com > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > 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 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.
