Syntax error in INSERT INTO command while using pyodbc

Question

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]
            )

Gord Thompson · Accepted Answer · 2021-05-06 11:57:08Z

0

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.)

edited May 6, 2021 at 11:57

answered May 6, 2021 at 11:47

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax error in INSERT INTO command while using pyodbc

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related