case inside values statement

I want to insert data from mysql into a table in postgresql. I want to perform a WHERE NOT EXISTS statement for a particular id.

# Source data: MySQL
   curr_msql.execute(''' SELECT code, subjectname 
                          FROM test_subj ''')

    # Target data: PostgreSQL
    for row in curr_msql:
            curr_psql.execute(''' INSERT INTO subs (
                                                    created, modified,
                                                    subjcode, subjname,
                                                    is_pe_or_nstp)

                                  VALUES (current_timestamp, current_timestamp,
                                          %s, %s,
                                          %s) ''', (row['code'], row['subjectname'],
False))

I want to know how can I replace the "False" to a case statement. Something like

CASE
    WHEN code like '%PE%' or code like '%NSTP%'
    THEN True
    ELSE False
END

I tried to run this code:

for row in curr_msql:
            curr_psql.execute(''' INSERT INTO subs (
                                                    created, modified,
                                                    subjcode, subjname,
                                                    is_pe_or_nstp)

                                  VALUES (current_timestamp, current_timestamp,
                                          %s, %s,
                                          CASE
        WHEN code like '%PE%' or code like '%NSTP%'
        THEN True
        ELSE False
    END) ''', (row['code'], row['subjectname']))

I'm getting IndexError: tuple out of index error message
Any advice pls?

• The destination DB is PostgreSQL, source data is from MySQL.

One part of your problem is that execute() tries to interpret the LIKE expressions ('%PE%') as a placeholder. You have to double all the percent signs, so that the following query in pure SQL (with a pseudocode placeholder)

SELECT ? WHERE 'abc' LIKE '%bc'

turns into

cur.execute("SELECT %s WHERE 'abc' LIKE '%%bc'", ['%s'])
cur.fetchall()
==> [('%s',)]

Since the column code is also from the source table, it needs to be "fed" from the parameters, too.

for row in curr_msql:
    curr_psql.execute(''' 
        INSERT INTO subs (
                           created, modified,
                           subjcode, subjname,
                           is_pe_or_nstp )
        VALUES (current_timestamp, current_timestamp,
                %s, %s,
                CASE
                    WHEN %s LIKE %s OR %s LIKE %s
                    THEN TRUE
                    ELSE FALSE
                END) 
    ''', (row['code'], row['subjectname'], 
          row['code'], '%PE%', 
          row['code'], '%NSTP%'))

and much cleaner, using named parameters:

for row in curr_msql:
    param = dict(code = row['code'], 
                 subjectname = row['subjectname'], 
                 str1 = '%PE%', 
                 str2 = '%NSTP%')
    curr_psql.execute(''' 
        INSERT INTO subs (
                           created, modified,
                           subjcode, subjname,
                           is_pe_or_nstp )
        VALUES (current_timestamp, current_timestamp,
                %(code)s, %(subjectname)s,
                CASE
                    WHEN %(code)s LIKE %(str1)s OR %(code)s LIKE %(str2)s
                    THEN TRUE
                    ELSE FALSE
                END) 
    ''', param)

Link to psycopg2: Basic module usage for further reading.

StackExchange Database Administrators Q#207816, answer score: 5