SQLite with R - The sqldf package

R 's data sorting functions sort and order, the data filtering function which, vector accessing operators [], vector and matrix manipulation functions cbind and rbind, and other functions and keywords make data analysis easy in much situations. SQL (Structered Querying Language) is used for storing, adding, removing, sorting and filtering the data in which saved on a disk permenantly or memory.

The R package sqldf builds a SQLite database using an R data.frame object. A data.frame is a matrix with richer properties in R.  In this blog post, we present a basic introduction of sqldf package and its use in R.

First of all, the package can be installed by typing:

> install.packages("dftable")

After installing the package, it can be got ready to use by typing:

> require("dftable")
Loading required package: sqldf
Loading required package: gsubfn
Loading required package: proto
Loading required package: RSQLite
Loading required package: DBI

Now lets create two vectors with length of 100:

> assign("x", rnorm(100))

> assign("y", rnorm(100))

> assign("mydata", as.data.frame(cbind(x,y)))

We can see first 6 rows:

> head(mydata)

           x         y

1 -1.9357660 0.2784369

2 -0.6976428 1.4646022

3  0.1913628 0.1578977

4  0.3049607 0.6055087

5  2.3773249 1.1800434

6  0.4641791 1.7143130

Let's perform some SQL statements on this data frame using sqldf: 

Averages of x and y

> sqldf("select avg(x), avg(y) from mydata")

     avg(x)   avg(y)

1 0.0790934 0.220756

Number of cases

> sqldf("select count(x), count(y) from mydata")
  count(x) count(y)
1      100      100

First Three Cases

> sqldf("select x,y from mydata limit 3")

           x         y

1 -1.9357660 0.2784369

2 -0.6976428 1.4646022

3  0.1913628 0.1578977

Minimum and Maximum Values

> sqldf("select min(x),max(x),min(y),max(y) from mydata")

     min(x)   max(x)   min(y)   max(y)

1 -2.155768 2.377325 -1.75477 2.531869

First 3 Cases of Ordered Data

> sqldf("select x,y from mydata order by x limit 3")

          x         y

1 -2.155768 0.6614813

2 -1.935766 0.2784369

3 -1.837502 0.1073177

> sqldf("select x,y from mydata order by y limit 3")

          x         y

1 0.7665811 -1.754770

2 0.3373319 -1.736727

3 0.6199159 -1.335649

Insert into 

dftable does not alter the data frame. After inserting a new case, a new data.frame is created and returned. In the example below, sqldf takes a vector of two sql statements as parameters and the result is in accessable with the name main.mydata rather than mydata. 

> tail (sqldf( 

+ c(

+ "insert into mydata values (6,7)"

+ ,

+ "select * from main.mydata"

+ )

+ )

+ )

              x          y

96   1.58024523  1.3937920

97  -1.79352203  0.2105787

98   0.02632872 -1.0567890

99  -0.60934162 -0.1359667

100  1.43393159 -0.9396326

101  6.00000000  7.0000000

Delete

> sqldf( 
+ c(
+ "delete from mydata where x < 0 or y < 0"
+ ,
+ "select * from main.mydata"
+ )
+ )
            x          y
1  0.19136277 0.15789771
2  0.30496074 0.60550873
3  2.37732485 1.18004342
4  0.46417906 1.71431305
5  1.16290585 1.17154756
6  0.49335335 0.19904607
7  1.45769371 0.08291387
8  0.78473338 1.07769098
9  0.69043300 1.35040512
10 1.47893118 1.01057351
.....


Have a nice read!

How to Connect SQLite Database in Android & A Simple App: "Accessing Data With Android Cursors"

Hi Everyone! I'd showed you how to do an application on Android in the last article. This one is going to be about connection SQLite database and access data with cursors.

If you want to use your data or something else, should connect a database. Using SQLite on Android is so simple. There are so much SQLite Editor but I will use Firefox SQLite Manager in this article. For that, first open your Firefox Browser, then download SQLite Manager and as result go this directory: Tools/SQLite Manager. After that, you can range database. 

And now, it's time to ready our files we use to. Just follow this directories:


Documents:
1- src/DatabaseActivity.java
2- src/Database.java
3- layout/data.xml

We need "database.java" file, that's why is gotta a table on database. If you want to add something, I must have a database. Now, code Database.java file.

package database.connection;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class Database extends SQLiteOpenHelper {
 private static final String MYDATABASE = "names";
 private static final int VERSION = 1;

 public Database(Context connection) {
  super(connection, MYDATABASE, null, VERSION);
 }

 @Override
 public void onCreate(SQLiteDatabase db) {
  db.execSQL("CREATE TABLE mynames(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);");
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
  db.execSQL("DROP TABLE IF EXIST mynames");
  onCreate(db);
 }
}

So, we've just created a database, as called MYDATABASE. Table's name is "names". There are two fields on it: "id and name". As you know that is all about SQL. If you know SQL, you can get it easily. id field is an integer piece of table. The other one is a text field.

We'got a database and a table of this. The form we can add data is what we need exactly. For that, gotta compose a Android XML File. This file will have a textfield widget and a button widget, that's it! Let's do data.xml file!

If you want, just look what we got up right now. We created a database, a table of this database and form widgets. I can add a data after make this platform up:) Let's do our platform: DatabaseActivity.java file

package database.connection;
//Those are included by the system 
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
 
public class DataBaseActivity extends Activity {
    private DB names;
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        setContentView(R.layout.data); //including layout/data.xml file
        names = new DB(this);
        final EditText name=(EditText) findViewById(R.id.editText1);
 
        Button senddata=(Button) findViewById(R.id.senddata);
 
        senddata.setOnClickListener(new View.OnClickListener() {
 
            public void onClick(View v) {
                    try{
                     AddDATA(name.getText().toString());
                     Cursor cursor = ShowDATA();
                     ShowDATA(cursor);
                     }
                     finally{
                     names.close();
                    }
            }
        });
 
    }
    
    private void AddDATA(String ResultName){

    SQLiteDatabase db = names.getWritableDatabase();
    ContentValues datas = new ContentValues();
    datas.put("name", ResultName);
    db.insertOrThrow("ournames", null, datas);
    }

    private String[] SELECT = {"id", "name"};

    private Cursor ShowDATA(){
    SQLiteDatabase db = names.getReadableDatabase();
    Cursor cursor = db.query("ournames", SELECT, null, null, null, null, null);

    startManagingCursor(cursor);
    return cursor;
    }

    private void ShowDATA(Cursor cursor){
        StringBuilder builder = new StringBuilder("RESULTS!:\n");

        while(cursor.moveToNext()){

        String whatthenameis = cursor.getString((cursor.getColumnIndex("name")));
        builder.append(whatthenameis).append("\n");
        }

        TextView text = (TextView)findViewById(R.id.textView1);
        text.setText(builder);
    }
}

Generally, coder need to use a database, while saving data. We use SQLite Database on Android'cuz it's simple. Here, SQL is as you know before. "SELECT" command using also. By the way, if you want to check your db file out, just go this directory on Eclipse: file explorer/data/[your project name]/database

Write Something

That's it! We can add and save our datas, through SQLite Database on Android.
When you write something and click button, data will been saved and can show it on the screen. We'll you guys next article!