Re: case statement within insert

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(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>