1
\$\begingroup\$

I have some Python code that runs as part of an Azure Synapse Analytics Apache Spark Notebook (or Synapse Notebook) and would like to add effective error handling. The code simply executes a given SQL script against the database. The code runs but I sometimes see errors like attempt to use closed connection. I would like to do the following:

  • Improve code that I wrote through peer review
  • Can I improve the error handling? eg pseudo-code if connection still open close connection
  • The code using SQL auth works. I would like to authenticate as the Managed Identity, I've tried using the object id of the MI in the connection string with Authentication=ActiveDirectoryMsi but it didn't work

Cell1 - parameters

pAccountName = 'someStorageAccount'
pContainerName = 'someContainer'
pRelativePath = '/raw/sql/some_sql_files/'
pFileName = 'someSQLscript.sql'

Cell 2 - main

import pyodbc
from pyspark import SparkFiles

try:
    # Add the file to the cluster so we can view it
    sqlFilepath = f"""abfss://{pContainerName}""" + "@" + f"""{pAccountName}.dfs.core.windows.net{pRelativePath}{pFileName}"""
    sc.addFile(sqlFilepath, False)


    # Open the file for reading
    with open(SparkFiles.getRootDirectory() + f'/{pFileName}', 'r') as f:
        lines = f.read()    ## read all text from a file into a string


    # Open the database connection
    conn = pyodbc.connect( 'DRIVER={ODBC Driver 17 for SQL Server};'
                        'SERVER=someServer.sql.azuresynapse.net;'
                        'DATABASE=someDatabase;UID=batchOnlyUser;'
                        'PWD=youWish;', autocommit = True )


    # Split the script into batches separated by "GO"
    for batch in lines.split("GO"):
        conn.execute(batch)  # execute the SQL statement

except:
    raise

finally:
    # Tidy up
    conn.close()
\$\endgroup\$
6
  • 1
    \$\begingroup\$ Can you show an example of what someSQLscript contains? \$\endgroup\$ Commented May 28, 2021 at 14:25
  • \$\begingroup\$ It could be any sql script really, so anything from just select @@version to big create table, insert statements etc \$\endgroup\$ Commented May 28, 2021 at 14:41
  • \$\begingroup\$ Welcome to the Code Review Community. We can review the code and make suggestions on how to improve it. We can't help you write new code or debug the code. Please read How do I ask a good question?. At least 2 things are making the question off-topic, the first is that your valid concerns about security are making you use generic names. The second is that Authentication=ActiveDirectoryMsi doesn't work. We can only review working code. \$\endgroup\$ Commented May 28, 2021 at 14:43
  • 1
    \$\begingroup\$ @pacmaninbw Per e.g. codereview.meta.stackexchange.com/a/696/25834 I don't see the generic names as a problem, and it's common practice to omit or change credentials; that on its own is not a reason for closure. \$\endgroup\$ Commented May 28, 2021 at 15:17
  • 1
    \$\begingroup\$ Welcome to Code Review! The current question title, asks for code to be written, which is off-topic for Code Review. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly. \$\endgroup\$ Commented May 28, 2021 at 16:38

1 Answer 1

Reset to default
3
\$\begingroup\$

Ignoring your closed-connection and managed-identity questions, since (a) I don't know how to answer them and (b) on their own they're off topic, there is still review to be done:

  • A Pythonic way to tell the standard library to format your URI instead of you doing it yourself is to call into urlunparse
  • Consider using pathlib
  • connect accepts kwargs as an alternative to the conn string, and the former will lay out your connection parameters more nicely
  • except / raise is redundant and should be deleted
  • Your try starts too early and should only start after the connection has been established
  • You're not reading a lines list; you're reading content, which would be a better variable name

Suggested

from pathlib import Path
from urllib.parse import urlunparse, ParseResult
import pyodbc
from pyspark import SparkFiles


pAccountName = 'someStorageAccount'
pContainerName = 'someContainer'
pRelativePath = '/raw/sql/some_sql_files/'
pFileName = 'someSQLscript.sql'

# Add the file to the cluster so we can view it
sql_filename = urlunparse(
    ParseResult(
        scheme='abfss',
        netloc=f'{pContainerName}@{pAccountName}.dfs.core.windows.net',
        path=f'{pRelativePath}{pFileName}',
        params=None, query=None, fragment=None,
    )
)

sc.addFile(sql_filename, False)

# Open the file for reading
sql_file_path = Path(SparkFiles.getRootDirectory()) / pFileName
with sql_file_path.open() as f:
    content = f.read()

# Open the database connection
conn = pyodbc.connect(
    DRIVER='{ODBC Driver 17 for SQL Server}',
    SERVER='someServer.sql.azuresynapse.net',
    DATABASE='someDatabase',
    UID='batchOnlyUser',
    PWD='youWish',
    autocommit=True,
)

try:
    # Split the script into batches separated by "GO"
    for batch in content.split("GO"):
        conn.execute(batch)  # execute the SQL statement
finally:
    conn.close()
\$\endgroup\$
0

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.