Raw Sql query to fetch mysql table row count using beego ORM

Raw Sql query to fetch mysql table row count using beego ORM

import package beego orm

package main

import "github.com/astaxie/beego/orm"

We have to initialize the mysql connection, for that we have to import the mysql dapter for golang

add  `_ "github.com/go-sql-driver/mysql"` to the import statement

let us initialize the connection

func init() {
  orm.RegisterDriver("mysql", orm.DR_MySQL)
  orm.RegisterDataBase("default", "mysql", "mysql_username:mysql_password@/db_name?charset=utf8")
}

We register the mysql Adapter here, and then Register Database

Couple of things to note,

1. We need to have a “default” connection,

2. database has to be specified again, here it is “mysql”

3. “mysql_username:mysql_password@/db_name” replace them with your credentials. You are running it on local machine and the charset is utf8 is my assumtion (just keeping the scope limited)

Lets say you have a Table called Visits with columns id, name

So we create a struct, that will act as the Model for the table

type Visits struct {
  Id int
  Name string
}

Now we have to register the Model to the ORM

in init function, we add `orm.RegisterModel(new(Visits))` after the existing lines of code.

Time to fetch some data,

create your main function

function main() {
  o := orm.NewOrm()
  o.Using("default")
}

you create an ORM instance, tell it which config to use. We have created a default one. This means you can define multiple databases

The query we want data from is

select count(*) as Count from visits where name  = 'name';

and the return has to be an integer value, the count

lets create the call, and pass the value for name as a parameter, so we can show some runtime control over the query.

  var count int
  o.Raw("select count(*) as Count from visits where name = ?", 'plant').QueryRow(&count)

add this to your main function and print the value, you will have got it!

your whole code look something like this

package main

import (
    "fmt"
    "github.com/astaxie/beego/orm"
    _ "github.com/go-sql-driver/mysql"
)

func init() {
    orm.RegisterDriver("mysql", orm.DR_MySQL)
    orm.RegisterDataBase("default", "mysql", "mysql_username:mysql_password@/database_name?charset=utf8")

    orm.RegisterModel(new(Visits))
}

type Visits struct {
    Id   int
    Name string
}

func main() {
    o := orm.NewOrm()
    o.Using("default")

    var count int
    o.Raw("select count(*) as Count from visits where name = ?", "1").QueryRow(&count)

    fmt.Println(count)
}

get the code here: https://gist.github.com/sumitasok/bfaa8a7194d3532878e9#file-orm_raw_query_integer-go

couple of things, for using beego/orm you have to get it. Run go get github.com/astaxie/beego on your console

and to use println, import fmt package.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s