Convert SQLite Database into ROOT TTree

ROOT
1

Hi there. I have a program that stores data in a SQLite database file (*.sdb). Is there a straightforward way to convert this data into a TTree? The records are “flat” in the database (i.e. they could just be a spreadsheet or csv file).

I tried reading the examples about databases but they all require me to connect to a server of sorts. Nothing I found talks about just using the .sdb file itself.

Jean-François

2

Ah I was able to dump the database to a csv file and then read it back using TTree::ReadFile.

Unless there is a nicer way to do this directly, this is resolved.

3

Hi,

I am trying to do the same.

Could you share the script for reading a CSV file?

Thanks.

Garjola

4

Hi Garjola,

we have something which perfectly matches your usecase, the TDataFrame to be used in combination with the CSV data source (TCSvDS).
Here you can find a complete example which shows how to read and analyse a given CSV file: https://root.cern.ch/doc/master/tdf014__CSVDataSource_8C.html

Let us know how it goes and if you need a hand!

Cheers,
D

5

Hi,

Thanks for your reply. In the meantime, I had solved the issue like this :

  TFile f("../data/input_samples.root","RECREATE");
  TTree T("ntuple","data from ascii file");
  Long64_t nlines = T.ReadFile("../data/input_samples.csv",csv_format.c_str());
  printf(" found %lld points\n",nlines);
  T.Write();

The csv_format is a std::string which contains the names of the vars and their type (the header of the CSV file). The issue with this approach is that I can’t easily infer the type of the columns from the file (not all my columns are floating point numbers).

So I am back to the SQLite as suggested file without going through the CSV conversion. I am trying something like this:

  auto dbname = "sqlite://../data/input_samples.sqlite";
  auto username = "";
  auto userpass = "";
  auto f = new TSQLFile(dbname, "open", username, userpass);
  f->ls();

But there seems to be a problem that I don’t understand, since I get the following message when I run this code:

Error in <TSQLFile::TSQLFile>: DB sqlite://../data/input_samples.sqlite tables not exist
TSQLFile**		sqlite://../data/input_samples.sqlite	TFile interface to SQL DB
 TSQLFile*		sqlite://../data/input_samples.sqlite	TFile interface to SQL DB

I don’t think that there is a problem with my file, since the following works:


  TSQLServer *db = TSQLServer::Connect("sqlite://../data/input_samples.sqlite","", "");
  printf("Server info: %s\n", db->ServerInfo());

  TSQLRow *row;
  TSQLResult *res;

  // list tables in database "test" (the permission tables)
  printf("\nList all tables in database\n",
         db->GetHost());
  res = db->GetTables("");
  while ((row = res->Next())) {
  printf("%s\n", row->GetField(0));
  delete row;
  }
  delete res;

  auto* cols = db->GetTableInfo("selection")->GetColumns();

  for(const auto&& col : *cols) 
    {
    col->Print();
    std::cout << "Name " << col->GetName() << '\n';
    }

The col->Print() gives the correct information and col->GetName() also.

Now I am stuck retrieving the types of the columns, because

col->GetSQLType()

does not compile:

‘class TObject’ has no member named ‘GetSQLType’

and if I do:

std::cout << "SQL type " << ((TSQLColumnInfo*)col)->GetSQLType() << '\n';

I get -1; which means that something is wrong.

Any ideas?

Thanks.

Garjola.

6

Hi,

did you give a try to the TCsvDS and TDataFrame? Type inference (and safety, when it comes to the interface) are ensured there.

Cheers,
D

7

Hi,

Yes. See here. But I would really like to avoid the SQLite to CSV conversion, because it doubles the size of the file on disk.

Any idea on how to get the column types and other information in my example above?

Thanks.

Garjola

8

Hi,

I can understand that. Is it possible to have the sql file in order to reproduce the issue?

Cheers,
D

9

Hi,

A small file that I use for the tests is attached. I contains 3 tables. The one containing the data with the measures is called “selection”.

Thanks.

input_samples.sqlite.gz (85.5 KB)