SQL / Python Trying to check if data already exists: Arithmetic overflow error converting nvarchar to data type numeric

Question

I am trying to iterate through a table to check if a row of data has already been committed. I inserted 5 rows then worked out the solution to check if working row exists before inserting or skipping. This worked for 10 rows of data. I then took out the limitation and tried to insert all of the remaining rows. However, at row 18 my solution hits a wall giving me the error:

Traceback (most recent call last):
...
    cursor_object.execute("""SELECT COUNT(*) 
pyodbc.DataError: ('22003', '[22003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Arithmetic overflow error converting nvarchar to data type numeric. (8115) (SQLExecDirectW)')

This is the code that I adapted from this discussion on Stack Overflow.

    for filing in directory_list:
        value_list = []

        value = filing['filing']['report_num']
        
        print(filing)
        cursor_object.execute("""SELECT COUNT(*) 
                                FROM filings_table 
                                WHERE report_num = {} 
                                GROUP BY report_num""".format(value))

        exists = cursor_object.fetchone()
        print(exists)

It fails on the last filing shown. The one immediately above it is a numeric anomaly in the numbering sequence. I have verified that it is valid in the source index.json file.

Filing already in database.
{'filing': {'report_num': '999999999520001257', 'filing_date': '2020-05-27', 'url': 'https://www.sec.gov/Archives/edgar/data//0001465885/999999999520001257/9999999995-20-001257-index.html', 'report_type': 'Form EFFECT', 'cik_num': '0001465885'}}
(1, )
Filing already in database.
{'filing': {'report_num': '000162828020008319', 'filing_date': '2020-05-22', 'url': 'https://www.sec.gov/Archives/edgar/data//0001465885/000162828020008319/0001628280-20-008319-index.html', 'report_type': 'Form CORRESP', 'cik_num': '0001465885'}}
Traceback (most recent call last):

How can I resolve this issue where there shouldn't be any arithmetic or conversion happening?

Edit: The select function returns 'None' if there is no match and '(1, )' if there is a match.

What does select schema_name(tab.schema_id) as schema_name, tab.name as table_name, col.column_id, col.name as column_name, t.name as data_type, col.max_length, col.precision from sys.tables as tab inner join sys.columns as col on tab.object_id = col.object_id left join sys.types as t on col.user_type_id = t.user_type_id where table_name = 'filings_table' order by schema_name, table_name, column_id; show? — hd1
– hd1, Commented Oct 12, 2020 at 18:50
schema table_name col_id column_name data_type max_length precision dbo filings_table 1 report_num nvarchar 100 0 dbo filings_table 2 filing_date date 3 10 dbo filings_table 3 url varchar -1 0 dbo filings_table 4 report_type nvarchar 100 0 dbo filings_table 5 cik_num nchar 20 0 — Tom Paulk
– Tom Paulk, Commented Oct 12, 2020 at 19:36

Tom Paulk · Accepted Answer · 2020-10-12 20:50:07Z

0

Turns out the issue is that SQL Server is doing an implicit conversion. Putting quotes around the brackets resolved the issue. Credit goes to my wife who figured it out in about 2 minutes.

cursor_object.execute("""SELECT COUNT(*) 
                         FROM filings_table 
                         WHERE report_num = '{}'
                         GROUP BY report_num""".format(value))

answered Oct 12, 2020 at 20:50

Tom Paulk

33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL / Python Trying to check if data already exists: Arithmetic overflow error converting nvarchar to data type numeric

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related