def test_psql_quotation_marks(): # issue83 # regression: make sure plain $$ work t = sqlparse.split( """ CREATE OR REPLACE FUNCTION testfunc1(integer) RETURNS integer AS $$ .... $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION testfunc2(integer) RETURNS integer AS $$ .... $$ LANGUAGE plpgsql;""" ) assert len(t) == 2 # make sure $SOMETHING$ works too t = sqlparse.split( """ CREATE OR REPLACE FUNCTION testfunc1(integer) RETURNS integer AS $PROC_1$ .... $PROC_1$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION testfunc2(integer) RETURNS integer AS $PROC_2$ .... $PROC_2$ LANGUAGE plpgsql;""" ) assert len(t) == 2
def test_split_quotes_with_new_line(): stmts = sqlparse.split('select "foo\nbar"') assert len(stmts) == 1 assert stmts[0] == 'select "foo\nbar"' stmts = sqlparse.split("select 'foo\n\bar'") assert len(stmts) == 1 assert stmts[0] == "select 'foo\n\bar'"
def sqlRstToBlockSequence(sqlText): def __tryMatchCommentBlock(text): """ :param text: the text to parse :return: (CommentBlock|None,str). The block matched and the rest of the string or None and the text. """ m = re.match(BLOCK_COMMENT_BLOCK, text, re.MULTILINE | re.DOTALL) if m: return ( structure.BlockCommentBlock(m.group("comment"), m.group("before"), m.group("after")), m.group("rest"), ) m = re.match(LINES_COMMENT_BLOCK, text, re.MULTILINE | re.DOTALL) if m: return ( structure.LinesCommentBlock(m.group("comment"), m.group("before"), m.group("after")), m.group("rest"), ) return (None, text) def __doMatchSQLStatement(text): m = re.match(CREATE_TABLE_RE + ".*", text, re.IGNORECASE) if m: return structure.CreateTableStatementBlock(text, name=m.group("name")) m = re.match(CREATE_VIEW_RE + ".*", text, re.IGNORECASE) if m: return structure.CreateViewStatementBlock(text, name=m.group("name")) m = re.match(SELECT_RE + ".*", text, re.IGNORECASE) if m: return structure.SelectStatementBlock(text) return structure.UnknownStatementBlock(text) blocks = [] # Split the text in (comments* +sqlstatement restofline) segments. # Spaces+lines before comments are removed, spaces after blocks # are removed. some blank lines may disappear in the process. segments = sqlparse.split(sqlText) # Process each segment as they still may contains some comments # additionaly to one (or more?) sql statements for segment in segments: rest = segment # try to get all comments before the sql statement(s?) (comment_block, rest) = __tryMatchCommentBlock(rest) while comment_block is not None: # and not re.match('\s*',rest, re.MULTILINE): blocks.append(comment_block) (comment_block, rest) = __tryMatchCommentBlock(rest) # a priori there is just one remaining sql block, # but just to be sure... stmt_texts = sqlparse.split(rest) for stmt_text in stmt_texts: blocks.append(__doMatchSQLStatement(stmt_text)) return blocks
def find_statements(self): """Finds statements in current buffer. Returns: List of 2-tuples (start iter, end iter). """ buffer_ = self.get_buffer() buffer_start, buffer_end = buffer_.get_bounds() content = buffer_.get_text(buffer_start, buffer_end) iter_ = buffer_.get_start_iter() for stmt in sqlparse.split(content): if not stmt.strip(): continue # FIXME: Does not work if linebreaks in buffers are not '\n'. # sqlparse unifies them! bounds = iter_.forward_search(stmt.strip(), gtk.TEXT_SEARCH_TEXT_ONLY) if bounds is None: continue start, end = bounds yield start, end iter_ = end if not iter_: raise StopIteration raise StopIteration
def exec_impala_query_from_file(file_name): """Execute each query in an Impala query file individually""" if not os.path.exists(file_name): LOG.info("Error: File {0} not found".format(file_name)) return False LOG.info("Beginning execution of impala SQL: {0}".format(file_name)) is_success = True impala_client = ImpalaBeeswaxClient(options.impalad, use_kerberos=options.use_kerberos) output_file = file_name + ".log" with open(output_file, 'w') as out_file: try: impala_client.connect() with open(file_name, 'r+') as query_file: queries = sqlparse.split(query_file.read()) for query in queries: query = sqlparse.format(query.rstrip(';'), strip_comments=True) if query.strip() != "": result = impala_client.execute(query) out_file.write("{0}\n{1}\n".format(query, result)) except Exception as e: out_file.write("ERROR: {0}\n".format(query)) traceback.print_exc(file=out_file) is_success = False if is_success: LOG.info("Finished execution of impala SQL: {0}".format(file_name)) else: LOG.info("Error executing impala SQL: {0} See: {1}".format(file_name, \ output_file)) return is_success
def executeSQL(connection,command,parent_conn = None): ''' command is a sequence of SQL commands separated by ";" and possibly "\n" connection is a MySQLdb connection returns the output from the last command in the sequence ''' #split commands by \n commands = command.split("\n") #remove comments and whitespace" commands = [x for x in commands if x.lstrip()[0:2] != '--'] commands = [re.sub('\r','',x) for x in commands if x.lstrip() != '\r'] command = ' '.join(commands) statements = sqlparse.split(command) count = 0 for statement in statements: cur = connection.cursor() #make sure actually does something if sqlparse.parse(statement): cur.execute(statement) cur.close() connection.commit() if parent_conn: parent_conn.send(True) return True
def run(self, statement, pgspecial=None, exception_formatter=None, on_error_resume=False): """Execute the sql in the database and return the results. :param statement: A string containing one or more sql statements :param pgspecial: PGSpecial object :param exception_formatter: A callable that accepts an Exception and returns a formatted (title, rows, headers, status) tuple that can act as a query result. If an exception_formatter is not supplied, psycopg2 exceptions are always raised. :param on_error_resume: Bool. If true, queries following an exception (assuming exception_formatter has been supplied) continue to execute. :return: Generator yielding tuples containing (title, rows, headers, status, query, success) """ # Remove spaces and EOL statement = statement.strip() if not statement: # Empty string yield (None, None, None, None, statement, False) # Split the sql into separate queries and run each one. for sql in sqlparse.split(statement): # Remove spaces, eol and semi-colons. sql = sql.rstrip(';') try: if pgspecial: # First try to run each query as special _logger.debug('Trying a pgspecial command. sql: %r', sql) cur = self.conn.cursor() try: for result in pgspecial.execute(cur, sql): # e.g. execute_from_file already appends these if len(result) < 6: yield result + (sql, True) else: yield result continue except special.CommandNotFound: pass # Not a special command, so execute as normal sql yield self.execute_normal_sql(sql) + (sql, True) except psycopg2.DatabaseError as e: _logger.error("sql: %r, error: %r", sql, e) _logger.error("traceback: %r", traceback.format_exc()) if (isinstance(e, psycopg2.OperationalError) or not exception_formatter): # Always raise operational errors, regardless of on_error # specification raise yield None, None, None, exception_formatter(e), sql, False if not on_error_resume: break
def prepare_sql_script(self, sql, _allow_fallback=False): """ Takes a SQL script that may contain multiple lines and returns a list of statements to feed to successive cursor.execute() calls. Since few databases are able to process raw SQL scripts in a single cursor.execute() call and PEP 249 doesn't talk about this use case, the default implementation is conservative. """ # Remove _allow_fallback and keep only 'return ...' in Django 1.9. try: # This import must stay inside the method because it's optional. import sqlparse except ImportError: if _allow_fallback: # Without sqlparse, fall back to the legacy (and buggy) logic. warnings.warn( "Providing initial SQL data on a %s database will require " "sqlparse in Django 1.9." % self.connection.vendor, RemovedInDjango19Warning) from django.core.management.sql import _split_statements return _split_statements(sql) else: raise else: return [sqlparse.format(statement, strip_comments=True) for statement in sqlparse.split(sql) if statement]
def run(self, statement, pgspecial=None): """Execute the sql in the database and return the results. :param statement: A string containing one or more sql statements :param pgspecial: PGSpecial object :return: List of tuples containing (title, rows, headers, status) """ # Remove spaces and EOL statement = statement.strip() if not statement: # Empty string yield (None, None, None, None) # Split the sql into separate queries and run each one. for sql in sqlparse.split(statement): # Remove spaces, eol and semi-colons. sql = sql.rstrip(";") if pgspecial: # First try to run each query as special try: _logger.debug("Trying a pgspecial command. sql: %r", sql) cur = self.conn.cursor() for result in pgspecial.execute(cur, sql): yield result return except special.CommandNotFound: pass yield self.execute_normal_sql(sql)
def test_if_function(self): # see issue 33 # don't let IF as a function confuse the splitter sql = ('CREATE TEMPORARY TABLE tmp ' 'SELECT IF(a=1, a, b) AS o FROM one; ' 'SELECT t FROM two') stmts = sqlparse.split(sql) self.assertEqual(len(stmts), 2)
def run(conn, sql, config, user_namespace): if sql.strip(): for statement in sqlparse.split(sql): first_word = sql.strip().split()[0].lower() if first_word == 'begin': raise Exception("ipython_sql does not support transactions") if first_word.startswith('\\') and 'postgres' in str(conn.dialect): pgspecial = PGSpecial() _, cur, headers, _ = pgspecial.execute( conn.session.connection.cursor(), statement)[0] result = FakeResultProxy(cur, headers) else: txt = sqlalchemy.sql.text(statement) result = conn.session.execute(txt, user_namespace) try: # mssql has autocommit if config.autocommit and ('mssql' not in str(conn.dialect)): conn.session.execute('commit') except sqlalchemy.exc.OperationalError: pass # not all engines can commit if result and config.feedback: print(interpret_rowcount(result.rowcount)) resultset = ResultSet(result, statement, config) if config.autopandas: return resultset.DataFrame() else: return resultset #returning only last result, intentionally else: return 'Connected: %s' % conn.name
def loadDatabaseSchema(dumpFileName, ignoreSlonyTriggers = False): database = PgDatabase() logging.debug('Loading %s file' % dumpFileName) statements = sqlparse.split(open(dumpFileName,'r')) logging.debug('Parsed %d statements' % len(statements)) for statement in statements: statement = PgDumpLoader.strip_comment(statement).strip() if PgDumpLoader.PATTERN_CREATE_SCHEMA.match(statement): CreateSchemaParser.parse(database, statement) continue match = PgDumpLoader.PATTERN_DEFAULT_SCHEMA.match(statement) if match: database.setDefaultSchema(match.group(1)) continue if PgDumpLoader.PATTERN_CREATE_TABLE.match(statement): CreateTableParser.parse(database, statement) continue if PgDumpLoader.PATTERN_ALTER_TABLE.match(statement): AlterTableParser.parse(database, statement) continue if PgDumpLoader.PATTERN_CREATE_SEQUENCE.match(statement): CreateSequenceParser.parse(database, statement) continue if PgDumpLoader.PATTERN_ALTER_SEQUENCE.match(statement): AlterSequenceParser.parse(database, statement) continue if PgDumpLoader.PATTERN_CREATE_INDEX.match(statement): CreateIndexParser.parse(database, statement) continue if PgDumpLoader.PATTERN_CREATE_VIEW.match(statement): CreateViewParser.parse(database, statement) continue if PgDumpLoader.PATTERN_ALTER_VIEW.match(statement): AlterViewParser.parse(database, statement) continue if PgDumpLoader.PATTERN_CREATE_TRIGGER.match(statement): CreateTriggerParser.parse(database, statement, ignoreSlonyTriggers) continue if PgDumpLoader.PATTERN_CREATE_FUNCTION.match(statement): CreateFunctionParser.parse(database, statement) continue if PgDumpLoader.PATTERN_COMMENT.match(statement): CommentParser.parse(database, statement) continue logging.info('Ignored statement: %s' % statement) return database
def run(self, statement): """Execute the sql in the database and return the results. The results are a list of tuples. Each tuple has 4 values (title, rows, headers, status). """ # Remove spaces and EOL statement = statement.strip() if not statement: # Empty string yield (None, None, None, None) # Split the sql into separate queries and run each one. for sql in sqlparse.split(statement): # Remove spaces, eol and semi-colons. sql = sql.rstrip(';') # \G is treated specially since we have to set the expanded output # and then proceed to execute the sql as normal. if sql.endswith('\\G'): special.set_expanded_output(True) yield self.execute_normal_sql(sql.rsplit('\\G', 1)[0]) else: try: # Special command _logger.debug('Trying a dbspecial command. sql: %r', sql) cur = self.conn.cursor() for result in special.execute(cur, sql): yield result except special.CommandNotFound: # Regular SQL yield self.execute_normal_sql(sql)
def execute_statements(cursor, statements): """Executes statements.""" statements = statements.strip(u'%s%s' % (string.whitespace, ';')) statement_list = None if statements: statement_list = sqlparse.split(statements) if not statements: return try: for statement in statement_list: statement = statement.rstrip(u'%s%s' % (string.whitespace, ';')) if not statement: continue cursor.execute(statement) while cursor.nextset() is not None: pass finally: while cursor.nextset() is not None: pass
def explain(self): self.results.assure_visible() buf = self.textview.get_buffer() bounds = buf.get_selection_bounds() if not bounds: bounds = buf.get_bounds() statement = buf.get_text(*bounds) if len(sqlparse.split(statement)) > 1: dialogs.error(_(u"Select a single statement to explain.")) return if not self.connection: return queries = [Query(stmt, self.connection) for stmt in self.connection.explain_statements(statement)] def _execute_next(last, queries): if last is not None and last.failed: self.results.set_explain_results(last) return q = queries.pop(0) if len(queries) == 0: q.connect('finished', lambda x: self.results.set_explain_results(x)) else: q.connect('finished', lambda x: _execute_next(x, queries)) q.execute() _execute_next(None, queries)
def clean_reply(self, reply, dataset): solve_sql = reply.solve_sql.strip() if not solve_sql: raise FormatError("Empty query") if len(sqlparse.split(solve_sql)) > 1: raise FormatError("Only one query is allowed") return solve_sql
def execute_favorite_query(cur, arg, **_): """Returns (title, rows, headers, status)""" if arg == '': for result in list_favorite_queries(): yield result """Parse out favorite name and optional substitution parameters""" name, _, arg_str = arg.partition(' ') args = shlex.split(arg_str) query = favoritequeries.get(name) if query is None: message = "No favorite query: %s" % (name) yield (None, None, None, message) else: query, arg_error = subst_favorite_query_args(query, args) if arg_error: yield (None, None, None, arg_error) else: for sql in sqlparse.split(query): sql = sql.rstrip(';') title = '> %s' % (sql) cur.execute(sql) if cur.description: headers = [x[0] for x in cur.description] yield (title, cur, headers, None) else: yield (title, None, None, None)
def get_schema(self, name): fp = self.load_file(name, 'schemas') query = fp.read() # queries = query.split(';') queries = sqlparse.split(query) ret_queries = [q.strip() for q in queries if q.strip()] return ret_queries
def run(self, statement): """Execute the sql in the database and return the results. The results are a list of tuples. Each tuple has 4 values (title, rows, headers, status). """ # Remove spaces and EOL statement = statement.strip() if not statement: # Empty string yield (None, None, None, None) # Split the sql into separate queries and run each one. # Unless it's saving a favorite query, in which case we # want to save them all together. if statement.startswith('\\fs'): components = [statement] else: components = sqlparse.split(statement) for sql in components: # Remove spaces, eol and semi-colons. sql = sql.rstrip(';') # \G is treated specially since we have to set the expanded output. if sql.endswith('\\G'): special.set_expanded_output(True) sql = sql[:-2].strip() try: # Special command _logger.debug('Trying a dbspecial command. sql: %r', sql) cur = self.conn.cursor() for result in special.execute(cur, sql): yield result except special.CommandNotFound: # Regular SQL yield self.execute_normal_sql(sql)
def test_issue485_split_multi(): p_sql = '''CREATE OR REPLACE RULE ruled_tab_2rules AS ON INSERT TO public.ruled_tab DO instead ( select 1; select 2; );''' assert len(sqlparse.split(p_sql)) == 1
def load_database_schema(dump_file_name, ignore_slony_triggers=False): database = PgDatabase() print("Loading file dump: %s\n" % dump_file_name) statements = sqlparse.split(open(dump_file_name, 'r')) for statement in statements: statement = PgDumpLoader.strip_comment(statement).strip() if PgDumpLoader.PATTERN_CREATE_SCHEMA.match(statement): CreateSchemaParser.parse(database, statement) continue match = PgDumpLoader.PATTERN_DEFAULT_SCHEMA.match(statement) if match: database.setDefaultSchema(match.group(1)) continue if PgDumpLoader.PATTERN_CREATE_TABLE.match(statement): CreateTableParser.parse(database, statement) continue if PgDumpLoader.PATTERN_ALTER_TABLE.match(statement): AlterTableParser.parse(database, statement) continue if PgDumpLoader.PATTERN_CREATE_SEQUENCE.match(statement): CreateSequenceParser.parse(database, statement) continue if PgDumpLoader.PATTERN_ALTER_SEQUENCE.match(statement): AlterSequenceParser.parse(database, statement) continue if PgDumpLoader.PATTERN_CREATE_INDEX.match(statement): CreateIndexParser.parse(database, statement) continue if PgDumpLoader.PATTERN_CREATE_VIEW.match(statement): CreateViewParser.parse(database, statement) continue if PgDumpLoader.PATTERN_ALTER_VIEW.match(statement): AlterViewParser.parse(database, statement) continue if PgDumpLoader.PATTERN_CREATE_TRIGGER.match(statement): CreateTriggerParser.parse(database, statement, ignore_slony_triggers) continue if PgDumpLoader.PATTERN_CREATE_FUNCTION.match(statement): CreateFunctionParser.parse(database, statement) continue if PgDumpLoader.PATTERN_COMMENT.match(statement): CommentParser.parse(database, statement) continue return database
def need_completion_refresh(queries): """Determines if the completion needs a refresh by checking if the sql statement is an alter, create, drop or change db.""" for query in sqlparse.split(queries): try: first_token = query.split()[0] return first_token.lower() in ("alter", "create", "use", "\\c", "\\connect", "drop") except Exception: return False
def run(conn, sql, config): if sql.strip(): for statement in sqlparse.split(sql): txt = sqlalchemy.sql.text(statement) result = conn.session.execute(txt) return ResultSet(result, statement, config) # returning only last result, intentionally else: return "Connected: %s" % conn.name
def test_issue193_splitting_function(): sql = """ CREATE FUNCTION a(x VARCHAR(20)) RETURNS VARCHAR(20) BEGIN DECLARE y VARCHAR(20); RETURN x; END; SELECT * FROM a.b;""" statements = sqlparse.split(sql) assert len(statements) == 2
def run(conn, sql, config, user_namespace={}): rowcount = 0 for statement in sqlparse.split(sql): txt = sqlalchemy.sql.text(statement) result = conn.session.execute(txt, user_namespace) resultset = ResultSet(result, statement, config) return resultset
def execute_query(self, cursor, query): """Executes query.""" statements = sqlparse.split(query) for statement in statements: statement = statement.rstrip(unicode(string.whitespace + ';')) if statement: cursor.execute(statement) while cursor.nextset() is not None: pass
def parse_statements(sql_location): if os.path.isfile(sql_location) and os.access(sql_location, os.R_OK): file = open(sql_location, "r") content = file.read() sql = filter(None, sqlparse.split(content)) assert isinstance(sql, list) return sql else: print("SQL file not found or not accessible")
def execute_count_statements(cursor, statements): """Executes count statement(s).""" counts = [] if not statements: return counts statements = statements.strip(u'%s%s' % (string.whitespace, ';')) statement_list = None if statements: statement_list = sqlparse.split(statements) if not statements: return counts try: for statement in statement_list: count = None statement = statement.rstrip(u'%s%s' % (string.whitespace, ';')) if not statement: counts.append(count) continue row_count = cursor.execute(statement) if len(cursor.description) > 1: raise exceptions.Error( 'Statement should return a single value only.') if row_count > 1: raise exceptions.Error( u'Statement should return a single row only. ' u'Statement was: %s' % (statement,) ) if not row_count: raise exceptions.Error( u'Statement returned an empty set. ' u'Statement was: %s' % (statement,) ) row = cursor.fetchone() if row is None: raise exceptions.Error( u'Statement returned an empty set. ' u'Statement was: %s' % (statement,) ) counts.append(row[0]) finally: while cursor.nextset() is not None: pass return counts
def run(self): sqlFile = os.path.join(self._dirPath, self._plugin_settings["dataFile"]) self._log("Loading SQL file:" + sqlFile) sql = open(sqlFile).read() sql_parts = sqlparse.split(sql) for sql_part in sql_parts: if sql_part.strip() == "": continue self._execSql(sql_part)
def get_query(self, name): if name in self.query_cache: return self.query_cache.get(name) fp = self.load_file(name, 'queries') query = fp.read() queries = sqlparse.split(query) ret_queries = [q.strip() for q in queries if q.strip()] self.query_cache[name] = ret_queries return ret_queries
def upgrade(): migrate_engine = op.get_bind() meta = MetaData(bind=migrate_engine) engine_name = migrate_engine.engine.name if engine_name == 'sqlite': sql_file = os.path.splitext(__file__)[0] sql_file += '.sql' with open(sql_file, 'r') as sqlite_script: sql = sqlparse.format(sqlite_script.read(), strip_comments=True) for statement in sqlparse.split(sql): op.execute(statement) return enum = Enum('private', 'public', 'shared', 'community', metadata=meta, name='image_visibility') enum.create() v_col = Column('visibility', enum, nullable=False, server_default='shared') op.add_column('images', v_col) op.create_index('visibility_image_idx', 'images', ['visibility']) images = Table('images', meta, autoload=True) images.update(values={'visibility': 'public'}).where( images.c.is_public).execute() image_members = Table('image_members', meta, autoload=True) # NOTE(dharinic): Mark all the non-public images as 'private' first images.update().values(visibility='private').where( not_(images.c.is_public)).execute() # NOTE(dharinic): Identify 'shared' images from the above images.update().values(visibility='shared').where(and_( images.c.visibility == 'private', images.c.id.in_(select( [image_members.c.image_id]).distinct().where( not_(image_members.c.deleted))))).execute() op.drop_index('ix_images_is_public', 'images') op.drop_column('images', 'is_public')
def load_sql_script_file(sql_file_name, msg='load_sql_script_file', exit_if_file_not_found=True): logger.info(msg) ignore = {'CREATE FUNCTION'} # extend this def _filter(statement, allow=0): ddl = [ t for t in statement.tokens if t.ttype in (tokens.DDL, tokens.Keyword) ] start = ' '.join(d.value for d in ddl[:2]) if ddl and start in ignore: allow = 1 for tok in statement.tokens: if allow or not isinstance(tok, sqlparse.sql.Comment): yield tok sql_file_name = os.path.join(get_ruian_services_sql_scripts_path(), sql_file_name) logger.info(" Loading SQL commands from {0}".format(sql_file_name)) if not os.path.exists(sql_file_name): if exit_if_file_not_found: logger.error("ERROR: File %s not found." % sql_file_name) exit_app() else: logger.warning("ERROR: File %s not found." % sql_file_name) return in_file = codecs.open(sql_file_name, "r", "utf-8") raw = in_file.read() in_file.close() statements = [] for stmt in sqlparse.split(raw): sql = sqlparse.parse(stmt)[0] tl = sqlparse.sql.TokenList([t for t in _filter(sql)]) statements.append(tl.value) logger.info(" Loading SQL commands - done.") return statements
def test_split_begin_end_block(): sql = """ select * from dual; DECLARE test_num number; begin dbms_output.put_line("123"); BEGIN select (case when rownum > 1 then case when rownum == 1 then 0 else 2 end else 0 end) a into test_num from dual; dbms_output.put_line(test_num); end; end; select * from dual; """ stmts = sqlparse.split(sql.strip()) assert len(stmts) == 3
def run(self, statement): """Execute the sql in the database and return the results. The results are a list of tuples. Each tuple has 3 values (rows, headers, status). """ # Remove spaces and EOL statement = statement.strip() if not statement: # Empty string yield (None, None, None, None) # Split the sql into separate queries and run each one. for sql in sqlparse.split(statement): # Remove spaces, eol and semi-colons. sql = sql.rstrip(';') # Check if the command is a \c or 'use'. This is a special # exception that cannot be offloaded to `pgspecial` lib. Because we # have to change the database connection that we're connected to. if sql.startswith('\c') or sql.lower().startswith('use'): _logger.debug('Database change command detected.') try: dbname = sql.split()[1] except: _logger.debug('Database name missing.') raise RuntimeError('Database name missing.') self.connect(database=dbname) self.dbname = dbname _logger.debug('Successfully switched to DB: %r', dbname) yield (None, None, None, 'You are now connected to database "%s" as ' 'user "%s"' % (self.dbname, self.user)) else: try: # Special command _logger.debug('Trying a pgspecial command. sql: %r', sql) cur = self.conn.cursor() for result in pgspecial.execute(cur, sql): yield result except KeyError: # Regular SQL yield self.execute_normal_sql(sql)
def __init__(self, owner_uri: str, query_text: str, query_execution_settings: QueryExecutionSettings, query_events: QueryEvents) -> None: self._execution_state: ExecutionState = ExecutionState.NOT_STARTED self._owner_uri: str = owner_uri self._query_text = query_text self._disable_auto_commit = False self._current_batch_index = 0 self._batches: List[Batch] = [] self._execution_plan_options = query_execution_settings.execution_plan_options self.is_canceled = False # Initialize the batches statements = sqlparse.split(query_text) selection_data = compute_selection_data_for_batches(statements, query_text) for index, batch_text in enumerate(statements): # Skip any empty text formatted_text = sqlparse.format(batch_text, strip_comments=True).strip() if not formatted_text or formatted_text == ';': continue sql_statement_text = batch_text # Create and save the batch if bool(self._execution_plan_options): if self._execution_plan_options.include_estimated_execution_plan_xml: sql_statement_text = Query.EXPLAIN_QUERY_TEMPLATE.format(sql_statement_text) elif self._execution_plan_options.include_actual_execution_plan_xml: self._disable_auto_commit = True sql_statement_text = Query.ANALYZE_EXPLAIN_QUERY_TEMPLATE.format(sql_statement_text) batch = create_batch( sql_statement_text, len(self.batches), selection_data[index], query_events.batch_events, query_execution_settings.result_set_storage_type) self._batches.append(batch)
def run_sql(session, sql, params=None, stop_on_error=False): queries = split(sql) for q in queries: sql = format(q, strip_comments=True).strip() if sql == "": continue try: session.execute(text(sql), params=params) if hasattr(session, "commit"): session.commit() pretty_print(sql, dim=True) except (ProgrammingError, IntegrityError) as err: err = str(err.orig).strip() dim = "already exists" in err if hasattr(session, "rollback"): session.rollback() pretty_print(sql, fg=None if dim else "red", dim=True) if dim: err = " " + err secho(err, fg="red", dim=dim) if stop_on_error: return
def query_check(sql=''): result = { 'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False } try: sql = sqlparse.format(sql, strip_comments=True) sql = sqlparse.split(sql)[0] result['filtered_sql'] = sql.strip() except Exception as err: result['bad_query'] = True result['msg'] = 'SQL语句无效' if re.match("select|show|explain|desc", sql) is None: result['bad_query'] = True result['msg'] = '不支持的语句类型' if re.search('\*', sql) is not None: result['has_star'] = True result['msg'] = 'SQL语句中含有 * ' return result
def execute_query(self, query): # Try to run first as special command try: for rows, columns, status, statement, is_error in special.execute(self, query): yield rows, columns, status, statement, is_error except special.CommandNotFound: # Execute as normal sql # Remove spaces, EOL and semi-colons from end query = query.strip() if not query: yield None, None, None, query, False else: for single_query in sqlparse.split(query): # Remove spaces, EOL and semi-colons from end single_query = single_query.strip().rstrip(';') if single_query: for rows, columns, status, statement, is_error \ in self._execute_query(single_query): yield rows, columns, status, statement, is_error else: yield None, None, None, None, False continue
def _batch_DDLs(self, sql): """ Check that the given operation contains only DDL statements and batch them into an internal list. :type sql: str :param sql: A SQL query statement. :raises: :class:`ValueError` in case not a DDL statement present in the operation. """ statements = [] for ddl in sqlparse.split(sql): if ddl: ddl = ddl.rstrip(";") if parse_utils.classify_stmt(ddl) != parse_utils.STMT_DDL: raise ValueError("Only DDL statements may be batched.") statements.append(ddl) # Only queue DDL statements if they are all correctly classified. self.connection._ddl_statements.extend(statements)
def parse(self, fd): text = fd.read().rstrip('\n') statements = [] for statement in sqlparse.split(text): match = name_token.search(statement) sql = ' '.join( sqlparse.format(statement, strip_comments=True).split()) sql = params_token.sub(r'%(\1)s', sql) name = match.group('name') command = match.group('command') result = match.group('result') if name: statements.append({ 'name': name.replace('-', '_'), 'command': command, 'result': result, 'sql': sql }) return statements
def exec_threaded(statement, start_line): if self.app.config.get("sqlparse.enabled", True): stmts = sqlparse.split(statement) else: stmts = [statement] for stmt in stmts: add_offset = len(stmt.splitlines()) if not stmt.strip(): start_line += add_offset continue query = Query(stmt, self.connection) # query.coding_hint = self.connection.coding_hint gtk.gdk.threads_enter() query.set_data('editor_start_line', start_line) query.connect("started", self.on_query_started) query.connect("finished", self.on_query_finished, tag_notice) gtk.gdk.threads_leave() query.execute(True) start_line += add_offset if query.failed: # hmpf, doesn't work that way... so just return here... return
def explainPlan(self, queries, callback): queryName = 'explain plan' explainQuery = self.getNamedQuery(queryName) if not explainQuery: return strippedQueries = [ explainQuery.format(query.strip().strip(";")) for rawQuery in queries for query in filter(None, sqlparse.split(rawQuery)) ] queryToRun = self.buildNamedQuery(queryName, strippedQueries) args = self.buildArgs(queryName) env = self.buildEnv() self.Command.createAndRun(args=args, env=env, callback=callback, query=queryToRun, encoding=self.encoding, timeout=self.timeout, silenceErrors=False, stream=self.useStreams)
def save(self, request): id = self.validated_data['id'] obj = models.DbOrders.objects.get(pk=id) # 如果记录已经存在,直接跳转 if models.DbOrdersExecuteTasks.objects.filter(order__id=id).exists(): return models.DbOrdersExecuteTasks.objects.filter( order__id=id).first().task_id # 生成记录 splitsqls = [ sql.strip(';') for sql in sqlparse.split(obj.contents, encoding='utf8') ] task_id = ''.join(str(uuid.uuid4()).split('-')) # 基于UUID生成任务ID for sql in splitsqls: models.DbOrdersExecuteTasks.objects.create( applicant=obj.applicant, task_id=task_id, sql=sql.strip(';'), sql_type=obj.sql_type, file_format=obj.file_format, order_id=id) return task_id
def mysql_parse(self, sql, paramHost, paramPort, paramUser, paramPasswd, paramDb): result_key = [] result_value = [] conn = MySQLdb.connect(host=paramHost, user=paramUser, passwd=paramPasswd, db=paramDb, port=paramPort, charset='utf8') cursor = conn.cursor() list_sql = sqlparse.split(sql) for row in list_sql: try: cursor.execute('EXPLAIN ' + row) except Exception as e: result_key.append(row) result_value.append(str(e)) if result_key: return False, dict(zip(result_key, result_value)) else: return True, 'ok'
def exec_sql(engine: Engine, path: Path): """ Call this function to execute the SQL statements within a file against your database. :param engine: the engine connected to the database :param path: the path to the containing your SQL statements """ with engine.connect() as connection: logger: logging.Logger = logging.getLogger(__name__) for sql_stmt in sqlparse.split(path.read_text().strip()): # Parse the statement so that we may detect comments. sqlp = sqlparse.parse(sql_stmt) # If the parsed statement has only one token and it's statement # type is 'unknown'... if len(sqlp) == 1 and sqlp[0].get_type() == 'UNKNOWN': # ...move along. This is likely a comment and will cause an # exception if we try to execute it by itself. continue # We're all set. Execute the statement. logger.debug(sql_stmt) connection.execute(sql_stmt)
def execute(self, queries, callback, stream=False): queryToRun = '' for query in self.getOptionsForSgdbCli()['before']: queryToRun += query + "\n" if isinstance(queries, str): queries = [queries] for rawQuery in queries: for query in sqlparse.split(rawQuery): if self.safe_limit: parsedTokens = sqlparse.parse(query.strip().replace( "'", "\"")) if ((parsedTokens[0][0].ttype in sqlparse.tokens.Keyword and parsedTokens[0][0].value == 'select')): applySafeLimit = True for parse in parsedTokens: for token in parse.tokens: if token.ttype in sqlparse.tokens.Keyword and token.value == 'limit': applySafeLimit = False if applySafeLimit: if (query.strip()[-1:] == ';'): query = query.strip()[:-1] query += " LIMIT {0};".format(self.safe_limit) queryToRun += query + "\n" Log("Query: " + queryToRun) if self.history: self.history.add(queryToRun) self.Command.createAndRun(self.builArgs(), queryToRun, callback, options={'show_query': self.show_query}, timeout=self.timeout, stream=stream)
def query_check(self, db_name=None, sql=''): # 查询语句的检查、注释去除、切分 result = {'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False} banned_keywords = ["ascii", "char", "charindex", "concat", "concat_ws", "difference", "format", "len", "nchar", "patindex", "quotename", "replace", "replicate", "reverse", "right", "soundex", "space", "str", "string_agg", "string_escape", "string_split", "stuff", "substring", "trim", "unicode"] keyword_warning = '' star_patter = r"(^|,| )\*( |\(|$)" # 删除注释语句,进行语法判断,执行第一条有效sql try: sql = sql.format(sql, strip_comments=True) sql = sqlparse.split(sql)[0] result['filtered_sql'] = sql.strip() sql_lower = sql.lower() except IndexError: result['has_star'] = True result['msg'] = '没有有效的SQL语句' return result if re.match(r"^select", sql_lower) is None: result['bad_query'] = True result['msg'] = '仅支持^select语法!' return result if re.search(star_patter, sql_lower) is not None: keyword_warning += '禁止使用 * 关键词\n' result['bad_query'] = True result['has_star'] = True if '+' in sql_lower: keyword_warning += '禁止使用 + 关键词\n' result['bad_query'] = True for keyword in banned_keywords: pattern = r"(^|,| |=){}( |\(|$)".format(keyword) if re.search(pattern, sql_lower) is not None: keyword_warning += '禁止使用 {} 关键词\n'.format(keyword) result['bad_query'] = True if result.get('bad_query'): result['msg'] = keyword_warning return result
def explain(request): sql_content = request.POST.get('sql_content') instance_name = request.POST.get('instance_name') db_name = request.POST.get('db_name') result = {'status': 0, 'msg': 'ok', 'data': []} # 服务器端参数验证 if sql_content is None or instance_name is None: result['status'] = 1 result['msg'] = '页面提交参数可能为空' return HttpResponse(json.dumps(result), content_type='application/json') sql_content = sql_content.strip() # 过滤非查询的语句 if re.match(r"^explain", sql_content.lower()): pass else: result['status'] = 1 result['msg'] = '仅支持explain开头的语句,请检查' return HttpResponse(json.dumps(result), content_type='application/json') # 执行第一条有效sql sql_content = sqlparse.split(sql_content)[0].rstrip(';') # 执行获取执行计划语句 sql_result = Dao(instance_name=instance_name).mysql_query( str(db_name), sql_content) result['data'] = sql_result # 返回查询结果 return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True), content_type='application/json')
def add_query(self, sql, model_name=None, auto_begin=True, bindings=None, abridge_sql_log=False): connection = None cursor = None # TODO: is this sufficient? Largely copy+pasted from snowflake, so # there's some common behavior here we can maybe factor out into the # SQLAdapter? queries = [q.rstrip(';') for q in sqlparse.split(sql)] for individual_query in queries: # hack -- after the last ';', remove comments and don't run # empty queries. this avoids using exceptions as flow control, # and also allows us to return the status of the last cursor without_comments = re.sub(re.compile('^.*(--.*)$', re.MULTILINE), '', individual_query).strip() if without_comments == "": continue parent = super(HiveConnectionManager, self) connection, cursor = parent.add_query(individual_query, model_name, auto_begin, bindings, abridge_sql_log) if cursor is None: raise RuntimeException( "Tried to run an empty query on model '{}'. If you are " "conditionally running\nsql, eg. in a model hook, make " "sure your `else` clause contains valid sql!\n\n" "Provided SQL:\n{}".format(model_name, sql)) return connection, cursor
def query_check(self, db_name=None, sql=''): # 查询语句的检查、注释去除、切分 result = { 'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False } # 删除注释语句,进行语法判断,执行第一条有效sql try: sql = sqlparse.format(sql, strip_comments=True) sql = sqlparse.split(sql)[0] result['filtered_sql'] = sql.strip() except IndexError: result['bad_query'] = True result['msg'] = '没有有效的SQL语句' if re.match(r"^select", sql, re.I) is None: result['bad_query'] = True result['msg'] = '不支持的查询语法类型!' if '*' in sql: result['has_star'] = True result['msg'] = 'SQL语句中含有 * ' return result
def execute_sql(connection, code_to_execute, schema=None): sql_statements = sqlparse.split(code_to_execute) if schema is not None: pre_statement = "set search_path=%s;" % schema else: pre_statement = "" trans = connection.begin() try: i = 0 for sql_statement in sql_statements: print(sql_statement) sql_to_execute = pre_statement + sql_statement connection.execute(sql_to_execute) i += 1 except: trans.rollback() raise trans.commit() print("Executed %s statements" % i)
def init_database(): try: db = pymysql.connect(host=config['database']['host'], user=config['database']['user'], password=config['database']['password'], port=config['database']['port'], cursorclass=pymysql.cursors.DictCursor) cursor = db.cursor() stmts = sqlparse.split(sql.read()) for stmt in stmts: cursor.execute(stmt) db.commit() print('All queries were executed successfully') except (pymysql.err.OperationalError, pymysql.ProgrammingError, pymysql.InternalError, pymysql.IntegrityError) as error: print('Exception number: {}, value {!r}'.format(error.args[0], error)) raise else: cursor.close() db.close()
def run(conn, sql, config, user_namespace): if sql.strip(): for statement in sqlparse.split(sql): if sql.strip().split()[0].lower() == 'begin': raise Exception("ipython_sql does not support transactions") txt = sqlalchemy.sql.text(statement) result = conn.session.execute(txt, user_namespace) try: # mssql has autocommit if 'mssql' not in str(conn.dialect): conn.session.execute('commit') except sqlalchemy.exc.OperationalError: pass # not all engines can commit if result and config.feedback: print(interpret_rowcount(result.rowcount)) resultset = ResultSet(result, statement, config) if config.autopandas: return resultset.DataFrame() else: return resultset #returning only last result, intentionally else: return 'Connected: %s' % conn.name
def from_file(filepath): """Read in a RawUdf from a SQL file on disk.""" dirpath, basename = os.path.split(filepath) name = os.path.basename(dirpath) + "_" + basename.replace(".sql", "") with open(filepath) as f: text = f.read() sql = sqlparse.format(text, strip_comments=True) statements = [s for s in sqlparse.split(sql) if s.strip()] definitions = [ s for s in statements if s.lower().startswith("create temp function") ] tests = [ s for s in statements if not s.lower().startswith("create temp function") ] dependencies = re.findall(UDF_RE, "\n".join(definitions)) if name not in dependencies: raise ValueError( "Expected a temporary UDF named {} to be defined in {}".format( name, filepath)) dependencies.remove(name) return RawUdf(name, filepath, definitions, tests, set(dependencies))
def post(self, request): data = format_request(request) sqlContent = data.get('sql_content').strip() sqlSplit = [] for stmt in sqlparse.split(sqlContent): sql = sqlparse.parse(stmt)[0] sql_comment = sql.token_first() if isinstance(sql_comment, sqlparse.sql.Comment): sqlSplit.append({ 'comment': sql_comment.value, 'sql': sql.value.replace(sql_comment.value, '') }) else: sqlSplit.append({'comment': '', 'sql': sql.value}) beautifySQL_list = [] try: for row in sqlSplit: comment = row['comment'] sql = row['sql'] res = sqlparse.parse(sql) if res[0].tokens[0].ttype[1] == 'DML': sqlFormat = sqlparse.format(sql, keyword_case='upper', reindent=True) beautifySQL_list.append(comment + sqlFormat) elif res[0].tokens[0].ttype[1] == 'DDL': sqlFormat = sqlparse.format(sql, keyword_case='upper') beautifySQL_list.append(comment + sqlFormat) beautifySQL = '\n\n'.join(beautifySQL_list) context = {'data': beautifySQL} except Exception as err: raise OSError(err) context = {'errCode': 400, 'errMsg': "注释不合法, 请检查"} return HttpResponse(json.dumps(context))
def on_action_execute(self, widget): try: self.logger.get_model().clear() tabs = self.notebookLog.get_n_pages() while(tabs != 0): self.notebookLog.remove_page(tabs) tabs -=1 widget = self.notebookEditor.get_nth_page(self.notebookEditor.get_current_page()) editorText = widget.get_tooltip_text() currentSource = widget.get_children() _buffer = currentSource[0].get_buffer() startIter = _buffer.get_start_iter() endIter = _buffer.get_end_iter() text = _buffer.get_text(startIter, endIter, True) text = text.rstrip('\n') sqlStatements = sqlparse.split(text) statements = [] for i in sqlStatements: formatedText = sqlparse.format(i, reindent=True,keyword_case='upper', strip_comments=True) statements.append(formatedText) statements = list(filter(None, statements)) # fastest if len(statements) >= 1: n = len(statements) for i in range(0,n): self.insert_result(statements[i]) except sqlite3.OperationalError as error: print(error) except AttributeError as error: print(error) finally: self.boxNotebookLog.show_all()
def execute(self, sql, **kwargs): """ Execute a DDL or DML SQL statement. Args: sql (string): SQL statement(s) separated by semicolons (;) kwargs (dict): optional statement named parameters Returns: results (list): list of dataframes. Non-SELECT statements returns empty dataframes. """ results = [] statements = sqlparse.split(sql) connection = self.engine.connect() # begin transaction trans = connection.begin() try: for statement in statements: result_set = pd.DataFrame() result = connection.execute(text(statement.strip(';')), **kwargs) if result.returns_rows: result_set = pd.DataFrame(result.fetchall()) result_set.columns = result.keys() LOGGER.info('Number of returned rows: %s', str(len(result_set.index))) results.append(result_set) # end transaction trans.commit() except DatabaseError as db_error: trans.rollback() LOGGER.error(db_error) raise finally: connection.close() return results
def query_check(self, db_name=None, sql=''): # 查询语句的检查、注释去除、切分 result = {'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False} # 删除注释语句,进行语法判断,执行第一条有效sql try: sql = sqlparse.format(sql, strip_comments=True) sql = sqlparse.split(sql)[0] result['filtered_sql'] = sql.strip() except IndexError: result['bad_query'] = True result['msg'] = '没有有效的SQL语句' if re.match(r"^select|^show|^explain", sql, re.I) is None: result['bad_query'] = True result['msg'] = '不支持的查询语法类型!' if '*' in sql: result['has_star'] = True result['msg'] = 'SQL语句中含有 * ' # select语句先使用Explain判断语法是否正确 if re.match(r"^select", sql, re.I): explain_result = self.query(db_name=db_name, sql=f"explain {sql}") if explain_result.error: result['bad_query'] = True result['msg'] = explain_result.error return result
def query_check(self, db_name=None, sql=''): # 查询语句的检查、注释去除、切分 result = {'msg': '', 'bad_query': False, 'filtered_sql': sql, 'has_star': False} keyword_warning = '' sql_whitelist = ['select', 'explain'] # 根据白名单list拼接pattern语句 whitelist_pattern = "^" + "|^".join(sql_whitelist) # 删除注释语句,进行语法判断,执行第一条有效sql try: sql = sql.format(sql, strip_comments=True) sql = sqlparse.split(sql)[0] result['filtered_sql'] = sql.strip() # sql_lower = sql.lower() except IndexError: result['has_star'] = True result['msg'] = '没有有效的SQL语句' return result if re.match(whitelist_pattern, sql) is None: result['bad_query'] = True result['msg'] = '仅支持{}语法!'.format(','.join(sql_whitelist)) return result if result.get('bad_query'): result['msg'] = keyword_warning return result
def run(self, statement): """Execute the sql in the database and return the results. The results are a list of tuples. Each tuple has 4 values (title, rows, headers, status). """ # Remove spaces and EOL statement = statement.strip() if not statement: # Empty string yield (None, None, None, None) # Split the sql into separate queries and run each one. # Unless it's saving a favorite query, in which case we # want to save them all together. if statement.startswith('\\fs'): components = [statement] else: components = sqlparse.split(statement) for sql in components: # Remove spaces, eol and semi-colons. sql = sql.rstrip(';') # \G is treated specially since we have to set the expanded output # and then proceed to execute the sql as normal. if sql.endswith('\\G'): special.set_expanded_output(True) yield self.execute_normal_sql(sql.rsplit('\\G', 1)[0]) else: try: # Special command _logger.debug('Trying a dbspecial command. sql: %r', sql) cur = self.conn.cursor() for result in special.execute(cur, sql): yield result except special.CommandNotFound: # Regular SQL yield self.execute_normal_sql(sql)