0

I'm setting up small RestFul API based on Flask to have an access to a DB. I've tackled GET and POST methods which are working great, but having issues with PUT.

I've already swapped pyodbc to pypyodbc since it actually have 'update' methods. I'm running it on 10.0.17134.1 (tried 14.00.7010.1000 which is older) ODBC 32-bit driver with 32-bit python 3.7.2 on Windows 10 machine.

def put(self, code_id):
        query = request.get_json()[0]
        sql = 'UPDATE `employees` SET `employee`=?, `access_level`=? WHERE `CODE`="' + code_id+ '";'
        params = [query['Employee'], query['Access level']]

        cursor.execute(sql, params)
        return Response(status=200)

I'm getting next error: pypyodbc.DatabaseError: ('07002', '[07002] [ODBC Microsoft Access Driver] Too few parameters. Expected 3

I've tried to send only one parameter to change single field, but still getting the same error with a little twist - it'll expect you to send 2 parameters instead. If you add one extra parameter to the 'params' list, then you'll get pypyodbc.ProgrammingError: ('HY000', 'The SQL contains 2 parameter markers, but 3 parameters were supplied') error.

Also, column and table names in DB are in Russian (which I translated) and cannot be changed. Therefore quotation marks are neccesary when making a query.

PS. INSERT and SELECT queries are working just great.

Kirill Gorshkoff
  • 1
  • 2
  • In Access SQL, as I know it, table an field names shouldn't be in quotes, could be square brackets, if needed: `sql = 'UPDATE [employees] SET [employee]=?, [access_level]=? WHERE [CODE]="' + code_id+ '";'. BTW, is `CODE` a String? ` – marlan May 08 '19 at 14:21
  • @marlan just tried to swap quotes to brackets to no avail, still getting the same error. CODE is a string, yes. And I'm getting it from json as a string, but it doesn't work without extra quotes I placed there, giving me 'Data type mismatch' error. – Kirill Gorshkoff May 08 '19 at 14:29
  • First error is from Access, second from ODBC. second has not sent the sql to access. stay with the first option's code, and modify the SQL in it. – marlan May 08 '19 at 14:42
  • as for the error in the SQL, this error usually points to ACE can't match field names to actual fields (and therefore treats them as parameter names with no value). See if [this](http://access.mvps.org/access/queries/qry0013.htm) helps you – marlan May 08 '19 at 14:49
  • Sadly it didn't help. All the fields names should match, since I did an INSERT query with basically the same parameters. I just didn't try to state a WHERE clause. – Kirill Gorshkoff May 08 '19 at 15:21

1 Answers1

0

OK, I finally got it.

So, apparently WHERE clause requires single quotes (') instead of double ones (").

When argument is enclosed in those, it works like a charm.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kirill Gorshkoff
  • 1
  • 2