Convert numpy.int64 to python int in pandas

Question

I have an excel file with one sheet. This contains two columns num1, num2 and both of them has integer values. I'm trying to pull this data and insert it into Mysql database using Sqlalchemy and pandas.

from sqlalchemy import create_engine, MetaData,Column,Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,validates
import pandas as pd

Base = declarative_base()
connection_string = # give your connection string here
engine= create_engine(connection_string)
Base.metadata.bind = engine
s = sessionmaker()
session = s()

class a(Base):
    __tablename__ = 'a'
    id = Column(Integer,primary_key=True)
    num1 = Column(Integer)
    num2 = Column(Integer)

a.__table__.create(checkfirst=True)

excel_sheet_path = # give path to the excel sheet
sheetname = # give your sheet name here

df = pd.read_excel(excel_sheet_path,sheetname).transpose()


dict = df.to_dict()

for i in dict.values():
    session.add(a(**i))
session.commit()

This code throwing me an AttributeError saying

AttributeError: 'numpy.int64' object has no attribute 'translate'

So before converting dataframe into dictionary I tried many functions like astype, to_numeric to change the datatypes into normal python int, but they aren't working at all. The problem seems to persist only when the dataframe has all integer values. If you have atleast one another column with type string or date, Then the program works normally. How do I solve this ?

Are you by any chance using pymysql? A somewhat related post: stackoverflow.com/questions/45291944/… — Ilja Everilä
– Ilja Everilä, Commented Sep 21, 2017 at 11:20

Qinhong Ma · Accepted Answer · 2019-02-22 01:15:02Z

1

In trouble with this, too. I finally find out a somewhat unskillful solution as follows:

def trans(data):
"""
translate numpy.int/float into python native data type
"""
result = []
for i in data.index:
    # i = data.index[0]
    d0 = data.iloc[i].values
    d = []
    for j in d0:
        if 'int' in str(type(j)):
            res = j.item() if 'item' in dir(j) else j
        elif 'float' in str(type(j)):
            res = j.item() if 'item' in dir(j) else j
        else:
            res = j
        d.append(res)
    d = tuple(d)
    result.append(d)
result = tuple(result)
return result

However, it performs poorly when handling data with large number of rows. You will spend some minutes in translate a dataframe with more than 100,000 records.

answered Feb 22, 2019 at 1:15

Qinhong Ma

91 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert numpy.int64 to python int in pandas

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related