Insert Binary File into Mssql Db (Varbinary) with Python Pymssql

Insert binary file into MSSQL db (varbinary) with python pymssql

Maybe you have to convert the varchar explicitly. That's what your error message implies.

See: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8f9d772a-4fa2-45b4-9fed-f03c73bd757a/implicit-conversion-from-data-type-varchar-to-varbinary-is-not-allowed-use-the-convert-function-to?forum=transactsql

This seems to solve the same problem.

Insert byte into sql server varbinary(max) column using pymssql

Try to modify your string_sql_insert to:

string_sql_insert = "INSERT INTO CPBB_DevClusterObjs(str_cluster_id, \
CONVERT(varbinary(max), obj_cluster_empty)) \
VALUES('BatchKm|20|k-means++|1'," + "'" + hex_01 + "'"')'

You are inserting this:

INSERT INTO CPBB_DevClusterObjs(str_cluster_id, obj_cluster_empty)     VALUES('BatchKm|20|k-means++|1',0x54686973206973206120627974652074657374)

Instead of this:

INSERT INTO CPBB_DevClusterObjs(str_cluster_id, obj_cluster_empty)     VALUES('BatchKm|20|k-means++|1','0x54686973206973206120627974652074657374')

If you look at VALUES you will see why it is throwing an error.

According to https://github.com/pymssql/pymssql/pull/179/files

def insert_and_select(self, cname, value, vartype, params_as_dict=False):

vartype is 's'

def test_binary_string(self):
    bindata = '{z\n\x03\x07\x194;\x034lE4ISo'.encode('ascii')
    testval = '0x'.encode('ascii') + binascii.hexlify(bindata)
    colval = self.insert_and_select('data_binary', testval, 's')
    self.typeeq(bindata, colval)
    eq_(bindata, colval)

Insert value into VARBINARY column using sqlalchemy

Assuming that, like in my case, address is a large integer, one can write

a = 72037797995605455
MyTable.address = a.to_bytes(16, 'big')

Output file stored in VARBINARY column with Flask

OK, I figured that out.

Code I have was correct, and it outputs file in a right format.

Problem was with freedts driver, it has limit of 64512 bytes in freedts.conf

text size = 64512

Changing it to something more sane fixes the error.

Reading binary data (image data type) from SQL database and inflate it, Matlab vs. Python

It sounds like you want to unpack things into unsigned 16-bit integers?

So, you have something like:

bytearray(b'\x01\x00\x02\x00\x03\x00\x04\x00')

And you want:

[1, 2, 3, 4]

If so, you have several options.

If you're going to be using numpy anyway, consider using numpy for this.

import numpy as np
dat = bytearray(b'\x01\x00\x02\x00\x03\x00\x04\x00')
data = np.frombuffer(buffer(dat), dtype=np.uint16)

Alternately, you could do something like this using python's builtin array:

import array
dat = bytearray(b'\x01\x00\x02\x00\x03\x00\x04\x00')
data = array.array('H')
data.fromstring(buffer(dat))

You could also use the struct module, but it's less than ideal for repetitive data like this.

Decoding array of numbers from database in python

So, it turns out the numbers are saved in int16 format. Also, np.fromstring is deprecated, and replaced by np.frombuffer. The unit was in mm instead of cm, so the resultant numbers are between -400 to 400.

import numpy as np
import binascii
x=b'\x91\xfe\xc3\xfe\xeb\xfe\xef\xfe\x04\xff\x1d\xff+\xff+\xff1\xff:\xffD\xffO\xffS\xffc\xffl\xff|\xff\x8f\xff\xa8\xff\xb3\xff\xbd\xff\xc7\xff\xcc\xff\xd0\xff\xd6\xff\xe5\xff\xfe\xff\xfe\xff\xfe\xff\xfe\xff\xfe\xff\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x06\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' 
y = np.fromstring(x,dtype=np.float16,count=60)
print(y) 

[-367 -317 -277 -273 -252 -227 -213 -213 -207 -198 -188 -177 -173 -157
 -148 -132 -113  -88  -77  -67  -57  -52  -48  -42  -27   -2   -2   -2
   -2   -2    6    6    6    6    6    6    6    6    6    6    6    6
    6    6    6    6    6    6    6    6    6    6    6    6    6    6
    6    6    6    6]

how to INSERT but IGNORE same date tag with pymssql python

You have a few options, you can make date your primary key, then on any duplicate entries just update the price using ON DUPLICATE KEY UPDATE:

Table_create = '''CREATE TABLE table1 (t_date date PRIMARY KEY, price FLOAT )'''
cur.execute(Table_create)

today = str(dt.datetime.now().date())
yesterday = str(dt.datetime.now().date() - dt.timedelta(days=1))
nextday = str(dt.datetime.now().date() + dt.timedelta(days=1))
cur.executemany("INSERT  INTO table1(t_date, price) VALUES(%s, %s) on DUPLICATE KEY UPDATE price=price",([(today, 100), (yesterday, 200)]))

That would only update the price if you happened to get a different/new price for an existing row or do an insert for a new date

You could also just IGNORE any duplicates:

"INSERT INSERT IGNORE INTO table1(t_date, price) VALUES(%s, %s)"

But IGNORE will ignore more than an IntegrityError based on the duplicate entry so I would personally prefer the former.

Another option if you wanted to have an auto_increment id and to use both t_date and price or just the t_date would be to create a UNIQUE CONSTRAINT

  Table_create = '''CREATE TABLE table1 (
                            ID int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                            t_date date,
                            price FLOAT,
                            CONSTRAINT no_dupes UNIQUE  (t_date, price))''' # CONSTRAINT no_dupes UNIQUE (t_date) for just the t_date
cur.execute(Table_create)

The same logic would apply when inserting.

---

Related Topics