Concatenate 2 Column Values For One Column

On Tue, May 8, 2018 at 7:17 PM, tango ward <tangoward15@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.

On Tue, May 8, 2018 at 7:44 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tue, May 8, 2018 at 7:17 PM, tango ward <tangoward15@gmail.com> wrote:

I am trying to concatenate the value of column firstname and lastname
from source DB to name column of destination DB.

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']) )

​Actually, what I would do looks nothing like that...

I'd use psql to \copy the relevant information out of the source DB into a
CSV file
I'd use psql to \copy the just-exported data into the target DB into a
staging (temp/unlogged) table
I'd then write, still in the psql script connected to the target machine:

INSERT INTO lib_author
SELECT ...
FROM temp_table;

DROP temp_table; (if unlogged, if its truly a temp it will drop when the
session ends)

A for-loop based migration should be a measure of last resort. SQL is a
set-oriented language/system and you should design your processes to
leverage that. Act on whole tables (or subsets - WHERE clauses - thereof)
at a time and not individual records.

You can access the same API via Python so you wouldn't have to use psql -
but moving csv data in bulk between the servers and performing calculations
in bulk is the way to go is this is going to be anything more than a
one-time toy project and you'll never touch a DB again.

My $0.02

David J.

​

thanks for the ideas Sir.

I haven't touched DB this deep before. Basically I need to migrate the data
of a DB into a new complete system made in Django. The system architecture
created in Django has big difference in terms of tables and columns than
the data from source DB. Source DB doesn't have timestamp data but it is
not null column in destination DB and no default value.

On Wed, May 9, 2018 at 10:54 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On 05/08/2018 07:17 PM, tango ward wrote:

Hi,

Sorry for asking question again.

I am trying to concatenate the value of column firstname and lastname
from source DB to name column of destination DB.

My code so far:

cur_t.execute("""
                SELECT firstname, lastname
                FROM authors;
                """)

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

That is because:

(row['firstname'], row['lastname'])

is making a Python tuple for entry into the last %s.

Not tested but try:

(row['firstname'] + ', ' + row['lastname'])

How can I remove the ( ) in the DB? I can't call the row['firstname']
and row['lastname'] as values without using ( ).

Any suggestion is highly appreciated.

Thanks,
J

--
Adrian Klaver
adrian.klaver@aklaver.com

it works Sir Adrian. Thanks!!

From psycopg2 documentation "*Never* use % or + to merge values into queries
<http://initd.org/psycopg/docs/usage.html#sql-injection&gt;:&quot; but in this
scenario, I can use it, right?

On Wed, May 9, 2018 at 12:21 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote: