Insert two commands (INSERT and UPDATE) into the same statement in SQLite via R Language

Question

Insert two commands (INSERT and UPDATE) into the same statement in SQLite via R Language

Navigation

#1 by (3 votes)

1

Hello, people

I have a problem with how to insert two commands (INSERT and UPDATE) into the same SQLite statement via the R language.

I do not know if this is impossible because of SQLite or the DBI package of R.

Here is an example of what I'm trying to do:

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

DBI::dbWriteTable(con, "cars_a", head(cars, 3))

DBI::dbWriteTable(con, "cars_b", head(cars, 3))

DBI::dbExecute(con, 
    "INSERT INTO cars_a
     VALUES (10, 10 );

     UPDATE cars_b
     SET dist = 400
     WHERE speed = 7;"

DBI::dbReadTable(con, "cars_a")

DBI::dbReadTable(con, "cars_b")

NOTE: Although the tables are named "cars_a" and "cars_b" interpret them as related. In my case, since I am working with a Web Scraping, INSERT is meant to aggregate data into the table with the scraped data, and UPDATE updates the request table.

After trying several forms, I was only able to execute the first SQL statement. As pictured below:

sql sqlite r

asked by anonymous 26.07.2018 / 20:20

1 answer

Access data from an object js [closed] Place a button on the input side

score 3 · Accepted Answer

Placing the two statements to be executed in the same query is probably possible, however this will not guarantee that the two operations will be performed. For even if it is sent in the same query , it will execute only one command at a time.

How to ensure that two transactions are performed in the bank?

For this you can use the Transaction (in Portuguese, transaction) that is used to ensure the integrity and reliability of the data.

How does transaction work?

The transaction has three commands:

BEGIN : Starts the transaction.

COMIT : Confirms operations performed on the bank.

ROLLBACK : Undo operations performed.

How to use?

See an example, taken from this site

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbBegin(con) # inicia a transação
withdrawal <- 5000

#realiza duas operações de update
dbExecute(con, "UPDATE cash SET amount = amount + ?", list(withdrawal)) 
dbExecute(con, "UPDATE account SET amount = amount - ?", list(withdrawal))

# realiza uma verificação
if (dbReadTable(con, "account")$amount >= 0) {
  dbCommit(con)
} else {
  dbRollback(con)
}

In the verification made, if it happens in its condition it will perform% with% confirmed% as% as it did previously, if the condition of validation is false, it will perform a dbCommit(con) undoing the UPDATE .

You can read more about SQLite with Transaction here and #