flytable_query performs a SQL query against a flytable
database. You can omit the base argument unless you have tables of
the same name in different bases.
Usage
flytable_list_rows(
table,
base = NULL,
view_name = NULL,
order_by = NULL,
desc = FALSE,
start = 0L,
limit = Inf,
python = FALSE,
chunksize = NULL
)
flytable_query(
sql,
limit = 100000L,
base = NULL,
python = FALSE,
convert = TRUE
)Arguments
- table
The name of a table inside your database
- base
Character vector naming a seatable base (recommended) or a
Baseobject returned byflytable_base(expert use).- view_name
An optional view which may limit the rows/columns displayed.
- order_by
Optional name of columns to order results
- desc
Whether to use descending order (default
FALSE=> ascending order)- start
Optional starting row
- limit
An optional limit, which only applies if you do not specify a limit directly in the
sqlquery. By default seatable limits SQL queries to 100 rows. We increase the limit to 100000 rows by default.- python
Whether to return a Python pandas
DataFrame. The default ofFALSEreturns an Rdata.frame- chunksize
Optional The maximum number of rows to request in one web request. For advanced use only as the default value of
NULLwill fetch as many as possible.- sql
A SQL query string. See examples and seatable docs.
- convert
Expert use only: Whether or not to allow the Python seatable module to process raw output from the database. This is is principally for debugging purposes. NB this imposes a requirement of seatable_api >=2.4.0.
Value
An R data.frame or Pandas DataFrame depending on the
value of the python argument.
a data.frame of results. There should be 0 rows if no rows
matched query.
Details
Flytable uses programmatic access to the seatable API.
See also
tabify_coords to help with copy-pasting coordinates to
seatable.
Other flytable:
flytable_list_selected(),
flytable_login(),
flytable_update_rows()
Examples
# \donttest{
flytable_list_rows(table = "testfruit")
#> _id _mtime _ctime fruit_name
#> 1 H8BckTnXRL2PaVuLDcRPMA 2022-05-12 16:58:07 2021-12-17 13:36:17 apple
#> 2 SrBW3vFLRxafKRYHoPrGQQ 2022-05-12 16:58:08 2021-12-17 13:36:17 banana
#> 3 F1h-TZKpTcWiHujZ2xnF8Q 2024-08-28 01:30:00 2021-12-17 13:36:17 clementine
#> 4 dwTLFmsDRoCxhE3BwY-ovQ 2024-10-21 19:27:38 2024-08-28 01:30:00 kiwi
#> 5 Im6VZG_DQ7mRJh6BAUwW7A 2024-08-28 01:31:13 2024-08-28 01:31:13 kiwi
#> 6 bQRjGvpVRsSwPs64A0MCvA 2024-08-28 01:32:04 2024-08-28 01:32:04 kiwi
#> 7 GbicL5tqQNaLNEQ_yM0Klw 2024-08-31 12:38:49 2024-08-31 12:38:49 kiwi
#> 8 aw-npAVQT4mntcWx7bgFWw 2024-08-31 12:38:52 2024-08-31 12:38:52 kiwi
#> 9 ZDzN99vJTg62XCg1F9l_Sg 2024-08-31 12:39:46 2024-08-31 12:39:46 kiwi
#> 10 dsuHTT-kQCK7wiMcV_TpQA 2024-08-31 12:40:00 2024-08-31 12:40:00 kiwi
#> 11 Bw_VNvoSTCurbic6P-8Wcw 2024-08-31 12:41:13 2024-08-31 12:41:13 kiwi
#> 12 OO5EnFvtR7G8YwLwaod2yw 2024-08-31 12:41:22 2024-08-31 12:41:22 kiwi
#> 13 fnSGJR-YTaiZFHIkY5-KkQ 2024-09-02 12:04:36 2024-09-02 12:04:36 kiwi
#> 14 WGgpaimYSnqad-O6_9coPw 2024-09-02 12:06:00 2024-09-02 12:06:00 kiwi
#> 15 aNnNs9VUS1CQ0xJt2x2Aow 2024-09-02 12:07:49 2024-09-02 12:07:49 kiwi
#> 16 UsmS_5PnT2i0tyQOJbRc8g 2024-09-07 09:42:06 2024-09-07 09:42:06 kiwi
#> 17 Ar7RjcG4RDahYuzCyxutwg 2024-09-07 09:42:29 2024-09-07 09:42:29 kiwi
#> 18 C8v4eN3NSKqtUgkC7FORqQ 2024-09-07 09:42:36 2024-09-07 09:42:36 kiwi
#> 19 RX1-ov-0TBCuXe1fdEZD_A 2024-09-07 10:35:19 2024-09-07 10:35:19 kiwi
#> 20 aCtWKRz7QbyLtVqvEOQjLg 2024-09-07 10:36:23 2024-09-07 10:36:23 kiwi
#> 21 B3hDsTqPSxq8XNoG-uAk5Q 2024-09-07 10:36:38 2024-09-07 10:36:38 kiwi
#> 22 WCHZt7M9Tt264ii769syvw 2024-09-07 14:32:14 2024-09-07 14:32:14 kiwi
#> 23 cVn-ZOc_RSqzxuwlumMOLQ 2024-09-07 14:33:04 2024-09-07 14:33:04 kiwi
#> 24 Xbsa6qGVSASp3QUcQx6b-Q 2024-09-07 14:35:43 2024-09-07 14:35:43 kiwi
#> 25 GeIE3dUuQsyxkR_Mjgadng 2024-09-07 16:07:22 2024-09-07 16:07:22 kiwi
#> 26 N9locli7T6uAy0lRcoWgNQ 2024-09-07 16:08:40 2024-09-07 16:08:40 kiwi
#> 27 X6bkdGHyRO6Mr4Yg2nhjXQ 2024-09-07 16:09:26 2024-09-07 16:09:26 kiwi
#> 28 X18JYP_-SSuzhrmVXvJg_g 2024-09-12 14:59:43 2024-09-12 14:59:43 kiwi
#> 29 evAYgrC4SZ6xDEXWLKsOKA 2024-09-12 16:07:35 2024-09-12 16:07:35 kiwi
#> 30 ehStvn3lSmWLIxcdhF_LYQ 2024-09-12 16:08:23 2024-09-12 16:08:23 kiwi
#> 31 fl8ki1g4QgWoCPedqKmOkA 2024-09-12 16:08:34 2024-09-12 16:08:34 kiwi
#> 32 EG-hwTJlQLKSH8FpKocPHw 2024-09-23 05:39:41 2024-09-23 05:39:41 kiwi
#> 33 FfxUUPRDRsmDo5HSOeting 2024-09-23 10:14:08 2024-09-23 10:14:08 kiwi
#> 34 c4izfXquRIyn2j8FmD0NLA 2024-10-20 22:40:48 2024-10-20 22:40:48 kiwi
#> 35 KYKhCXbyShGjb8-hvNQ3Rw 2024-10-20 22:55:47 2024-10-20 22:55:47 kiwi
#> 36 JNCpf_3xQnyvTM5QION5YQ 2024-10-20 23:06:48 2024-10-20 23:06:48 kiwi
#> 37 FVL0GzeAQhKj5TlHWm97EQ 2024-10-21 02:43:09 2024-10-21 02:43:09 kiwi
#> 38 S1VH2tR7TXe2Ttl6iP1Wyg 2024-10-21 09:27:59 2024-10-21 09:27:59 kiwi
#> 39 Ftq8Scc2R1KYgB1NCmJlJg 2024-10-21 18:00:20 2024-10-21 18:00:20 kiwi
#> 40 EWRkx7u9THK1SKFwGggtAw 2024-10-21 18:08:44 2024-10-21 18:08:44 kiwi
#> 41 BbRhVBJ8RlitYRXpF6obCA 2024-10-21 18:30:26 2024-10-21 18:30:26 kiwi
#> 42 BOH8Y1g1T0SJC4VUYHMhCQ 2024-10-21 18:36:40 2024-10-21 18:36:40 kiwi
#> 43 RF7tSp2VSeS7HVNRFJn1dw 2024-10-21 18:42:38 2024-10-21 18:42:38 kiwi
#> 44 NLwCyXxvTvyPsHmeWyLF1A 2024-10-21 18:43:47 2024-10-21 18:43:47 kiwi
#> 45 R5bOqBHJTwGzU37IO4DbWw 2024-10-21 18:44:16 2024-10-21 18:44:16 kiwi
#> 46 Pvfl8qdLQ3e9DXvfN6lYZQ 2024-10-21 18:45:31 2024-10-21 18:45:31 kiwi
#> 47 MX6L7fw9Sg69SSU96SvvYw 2024-10-21 18:48:12 2024-10-21 18:48:12 kiwi
#> 48 Pvujd_F3TWmSdys3MjEsmQ 2024-10-21 18:49:18 2024-10-21 18:49:18 kiwi
#> 49 QZc-osr8RfGumH9wrGzBPQ 2024-10-21 18:52:46 2024-10-21 18:52:46 kiwi
#> 50 L_wNAO-yQQ-Ki8r5fayk4w 2024-10-21 19:04:00 2024-10-21 19:04:00 kiwi
#> 51 ZRn0BDHsTaiEgoHqTBwJRA 2024-10-21 19:08:30 2024-10-21 19:08:30 kiwi
#> 52 LNsqDUGCQsO9XMlq6utHyg 2024-10-21 19:08:45 2024-10-21 19:08:45 kiwi
#> 53 N6QMUi1XTTWZGhFqGNPBiw 2024-10-21 19:11:30 2024-10-21 19:11:30 kiwi
#> 54 YzwemhbLSbCAY2kfj2Q9AQ 2024-10-21 19:22:52 2024-10-21 19:22:52 kiwi
#> 55 H7T6dDrDTWikKLoEqX4lgQ 2024-10-21 19:23:43 2024-10-21 19:23:43 kiwi
#> 56 fO8HjBbPQxa3DRkbpcly6A 2024-10-21 19:24:20 2024-10-21 19:24:20 kiwi
#> 57 Bal6eWgeSZS53Xvg5iP5-g 2024-10-21 19:24:40 2024-10-21 19:24:40 kiwi
#> 58 E54Y-OonQxeZqCCuU3kVyg 2024-10-21 19:27:30 2024-10-21 19:27:30 kiwi
#> 59 OI-NfQzlSPmw2biG3TC2Rg 2024-10-21 19:27:39 2024-10-21 19:27:39 kiwi
#> nid person last_modified date_nominute
#> 1 1 Alice 2022-05-12 16:58:07 2022-01-06
#> 2 2 Bob 2022-05-12 16:58:08 2022-01-03
#> 3 3 Clara 2024-08-28 01:30:00 2021-08-05
#> 4 976376 Frederick the Great 2024-10-21 19:27:38 <NA>
#> 5 7706772 Frederick the Great 2024-08-28 01:31:13 <NA>
#> 6 5105234 Frederick the Great 2024-08-28 01:32:04 <NA>
#> 7 4138893 Frederick the Great 2024-08-31 12:38:49 <NA>
#> 8 6926386 Frederick the Great 2024-08-31 12:38:52 <NA>
#> 9 6426972 Frederick the Great 2024-08-31 12:39:46 <NA>
#> 10 2384009 Frederick the Great 2024-08-31 12:40:00 <NA>
#> 11 8862077 Frederick the Great 2024-08-31 12:41:13 <NA>
#> 12 7150609 Frederick the Great 2024-08-31 12:41:22 <NA>
#> 13 9613681 Frederick the Great 2024-09-02 12:04:36 <NA>
#> 14 1634217 Frederick the Great 2024-09-02 12:06:00 <NA>
#> 15 6431695 Frederick the Great 2024-09-02 12:07:49 <NA>
#> 16 9673266 Frederick the Great 2024-09-07 09:42:06 <NA>
#> 17 4226566 Frederick the Great 2024-09-07 09:42:29 <NA>
#> 18 671154 Frederick the Great 2024-09-07 09:42:36 <NA>
#> 19 9921769 Frederick the Great 2024-09-07 10:35:19 <NA>
#> 20 173376 Frederick the Great 2024-09-07 10:36:23 <NA>
#> 21 7421177 Frederick the Great 2024-09-07 10:36:38 <NA>
#> 22 6211869 Frederick the Great 2024-09-07 14:32:14 <NA>
#> 23 9473833 Frederick the Great 2024-09-07 14:33:04 <NA>
#> 24 5196571 Frederick the Great 2024-09-07 14:35:43 <NA>
#> 25 6930475 Frederick the Great 2024-09-07 16:07:22 <NA>
#> 26 463186 Frederick the Great 2024-09-07 16:08:40 <NA>
#> 27 5047434 Frederick the Great 2024-09-07 16:09:26 <NA>
#> 28 8893869 Frederick the Great 2024-09-12 14:59:43 <NA>
#> 29 5765164 Frederick the Great 2024-09-12 16:07:35 <NA>
#> 30 4353692 Frederick the Great 2024-09-12 16:08:23 <NA>
#> 31 9891518 Frederick the Great 2024-09-12 16:08:34 <NA>
#> 32 7635402 Frederick the Great 2024-09-23 05:39:41 <NA>
#> 33 9697229 Frederick the Great 2024-09-23 10:14:08 <NA>
#> 34 8781067 Frederick the Great 2024-10-20 22:40:48 <NA>
#> 35 17840 Frederick the Great 2024-10-20 22:55:47 <NA>
#> 36 4822071 Frederick the Great 2024-10-20 23:06:48 <NA>
#> 37 5084988 Frederick the Great 2024-10-21 02:43:09 <NA>
#> 38 7358315 Frederick the Great 2024-10-21 09:27:59 <NA>
#> 39 6098978 Frederick the Great 2024-10-21 18:00:20 <NA>
#> 40 3632196 Frederick the Great 2024-10-21 18:08:44 <NA>
#> 41 7984698 Frederick the Great 2024-10-21 18:30:26 <NA>
#> 42 3310355 Frederick the Great 2024-10-21 18:36:40 <NA>
#> 43 7255713 Frederick the Great 2024-10-21 18:42:38 <NA>
#> 44 2274884 Frederick the Great 2024-10-21 18:43:47 <NA>
#> 45 5693255 Frederick the Great 2024-10-21 18:44:16 <NA>
#> 46 2906068 Frederick the Great 2024-10-21 18:45:31 <NA>
#> 47 1750299 Frederick the Great 2024-10-21 18:48:12 <NA>
#> 48 8836150 Frederick the Great 2024-10-21 18:49:18 <NA>
#> 49 2458177 Frederick the Great 2024-10-21 18:52:46 <NA>
#> 50 5772963 Frederick the Great 2024-10-21 19:04:00 <NA>
#> 51 4534791 Frederick the Great 2024-10-21 19:08:30 <NA>
#> 52 3396131 Frederick the Great 2024-10-21 19:08:45 <NA>
#> 53 4877229 Frederick the Great 2024-10-21 19:11:30 <NA>
#> 54 1434321 Frederick the Great 2024-10-21 19:22:52 <NA>
#> 55 745657 Frederick the Great 2024-10-21 19:23:43 <NA>
#> 56 6599655 Frederick the Great 2024-10-21 19:24:20 <NA>
#> 57 6948345 Frederick the Great 2024-10-21 19:24:40 <NA>
#> 58 1843960 Frederick the Great 2024-10-21 19:27:30 <NA>
#> 59 7760922 Frederick the Great 2024-10-21 19:27:39 <NA>
#> date_wminute user camid
#> 1 2022-01-12 09:30:00 8adf4f5dd661449fa6cc1f5a0b1815c0@auth.local 100001
#> 2 2022-01-03 07:56:00 c7efb8019da54923a9b04d4a74f0fde8@auth.local 100002
#> 3 2021-08-05 08:30:00 c7efb8019da54923a9b04d4a74f0fde8@auth.local 100003
#> 4 <NA> NaN 100908
#> 5 <NA> NaN 100909
#> 6 <NA> NaN 100910
#> 7 <NA> NaN 100911
#> 8 <NA> NaN 100912
#> 9 <NA> NaN 100913
#> 10 <NA> NaN 100914
#> 11 <NA> NaN 100915
#> 12 <NA> NaN 100916
#> 13 <NA> NaN 100917
#> 14 <NA> NaN 100918
#> 15 <NA> NaN 100919
#> 16 <NA> NaN 100920
#> 17 <NA> NaN 100921
#> 18 <NA> NaN 100922
#> 19 <NA> NaN 100923
#> 20 <NA> NaN 100924
#> 21 <NA> NaN 100925
#> 22 <NA> NaN 100926
#> 23 <NA> NaN 100927
#> 24 <NA> NaN 100928
#> 25 <NA> NaN 100929
#> 26 <NA> NaN 100930
#> 27 <NA> NaN 100931
#> 28 <NA> NaN 100932
#> 29 <NA> NaN 100933
#> 30 <NA> NaN 100934
#> 31 <NA> NaN 100935
#> 32 <NA> NaN 100936
#> 33 <NA> NaN 100937
#> 34 <NA> NaN 100938
#> 35 <NA> NaN 100939
#> 36 <NA> NaN 100940
#> 37 <NA> NaN 100941
#> 38 <NA> NaN 100942
#> 39 <NA> NaN 100943
#> 40 <NA> NaN 100944
#> 41 <NA> NaN 100945
#> 42 <NA> NaN 100946
#> 43 <NA> NaN 100947
#> 44 <NA> NaN 100948
#> 45 <NA> NaN 100949
#> 46 <NA> NaN 100950
#> 47 <NA> NaN 100951
#> 48 <NA> NaN 100952
#> 49 <NA> NaN 100953
#> 50 <NA> NaN 100954
#> 51 <NA> NaN 100955
#> 52 <NA> NaN 100956
#> 53 <NA> NaN 100957
#> 54 <NA> NaN 100958
#> 55 <NA> NaN 100959
#> 56 <NA> NaN 100960
#> 57 <NA> NaN 100961
#> 58 <NA> NaN 100962
#> 59 <NA> NaN 100963
# }
# \donttest{
flytable_query("SELECT person, fruit_name FROM testfruit WHERE person!='Bob'")
#> person fruit_name
#> 1 Alice apple
#> 2 Clara clementine
#> 3 Frederick the Great kiwi
#> 4 Frederick the Great kiwi
#> 5 Frederick the Great kiwi
#> 6 Frederick the Great kiwi
#> 7 Frederick the Great kiwi
#> 8 Frederick the Great kiwi
#> 9 Frederick the Great kiwi
#> 10 Frederick the Great kiwi
#> 11 Frederick the Great kiwi
#> 12 Frederick the Great kiwi
#> 13 Frederick the Great kiwi
#> 14 Frederick the Great kiwi
#> 15 Frederick the Great kiwi
#> 16 Frederick the Great kiwi
#> 17 Frederick the Great kiwi
#> 18 Frederick the Great kiwi
#> 19 Frederick the Great kiwi
#> 20 Frederick the Great kiwi
#> 21 Frederick the Great kiwi
#> 22 Frederick the Great kiwi
#> 23 Frederick the Great kiwi
#> 24 Frederick the Great kiwi
#> 25 Frederick the Great kiwi
#> 26 Frederick the Great kiwi
#> 27 Frederick the Great kiwi
#> 28 Frederick the Great kiwi
#> 29 Frederick the Great kiwi
#> 30 Frederick the Great kiwi
#> 31 Frederick the Great kiwi
#> 32 Frederick the Great kiwi
#> 33 Frederick the Great kiwi
#> 34 Frederick the Great kiwi
#> 35 Frederick the Great kiwi
#> 36 Frederick the Great kiwi
#> 37 Frederick the Great kiwi
#> 38 Frederick the Great kiwi
#> 39 Frederick the Great kiwi
#> 40 Frederick the Great kiwi
#> 41 Frederick the Great kiwi
#> 42 Frederick the Great kiwi
#> 43 Frederick the Great kiwi
#> 44 Frederick the Great kiwi
#> 45 Frederick the Great kiwi
#> 46 Frederick the Great kiwi
#> 47 Frederick the Great kiwi
#> 48 Frederick the Great kiwi
#> 49 Frederick the Great kiwi
#> 50 Frederick the Great kiwi
#> 51 Frederick the Great kiwi
#> 52 Frederick the Great kiwi
#> 53 Frederick the Great kiwi
#> 54 Frederick the Great kiwi
#> 55 Frederick the Great kiwi
#> 56 Frederick the Great kiwi
#> 57 Frederick the Great kiwi
#> 58 Frederick the Great kiwi
# }
if (FALSE) { # \dontrun{
flytable_query(paste("SELECT root_id, supervoxel_id FROM info limit 5"))
} # }