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 !!!
CodePudding user response:
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.
CodePudding user response:
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}