Re: case statement within insert

On 05/25/2018 06:52 AM, Adrian Klaver wrote:
> On 05/25/2018 02:04 AM, tango ward wrote:
>>
>>
>> I want to insert data from mysql into a table in postgresql. I want to
>> check when the subjectcode contains PE or NSTP so I can assign True or
>> False to another column in destination DB.
>>
>>
>> # Source data:
>>
>>   # Source data: MySQL
>>     curr_msql.execute(''' SELECT code, subjectname
>>                            FROM test_subj ''')
>>
>> # Destination
>> 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%%'
>
> Shouldn't the above be?:
>
> subjcode like '%%PE%%' or subjcode like '%%NSTP%%'

Turns out that won't work as you cannot refer to a column in the CASE:

cur.execute("insert into cell_per(category, cell_per, season,
plant_type, short_category) values('herb test', 1, 'annual', CASE WHEN
category like '%%herb%%' THEN 'herb' ELSE 'flower' END, 'ht' )")

ProgrammingError: column "category" does not exist
LINE 1: ...gory) values('herb test', 1, 'annual', CASE WHEN category l...
^
HINT: There is a column named "category" in table "cell_per", but it
cannot be referenced from this part of the query.

This works:

cur.execute("insert into cell_per(category, cell_per, season,
plant_type, short_category) values(%s, 1, 'annual', CASE WHEN %s like
'%%herb%%' THEN 'herb' ELSE 'flower' END, 'ht' )", ('herb test', 'herb
test'))

So change code to row['code']?

>
>>          THEN True
>>          ELSE False
>>      END) ''', (row['code'], row['subjectname']))
>>
>> I am getting TypeError: not all arguments converted during string
>> formatting.
>>
>> Any advice pls?
>>
>>
>>
>>
>>
>>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com