CODEWITHSUNDEEP
pythonsqloracle-databaseline-breakstext-formatting
User1974
User1974

Reputation: 396

Python script that executes SQL: Preserve line breaks in SQL text when creating a view

I have a Python script that I run in PyScripter (Windows) that I use to execute SQL on an Oracle database. The SQL creates a view.

import sys
import arcpy
arcpy.env.workspace = sys.path[0]
egdb_conn = arcpy.ArcSDESQLExecute(r"Database Connections\Connection1.sde")
sql_statement = """

CREATE OR REPLACE VIEW ROAD_VW AS
SELECT
    NAME
    ,FROM_
    ,TO_
FROM
    USER1.ROAD

"""
egdb_return = egdb_conn.execute(sql_statement)
print "Complete."

The script successfully creates a view in the database.

The problem is that the line breaks in the database view definition only seem to work (be visible) in certain programs, not others. If I access the view definition (in say - MS Access or ArcGIS Desktop), copy it, then paste it, this happens:

Line breaks are visible in:

SELECT
    NAME
    ,FROM_
    ,TO_
FROM
    USER1.ROAD
  1. Stack Overflow
  2. Microsoft Word
  3. PyScripter

Line breaks are not visible in:

SELECT        NAME        ,FROM_        ,TO_    FROM        USER1.ROAD
  1. Notepad
  2. MS Access >> SQL editor
  3. ArcGIS Desktop >> Create View

Can I format the SQL text so that, when it's executed to create a view, the view definition's line breaks are preserved (when the SQL definition text is used in other programs)?

Upvotes: 0

Views: 2590

Answers (1)

User1974
User1974

Reputation: 396

As hinted by @Sudipta Mondal, I need to convert \n to \r\n:

sql_statement = sql_statement.replace('\n', '\r\n')

Related resources:

Difference between \n and \r?

Replace \n with {something else}

Upvotes: 1

Related Questions