Python and Knime, issue with cutoff on 1000 records

Hello All,


I was trying to use a function of mine from Python in Knime to load/replace a MicrosotSQL database. However when I tried building a dataframe off of the output table I was cutoff at 1,000 records.


I tried using KIn as a variable to read in but I go the error "KIn is not defined". Any idea? I have been googling a while =)

 

# Copy input to output
from DL import sqload_replace
import pandas as pd

output_table = input_table.copy()

#shows 1,000 records in df, using shift + f6 before shows me all 13,000 records

df = pd.Dataframe(columns = column_list,output_table)

sqload_replace(table,database,output_table)

# 1,000 records entered.

Thanks! 
Justin

Hi Justin,

Are you perhaps using the Python DB script nodes with connection-type ports? These ports always ever only show a dataset preview. If you want the whole thing, you need to cast to a regular KNIME table with the Database Connection Table Reader to execute the full query.

HTH,
E

Hello HTH,

 

I am using Python Source, 


First I use a script to pull data from SFDC (backend way). Then I manipulate it. At the very end I try to reload the data but the input_table only reads in 1000 records. If I try passing this through a sqlalchemy function to replace my MSSQL table it will only load 1000 records. Please see my code below. 

 

The real reason why I went back to python instead of using MSSQL wrtier is I got the error ("Cannot insert an explicit value into a timestamp column". Data type was datetime64[ns] but for some reason it failed when I tried to insert OR replace a table.

 

# Copy input to output and reloat data


from DL import sqload_replace
import pymssql

output_table = input_table.copy() # 1000 records only. 

sqload_replace(output_table,Database,table)

Hey All,

Anyone else have any ideas? This is a huge hurdle in my adoption of knime at this point. I have pretty bad tendonitis from typing so much in python!

 

Best,

ImportJJ

Hey Justin,

The "official KNIME way" would probably use the database connector/reader nodes instead of vanilla Python code - any reason why this wouldn't work for you? Avoids even more code and tendonitis... :-)

Cheers
E

Hi Justin,

the Python nodes only show you a slice of the data in the dialog(to avoid long runtimes). The default for this is the first 1000 rows. It can be configured in the dialog tab 'Options'. I hope this helps you.

Cheers,

Patrick

Thanks =) My file from the database has two empty columns and knime couldnt read it in, python just assigned a unnamed column value...only reason why really.

The thread is old, but still worth an answer.
The Python Script (1 => 1) in the configure windows, in the Option sheet, has the field “Row limit (dialog)” on the top of the windows, which is set to 1000 by default. It’s a tiny bar, it took me a while to notice it.
Hope this helps anybody.
Bests

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.