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
