dtype in pandas to_sql raises ValueError

user_2284814 0
Viewed 3214

I am able to successfully write the CSV data to SQLite using pandas in python using this code: 

df = pandas.read_csv(path + '/issues.csv')
df.to_sql('issues', connection, if_exists='replace', index=False)

However, I want to define one of the columns as an integer, and according to the documents when I try to use this code: 

df = pandas.read_csv(path + '/issues.csv')
df.to_sql('issues', connection, if_exists='replace', index=False, dtype={"severity_int": sqlalchemy.types.Integer()})

I get an error: 

File "/usr/local/lib/python3.7/site-packages/pandas/io/sql.py", line 1722, in to_sql
    raise ValueError(f"{col} ({my_type}) not a string")
ValueError: severity_int (INTEGER) not a string

I think I am doing it right but don't know where the error is.

pandas csv sqlite python
2 Answers

I don't know where the error is

No problem. I looked at the source file for where your error is thrown, and the issue only arises when the dtype passed is not a string, as the error message says.

if dtype is not None:
  for col, my_type in dtype.items():
    if not isinstance(my_type, str): #Right here!
      raise ValueError(
        "{column} ({type!s}) not a string".format(
          column=col, type=my_type
        )
      )

I also looked at the docs and see they import the types instead of composing as you did. Try it their way first:

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

from sqlalchemy.types import Integer

df.to_sql('integers', con=engine, index=False, dtype={"A": Integer()})
                                                          #^ Right here
user_113233040

I think that you need to convert all columns to string. try this:

df = df.astype(str)
user_65482230
Related