0

I am writing a code to copy rows of matching columns from CSV to PostgreSQL table. I am using python and qgis for the same.Code is as follows

    connection=psycopg2.connect(host=host, port=port, dbname=dbname, user=name_user, password=password)        
    cursor = connection.cursor ()        
    cursor.execute("""SELECT Count(*) FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'table'""")
    csv1 = pd.read_csv(self.dlg.lineEdit_5.text())
    csvfile = open(self.dlg.lineEdit_5.text(),'r')
    columnnames = csv1.columns.values
  
    table=self.dlg.comboBox.currentText()
    table_name = table.strip(' ' ' ')
    self.dlg.lineEdit_6.setText(str(table))
    with open(self.dlg.lineEdit_5.text(), 'r') as f:
        reader = csv.reader(f)
        next(reader) # This skips the 1st row which is the header.
          
        for x in columnnames:
            column = x.strip(' ' ' ')
            #self.dlg.lineEdit_6.setText(str(column))
            sql_insert = """INSERT INTO table_name(x) VALUES(%s)"""
            for record in reader:
                cursor.execute(sql_insert,[record])
                connection.commit()

I am getting error as follows

        psycopg2.errors.UndefinedTable: relation "table_name" does not exist
         LINE 1: INSERT INTO table_name(x) VALUES(ARRAY['501','mah','A'])

How to resolve this error?. table_name exists in the database.

vigna purohit
  • 137
  • 9
  • 1) In the first part you have `TABLE_NAME = 'table' and in the second `table_name`. I know they are made up names, still they should be consistent. 2) What is the actual table name in the database? My suspicion is that: a) It is mixed or upper case and you will need to double quote it in the query e.g "Table_Name". b) The [search_path](https://www.postgresql.org/docs/14/ddl-schemas.html#DDL-SCHEMAS-PATH) is such that the table name needs to be schema qualified to be found. – Adrian Klaver Oct 27 '21 at 15:33
  • @AdrianKlaver table name is lower case only it is being taken from comboBox text by table=self.dlg.comboBox.currentText(). I tried double quotes too but it gives the same error. – vigna purohit Oct 28 '21 at 04:20

1 Answers1

0

It was a silly mistake. I was taking table name from a variable in a python code. so, query need to be written as follows.

    table=self.dlg.comboBox.currentText()
    table_name = table.strip(' ' ' ') 
    sql_insert = """INSERT INTO %(table_name)s (x) VALUES(%s);""" 
      cursor.execute(sql_insert,[value])         
      connection.commit()
vigna purohit
  • 137
  • 9