0

I am performing an insert query from Python (3.6) to SQL Server. I am trying to insert a bytes datatype into a varbinary(max) column.

Now, the query executes successfully but value in the SQL Server column is not correct. Not sure what I am missing.

Is the data getting converted to some other format? How can this be rectified? Any pointers in the right direction will be appreciated.

Edit: In Python it's alphanumeric but when inserted into the sql server it is only numbers.

cn = pyodbc.connect()
cr = cn.cursor()
script=""" insert into table (Col1, col2) values (?,?)""" # Col1 is varbinary(max) column
VBM = VBM.encode('utf-8')#Bytes information corresponding to the varbinary(max) column
values = (VBM, FN) 
cur.execute(Script, values)
cn.commit()

VBM in Python:

b'0x443611119900554616035D15220'

VBM when inserted into SQL Server:

0x307834353143313131313936303
Akshay
  • 1
  • 2
  • If this is **binary** data you're trying to insert - why are you applying `encode('utf-8')` to it?? That 's a **textual** operation ..... what's the idea behind this? What are you trying to do? Just insert the binary bytes **as is** ...... – marc_s Feb 19 '21 at 16:04
  • The data gets imported from an Excel file as a string, can't insert string as it is, Python throws an conversion error (can't convert string to varbinary(max). Hence, converted to byte format first. Unless anyone else knows a better way to insert a string into varbinary(max) column. – Akshay Feb 19 '21 at 16:36
  • A [bytes literal string](https://docs.python.org/3/reference/lexical_analysis.html#string-and-bytes-literals) is not the same thing as a [bytearray](https://docs.python.org/3/library/stdtypes.html#bytearray), or its immutable version `bytes`. You should be passing a `bytearray` or `bytes` into `binary` or `varbinary` columns. – AlwaysLearning Feb 20 '21 at 10:13
  • I tried changing to `bytes` or `bytearray` format as suggested but that doesn't solve the problem. The original string was imported from sql server with conversion to `varchar(max)` from `varbinary(max)` (this could be an image and not string) and saved in the Excel. 1 Is there a way to know what format (encoding) original `varbinary(max)` is, so that, it can be converted to the appropriate format before saving in Excel (or any other file based on the format)? – Akshay Feb 22 '21 at 17:01
  • 2. when imported from Excel (or other format) as a string it can be converted back to the correct format when inserting to `varbinary(max)` column in sql server? – Akshay Feb 22 '21 at 17:05

0 Answers0