Selecting from decimal (18,6) return NaN in pandas

[Diyago]

Evn: Python 3.6, windows 10,

Connection:

connect = udaExec.connect(
    method="odbc",
    system='***',
    username= username,
    password= password,
    driver="Teradata Database ODBC Driver 16.10",
    dataTypeConverter=datatypes.DefaultDataTypeConverter(
                    useFloat=False) #tried setting to True - same results
)

query = 'sel top 1 MNQ from tb1'
pd.read_sql(query, connect) #this return NaNs as results

To reproduce:

query = 'sel 1.234234 as qqq'
pd.read_sql(query, connect) 

How to fix this?

[Diyago]

My problem easily fixed my using another library:

from turbodbc import connect
driver="Teradata Database ODBC Driver 16.10"
connection = connect(driver=driver,
                      server=ip, 
                      DBCName='.com', 
                      uid=username,
                      pwd=password)

cursor = connection.cursor()
query = 'sel * from tb'
cursor.execute(query)
data = pd.DataFrame(cursor.fetchallnumpy()) #to get pandas dataframe
data.to_csv('tb.csv')
cursor.close()

Is this library actually supported?

[tetraptych]

Note that in the second example you are avoiding pd.read_sql entirely, suggesting this method might be causing the problem.

I suggest fetching the rows from the database using the Teradata library (via cursor.execute(query).fetchall()), then creating a dataframe using the list of returned Row objects. Depending on what the pandas constructor expects, you may need to access the .columns and .values of individual rows.