pandas - Python: Issues in retaining the exact format of string while uploading data from pipe delimited text to database

Question

I have a raw .txt file that contains the following fields and data

ID     MaterialCode    Vendor_Code
1      00033456        1990
2      4455600         02567

I am uploading this data to postgresql using sqlalchemy. I am using the following code:

conn = sqlalchemy.create_engine('conn_strng')
outputdype_app = sqlcol_append_stg(dataframe)
df = pd.read_csv(f,usecols=col_lst,sep='|',converters = 
     {'MaterialCode':'str','Vendor_Code':'str'},engine='python',encoding='iso-8859-1')
df.to_sql(schema=schema_name,name=table_name,con = conn,if_exists = 'append', method = 'multi', index=False,dtype=outputdype_app)

The outputdtype_app is being derived as follows:

def sqlcol_append_stg(dfparam):
    dtypedict_app = {}
    for i,j in zip(dfparam.columns, dfparam.dtypes):
        if "datetime" in str(j):
           dtypedict_app.update({i: sqlalchemy.types.DateTime()})
        else:
           dtypedict_app.update({i: sqlalchemy.types.VARCHAR()})
    return dtypedict_app

When I am seeing the DB, I see the 0 prefixes in MaterialCode and Vendor_Code are gone! I see the following:

ID     MaterialCode    Vendor_Code
1      33456           1990
2      4455600         2567

How can I fix this issue? I want exact same string in DB.

question from:https://stackoverflow.com/questions/65943771/python-issues-in-retaining-the-exact-format-of-string-while-uploading-data-from

Answer 1

Waitting for answers