Understanding the Result data type is crucial for advanced data analysis and manipulation in Polypheny notebooks. Optimized for relational data models, the Result type is versatile enough to handle various other data models as well.
Accessing Data
Let’s assume you have stored the result of the SQL query SELECT * FROM public.emps in a variable named result.
As a 2D List
You can treat the Result object like a two-dimensional list. For instance, to get the value of the element in the first row and second column, you can use:
>>> result[0][1]
'Bill'
As a List of Dictionaries
Alternatively, you can retrieve the data as a list of dictionaries, where each row becomes a dictionary with column names as keys:
>>> for employee in result.dicts():
... print(employee['name'])
Bill
Theodore
Sebastian
Eric
As Pandas DataFrame
You can also convert the Result object into a Pandas DataFrame for easier manipulation and visualization:
>>> df = result.as_df()
To quickly visualize this data, you can use:
>>> df.plot.scatter(x='salary', y='commission')
Handling Non-Relational Results
Working with non-relational data like documents or graphs may require a slightly different approach, though the basic structure remains the same.
Document Data
For example, when dealing with documents:
>>> for row in result:
... document = row[0]
Each document will already be cast to a Python dict.
Casting Types
If you encounter any uncast properties stored as strings, you can manually cast them using Python’s int(), float(), or bool() functions.
Debugging
To get an overview of the data structure, simply execute:
>>> result
This will print the result data formatted as a table.
Data Types and Mapping
Polypheny automatically maps its data types to corresponding Python types:
| Type in Polypheny | Type in Python |
|---|---|
BIGINT, INTEGER, SMALLINT, TINYINT |
int |
DECIMAL, DOUBLE, REAL |
float |
BOOLEAN |
bool |
DOCUMENT, JSON, NODE, PATH |
dict |
ARRAY |
list |
