Python Forum

Hi there all

I am using the psycopg2 module for postgresql and I keep receiving an error in my SQL statement saying there is a syntax error at or near "INSERT INTO. I've stared at it forever and I just can't seem to find it, maybe someone can help me? Thanks!

The connection is working fine between the database, and dates is a list of dates also autocommit is set to True

def addToDatabase(self, dates):
.
.
.
   sql = """INSERT INTO "%s" ("Date") VALUES(%s);"""

       for date in range(0, len(dates)):
           data = (ticker, dates[date])
           cursor.execute(sql, data)
.
.
.

Please:

• Show the code in context
  
• Show actual (verbatim) error traceback
  

Thank you

The error traceback is as follows:
Traceback (most recent call last):
  File "stockMain.py", line 21, in <module>
    main()
  File "stockMain.py", line 8, in main
    databases.addToDatabases(databases.dataFeed.genDates['daily'])
  File "/home/dondusko/Documents/AtomProjects/StockProject/stockDatabase.py", line 110, in addToDatabases
    cursor.execute(sql, data)
psycopg2.ProgrammingError: syntax error at or near "INSERT INTO"
LINE 1: INSERT INTO "'AAPL'" ("Date") VALUES('2008-01-01');

This is the function in question, not exactly sure what you mean code in context so this might be helpful?:

def addToDatabases(self, dates):
       ticker = self.getTickerName()

       for databaseName in range(0, len(self.databaseNames)):
           tempConnection = psycopg2.connect(dbname="'"+self.databaseNames[databaseName]+"'", user=self.databaseCredentials['username'], host=self.databaseCredentials['host'], password=self.databaseCredentials['password'])
           cursor = tempConnection.cursor()
           tempConnection.autocommit = True

           sql = """CREATE TABLE IF NOT EXISTS "%s" (""" + self.getTableSQL() + ");"
           data = (ticker, )
           cursor.execute(sql, data)

           sql = """INSERT INTO "%s" ("Date") VALUES(%s);"""

           for date in range(0, len(dates)):
               data = (ticker, dates[date])
               cursor.execute(sql, data)

           cursor.close()
           tempConnection.close()

add a print statement after line 9

print(sql: {}'.format(sql))

(Jul-18-2017, 03:58 AM)olgethorpe Wrote: [ -> ]INSERT INTO "'AAPL'"

I don't know postgres, but for most databases, it's an sql error to quote a table name.  If you can't just use the table name (ie: insert into AAPL), you could use brackets (ie: insert into [AAPL]), or sometimes backticks.