0

Versions where problem occurs:

python 3.6.13 
pandas 1.1.5 
numpy 1.19.2

This seems trivial but I can't find a satifying solultion so far. First, I import data into a pandas Dataframe before loading to an SQL database. The failure message that I've gotten is:

ProgrammingError: (pyodbc.ProgrammingError) ('Invalid parameter type.  param-index=0 param-type=numpy.int64', 'HY105')

Apparently, to get the dataframe into the database, the dtype can't be numpy.int64 and must be int. I had found a solution here: "Invalid parameter type" (numpy.int64) when inserting rows with executemany()

Here is a screenshot of the target column dtype:

enter image description here

The only way I've found to get data to be dtype int is the native function int(), but that can be only used on singular values.

The numpy method .astype(int) for some reason only converts to numpy.int32:

df = pd.DataFrame(data=[[1,4,5], [2, 'nan', 4]], columns=['A', 'B', 'C'])
df[['A', 'C']] = df[['A', 'C']].astype(int)
df.info()

Both the .info() method, as well as checking the type of individual values yields int32 for me.

Can someone please tell me how to turn the whole dataframe into native int that way I can import into my database??

chic9009
  • 77
  • 8
  • The underlying data structure of the DataFrame is going to be one of the valid numpy types or `object` (even if using some of the pandas experimental types). There is typically some configurations available in the transfer protocol from pandas to sql. You've not provided the code for how you're trying to export from pandas to SQL nor the table schema. That would be helpful to determine what options are available. – Henry Ecker Oct 11 '21 at 21:44
  • updated question! – chic9009 Oct 11 '21 at 21:50
  • `df.to_numpy().tolist()` should produce a list of lists of ints. There may also be a `df.to_list()` method – hpaulj Oct 11 '21 at 22:04
  • I mentioned below that I need the values in the dataframe for a couple further steps. When I try to assign the dataframe columns to these generated lists, it converts back to int64 – chic9009 Oct 11 '21 at 22:11
  • You could try going to string instead and let the Database parse the string input into the appropriate type. – Henry Ecker Oct 11 '21 at 22:27
  • Hm, interesting suggestion. Not completely viable because I do operations with the frame once more before importing, but I'll see if I can integrate it – chic9009 Oct 12 '21 at 10:39
  • Thank you to those who gave input! I couldn't find a way to convert a pandas df to int, only numpy.intXX, but I found a solution where I write the values individually to the SQL database, so at that point I convert the indiviual values to int. Therefore I circumvented the problem. – chic9009 Oct 12 '21 at 11:14

1 Answers1

0

You should know which int bit length your database uses and convert with the appropriate type: np.int8/np.int16/np.int32/np.int64

Example:

import numpy as np
df['col'].astype(np.int8)
mozway
  • 194,879
  • 13
  • 39
  • 75
  • In the Microsoft SQL server the value type is int. It's also rejected np.int32 and np.int64. That's why I assume it can't be a numpy dtype. – chic9009 Oct 11 '21 at 21:51
  • int doesn't mean anything *per se*, there are different types of bit length, have you tried np.int16 and np.int8? – mozway Oct 11 '21 at 21:53
  • both also fail with the same response. param-type=numpy.intXX respectively. – chic9009 Oct 11 '21 at 21:57
  • You could use `df[['A', 'C']].values.tolist()` to get lists of python int – mozway Oct 11 '21 at 22:02
  • It does but I need the values in the dataframe for a couple further steps. When I try to assign the dataframe columns to these generated lists, it converts back to int64 – chic9009 Oct 11 '21 at 22:10
  • it's worth noteing, that this had worked for a while. then I had to reset my enviorment and something got upgraded that's not alow this anymore. – chic9009 Oct 11 '21 at 22:17