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

Question

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

Stack Overflow
Microsoft Word
PyScripter

Line breaks are not visible in:

SELECT        NAME        ,FROM_        ,TO_    FROM        USER1.ROAD

Notepad
MS Access >> SQL editor
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)?

maybe you can have a look at this link and use that for new lines in your code. That is a suggestion and not a tested answer, MS Access or ArcGIS applications might be displaying them the way they want. — Sudipta Mondal
– Sudipta Mondal, Commented May 9, 2017 at 14:26
Is the Python running on Linux, i.e. are the line breaks in the Python just a line feed LF and Oracle requires a carriage return as well (CRLF). — Ben
– Ben, Commented May 9, 2017 at 14:41
@Ben I've updated the question: I have a Python script that I run in PyScripter (Windows) that I use to execute SQL on an Oracle database. The python is running on Windows. I don't have any details about the Oracle Server, but my uneducated guess is that it's irrelevant. — User1974
– User1974, Commented May 9, 2017 at 14:44

Community · Accepted Answer · 2017-05-23 12:34:34Z

1

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}

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered May 9, 2017 at 20:04

User1974

3661 gold badge26 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related