DatabaseError: ',",\ with comma(,) write pandas dataframe to vertica using to_sql and vertica_python
BhoopeshSisoudiya
Community Edition User
I would like to write "data frame" to Vertica database using to_sql method. In python using vertica_python module. Following code below mentioned.
For data follow type1.t and type2.txt file
Type1 Records.
EmpId EmpAddress1 EmpAddress2 Country
11000956 "JIBAN DEEP", SOUTH SARANIA GANDHI MANDAP LINK ROAD 1
11001193 F-304, "MANTRI PARADISE" BANERGHATTA ROAD, 1
11005604 W/O SHRI A.K. SINGH,"ANJANA" V.T.L. DOHI COLONY REWA 1
11006411 PLOT NO= 370,"B"SEC SARSWATI APPT 304,SARBDHARM COLONY 1
11010033 SHANKER CLINIC,NEW ASHOK NAGAR"A" SALEM TAPRI, P.O NETA JI NAGAR 1
11010781 203 " SAMMAN APTT" G-23, M.I.G COLONY (R.S.S.NAGAR) 1
11111110 75, " GOYAL KUNJ" NEAR RAILWAY STATION CROSSING, BAGRA DISTRICT - JALORE [ RAJASTHAN ] - 343025 1
12495786 A-1, "A" BLOCK, NEAR ONKARESHWER TEMPLE, CHANDRAVARDAI NAGAR, AJMER 1
14050362 BLOCK NO-30, "RELHA" SRI NAGAR SOCIETY, MADHURAM, NR.VAMTHLI ROAD. JUNAGADH 1
14091081 BAHARPARA, STREET NO-2, "RADHE KRISHMA" BUS STATION ROAD. MANAVADAR 1
Type2 Records.
EmpId EmpAddress1 EmpAddress2 Country
42866430 DODDA KAVALANE HOBLI\ NANJANGUDU TALUK DASANURU 1
44178051 S/O VISHNU SAHU,MAHTO TOLA,KATAMKULI,RANCHI\ S/O VISHNU SAHU,MAHTO TOLA,KATAMKULI,RANCHI- 1
52606561 BARANA DARBHANGS BIHAR\ BARANA DARBHANGS BIHAR 1
59765287 W/OCHANDRASERHNO.V-127ST.NO.21VIJAYPARKDELHI\ 1
60165374 WARD NO 20 MAHINDRA FARM \ CHHACHHRAULI 1
62226510 W-49 GALI NO-2 GOVIND GALI BABARPUR\ 1
62459102 VILL- BHIV MANDER GALI KHAJPURA P\ PATNA 1
63033106 NEAR POWER COLONY ,NIRJULI COMPLEX\ 1
63766234 2/10, VELLARIKAMBAI, JACKANARAI [PO]\ KUNJAPANAI, THE NILGIRIS 1
64254224 VOP KANG TEH KHADUR SAHIB \ TARN TARAN 1
Table Create Statement
CREATE TABLE EmpTable (
EmpId integer DEFAULT NULL,
EmpAddress1 varchar(100) DEFAULT NULL,
EmpAddress2 varchar(100) DEFAULT NULL,
Country varchar(50) DEFAULT NULL
);
In "assd.py" file Without step 2 ,setp 3 is not workig.
requiremnet --
import pandas as pd
import vertica_python
import sqlalchemy as sa
STEP 1.
verticaconn = sa.create_engine('vertica+vertica_python://dbadmin:user@ip:port/table')
sa.create_engine('vertica+vertica_python://dbadmin:user@ip:port/table')
df = pd.read_csv('type1.txt',delimiter='\t')
'''
Due to errors we did this pactch
To test : please comment for loop below
'''
STEP 2.
for col in df.select_dtypes('O'):
try:
df[col] = df[col].str.replace('"',"'")
df[col] = df[col].apply(lambda x : str(x).strip().strip("\") if x is not None and x.strip().endswith("\") else x)
except Exception as e:
print(e)
STEP 3.
df.to_sql(con=verticaconn,name='EmpTable',if_exists='append',index=False)
Please help, how to write these types of records in vertica by to_sql function.
Answers
Hi, I will try to replicate this issue. However, the records are missing comma delimiter in some cases, probably due to HTML formatting on the form. Could you attach the data files type1.txt and type2.txt instead of pasting, and also if possible attach the Python script as a text file? Thanks!
Sure,
"assd.txt" file is "assd.py" becuase .py is not showing after attached.
Hi, there appears to be an issue with SQLAlchemy, or more likely the VerticaDialect, where the trailing backslash is parsed incorrectly. For example, the following line
is sent to Vertica as
This is an issue because \" is an escape character, so Vertica can't find the matching quote at end of field because it appears to be escaped due to SQLAlchemy process.
Unfortunately this means the for loop workaround is necessary, at least to remove backslash at the end of field.
I am doing some other work on SQLAlchemy so I will look into fixing this in the code. However, it will probably be into the new calendar year before we publish a new release.
Hi Bryan_H, I don't have privilege to remove backslash at the end of field.
Hi Bryan_H, I don't know why this value "# pic:imex@unilogistics.vn" changed in "# PIC'I'@UNILOGISTICS.VN". If you fixing any issues please help me.
It looks like the forum changed the string. What is the issue: lower case changing to upper case, characters added/removed, or something else?