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 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')
    # 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
                can you share an example row of the csv that is causing this issue that would reproduce the error?
– lemonhead
                Jan 19, 2021 at 22:10
                sorry, still can't reproduce -- didn't have any issues with your example when I created table and file for myself using your example.  Please include a minimal, complete, reproducible example, including the command to create the table, and attach the csv file itself (or a stringio equivalent) -- your example as is also tab-delimited and only contains forward slash characters rather than backslash
– lemonhead
                Jan 20, 2021 at 2:42
    output = io.StringIO()
    # ignore the index
    # df_a.to_csv(output, sep='\t', index = False, header = False, quoting=csv.QUOTE_NONE)
    df_a.to_csv(output, sep='\t', 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 DataFrame to an exists table, as the cols of the table and the df's columns should be the same.
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://user:psw@localhost:5432/dbname')
df = pd.read_csv(file)
df2db(df, table_name, engine)
                This worked perfectly to address an issue in my tilde separated file where someone entered "A~B~K\~F~X" and now I have the input in my DB as "K\" which is a nonissue. Thanks!
– ctde
                Jun 14, 2021 at 16:44
        
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.