Python sqlalchemy.engine.result.ResultProxy() Examples

def _formatter_data(res):
    """
    sqlalchemy.engine.result.ResultProxy 对象数据提取

    res.cursor._rows   # 数据
    res._metadata.keys 或 res.cursor.description # 数据库表字段名
    :param res:
    :return: list
    """
    assert isinstance(res, ResultProxy)
    assert res.returns_rows
    rows = []
    for _row in res.cursor._rows:
        row = {}
        for index, column in enumerate(res._metadata.keys):
            row[column] = _row[index]
        rows.append(row)
    return rows 

def get_result_proxy(self):
        if hasattr(self.compiled, 'returning_parameters'):
            rrs = None
            try:
                try:
                    rrs = self.statement.__statement__.getReturnResultSet()
                    next(rrs)
                except SQLException as sqle:
                    msg = '%s [SQLCode: %d]' % (sqle.getMessage(), sqle.getErrorCode())
                    if sqle.getSQLState() is not None:
                        msg += ' [SQLState: %s]' % sqle.getSQLState()
                    raise zxJDBC.Error(msg)
                else:
                    row = tuple(self.cursor.datahandler.getPyObject(rrs, index, dbtype)
                                for index, dbtype in self.compiled.returning_parameters)
                    return ReturningResultProxy(self, row)
            finally:
                if rrs is not None:
                    try:
                        rrs.close()
                    except SQLException:
                        pass
                self.statement.close()

        return _result.ResultProxy(self) 

def from_everything(everything, engine, limit=None):
    """
    Construct a Prettytable from any kinds of sqlalchemy query.
    """
    if isinstance(everything, Table):
        return from_table(everything, engine, limit=limit)

    if type(everything) is DeclarativeMeta:
        return from_object(everything, engine, limit=limit)

    if isinstance(everything, Query):
        return from_query(everything, engine, limit=limit)

    if isinstance(everything, Select):
        return from_sql(everything, engine, limit=limit)

    if isinstance(everything, ResultProxy):
        return from_resultproxy(everything)

    if isinstance(everything, list):
        return from_data(everything) 

def from_sql(sql, engine, limit=None):
    """
    Create a :class:`prettytable.PrettyTable` from :class:`sqlalchemy.select`.

    :param sql: a ``sqlalchemy.sql.selectable.Select`` object.
    :param engine: an ``sqlalchemy.engine.base.Engine`` object.
    :param limit: int, limit rows to return.

    **中文文档**

    将sqlalchemy的sql expression query结果放入prettytable中.

    .. note::

        注意, from_db_cursor是从原生的数据库游标通过调用fetchall()方法来获取数据。
        而sqlalchemy返回的是ResultProxy类。所以我们需要从中获取游标
        至于为什么不能直接使用 from_db_cursor(engine.execute(sql).cursor) 的语法
        我也不知道为什么.
    """
    if limit is not None:
        sql = sql.limit(limit)
    result_proxy = engine.execute(sql)
    return from_db_cursor(result_proxy.cursor) 

def execute_query(instance: PluginInstance, sql_query: Union[str, Query],
                  rows_as_dict: bool = False) -> web.Response:
    try:
        res: ResultProxy = instance.inst_db.execute(sql_query)
    except exc.IntegrityError as e:
        return resp.sql_integrity_error(e, sql_query)
    except exc.OperationalError as e:
        return resp.sql_operational_error(e, sql_query)
    data = {
        "ok": True,
        "query": str(sql_query),
    }
    if res.returns_rows:
        row: RowProxy
        data["rows"] = [({key: check_type(value) for key, value in row.items()}
                         if rows_as_dict
                         else [check_type(value) for value in row])
                        for row in res]
        data["columns"] = res.keys()
    else:
        data["rowcount"] = res.rowcount
    if res.is_insert:
        data["inserted_primary_key"] = res.inserted_primary_key
    return web.json_response(data) 

def get_result_proxy(self):
        if hasattr(self.compiled, 'returning_parameters'):
            rrs = None
            try:
                try:
                    rrs = self.statement.__statement__.getReturnResultSet()
                    next(rrs)
                except SQLException as sqle:
                    msg = '%s [SQLCode: %d]' % (
                        sqle.getMessage(), sqle.getErrorCode())
                    if sqle.getSQLState() is not None:
                        msg += ' [SQLState: %s]' % sqle.getSQLState()
                    raise zxJDBC.Error(msg)
                else:
                    row = tuple(
                        self.cursor.datahandler.getPyObject(
                            rrs, index, dbtype)
                        for index, dbtype in
                        self.compiled.returning_parameters)
                    return ReturningResultProxy(self, row)
            finally:
                if rrs is not None:
                    try:
                        rrs.close()
                    except SQLException:
                        pass
                self.statement.close()

        return _result.ResultProxy(self) 

def get_result_proxy(self):
        if hasattr(self.compiled, 'returning_parameters'):
            rrs = None
            try:
                try:
                    rrs = self.statement.__statement__.getReturnResultSet()
                    next(rrs)
                except SQLException as sqle:
                    msg = '%s [SQLCode: %d]' % (
                        sqle.getMessage(), sqle.getErrorCode())
                    if sqle.getSQLState() is not None:
                        msg += ' [SQLState: %s]' % sqle.getSQLState()
                    raise zxJDBC.Error(msg)
                else:
                    row = tuple(
                        self.cursor.datahandler.getPyObject(
                            rrs, index, dbtype)
                        for index, dbtype in
                        self.compiled.returning_parameters)
                    return ReturningResultProxy(self, row)
            finally:
                if rrs is not None:
                    try:
                        rrs.close()
                    except SQLException:
                        pass
                self.statement.close()

        return _result.ResultProxy(self) 

def from_resultproxy(result_proxy):
    """
    Construct a Prettytable from ``ResultProxy``.

    :param result_proxy: a ``sqlalchemy.engine.result.ResultProxy`` object.
    """
    return from_db_cursor(result_proxy.cursor) 

def get_result_proxy(self):
        if hasattr(self.compiled, 'returning_parameters'):
            rrs = None
            try:
                try:
                    rrs = self.statement.__statement__.getReturnResultSet()
                    next(rrs)
                except SQLException as sqle:
                    msg = '%s [SQLCode: %d]' % (
                        sqle.getMessage(), sqle.getErrorCode())
                    if sqle.getSQLState() is not None:
                        msg += ' [SQLState: %s]' % sqle.getSQLState()
                    raise zxJDBC.Error(msg)
                else:
                    row = tuple(
                        self.cursor.datahandler.getPyObject(
                            rrs, index, dbtype)
                        for index, dbtype in
                        self.compiled.returning_parameters)
                    return ReturningResultProxy(self, row)
            finally:
                if rrs is not None:
                    try:
                        rrs.close()
                    except SQLException:
                        pass
                self.statement.close()

        return _result.ResultProxy(self) 

def get_result_proxy(self):
        if hasattr(self.compiled, 'returning_parameters'):
            rrs = None
            try:
                try:
                    rrs = self.statement.__statement__.getReturnResultSet()
                    next(rrs)
                except SQLException as sqle:
                    msg = '%s [SQLCode: %d]' % (
                        sqle.getMessage(), sqle.getErrorCode())
                    if sqle.getSQLState() is not None:
                        msg += ' [SQLState: %s]' % sqle.getSQLState()
                    raise zxJDBC.Error(msg)
                else:
                    row = tuple(
                        self.cursor.datahandler.getPyObject(
                            rrs, index, dbtype)
                        for index, dbtype in
                        self.compiled.returning_parameters)
                    return ReturningResultProxy(self, row)
            finally:
                if rrs is not None:
                    try:
                        rrs.close()
                    except SQLException:
                        pass
                self.statement.close()

        return _result.ResultProxy(self) 

def get_result_proxy(self):
        if hasattr(self.compiled, 'returning_parameters'):
            rrs = None
            try:
                try:
                    rrs = self.statement.__statement__.getReturnResultSet()
                    next(rrs)
                except SQLException as sqle:
                    msg = '%s [SQLCode: %d]' % (
                        sqle.getMessage(), sqle.getErrorCode())
                    if sqle.getSQLState() is not None:
                        msg += ' [SQLState: %s]' % sqle.getSQLState()
                    raise zxJDBC.Error(msg)
                else:
                    row = tuple(
                        self.cursor.datahandler.getPyObject(
                            rrs, index, dbtype)
                        for index, dbtype in
                        self.compiled.returning_parameters)
                    return ReturningResultProxy(self, row)
            finally:
                if rrs is not None:
                    try:
                        rrs.close()
                    except SQLException:
                        pass
                self.statement.close()

        return _result.ResultProxy(self) 

def _all(cls: Type[T], rows: ResultProxy) -> Iterator[T]:
        """
        Scan all rows from a ResultProxy.

        Args:
            rows: The SQLAlchemy result to scan.

        Yields:
            Each row scanned with :meth:`scan`
        """
        for row in rows:
            yield cls.scan(row) 

def _one_or_none(cls: Type[T], rows: ResultProxy) -> Optional[T]:
        """
        Try scanning one row from a ResultProxy and return ``None`` if it fails.

        Args:
            rows: The SQLAlchemy result to scan.

        Returns:
            The scanned object, or ``None`` if there were no rows.
        """
        try:
            return cls.scan(next(rows))
        except StopIteration:
            return None 

def get_result_proxy(self):
        if hasattr(self.compiled, 'returning_parameters'):
            rrs = None
            try:
                try:
                    rrs = self.statement.__statement__.getReturnResultSet()
                    next(rrs)
                except SQLException as sqle:
                    msg = '%s [SQLCode: %d]' % (
                        sqle.getMessage(), sqle.getErrorCode())
                    if sqle.getSQLState() is not None:
                        msg += ' [SQLState: %s]' % sqle.getSQLState()
                    raise zxJDBC.Error(msg)
                else:
                    row = tuple(
                        self.cursor.datahandler.getPyObject(
                            rrs, index, dbtype)
                        for index, dbtype in
                        self.compiled.returning_parameters)
                    return ReturningResultProxy(self, row)
            finally:
                if rrs is not None:
                    try:
                        rrs.close()
                    except SQLException:
                        pass
                self.statement.close()

        return _result.ResultProxy(self) 

def _execute_success(res):
    """
    sqlalchemy.engine.result.ResultProxy 数据库修改状态

    res.returns_rows   # 是否返回数据
    res.rowcount 是否执行成功 1 success,0 error
    :param res:
    :return: boolean
    """
    assert isinstance(res, ResultProxy)
    return res.rowcount > 0 

def get_result_proxy(self):
        if hasattr(self.compiled, 'returning_parameters'):
            rrs = None
            try:
                try:
                    rrs = self.statement.__statement__.getReturnResultSet()
                    next(rrs)
                except SQLException as sqle:
                    msg = '%s [SQLCode: %d]' % (
                        sqle.getMessage(), sqle.getErrorCode())
                    if sqle.getSQLState() is not None:
                        msg += ' [SQLState: %s]' % sqle.getSQLState()
                    raise zxJDBC.Error(msg)
                else:
                    row = tuple(
                        self.cursor.datahandler.getPyObject(
                            rrs, index, dbtype)
                        for index, dbtype in
                        self.compiled.returning_parameters)
                    return ReturningResultProxy(self, row)
            finally:
                if rrs is not None:
                    try:
                        rrs.close()
                    except SQLException:
                        pass
                self.statement.close()

        return _result.ResultProxy(self) 

def default(self, obj):
        '''
        Converts an object and returns a ``JSON``-friendly structure.

        :param obj: object or structure to be converted into a
                    ``JSON``-ifiable structure

        Considers the following special cases in order:

        * object has a callable __json__() attribute defined
            returns the result of the call to __json__()
        * date and datetime objects
            returns the object cast to str
        * Decimal objects
            returns the object cast to float
        * SQLAlchemy objects
            returns a copy of the object.__dict__ with internal SQLAlchemy
            parameters removed
        * SQLAlchemy ResultProxy objects
            Casts the iterable ResultProxy into a list of tuples containing
            the entire resultset data, returns the list in a dictionary
            along with the resultset "row" count.

            .. note:: {'count': 5, 'rows': [('Ed Jones',), ('Pete Jones',),
                ('Wendy Williams',), ('Mary Contrary',), ('Fred Smith',)]}

        * SQLAlchemy RowProxy objects
            Casts the RowProxy cursor object into a dictionary, probably
            losing its ordered dictionary behavior in the process but
            making it JSON-friendly.
        * webob_dicts objects
            returns webob_dicts.mixed() dictionary, which is guaranteed
            to be JSON-friendly.
        '''
        if hasattr(obj, '__json__') and six.callable(obj.__json__):
            return obj.__json__()
        elif isinstance(obj, (date, datetime)):
            return str(obj)
        elif isinstance(obj, Decimal):
            # XXX What to do about JSONEncoder crappy handling of Decimals?
            # SimpleJSON has better Decimal encoding than the std lib
            # but only in recent versions
            return float(obj)
        elif is_saobject(obj):
            props = {}
            for key in obj.__dict__:
                if not key.startswith('_sa_'):
                    props[key] = getattr(obj, key)
            return props
        elif isinstance(obj, ResultProxy):
            props = dict(rows=list(obj), count=obj.rowcount)
            if props['count'] < 0:
                props['count'] = len(props['rows'])
            return props
        elif isinstance(obj, RowProxy):
            return dict(obj)
        elif isinstance(obj, webob_dicts):
            return obj.mixed()
        else:
            return JSONEncoder.default(self, obj)