Python sqlalchemy.sql方法代码示例

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def test_contain_percents_character_query_with_parameter(self, engine, conn):
        query = sqlalchemy.sql.text(
            """
            SELECT date_parse('20191030', '%Y%m%d'), :word
            """
        )
        result = engine.execute(query, word="cat")
        self.assertEqual(result.fetchall(), [(datetime(2019, 10, 30), "cat")])

        query = sqlalchemy.sql.text(
            """
            SELECT col_string FROM one_row_complex
            WHERE col_string LIKE 'a%' OR col_string LIKE :param
            """
        )
        result = engine.execute(query, param="b%")
        self.assertEqual(result.fetchall(), [("a string",)]) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def add_note(db, server, user, text):
    id_query = select([sqlalchemy.sql.expression.func.max(table.c.note_id).label("maxid")]) \
        .where(table.c.user == user.lower())
    max_id = db.execute(id_query).scalar()

    if max_id is None:
        note_id = 1
    else:
        note_id = max_id + 1

    query = table.insert().values(
        note_id=note_id,
        connection=server,
        user=user.lower(),
        text=text,
        deleted=False,
        added=datetime.today()
    )
    db.execute(query)
    db.commit() 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def generate_default_port_by_dialect(dialect: str) -> str:
    """
    In case no port was chosen, a default port will be chosen according to the SQL db type
    :param dialect: sql db type
    :return: default port needed for connection
    """
    if dialect == "MySQL":
        return "3306"
    elif dialect == "PostgreSQL":
        return "5432"
    elif dialect == "Oracle":
        return "1521"
    elif dialect == "Microsoft SQL Server":
        return "1433"
    else:
        # set default to mysql
        return "3306" 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def col_expr_requires_cte(call, sel, is_mutate = False):
    """Return whether a variable assignment needs a CTE"""

    call_vars = set(call.op_vars(attr_calls = False))

    columns = lift_inner_cols(sel)
    sel_labs = set(k for k,v in columns.items() if isinstance(v, sql.elements.Label))

    # I use the acronym fwg sol (frog soul) to remember sql clause eval order
    # from, where, group by, select, order by, limit
    # group clause evaluated before select clause, so not issue for mutate
    group_needs_cte = not is_mutate and len(sel._group_by_clause)
    
    return (   group_needs_cte
            or len(sel._order_by_clause)
            or not sel_labs.isdisjoint(call_vars)
            ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def _mutate(__data, **kwargs):
    # Cases
    #  - work with group by
    #  - window functions
    # TODO: verify it can follow a renaming select

    # track labeled columns in set
    sel = __data.last_op

    # evaluate each call
    for colname, func in kwargs.items():
        # keep set of columns labeled (aliased) in this select statement
        # need to use inner cols, since sel.columns uses ColumnClause, not Label
        labs = set(k for k,v in lift_inner_cols(sel).items() if isinstance(v, sql.elements.Label))
        new_call = __data.shape_call(func, verb_name = "Mutate", arg_name = colname)

        sel = _mutate_select(sel, colname, new_call, labs, __data)

    return __data.append_op(sel) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def _group_by(__data, *args, add = False, **kwargs):
    if kwargs:
        data = mutate(__data, **kwargs)
    else:
        data = __data

    cols = data.last_op.columns

    # put kwarg grouping vars last, so similar order to function call
    groups =  tuple(simple_varname(arg) for arg in args) + tuple(kwargs)
    if None in groups:
        raise NotImplementedError("Complex expressions not supported in sql group_by")

    unmatched = set(groups) - set(cols.keys())
    if unmatched:
        raise KeyError("group_by specifies columns missing from table: %s" %unmatched)

    if add:
        groups = ordered_union(data.group_by, groups)

    return data.copy(group_by = groups) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def _anti_join(left, right = None, on = None, *args, sql_on = None):
    _raise_if_args(args)

    left_sel = left.last_op.alias()
    right_sel = right.last_op.alias()

    # handle arguments ----
    on  = _validate_join_arg_on(on, sql_on)
    
    # create join conditions ----
    bool_clause = _create_join_conds(left_sel, right_sel, on)

    # create inner join ----
    not_exists = ~sql.exists([1], from_obj = right_sel).where(bool_clause)
    sel = sql.select(left_sel.columns, from_obj = left_sel).where(not_exists)
    return left.append_op(sel) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def _validate_join_arg_on(on, sql_on = None):
    # handle sql on case
    if sql_on is not None:
        if on is not None:
            raise ValueError("Cannot specify both on and sql_on")

        return sql_on

    # handle general cases
    if on is None:
        raise NotImplementedError("on arg currently cannot be None (default) for SQL")
    elif isinstance(on, str):
        on = {on: on}
    elif isinstance(on, (list, tuple)):
        on = dict(zip(on, on))

    if not isinstance(on, Mapping):
        raise TypeError("on must be a Mapping (e.g. dict)")

    return on 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def _create_join_conds(left_sel, right_sel, on):
    left_cols  = left_sel.columns  #lift_inner_cols(left_sel)
    right_cols = right_sel.columns #lift_inner_cols(right_sel)

    if callable(on):
        # callable, like with sql_on arg
        conds = [on(left_cols, right_cols)]
    else:
        # dict-like of form {left: right}
        conds = []
        for l, r in on.items():
            col_expr = left_cols[l] == right_cols[r]
            conds.append(col_expr)
            
    return sql.and_(*conds)
    

# Head ------------------------------------------------------------------------ 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def test_dry_run(engine, api_client):
    sql = 'SELECT * FROM test_pybigquery.sample_one_row'
    assert api_client.dry_run_query(sql).total_bytes_processed == 148

    sql = 'SELECT * FROM sample_one_row'
    with pytest.raises(BadRequest) as excinfo:
        api_client.dry_run_query(sql)

    assert 'Table name "sample_one_row" missing dataset while no default dataset is set in the request.' in str(excinfo.value.message) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def test_contain_percents_character_query(self, engine, conn):
        query = sqlalchemy.sql.text(
            """
            SELECT date_parse('20191030', '%Y%m%d')
            """
        )
        result = engine.execute(query)
        self.assertEqual(result.fetchall(), [(datetime(2019, 10, 30),)]) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def test_query_with_parameter(self, engine, conn):
        query = sqlalchemy.sql.text(
            """
            SELECT :word
            """
        )
        result = engine.execute(query, word="cat")
        self.assertEqual(result.fetchall(), [("cat",)]) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def sql_query_execute(client: Client, args: dict, *_) -> Tuple[str, Dict[str, Any], List[Dict[str, Any]]]:
    """
    Executes the sql query with the connection that was configured in the client
    :param client: the client object with the db connection
    :param args: demisto.args() including the sql query
    :return: Demisto outputs
    """
    try:
        sql_query = str(args.get('query'))
        limit = int(args.get('limit', 50))
        skip = int(args.get('skip', 0))
        bind_variables_names = args.get('bind_variables_names', "")
        bind_variables_values = args.get('bind_variables_values', "")
        bind_variables = generate_bind_vars(bind_variables_names, bind_variables_values)

        result, headers = client.sql_query_execute_request(sql_query, bind_variables)
        # converting an sqlalchemy object to a table
        converted_table = [dict(row) for row in result]
        # converting b'' and datetime objects to readable ones
        table = [{str(key): str(value) for key, value in dictionary.items()} for dictionary in converted_table]
        table = table[skip:skip + limit]
        human_readable = tableToMarkdown(name="Query result:", t=table, headers=headers,
                                         removeNull=True)
        context = {
            'Result': table,
            'Query': sql_query,
            'InstanceName': f'{client.dialect}_{client.dbname}'
        }
        entry_context: Dict = {'GenericSQL(val.Query && val.Query === obj.Query)': {'GenericSQL': context}}
        return human_readable, entry_context, table

    except Exception as err:
        # In case there is no query executed and only an action e.g - insert, delete, update
        # the result will raise an exception when we try to read the data from it
        if str(err) == "This result object does not return rows. It has been closed automatically.":
            human_readable = "Command executed"
            return human_readable, {}, []
        raise err 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def main():
    params = demisto.params()
    dialect = params.get('dialect')
    port = params.get('port')
    if port is None:
        port = generate_default_port_by_dialect(dialect)
    user = params.get("credentials").get("identifier")
    password = params.get("credentials").get("password")
    host = params.get('host')
    database = params.get('dbname')
    ssl_connect = params.get('ssl_connect')
    connect_parameters = params.get('connect_parameters')
    try:
        command = demisto.command()
        LOG(f'Command being called in SQL is: {command}')
        client = Client(dialect=dialect, host=host, username=user, password=password,
                        port=port, database=database, connect_parameters=connect_parameters, ssl_connect=ssl_connect)
        commands: Dict[str, Callable[[Client, Dict[str, str], str], Tuple[str, Dict[Any, Any], List[Any]]]] = {
            'test-module': test_module,
            'query': sql_query_execute,
            'sql-command': sql_query_execute
        }
        if command in commands:
            return_outputs(*commands[command](client, demisto.args(), command))
        else:
            raise NotImplementedError(f'{command} is not an existing Generic SQL command')
        client.connection.close()
    except Exception as err:
        return_error(f'Unexpected error: {str(err)} \nquery: {demisto.args().get("query")} \n{traceback.format_exc()}') 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import sql [as 别名]
def test_drop_dup_entries_in_file_conn(self):
        table_name = "__test_tmp_table__"
        tmp_db_file = self.create_tempfiles([['name', '']], ext='.sql')[0]
        in_file_engine = session.EngineFacade(
            'sqlite:///%s' % tmp_db_file).get_engine()
        meta = MetaData()
        meta.bind = in_file_engine
        test_table, values = self._populate_db_for_drop_duplicate_entries(
            in_file_engine, meta, table_name)
        utils.drop_old_duplicate_entries_from_table(
            in_file_engine, table_name, False, 'b', 'c')