I need to insert some data into a table, and I have many user defined enum types there. I have the data in a pandas dataframe. How can I insert my data. A simplified version:
The type and the table is defined in PostgreSQL like that:
CREATE TYPE fruit_type AS enum ('APPLE', 'BANANA');
CREATE TABLE test(id int8, fruit fruit_type);
And the data frame I want to insert is this:
df = pd.DataFrame(data=dict(id=(1,2), fruit=("APPLE", "BANANA")))
When I want to insert the data using to_sql (with the index=False keyword argument) it gives me this error message:
sqlalchemy.exc.ProgrammingError: (psycopg.errors.DatatypeMismatch) column "fruit" is of type fruit_type but expression is of type character varying
How can I overcome this problem?
pandas version is 1.5.3, psycopg is 3.1.9, SQLAlchemy is 2.0.7, Python is 3.11
My code is something like this:
with sqlalchemy.create_engine("postgresql+psycopg://x:y@localhost:5432/fruit_db").connect().execution_options(autocommit=True) as con:
df.to_sql(table_name, con=con, if_exists="append", index=False, chunksize=chunksize)
con.commit()
UPDATE It works properly if I use psycopg2: replacing "postgresql+psycopg://" with "postgresql+psycopg2://" solves the problem (and I guess you also need the psycopg2 package installed). So the question is a specific psycopg 3 one.
