<h3 dir="auto">Describe the bug</h3>
<p dir="auto">See the reproduction source code. I select two same columns in Core. Result column descriptions are drastically different when compiled with SQLite dialect. That leads to crashes in the downstream projects that rely on <code class="notranslate">_result_columns</code>, for example, <a href="https://github.com/encode/databases/blob/master/databases/backends/sqlite.py#L180">encode/databases</a>. The subsequent crash is</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    metadata = CursorResultMetaData(context, cursor.description)
/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/cursor.py:180: in __init__
    raw = self._merge_cursor_description(
/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/cursor.py:415: in _merge_cursor_description
    return [
/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/cursor.py:415: in &lt;listcomp&gt;
    return [
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = &lt;sqlalchemy.engine.cursor.CursorResultMetaData object at 0x7f2e0195bee0&gt;
context = &lt;sqlalchemy.dialects.sqlite.base.SQLiteExecutionContext object at 0x7f2e01928e50&gt;
cursor_description = (('pr_node_id', None, None, None, None, None, ...), ('repository_full_name', None, None, None, None, None, ...), ('rel...e, None, None, ...), ('author', None, None, None, None, None, ...), ('merger', None, None, None, None, None, ...), ...)
result_columns = [('pr_node_id', 'pr_node_id', (Column('pr_node_id', BigInteger(), table=&lt;done_pull_request_facts&gt;, primary_key=True, n...(), table=&lt;done_pull_request_facts&gt;), 'merger', 'merger', 'github.done_pull_request_facts_merger'), BigInteger()), ...]

    def _merge_textual_cols_by_position(
        self, context, cursor_description, result_columns
    ):
        num_ctx_cols = len(result_columns) if result_columns else None
    
        if num_ctx_cols &gt; len(cursor_description):
            util.warn(
                &quot;Number of columns in textual SQL (%d) is &quot;
                &quot;smaller than number of columns requested (%d)&quot;
                % (num_ctx_cols, len(cursor_description))
            )
        seen = set()
        for (
            idx,
            colname,
            untranslated,
            coltype,
        ) in self._colnames_from_description(context, cursor_description):
            if idx &lt; num_ctx_cols:
                ctx_rec = result_columns[idx]
                obj = ctx_rec[RM_OBJECTS]
                ridx = idx
                mapped_type = ctx_rec[RM_TYPE]
&gt;               if obj[0] in seen:
E               IndexError: tuple index out of range

/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/cursor.py:503: IndexError"><pre class="notranslate"><code class="notranslate">    metadata = CursorResultMetaData(context, cursor.description)
/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/cursor.py:180: in __init__
    raw = self._merge_cursor_description(
/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/cursor.py:415: in _merge_cursor_description
    return [
/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/cursor.py:415: in &lt;listcomp&gt;
    return [
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = &lt;sqlalchemy.engine.cursor.CursorResultMetaData object at 0x7f2e0195bee0&gt;
context = &lt;sqlalchemy.dialects.sqlite.base.SQLiteExecutionContext object at 0x7f2e01928e50&gt;
cursor_description = (('pr_node_id', None, None, None, None, None, ...), ('repository_full_name', None, None, None, None, None, ...), ('rel...e, None, None, ...), ('author', None, None, None, None, None, ...), ('merger', None, None, None, None, None, ...), ...)
result_columns = [('pr_node_id', 'pr_node_id', (Column('pr_node_id', BigInteger(), table=&lt;done_pull_request_facts&gt;, primary_key=True, n...(), table=&lt;done_pull_request_facts&gt;), 'merger', 'merger', 'github.done_pull_request_facts_merger'), BigInteger()), ...]

    def _merge_textual_cols_by_position(
        self, context, cursor_description, result_columns
    ):
        num_ctx_cols = len(result_columns) if result_columns else None
    
        if num_ctx_cols &gt; len(cursor_description):
            util.warn(
                "Number of columns in textual SQL (%d) is "
                "smaller than number of columns requested (%d)"
                % (num_ctx_cols, len(cursor_description))
            )
        seen = set()
        for (
            idx,
            colname,
            untranslated,
            coltype,
        ) in self._colnames_from_description(context, cursor_description):
            if idx &lt; num_ctx_cols:
                ctx_rec = result_columns[idx]
                obj = ctx_rec[RM_OBJECTS]
                ridx = idx
                mapped_type = ctx_rec[RM_TYPE]
&gt;               if obj[0] in seen:
E               IndexError: tuple index out of range

/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/cursor.py:503: IndexError
</code></pre></div>
<h3 dir="auto">To Reproduce</h3>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import Column, Integer, select
from sqlalchemy.dialects.sqlite import pysqlite
from sqlalchemy.engine import ExecutionContext
from sqlalchemy.ext.declarative import declarative_base


class Foo(declarative_base()):
    __tablename__ = &quot;whatever&quot;

    bar = Column(Integer(), primary_key=True)


class CompilationContext:
    def __init__(self, context: ExecutionContext):
        self.context = context


def main():
    query = select([Foo.bar, Foo.bar])
    compiled = query.compile(dialect=pysqlite.dialect(paramstyle=&quot;qmark&quot;))
    print(compiled._result_columns[0])
    print(compiled._result_columns[1])


if __name__ == &quot;__main__&quot;:
    main()"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>, <span class="pl-v">Integer</span>, <span class="pl-s1">select</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">dialects</span>.<span class="pl-s1">sqlite</span> <span class="pl-k">import</span> <span class="pl-s1">pysqlite</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">engine</span> <span class="pl-k">import</span> <span class="pl-v">ExecutionContext</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</span>


<span class="pl-k">class</span> <span class="pl-v">Foo</span>(<span class="pl-en">declarative_base</span>()):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"whatever"</span>

    <span class="pl-s1">bar</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>(), <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)


<span class="pl-k">class</span> <span class="pl-v">CompilationContext</span>:
    <span class="pl-k">def</span> <span class="pl-en">__init__</span>(<span class="pl-s1">self</span>, <span class="pl-s1">context</span>: <span class="pl-v">ExecutionContext</span>):
        <span class="pl-s1">self</span>.<span class="pl-s1">context</span> <span class="pl-c1">=</span> <span class="pl-s1">context</span>


<span class="pl-k">def</span> <span class="pl-en">main</span>():
    <span class="pl-s1">query</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>([<span class="pl-v">Foo</span>.<span class="pl-s1">bar</span>, <span class="pl-v">Foo</span>.<span class="pl-s1">bar</span>])
    <span class="pl-s1">compiled</span> <span class="pl-c1">=</span> <span class="pl-s1">query</span>.<span class="pl-en">compile</span>(<span class="pl-s1">dialect</span><span class="pl-c1">=</span><span class="pl-s1">pysqlite</span>.<span class="pl-en">dialect</span>(<span class="pl-s1">paramstyle</span><span class="pl-c1">=</span><span class="pl-s">"qmark"</span>))
    <span class="pl-en">print</span>(<span class="pl-s1">compiled</span>.<span class="pl-s1">_result_columns</span>[<span class="pl-c1">0</span>])
    <span class="pl-en">print</span>(<span class="pl-s1">compiled</span>.<span class="pl-s1">_result_columns</span>[<span class="pl-c1">1</span>])


<span class="pl-k">if</span> <span class="pl-s1">__name__</span> <span class="pl-c1">==</span> <span class="pl-s">"__main__"</span>:
    <span class="pl-en">main</span>()</pre></div>
<h3 dir="auto">Error</h3>
<p dir="auto">The provided code prints:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="('bar', 'bar', (Column('bar', Integer(), table=&lt;whatever&gt;, primary_key=True, nullable=False), 'bar', 'bar', 'whatever_bar'), Integer())
('bar__1', '%(575564119221534721 bar_)s', (), Integer())"><pre class="notranslate"><code class="notranslate">('bar', 'bar', (Column('bar', Integer(), table=&lt;whatever&gt;, primary_key=True, nullable=False), 'bar', 'bar', 'whatever_bar'), Integer())
('bar__1', '%(575564119221534721 bar_)s', (), Integer())
</code></pre></div>
<p dir="auto">The third tuple element in the second item is an empty tuple instead of a proper pointer to Column.</p>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li>OS: Ubuntu 20.04</li>
<li>Python: 3.8.10</li>
<li>SQLAlchemy: 1.4.29</li>
<li>Database: SQLite</li>
<li>DBAPI (eg: psycopg, cx_oracle, mysqlclient): pysqlite</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto">This code works differently on 1.3.x, it prints:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="('bar', 'bar', (Column('bar', Integer(), table=&lt;whatever&gt;, primary_key=True, nullable=False), 'bar', 'bar'), Integer())
Traceback (most recent call last):
  File &quot;reproduce.py&quot;, line 26, in &lt;module&gt;
    main()
  File &quot;reproduce.py&quot;, line 22, in main
    print(compiled._result_columns[1])
IndexError: list index out of range"><pre class="notranslate"><code class="notranslate">('bar', 'bar', (Column('bar', Integer(), table=&lt;whatever&gt;, primary_key=True, nullable=False), 'bar', 'bar'), Integer())
Traceback (most recent call last):
  File "reproduce.py", line 26, in &lt;module&gt;
    main()
  File "reproduce.py", line 22, in main
    print(compiled._result_columns[1])
IndexError: list index out of range
</code></pre></div>
<p dir="auto">In other words, 1.3.x does not generate the second element for the duplicate.</p>
<p dir="auto"><strong>Migrated issue, originally created by wenlong_lu</strong></p>
<p dir="auto">I have several large(billions of rows) tables which need upsert operations. I have checked the <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="384613771" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/960" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/960/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/960">#960</a> and <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="384626284" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/2551" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/2551/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/2551">#2551</a>, and still did not get what I want. if you think this is duplicated, pls close it.</p>
<h3 dir="auto">upsert idea from PostgreSQL official page</h3>
<p dir="auto">I just follow the idea presented on postgres page:<br>
<a href="http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING" rel="nofollow">http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING</a></p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis')"><pre class="notranslate"><span class="pl-k">CREATE</span> <span class="pl-k">TABLE</span> <span class="pl-en">db</span> (a <span class="pl-k">INT</span> <span class="pl-k">PRIMARY KEY</span>, b <span class="pl-k">TEXT</span>);

<span class="pl-k">CREATE</span> <span class="pl-k">FUNCTION</span> <span class="pl-en">merge_db</span>(key <span class="pl-k">INT</span>, data <span class="pl-k">TEXT</span>) RETURNS VOID <span class="pl-k">AS</span>
$$
<span class="pl-k">BEGIN</span>
    LOOP
        <span class="pl-c"><span class="pl-c">--</span> first try to update the key</span>
        <span class="pl-k">UPDATE</span> db <span class="pl-k">SET</span> b <span class="pl-k">=</span> data <span class="pl-k">WHERE</span> a <span class="pl-k">=</span> key;
        IF found THEN
            RETURN;
        END IF;
        <span class="pl-c"><span class="pl-c">--</span> not there, so try to insert the key</span>
        <span class="pl-c"><span class="pl-c">--</span> if someone else inserts the same key concurrently,</span>
        <span class="pl-c"><span class="pl-c">--</span> we could get a unique-key failure</span>
        <span class="pl-k">BEGIN</span>
            <span class="pl-k">INSERT INTO</span> db(a,b) <span class="pl-k">VALUES</span> (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            <span class="pl-c"><span class="pl-c">--</span> Do nothing, and loop to try the UPDATE again.</span>
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

<span class="pl-k">SELECT</span> merge_db(<span class="pl-c1">1</span>, <span class="pl-s"><span class="pl-pds">'</span>david<span class="pl-pds">'</span></span>);
<span class="pl-k">SELECT</span> merge_db(<span class="pl-c1">1</span>, <span class="pl-s"><span class="pl-pds">'</span>dennis<span class="pl-pds">'</span></span>)</pre></div>
<h3 dir="auto">Our modified version</h3>
<p dir="auto">Since our insert and update statement are dynamic and complex type(jsonb, hstore, etc) involved, We prefer to build sql statements on sqlalchemy side. our pgsql script follows as:</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="-- Function: merge_record(text, text)
-- DROP FUNCTION merge_record(text, text);

CREATE OR REPLACE FUNCTION merge_record(update_expression text, insert_expression text)
  RETURNS integer AS
$BODY$
declare
  row_affected integer;
BEGIN
    -- usually loop twice would be ok, here we set max value as 50 times.
    -- it should be enough
    FOR i IN 1..50 LOOP
        -- first try to update the key
        execute update_expression;
        GET DIAGNOSTICS row_affected = ROW_COUNT;
        IF row_affected &gt; 0 THEN
            RETURN 1;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            execute insert_expression;
            RETURN 1;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
   END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION merge_record(text, text)
  OWNER TO postgres;"><pre class="notranslate"><span class="pl-c"><span class="pl-c">--</span> Function: merge_record(text, text)</span>
<span class="pl-c"><span class="pl-c">--</span> DROP FUNCTION merge_record(text, text);</span>

<span class="pl-k">CREATE OR REPLACE</span> <span class="pl-k">FUNCTION</span> <span class="pl-en">merge_record</span>(update_expression <span class="pl-k">text</span>, insert_expression <span class="pl-k">text</span>)
  RETURNS <span class="pl-k">integer</span> <span class="pl-k">AS</span>
$BODY$
declare
  row_affected <span class="pl-k">integer</span>;
<span class="pl-k">BEGIN</span>
    <span class="pl-c"><span class="pl-c">--</span> usually loop twice would be ok, here we set max value as 50 times.</span>
    <span class="pl-c"><span class="pl-c">--</span> it should be enough</span>
    FOR i <span class="pl-k">IN</span> <span class="pl-c1">1</span>..<span class="pl-c1">50</span> LOOP
        <span class="pl-c"><span class="pl-c">--</span> first try to update the key</span>
        execute update_expression;
        GET DIAGNOSTICS row_affected <span class="pl-k">=</span> ROW_COUNT;
        IF row_affected <span class="pl-k">&gt;</span> <span class="pl-c1">0</span> THEN
            RETURN <span class="pl-c1">1</span>;
        END IF;
        <span class="pl-c"><span class="pl-c">--</span> not there, so try to insert the key</span>
        <span class="pl-c"><span class="pl-c">--</span> if someone else inserts the same key concurrently,</span>
        <span class="pl-c"><span class="pl-c">--</span> we could get a unique-key failure</span>
        <span class="pl-k">BEGIN</span>
            execute insert_expression;
            RETURN <span class="pl-c1">1</span>;
        EXCEPTION WHEN unique_violation THEN
            <span class="pl-c"><span class="pl-c">--</span> Do nothing, and loop to try the UPDATE again.</span>
        END;
   END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST <span class="pl-c1">100</span>;
<span class="pl-k">ALTER</span> <span class="pl-k">FUNCTION</span> merge_record(<span class="pl-k">text</span>, <span class="pl-k">text</span>)
  OWNER TO postgres;</pre></div>
<h3 dir="auto">Target script to be generated</h3>
<p dir="auto">SO what i want to build on client side is</p>
<div class="highlight highlight-source-sql notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="select merge_record(update_expression, insert_expression);
e.g.
select merge_record(
     'update target_table set s1=&quot;v1&quot;, jsonb2={&quot;k1&quot;: &quot;v1&quot;, &quot;k1&quot;: &quot;v2&quot;}, hstore3={&quot;k3&quot;=&gt;&quot;v3&quot;, &quot;k4&quot;=&gt;&quot;v4&quot;} where unique_id=45678766', 
     'insert into target_table (s1, jsonb2, hstore3, unique_id) VALUES(&quot;v1&quot;, {&quot;k1&quot;: &quot;v1&quot;, &quot;k1&quot;: &quot;v2&quot;}, {&quot;k3&quot;=&gt;&quot;v3&quot;, &quot;k4&quot;=&gt;&quot;v4&quot;}, 45678766)'
);"><pre class="notranslate"><span class="pl-k">select</span> merge_record(update_expression, insert_expression);
<span class="pl-c1">e</span>.<span class="pl-c1">g</span>.
<span class="pl-k">select</span> merge_record(
     <span class="pl-s"><span class="pl-pds">'</span>update target_table set s1="v1", jsonb2={"k1": "v1", "k1": "v2"}, hstore3={"k3"=&gt;"v3", "k4"=&gt;"v4"} where unique_id=45678766<span class="pl-pds">'</span></span>, 
     <span class="pl-s"><span class="pl-pds">'</span>insert into target_table (s1, jsonb2, hstore3, unique_id) VALUES("v1", {"k1": "v1", "k1": "v2"}, {"k3"=&gt;"v3", "k4"=&gt;"v4"}, 45678766)<span class="pl-pds">'</span></span>
);</pre></div>
<h3 dir="auto">Problems we encountered</h3>
<p dir="auto">WE have used sqlalchemy SQL language expression to build the SQL statements dynamically. BUT the problem is that, values have many special characters, like $, \n etc (some of them are html text, so everything is possible). The exceptions are, that is invalid SQL statements generated,  always happen several times per month.</p>
<h2 dir="auto">Reasons for the problems</h2>
<p dir="auto"><strong>SO we think we must miss sth</strong>. pls give some suggestion if any.  related code follows as:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="raw_upsert_sql_statement = &quot;select merge_record('%s', '%s')&quot; % (update_expression, insert_expression)
conn.execute(text(raw_upsert_sql_statement))

insert_expression
        dialect = postgresql.dialect()
        target_table = self.__table__
        stmt = target_table.insert().values(attr_value_hash)
        sql_template = unicode(stmt.compile(dialect=dialect))
        attr_value_hash = self._convert_safe_sql_str(attr_value_hash)
        insert_expression = sql_template % (attr_value_hash)

update_expression
        dialect = postgresql.dialect()
        stmt = target_table.update().where(eval(self.condition_script)).values(attr_template_hash)
        sql_template = unicode(stmt.compile(dialect=dialect))
        attr_value_hash = self._convert_safe_sql_str(attr_value_hash)
        update_expression = sql_template % (attr_value_hash)

### this should be the stupid parts
def _convert_safe_sql_str(self, params):
        ret_params = {} 
        log.debug(&quot;Before converting: %s&quot; % params)
        
        # error:'dict' object does not support indexing
        # fix:  replace(&quot;%&quot;, &quot;%%&quot;)
        for k, v in params.iteritems():
            try:
                if type(v) in (datetime, str, unicode):
                    ret_params[k] = ('$$%s$$' % v).replace(&quot;'&quot;, &quot;&quot;).replace(&quot;%&quot;, &quot;%%&quot;)
                elif type(v) in (MutableList, list):
                    ret_params[k] = ('$$%s$$' % v).replace(&quot;[&quot;, &quot;{&quot;).replace(&quot;]&quot;, &quot;}&quot;).replace(&quot;u'&quot;, &quot;&quot;).replace(&quot;'&quot;, &quot;&quot;).replace(&quot;%&quot;, &quot;%%&quot;)
                elif type(v) == MutableDict:
                    ret_params[k] = ('$$%s$$' % v).replace(&quot;{&quot;, &quot;&quot;).replace(&quot;}&quot;, &quot;&quot;).replace(&quot;:&quot;, &quot;=&gt;&quot;).replace(&quot;'&quot;, &quot;&quot;).replace(&quot;%&quot;, &quot;%%&quot;)
                elif type(v) == dict:
                    ret_params[k] = ('$$%s$$' % json.dumps(v)).replace(&quot;\'&quot;, &quot;&quot;).replace(&quot;%&quot;, &quot;%%&quot;)
                elif type(v) in (int, bool, float):
                    ret_params[k] = v
            except Exception as ex:
                log.error(ex.message)
                log.error(traceback.format_exc())
                raise ex

        log.debug(&quot;After converting: %s&quot; % ret_params)
        return ret_params"><pre class="notranslate"><span class="pl-s1">raw_upsert_sql_statement</span> <span class="pl-c1">=</span> <span class="pl-s">"select merge_record('%s', '%s')"</span> <span class="pl-c1">%</span> (<span class="pl-s1">update_expression</span>, <span class="pl-s1">insert_expression</span>)
<span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-en">text</span>(<span class="pl-s1">raw_upsert_sql_statement</span>))

<span class="pl-s1">insert_expression</span>
        <span class="pl-s1">dialect</span> <span class="pl-c1">=</span> <span class="pl-s1">postgresql</span>.<span class="pl-en">dialect</span>()
        <span class="pl-s1">target_table</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>.<span class="pl-s1">__table__</span>
        <span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">target_table</span>.<span class="pl-en">insert</span>().<span class="pl-en">values</span>(<span class="pl-s1">attr_value_hash</span>)
        <span class="pl-s1">sql_template</span> <span class="pl-c1">=</span> <span class="pl-en">unicode</span>(<span class="pl-s1">stmt</span>.<span class="pl-en">compile</span>(<span class="pl-s1">dialect</span><span class="pl-c1">=</span><span class="pl-s1">dialect</span>))
        <span class="pl-s1">attr_value_hash</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>.<span class="pl-en">_convert_safe_sql_str</span>(<span class="pl-s1">attr_value_hash</span>)
        <span class="pl-s1">insert_expression</span> <span class="pl-c1">=</span> <span class="pl-s1">sql_template</span> <span class="pl-c1">%</span> (<span class="pl-s1">attr_value_hash</span>)

<span class="pl-s1">update_expression</span>
        <span class="pl-s1">dialect</span> <span class="pl-c1">=</span> <span class="pl-s1">postgresql</span>.<span class="pl-en">dialect</span>()
        <span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">target_table</span>.<span class="pl-en">update</span>().<span class="pl-en">where</span>(<span class="pl-en">eval</span>(<span class="pl-s1">self</span>.<span class="pl-s1">condition_script</span>)).<span class="pl-en">values</span>(<span class="pl-s1">attr_template_hash</span>)
        <span class="pl-s1">sql_template</span> <span class="pl-c1">=</span> <span class="pl-en">unicode</span>(<span class="pl-s1">stmt</span>.<span class="pl-en">compile</span>(<span class="pl-s1">dialect</span><span class="pl-c1">=</span><span class="pl-s1">dialect</span>))
        <span class="pl-s1">attr_value_hash</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>.<span class="pl-en">_convert_safe_sql_str</span>(<span class="pl-s1">attr_value_hash</span>)
        <span class="pl-s1">update_expression</span> <span class="pl-c1">=</span> <span class="pl-s1">sql_template</span> <span class="pl-c1">%</span> (<span class="pl-s1">attr_value_hash</span>)

<span class="pl-c">### this should be the stupid parts</span>
<span class="pl-k">def</span> <span class="pl-en">_convert_safe_sql_str</span>(<span class="pl-s1">self</span>, <span class="pl-s1">params</span>):
        <span class="pl-s1">ret_params</span> <span class="pl-c1">=</span> {} 
        <span class="pl-s1">log</span>.<span class="pl-en">debug</span>(<span class="pl-s">"Before converting: %s"</span> <span class="pl-c1">%</span> <span class="pl-s1">params</span>)
        
        <span class="pl-c"># error:'dict' object does not support indexing</span>
        <span class="pl-c"># fix:  replace("%", "%%")</span>
        <span class="pl-k">for</span> <span class="pl-s1">k</span>, <span class="pl-s1">v</span> <span class="pl-c1">in</span> <span class="pl-s1">params</span>.<span class="pl-en">iteritems</span>():
            <span class="pl-k">try</span>:
                <span class="pl-k">if</span> <span class="pl-en">type</span>(<span class="pl-s1">v</span>) <span class="pl-c1">in</span> (<span class="pl-s1">datetime</span>, <span class="pl-s1">str</span>, <span class="pl-s1">unicode</span>):
                    <span class="pl-s1">ret_params</span>[<span class="pl-s1">k</span>] <span class="pl-c1">=</span> (<span class="pl-s">'$$%s$$'</span> <span class="pl-c1">%</span> <span class="pl-s1">v</span>).<span class="pl-en">replace</span>(<span class="pl-s">"'"</span>, <span class="pl-s">""</span>).<span class="pl-en">replace</span>(<span class="pl-s">"%"</span>, <span class="pl-s">"%%"</span>)
                <span class="pl-k">elif</span> <span class="pl-en">type</span>(<span class="pl-s1">v</span>) <span class="pl-c1">in</span> (<span class="pl-v">MutableList</span>, <span class="pl-s1">list</span>):
                    <span class="pl-s1">ret_params</span>[<span class="pl-s1">k</span>] <span class="pl-c1">=</span> (<span class="pl-s">'$$%s$$'</span> <span class="pl-c1">%</span> <span class="pl-s1">v</span>).<span class="pl-en">replace</span>(<span class="pl-s">"["</span>, <span class="pl-s">"{"</span>).<span class="pl-en">replace</span>(<span class="pl-s">"]"</span>, <span class="pl-s">"}"</span>).<span class="pl-en">replace</span>(<span class="pl-s">"u'"</span>, <span class="pl-s">""</span>).<span class="pl-en">replace</span>(<span class="pl-s">"'"</span>, <span class="pl-s">""</span>).<span class="pl-en">replace</span>(<span class="pl-s">"%"</span>, <span class="pl-s">"%%"</span>)
                <span class="pl-k">elif</span> <span class="pl-en">type</span>(<span class="pl-s1">v</span>) <span class="pl-c1">==</span> <span class="pl-v">MutableDict</span>:
                    <span class="pl-s1">ret_params</span>[<span class="pl-s1">k</span>] <span class="pl-c1">=</span> (<span class="pl-s">'$$%s$$'</span> <span class="pl-c1">%</span> <span class="pl-s1">v</span>).<span class="pl-en">replace</span>(<span class="pl-s">"{"</span>, <span class="pl-s">""</span>).<span class="pl-en">replace</span>(<span class="pl-s">"}"</span>, <span class="pl-s">""</span>).<span class="pl-en">replace</span>(<span class="pl-s">":"</span>, <span class="pl-s">"=&gt;"</span>).<span class="pl-en">replace</span>(<span class="pl-s">"'"</span>, <span class="pl-s">""</span>).<span class="pl-en">replace</span>(<span class="pl-s">"%"</span>, <span class="pl-s">"%%"</span>)
                <span class="pl-k">elif</span> <span class="pl-en">type</span>(<span class="pl-s1">v</span>) <span class="pl-c1">==</span> <span class="pl-s1">dict</span>:
                    <span class="pl-s1">ret_params</span>[<span class="pl-s1">k</span>] <span class="pl-c1">=</span> (<span class="pl-s">'$$%s$$'</span> <span class="pl-c1">%</span> <span class="pl-s1">json</span>.<span class="pl-en">dumps</span>(<span class="pl-s1">v</span>)).<span class="pl-en">replace</span>(<span class="pl-s">"<span class="pl-cce">\'</span>"</span>, <span class="pl-s">""</span>).<span class="pl-en">replace</span>(<span class="pl-s">"%"</span>, <span class="pl-s">"%%"</span>)
                <span class="pl-k">elif</span> <span class="pl-en">type</span>(<span class="pl-s1">v</span>) <span class="pl-c1">in</span> (<span class="pl-s1">int</span>, <span class="pl-s1">bool</span>, <span class="pl-s1">float</span>):
                    <span class="pl-s1">ret_params</span>[<span class="pl-s1">k</span>] <span class="pl-c1">=</span> <span class="pl-s1">v</span>
            <span class="pl-k">except</span> <span class="pl-v">Exception</span> <span class="pl-k">as</span> <span class="pl-s1">ex</span>:
                <span class="pl-s1">log</span>.<span class="pl-en">error</span>(<span class="pl-s1">ex</span>.<span class="pl-s1">message</span>)
                <span class="pl-s1">log</span>.<span class="pl-en">error</span>(<span class="pl-s1">traceback</span>.<span class="pl-en">format_exc</span>())
                <span class="pl-k">raise</span> <span class="pl-s1">ex</span>

        <span class="pl-s1">log</span>.<span class="pl-en">debug</span>(<span class="pl-s">"After converting: %s"</span> <span class="pl-c1">%</span> <span class="pl-s1">ret_params</span>)
        <span class="pl-k">return</span> <span class="pl-s1">ret_params</span></pre></div>
<p dir="auto">thx a lot.</p>
<p dir="auto">wenlong</p>