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>:" but in this
scenario, I can use it, right?
On Wed, May 9, 2018 at 12:21 PM, Adrian Klaver <adrian.kla...@aklaver.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.kla...@aklaver.com
>
