case statement within insert
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%%'
THEN True
ELSE False
END) ''', (row['code'], row['subjectname']))
I am getting TypeError: not all arguments converted during string
formatting.
Any advice pls?
On Friday, May 25, 2018, tango ward <tangoward15@gmail.com> wrote:
WHEN code like '%%PE%%' or code like '%%NSTP%%'
I am getting TypeError: not all arguments converted during string
formatting.Any advice pls?
Unclear how to inject percent signs in the query string here. I'd just
avoid them and use regular expressions.
David J.
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%%'
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
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
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.klaver@aklaver.com
On Fri, May 25, 2018 at 11:04 AM, tango ward <tangoward15@gmail.com> wrote:
CASE
WHEN code like '%%PE%%' or code like '%%NSTP%%'
THEN True
ELSE False
I cannot advise you on the %% stuff, which I do not totally understand, but
CASE WHEN condition THEN true ELSE false END
when contition is ( as it should in a CASE ) a boolean expression is a
classic antipattern , just use 'condition' or '(condition)'
Francisco Olarte.
On Fri, May 25, 2018 at 10:19 PM, Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.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.
On 2018-05-25 17:04:25 +0800, 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%%'
THEN True
ELSE False
END) ''', (row['code'], row['subjectname']))
Others have already explained why that doesn't work and how you can fix
it.
But since you are inserting one row at a time in a Python loop, I don't
see any advantage in writing the condition in SQL. Do it in Python
instead:
for row in curr_msql:
is_pe_or_nstp = 'PE' in row['code'] or 'NSTP' in row['code']
curr_psql.execute(''' INSERT INTO subs (
created, modified,
subjcode, subjname,
is_pe_or_nstp)
VALUES (current_timestamp, current_timestamp,
%s, %s,
%s)
''',
(row['code'], row['subjectname'],
is_pe_or_nstp,))
A bit more readable, IMHO.
Alternatively, import the complete table *unchanged* from MySQL (you can
use copy_from() for this which is much faster than individual inserts),
and then convert it with a single SQL statement.
hp
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>