On Tue, May 8, 2018 at 7:17 PM, tango ward <tangowar...@gmail.com> wrote:
> I am trying to concatenate the value of column firstname and lastname from
> source DB to name column of destination DB.
>
> (SELECT CONCAT(first_name, ',', last_name) AS
> name FROM lib_author LIMIT 1)
> )
> """, (row['lastname'], row['firstname'], ''))
>
> The code will take the first and lastname of the FIRST data existing on
> the destination table.
>
That seems so not useful (and "first" is random here since you lack an
ORDER BY)...and is "first,last" with no space following the comma a
cultural thing I'm unfamiliar with?
> I modified the code, instead of running SELECT and CONCAT, I passed string
> formatter and call the row['firstname'], row['lastname']
>
> for row in cur_t:
> cur_p.execute("""
> INSERT INTO lib_author (
> created, modified,
> last_name,
> first_name, country,
> school_id, name)
> VALUES (current_timestamp, current_timestamp, %s, %s,
> %s,
> (SELECT id FROM ed_school WHERE name='My Test
> School'),
> %s
> )
> """, (row['lastname'], row['firstname'], '',
> (row['firstname'], row['lastname']) )
>
> The second code works but it includes the parenthesis in the DB.
>
I'm somewhat surprised that's the only oddity you observed...
> How can I remove the ( ) in the DB? I can't call the row['firstname'] and
> row['lastname'] as values without using ( ).
>
1. Store the desired value, complete, in a variable and pass that variable
to cur_p.
2. Pass the row[] constructs individually and write "%s || ',' || %s" (or
use the CONCAT function you discovered earlier) instead of a single %s for
the "name" column
I'd probably write it as:
INSERT INTO lib_author (...)
SELECT
current_timestamp AS ct,
current_timestamp AS mt,
vals.last_name,
vals.first_name,
vals.country,
(SELECT id ....) AS school_id,
vals.last_name || ', ' || vals.first_name
FROM (VALUES (%s, %s, %s)) vals (first_name, last_name, country)
And I'd probably rely on defaults for the timestamp columns and only do:
INSERT INTO lib_author (last_name, first_name, country, school_id, name)
SELECT ...
David J.
