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()