Copy Pandas Dataframe to Postgres with odo

Question

There is this top python library odo that I would love to use to transfer data from Pandas to my postgres db . it looks so convenient but i don't manage to use it :( .

Has someone an idea what I am missing. It must be a little thing .....

Dataframe = df
username: postgres
password: password
host : localhost
table: table_

So i come with the following ligne of code :

import odo, pandas 
..... 
odo(df, 'postgresql://postgres:password@localhost::mapping_ean_asin')

I got the error:

TypeError: 'module' object is not callable

Thank you in advance for your help :)

why not use something like pyodbc or sqlalchemy..much easier and lot of comm support — iamklaus, Oct 18 '18 at 12:53
can also use `psycopg2` which is specific to `postgres`. And really easy to use too. — Mayank Porwal, Oct 18 '18 at 12:56
With sqlalchemy I got a probelm with the engine and I guess will do it with psycopg2 and insert row by row the data. it would have been really cool to do it in one line of code :) — Remi-007, Oct 18 '18 at 13:02
You don't need to copy the data row-by-row to Postgres. For bulk loading, just create a file(.csv) from Dataframe and use `copy_expert` function to dump this in Postgres. — Mayank Porwal, Oct 18 '18 at 13:16
THE MODULE has the same name as the FUNCTION !!!! SO : odo.odo(df, 'postgresql://postgres:password@localhost::mapping_ean_asin') — Remi-007, Oct 18 '18 at 13:17

score 0 · Answer 1 · answered Oct 18 '18 at 13:42

Solution to my on question :) !

SQL_STATEMENT = """ COPY %s FROM STDIN WITH
    CSV
    HEADER
    DELIMITER AS ','
"""

my_file = open("/home.........csv")


def process_file(conn, table_name, file_object):
      cursor = conn.cursor()
      cursor.copy_expert(sql=SQL_STATEMENT % table_name, file=file_object)
      conn.commit()
      cursor.close()

connection =  psycopg2.connect("dbname='postgres' user='postgres' host='localhost'           password='password'")
     try:
           process_file(connection, 'my table', my_file)
     finally:
           connection.close()

I would have prefer the a solution in one line with odo but I got an second error.....

Copy Pandas Dataframe to Postgres with odo

1 Answers1