This article introduces a MySQL access library soci and mysqlsoci for C ++ programs.

This article introduces a MySQL access library soci and mysqlsoci for C ++ programs.

For a long time, I have been constantly looking for a more user-friendly database access method (not to say that the default method is not good, but sometimes it is not very convenient in modular design ).
Later, I was lucky enough to find the ActiveReord of codeigniter in php. For details, refer to this article: getting away from CodeIgniter's database category class!
However, c ++ has always been using the most primitive method. Yesterday, taking advantage of the project's opportunities, I searched the internet for a long time and finally found two c ++ database access frameworks:

Soci
Litesql

Let me take a look at both sets of code. litesql implements a complete set of code automatically generated, which is powerful, but also very heavy; soci is relatively lightweight, however, the ing between data structures and database tables is also realized. I still like simple things, so I finally chose soci. After the two-day trial, I feel very good.

The official documentation is also very detailed, so here I will use the unit test code to give a simple explanation:
First, create a database table:

create database soci;CREATE TABLE `tb_test` ( `id` int(11) NOT NULL auto_increment, `name` varchar(32) default "", `sex` int(11) default 0, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`)); create database soci;CREATE TABLE `tb_test` ( `id` int(11) NOT NULL auto_increment, `name` varchar(32) default "", `sex` int(11) default 0, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`));

1. Simple select single record

TEST(soci,select_one){  try  {    session sql(mysql, "host=localhost db=soci user=dantezhu");    indicator ind;    string name = "dandan";    int sex;    sql << "select sex from tb_test where name = :name",      into(sex, ind), use(name);    ASSERT_EQ(ind, i_ok) << name;  }  catch (exception const &e)  {    FAIL()<<e.what();  }} TEST(soci,select_one){  try  {    session sql(mysql, "host=localhost db=soci user=dantezhu");    indicator ind;     string name = "dandan";    int sex;    sql << "select sex from tb_test where name = :name",      into(sex, ind), use(name);     ASSERT_EQ(ind, i_ok) << name;  }  catch (exception const &e)  {    FAIL()<<e.what();  }}

If the select result is successful, ind will be I _ OK, and the same value of sex will be assigned; otherwise

2. Simple select multiple records

TEST(soci,select_multi2){  try  {    session sql(mysql, "db=soci user=dantezhu");    indicator ind;    int count;    sql << "select count(*) from tb_test", into(count, ind);    ASSERT_EQ(ind, i_ok) << count;    if (count == 0)    {      SUCCEED();      return;    }    int sex = 1;    vector<string> vec_name(count);    vector<int> vec_sex(count);    sql << "select name,sex from tb_test where sex = :sex",      into(vec_name), into(vec_sex), use(sex);  }  catch (exception const &e)  {    FAIL()<<e.what();  }} TEST(soci,select_multi2){  try  {    session sql(mysql, "db=soci user=dantezhu");    indicator ind;     int count;    sql << "select count(*) from tb_test", into(count, ind);    ASSERT_EQ(ind, i_ok) << count;     if (count == 0)    {      SUCCEED();      return;    }     int sex = 1;    vector<string> vec_name(count);    vector<int> vec_sex(count);    sql << "select name,sex from tb_test where sex = :sex",      into(vec_name), into(vec_sex), use(sex);  }  catch (exception const &e)  {    FAIL()<<e.what();  }}

The only difference from a single select record is that the into () parameter is a vector. In fact, using multiple vectors is not a good choice. We will introduce the data structure-based method later.

3. Simple insert

TEST(soci,insert_exist){  try  {    session sql(mysql, "db=soci user=dantezhu");    string name = "dandan";    int sex = 1;    sql << "insert into tb_test(name, sex) values(:name, :sex)",      use(name), use(sex);  }  catch (exception const &e)  {    SUCCEED()<<e.what();  }} TEST(soci,insert_exist){  try  {    session sql(mysql, "db=soci user=dantezhu");     string name = "dandan";    int sex = 1;     sql << "insert into tb_test(name, sex) values(:name, :sex)",      use(name), use(sex);  }  catch (exception const &e)  {    SUCCEED()<<e.what();  }}

Insert, update, and delete both have the same problem:
A) affect_rows (number of rows in the operation) cannot be returned
B) The operation id cannot be known, especially when the insert primary key is auto-incrementing, the value of the inserted primary key cannot be known.

The update and delete operations are similar to insert operations.

Next is an important feature of this framework, that is, binding database tables to data structures:

First, we need to define a struct and tell soci how to match the column name with the data structure field:

struct Person{  int id;  std::string name;  int sex;};namespace soci{  template<> struct type_conversion<Person>  {    typedef values base_type;    static void from_base(values const & v, indicator /* ind */, Person & p)    {      p.id = v.get<int>("id");      p.name = v.get<std::string>("name");      p.sex = v.get<int>("sex");    }    static void to_base(const Person & p, values & v, indicator & ind)    {      v.set("id", p.id);      v.set("name", p.name);      v.set("sex", p.sex);      ind = i_ok;    }  };} struct Person{  int id;  std::string name;  int sex;}; namespace soci{  template<> struct type_conversion<Person>  {    typedef values base_type;    static void from_base(values const & v, indicator /* ind */, Person & p)    {      p.id = v.get<int>("id");      p.name = v.get<std::string>("name");      p.sex = v.get<int>("sex");    }    static void to_base(const Person & p, values & v, indicator & ind)    {      v.set("id", p.id);      v.set("name", p.name);      v.set("sex", p.sex);      ind = i_ok;    }  };}

About

template<> struct type_conversion<Person> template<> struct type_conversion<Person>

Here, the official document is incorrect. I checked it for a long time and wrote it according to the above method.

1. Use the data structure to select

TEST(soci,select_obj_one){  try  {    session sql(mysql, "db=soci user=dantezhu");    indicator ind;    int count;    sql << "select count(*) from tb_test", into(count, ind);    ASSERT_EQ(ind, i_ok) << count;    string name = "dandan";    Person p;    sql << "select id,name,sex from tb_test where name = :name",      into(p, ind), use(name);    ASSERT_EQ(ind, i_ok) << name;    if (sql.got_data())    {      cout<< p.id         << ","         << p.name         << ","         << p.sex         << endl;    }  }  catch (exception const &e)  {    FAIL()<<e.what();  }} TEST(soci,select_obj_one){  try  {    session sql(mysql, "db=soci user=dantezhu");    indicator ind;     int count;    sql << "select count(*) from tb_test", into(count, ind);    ASSERT_EQ(ind, i_ok) << count;     string name = "dandan";    Person p;    sql << "select id,name,sex from tb_test where name = :name",      into(p, ind), use(name);     ASSERT_EQ(ind, i_ok) << name;     if (sql.got_data())    {      cout<< p.id         << ","         << p.name         << ","         << p.sex         << endl;    }   }  catch (exception const &e)  {    FAIL()<<e.what();  }}

2. insert with Data Structure

TEST(soci,insert_obj_noexist){  try  {    session sql(mysql, "db=soci user=dantezhu");    Person p = {      0,      "niuniu",      2    };    sql << "insert into tb_test(name, sex) values(:name, :sex)",      use(p);  }  catch (exception const &e)  {    FAIL()<<e.what();  }} TEST(soci,insert_obj_noexist){  try  {    session sql(mysql, "db=soci user=dantezhu");     Person p = {      0,      "niuniu",      2    };     sql << "insert into tb_test(name, sex) values(:name, :sex)",      use(p);  }  catch (exception const &e)  {    FAIL()<<e.what();  }}

This is the case ~~ The download path of the code file is as follows:
Http://code.google.com/p/vimercode/source/browse/#svn%2Ftrunk%2Fsoci_test

In addition, although mysql access in python is relatively simple, I still want to know if there is a more Pythonic library or interface. If you have any friends, please do not hesitate to inform me.