Dear ALL,
Enclosed is a Python scripts to put EPICS database information in .db files
to PostgreSQL RDB.
In the EPICS meeting at KEK on May,2000, Mr. Ron Chestnut showed us how to
put EPICS record definitions on .db files into ORACLE RDB and how useful
it is.
[see http://www-acc.kek.jp/EPICS_meeting/Presentations/NLC/DB2ORA.pdf
for his presentation.]
Ron kindly sent me his scripts to create tables on ORACLE and to load
data from
.db file to ORACLE tables. Ron kindly sent me his scripts and I adopted
it to
Python/PosgreSQL combination.
Here is an example session using this python script.
#You can get information on the field of table as a dictionary in Python.
>>> from pvlistDB import *
>>> pvl=pvlist()
>>> pvl.get_attnames("pvlist")
{'fld5': 'text', 'nfld': 'int', 'pv_last_field': 'text', 'pv_type': 'text',
'pv_inout': 'text', 'fld1': 'text', 'fld3': 'text', 'fld2': 'text',
'pv_name': 'text', 'fld4': 'text', 'fld6': 'text', 'pv_desc': 'text'}
#You can execute an SQL statement using query() function call.
>>> pvl.query("select count(*) from pvlist")
count
-----
7397
(1 row)
# A regular expression can be used in a SQL statement.
>>> result=pvl.query("select pv_name from pvlist where pv_name ~ '^MGH.+RE'")
>>> result.ntuples()
2220
>>> data=result.getresult() # get data from all selected rows.
You can get PostgreSQL , Python and PyGreSQL( Python interface module
for PostgreSQL) through
network without additional fee.
Versions of software I used are:
Python 1.5.2 ( http://www.python.org ) [ Python 2.x should work with
this script, but I didn't tried it yet.]
PostgreSQL version 7.0.3 ( http://www.postgresql.org )
and
PyGreSQL-3.1 ( ftp://ftp.druid.net/pub/distrib/PyGreSQL.tgz )
on HP-UX 10.2 (PostgreSQL server and client) and OSF1 V5.1 (PostgreSQL
client side only).
Bye.
Noboru Yamamoto
KEKB control group
KEK, JAPAN#!python
"""
Creating pvlist table in RDB(oracl or PostgreSQL)
Based on the work by Ron Chestnut , SLAC
"""
import pg
import string,time,sys,os,traceback
def Env(**kw):
return kw
class pvlist(pg.DB):
_dbname="yourdatabasename"
_tablename="pvlist"
_host="yourhostname"
def __init__(self,host="abco1"):
pg.DB.__init__(self, self._dbname,host)
def create_table(self):
try:
self.query("drop table %s ;"%self._tablename)
except:
traceback.print_exc()
try:
self.query("drop table pv_gt ;")
except:
traceback.print_exc()
self.query("create table %s"\
"(pv_type varchar(15), pv_name varchar(30), "\
"pv_inout varchar(35), pv_desc varchar(20)"\
"); "%self._tablename
)
self.query("create table pv_gt (gtyp varchar(15));")
#/* List of legal PV types for DCXP_MAKE_UBLK_EPICS.PC */
self.query("insert into pv_gt values('ai');")
self.query("insert into pv_gt values('ao');")
self.query("insert into pv_gt values('longin');")
self.query("insert into pv_gt values('longout');")
self.query("insert into pv_gt values('calc');")
self.query("insert into pv_gt values('bi');")
self.query("insert into pv_gt values('bo');")
self.query("insert into pv_gt values('mbbo');")
self.query("insert into pv_gt values('mbbi');")
self.query("insert into pv_gt values('sub');")
self.query("insert into pv_gt values('sel');")
self.query("insert into pv_gt values('p2RfAim');")
def amend(self):
self.query("alter table %s add fld1 varchar(15);"%self._tablename)
self.query("alter table %s add fld2 varchar(15);"%self._tablename)
self.query("alter table %s add fld3 varchar(15);"%self._tablename)
self.query("alter table %s add fld4 varchar(15);"%self._tablename)
self.query("alter table %s add fld5 varchar(15);"%self._tablename)
self.query("alter table %s add fld6 varchar(15);"%self._tablename)
self.query("alter table %s add nfld int4;"%self._tablename)
self.query("alter table %s add pv_last_field varchar(15);"%\
self._tablename)
self.query("create index pv_name_indx on pvlist(pv_name);")
def delete_table(self):
self.query("DROP TABLE %s"%self._tablename)
def insert_data(self,*val,**kw):
ins_cmd="INSERT INTO %s "%self._tablename
if val:
ins_st=ins_cmd+"VALUES %s"%str(val)
if kw:
kwl=reduce(lambda x,y:"%s, %s"%(x,y),kw.keys())
ins_st=ins_cmd+"( %s ) VALUES %s"%(kwl,str(tuple(kw.values())))
else:
inst_st=inc_cmd
self.query(ins_st)
def update_data(self, filnum, **kw):
_cmd="UPDATE %s SET "%self._tablename
_st=""
if kw:
for key,val in kw.items():
_st=_st+" %s = %s, "%(key,val)
if _st > 2:
_st=_cmd+_st[:-2]+" WHERE fillnum = %s"%filnum
#print _st
self.query(_st)
def delete_all_data(self):
self.query("DELETE FROM %s "%self._tablename)
def pvloadTemplate(self,template=""):
if not template:
raise ValueError
cmd="/proj/epics/R313/base/bin/hppa8k/dbLoadTemplate %s|dbreport OUT.INP.35 DESC.20 | sort "
f=os.popen(cmd%template)
lines=f.readlines()
self._pvload(lines)
def pvload(self, dbrname=""):
"""
-- Table to control loading of the PVLIST files into Oracle
-- Note that the BaBar file is done first, in another file
-- so that the BBR: prefix can be added before loading these
"""
if not dbrname:
raise ValueError
cmd="dbreport -f %s OUT.INP.35 DESC.20 | sort "
f=os.popen(cmd%dbrname)
lines=f.readlines()
self._pvload(lines)
def _pvload(self,lines):
self.query("begin transaction")
for l in lines:
if l[0] == "#" or l[0] == '\n':
continue
ll=len(l)
#print l,len(l)
pv_type=string.strip(l[:15])
pv_name=string.strip(l[15:45])
pv_inout=string.strip(l[45:80])
pv_desc=string.strip(l[80:100])
if pv_type == "TYPE":
continue
#print pv_type, pv_name, pv_inout, pv_desc
if not pv_name:
continue
self.insert(self._tablename, Env(pv_type=pv_type,
pv_name=pv_name,
pv_inout=pv_inout,
pv_desc=pv_desc))
self.query("end transaction;")
def name2fld(self):
attnames=self.get_attnames(self._tablename)
if not attnames.has_key("fld1"):
self.amend()
result=self.query("select * from %s"%self._tablename)
records=result.getresult()
record_count=0
self.query("begin transaction")
for record in records:
pv_type,pv_name,pv_inout,pv_desc=record[:4]
#print pv_name
fields=string.split(pv_name,":")
nfld=len(fields)
if nfld <= 6:
fld1,fld2,fld3,fld4,fld5,fld6=fields+(6-nfld)*[""]
pv_last_field=""
else:
fld1,fld2,fld3,fld4,fld5,fld6=fields[:6]
pv_last_field=string.join(fields[6:],":")
qcmd="update %s set "\
"fld1 = '%s', fld2='%s',"\
"fld3 = '%s', fld4='%s',"\
"fld5 = '%s', fld6='%s',"\
"pv_last_field = '%s',"\
"nfld = %d where pv_name = '%s'"%(self._tablename, \
fld1,fld2,fld3,fld4,fld5,fld6,\
pv_last_field,nfld,pv_name)
#print qcmd
self.query(qcmd)
record_count=record_count+1
if record_count > 200:
self.query("commit")
self.query("begin transaction")
record_count=0
if record_count >0:
self.query("commit")
- Navigate by Date:
- Prev:
CA clients on vxWorks Matthieu Bec
- Next:
Re: medm window resizing Ken Evans
- Index:
1994
1995
1996
1997
1998
1999
2000
<2001>
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
- Navigate by Thread:
- Prev:
Re: medm window resizing Ken Evans
- Next:
SNS's investigation of EDM Chestnut, Ronald P.
- Index:
1994
1995
1996
1997
1998
1999
2000
<2001>
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
|
·
Home
·
News
·
About
·
Base
·
Modules
·
Extensions
·
Distributions
·
Download
·