On Fri, May 25, 2018 at 10:19 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
Insufficient caffeine.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']?
...
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.klaver@xxxxxxxxxxx
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.
