<\body> \; \; R makes it easy to connect to databases of different types. Accessing a database is good because it gives you a central storage, and you can read just the parts of the data that you need. Currently the interface to databases in R is somewhat under flux. Eventually, it might be possible to just have one uniform interface to all databases. Today we will see how to access 3 types of databases: The Oracle database (Bjoern), the PostgreSQL (Johan), and Access/Excel (Windows) Basically, accessing a database involves reading and writing tables to and from the database. To access a database, we need the following parts: <\itemize-dot> connecting to a database - this will give us a variable that stores the ``connection'', with which we access everything. Seeing what tables are in the database reading a table, or part of a table through a query writing a table deleting a table disconnecting This consists of 2 steps: <\input| >> library(ROracle) <\input| >> con=dbConnect(dbDriver("Oracle"),user="lachmann",password="alexandria",dbname="TEST_DB") <\input| >> \; > Now we are connected to the database. All our commands will use . Let us see what we have in the database: <\input| >> dbListTables(con) <\input| >> \; > Let us start with simple things: <\input| >> data(ToothGrowth) <\input| >> ls() <\input| >> ToothGrowth <\input| >> \; > the data() statement (which has nothing to do with databases or Oracle) loads sample data that is provided by some libraries. This is convenient for examples. We can save a table to the database: <\input| >> dbWriteTable(con,"hamster",ToothGrowth) <\input| >> a=dbReadTable(con,"hamster") <\input| >> a <\input| >> a=dbReadTable(con,"hamster",row.names="ROW_NAMES") <\input| >> a <\input| >> \; > So, we can load and save.\ And, we can remove tables: <\input| >> dbRemoveTable(con,"hamster") <\input| >> dbListTables(con) <\input| >> \; > (I don't know why we get the above error message) This is already fairly convenient - it gives a convenient way to exchange data. <\input| >> a <\input| >> \; <\input| >> \; > One of the strength of interfacing with a database is that we can not just read a table, but format the data as a new table. For example: <\input| >> a=dbGetQuery(con,"SELECT * from hamster") <\input| >> a <\input| >> dbGetQuery(con,"select len,supp from hamster") <\input| >> dbGetQuery(con,"select len,supp,dose from hamster where dose \ 1.0") <\input| >> \; > or, something more complicated: <\input| >> class(con) <\input| >> ?dbWriteTable <\input| >> a=dbGetQuery(con,"select len from hamster") <\input| >> a <\input| >> b=data.frame(1:60,len=a$LEN,lensq=a$LEN^2); b=b[sample(1:60),] <\input| >> b <\input| >> dbWriteTable(con,"hamsq",b) <\input| >> dbGetQuery(con,"select a.dose,b.lensq,a.len,b.len from hamster a, hamsq b where a.len=b.len") <\input| >> dbRemoveTable(con,"hamsq") <\input| >> ?dbWriteTable <\input| >> \; > dbWriteTable has a flag: append, which allows us to add data to a table <\input| >> a=dbReadTable(con,"hamster",row.names="ROW_NAMES") <\input| >> a <\input| >> a2=a[1:3,] <\input| >> a2 <\input| >> rownames(a2)=100:102 <\input| >> a2 <\input| >> dbWriteTable(con,"hamster",a2,append=T) <\input| >> a=dbReadTable(con,"hamster",row.names="ROW_NAMES") <\input| >> a <\input| >> \; > If the database is very big, we might not want to load it all at once into memory. We might want to compute things slowly over it. <\input| >> x=rnorm(10000);y=1:10000 <\input| >> a=data.frame(x=x,y=y) <\input| >> dbWriteTable(con,"bigtab",a) <\output> [1] TRUE <\input| >> \; > Now assume we want to calculate the sum of all x: <\input| >> quer=dbSendQuery(con,"select * from bigtab") <\input| >> sumx=0 <\input| >> while(!dbHasCompleted(quer) ) {\ \ \ dat=fetch(quer,n=1) \ \ if( !dbHasCompleted(quer) ){ \ \ sumx = sumx+dat$X[1] \ } } <\input| >> fetch(quer,n=3,row.names="ROW_NAMES") <\input| >> \; <\input| >> dbClearResult(quer) <\input| >> sumx > You see that going over a table line by line is slow. We can do in in batches of 100: <\input| >> quer=dbSendQuery(con,"select X from bigtab") <\input| >> sumx=0 <\input| >> while(!dbHasCompleted(quer) ) {\ \ \ dat=fetch(quer,n=100) \ \ if( !dbHasCompleted(quer) ){ \ \ sumx = sumx+sum(dat$X) \ } } <\input|>> \; <\input| \ >> dbClearResult(quer) <\input| >> sumx <\input| >> \; <\input| >> \; > The command is very important. Without it, we can not submit another query. Let us see what can go wrong: <\input| >> quer=dbSendQuery(con,"select X from bigtab") <\input| >> quer=3 <\input| >> quer=dbSendQuery(con,"select X from bigtab") <\input| >> \; > So, we would like to submit another query, but we can not, and we don't have quer any more to call dbClearresult with it. There is the following convenient command: <\input| >> dbListResults(con) <\input| >> quer=dbListResults(con)[[1]] <\input| >> dbClearResult(quer) <\input| >> quer=dbSendQuery(con,"select X from bigtab") <\input| >> \; > Now all went well. <\input| >> dbClearResult(quer) <\input| >> dbListTables(con) <\input| >> dbRemoveTable(con,"hamster") <\input| >> dbRemoveTable(con,"bigtab") <\input| >> dbListTables(con) <\input| >> \; > That was nice! Now let us do it all again in PostgreSQL: <\input| >> library(RdbiPgSQL) <\input| >> con2=dbConnect( PgSQL(), host="biodb02", dbname="testdb",user="guest") <\input| >> dbListTables(con2) <\input| >> dbWriteTable(con2,name="hamster",data=ToothGrowth) <\input| >> dbListTables(con2) <\input| >> a=dbReadTable(con2,"hamster") <\input| >> a <\input| >> dbRemoveTable(con2,"hamster") <\input| >> \; > Sadly, there is no way to delete a table.... \; <\input| >> dbGetQuery(con2,"select * from hamster where dose \= 1.0") <\input| >> \; > We can seperate the query from the fetching of the result, but there is no way to slowly go over the results. <\input| >> class(con2) <\input| >> quer=dbSendQuery(con2,"select * from hamster") <\input| >> dbGetResult(quer) <\input| >> dbClearResult(quer) <\input| >> dbDisconnect(con2) <\input| >> \; > \; <\initial> <\collection> <\references> <\collection> > > > > > > > > |?>> |?>> |?>> |?>> |?>> |?>> |?>> |?>> <\auxiliary> <\collection> <\associate|toc> |Permutation tests |.>>>>|> > |math-font-series||Lecture 8 - access to databases> |.>>>>|> |Interfacing with an Oracle database |.>>>>|> > |Connecting to the database |.>>>>|> > |Simple queries |.>>>>|> > | |.>>>>|> > |Adding data to a table |.>>>>|> > |Going slowly through a query |.>>>>|> >