Обсуждение: Concatenate 2 Column Values For One Column
Hi,
Sorry for asking question again. 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'], ''))
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'),
)
""", (row['lastname'], row['firstname'], '', (row['firstname'], row['lastname']) )
""", (row['lastname'], row['firstname'], '', (row['firstname'], row['lastname']) )
The second code works but it includes the parenthesis in the DB.
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
The code will take the first and lastname of the FIRST data existing on the destination table.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'], ''))
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']%s
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'),)
""", (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.
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:%s
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'),)
""", (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:
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:%s
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'),)
""", (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 fileI'd use psql to \copy the just-exported data into the target DB into a staging (temp/unlogged) tableI'd then write, still in the psql script connected to the target machine:INSERT INTO lib_authorSELECT ...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.02David J.
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:" 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:
That is because: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.
(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