Pandas to_sql set column type

Question

I have a column with numbers in a String format that I want to send to PostresSQL as float. How can I ensure that SQLAlchemy sets this column as float? (note that could be a NaN in the column). Here's the code

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('postgresql://{}:{}@{}:5432/{}'.format(USER, DB_PW, HOST, DB))

df = pd.DataFrame({'String2Number': ['0,2', '', '0,0000001']})

df.to_sql(name='TABLE_NAME', con=engine, index=False)

Answer 1

First of all, your numbers should be in decimal point format, so we need to replace your decimals with comma. Next you should ensure that to_sql function will use float, and You can achieve this with dtype argument that enable to set a column type (based on sqlalchemy types) when inserting in database. Here the code:

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.types import Float # note this import to use sqlalchemy Float type

engine = create_engine('postgresql://{}:{}@{}:5432/{}'.format(USER, DB_PW, HOST, DB))

df = pd.DataFrame({'String2Number': ['0,2', '', '0,0000001']})

# Replacing ',' to '.'
df['String2Number'] = df['String2Number'].apply(lambda x: str(x).replace(',', '.'))

# Set column type as SQLAlchemy Float
df.to_sql(
  name='TABLE_NAME',
  con=engine,
  index=False,
  dtype={'String2Number': Float()}
)

Answer 2

to_sql have a parameter called dtype you can use that to define schema. (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html)

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.types import Float

engine = create_engine('postgresql://{}:{}@{}:5432/{}'.format(USER, DB_PW, HOST, DB))

df = pd.DataFrame({'String2Number': ['0,2', '', '0,0000001']})

df.to_sql(name='TABLE_NAME', con=engine, index=False, dtype={"String2Number": Float()})