CODEWITHSUNDEEP
pandasms-accesspyodbc
Damodarji
Damodarji

Reputation: 93

Syntax error in INSERT INTO command while using pyodbc

This is the code where syntax error occurs

import pyodbc
import pandas as pd
from datetime import date

Today = date.today()
Today = Today.strftime('%d-%m-%y')

NSE_Deli = pd.read_csv("C:/Users/PC/Desktop/Daily Data/NSE_Deliverables.csv")
def append_NSE_Deliverables():
    for row in NSE_Deli.itertuples():
        cursor.execute("INSERT INTO tbl1A_Deliverable_NSE (Symbol,Series,Volume,DeliVolume,Deli%,`Timestamp`) VALUES (?,?,?,?,?,?)",
            row[1],
            row[2],
            row[3],
            row[4],
            row[5],
            Today
            )

The error returned is,

cursor.execute("INSERT INTO tbl1A_Deliverable_NSE (Symbol,Series,Volume,DeliVolume,Deli%,Timestamp) VALUES (?,?,?,?,?,?)", pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)')

The code given below with the same syntax didn't return any problem

NSE_Bhav = pd.read_csv("C:/Users/PC/Desktop/Daily Data/cm05MAY2021bhav.csv")
def append_NSE_Bhavcopy():
    for row in NSE_Bhav.itertuples():
        cursor.execute("INSERT INTO tbl1A_Bhavcopy_NSE (Symbol,Series,Open,High,Low,Close,Last,PrevClose,Volume,Turnover,`Timestamp`,TotalTrades,ISIN) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
            row[1], 
            row[2], 
            row[3],
            row[4],
            row[5],
            row[6],
            row[7],
            row[8],
            row[9],
            row[10],
            row[11],
            row[12],
            row[13]
            )

Upvotes: 1

Views: 331

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

The column name Deli% needs to be quoted. Access ODBC will accept backquotes, but square brackets are more commonly used in Microsoft's dialects of SQL

cursor.execute("INSERT INTO tbl1A_Deliverable_NSE ([Symbol],[Series],[Volume],[DeliVolume],[Deli%],[Timestamp]) VALUES …

Since you're using pandas you may want to consider using the sqlalchemy-access dialect for SQLAlchemy. (I am the maintainer of that dialect.)

Upvotes: 0

Related Questions