Pandas and SQL Alchemy: Specify Column Data Types

Question

Updated:

I saw some guidance here, but I can't seem to figure out how to specify the column types when I insert data from pandas into Oracle using the code below. One column is a date, for example, but upon importing, it is converted to a string.

Also, if I want the column names to be slightly different in my Oracle database, do I need to first rename the columns via pandas then send them to Oracle via to_sql?

import pandas as pd
from sqlalchemy import create_engine
import cx_Oracle as cx
pwd=input('Enter Password for server:')
engine = create_engine('oracle+cx_oracle://schema:'+pwd+'@server:1521/service_name')
df=pd.read_csv(r'path\data.csv',encoding='latin-1',index_col=0)
name='table1'
df.to_sql(name,engine,if_exists='append')

Answer 1

Please read SQL Data Types section of the pandas documentation as well as the to_sql method.

You are able to specify the data type using dtype parameter like this:

from sqlalchemy.types import String, Date, DateTime
df.to_sql(table_name, engine, if_exists='append', dtype={'mydatecol': DateTime})

As to the names of the columns, it is easiest to rename columns in the dataframe before calling to_sql:

df2 = df.rename(columns={'oldname': 'newname', ...})