How to convert np.int64 into python int64 for PandasSeries?

user_11012757 0
Viewed 2959

I try to insert data from pandas DataFrame into the PostgreSQL table,

table that a try to insert looks like:

city_id date        forecast
5       29.05.2019  0
1       29.05.2019  0
151     29.05.2019  0
55      29.05.2019  0
...

types:

  • city_id - numpy.int64
  • date - datetime.date
  • forecast - numpy.int64

And the block of code, that inserting data to db:

        with psycopg2.connect(f"host='{hostname}' \
                              dbname='{database}' \
                              user='{username}' \
                              password='{password}'") as connection:
            with connection.cursor() as cursor:
                connection.set_client_encoding('UTF8')
                for i in df_with_new_one.index:
                    date = df_with_new_one['date'][i]
                    city_id = df_with_new_one['city_id'][i]
                    value = df_with_new_one['forecast'][i]

                    cursor.execute("INSERT INTO forecast \
                    (city_id, computed_time, date, value) \
                    VALUES (%s, %s, %s, %s)", (city_id, now, date, value))

Where now is time saved as datetime.datetime.now()

And i get ProgrammingError:

    ProgrammingError: can't adapt type 'numpy.int64'

I checked type type(df_with_new_one['forecast'][0]) type is numpy.int64

So I get that PostreSQL can read only pythonic int and float, and the first thing i've tried was converting np.int64 into simple int with:

  • tolist()
  • pd.to_numeric()
  • int() for ((int(city_id), now, date, int(value))
  • .astype(int)
  • .value.astype('int')

Upd.:

  • city_id = int(df_with_new_one['city_id'][i]) value = int(df_with_new_one['forecast'][i])

Unfortunately none of them works for me

When I tried int() I get another error: 

    TypeError: cannot convert the series to <class 'int'>

Answers that i found, but no one of them helped me:

Are there any other methods to change type of values?

pandas numpy postgresql python types
3 Answers

The problem was in wrong indexation:

  • first index was from 83 to 1161 and after 1161, where should've been 1161, was 83 again and next values were 83 + 1 etc.

Thus, problem was solved by .reset_index()

df_with_new_one.reset_index(drop = True, inplace = True)

Thanks you all for answers!

user_110127570

You can use numpy.ndarray.item()

type(np.arange(1)[0])
# numpy.int64
type(np.arange(1)[0].item())
# int
user_6366260

First, your method is not very efficient because you repeatedly call cursor.execute instead of calling cursor.executemany.

But as you pass individuals values, it is easy to convert them just in time:

            ...
            for i in df_with_new_one.index:
                date = df_with_new_one['date'][i]
                city_id = int(df_with_new_one['city_id'][i])
                value = int(df_with_new_one['forecast'][i])
            ...
user_35452730
Related