Add CR & LF in all lines python

Question

first of all, thanks to read my post. I hope you guys can help me, I'm really new in Python, sorry maybe the answer is really easy.

I read several posts to add [CR][LN] in all lines but the main issue I have in my script ( I don't create that ), is the need to integrate [CR][LN] in all the lines.

At the moment the script only adds [LN] but not the [CR]. The script goes to SQL to extract some tables, convert the information to CSV ( at this moment the information maintains [CR][LN] ), and after that convert to JSON ( in this step only give me the [LN].

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 = 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)

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()

So, I don't know where I need to add this instruction.

Again thanks so much for all you guys always helping me !!!

Kind regards.

Answer 1

pandas.DataFrame.to_json uses the newline rules of the underlying file object when writing records. If you pass in a file name, pandas will open the file in the default "\n" newline mode. Alternately, you could open the file yourself, choosing the newline policy you want.

import pandas as pd

df = pd.DataFrame([[1,2,3],[4,5,6],[7,8,9]])
df.to_json(open("test.json", "w", newline="\r\n"), orient="records", lines=True)
print(open("test.json", "rb").read())

Output

b'{"0":1,"1":2,"2":3}\r\n{"0":4,"1":5,"2":6}\r\n{"0":7,"1":8,"2":9}'

(Note also that lines should be True or False, not bool - which happens to be "truthy" so it works, but not correct).