<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">tons of use cases for TextAsFrom that should be intuitive that don't work.    When we make a TextAsFrom with a positional set of columns, those columns should be welded to it.  The statement should be able to work in any ORM context flawlessly, no reliance on names matching up should be needed as we do not target on name anymore:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    bs = relationship(&quot;B&quot;)


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))

e = create_engine(&quot;sqlite://&quot;, echo='debug')
Base.metadata.create_all(e)

s = Session(e)
s.add_all([
    A(bs=[B(), B()]),
    A(bs=[B(), B()])
])
s.commit()

b1 = aliased(B)

# works
sql = &quot;select a.id, ba.id as bid, ba.a_id from &quot;\
    &quot;a left outer join b as ba on a.id=ba.a_id&quot;

# fails.  why?
# sql = &quot;select a.id as aid, ba.id as bid, ba.a_id from &quot;\
#    &quot;a left outer join b as ba on a.id=ba.a_id&quot;

# fails. why?
# sql = &quot;select a.id as aid, ba.id, ba.a_id from &quot;\
#    &quot;a left outer join b as ba on a.id=ba.a_id&quot;


# are we relying upon names somehow?  we should be able to
# be 100% positional now
t = text(sql).columns(A.id, b1.id, b1.a_id)

q = s.query(A).from_statement(t).options(contains_eager(A.bs, alias=b1))

for a in q:
    print a.id
    print a, a.bs

# forget about if we try a1 = aliased(A) also..."><pre class="notranslate"><code class="notranslate">from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    bs = relationship("B")


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))

e = create_engine("sqlite://", echo='debug')
Base.metadata.create_all(e)

s = Session(e)
s.add_all([
    A(bs=[B(), B()]),
    A(bs=[B(), B()])
])
s.commit()

b1 = aliased(B)

# works
sql = "select a.id, ba.id as bid, ba.a_id from "\
    "a left outer join b as ba on a.id=ba.a_id"

# fails.  why?
# sql = "select a.id as aid, ba.id as bid, ba.a_id from "\
#    "a left outer join b as ba on a.id=ba.a_id"

# fails. why?
# sql = "select a.id as aid, ba.id, ba.a_id from "\
#    "a left outer join b as ba on a.id=ba.a_id"


# are we relying upon names somehow?  we should be able to
# be 100% positional now
t = text(sql).columns(A.id, b1.id, b1.a_id)

q = s.query(A).from_statement(t).options(contains_eager(A.bs, alias=b1))

for a in q:
    print a.id
    print a, a.bs

# forget about if we try a1 = aliased(A) also...
</code></pre></div>
<p dir="auto">I've added docs in <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/sqlalchemy/sqlalchemy/commit/7d268d4bcb5e6205d05ace0ea2fd94895bc2efed/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/commit/7d268d4bcb5e6205d05ace0ea2fd94895bc2efed"><tt>7d268d4</tt></a> and <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/sqlalchemy/sqlalchemy/commit/4f51fa947ffa0cadeab7ad7dcab649ce3fbcf970/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/commit/4f51fa947ffa0cadeab7ad7dcab649ce3fbcf970"><tt>4f51fa9</tt></a> that we may even have to dial back for versions that don't have this feature.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3501/3501.patch">3501.patch</a></p>
<p dir="auto">Hi,</p>
<p dir="auto">I'm trying to upsert a table that has a column named "id" (primary key) and a column named "name" (unique key).</p>
<p dir="auto">The var "value" is a dict, and the following code gives me:</p>
<p dir="auto">TypeError: on_duplicate_key_update() takes exactly 1 argument (2 given)</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="try:                                                          
    stmt = insert(dbmeta.tables['Assets']).values(values)     
    ustmt = stmt.on_duplicate_key_update(values)              
    dbconn.execute(ustmt)                                     
    trans.commit()                                            
except exc.SQLAlchemyError as e:                              
    print(&quot;[ ERROR ] - Rolling back transation (%s)&quot; % e, file=sys.stderr)                
    trans.rollback()                                          
    return False                                              
return True                                                   "><pre class="notranslate"><code class="notranslate">try:                                                          
    stmt = insert(dbmeta.tables['Assets']).values(values)     
    ustmt = stmt.on_duplicate_key_update(values)              
    dbconn.execute(ustmt)                                     
    trans.commit()                                            
except exc.SQLAlchemyError as e:                              
    print("[ ERROR ] - Rolling back transation (%s)" % e, file=sys.stderr)                
    trans.rollback()                                          
    return False                                              
return True                                                   
</code></pre></div>
<p dir="auto">What am I doing wrong?</p>
<p dir="auto">Thanks,<br>
Bruno</p>