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'),
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 ...
