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 ('0x54686973206973206120627974652074657374')"
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), '0x54686973206973206120627974652074657374'))"
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), '0x54686973206973206120627974652074657374'))"
Lesson Learned
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.
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.
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.
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','0x54686973206973206120627974652074657374')"
# sucess!! below insert works
string_sql_insert = 'INSERT INTO CPBB_DevClusterObjs'
string_sql_insert += '(obj_cluster_empty) '
string_sql_insert += "VALUES (CONVERT(varbinary(max), '0x54686973206973206120627974652074657374'))"
# 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