Does Linux use the ODBC method or the DBI way to install the R language extension package?

Linux system users need to install other extension packages when using R language to connect to SQL database. So, should we use ODBC to install or use DBI to install? Today, Xiaobian will give you an analysis of the difference between the two installation methods.

Data analysis often need to get data from the outside. In many cases, the data is stored in a relational database. In general, we can use SQL to extract the required data, save it as text and read it in by R. This approach combines the storage capacity of the database with the analytical capabilities of R, and is also very fast. But if you want to form a set of repetitive automated workflows, you can connect R to an external database, manipulate the database directly in R, and generate the final result, which is also a viable approach.

Connecting to the database in R requires the installation of other extension packages. Depending on the connection method, we have two options: one is ODBC, you need to install the RODBC package and install the ODBC driver. The other is the DBI mode, which installs the driver based on the type of database already installed. Because the latter retains the original features of each database, individuals prefer to use the DBI connection. There are several main packages below that provide DBI connectivity: RMySQL, RSQLite, ROracle, RPostgreSQL. It can be seen from the names that they correspond to several mainstream databases.

In these databases, SQLite is a lightweight database that is completely free, easy to use, does not require installation, does not require any configuration, and does not require an administrator. If you only need local stand-alone operation, it is very convenient to use it to access data with R. Let's take a look at an example of operating a SQLite database in R.

Starting from scratch:

1. Download SQLite, which is a separate executable file and is operated from the command line. If you are not used to installing a GUI such as sqlitebrowser. Put it in a folder first, such as sqlite.

2, execute the sql script in sqlite, create a database. Put the script file in the same folder and do the following. There should now be a database file for train.db in the folder.

.read Scheme.sql

.read Data.sql

.backup train.db

3. Install the RSQlite package in R

install.packages(‘RSQlite’)

4, load the RSQlite package, use SQL to read the data after the connection is established

library(RSQLite)

#Create Connection, pay attention to database type and file address

con "- dbConnect(‘SQLite’, dbname=‘d:/sqlite/train.db’)

# Observe three in the database Data Table

dbListTables(con)

# Observe the fields of one of the tables

dbListFields(con, ‘Apply’)

# Write a SQL Statement to select the required data

sql "- “select sName, GPA, decision,cName

from Student, Apply

where Student.sID = Apply.sID

and sizeHS 1000 and major = ‘CS’ and cName = ‘Stanford’”

# will select the data Enter data and display it at the same time

(data "- dbGetQuery(con, sql))

# Close database connection

dbDisconnect(con)

The above is the difference between using ODBC to install and using DBI to install the extension package. Users who need it can check out this article.