DATABASE_CONNECTIONS/RConnections.R at master · ParfaitG/DATABASE_CONNECTIONS · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
library(RSQLite)
library(RODBC)
library(RMySQL)

# LISTS ALL INSTALLED ODBC SOURCES (DRIVERS AND DSNs)
odbcDataSources()

#----------------------------------------FILE LEVEL DATABSES----------------------------------------#

################
# SQLITE
################
sqlite <- dbDriver("SQLite")
conn <- dbConnect(sqlite,"C:\\Path\\To\\Database\\File\\newexample.db")
df <- dbGetQuery(conn, "select * from tablename;")
dbDisconnect(conn)


################
# MS ACCESS
################

# (USING DSN)
conn <-odbcConnect('MS Access Database;
                   DBQ=C:\\Path\\To\\Database\\File.accdb')
df <- sqlQuery(conn, "select * from tablename;")
close(conn)

# (USING DRIVER)
conn <-odbcDriverConnect('driver={Microsoft Access Driver (*.mdb, *.accdb)};
                         DBQ=C:\\Path\\To\\Database\\File.accdb')
df <- sqlQuery(conn, "select * from tablename;")
close(conn)


#----------------------------------------SERVER LEVEL DATABSES----------------------------------------#

################
# MySQL
################

# USING DSN
conn <-odbcConnect('DSN Name;host=hostname;database=databasename;
                   UID=username;PWD=password')
df <- sqlQuery(conn, "select * from tablename;")
close(conn)

# USING DRIVER
conn <-odbcDriverConnect('driver={MySQL ODBC 5.3 Unicode Driver};host=hostname;database=databasename;
                         UID=username;PWD=password')
df <- sqlQuery(conn, "select * from tablename;")
close(conn)

# USING RMySQL
conn <- dbConnect(RMySQL::MySQL(), dbname="databasename", host="hostname",
                    username="username", password="password")
df <- dbGetQuery(conn, "select * from tablename;")
dbDisconnect(conn)


################
# SQL SERVER
################

# USING DSN
conn <-odbcConnect('DSN Name')
df <- sqlQuery(conn, "select * from dbo.tablename;")
close(conn)

# USING DRIVER
conn <-odbcDriverConnect('driver={SQL Server};server=servername;database=databasename;
                         trusted_connection=yes;UID=username;PWD=password')
df <-sqlQuery(conn, "select * from dbo.tablename;")
close(conn)