Error while updating MySQL DB from PostgreSQL DB

Question

I need to update/insert rows to MySQL database using the data from Postgres DB.So here is the script which i'm using but getting the below error while i schedule this in Jenkins.

Can anyone please guide on what i can do/change to rectify this.

File "signup.py", line 80, in <module>
11:59:27     cur_msql_1.execute(msql_insert_1, row)
11:59:27   File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 209, in execute
11:59:27     res = self._query(query)
11:59:27   File "/usr/local/lib/python3.5/dist-packages/MySQLdb/cursors.py", line 315, in _query
11:59:27     db.query(q)
11:59:27   File "/usr/local/lib/python3.5/dist-packages/MySQLdb/connections.py", line 239, in query
11:59:27     _mysql.connection.query(self, query)
11:59:27 MySQLdb._exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'"timestamp", ip, store_id, confirmed_at) SELECT \'[email protected]\', 15463\' at line 2')
11:59:27 Build step 'Execute shell' marked build as failure
11:59:27 Finished: FAILURE

Below is the entire code:

import psycopg2
import os
import time
import MySQLdb

import sys
from pprint import pprint
from datetime import datetime
from utils.config import Configuration as Config
from utils.postgres_helper import get_connection
from utils.utils import get_global_config


# MySQLdb connection
try:
    source_host = 'magento'
    conf = get_global_config()
    cnx_msql = MySQLdb.connect(host=conf.get(source_host, 'host'),
                               user=conf.get(source_host, 'user'),
                               passwd=conf.get(source_host, 'password'),
                               port=int(conf.get(source_host, 'port')),
                               db=conf.get(source_host, 'db'))
    print('Magento MySQL DB Connected')
except mysql.connector.Error as e:
   print ("MYSQL: Unable to connect!", e.msg)
   sys.exit(1)

# Postgresql connection
try:
   cnx_psql = get_connection(get_global_config(), 'pg_dwh')
   print('DWH PostgreSQL DB Connected')
except psycopg2.Error as e:
   print('PSQL: Unable to connect!\n{0}').format(e)
   sys.exit(1)

# Cursors initializations
cur_msql = cnx_msql.cursor()
cur_msql_1 = cnx_msql.cursor()
cur_psql = cnx_psql.cursor()
cur_psql_1 = cnx_psql.cursor()

now = time.strftime('%Y-%m-%d %H:%M:%S')

##################################################################################
update_sql_base="""select gr.email from unsubscribed_contacts gr
                   INNER JOIN subscriber sn on sn.email=gr.email"""

msql_update_1="""UPDATE subscriber SET status=3,timestamp=CAST(TO_CHAR(now(),'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP) WHERE email='%s'"""

msql_update_2="""UPDATE n_subscriber SET subscriber_status=3,change_status_at=CAST(TO_CHAR(now(),'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP)
             WHERE subscriber_email='%s';"""

cur_psql.execute(update_sql_base)

for row in cur_psql:
    email=row[0]
    cur_msql.execute(msql_update_1 %email)
    cnx_msql.commit()
    cur_msql.execute(msql_update_2 %email)
    cnx_msql.commit()

##################################################################################

insert_sql_base="""select gr.email,c.customer_id,'',3,'',CAST(TO_CHAR(now(),'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),'','',CAST(TO_CHAR(now(),'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP)
                   from unsubscribed_contacts gr
                   LEFT JOIN n_subscriber sn on sn.email=gr.email
                   LEFT JOIN customers_2 c on c.customer_email=gr.email
                   WHERE sn.email IS NULL"""

msql_insert="""INSERT INTO n_subscriber(
           email, customer_id, options, status, confirm_code, "timestamp", ip, store_id, confirmed_at) SELECT """

msql_insert_1="""INSERT INTO n_subscriber(
           email, customer_id, options, status, confirm_code, "timestamp", ip, store_id, confirmed_at) SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s"""

cur_psql_1.execute(insert_sql_base)

for row in cur_psql_1:
    print(msql_insert_1)
    cur_msql_1.execute(msql_insert_1, row)
    cnx_msql.commit()


## Closing cursors'
cur_msql.close()
cur_psql.close()
cur_psql_1.close()
cur_msql_1.close()

## Closing database connections
cnx_msql.close()
cnx_psql.close()

Python : 3.5 PostgreSQL: Version 11

A slightly unrelated note: have you considered using a Foreign Data Wrapper to transfer this data? I've used many times from MySQL to Postgres and cannot recommend it enough :-) — Jim Jones
– Jim Jones, Commented Jun 3, 2020 at 13:07
@JimJones Managed to fix that , When i use escape character to skip the reserved keyword "timestamp"in mySQL.Do you see recommend any other changes to this script? — Linu
– Linu, Commented Jun 3, 2020 at 15:42

Danila Ganchar · Accepted Answer · 2020-06-03 15:55:36Z

The main problem is wrong syntax(cur_msql_1.execute(msql_insert_1, row)). Just trying to explain using a few tables:

create table subscriber
(
    customer_id int null,
    email varchar(100) null,
    timestamp int null
);

INSERT INTO subscriber (customer_id, email, timestamp) VALUES (1, '[email protected]', 1591187277);
INSERT INTO subscriber (customer_id, email, timestamp) VALUES (2, '[email protected]', 1591187303);

create table n_subscriber
(
    customer_id int null,
    email varchar(100) null,
    timestamp int null
);

in your case it works something like this:

import MySQLdb

db = MySQLdb.connect(...)

cursor = db.cursor()
cursor.execute("SELECT customer_id, email, timestamp FROM subscriber")

for row in cursor:
    cursor.execute("""INSERT INTO n_subscriber(customer_id, email, "timestamp") SELECT %s, %s, %s""", row)

db.commit()

MySQLdb._exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'"timestamp") SELECT 1, \'[email protected]\', 1591187277\' at line 1')

Correct syntax:

cursor.execute("INSERT INTO n_subscriber(customer_id, email, timestamp) VALUES (%s, %s, %s)", row)

Also you can do it using executemany():

cursor = db.cursor()
cursor.execute("SELECT customer_id, email, timestamp FROM subscriber")

data = cursor.fetchall()
cursor.executemany("INSERT INTO n_subscriber(customer_id, email, timestamp) VALUES (%s, %s, %s)", data)
db.commit()

Hope this helps.

Collectives™ on Stack Overflow

Error while updating MySQL DB from PostgreSQL DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related