Search code examples
sqlsql-serverflaskpyodbcinsert-update

How can I update varbinary(max) columns in SQL Server with flask?

I have a City table:

name | id | pic
-----+----+-----
A    | 1  | null
B    | 2  | null
C    | 3  | null
D    | 4  | null

I want to update the pic column of this table where id is 2. How can I do it?

My code is:

file = request.files['file']
saveFileToDB = "UPDATE [Db2].[dbo].[City] SET (pic) VALUES (?) WHERE id = 2"
CU.execute(saveFileToDB, (file.read()))
CU.commit()

Error message:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '('. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)"

or:

saveFileToDB = "UPDATE [Db2].[dbo].[City] SET pic = Convert(Varbinary(max),'%s') WHERE id = 2"%file.read()

but it's not working.

Solution

  • Your update syntax is off. Use this version:

    file = request.files['file']

saveFileToDB = "UPDATE City SET pic = ? WHERE id = 2"
CU.execute(saveFileToDB, (file.read(),))
CU.commit()

    As a note, I used (file.read(),), with a trailing comma here. This is to ensure that Python reads it as a tuple and not a scalar variable.