CODEWITHSUNDEEP
pythondatabasems-accesspyodbc
BaseballIphone69
BaseballIphone69

Reputation: 1

Issue with writing new rows in a MS Access table using Pyodbc

Recently I have been trying to write a code that allows for info to be read and written from an MS Access database. For this I am using python 3.7 in Visual Studio, and the PIP Pyodbc. I have successfully connected to the database and my read() function is working properly. The issue is occurring with my write() function, as it is throwing an error and I am not sure why. The write() function is set to take variables from a defined class and write these to the database. When I run the code I am receiving this error.

('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)')

Attached below is a copy of my code and an image of the database. MS Access Image

import pyodbc #Opens MS Access database
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\peyto\Desktop\Test Database.accdb;')
cursor = conn.cursor()
cursor.execute('select * from ToDo')

class GlobalVars(): #Variables to be taken from website
    Group = ""
    TaskName = ""
    TaskDesc = ""
    DueDate = "00000001"
    DueTime = "000001"
    PM = False #Will allow PM/AM to append to date in Write()
    Append = False #If false will not attempt to update record
    RowToAppend = ""
    Delete = False
    Write = False

def Read(): #Prints all values in the table
    for row in cursor.fetchall():
        print (row)

def Update(): #Update database row
    #Code not yet tested not relevant to current issue.
    cursor.execute('UPDATE ToDo'
               'SET GlobalVars.Group, GlobalVars.TaskName, GlobalVars.TaskDesc, GlobalVars.DueDate, GlobalVars.DueTime'
               'WHERE condition')
    cursor.commit()

def Delete(): #Will delete any given row of database
    print("Code not yet ready")

def Write():
    if GlobalVars.PM == False:
        GlobalVars.DueTime = GlobalVars.DueTime + " AM" #Concatenate AM on end of string
    else:
        GlobalVars.DueTime = GlobalVars.DueTime + " PM" #Concatenate PM on end of string
    sql = "INSERT INTO ToDo (Group, TaskName, TaskDesc, DueDate, DueTime) VALUES (?, ?, ?, ?, ?)"
    val = (GlobalVars.Group, GlobalVars.TaskName, GlobalVars.TaskDesc, GlobalVars.DueDate, GlobalVars.DueTime)
    cursor.execute(sql, val)

    cursor.commit()


if GlobalVars.Append == True: #Checks which function to run based on website input
    Update()
elif GlobalVars.Delete == True:
    Delete()
elif GlobalVars.Write == True:
    Write()
Write()
Read()

Upvotes: 0

Views: 158

Answers (1)

Gustav
Gustav

Reputation: 55981

Fields DueDate and DueTime seems to be of data type text, as they are left-aligned.

Change those fields to DateTime and make sure, when inserting, that valid date and time values are passed - which means, that GlobalVars.DueDate and GlobalVars.DueTime must hold true date/time values.

Upvotes: 0

Related Questions