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

問(wèn)題描述

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

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

Explicit Convert of Varbinary(max) column

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

我已驗(yàn)證我有權(quán)限并且可以將其他數(shù)據(jù)類(lèi)型插入到SQL服務(wù)器數(shù)據(jù)庫(kù)中。下面是表結(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'

我錯(cuò)過(guò)了什么，但我不知道是什么。我確實(shí)需要一些幫助。

更新01

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

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)容，但收到錯(cuò)誤”無(wú)法隱式將字節(jié)轉(zhuǎn)換為字符串”，因此我將其顯式轉(zhuǎn)換為字符串。

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

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

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

更新02

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

已驗(yàn)證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ā)布其他更新，因?yàn)槲野l(fā)現(xiàn)還有哪些可以工作，哪些不能

_MSSQL包&amp；異常處理

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

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

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

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ù)類(lèi)型varchar隱式轉(zhuǎn)換為varinary(Max)。使用CONVERT函數(shù)運(yùn)行此查詢(xún)。DB-LIB錯(cuò)誤消息20018，嚴(yán)重性16：常規(guī)SQL Server錯(cuò)誤：檢查來(lái)自SQL Server的消息。

這導(dǎo)致我更改了需要Convert()函數(shù)來(lái)轉(zhuǎn)換數(shù)據(jù)的位置，因?yàn)槲以噲D轉(zhuǎn)換目標(biāo)數(shù)據(jù)庫(kù)中的列，而該列在目標(biāo)數(shù)據(jù)庫(kù)中已經(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)

我是個(gè)‘白癡’，沒(méi)有確保我的列變量是正確的。一定要在發(fā)帖前做到這一點(diǎn)。這會(huì)有很大幫助的。

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

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

請(qǐng)?jiān)趯で髱椭鷷r(shí)盡可能詳細(xì)和具體。我本來(lái)可以在發(fā)布我的確切測(cè)試代碼時(shí)做得更好。這可能不會(huì)加快這一過(guò)程。

/li>

測(cè)試代碼的最終版本

下面是測(cè)試方法的最終版本，該方法用于將二進(jìn)制數(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

推薦答案

嘗試將您的字符串_SQL_INSERT修改為：

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

您正在插入以下內(nèi)容：

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

而不是這個(gè)：

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

如果查看值，就會(huì)發(fā)現(xiàn)它引發(fā)錯(cuò)誤的原因。

根據(jù)https://github.com/pymssql/pymssql/pull/179/files

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

vartype為”%s”

def test_binary_string(self):
    bindata = '{z
x03x07x194;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)

這篇關(guān)于使用pymssql將字節(jié)插入到Sql服務(wù)器變量二進(jìn)制(Max)列中的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，