0

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.

Tom Paulk
  • 3
  • 3
  • 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 Oct 12 '20 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 Oct 12 '20 at 19:36

1 Answers1

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))
Tom Paulk
  • 3
  • 3