def test__exec_ddl_already_exists(self):
migrator = self.get_migrator(clever_ddls=True)
migrator._commit = True
migrator._target = Mock()
migrator._target.execute.side_effect = pyodbc.ProgrammingError(
'42601', 'message')
migrator._exec_ddl('DDL')
def f(*args, **kw):
if not self._connected:
raise pyodbc.ProgrammingError("Not connected")
try:
table = kw['table']
except KeyError:
raise TypeError(
'no table name provided, a table name must be provided as keyword argument'
)
where = kw.get('where', None)
groupBy = kw.get('groupBy', None)
restQuery = kw.get('restQuery', '')
qis = kw.get('quotedIdentifiers', qi)
returnSQL = kw.get('returnSQL', False)
for k in ('table', 'where', 'groupBy', 'restQuery',
'quotedIdentifiers', 'returnSQL'):
if k in kw:
del kw[k]
funargs = [self._q(n, qis) for n in args]
whereSQL = ""
if where:
whereSQL = "WHERE %s" % where
groupBySQL = ""
if groupBy:
groupBySQL = "GROUP BY %s" % self._q(groupBy, qis)
code = "SELECT * FROM (SELECT %s(%s) FROM %s %s %s) %s" % \
(scriptName, ", ".join(funargs), self._q(table, qis), whereSQL, groupBySQL, str(restQuery))
if returnSQL:
return '(%s)' % code
return self.readData(code, **kw)
def run_without_transaction(self, query, debug=False):
try:
import pyodbc
except ImportError:
print("please, install pyodbc")
raise
from .print9 import Print
if debug:
Print('SQL settings:"' + self.connect_to_sql_string + '"')
Print("Trying connect to SQL")
con = pyodbc.connect(self.connect_to_sql_string, autocommit=True)
cur = con.cursor()
if debug:
Print("Successful connection to SQL")
Print(f"Run without transaction: '{query}'")
try:
cur.execute(query)
except pyodbc.ProgrammingError as e:
from .const9 import newline
raise pyodbc.ProgrammingError(
f"self.connect_to_sql_string:'{self.connect_to_sql_string}{newline}query:{query}{newline}{e}"
)
while cur.nextset():
pass
if debug:
Print(f"End '{query}'")
Print("Try to close connection")
cur.close()
con.close()
if debug:
Print("Close connection finished")
def execute(self, sqlText, *args):
"""Executes a single SQL statement
Note:
This method is to execute a single SQL statement and retrieving the result. If you try
to execute more than one statement use .addToBuffer() and .executeBuffer() instead.
Args:
sqlText: The SQL statement which should be executed on the DB instance
*args: All variables which are necessary to execute a prepared statement;
Returns:
None: If no result is present
List: A list of all result rows
"""
cursor = self.__conn.cursor()
result = []
exe = cursor.execute(sqlText, *args)
if exe and cursor.rowcount > 0:
try:
fetched = exe.fetchall()
if fetched:
for row in fetched:
result.append(row)
except pyodbc.ProgrammingError as e:
if not "No results." in str(e):
raise pyodbc.ProgrammingError(e)
return result
return None
def test__exec_ddl_other_error(self):
migrator = self.get_migrator(clever_ddls=True)
migrator._commit = True
migrator._target = Mock()
migrator._target.execute.side_effect = pyodbc.ProgrammingError(
'42', 'message')
self.assertRaises(pyodbc.ProgrammingError, migrator._exec_ddl, 'DDL')
def test_migrate_data_error(self, target, source):
migrator = self.get_migrator()
migrator._connection_type = Mock()
migrator._connection_details = Mock()
migrator._get_table_list = Mock(return_value=['s.t'])
migrator._migrate_table = Mock()
migrator._migrate_table.side_effect = pyodbc.ProgrammingError(42, 'm')
migrator.migrate_data('')
self.assertEqual(migrator.migrate_data(''), None)
def get_out_arg(results, out_arg):
""" Returns the out arguments from the result sets from a SP call. """
out_arg = out_arg.lower()
results_count = len(results)
if results_count < 1 or out_arg not in results[results_count - 1][0]:
raise pyodbc.ProgrammingError(
f'The out argument "{out_arg}" was not captured from call to the stored procedure.'
)
return results[results_count - 1][0][out_arg]
def close(self):
"""Closes the underlying pyodbc.Connection object and stops
any implicitly started output service."""
if not self._connected:
raise pyodbc.ProgrammingError("Not connected")
self._connected = False
try:
self.odbc.close()
finally:
self._stopOutputService()
def query(self, query, debug=False):
try:
import pyodbc
except ImportError:
print("please, install pyodbc")
raise
from .print9 import Print
if debug:
Print('SQL settings:"' + self.connect_to_sql_string + '"')
Print("Trying connect to SQL")
con = pyodbc.connect(self.connect_to_sql_string, autocommit=True)
cur = con.cursor()
if debug:
Print("Successful connection to SQL")
Print(f"Run query: '{query}'")
out = []
def get_rows(cursor):
output = []
for row in cursor.fetchall():
output.append(row)
return output
try:
cur.execute(query)
except pyodbc.ProgrammingError as e:
from .const9 import newline
raise pyodbc.ProgrammingError(
f"self.connect_to_sql_string:'{self.connect_to_sql_string}{newline}query:{query}{newline}{e}"
)
out.append(get_rows(cur))
while cur.nextset():
out.append(get_rows(cur))
if debug:
Print(f"End '{query}'")
Print("Try to close connection")
cur.close()
con.close()
if debug:
Print("Close connection finished")
if len(out) == 1:
return out[0]
return out
def get_data(self, sql):
"""
Execute an SQL string against this class' database that should
result in a recordset. Return the results as a list of pyodbc
records and return the pyodbc cursor description.
Parameters
----------
sql : str
The SQL string to execute
Returns
-------
records: list of pyodbc records
data resulting from execution of sql string
description: pyodbc cursor description
field names and data types
"""
db = pyodbc.connect('DSN=' + self.dsn)
cursor = db.cursor()
try:
records = cursor.execute(sql).fetchall()
except pyodbc.DataError:
err_msg = 'caused by the sql statement "' + sql + '"'
raise pyodbc.DataError(err_msg)
except pyodbc.ProgrammingError:
err_msg = 'caused by the sql statement "' + sql + '"'
raise pyodbc.ProgrammingError(err_msg)
except AttributeError:
err_msg = 'caused by the sql statement "' + sql + '"'
raise AttributeError(err_msg)
except pyodbc.DataError:
err_msg = 'caused by the sql statement "' + sql + '"'
raise pyodbc.DataError(err_msg)
except Exception:
err_msg = 'caused by the sql statement "' + sql + '"'
raise Exception(err_msg)
finally:
db.close()
# this section commented out since we discovered there are scenarios
# where we want to return no records, and it is not an error
#if len(records) < 1:
# err_msg = 'The sql statement "' + sql + '" returned no records'
# raise IndexError(err_msg)
return records, cursor.description
cursor.close()
def _startOutputService(self):
"""Start service for EXASolution UDF scripts' output
After the service is running, the createScript function
produces additional code in scripts, which redirects the
stdout and stderr of a stript to this service.
The output of this service is the local stdout.
"""
if not self._connected: raise pyodbc.ProgrammingError("Not connected")
self._stopOutputService()
self._outputService = ScriptOutputThread()
self._outputService.fileObject = self.outputFileObject
self._outputService.finished = False
self._outputService.serverAddress = self.clientAddress
self._outputService.init()
self.clientAddress = self._outputService.serverAddress
self._outputService.start()
def execute_scheduled_tasks_sp(*args,
out_arg='sp_status_code',
only_first=False):
"""
Helper function to execute the MWH.MANAGE_SCHEDULE_TASK_JOBS stored procedure.
:return: Stored procedure result sets and out argument
:rtype: list
"""
results = execute_sp_with_required_in_args(*args,
sp_args_length=11,
out_arg=out_arg)
status_code = get_out_arg(results, out_arg)
if status_code > -1:
raise pyodbc.ProgrammingError(
f'Stored Procedure call to "{args[0]}" failed.', status_code)
result = get_sp_result_set(results, 0, out_arg)
if not result:
return None if only_first else []
return result if not only_first else result[0]
def createScript(self,
name=None,
env=None,
initFunction=None,
cleanFunction=None,
replaceScript=True,
quotedIdentifiers=False,
inType=SET,
inArgs=None,
outType=EMITS,
outArgs=None):
"""Converts a Python function to EXASolution UDF script
This function decorator converts a regular python function to
an EXASolution UDF script, which is created in connected
EXASolution RDMBS. The modified function runs then in the
EXASolution RDMBS context in multiple parallel instances,
therefore the function has no access to local context of
E.connect. To import modules or prepare the context for the
function please set the initFunction and do it there.
It has following keyword arguments:
name
The script name to use in the database, default is the
python name of the function
env
A dictionary with variable names as keys and variable
content as values, which should be defined when the script
is started
initFunction
A function which is called on initialization. All contex
changes, which should be available in the modified
function need to be done here and defined as global:
def myInit():
global ftplib
import ftplib
cleanFunction
This function will be called to clean up the context of
modified function, e.g. close connections or similar
replaceScript = True
If this keyword argument is True (default) then the script
will be replaced on EXASolution side if already exists
quotedIdentifiers = False
If this keyword argument is True, then all identifiers in
generated SQL will be quoted
inType = SET
The type of EXASolution UDF script, please refer the
EXASolution documentation
inArgs = []
The input argumens as list of (name, type) tuples
outType = EMITS
The type of EXASolution UDF script, please refer the
EXASolution documentation
outArgs = []
Output arguments of the EXASolution UDF script. If
outType==EMITS, then the same format as with inArgs, but
if outType==RETURNS, then only the SQL type name
The modified function has then other arguments:
fun(*args, # args should be a list of strings and need to
# correspond to inArgs
table, # name of input table, is required
where = None, # the WHERE part of SQL
groupBy = None, # the GROUP BY part of SQL
restQuery = '', # rest of the QUERY (e.g. ORDER BY)
quotedIdentifiers = False,/
returnSQL = False, # on execute return only the SQL text
**kw) # keywords to pass to readData
If the modified function is called, then a query in the
EXASolution DBMS is executed which applys the created script
on the given table. The result is then returned in the same
format as with readData.
"""
if sys.version_info[0:2] != expected_version:
raise RuntimeError('createScript requires Python %s'.format(
'.'.join(map(str, expected_version))))
if inArgs is None:
inArgs = []
if outArgs is None:
outArgs = []
if not self._connected:
raise pyodbc.ProgrammingError("Not connected")
qi = quotedIdentifiers
def createPythonScript(function):
if name is None:
if self.scriptSchema is None:
scriptName = get_func_name(function)
else:
scriptName = "%s.%s" % (self.scriptSchema,
get_func_name(function))
else:
scriptName = name
if qi:
scriptName = '"%s"' % scriptName
scriptCode = [
"# AUTO GENERATED CODE FROM EXASOLUTION PYTHON PACKAGE",
"import marshal, types, sys, socket, time, zlib"
]
if env is not None:
scriptCode.append(
"env = marshal.loads(zlib.decompress(%s))" %
repr(zlib.compress(str(marshal.dumps(env),
9))).encode('utf-8'))
code_str = repr(
zlib.compress(marshal.dumps(get_func_code(function)), 9))
scriptCode.append(
"run = types.FunctionType(marshal.loads(zlib.decompress((%s))), globals(), %s)"
% (code_str, repr(get_func_name(function))))
if cleanFunction is not None:
code_str = repr(
zlib.compress(
str(marshal.dumps(get_func_code(cleanFunction))), 9))
scriptCode.append(
"cleanup = types.FunctionType(marshal.loads(zlib.decompress(%s)), globals(), %s)"
% (code_str, repr(get_func_name(cleanFunction))))
if self._outputService is not None or self.externalClient:
serverAddress = self.clientAddress
if self._outputService is not None:
serverAddress = self._outputService.serverAddress
scriptCode.append("""# OUTPUT REDIRECTION
class activate_remote_output:
def __init__(self, address):
self.s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
self.s.connect(address)
sys.stdout = sys.stderr = self
def write(self, data):
return self.s.sendall(data)
def close(self):
self.s.close()
activate_remote_output(%s)""" % repr(serverAddress))
if initFunction is not None:
scriptCode.append(
"types.FunctionType(marshal.loads(%s), globals(), %s)()" %
(repr(marshal.dumps(get_func_code(initFunction))),
repr(get_func_name(initFunction))))
scriptCode = '\n'.join(scriptCode)
scriptReplace = ""
if replaceScript:
scriptReplace = "OR REPLACE"
scriptInType = "SET"
if inType == SCALAR:
scriptInType = "SCALAR"
if not isinstance(inArgs, basestring):
scriptInArgs = [
"%s %s" % (self._q(n, qi), t) for n, t in inArgs
]
scriptInArgs = ', '.join(scriptInArgs)
else:
scriptInArgs = inArgs
if outType == RETURNS:
if not isinstance(outArgs, basestring):
raise TypeError(
"outArgs need te be a string for outType == RETURNS")
scriptOutArgs = outArgs
else:
if not isinstance(outArgs, basestring):
scriptOutArgs = [
"%s %s" % (self._q(n, qi), t) for n, t in outArgs
]
scriptOutArgs = '(' + ", ".join(scriptOutArgs) + ')'
else:
scriptOutArgs = '(' + outArgs + ')'
if len(scriptOutArgs) == 2: # just empty brackets
raise RuntimeError("One or more output arguments required")
scriptOutType = "EMITS"
if outType == RETURNS:
scriptOutType = "RETURNS"
sqlCode = 'CREATE %s PYTHON %s SCRIPT %s (%s) %s %s AS\n%s\n' % \
(scriptReplace, scriptInType, scriptName, scriptInArgs, scriptOutType, scriptOutArgs, scriptCode)
self.odbc.execute(sqlCode)
def f(*args, **kw):
if not self._connected:
raise pyodbc.ProgrammingError("Not connected")
try:
table = kw['table']
except KeyError:
raise TypeError(
'no table name provided, a table name must be provided as keyword argument'
)
where = kw.get('where', None)
groupBy = kw.get('groupBy', None)
restQuery = kw.get('restQuery', '')
qis = kw.get('quotedIdentifiers', qi)
returnSQL = kw.get('returnSQL', False)
for k in ('table', 'where', 'groupBy', 'restQuery',
'quotedIdentifiers', 'returnSQL'):
if k in kw:
del kw[k]
funargs = [self._q(n, qis) for n in args]
whereSQL = ""
if where:
whereSQL = "WHERE %s" % where
groupBySQL = ""
if groupBy:
groupBySQL = "GROUP BY %s" % self._q(groupBy, qis)
code = "SELECT * FROM (SELECT %s(%s) FROM %s %s %s) %s" % \
(scriptName, ", ".join(funargs), self._q(table, qis), whereSQL, groupBySQL, str(restQuery))
if returnSQL:
return '(%s)' % code
return self.readData(code, **kw)
set_func_name(f, get_func_name(function))
return f
return createPythonScript
def writeData(self,
data,
table,
columnNames=None,
quotedIdentifiers=False,
writeCallback=None,
**kw):
"""Import data to a table in EXASolution DBMS
Per default it imports the given pandas data frame to the
given table. If a writeCallback is specified, then this
function is called with given data frame and a file object,
where the CSV file should be written. The format of CSV should
be csv.excel dialect.
"""
if not self._connected:
raise pyodbc.ProgrammingError("Not connected")
if writeCallback is None:
if self.csvIsDefault:
writeCallback = csvWriteCallback
else:
writeCallback = pandasWriteCallback
odbc = self.odbc
self.odbc = None
try:
srv = TunneledTCPServer(self.serverAddress, HTTPIOHandler)
srv.pipeInFd, srv.pipeOutFd = os.pipe()
srv.outputMode = False
srv.doneEvent = threading.Event()
srv.startedEvent = threading.Event()
srv.error = None
srv.pipeIn, srv.pipeOut = os.fdopen(srv.pipeInFd), os.fdopen(
srv.pipeOutFd, 'w')
s = HTTPIOServerThread()
s.srv = srv
srv.serverThread = s
q = HTTPImportQueryThread()
q.srv = srv
srv.queryThread = q
q.tableName = self._q(table, quotedIdentifiers)
q.columnNames = None
if columnNames is not None:
q.columnNames = [
self._q(c, quotedIdentifiers) for c in columnNames
]
q.odbc = odbc
s.start()
q.start()
for k in ('columnNames', 'quotedIdentifiers', 'writeCallback'):
if k in kw:
del kw[k]
try:
try:
while not srv.startedEvent.wait(1):
if srv.error is not None:
srv.doneEvent.set()
raise RuntimeError("Server error")
writeCallback(data, srv.pipeOut, **kw)
except Exception as err:
if srv.error is not None:
raise srv.error
raise err
finally:
try:
srv.pipeOut.close()
except:
pass
srv.doneEvent.wait()
srv.server_close()
s.join()
q.join()
finally:
self.odbc = odbc
if srv.error is not None:
raise srv.error
def readData(self, sqlCommand, readCallback=None, **kw):
"""Execute a DQL statement and returns the result
This is a optimized version of pyodbc.Connection.execute
function. ReadData returns per default a pandas data frame
or any other data, if a different readCallback was specified.
readCallback
A function, which is called with the file object contained
the query result as CSV and all keyword arguments given to
readData. The returned data will be returned from
readData function.
"""
if not self._connected:
raise pyodbc.ProgrammingError("Not connected")
if readCallback is None:
if self.csvIsDefault:
readCallback = csvReadCallback
else:
readCallback = pandasReadCallback
odbc = self.odbc
self.odbc = None # during command execution is odbc not usable
try:
srv = TunneledTCPServer(self.serverAddress, HTTPIOHandler)
srv.pipeInFd, srv.pipeOutFd = os.pipe()
srv.outputMode = True
srv.error, srv.pipeIn, srv.pipeOut = None, os.fdopen(
srv.pipeInFd), os.fdopen(srv.pipeOutFd, 'w')
s = HTTPIOServerThread()
s.srv = srv
srv.serverThread = s
q = HTTPExportQueryThread()
q.srv = srv
srv.queryThread = q
q.sqlCommand = sqlCommand
q.odbc = odbc
s.start()
q.start()
try:
try:
ret = readCallback(s.srv.pipeIn, **kw)
except Exception as err:
if srv.error is not None:
raise srv.error
raise err
finally:
srv.server_close()
try:
srv.pipeIn.close()
srv.pipeOut.close()
except:
pass
q.join()
s.join()
finally:
self.odbc = odbc
if srv.error is not None:
raise srv.error
return ret
def __enter__(self):
"""Allows to use E.connect in "with" statements"""
if not self._connected:
raise pyodbc.ProgrammingError("Not connected")
return self
def test_close_connection_exception(self):
with patch("pyodbc.connect") as mock_connect:
mock_connect.close.side_effect = pyodbc.ProgrammingError("Connection already closed")
close_mssql_connection(logger=self.logger, conn=mock_connect)
mock_connect.close.assert_called()
