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')

You have to pass a table name, like: df.to_sql('table_name', engine, if_exists='append') (actually the code you give should error I think) — joris
– joris, Commented Apr 20, 2016 at 21:32
Thanks, @joris . I was supposed to put the service name where I had the table name and then add the table name as you suggested. This worked, so I'm now going to see how to specify field types. — Dance Party2
– Dance Party2, Commented Apr 21, 2016 at 14:10

van · Accepted Answer · 2016-04-21 19:19:25Z

10

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', ...})

answered Apr 21, 2016 at 19:19

van

77.5k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas and SQL Alchemy: Specify Column Data Types

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related