python - Psycopg2 copy_from for csv to postgress

Question

I have a csv file that i read into pandas, and im supposed to insert into postgres. The file contains strings in some fields with the backslash "character". This causes a problem because the copy_from function reads it as an escape character. how do i let it ignore "" and leave it as a string. i have tried many different encoding formats but i stil get a "cannot decode character" error. issue is i cannot replace that character, it is important in the string.

def load_into_db(cur, con, file,table_name):
f = open(file, mode="r", encoding='utf-8')
try:
    # print("wrote to csv")
    sqlstr = "COPY {} FROM STDIN DELIMITER '|' CSV".format(table_name)
    cur.copy_from(f, table_name, null="nan", sep="|")
    con.commit()
    f.close() 
except Exception as e:
    print(e)
    print("something went wrong")

example of the rows causing the issue

name	age	attribute
name1	23	example/1/test
name2	26	example/2/test

Answer 1

import io
import csv
def df2db(df_a, table_name, engine):
    output = io.StringIO()
    # ignore the index
    # df_a.to_csv(output, sep='', index = False, header = False, quoting=csv.QUOTE_NONE)
    df_a.to_csv(output, sep='', index = False, header = False, quoting=csv.QUOTE_NONE, escapechar='\')
    output.getvalue()
    # jump to start of stream
    output.seek(0)
    
    #engine <--- from sqlalchemy import create_engine
    connection = engine.raw_connection() 
    cursor = connection.cursor()
    # null value become ''
    cursor.copy_from(output,table_name,null='')
    connection.commit()
    cursor.close()

use the function df2db to insert a df to an exists table, as the cols of the table and the df's columns should be the same.

from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://user:psw@localhost:5432/dbname')
df2db(df, table_name, engine)