def run_query(self, query, user):
connection = None
try:
server = self.configuration.get("server")
user = self.configuration.get("user", "")
password = self.configuration.get("password", "")
db = self.configuration["db"]
port = self.configuration.get("port", 1433)
charset = self.configuration.get("charset", "UTF-8")
connection_string_fmt = "DRIVER={{ODBC Driver 17 for SQL Server}};PORT={};SERVER={};DATABASE={};UID={};PWD={};ApplicationIntent=ReadOnly"
connection_string = connection_string_fmt.format(
port, server, db, user, password)
if self.configuration.get("use_ssl", False):
connection_string += ";Encrypt=YES"
if not self.configuration.get("verify_ssl"):
connection_string += ";TrustServerCertificate=YES"
connection = pyodbc.connect(connection_string)
cursor = connection.cursor()
logger.debug("SQLServerODBC running query: %s", query)
cursor.execute(query)
data = cursor.fetchall()
if cursor.description is not None:
columns = self.fetch_columns([(i[0], types_map.get(i[1], None))
for i in cursor.description])
rows = [
dict(zip((column["name"] for column in columns), row))
for row in data
]
data = {"columns": columns, "rows": rows}
json_data = json_dumps(data)
error = None
else:
error = "No data was returned."
json_data = None
cursor.close()
except pyodbc.Error as e:
try:
# Query errors are at `args[1]`
error = e.args[1]
except IndexError:
# Connection errors are `args[0][1]`
error = e.args[0][1]
json_data = None
except (KeyboardInterrupt, JobTimeoutException):
connection.cancel()
raise
finally:
if connection:
connection.close()
return json_data, error
def run_query(self, query, user):
connection = None
try:
server = self.configuration.get('server', '')
user = self.configuration.get('user', '')
password = self.configuration.get('password', '')
db = self.configuration['db']
port = self.configuration.get('port', 1433)
charset = self.configuration.get('charset', 'UTF-8')
driver = self.configuration.get('driver',
'{ODBC Driver 13 for SQL Server}')
connection_string_fmt = 'DRIVER={};PORT={};SERVER={};DATABASE={};UID={};PWD={}'
connection_string = connection_string_fmt.format(
driver, port, server, db, user, password)
connection = pyodbc.connect(connection_string)
cursor = connection.cursor()
logger.debug("SQLServerODBC running query: %s", query)
cursor.execute(query)
data = cursor.fetchall()
if cursor.description is not None:
columns = self.fetch_columns([(i[0], types_map.get(i[1], None))
for i in cursor.description])
rows = [
dict(zip((c['name'] for c in columns), row))
for row in data
]
data = {'columns': columns, 'rows': rows}
json_data = json.dumps(data, cls=MSSQLJSONEncoder)
error = None
else:
error = "No data was returned."
json_data = None
cursor.close()
except pyodbc.Error as e:
try:
# Query errors are at `args[1]`
error = e.args[1]
except IndexError:
# Connection errors are `args[0][1]`
error = e.args[0][1]
json_data = None
except KeyboardInterrupt:
connection.cancel()
error = "Query cancelled by user."
json_data = None
except Exception as e:
raise sys.exc_info()[1], None, sys.exc_info()[2]
finally:
if connection:
connection.close()
return json_data, error
def run_query(self, query, user):
connection = None
try:
server = self.configuration.get('server', '')
user = self.configuration.get('user', '')
password = self.configuration.get('password', '')
db = self.configuration['db']
port = self.configuration.get('port', 1433)
charset = self.configuration.get('charset', 'UTF-8')
driver = self.configuration.get('driver', '{ODBC Driver 13 for SQL Server}')
connection_string_fmt = 'DRIVER={};PORT={};SERVER={};DATABASE={};UID={};PWD={}'
connection_string = connection_string_fmt.format(driver,
port,
server,
db,
user,
password)
connection = pyodbc.connect(connection_string)
cursor = connection.cursor()
logger.debug("SQLServerODBC running query: %s", query)
cursor.execute(query)
data = cursor.fetchall()
if cursor.description is not None:
columns = self.fetch_columns([(i[0], types_map.get(i[1], None)) for i in cursor.description])
rows = [dict(zip((c['name'] for c in columns), row)) for row in data]
data = {'columns': columns, 'rows': rows}
json_data = json.dumps(data, cls=MSSQLJSONEncoder)
error = None
else:
error = "No data was returned."
json_data = None
cursor.close()
except pyodbc.Error as e:
try:
# Query errors are at `args[1]`
error = e.args[1]
except IndexError:
# Connection errors are `args[0][1]`
error = e.args[0][1]
json_data = None
except KeyboardInterrupt:
connection.cancel()
error = "Query cancelled by user."
json_data = None
except Exception as e:
raise sys.exc_info()[1], None, sys.exc_info()[2]
finally:
if connection:
connection.close()
return json_data, error
def run_query(self, query, user):
connection = None
try:
server = self.configuration.get("server", "")
user = self.configuration.get("user", "")
password = self.configuration.get("password", "")
db = self.configuration["db"]
port = self.configuration.get("port", 1433)
charset = self.configuration.get("charset", "UTF-8")
driver = self.configuration.get("driver",
"{ODBC Driver 13 for SQL Server}")
connection_string_fmt = (
"DRIVER={};PORT={};SERVER={};DATABASE={};UID={};PWD={}")
connection_string = connection_string_fmt.format(
driver, port, server, db, user, password)
connection = pyodbc.connect(connection_string)
cursor = connection.cursor()
logger.debug("SQLServerODBC running query: %s", query)
cursor.execute(query)
data = cursor.fetchall()
if cursor.description is not None:
columns = self.fetch_columns([(i[0], types_map.get(i[1], None))
for i in cursor.description])
rows = [
dict(zip((column["name"] for column in columns), row))
for row in data
]
data = {"columns": columns, "rows": rows}
json_data = json_dumps(data)
error = None
else:
error = "No data was returned."
json_data = None
cursor.close()
except pyodbc.Error as e:
try:
# Query errors are at `args[1]`
error = e.args[1]
except IndexError:
# Connection errors are `args[0][1]`
error = e.args[0][1]
json_data = None
except KeyboardInterrupt:
connection.cancel()
error = "Query cancelled by user."
json_data = None
finally:
if connection:
connection.close()
return json_data, error
