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@xxxxxxxxxxx> 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@xxxxxxxxxxx
