Logo ROOT  
Reference Guide
 
Loading...
Searching...
No Matches
sqlselect.py File Reference

Detailed Description

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))
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
Author
Juan Fernando Jaramillo Botero

Definition in file sqlselect.py.