Re: case statement within insert

On Fri, May 25, 2018 at 10:19 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 05/25/2018 07:05 AM, Adrian Klaver wrote:
>
>> 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']?
>>
>
> Insufficient caffeine.
>
> ...
> WHEN %s like '%%PE%%' or %s like '%%NSTP%%'
>
> ...
> , (row['code'], row['subjectname'], row['code'], row['code'])
>
> FYI this is why I like the named parameters then the above could be
> shortened to:
>
> {'code': row['code'], 'subjectname': row['subjectname']}
>
> and you get clearer query code:
>
> VALUES (current_timestamp, current_timestamp,
> %(code)s, %(subjectname)s,
> CASE
> WHEN %(code)s like '%%PE%%' or %(code)s like '%%NSTP%%'
>
> THEN True
> ELSE False
> END)
>
>
>>
>>
>>
>>> 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
>

Using %s works. I also learned that if I just use '%PE%' or '%NSTP%', the
LIKE expression will treat them as placeholder. Solution is to double the
percent signs.

Thanks a lot guys! I love you all.