[Sqlalchemy-tickets] Issue #3154: Using aliased table makes literal bindings fail for CTE statement
Brought to you by:
zzzeek
|
From: mike_solomon <iss...@bi...> - 2014-08-02 14:18:27
|
New issue 3154: Using aliased table makes literal bindings fail for CTE statement https://bitbucket.org/zzzeek/sqlalchemy/issue/3154/using-aliased-table-makes-literal-bindings mike_solomon: I've had no problems thus far compiling complex insert statements with literal bind parameters, but the example below fails, binding some parameters into the statement and not binding others when an alias of a table is used. I've put a big fat comment saying LOOK HERE at the point in the code where you can comment in and out a line to see the behavior kicking in. ``` #!python from sqlalchemy import Table, Column, String, Integer, Float, MetaData, select, cast, literal, and_ _metadata = MetaData() string_box = Table('string_box', _metadata, Column('name', String, primary_key = True), Column('str', Integer, primary_key = True), Column('x', Integer), Column('y', Integer), Column('width', Integer), Column('height', Integer) ) font_name = Table('font_name', _metadata, Column('id', Integer), Column('val', String)) name = Table('name', _metadata, Column('id', Integer), Column('val', String)) font_size = Table('font_size', _metadata, Column('id', Integer), Column('val', Float)) time_signature = Table('time_signature', _metadata, Column('id', Integer), Column('num', Integer), Column('den', Integer)) height = Table('height', _metadata, Column('id', Integer), Column('val', Float)) width = Table('width', _metadata, Column('id', Integer), Column('val', Float)) stencil = Table('stencil', _metadata, Column('id', Integer, primary_key = True), Column('sub_id', Integer, primary_key = True), Column('font_name', String), Column('font_size', Float), Column('str', String), Column('x', Float), Column('y', Float)) ####################################### time_signatures_to_xy_info = select([ name.c.id.label('id'), font_name.c.val.label('font_name'), font_size.c.val.label('font_size'), time_signature.c.num.label('num_str'), time_signature.c.den.label('den_str'), literal(0.0).label('num_x'), literal(1.0).label('den_x'), literal(1.0).label('num_y'), literal(0.0).label('den_y') ]).where(and_(name.c.val == 'time_signature', name.c.id == font_name.c.id, name.c.id == font_size.c.id, name.c.id == time_signature.c.id)).\ cte(name='time_signatures_to_xy_info') ''' LOOK HERE ''' time_signatures_to_xy_info_num = time_signatures_to_xy_info.alias('time_signatures_to_xy_info_num') # the line above causes literal binding to fail # comment out the line above and uncomment the line below for the binding to succeed #time_signatures_to_xy_info_num = time_signatures_to_xy_info time_signatures_to_stencils = select([ time_signatures_to_xy_info_num.c.id.label('id'), literal(0).label('sub_id'), time_signatures_to_xy_info_num.c.font_name.label('font_name'), time_signatures_to_xy_info_num.c.font_size.label('font_size'), time_signatures_to_xy_info_num.c.num_str.label('str'), time_signatures_to_xy_info_num.c.num_x.label('x'), time_signatures_to_xy_info_num.c.num_y.label('y'), ]).cte(name="time_signatures_to_stencils") insert_stmt = stencil.insert().from_select(['id', 'sub_id', 'font_name', 'font_size', 'str', 'x', 'y'], select([time_signatures_to_stencils])) print str(insert_stmt) print "!=+~"*20 print "binding partially fails, partially succeeds" print "!=+~"*20 print insert_stmt.compile(compile_kwargs={"literal_binds": True}) ``` |