本文介紹了使用pymssql將字節(jié)插入到Sql服務(wù)器變量二進制(Max)列中的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！

問題描述

我正在嘗試使用Python3.5和pymssql包將字節(jié)或字節(jié)數(shù)組插入到SQL Server表中。嘗試插入后，我不斷收到pymssql.ProgrammingError。

我已使用以下鏈接和問題作為參考，但仍有問題：

Explicit Convert of Varbinary(max) column

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

我已驗證我有權(quán)限并且可以將其他數(shù)據(jù)類型插入到SQL服務(wù)器數(shù)據(jù)庫中。下面是表結(jié)構(gòu)。

Table Structure

嘗試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)

結(jié)果01

pymssql.ProgrammingError

hex_01 = b'0x54686973206973206120627974652074657374'

嘗試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)

結(jié)果02

pymssql.ProgrammingError

hex_01 = b'0x54686973206973206120627974652074657374'

嘗試03

我取出Convert()函數(shù)和…

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)

結(jié)果03

pymssql.ProgrammingError

hex_01 = b'0x54686973206973206120627974652074657374'

我錯過了什么，但我不知道是什么。我確實需要一些幫助。

更新01

下面是我在測試方法中的完整代碼：

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

更新01結(jié)果

我嘗試了您最初推薦的內(nèi)容，但收到錯誤”無法隱式將字節(jié)轉(zhuǎn)換為字符串”，因此我將其顯式轉(zhuǎn)換為字符串。

而不是在獲取pymssql.OperationalError時出現(xiàn)pymssql.ProgrammingError。

如果我取出Convert()函數(shù)，則會得到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'')

是否應(yīng)為STRING_HEX_01：

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

這還會產(chǎn)生pymssql.OperationalError

更新02

我驗證了我能夠插入到數(shù)據(jù)庫和表中。注意：請確保我的列名是正確的(str_cluster_id，否則就是string_cluster_id)。我仍然收到pymssql.OperationalError。Convert()函數(shù)如何適應(yīng)這種情況。如果我取出它，我會得到一個pymssql。ProgrammingError。

已驗證SQL INSERT是否正常工作：

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

字符串_群集_表=r‘CPBB_DevClusterObjs’

更新03

嘗試插入以下字符串：

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

結(jié)果更新03

pymssql.OperationalError

更新04

成功！！以下是有效的方法。

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

更新04結(jié)果

成功插入到varinary(Max)列中
我將發(fā)布其他更新，因為我發(fā)現(xiàn)還有哪些可以工作，哪些不能

_MSSQL包&amp；異常處理

為了理解我如何能夠認(rèn)識到我的錯誤在哪里，我繞了一圈。我使用_msql包重寫了我的測試方法，并按照_http://pymssql.org/en/stable/_mssql_examples.html.

的文檔中的指示利用了異常處理

對我來說，關(guān)鍵是了解如何將異常處理作為對象使用，例如：

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給我以下消息：

不允許從數(shù)據(jù)類型varchar隱式轉(zhuǎn)換為varinary(Max)。使用CONVERT函數(shù)運行此查詢。DB-LIB錯誤消息20018，嚴(yán)重性16：常規(guī)SQL Server錯誤：檢查來自SQL Server的消息。

這導(dǎo)致我更改了需要Convert()函數(shù)來轉(zhuǎn)換數(shù)據(jù)的位置，因為我試圖轉(zhuǎn)換目標(biāo)數(shù)據(jù)庫中的列，而該列在目標(biāo)數(shù)據(jù)庫中已經(jīng)是varinary(Max)列。

有效的插入字符串為：

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

吸取的教訓(xùn)

我是個‘白癡’，沒有確保我的列變量是正確的。一定要在發(fā)帖前做到這一點。這會有很大幫助的。

將異常處理用作對象。它將幫助您解決您的錯誤所在。如果您在離開十多年后重新投入開發(fā)/編碼，這將有所幫助。

如果你找不到與你想要實現(xiàn)的目標(biāo)相匹配的答案，不要害怕尋求幫助。我找到了兩個推薦人，但我找不到其中的聯(lián)系。這個問題會有人作為證據(jù)幫助你的。

請在尋求幫助時盡可能詳細和具體。我本來可以在發(fā)布我的確切測試代碼時做得更好。這可能不會加快這一過程。

/li>

測試代碼的最終版本

下面是測試方法的最終版本，該方法用于將二進制數(shù)據(jù)插入到Microsoft SQL Server的varinary(Max)列中。

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