CODEWITHSUNDEEP
pythonsql-serverpython-3.5pymssql
Brent
Brent

Reputation: 61

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

I'm attempting to insert a byte or byte array into Sql Server table using Python 3.5 and the pymssql package. I keep getting a pymssql.ProgrammingError after attempting to insert.

I've used the following link and question as a reference but I'm still having trouble:

Explicit Convert of Varbinary(max) column

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

I've verified I have permission and can insert other data types into the sql server database. Below is the table structure.

Table Structure

Attempt 01:

sql_cursor = m_sql_conn.cursor()

byte_test_01 = b'This is a byte test'
hex_01 = '0x'.encode('ascii') + binascii.hexlify(byte_test_01)

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

sql_cursor.execute(string_sql_insert)

Result 01

pymssql.ProgrammingError

hex_01 = b'0x54686973206973206120627974652074657374'

Attempt 02

sql_cursor = m_sql_conn.cursor()

byte_test_01 = b'This is a byte test'
hex_01 = '0x'.encode('ascii') + binascii.hexlify(byte_test_01)

list_insert_many = [('BatchKm|20|k-means++|1', hex_01)]

string_sql_insert = "INSERT INTO \
    CPBB_DevClusterObjs(str_cluster_id,Covert(varbinary(max), obj_cluster_empty)) \ 
    VALUES (%s,%b)"

sql_cursor.executemany(str_sql_statement, list_insert_many)

Result 02

pymssql.ProgrammingError

hex_01 = b'0x54686973206973206120627974652074657374'

Attempt 03

I take out the CONVERT() function and...

sql_cursor = m_sql_conn.cursor()

byte_test_01 = b'This is a byte test'
hex_01 = '0x'.encode('ascii') + binascii.hexlify(byte_test_01)

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

sql_cursor.execute(string_sql_insert)

Result 03

pymssql.ProgrammingError

hex_01 = b'0x54686973206973206120627974652074657374'

I'm missing something but I don't know what. I definitely need some help here.

Update 01

Below is my complete code in a test method:

def Test():
    # lists
    list_sql_insert_data_type = ['%s', '%b']
    list_return = list()

    # variables
    string_sql_table = r'CPBB_DevClusterObjs'
    str_sql_error = ''
    user = r'user_me'
    host = r'server_me'
    pswd = r'pswd_me'
    db_name = r'db_me'
    bool_insert_into_table = False

    # sql connection
    list_sql_conn = SqlMethods.SqlGenConnection(user, host, pswd, db_name)
    sql_cursor = list_sql_conn[1].cursor()

    # byte test variables
    byte_test_01 = b'This is a byte test'
    hex_01 = '0x'.encode('ascii') + binascii.hexlify(byte_test_01)

    # create insert string
    string_sql_insert = 'INSERT INTO ' + string_sql_table
    string_sql_insert += '(string_cluster_id, CONVERT(varbinary(max), obj_cluster_empty)) '
    string_sql_insert += "VALUES ('BatchKm|20|k-means++|1'," + "'" + str(hex_01) + "'" + ')'

    # explicit string conversation of hex_01
    string_hex_01 = "'" + str(hex_01) + "'"

    # take out the CONVERT() statement
    #string_sql_insert = 'INSERT INTO ' + string_sql_table
    #string_sql_insert += '(string_cluster_id, obj_cluster_empty) '
    #string_sql_insert += "VALUES ('BatchKm|20|k-means++|1'," + "'" + str(hex_01) + "'" + ')'

    try:
        sql_cursor.execute(string_sql_insert)
    except pymssql.OperationalError:
        str_sql_error = 'Operational error was raised'
    except pymssql.ProgrammingError:
        str_sql_error = 'A program error was raised.'
    except pymssql.Error:
        str_sql_error = 'General error raised.'
    except pymssql.DatabaseError:
        str_sql_error = 'Database error raised.'
    except pymssql.DataError:
        str_sql_error = 'Data error raised.'
    except pymssql.IntegrityError:
        str_sql_error = 'Integrity error raised.'
    except pymssql.InterfaceError:
        str_sql_error = 'Interface error raised.'
    except pymssql.InternalError:
        str_sql_error = 'Internal error raised.'
    except pymssql.NotSupportedError:
        str_sql_error = 'Not supported error raised.'
    except pymssql.StandardError:
        str_sql_error = 'Standard error raised.'
    else:
        bool_insert_into_table = True
        list_sql_conn[1].commit()
    finally:
        list_return.append(bool_insert_into_table)
        list_return.append(str_sql_error)

    # return list
    return list_return

update 01 result

I tried what you recommended initially and I received and error, "unable to implicitly convert byte to string" as a result I explicitly converted it to a string.

instead of a pymssql.ProgrammingError in getting a pymssql.OperationalError.

if I take out the CONVERT() function I get a pymssql.ProgrammingError

hex_01 = 0x54686973206973206120627974652074657374

string_hex_01 = "'" + str(hex_01) + "'" = 'b'0x54686973206973206120627974652074657374''

string_sql_insert = INSERT INTO CPBB_DevClusterObjs(string_cluster_id, 
                    CONVERT(varbinary(max), obj_cluster_empty)) VALUES   
                    ('BatchKm|20|k-means++','b'0x54686973206973206120627974652074657374'')

Should this be string_hex_01 be:

string_hex_01 = str(hex_01)[1:] = '0x54686973206973206120627974652074657374'

this also gives a pymssql.OperationalError

Update 02

I verified that I am able to insert into the database and table. Mental note: make sure I have my column names correct (str_cluster_id vice string_cluster_id). I'm still getting a pymssql.OperationalError. How does the CONVERT() function fit into this. If I take it out I get a pymssql.ProgrammingError.

verified sql insert works:

# test insert into string_cluster_id
string_sql_insert = 'INSERT INTO ' + string_sql_table
string_sql_insert += ' (str_cluster_id) '
string_sql_insert += "VALUES ('BatchKm|20|k-means++|1')"

string_cluster_table = r'CPBB_DevClusterObjs'

Update 03

attempted to insert the below string:

string_sql_insert = 'INSERT INTO CPBB_DevClusterObjs'
string_sql_insert += '(obj_cluster_empty) '
string_sql_insert += "VALUES ('0x5468697320697320612062797‌​4652074657374')"

Result update 03

pymssql.OperationalError

Update 04

Success!! Below is what worked.

string_sql_insert = 'INSERT INTO CPBB_DevClusterObjs'
string_sql_insert += '(obj_cluster_empty) '
string_sql_insert += "VALUES (CONVERT(varbinary(max), '0x5468697320697320612062797‌​4652074657374'))"

Update 04 Result

successful insert into varbinary(max) column will post other updates as I find what else will work and what won't

_mssql package & exception handling

In order to understand how I was able to realize where my error was I took a round about path. I rewrote my test method using the _mssql package and took advantage of the exception handleing as directed in the documentation at _http://pymssql.org/en/stable/_mssql_examples.html.

The key for me was to realize how to use the exception handling as an object, e.g.: 

except _mssql.MSSQLDatabaseException as db_e:
    str_sql_error = 'mssql database error raised'
exc_db_number = db_e.number
exc_db_msg = db_e.message

db_e.text gave me this message:

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.DB-Lib error message 20018, severity 16: General SQL Server error: Check messages from the SQL Server.

this lead me to change where I needed the CONVERT() function to convert the data vice trying to convert the column in the destination database in which it is already a varbinary(max) column.

The insert string that worked is:

string_sql_insert = 'INSERT INTO CPBB_DevClusterObjs'
string_sql_insert += '(obj_cluster_empty) '
string_sql_insert += "VALUES (CONVERT(varbinary(max), '0x5468697320697320612062797‌​4652074657374'))"

Lesson Learned

  1. I was an 'idiot' and didn't ensure my column variables are correct. Make sure you do that before you post. It will help a lot.

  2. Use the exception handling as an object. It will help you trouble shoot where your errors are. If you're getting back into development / coding after over a decade of absence this will help.

  3. Don't be afraid to ask for help if you don't find an answer that fits what you're trying to accomplish. I found two references but I couldn't make the connection. Somebody will help you as evidence by this question.

  4. Be as detailed and specific as possible when asking for help. I could've done better at posting my exact test code originally. It probably wouldn't speed up the process.

Final Version of Test Code

Below is the final version of the test method that works to insert a binary piece of data into a varbinary(max) column in Microsoft Sql Server.

def Test_01():

    # lists
    list_sql_insert_data_type = ['%s', '%b']
    list_return = list()

    # variables
    string_sql_table = r'CPBB_DevClusterObjs'
    str_sql_error = ''
    user = r'user_me'
    host = r'server_me'
    pswd = r'pswd_me'
    db_name = r'db_me'
    bool_insert_into_table = False

    # sql connection
    list_sql_conn = SqlMethods.SqlGenConnection(user, host, pswd, db_name)
    sql_cursor = list_sql_conn[1].cursor()

    # _mssql connection
    _mssql_conn = _mssql.connect(server = host, user = user, password = pswd, database = db_name)

    # byte test variables
    byte_test_01 = b'This is a byte test'
    hex_01 = '0x'.encode('ascii') + binascii.hexlify(byte_test_01)

    # explicit string conversion of hex_01
    string_hex_01 = "'" + str(hex_01) + "'"
    string_hex_02 = str(hex_01)[1:]

    # create insert string
    #string_sql_insert = 'INSERT INTO CPBB_DevClusterObjs'
    #string_sql_insert += '(str_cluster_id, obj_cluster_empty) '
    #string_sql_insert += "VALUES ('BatchKm|20|k-means++|1','0x5468697320697320612062797‌​4652074657374')"

    # sucess!!  below insert works
    string_sql_insert = 'INSERT INTO CPBB_DevClusterObjs'
    string_sql_insert += '(obj_cluster_empty) '
    string_sql_insert += "VALUES (CONVERT(varbinary(max), '0x5468697320697320612062797‌​4652074657374'))"

    # test insert into string_cluster_id
    #string_sql_insert = 'INSERT INTO ' + string_sql_table
    #string_sql_insert += ' (str_cluster_id) '
    #string_sql_insert += "VALUES ('BatchKm|20|k-means++|1')"

    # take out the CONVERT() statement
    #string_sql_insert = 'INSERT INTO ' + string_sql_table
    #string_sql_insert += '(str_cluster_id, obj_cluster_empty) '
    #string_sql_insert += "VALUES ('BatchKm|20|k-means++|1'," + "'" + str(hex_01) + "'" + ')'

    try:
        sql_cursor.execute(string_sql_insert)
    except pymssql.OperationalError as oe:
        str_sql_error = 'Operational error was raised'
    except pymssql.ProgrammingError:
        str_sql_error = 'A program error was raised.'
    except pymssql.Error:
        str_sql_error = 'General error raised.'
    except pymssql.DatabaseError:
        str_sql_error = 'Database error raised.'
    except pymssql.DataError:
        str_sql_error = 'Data error raised.'
    except pymssql.IntegrityError:
        str_sql_error = 'Integrity error raised.'
    except pymssql.InterfaceError:
        str_sql_error = 'Interface error raised.'
    except pymssql.InternalError:
        str_sql_error = 'Internal error raised.'
    except pymssql.NotSupportedError:
        str_sql_error = 'Not supported error raised.'
    except pymssql.StandardError:
        str_sql_error = 'Standard error raised.'
    else:
        bool_insert_into_table = True
        list_sql_conn[1].commit()
    finally:
        list_return.append(bool_insert_into_table)
        list_return.append(str_sql_error)

    # return list
    return list_return

Upvotes: 3

Views: 8356

Answers (2)

tao lee
tao lee

Reputation: 1

insert_sql = "INSERT INTO [collecttest].[dbo].[Table_1]([c1],[c2]) VALUES (%s, %s)"
with open("../data/tpc-ds_ER.png", 'rb') as f:
    bdata = f.read()
    # hdata = f.read().encode('hex')  # only python2
    # hdata = f.read()
#     hdata = binascii.hexlify(bdata) # 
    print(type(bdata))
    values = [
        (pymssql.Binary(bdata), pymssql.Binary(bdata)),
        (pymssql.Binary(bdata), pymssql.Binary(bdata)),
    ]

    cursor.executemany(insert_sql, values)
    conn.commit()

Upvotes: 0

oshaiken
oshaiken

Reputation: 2650

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)

Upvotes: 2

Related Questions