UC Valley: January 2015

Saturday, 24 January 2015

Java program to parse a CSV file

import java.io.FileReader;
import java.io.IOException;
import java.io.LineNumberReader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import au.com.bytecode.opencsv.CSVReader;

public class ParseCSV {

    public static void main(String[] args) throws IOException {

        String csvFile = "data1.csv";
        String keyword = "fn";
        ParseCSV firstname = new ParseCSV();
        System.out.println(firstname.ParseCSV(csvFile, keyword));
    }

    List<String> ParseCSV (String csvFile, String keyword) throws IOException {

        FileReader csvfileReader = new FileReader(csvFile);
        CSVReader csvReader = new CSVReader(csvfileReader);
        FileReader lineReader = new FileReader(csvFile);
        LineNumberReader lineNumberReader = new LineNumberReader(lineReader);
        List<String> cValue = new ArrayList<String>();

        try{
            // which column?
            String[] nextLine;
            csvReader.readNext();
            int columnNum;

            // find 1st line?
            String firstLine = null;

            while (lineNumberReader.getLineNumber() == 0){
                firstLine = lineNumberReader.readLine();
            }

            ArrayList<String> list = new ArrayList<String>(Arrays.asList(firstLine.split("\\,")));
            columnNum = list.indexOf(keyword);

            while ((nextLine = csvReader.readNext()) != null) {
                cValue.add(nextLine[columnNum]);
            }


        } finally{

            csvReader.close();
            csvfileReader.close();
            lineReader.close();
            lineNumberReader.close();

        }


        return cValue;
    }

}

=============
sample CSV file:

id,fn,ln,nation,age
1,Lokesh,Gupta,India,32
2,David,Miller,England,34

output:
[Lokesh, David]

Sunday, 18 January 2015

Internetworking error; unspecified: Finding the Top 10 Callers in your CUCM CDRs

Internetworking error; unspecified: Finding the Top 10 Callers in your CUCM CDRs: I'm traveling this week so I only have some quick SQL queries to share. You may think you need to purchase some expensive CDR analysis...

Saturday, 3 January 2015

Form in Django

1. Crate a project named csvt07 and an application called blog

Add the application to setttings.py
INSTALLED_APPS = (
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'blog',
)

2. Setup form procedures

root@10:/home/python/django/csvt07# nano blog/views.py
from django import forms
from django.http import HttpResponse
from django.shortcuts import render_to_response

class UserForm(forms.Form):
    name = forms.CharField()
    sex = forms.CharField()

def register(req):
    if req.method == 'POST':
        form = UserForm(req.POST)
        if form.is_valid():
            print form.cleaned_data
            return HttpResponse('ok')
    else:
        form = UserForm()
    return render_to_response('register.html',{'form':form})

3. create template file

root@10:/home/python/django/csvt07# nano blog/templates/register.html
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-$
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <title>Test Form</title>
</head>
<body>

<form method="post">
{{form}}
<input type="submit" value="ok" />
</form>

</body>
</html>

4. add register url

root@10:/home/python/django/csvt07# nano csvt07/urls.py
from django.conf.urls import patterns, include, url
from django.contrib import admin

urlpatterns = patterns('',
    # Examples:
    # url(r'^$', 'csvt07.views.home', name='home'),
    # url(r'^blog/', include('blog.urls')),

    url(r'^admin/', include(admin.site.urls)),
    url(r'^blog/register','blog.views.register'),
)
5. sync db
root@10:/home/python/django/csvt07# python manage.py syncdb

use admin page to config database in Django

Create a project named csvt05 and an application called blog

Add an application in settings.py

INSTALLED_APPS = (

'django.contrib.admin',

'django.contrib.auth',

'django.contrib.contenttypes',

'django.contrib.sessions',

'django.contrib.messages',

'django.contrib.staticfiles',

'blog',

)

Make sure admin url is added

root@10:/home/python/django/csvt05# nano csvt05/urls.py

from django.conf.urls import patterns, include, url

from django.contrib import admin

admin.autodiscover()

urlpatterns = patterns('',

# Examples:

# url(r'^$', 'csvt05.views.home', name='home'),

# url(r'^blog/', include('blog.urls')),

url(r'^admin/', include(admin.site.urls)),

)

Add tables

root@10:/home/python/django/csvt05# nano blog/models.py

from django.db import models

sex_choices = (

('f','female'),

('m','male'),

)

class User(models.Model):

name = models.CharField(max_length=30)

sex = models.CharField(max_length=1, choices=sex_choices)

def __unicode__(self):

return self.name

root@10:/home/python/django/csvt05# nano blog/admin.py

from django.contrib import admin

from blog.models import User

admin.site.register(User)

Sync db

root@10:/home/python/django/csvt05# python manage.py syncdb

Operations to perform:

Apply all migrations: admin, contenttypes, auth, sessions

Running migrations:

Applying contenttypes.0001_initial... OK

Applying auth.0001_initial... OK

Applying admin.0001_initial... OK

Applying sessions.0001_initial... OK

You have installed Django's auth system, and don't have any superusers defined.

Would you like to create one now? (yes/no): yes

Username (leave blank to use 'colin'): colin

Email address: colin@1.com

Password:

Password (again):

Superuser created successfully.

root@10:/home/python/django/csvt05# python manage.py migrate

root@10:/home/python/django/csvt05# python manage.py makemigrations

Test

root@10:/home/python/django/csvt05# sqlite3 db.sqlite3

SQLite version 3.7.9 2011-11-01 00:52:41

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> .tables

auth_group blog_user

auth_group_permissions django_admin_log

auth_permission django_content_type

auth_user django_migrations

auth_user_groups django_session

auth_user_user_permissions

sqlite> select * from blog_user

...> ;

1|alen|m

2|max|f

3|tom|m

Friday, 2 January 2015

Connect Mysql from Django

1. create a project:

root@10:/home/python/django# django-admin.py startproject csvt03

2. create an app

root@10:/home/python/django# cd csvt03/
root@10:/home/python/django/csvt03# django-admin.py startapp blog

3. setup connection info

root@10:/home/python/django/csvt03# nano csvt03/settings.py

INSTALLED_APPS = (
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'blog', # Add
)

#Add following:
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'csvt',
        'USER': 'root',
        'PASSWORD': 'password03',
        'HOST': '',
        'PORT': '',
    }
}

4. create database in mysql

mysql -uroot -p
create database csvt default charset=utf8;

5. create table

nano blog/models.py

#Add follwing:
from django.db import models

class Employee(models.Model):
    name = models.CharField(max_length=20)

6. synchronize to mysql

root@10:/home/python/django/csvt03# python manage.py syncdb
root@10:/home/python/django/csvt03# python manage.py migrate
root@10:/home/python/django/csvt03# manage.py makemigrations

7. ways to add data into MySQL

root@10:/home/python/django/csvt03# python manage.py shell

In [1]: from blog.models import Employee

In [2]: Employee
Out[2]: blog.models.Employee

In [3]: emp = Employee()

In [4]: emp.name = 'Alen'

In [5]: emp.save()

In [6]: emp = Employee(name='Tom')

In [8]: emp.save()

In [9]: Employee.objects.create(name='Max')
Out[9]: <Employee: Employee object>

In [11]: emp = Employee.objects.create(name='Summer')

mysql> select * from blog_employee;
+----+--------+
| id | name   |
+----+--------+
| 1 | Alen   |
| 2 | Tom    |
| 3 | Max    |
| 4 | Summer |
+----+--------+
4 rows in set (0.00 sec)

8. search database

root@10:/home/python/django/csvt03# nano blog/models.py
from django.db import models

class Employee(models.Model):
    name = models.CharField(max_length=20)

#add following:
    def __unicode__(self):
        return self.name

root@10:/home/python/django/csvt03# python manage.py shell

In [1]: from blog.models import Employee

In [2]: emps = Employee.objects.all()

In [3]: emps
Out[3]: [<Employee: Alen>, <Employee: Tom>, <Employee: Max>, <Employee: Summer>

9.display records on web

root@10:/home/python/django/csvt03# nano blog/views.py
from blog.models import Employee
from django.shortcuts import render_to_response

def index(req):
    emps = Employee.objects.all()
    return render_to_response('index.html',{'emps':emps})

root@10:/home/python/django/csvt03# nano blog/templates/index.html
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-$
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <title>Employee Name</title>
</head>
<body>
{{emps}}
</body>
</html>

root@10:/home/python/django/csvt03# nano csvt03/urls.py
from django.conf.urls import patterns, include, url

urlpatterns = patterns('',
    url(r'^index/$','blog.views.index'),
)

root@10:/home/python/django/csvt03# python manage.py runserver 0.0.0.0:8080

10. organizing outputs by using display labels

root@10:/home/python/django/csvt03# nano blog/templates/index.html
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-$
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Employee Name</title>
</head>
<body>
{% for emp in emps %}
<div>{{forloop.counter}} {{emp}}</div>
{% endfor %}
<div>{{emps|length}} records in total</div>
</body>
</html>