Databases are everywhere and they have been used for many years in many areas. They are mostly irreplaceable; however, one can always find any quick and dirty alternative. Package DBI is at your service, if you have your data in one of the DBI compliant database (in most cases, yes).
This quick tutorial is consisted of two parts:
Create a database with SQLite
Although SQLite is used for an example database, the information shown in this tutorial can be still applicable for other databases.
To create a database, run the command:
The name of your database is
main.db that SQLite will open a connection if it exist, otherwise a new database with that name will be created.
All meta commands, starting with a dot (.), allow you to perform all administrative operations in the database. Here are some useful meta commands for SQLite, which all other possible commands can be seen by typing
NB If TABLE specified, SQLite will only list those.
||Turn display of headers on or off|
||Show the current values for various settings|
||List names and files of attached databases|
||List names of tables.|
||Show the CREATE statements|
The schema I keep and edit accordingly when I need a quick test:
Due to the dynamic system of SQLite, we have to define the types for each column while creating table. Check the reference table for more detailed information in affinity names.
There is two common way to create the table from your saved e.g.
test_table.sql script: First, you use meta commands by typing
.read test_table.sql after you entered your database with
sqlite3 main.db command. Second, you can run it from command line by
sqlite3 main.db < test_table.sql. I prefer second option more and that allow you to create standalone bash scripts to execute the tables if needed.
Therefore, I highly recommend to use a GUI to operate your database and I found DBeaver such a good alternative, which also supports all other common databases. Advantage of an GUI is much better in terms of the effective database management rather than dealing with the SQLite’s terminal based interface.
Connecting database with DBI package to R
dbConnect to connect your database. Be sure that you provide full path to database if your working directory is not same with the database location.
It is also possible to create an ephemeral in-memory RSQLite database by
dbname = ":memory:" argument when desire to mimic SQL syntax.
dbReadTable() to read table from database, and
dbListFields() to list field names (attributes) from table. If everything looks right, you can start sending your queries. Don’t forget to clear the result set before you fetch the same query.
However, the method shown above does not provide a high speed so that using
dbGetQuery() can be much neater which sends, fetches and clears the result in a single row.
Easily write your
data.frame class tables to the database:
If you put your SQL statements backend production, which are going to be displayed in an app (i.e. created with Shiny), you need to secure it for injection attacks. The best way to do it:
At the end, do not forget to close the connection with
At a part of your analysis with the databases, using the power of R will be a brilliant idea to carry your results beyond the expectations.