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.
