Integrate strip or trim in python script

Question

Thanks so much for reading my post, I hope someone can help me with that, I have a script to connect to my database and extract several tables and convert them to JSONL format ( all with pandas ), my script:

import pyodbc
import fileinput
import csv
import pandas as pd
import json
import os
import sys

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=TEST;'
                      'UID=test;'
                      'PWD=12345;'
                      'Database=TEST;'
                      'Trusted_Connection=no;')
cursor = conn.cursor()

query = "SELECT * FROM placeholder"


with open(r"D:\Test.txt") as file:
    lines = file.readlines()
    print(lines)


for user_input in lines:

    result = query.replace("placeholder", user_input)
    print(result)
    sql_query = pd.read_sql(result,conn)
    df = pd.DataFrame(sql_query)
    user_inputs =  user_input.strip("\n")
    filename = os.path.join('D:\\', user_inputs + '.csv')
    df.to_csv (filename, index = False, encoding='utf-8', sep = '~', quotechar = "`", quoting=csv.QUOTE_ALL)
    print(filename)
    filename_json = os.path.join('D:\\', user_inputs + '.jsonl')
    csvFilePath = (filename)
    jsonFilePath = (filename_json)
    print(filename_json)
    df_o = df.astype(str)
    df_o.to_json(filename_json, orient = "records",  lines = bool, date_format = "iso", double_precision = 15, force_ascii = False, date_unit = 'ms', default_handler = str)

dir_name = "D:\\"
test = os.listdir(dir_name)

for item in test:
    if item.endswith(".csv"):
        os.remove(os.path.join(dir_name, item)) 

cursor.close()
conn.close()

My script works fine, the issue I have is having much black spaces in the results, like:

{"SucCod":1,"SucNom":"CENTRAL                  ","SucUsrMod":"aleos     ","SucFecMod":1537920000000,"SucHorMod":"11:30:21","SucTip":"S","SucBocFac":4,"SucCal":"SUTH               ","SucNro":1524,"SucPis":6,"SucDto":"    ","SucCarTel":"55   ","SucTel":52001}

I want a use a strip or trim function to delete the blank space.

Can you help me to know who I can integrate that with ???

Thanks so much.

Kind regards !!!

Does this answer your question? [How do I trim whitespace?](https://stackoverflow.com/questions/1185524/how-do-i-trim-whitespace) — theherk, Apr 22 '22 at 15:10
Actually, I read that, but my problem is who integrate that into my script. This is the issue I facing ... — Carlos, Apr 22 '22 at 15:16
Here is another answer that may help. [Strip / trim all strings or a dataframe](https://stackoverflow.com/a/40950485/2081835) — theherk, Apr 22 '22 at 15:16
Does this answer your question? [Strip / trim all strings of a dataframe](https://stackoverflow.com/questions/40950310/strip-trim-all-strings-of-a-dataframe) — Pranav Hosangadi, Apr 22 '22 at 15:38

mosc9575 · Answer 1 · 2022-04-22T15:36:02.603

0

I don't know where in your script the whitespaces are added, but you can trim the result afterwards.

result = {k: v.rstrip() if isinstance(v, str) else v for k, v in result.items()}
>>> result
{'SucCod': 1,
 'SucNom': 'CENTRAL',
 'SucUsrMod': 'aleos',
 'SucFecMod': 1537920000000,
 'SucHorMod': '11:30:21',
 'SucTip': 'S',
 'SucBocFac': 4,
 'SucCal': 'SUTH',
 'SucNro': 1524,
 'SucPis': 6,
 'SucDto': '',
 'SucCarTel': '55',
 'SucTel': 52001}

edited Apr 22 '22 at 15:36

answered Apr 22 '22 at 15:28

mosc9575

5,618
2
9
32

1

I think what you want here is `result = {k: v.rstrip() if isinstance(v, str) else v for k, v in result.items()}`. No reason to add other nested dictionary, right? – theherk Apr 22 '22 at 15:33
I'm sorry, you're right I forgot to put that part, the space is from the origin, I mean directly from the database, and appears in the first extraction "CSV". So I think is better if I integrate that from that right ??? Kind regards ... – Carlos Apr 22 '22 at 15:33
@theherk Thanks to your comment I updated my post. – mosc9575 Apr 22 '22 at 15:36

score 0 · Accepted Answer · answered Apr 22 '22 at 15:45

You should be able to do this right between two of your lines:

    df_o = df.astype(str)
    df_o = df_o.applymap(lambda x: x.strip() if isinstance(x, str) else x)
    df_o.to_json(filename_json, orient = "records",  lines = bool, date_format = "iso", double_precision = 15, force_ascii = False, date_unit = 'ms', default_handler = str)

Or wherever you want to do this stripping. Note that the other answer, to operate directly on a dictionary is valid too.

Integrate strip or trim in python script

2 Answers2