2

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)")

Dale K
  • 25,246
  • 15
  • 42
  • 71
quarkpol
  • 485
  • 1
  • 5
  • 10
  • 2
    Don't inject, parametrise. Also in seems your back slash ( `\ `) is being interpreted as a literal, not an escape character. – Thom A Jun 18 '20 at 08:46
  • I remove backslash, still does not work. Can you explain to me how not to inject parametrize? – quarkpol Jun 18 '20 at 08:51
  • 1
    From a quick [Google](https://www.google.com/search?q=HOw+to+parmetrise+a+query+in+python&ie=utf-8&oe=utf-8) I got: [How to put parameterized sql query into variable and then execute in Python?)](https://stackoverflow.com/q/1633332/2029983) which shows the method. – Thom A Jun 18 '20 at 08:53
  • 1
    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. – Milan Cermak Jun 18 '20 at 08:56

1 Answers1

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.

Thom Ives
  • 3,642
  • 3
  • 30
  • 29