sqlalchemy and PANDAS

PANDAS is high-performance, easy-to-use data structures and data analysis tools for the Python.
I love it.
And Sqlalchemy is the Python SQL toolkit and Object Relational Mapper.
I’m not good at SQL… ;-(, Sqlalchemy is very helpful for me.
I want to make table from pandas dataframe in postgresql.
I found cool method. “DataFrame.to_sql”
Example is follow.
I used irisdata set as example.
* need Pandas ver 14.x and psycopg2 to connect postgresql.


from sqlalchemy import *
import pandas as pd
from sklearn import datasets
iris = datasets.load_iris()
#make DataFrame
iris_data = pd.DataFrame(iris.data)
#create engine
engine = create_engine("postgresql+psycopg2://<username>:<password>@localhost/<dbname>")
#set dataset column names.
iris_data.columns = ["sep_length", "sepal_width","petal_length","petal_width" ]
#write dataframe to postgresql database
iris_data.to_sql("iris_data", con=engine, if_exists="replace")

Only to_sql ! Don’t need some loop or commit command.
Great!
Next check DB.


iwatobipen$ psql -U postgres -d testdb
psql (9.3.4)
Type "help" for help.
testdb=> \z
                             Access privileges
 Schema |   Name    | Type  | Access privileges | Column access privileges 
--------+-----------+-------+-------------------+--------------------------
 public | iris_data | table |                   | 
(1 rows)

testdb=> select * from iris_data ;
 index | sep_length | sepal_width | petal_length | petal_width 
-------+------------+-------------+--------------+-------------
     0 |        5.1 |         3.5 |          1.4 |         0.2
     1 |        4.9 |           3 |          1.4 |         0.2
     2 |        4.7 |         3.2 |          1.3 |         0.2
     3 |        4.6 |         3.1 |          1.5 |         0.2
     4 |          5 |         3.6 |          1.4 |         0.2
     5 |        5.4 |         3.9 |          1.7 |         0.4
     6 |        4.6 |         3.4 |          1.4 |         0.3
     7 |          5 |         3.4 |          1.5 |         0.2
 ..............

works fine! 😉

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s