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/>