How to use .env file with a json containing a backslash?

Question

Background, I have a MSSQL server database that uses a specific instances, hence the connect string/engine to this database would look like this:

Engine(mssql+pyodbc://User:Password@servername\instance,5555/database?driver=ODBC+Driver+17+for+SQL+Server)

Note the single back slash between servername and instance.

My testing, with both as a string and a json types:

Env File 1:

myvar = '[{"A":"123","B":"456", "C":"A\B"}]'
myvar2 = abc
myvar3 = [{"A":"123","B":"456", "C":"A\B"}]

main.py File:

from dotenv import load_dotenv
import os
import json

if __name__ == '__main__':
    load_dotenv('.env', override=True)
    myv2 = os.getenv('myvar2')
    myv = os.getenv('myvar')
    myv3 = os.getenv('myvar3')
    print(myv2)
    print(myv)
    print(myv3)
    print('Json Outputs')
    jmyv3 = json.loads(myv3)
    print(f"{jmyv3=}")
    jmyv = json.loads(myv)
    print(f"{jmyv=}")

Generates an error Invalid \escape for both the string variable in .env and the json variable in .env

Now, let's modify .env file to this:

myvar = '[{"A":"123","B":"456", "C":"A\\B"}]'
myvar2 = abc
myvar3 = [{"A":"123","B":"456", "C":"A\\B"}]

The json variable returns but returns two back slashes and not one.

jmyv3=[{'A': '123', 'B': '456', 'C': 'A\\B'}]

And, the string version still errors with Invalid \escape

Now, for grins, I put three back slashes in .env

myvar = '[{"A":"123","B":"456", "C":"A\\\B"}]'
myvar2 = abc
myvar3 = [{"A":"123","B":"456", "C":"A\\\B"}]

This time, string returns but with two back slashes:

jmyv=[{'A': '123', 'B': '456', 'C': 'A\\B'}]

And, the json generates an error Invalid \escape

Is there anyway to get the 'C' part of the json string to just print a single back slash? Like this:

[{'A': '123', 'B': '456', 'C': 'A\B'}]

And, these variables are stored in Airflow with base64 encoding which my cause another layer of uncertainty with back slashes.

Answer 1

It is easy to get confused about the difference between the CONTENT of a string and the REPRESENTATION of that string. Observe the following:

import json

myvar = '[{"A":"123","B":"456", "C":"A\\\\B"}]'
print(myvar)
jmyv3 = json.loads(myvar)
print(f"{jmyv3=}")
print(jmyv3[0]['C'])

Output:

[{"A":"123","B":"456", "C":"A\\B"}]
jmyv3=[{'A': '123', 'B': '456', 'C': 'A\\B'}]
A\B

OK, let's break down what happened. First:

myvar = '[{"A":"123","B":"456", "C":"A\\\\B"}]'

That string only contains two backslashes. I have to type FOUR in order to make Python do what I want, but the string that is stored in myvar only has two backslashes. You can see that in the output.

Now, I load that string as JSON:

jmyv3 = json.loads(myvar)

The string literally has two backslashes, which is what JSON needs if we want the resulting data to have one backslash. So, when I print that one value, jmyv3[0]['C'], as you see THAT string only has one backslash.

So, the answer to your question depends on whether that file is being interpreted as JSON, or interpreted as Python code. It if is Python code, then you need 4 backslashes to produce 1 backslash.

Having said all that, assuming that is on Windows, EVERY Windows API will accept forward slashes anywhere it expect backslashes, so you could just skip all of this and use a forward slash.