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.klaver@aklaver.com