Sqlselect

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

Author: Juan Fernando Jaramillo Botero
This notebook tutorial was automatically generated with ROOTBOOK-izer from the macro found in the ROOT repository on Sunday, September 20, 2020 at 10:01 AM.

In [ ]:
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

In [ ]:
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)

In [ ]:
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"

In [ ]:
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

In [ ]:
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)"

In [ ]:
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

In [ ]:
timer.Stop()
rtime = timer.RealTime()
ctime = timer.CpuTime()

print("")
print("RealTime=%f seconds, CpuTime=%f seconds" % (rtime, ctime))