Query with SQL¶
Query¶
Use sql statement to query a dtable
dtable.sqlQuery(sql)
Arguments
- sql: SQL statement to be executed
Note: By default, up to 100 results are returned. If you need more results, please add the limit parameter in the sql statement
Possible errors include
- ValueError: sql can not be empty
- ConnectionError: network error
- Exception: no such table
- Exception: no such column
- Exception: columns in group by should match columns in select
Example¶
Basic query¶
dtable.sqlQuery('select name, price, year from Bill')
result
[
{'_id': 'PzBiZklNTGiGJS-4c0_VLw', 'name': 'Bob', 'price': 300, 'year': 2019},
{'_id': 'Ep7odyv1QC2vDQR2raMvSA', 'name': 'Bob', 'price': 300, 'year': 2021},
{'_id': 'f1x3X_8uTtSDUe9D60VlYQ', 'name': 'Tom', 'price': 100, 'year': 2019},
{'_id': 'NxeaB5pDRFKOItUs_Ugxug', 'name': 'Tom', 'price': 100, 'year': 2020},
{'_id': 'W0BrjGQpSES9nfSytvXgMA', 'name': 'Tom', 'price': 200, 'year': 2021},
{'_id': 'EvwCWtX3RmKYKHQO9w2kLg', 'name': 'Jane', 'price': 200, 'year': 2020},
{'_id': 'BTiIGSTgR06UhPLhejFctA', 'name': 'Jane', 'price': 200, 'year': 2021}
]
WHERE¶
dtable.sqlQuery('select name, price from Bill where year = 2021 ')
result
[
{'_id': 'Ep7odyv1QC2vDQR2raMvSA', 'name': 'Bob', 'price': 300},
{'_id': 'W0BrjGQpSES9nfSytvXgMA', 'name': 'Tom', 'price': 200},
{'_id': 'BTiIGSTgR06UhPLhejFctA', 'name': 'Jane', 'price': 200}
]
ORDER BY¶
dtable.sqlQuery('select name, price, year from Bill order by year')
result
[
{'_id': 'PzBiZklNTGiGJS-4c0_VLw', 'name': 'Bob', 'price': 300, 'year': 2019},
{'_id': 'f1x3X_8uTtSDUe9D60VlYQ', 'name': 'Tom', 'price': 100, 'year': 2019},
{'_id': 'NxeaB5pDRFKOItUs_Ugxug', 'name': 'Tom', 'price': 100, 'year': 2020},
{'_id': 'EvwCWtX3RmKYKHQO9w2kLg', 'name': 'Jane', 'price': 200, 'year': 2020},
{'_id': 'Ep7odyv1QC2vDQR2raMvSA', 'name': 'Bob', 'price': 300, 'year': 2021},
{'_id': 'W0BrjGQpSES9nfSytvXgMA', 'name': 'Tom', 'price': 200, 'year': 2021},
{'_id': 'BTiIGSTgR06UhPLhejFctA', 'name': 'Jane', 'price': 200, 'year': 2021}
]
GROUP BY¶
dtable.sqlQuery('select name, sum(price) from Bill group by name')
result
[
{'SUM(price)': 600, 'name': 'Bob'},
{'SUM(price)': 400, 'name': 'Tom'},
{'SUM(price)': 400, 'name': 'Jane'}
]
DISTINCT¶
dtable.sqlQuery('select distinct name from Bill')
result
[
{'_id': 'PzBiZklNTGiGJS-4c0_VLw', 'name': 'Bob'},
{'_id': 'f1x3X_8uTtSDUe9D60VlYQ', 'name': 'Tom'},
{'_id': 'EvwCWtX3RmKYKHQO9w2kLg', 'name': 'Jane'}
]