I want export foo.csv to my database by using pandas.I use sqlalchemy for operating sqlite, and declare a model just like the following code:
class House:
__table__name='house'
id=db.Column(db.Integer,index=True,primary_key=True)
city=db.Column(db.String(128),index=True,default="")
When I try to use pandas.read_csv to deal with that--here is an example:df=pd.read_csv('path/foo.csv',error_bad_lines=False)I got an error :
sqlite3.IntegrityError: datatype mismatch
It seems that pandas read data as string. I also search the solution for converting data type.It does help but still can not solve my problem as it occurs:
File "pandas/_libs/parsers.pyx", line 1173, in pandas._libs.parsers.TextReader._convert_tokens
TypeError: Cannot cast array from dtype('O') to dtype('int64') according to the rule 'safe'
ValueError: invalid literal for int() with base 10
here is the code I have tried:
import pandas as pd
col_names=pd.read_csv('path/foo.csv',error_bad_lines=False,nrows=0).columns
type_dict={'price':int,'id':int}
type_dict.update({col:str for col in col_names if col not in type_dict})
con=sqlalchemy.create_engine(Config.SQLALCHEMY_DATABASE_URI,echo=True)
df=pd.read_csv('path/foo.csv',error_bad_lines=False,dtype=type_dict)
df.to_sql(name='house',con=con,if_exists='append',index=False)
attention: I know there is a question similar to this. But I can not see it could help solve this. I want to know how to solve this ,or are there some other decent ways to export csv to database?
added my part of data:
id,title,unit,price,latitude
1, US Fut town, 501#2,500,31.199851740354852
