Logo ROOT  
Reference Guide
 
Loading...
Searching...
No Matches
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
14from ROOT import TSQLServer, TSQLResult, TSQLRow, TStopwatch
15
16
17db = TSQLServer.Connect("mysql://localhost/test", "nobody", "")
18
19print("Server info: %s" % db.ServerInfo())
20
21# list databases available on server
22print("")
23print("List all databases on server %s" % db.GetHost())
24res = db.GetDataBases()
25row = res.Next()
26while row:
27 print("%s" % row.GetField(0))
28 row = res.Next()
29
30# list tables in database "test" (the permission tables)
31print('')
32print('List all tables in database "test" on server %s' % db.GetHost())
33res = db.GetTables("test")
34row = res.Next()
35while row:
36 print("%s" % row.GetField(0))
37 row = res.Next()
38
39# list columns in table "runcatalog" in database "mysql"
40print('')
41print('List all columns in table "runcatalog" in database "test" on server %s' %
42 db.GetHost())
43res = db.GetColumns("test", "runcatalog")
44row = res.Next()
45while row:
46 print("%s" % row.GetField(0))
47 row = res.Next()
48
49# start timer
50timer = TStopwatch()
51timer.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)"
56sql = "select count(*) from test.runcatalog " \
57 "WHERE tag&(1<<2)"
58
59res = db.Query(sql)
60
61nrows = res.GetRowCount()
62print("")
63print("Got %d rows in result" % nrows)
64
65nfields = res.GetFieldCount()
66for i in range(nfields):
67 print("%40s" % res.GetFieldName(i))
68print("")
69print("=" * (nfields * 40))
70print("")
71
72for 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
79timer.Stop()
80rtime = timer.RealTime()
81ctime = timer.CpuTime()
82
83print("")
84print("RealTime=%f seconds, CpuTime=%f seconds" % (rtime, ctime))
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.: mysql://pcroot....
Stopwatch class.
Definition TStopwatch.h:28