5.3.1 SQL : Structured Query Language

library(DBI)
con <- dbConnect(RPostgres::Postgres(), dbname = 'DATABASE_NAME',
                 host = 'HOST', port = 5432, user = 'USERNAME', password = 'PASSWORD')
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
dbListTables(con)
character(0)
df <- data.frame(x = runif(25),
                 label = sample(c("A", "B"), size = 25, replace = TRUE))
dbWriteTable(con, name = "Exemple", value = df)
dbListTables(con)
[1] "Exemple"
dbListFields(con, "Exemple")
[1] "x"     "label"
res <- dbSendQuery(con, "SELECT * FROM Exemple WHERE label = 'A'")
res
<SQLiteResult>
  SQL  SELECT * FROM Exemple WHERE label = 'A'
  ROWS Fetched: 0 [incomplete]
       Changed: 0
dbClearResult(res)
res <- dbSendQuery(con, "SELECT * FROM Exemple WHERE label = 'A'")
while(!dbHasCompleted(res)){
  chunk <- dbFetch(res, n = 5)
  print(res)
  print(chunk[,2])
}
<SQLiteResult>
  SQL  SELECT * FROM Exemple WHERE label = 'A'
  ROWS Fetched: 5 [incomplete]
       Changed: 0
[1] "A" "A" "A" "A" "A"
<SQLiteResult>
  SQL  SELECT * FROM Exemple WHERE label = 'A'
  ROWS Fetched: 10 [incomplete]
       Changed: 0
[1] "A" "A" "A" "A" "A"
<SQLiteResult>
  SQL  SELECT * FROM Exemple WHERE label = 'A'
  ROWS Fetched: 14 [complete]
       Changed: 0
[1] "A" "A" "A" "A"
dbClearResult(res)
con <- dbConnect(RSQLite::SQLite(), dbname = "LEveloSTAR.sqlite3")
dbListTables(con)
character(0)
dbListFields(con, "Etat")
Error in result_create(conn@ptr, statement) : no such table: Etat

5.3.2 JSON : JavaScript Object Notation

LS0tDQp0aXRsZTogIkJhc2UgZGUgZG9ubsOpZXMiDQphdXRob3I6ICJIdXNzb24gZXQgYWwuIg0KZGF0ZTogIjA5LzA5LzIwMTgiDQpvdXRwdXQ6DQogIGh0bWxfbm90ZWJvb2s6DQogICAgdG9jOiB5ZXMNCiAgICB0b2NfZGVwdGg6IDMNCiAgICB0b2NfZmxvYXQ6IHllcw0KICBodG1sX2RvY3VtZW50Og0KICAgIHRvYzogeWVzDQogICAgdG9jX2RlcHRoOiAnMycNCiAgICB0b2NfZmxvYXQ6IHllcw0KLS0tDQoNCmBgYHtyIHNldHVwLCBpbmNsdWRlPUZBTFNFfQ0Ka25pdHI6Om9wdHNfY2h1bmskc2V0KGVjaG8gPSBUUlVFLCBjYWNoZSA9IFRSVUUpDQpgYGANCg0KIyA1LjMuMSBTUUwgOiBTdHJ1Y3R1cmVkIFF1ZXJ5IExhbmd1YWdlDQoNCmBgYHtyLGV2YWw9RkFMU0V9DQpsaWJyYXJ5KERCSSkNCmNvbiA8LSBkYkNvbm5lY3QoUlBvc3RncmVzOjpQb3N0Z3JlcygpLCBkYm5hbWUgPSAnREFUQUJBU0VfTkFNRScsDQogICAgICAgICAgICAgICAgIGhvc3QgPSAnSE9TVCcsIHBvcnQgPSA1NDMyLCB1c2VyID0gJ1VTRVJOQU1FJywgcGFzc3dvcmQgPSAnUEFTU1dPUkQnKQ0KYGBgDQoNCg0KYGBge3IsbWVzc2FnZT1GQUxTRSx3YXJuaW5nPUZBTFNFfQ0KbGlicmFyeShSU1FMaXRlKQ0KY29uIDwtIGRiQ29ubmVjdChSU1FMaXRlOjpTUUxpdGUoKSwgZGJuYW1lID0gIjptZW1vcnk6IikNCmRiTGlzdFRhYmxlcyhjb24pDQpgYGANCg0KYGBge3IsbWVzc2FnZT1GQUxTRSx3YXJuaW5nPUZBTFNFfQ0KZGYgPC0gZGF0YS5mcmFtZSh4ID0gcnVuaWYoMjUpLA0KICAgICAgICAgICAgICAgICBsYWJlbCA9IHNhbXBsZShjKCJBIiwgIkIiKSwgc2l6ZSA9IDI1LCByZXBsYWNlID0gVFJVRSkpDQpkYldyaXRlVGFibGUoY29uLCBuYW1lID0gIkV4ZW1wbGUiLCB2YWx1ZSA9IGRmKQ0KZGJMaXN0VGFibGVzKGNvbikNCmRiTGlzdEZpZWxkcyhjb24sICJFeGVtcGxlIikNCmBgYA0KDQpgYGB7cixtZXNzYWdlPUZBTFNFLHdhcm5pbmc9RkFMU0V9DQpyZXMgPC0gZGJTZW5kUXVlcnkoY29uLCAiU0VMRUNUICogRlJPTSBFeGVtcGxlIFdIRVJFIGxhYmVsID0gJ0EnIikNCnJlcw0KZGJDbGVhclJlc3VsdChyZXMpDQpgYGANCg0KYGBge3IsbWVzc2FnZT1GQUxTRSx3YXJuaW5nPUZBTFNFfQ0KcmVzIDwtIGRiU2VuZFF1ZXJ5KGNvbiwgIlNFTEVDVCAqIEZST00gRXhlbXBsZSBXSEVSRSBsYWJlbCA9ICdBJyIpDQp3aGlsZSghZGJIYXNDb21wbGV0ZWQocmVzKSl7DQogIGNodW5rIDwtIGRiRmV0Y2gocmVzLCBuID0gNSkNCiAgcHJpbnQocmVzKQ0KICBwcmludChjaHVua1ssMl0pDQp9DQpkYkNsZWFyUmVzdWx0KHJlcykNCmBgYA0KDQpgYGB7cixtZXNzYWdlPUZBTFNFLHdhcm5pbmc9RkFMU0V9DQpjb24gPC0gZGJDb25uZWN0KFJTUUxpdGU6OlNRTGl0ZSgpLCBkYm5hbWUgPSAiTEV2ZWxvU1RBUi5zcWxpdGUzIikNCmRiTGlzdFRhYmxlcyhjb24pDQpgYGANCg0KYGBge3IsbWVzc2FnZT1GQUxTRSx3YXJuaW5nPUZBTFNFfQ0KZGJMaXN0RmllbGRzKGNvbiwgIkV0YXQiKQ0KYGBgDQoNCmBgYHtyLG1lc3NhZ2U9RkFMU0Usd2FybmluZz1GQUxTRX0NCmRiTGlzdEZpZWxkcyhjb24sICJUb3BvbG9naWUiKQ0KDQpyZXMgPC0gZGJHZXRRdWVyeShjb24sDQogICJTRUxFQ1QgbGVmdC5pZCBBUyBpZCwNCiAgcmlnaHQubm9tIEFTIG5vbSwNCiAoQ09BTEVTQ0UocmlnaHQuYWRyZXNzZV9udW1lcm8sICcnKSB8fA0KICcgJyB8fA0KIENPQUxFU0NFKHJpZ2h0LmFkcmVzc2Vfdm9pZSwgJycpDQogKSBBUyBhZHJlc3NlLA0KIGxlZnQuZGlzdGFuY2UgQVMgZGlzdGFuY2UNCiBGUk9NIChTRUxFQ1QgaWQsDQogUE9XRVIoKGxhdGl0dWRlIC0gNDguMTAzNzEyKSwgMi4wKSArDQogUE9XRVIoKGxvbmdpdHVkZSArIDEuNjcyMzQyKSwgMi4wKSBBUyBkaXN0YW5jZQ0KIEZST00gRXRhdA0KIFdIRVJFICgoZXRhdCA9ICdFbiBmb25jdGlvbm5lbWVudCcpIEFORCAodmVsb3NfZGlzcG9uaWJsZXMgPiAwKSkNCiApIEFTIGxlZnQNCiBMRUZUIEpPSU4gVG9wb2xvZ2llIEFTIHJpZ2h0DQogT04gKGxlZnQuaWQgPSByaWdodC5pZCkNCiBPUkRFUiBCWSBkaXN0YW5jZQ0KIExJTUlUIDMiKQ0KDQpkYkRpc2Nvbm5lY3QoY29uKQ0KYGBgDQoNCmBgYHtyLG1lc3NhZ2U9RkFMU0Usd2FybmluZz1GQUxTRX0NCmxpYnJhcnkoZHBseXIpDQpsaWJyYXJ5KGRicGx5cikNCmNvbiA8LSBEQkk6OmRiQ29ubmVjdChSU1FMaXRlOjpTUUxpdGUoKSwgZGJuYW1lID0gIkxFdmVsb1NUQVIuc3FsaXRlMyIpDQpldGF0X2RiIDwtIHRibChjb24sICJFdGF0IikNCnRvcG9sb2dpZV9kYiA8LSB0YmwoY29uLCAiVG9wb2xvZ2llIikNCmNsYXNzKGV0YXRfZGIpDQpgYGANCg0KYGBge3J9DQpyZXEgPC0gZXRhdF9kYiAlPiUgYXJyYW5nZShsYXRpdHVkZSkgJT4lIHNlbGVjdChub20sbGF0aXR1ZGUpICU+JSBoZWFkKDIpDQpyZXENCnJlcSAlPiUgY29sbGVjdCgpDQpyZXEgJT4lIHNob3dfcXVlcnkoKQ0KYGBgDQoNCmBgYHtyfQ0KZXRhdF9kYiAlPiUNCiAgZmlsdGVyKGV0YXQ9PSJFbiBmb25jdGlvbm5lbWVudCIsIHZlbG9zX2Rpc3BvbmlibGVzPjBMKSAlPiUNCiAgbXV0YXRlKGRpc3RhbmNlMiA9IChsYXRpdHVkZS00OC4xMDM3MTIpKioyKyhsb25naXR1ZGUrMS42NzIzNDIpKioyKSAlPiUNCiAgYXJyYW5nZShkaXN0YW5jZTIpICU+JSBoZWFkKDMpICU+JSBzZWxlY3QoaWQsIGRpc3RhbmNlMikgJT4lDQogIGxlZnRfam9pbih0b3BvbG9naWVfZGIsIGJ5PWMoImlkIiA9ICJpZCIpKSAlPiUNCiAgbXV0YXRlKGFkcmVzc2UgPSBhZHJlc3NlX3ZvaWUpICU+JQ0KICBzZWxlY3QoaWQsIG5vbSwgYWRyZXNzZSwgZGlzdGFuY2UyKSAlPiUgY29sbGVjdCgpDQpgYGANCg0KIyA1LjMuMiBKU09OIDogSmF2YVNjcmlwdCBPYmplY3QgTm90YXRpb24NCg0KYGBge3IsbWVzc2FnZT1GQUxTRSx3YXJuaW5nPUZBTFNFfQ0KbGlicmFyeSh0aWR5dmVyc2UpDQpsaWJyYXJ5KGpzb25saXRlKQ0KYGBgDQpgYGB7cn0NCmRmIDwtIHRpYmJsZSgNCiAgeCA9IGMoMCwgcGkpLA0KICB5ID0gY29zKHgpDQopDQp0b0pTT04oZGYpDQpgYGANCmBgYHtyfQ0KZGYgJT4lIHRvSlNPTigpICU+JSBmcm9tSlNPTigpDQpmcm9tSlNPTignW3sieCI6MX0seyJ5IjoyfV0nKQ0KZGYxIDwtIGZyb21KU09OKCdbeyJ4IjpbMSwgMiwgM119LHsieCI6NH1dJykNCmRmMQ0KbGwxIDwtIGZyb21KU09OKCdbeyJ4IjpbMSwgMiwgM119LHsieCI6NH1dJywgc2ltcGxpZnlWZWN0b3IgPSBGQUxTRSkNCmxsMQ0KZGYyIDwtIGZyb21KU09OKCdbeyJ4Ijp7InhhIjoxLCAieGIiOjJ9fSx7IngiOjN9XScpDQpkZjINCmxsMiA8LSBmcm9tSlNPTignW3sieCI6eyJ4YSI6MSwgInhiIjoyfX0seyJ4IjozfV0nLA0KICAgICAgICAgICAgICAgICBzaW1wbGlmeURhdGFGcmFtZSA9IEZBTFNFKQ0KbGwyDQpgYGANCg0KYGBge3J9DQp1cmwgPC0gcGFzdGUwKA0KImh0dHBzOi8vZGF0YS5yZW5uZXNtZXRyb3BvbGUuZnIvYXBpL3JlY29yZHMvMS4wL3NlYXJjaC8iLA0KIj9kYXRhc2V0PWV0YXQtZGVzLXN0YXRpb25zLWxlLXZlbG8tc3Rhci1lbi10ZW1wcy1yZWVsIiwNCiImcm93cz0xMDAiLA0KIiZmYWNldD1ub20iLA0KIiZmYWNldD1ldGF0IiwNCiImZmFjZXQ9bm9tYnJlZW1wbGFjZW1lbnRzYWN0dWVscyIsDQoiJmZhY2V0PW5vbWJyZWVtcGxhY2VtZW50c2Rpc3BvbmlibGVzIiwNCiImZmFjZXQ9bm9tYnJldmVsb3NkaXNwb25pYmxlcyINCikNCmxsIDwtIGpzb25saXRlOjpmcm9tSlNPTih1cmwpDQpkZiA8LSBsbCRyZWNvcmRzJGZpZWxkcw0KYGBgDQpgYGB7cn0NCmNsYXNzKGRmKQ0KZ2xpbXBzZShkZikNCmBgYA0KDQpgYGB7cn0NCnVybCA8LSAiaHR0cDovL2plcm9lbi5naXRodWIuaW8vZGF0YS9kaWFtb25kcy5qc29uIg0KZGlhbW9uZHMgPC0ganNvbmxpdGU6OnN0cmVhbV9pbih1cmwodXJsKSkNCnVybCA8LSAiaHR0cDovL2plcm9lbi5naXRodWIuaW8vZGF0YS9ueWNmbGlnaHRzMTMuanNvbi5neiINCmZsaWdodHMgPC0ganNvbmxpdGU6OnN0cmVhbV9pbihnemNvbih1cmwodXJsKSkpDQpgYGANCg0KYGBge3IsbWVzc2FnZT1GQUxTRSx3YXJuaW5nPUZBTFNFfQ0KbGlicmFyeShtb25nb2xpdGUpDQp1c2VybmFtZSA8LSAicmVhZHdyaXRlIg0KcGFzc3dvcmQgPC0gInRlc3QiDQpob3N0IDwtICJtb25nby5vcGVuY3B1Lm9yZyINCnBvcnQgPC0gIjQzOTQyIg0KcGF0aCA8LSAiL2plcm9lbl90ZXN0Ig0KdXJsIDwtIHBhc3RlMCgNCiJtb25nb2RiOi8vIiwNCnVzZXJuYW1lLCAiOiIsIHBhc3N3b3JkLCAiQCIsDQpob3N0LCAiOiIsIHBvcnQsDQpwYXRoKQ0KbSA8LSBtb25nbygibXRjYXJzIiwgdXJsID0gdXJsKQ0KYGBgDQoNCmBgYHtyfQ0KaWYobSRjb3VudCgpID4gMCkgbSRkcm9wKCkNCm0kaW5zZXJ0KG10Y2FycykNCm0kZXhwb3J0KGZpbGUoImR1bXAuanNvbiIpKQ0KZGYgPC0gc3RyZWFtX2luKGZpbGUoImR1bXAuanNvbiIpKQ0KYGBg