Logo ROOT  
Reference Guide
sqlselect.py
Go to the documentation of this file.
1 ## \file
2 ## \ingroup tutorial_sql
3 ## \notebook -nodraw
4 ## Query example to MySQL test database.
5 ## Example of query by using the test database made in MySQL, you need the
6 ## database test installed in localhost, with user nobody without password.
7 ##
8 ## Based on sqlselect.C by Sergey Linev
9 ##
10 ## \macro_code
11 ##
12 ## \author Juan Fernando Jaramillo Botero
13 
14 from ROOT import TSQLServer, TSQLResult, TSQLRow, TStopwatch
15 
16 
17 db = TSQLServer.Connect("mysql://localhost/test", "nobody", "")
18 
19 print("Server info: %s" % db.ServerInfo())
20 
21 # list databases available on server
22 print("")
23 print("List all databases on server %s" % db.GetHost())
24 res = db.GetDataBases()
25 row = res.Next()
26 while row:
27  print("%s" % row.GetField(0))
28  row = res.Next()
29 
30 # list tables in database "test" (the permission tables)
31 print('')
32 print('List all tables in database "test" on server %s' % db.GetHost())
33 res = db.GetTables("test")
34 row = res.Next()
35 while row:
36  print("%s" % row.GetField(0))
37  row = res.Next()
38 
39 # list columns in table "runcatalog" in database "mysql"
40 print('')
41 print('List all columns in table "runcatalog" in database "test" on server %s' %
42  db.GetHost())
43 res = db.GetColumns("test", "runcatalog")
44 row = res.Next()
45 while row:
46  print("%s" % row.GetField(0))
47  row = res.Next()
48 
49 # start timer
50 timer = TStopwatch()
51 timer.Start()
52 
53 # query database and print results
54 # sql = "select dataset,rawfilepath from test.runcatalog " \
55 # "WHERE tag&(1<<2) AND (run=490001 OR run=300122)"
56 sql = "select count(*) from test.runcatalog " \
57  "WHERE tag&(1<<2)"
58 
59 res = db.Query(sql)
60 
61 nrows = res.GetRowCount()
62 print("")
63 print("Got %d rows in result" % nrows)
64 
65 nfields = res.GetFieldCount()
66 for i in range(nfields):
67  print("%40s" % res.GetFieldName(i))
68 print("")
69 print("=" * (nfields * 40))
70 print("")
71 
72 for i in range(nrows):
73  row = res.Next()
74  for j in range(nfields):
75  print("%40s" % row.GetField(j))
76  print("")
77 
78 # stop timer and print results
79 timer.Stop()
80 rtime = timer.RealTime()
81 ctime = timer.CpuTime()
82 
83 print("")
84 print("RealTime=%f seconds, CpuTime=%f seconds" % (rtime, ctime))
TSQLServer::Connect
static TSQLServer * Connect(const char *db, const char *uid, const char *pw)
The db should be of the form: <dbms>://<host>[:<port>][/<database>], e.g.
Definition: TSQLServer.cxx:61
TStopwatch
Stopwatch class.
Definition: TStopwatch.h:28