Geek Answers Handbook

Psycopg2 "copy_from" command, can I ignore the delimiter in the quote (getting an error)?

I am trying to load data rows in postgres in a csv-like structure using the copy_from command (function to use the copy command in postgres). My data is separated by commas (and unfortunately, since I am not the owner of the data, I cannot just change the delimiter). I encounter a problem when I try to load a string that matters in quotation marks containing a comma (i.e., this comma should not be treated as a delimiter).

For example, this data row is beautiful:

",Madrid,SN,,SEN,,,SN,173,157"

This data row is small:

","Dominican, Republic of",MC,,YUO,,,MC,65,162",

Some codes:

    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()
+4
source share
2 answers

, copy_from csv quote, PostgreSQL COPY. psycopg2, copy_expert.

, -

curs.copy_expert("""COPY mytable FROM STDIN WITH (FORMAT CSV)""", _io_buffer)

.

+7

, , , craig-ringer. , , , df.to_csv(index=False,header=False, quoting=csv.QUOTE_NONNUMERIC,sep=',') , , , quoting=csv.QUOTE_NONNUMERIC.

MySQL Postgres :

#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()
0

More articles:


All Articles