I work with a somewhat old software (2000's) that interfaces with MS Access. I am using Python to write the MDB files that I need to insert into the software. I need to update the values of 7 different tables in order to get the expected value. For all the 7 tables I am using the following code:
def sql_cols(df):
cols_aux = tuple(df.columns)
cols_aux = str(cols_aux).replace("'","")
cols_aux = cols_aux.replace(', DESC', ', [DESC]')
return cols_aux
for k in range(len(df)):
vals = tuple(list(df.iloc[k]))
cols = sql_cols(df)
action = 'INSERT INTO Strategy {columns} VALUES {values}'.format(columns = cols, values = str(vals).replace("'",''))
cursor.execute(action)
conn.commit()
In 6 tables this procedure works, however i keep getting ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)') in the last table. the action statement for that table is something like:
'INSERT INTO Strategy (STRATGY_ID, NUMBUDGETS, PDISC_RATE, INC_IMPS, OPT_METHOD, OBJ_FUNC, MININCVAL, EFFFZONE, BUDGETPD, REPORTING, NUM_GROUPS, MATRIX_DEF, NAME, [DESC], FLT_CONRTE, WRK_CONRTE, NET_CONRTE, START_YEAR, END_YEAR, CURRENCY, FLEET, NETWORK, NUM_SECSEL, NUM_VEHSEL, ANAL_MODE, NUMPRJOPTS, NUMSECOPTS, INC_NMT, DOECONANAL, DISC_RATE, BASE_OPTN, ACCCOSTS, FATALCOST, INJURYCOST, DAMAGECOST, ALLACCCOST, ENERGY, EMISSIONS, ACCLEFFECT, LOGFILE, INCSENSTVY, NUMSENSCEN, DOASSETVAL, EXCVEHDATA, EXCPERDATA)
VALUES (0, 0, , 0, 1, 0, 0, 95, 0, -1, 0, 0, Road_Networks_PA, , 1, 1, 1, 2019, 2038, Reais, Vehicles 2017, Road_Networks_PA, 157, 12, 0, , 157, -1, -1, 6.49, , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, , 0, -1, -1)'
I've already tried to ut all columns in square brackets, [], but it didn't work also. Can anyone help-me with this problem?
Edit: Following Gordon's advice, I altered the code, but the same error appears:
for k in range(len(df)):
vals = tuple(list(df.iloc[k]))
cols = sql_cols(df)
action = 'INSERT INTO Strategy {columns} VALUES {values}'.format(columns = cols, values = str(vals))
cursor.execute(action)
conn.commit()
and the action statement is:
"INSERT INTO Strategy (STRATGY_ID, NUMBUDGETS, PDISC_RATE, INC_IMPS, OPT_METHOD, OBJ_FUNC, MININCVAL, EFFFZONE, BUDGETPD, REPORTING, NUM_GROUPS, MATRIX_DEF, NAME, [DESC], FLT_CONRTE, WRK_CONRTE, NET_CONRTE, START_YEAR, END_YEAR, CURRENCY, FLEET, NETWORK, NUM_SECSEL, NUM_VEHSEL, ANAL_MODE, NUMPRJOPTS, NUMSECOPTS, INC_NMT, DOECONANAL, DISC_RATE, BASE_OPTN, ACCCOSTS, FATALCOST, INJURYCOST, DAMAGECOST, ALLACCCOST, ENERGY, EMISSIONS, ACCLEFFECT, LOGFILE, INCSENSTVY, NUMSENSCEN, DOASSETVAL, EXCVEHDATA, EXCPERDATA)
VALUES (0, 0, ' ', 0, 1, 0, 0, 95, 0, -1, 0, 0, 'Road_Networks_PA', ' ', 1, 1, 1, 2019, 2038, 'Reais', 'Vehicles 2017', 'Road_Networks_PA', 157, 12, 0, ' ', 157, -1, -1, 6.49, ' ', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, -1, -1)"
Edit 2: As asked by Kshitij Saxena, here are my data types
Field Name Data Type
STRATGY_ID Number
NUMBUDGETS Number
PDISC_RATE Number
INC_IMPS Yes/No
OPT_METHOD Number
OBJ_FUNC Number
MININCVAL Number
EFFFZONE Number
BUDGETPD Yes/No
REPORTING Yes/No
NUM_GROUPS Number
MATRIX_DEF Yes/No
NAME Text
DESC Text
FLT_CONRTE Number
WRK_CONRTE Number
NET_CONRTE Number
START_YEAR Number
END_YEAR Number
CURRENCY Text
FLEET Text
NETWORK Text
NUM_SECSEL Number
NUM_VEHSEL Number
ANAL_MODE Number
NUMPRJOPTS Number
NUMSECOPTS Number
INC_NMT Yes/No
DOECONANAL Yes/No
DISC_RATE Number
BASE_OPTN Text
ACCCOSTS Yes/No
FATALCOST Number
INJURYCOST Number
DAMAGECOST Number
ALLACCCOST Number
ENERGY Yes/No
EMISSIONS Yes/No
ACCLEFFECT Yes/No
LOGFILE Yes/No
INCSENSTVY Yes/No
NUMSENSCEN Number
DOASSETVAL Yes/No
EXCVEHDATA Yes/No
EXCPERDATA Yes/No
CURRENCY is a reserved word in Access SQL, so if you have a column with that name you must enclose it in square brackets. That is, this will fail with "Syntax error in INSERT INTO statement." ...
crsr.execute("INSERT INTO Strategy (CURRENCY) VALUES ('USD')")
... but this will work:
crsr.execute("INSERT INTO Strategy ([CURRENCY]) VALUES ('USD')")