pyadomd

https://pyadomd.readthedocs.io/en/latest/
Admin
A pythonic approach to query SSAS (SQL Server Analysis Services) tabular data models with pandas...
Tokens:2,697
Snippets:68
Trust Score:9.6
Update:4 months ago
Show doc for...
Tokens:
Raw
### Integrates easily with pandas

Source: https://pyadomd.readthedocs.io/en/latest/getting_started.html

This code snippet shows how to integrate pyadomd with pandas to fetch query results into a DataFrame.

```python
from pandas import DataFrame

with Pyadomd(conn_str) as conn:
    with conn.cursor().execute(query) as cur:
        df = DataFrame(cur.fetchone(),
        columns=[i.name for i in cur.description])


```

--------------------------------

### Integrates easily with pandas

Source: https://pyadomd.readthedocs.io/en/latest/_sources/getting_started.rst.txt

Executes a query against an SSAS Tabular model and loads the results into a pandas DataFrame.

```python
from pandas import DataFrame

with Pyadomd(conn_str) as conn:
    with conn.cursor().execute(query) as cur:
        df = DataFrame(cur.fetchone(), 
        columns=[i.name for i in cur.description])
```

--------------------------------

### Query SSAS Tabular model

Source: https://pyadomd.readthedocs.io/en/latest/_sources/getting_started.rst.txt

Connects to an SSAS Tabular model, executes a query, and fetches all results.

```python
from sys import path
path.append('\Program Files\Microsoft.NET\ADOMD.NET\150')

from pyadomd import Pyadomd

conn_str = 'Provider=MSOLAP;Data Source=localhost;Catalog=AdventureWorks;'
query = """EVALUATE Product"""

with Pyadomd(conn_str) as conn:
    with conn.cursor().execute(query) as cur:
        print(cur.fetchall())
```

--------------------------------

### Query SSAS Tabular model

Source: https://pyadomd.readthedocs.io/en/latest/getting_started.html

This code snippet demonstrates how to connect to an SSAS Tabular model and execute a query using pyadomd.

```python
from sys import path
path.append('\Program Files\Microsoft.NET\ADOMD.NET\150')

from pyadomd import Pyadomd

conn_str = 'Provider=MSOLAP;Data Source=localhost;Catalog=AdventureWorks;'
query = """EVALUATE Product"""

with Pyadomd(conn_str) as conn:
    with conn.cursor().execute(query) as cur:
        print(cur.fetchall())


```

--------------------------------

### Cursor description Property

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Property to get the cursor's description.

```python
@property
def description(self) -> List[Description]:
    return self._description
```

--------------------------------

### Pyadomd Class Initialization

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Initializes the Pyadomd class with a connection string and sets up the AdomdConnection.

```python
def __init__(self, conn_str:str):
    self.conn = AdomdConnection()
    self.conn.ConnectionString = conn_str
```

--------------------------------

### Copyright and License

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Standard Apache License 2.0 header.

```python
Copyright 2020 SCOUT

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
```

--------------------------------

### Pyadomd Open Method

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Opens the connection.

```python
def open(self) -> None:
    """
    Opens the connection
    """
    self.conn.Open()
```

--------------------------------

### Cursor Class Initialization

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Initializes the Cursor class with an AdomdConnection and sets up an empty list for descriptions.

```python
def __init__(self, connection:AdomdConnection):
    self._conn = connection
    self._description:List[Description] = []
```

--------------------------------

### Cursor Execute Method

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Executes a query against the data source, creates an AdomdCommand, executes it with ExecuteReader, and populates the description based on the returned fields.

```python
def execute(self, query:str) -> Cursor:
    """
    Executes a query against the data source

    :params [query]: The query to be executed
    """
    self._cmd = AdomdCommand(query, self._conn)
    self._reader = self._cmd.ExecuteReader()
    self._field_count = self._reader.FieldCount
    
    for i in range(self._field_count):
        self._description.append(Description(
                self._reader.GetName(i), 
                adomd_type_map[self._reader.GetFieldType(i).ToString()].type_name
                ))
    return self
```

--------------------------------

### Cursor Creation

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Creates and returns a cursor object.

```python
        c = Cursor(self.conn)
        return c
```

--------------------------------

### AdomdClient Import and Error Handling

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Imports the AdomdConnection and AdomdCommand from Microsoft.AnalysisServices.AdomdClient and includes error handling for FileNotFoundException, providing guidance to the user.

```python
try:
    clr.AddReference('Microsoft.AnalysisServices.AdomdClient')
    from Microsoft.AnalysisServices.AdomdClient import AdomdConnection, AdomdCommand # type: ignore
except FileNotFoundException as e:
    print('========================================================================================')
    print(e.ToString())
    print()
    print('This error is raised when Pyadomd is not able to find the AdomdClient.dll file')
    print('The error might be solved by adding the dll to your path. ')
    print('Make sure that the dll is added, to the path, before you import Pyadomd.')
    print()
    print('If in doubt how to do that, please have a look at Getting Stated in the docs.')
    print('========================================================================================')
```

--------------------------------

### Cursor Fetchone Method

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Fetches the current line from the last executed query, yielding rows as tuples.

```python
def fetchone(self) -> Iterator[Tuple[T, ...]]:
    """
    Fetches the current line from the last executed query
    """
    while(self._reader.Read()):
        yield tuple(convert(self._reader.GetFieldType(i).ToString(), self._reader[i], adomd_type_map) for i in range(self._field_count))
```

--------------------------------

### State Property

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Returns the connection state (1 for Open, 0 for Closed).

```python
        return self.conn.State
```

--------------------------------

### Context Manager Entry

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Enters the runtime context related to this object, opening the connection.

```python
    def __enter__(self) -> Pyadomd:
        self.open()
        return self
```

--------------------------------

### Cursor Fetchall Method

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Fetches all the rows from the last executed query.

```python
def fetchall(self) -> List[Tuple[T, ...]]:
    """
    Fetches all the rows from the last executed query
    """
    # mypy issues with list comprehension :-(
    return [i for i in self.fetchone()] # type: ignore
```

--------------------------------

### Cursor Context Manager Methods

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Implements the context manager protocol for the Cursor class.

```python
def __enter__(self) -> Cursor:
    return self

def __exit__(self, exc_type, exc_value, traceback) -> None:
    self.close()
```

--------------------------------

### Pyadomd Cursor Method

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Returns a new Cursor object.

```python
def cursor(self) -> Cursor:
    """
```

--------------------------------

### Cursor Fetchmany Method

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Fetches one or more lines from the last executed query. If the requested size exceeds the available rows, it returns all remaining rows.

```python
def fetchmany(self, size=1) -> List[Tuple[T, ...]]:
    """
    Fetches one or more lines from the last executed query

    :params [size]: The number of rows to fetch. 
                    If the size parameter exceeds the number of rows returned from the last executed query then fetchmany will return all rows from that query.
    """
    l:List[Tuple[T, ...]] = []
    try:
        for i in range(size):
            l.append(next(self.fetchone()))
    except StopIteration:
        pass
    return l
```

--------------------------------

### Cursor ExecuteNonQuery Method

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Executes an Analysis Services Command against the database.

```python
def executeNonQuery(self, command:str) -> Cursor:
    """
        Executes a Analysis Services Command agains the database
        
        :params [command]: The command to be executed
    """
    self._cmd = AdomdCommand(command, self._conn)
    self._reader = self._cmd.ExecuteNonQuery()

    return self
```

--------------------------------

### Pyadomd Close Method

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Closes the connection.

```python
def close(self) -> None:
    """
    Closes the connection
    """
    self.conn.Close()
```

--------------------------------

### Context Manager Exit

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Exits the runtime context related to this object, closing the connection.

```python
    def __exit__(self, exc_type, exc_value, traceback) -> None:
        self.close()
```

--------------------------------

### Cursor Close Method

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Closes the cursor if it is not already closed.

```python
def close(self) -> None:
    """
    Closes the cursor
    """
    if self.is_closed:
        return
    self._reader.Close()
```

--------------------------------

### Cursor is_closed Property

Source: https://pyadomd.readthedocs.io/en/latest/_modules/pyadomd/pyadomd.html

Property to check if the cursor is closed.

```python
@property
def is_closed(self) -> bool:
    try:
        state = self._reader.IsClosed
    except AttributeError:
        return True        
    return state
```
Tokens:
Raw