Store numpy array in SQL Server

Question

I try to store pickled numpy array in SQL Server as a VARBINARY(MAX) object using pyodbc. INSERT statement from SQL Server looks like this:

INSERT INTO [dbo].[Images]
 ([UserId]
 ,[FileName]
 ,[FeaturesVector])
 VALUES
 (<UserId, int,>
 ,<FileName, nchar(100),>
 ,<FeaturesVector, varbinary(max),>)

In my python code I build query as a fstring:

query = f"INSERT INTO Images(UserID, FileName, FeaturesVector) \
 VALUES ('{user_id}', '{file_name}', '{features_vector}')"

When I try to insert object to database:

features_vector = np.arange(1)
features_vector.astype('float32')
features_vector = pickle.dumps(features_vector)
query = f"INSERT INTO Images(UserID, FileName, FeaturesVector) \
 VALUES ('{user_id}', '{file_name}', '{features_vector}')"
cnxn.execute(query)
cnxn.commit()

I get an error:

('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '\'. (102) (SQLExecDirectW)")

• Thom A: Don't inject, parametrise. Also in seems your back slash ( \ ) is being interpreted as a literal, not an escape character.
• quarkpol: I remove backslash, still does not work. Can you explain to me how not to inject parametrize?
• Thom A: From a quick Google I got: How to put parameterized sql query into variable and then execute in Python?) which shows the method.
• Milan Cermak: You don't need to escape the \ in the query though. It seems you meant to use it to split your python string into two lines, not the SQL query itself.

Answer 1

Reliable Insert Method With pyodbc - Why?

@quarkpol, I feel your pain! I ran into this a while back, and I don't appreciate why I had to do it the way that I had to do it, but I had to do it this way to get it to work, and I noticed others doing this too.

First, when you do something like the following on reading data, the following ...

query_string = f'SELECT {col_name_1}, {col_name_2} FROM {some_table} WHERE {other_col} = {some_val}
cursor.execute(query_string)
# do some stuff with what the cursor got

the python f'stuff' type statements work great!

When INSERT'ing however, I have found that I must do the following for it to work ...

command_string = f'''INSERT INTO My_Table
 (Col_Name_1, Col_Name_2, Col_Name_3) VALUES (?, ?, ?);'''
cursor.execute(command_string, val_1, val_2, val_3)
conn.commit()

Again, I don't know why, but at least this works.