Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am trying to load rows of data into postgres in a csv-like structure using the copy_from command (function to utilize copy command in postgres). My data is delimited with commas(and unfortunately since I am not the data owner I cannot just change the delimiter). I run into a problem when I try to load a row that has a value in quotes containing a comma (ie. that comma should not be treated as a delimiter).

For example this row of data is fine:

",Madrid,SN,,SEN,,,SN,173,157"
This row of data is not fine:
","Dominican, Republic of",MC,,YUO,,,MC,65,162",
Some code:
    conn = get_psycopg_conn()
    cur = conn.cursor()
    _io_buffer.seek(0) #This buffer is holding the csv-like data
    cur.copy_from(_io_buffer, str(table_name), sep=',', null='', columns=column_names)
    conn.commit()
It looks like copy_from doesn't expose the csv mode or quote options, which are available form the underlying PostgreSQL COPY command. So you'll need to either patch psycopg2 to add them, or use copy_expert.
I haven't tried it, but something like
curs.copy_expert("""COPY mytable FROM STDIN WITH (FORMAT CSV)""", _io_buffer)
might be sufficient.
                Thanks Craig this answered my question. Unfortunately the data is being thrown out of order somewhere along the way, so unless I fix that issue will not get the chance to implement this. One step further though!
– wouldbesmooth
                Nov 24, 2014 at 14:38
                @sudo Well, it's only sad until someone writes a patch to implement it. It's not overly hard to modify psycopg2.
– Craig Ringer
                Jan 3, 2016 at 3:06
I had this same error and was able to get close to a fix based on the single line of code listed by craig-ringer.  The other item I needed was to include quotes for the initial object by using df.to_csv(index=False,header=False, quoting=csv.QUOTE_NONNUMERIC,sep=',') and specifically , quoting=csv.QUOTE_NONNUMERIC.  
The full example of pulling one data source from MySQL and storing it in Postgres is below:
#run in python 3.6
import MySQLdb
import psycopg2
import os
from io import StringIO
import pandas as pd
import csv
mysql_db = MySQLdb.connect(host="host_address",# your host, usually localhost
                     user="user_name",         # your username
                     passwd="source_pw",  # your password
                     db="source_db")       # name of the data base
postgres_db = psycopg2.connect("host=dest_address dbname=dest_db_name user=dest_user password=dest_pw")
my_list = ['1','2','3','4']
# you must create a Cursor object. It will let you execute all the queries you need
mysql_cur = mysql_db.cursor()
postgres_cur = postgres_db.cursor()
for item in my_list:
  # Pull cbi data for each state and write it to postgres
  print(item)
  mysql_sql = 'select * from my_table t \
       where t.important_feature = \'' + item + '\';'
  # Do something to create your dataframe here...
  df = pd.read_sql_query(mysql_sql, mysql_db)
  # Initialize a string buffer
  sio = StringIO()
  sio.write(df.to_csv(index=False,header=False, quoting=csv.QUOTE_NONNUMERIC,sep=','))  # Write the Pandas DataFrame as a csv to the buffer
  sio.seek(0)  # Be sure to reset the position to the start of the stream
  # Copy the string buffer to the database, as if it were an actual file
  with postgres_db.cursor() as c:
      print(c)
      c.copy_expert("""COPY schema:new_table FROM STDIN WITH (FORMAT CSV)""", sio)
      postgres_db.commit()
mysql_db.close()
postgres_db.close()
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.