Logo ROOT  
Reference Guide
sqlselect.py File Reference

Namespaces

 sqlselect
 

Detailed Description

View in nbviewer Open in SWAN Query example to MySQL test database.

Example of query by using the test database made in MySQL, you need the database test installed in localhost, with user nobody without password.

Based on sqlselect.C by Sergey Linev

from ROOT import TSQLServer, TSQLResult, TSQLRow, TStopwatch
db = TSQLServer.Connect("mysql://localhost/test", "nobody", "")
print("Server info: %s" % db.ServerInfo())
# list databases available on server
print("")
print("List all databases on server %s" % db.GetHost())
res = db.GetDataBases()
row = res.Next()
while row:
print("%s" % row.GetField(0))
row = res.Next()
# list tables in database "test" (the permission tables)
print('')
print('List all tables in database "test" on server %s' % db.GetHost())
res = db.GetTables("test")
row = res.Next()
while row:
print("%s" % row.GetField(0))
row = res.Next()
# list columns in table "runcatalog" in database "mysql"
print('')
print('List all columns in table "runcatalog" in database "test" on server %s' %
db.GetHost())
res = db.GetColumns("test", "runcatalog")
row = res.Next()
while row:
print("%s" % row.GetField(0))
row = res.Next()
# start timer
timer = TStopwatch()
timer.Start()
# query database and print results
# sql = "select dataset,rawfilepath from test.runcatalog " \
# "WHERE tag&(1<<2) AND (run=490001 OR run=300122)"
sql = "select count(*) from test.runcatalog " \
"WHERE tag&(1<<2)"
res = db.Query(sql)
nrows = res.GetRowCount()
print("")
print("Got %d rows in result" % nrows)
nfields = res.GetFieldCount()
for i in range(nfields):
print("%40s" % res.GetFieldName(i))
print("")
print("=" * (nfields * 40))
print("")
for i in range(nrows):
row = res.Next()
for j in range(nfields):
print("%40s" % row.GetField(j))
print("")
# stop timer and print results
timer.Stop()
rtime = timer.RealTime()
ctime = timer.CpuTime()
print("")
print("RealTime=%f seconds, CpuTime=%f seconds" % (rtime, ctime))
Author
Juan Fernando Jaramillo Botero

Definition in file sqlselect.py.

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