Python Forum
Error while fetching data from PostgreSQL
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Error while fetching data from PostgreSQL
#1
i'm facing the below error and unable to move forward,if anyone have any leads or solution on how to handle this,Kindly let me know.

I'm trying to port the data from one database to another using the below logic and is stumbled with this error,Please help me with this.

I have been trying to resolve it for many days and till now haven't got any idea on how to do this,Can you please help me with this and show me where i'm getting these?

Quote:Error while fetching data from PostgreSQL tuple indices must be integers or slices, not str

import psycopg2
import os
import time
import sys
from pprint import pprint
from datetime import datetime
from psycopg2 import sql
import traceback


def psql_fetchandInsert(cnx_msql,cnx_psql,msql, psql, msql_command, psql_command):

    msql.execute(msql_command)
    for row in msql:
        try:
            print(row)
            print(psql_command)
            psql.execute(psql_command, row)
            exc_info = sys.exc_info()
            cnx_psql.commit()
        except psycopg2.Error as e:
            traceback.print_exception(exc_info)

            traceback.print_exc()
            print ("Cannot execute the query!!", e.pgerror)
            sys.exit("Some problem occured with the query!!!")



def Fetch():

 try:
   cnx_msql = psycopg2.connect(user="postgres",
                                  password="postgres",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres")
 except psycopg2.Error as e:
   print ("DB: Unable to connect!", e.msg)
   sys.exit(1)

# Postgresql connection
 try:

   cnx_psql = psycopg2.connect(user="postgres",
                                  password="postgres",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="Sendy")
  #cnx_psql = psycopg2.connect(conn_string_psql)
 except psycopg2.Error as e:
   print('PSQL: Unable to connect!\n{0}').format(e)
   sys.exit(1)



 try:
# Cursors initializations
   cur_msql = cnx_msql.cursor()
   cur_psql = cnx_psql.cursor()
   print(" cursor   initialised")
   print("creating table using cursor")

   SQL_schema="""CREATE SCHEMA IF NOT EXISTS st AUTHORIZATION postgres;"""

   SQL_products="""CREATE TABLE IF NOT EXISTS st.products
      (   Id              INTEGER
		, Name            CHARACTER VARYING
		, Product         CHARACTER VARYING
		, email           CHARACTER VARYING
		, sku             CHARACTER VARYING
		, html_code       CHARACTER VARYING
		, quote_id        INTEGER
		, itemcount 	  NUMERIC
		
	    );"""
   cur_psql.execute(SQL_schema)


   cur_psql.execute(SQL_products)
   cnx_psql.commit();


    
   commands = [("""SELECT "Id","Name","Product","email","htmlcode","itemcount" from dl."Products";""",
             """INSERT INTO st."products" (Id,Name,Product,email,htmlcode,itemcount) \
              VALUES (%(Id)s, %(Name)s, %(Product)s, %(email)s, %(html_code)s, %(itemcount)s);""")
            
            ]
   print ("check")

   for msql_command, psql_command in commands:
     psql_fetchandInsert(cnx_msql,cnx_psql,cur_msql, cur_psql, msql_command, psql_command)

 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__':
    Fetch()
Reply
#2
I don't use postgresql, but I think you need integers in the parentheses on line 87, not column names.
Craig "Ichabod" O'Brien - xenomind.com
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures
Reply
#3
(May-13-2019, 12:26 PM)ichabod801 Wrote: I don't use postgresql, but I think you need integers in the parentheses on line 87, not column names.

Thanks a lot for your Reply,Let me try and will post the results after that :)
Reply
#4
I hope this doesn't sound negative in any way, just trying to give you a resource:

I recommend learning Postgres at pgexercises.com
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to Connect to PostgreSQL Through Jump Server and SSH Tunnel using Python? nishans 1 848 Jan-02-2024, 10:37 AM
Last Post: khanzain
  fetching exit status hangs in paramiko saisankalpj 3 1,134 Dec-04-2022, 12:21 AM
Last Post: nilamo
  Fetching the port number using asyncio gary 0 910 Nov-01-2022, 02:53 AM
Last Post: gary
  pandas.errors.ParserError: Error tokenizing data. C error: Expected 9 fields in line Anldra12 9 15,090 Jun-15-2021, 08:16 AM
Last Post: Anldra12
  Fetching data from multiple tables in a single request. swaroop 0 1,856 Jan-09-2021, 04:23 PM
Last Post: swaroop
  maintain a postgresql database using osm2pgsql apollo 1 2,255 Aug-03-2020, 10:33 PM
Last Post: Larz60+
  PostgreSQL psycopg2.errors.DuplicateColumn: column specified more than once rajnish_nationfirst 2 3,695 Jun-21-2020, 08:17 AM
Last Post: ibreeden
  StopIteration exception when mock PostgreSQL connection in several tests igor87z 1 2,879 Jun-10-2020, 06:16 PM
Last Post: ibreeden
  hi guys, I got data reader error while pulling the data from yahoo gokulrajkmv 1 1,957 May-15-2020, 11:08 AM
Last Post: snippsat
  Python animate live plotting fetching data from Mysql Table dhirajm 6 3,575 Apr-24-2020, 05:07 PM
Last Post: dhirajm

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020