How to write DataFrame to postgres table

Question

There is DataFrame to sql method  but it works only for mysql  sqlite and oracle databases  I cant pass to this method postgres connection or sqlalchemy engine

User · Answer

Pandas 0 24 0  solution   In Pandas 0 24 0 a new feature was introduced specifically designed for fast writes to Postgres  You can learn more about it here  https   pandas pydata org pandas-docs stable user guide io html io-sql-method  import csv from io import StringIO  from sqlalchemy import create engine  def psql insert copy table  conn  keys  data iter         gets a DBAPI connection that can provide a cursor     dbapi conn   conn connection     with dbapi conn cursor   as cur          s buf   StringIO           writer   csv writer s buf          writer writerows data iter          s buf seek 0           columns        join        format k  for k in keys          if table schema              table name           format table schema  table name          else              table name   table name          sql    COPY         FROM STDIN WITH CSV  format              table name  columns          cur copy expert sql sql  file s buf   engine   create engine  postgresql   myusername mypassword myhost 5432 mydatabase   df to sql  table name   engine  method psql insert copy

User · Answer

Faster option  The following code will copy your Pandas DF to postgres DB much faster than df to sql method and you won t need any intermediate csv file to store the df  Create an engine based on your DB specifications  Create a table in your postgres DB that has equal number of columns as the Dataframe  df   Data in DF will get inserted in your postgres table  from sqlalchemy import create engine import psycopg2  import io  if you want to replace the table  we can replace it with normal to sql method using headers from our df and then load the entire big time consuming df into DB  engine   create engine  postgresql psycopg2   username password host port database    df head 0  to sql  table name   engine  if exists  replace  index False   drops old table and creates new empty table  conn   engine raw connection   cur   conn cursor   output   io StringIO   df to csv output  sep   t   header False  index False  output seek 0  contents   output getvalue   cur copy from output   table name   null  quot  quot     null values become    conn commit

User · Answer

For Python 2 7 and Pandas 0 24 2 and using Psycopg2  Psycopg2 Connection Module  def dbConnect  db parm  username parm  host parm  pw parm         Parse in connection information     credentials     host   host parm   database   db parm   user   username parm   password   pw parm      conn   psycopg2 connect   credentials      conn autocommit   True    auto-commit each entry to the database     conn cursor factory   RealDictCursor     cur   conn cursor       print   Connected Successfully to DB      str db parm          str host parm       return conn  cur   Connect to the database  conn  cur   dbConnect databaseName  dbUser  dbHost  dbPwd    Assuming dataframe to be present already as df  output   io BytesIO     For Python3 use StringIO df to csv output  sep   t   header True  index False  output seek 0    Required for rewinding the String object copy query    COPY mem info FROM STDOUT csv DELIMITER   t  NULL     ESCAPE      HEADER      Replace your table name in place of mem info cur copy expert copy query  output  conn commit

User · Answer

This is how I did it    It may be faster because it is using execute batch     df is the dataframe if len df   gt  0      df columns   list df        create  col1 col2          columns       join df columns         create VALUES   s     s       one   s  per column     values    VALUES      format     join    s  for   in df columns           create INSERT INTO table  columns  VALUES   s           insert stmt    INSERT INTO             format table columns values       cur   conn cursor       psycopg2 extras execute batch cur  insert stmt  df values      conn commit       cur close

User · Answer

Starting from pandas 0 14  released end of May 2014   postgresql is supported  The sql module now uses sqlalchemy to support different database flavors  You can pass a sqlalchemy engine for a postgresql database  see docs   E g   from sqlalchemy import create engine engine   create engine  postgresql   username password localhost 5432 mydatabase   df to sql  table name   engine    You are correct that in pandas up to version 0 13 1 postgresql was not supported  If you need to use an older version of pandas  here is a patched version of pandas io sql  https   gist github com jorisvandenbossche 10841234  I wrote this a time ago  so cannot fully guarantee that it always works  buth the basis should be there   If you put that file in your working directory and import it  then you should be able to do  where con is a postgresql connection   import sql    the patched version  file is named sql py  sql write frame df   table name   con  flavor  postgresql

[python] How to write DataFrame to postgres table?

Examples related to python

Examples related to postgresql

Examples related to pandas

Examples related to sqlalchemy