Postgres Pro
Downloads
Home   >   mailing lists

Thread: case statement within insert

case statement within insert

From
tango ward
Date:


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?






Re: case statement within insert

From
"David G. Johnston"
Date:
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. 

Re: case statement within insert

From
Adrian Klaver
Date:
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

Re: case statement within insert

From
Adrian Klaver
Date:
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

Re: case statement within insert

From
Adrian Klaver
Date:
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

Re: case statement within insert

From
Francisco Olarte
Date:
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.

Re: case statement within insert

From
tango ward
Date:

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.

Re: case statement within insert

From
"Peter J. Holzer"
Date:
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/>
Attachment