Rows are not getting inserted to mysql using python

Question

I want to insert some records to mySQL database from PostgreSQL(both are remote servers),So i'm using the below script but the case is that the data is not getting inserted into MySQL database.

There is a final count statement which always results zero.May i know what could be the reason?

Also any improvement need to do on this code.

Suggestions please.

import psycopg2
import os
import time
#import MySQLdb
from mysql.connector import (connection)

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

def db_connect():

# 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'))
    cnx_msql = connection.MySQLConnection(host='server.com', user='db13009',passwd='fgDT***********',port=3306,db='db13009')
    print('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')
   cnx_psql =psycopg2.connect(host='xxx.xxx.xx.xx',
                            dbname='postgres',
                            port='5432',
                            user='postgres',
                            password='*********')
   print('PSQL 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_psql = cnx_psql.cursor()
 SQL_test="""SELECT count(*) from action_status;"""
 cur_msql.execute(SQL_test)
 records = cur_msql.fetchall()

 for row in records:
        print("count = ", row[0], )
 msql_command=""
 try:
   SQL_load="""SELECT created_at,payload from staging.sync;"""
   msql_ins="""INSERT INTO action_status(payload,created_at)VALUES (%s, %s) """
   cur_psql.execute(SQL_load)

   for row in cur_psql:
        try:
            print(row[0])
            print(row[1])
            cur_msql.execute(msql_ins, row[0],row[1])
        except psycopg2.Error as e:
            print('ffffffffffffff')
            print ("Cannot execute the query!!", e.pgerror)
            sys.exit(1)
   cnx_msql.commit()

   cur_msql.execute(SQL_test)
   records = cur_msql.fetchall()

   for row in records:
        print("count = ", row[0], )
 except (Exception, psycopg2.Error) as error:
     print ("Error while fetching data from PostgreSQL", error)
 finally:
     ## Closing cursors
     cur_msql.close()
     cur_psql.close()
     ## Committing
     cnx_psql.commit()
     ## Closing database connections
     cnx_msql.close()
     cnx_psql.close()


if __name__ == '__main__':
    db_connect()

Answer 1

msql_ins is missing semicolon (not sure whether required or not). More importantly, you are missing a tuple; instead of:

cur_msql.execute(msql_ins, row[0],row[1])

try this instead:

cur_msql.execute(msql_ins, (row[0], row[1]))

Hope that helps :)