Python pymssql insert error

Question

My table has 24 columns around half of the column in my table are of float datatype. Specified 24 filed, I have truncated the insert statement here.

csv_data = csv.reader(file('filename.csv'))
for row in csv_data:
cursor.execute('insert into ddreplication (CTX, Mode,...,Max_repl_streams) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', tuple(row))

Error:

File "pymssql.pyx", line 467, in pymssql.Cursor.execute
(pymssql.c:7561)
    pymssql.OperationalError: (8114, 'Error converting data type varchar to float.DB-Lib error message 20018, severity 16:\nGeneral SQL
Server error: Check messages from the SQL Server\n')

Im having almost the same code on another script which is running fine without any issues.

Output of "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ddreplication' ORDER BY ORDINAL_POSITION"

[(u'CTX', u'int'), (u'Mode', u'nvarchar'), (u'Destination', u'nvarchar'), (u'Connection_Host', u'nvarchar'), (u'Enabled', u'nvarchar'), (u'Low_bandwidth_optimization', u'nvarchar'), (u'Replication_encryption', u'nvarchar'), (u'Replication_propagate_retention_lock', u'nvarchar'), (u'Local_fs_status', u'nvarchar'), (u'Connection', u'nvarchar'), (u'State', u'nvarchar'), (u'Error', u'nvarchar'), (u'Network_bytes_to_destination', u'float'), (u'PreComp_bytes_written_to_source', u'float'), (u'PreComp_bytes_sent_to_destination', u'float'), (u'Bytes_after_synthetic_optimization', u'float'), (u'Bytes_after_filtering_by_destination', u'float'), (u'Bytes_after_low_bandwidth_optimization', u'float'), (u'Bytes_after_local_comp', u'float'), (u'PreComp_bytes_remaining', u'float'), (u'Compression_ration', u'float'), (u'Synced_as_of_time', u'nvarchar'), (u'Current_throttle', u'nvarchar'), (u'Max_repl_streams', u'nvarchar')]

Can you provide sample data? Additionally, you checked that column order in the CSV matches exactly with the column list for the INSERT statement? — Arminius
I have attached 2 lines from my datafile. I have double checked Column order in CSV matches insert statement. I have truncated 2 row data due to space here 19,destination,mtree://dd9500ausprd01.storage.anz/data/col1/oracle-longterm-fromtm,dd9500ausdr01R.storage.anz,yes,disabled,disabled,enabled,enabled,idle since Sat Aug 19 02:33:45,normal,no error,324423376,0,0,0,0,0,0,0,N/A,Sat Aug 19 02:33,unlimited (Recovery only),32 (default) 20,source,mtree://d234sdfcom1.storage.com/data/col1/FROMNSDEVMW,....4624454382864,...,3812323071052,0,22.1,Sat Aug 19 02:46,unlimited,32 (default) — Raju
Please edit your question to show the results of cursor.execute("SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ddreplication' ORDER BY ORDINAL_POSITION"); print(cursor.fetchall()) — Gord Thompson
Additionally, please add then function def is_float(s): try: if s is None: return False f = float(s) return True except ValueError: return False in your script and post the output of this line, which you kindly insert before the cursor.execute line: [print(i, is_float(v)) for i,v in enumerate(row)] — Arminius
@Arminius - Your suggestion helped me to find the culprit within my data. There is a particular column which was supposed to write some number OR 0, But it was getting written as "N/A" which resulted in an error I mentioned in the question. Also it helped me to spot another error which happens when I was extracting the original data and creating the csv file. Last line of my CSV file have some issues. Now I managed to skip those errors and got my data into MSSQL DB. Now I will try and fix my code to avoid those errors in first place. Thanks Arminius — Raju

Raju Raju · Accepted Answer · 2017-08-19T13:53:08

Below function helped me to find out the errors on my original data. It give me details such as data type of each of the value that I'm trying to insert and count of values that is getting passed into the insert statement. Now I managed to override and get data inserted into the db, excluding the problem record.

Now I'm working on fixing my problem record and get it into DB.

def is_float(s): try: if s is None: return False f = float(s) return True except ValueError: return False

print (len(row)) for i,v in enumerate(row): print(i, is_float(v),row[i])

Python pymssql insert error

2 Answers