def _test_SqlAlchemy(self):
'Watch out with this method, it kills.'
if True: # default: True
db_name = PDBJ_DB_NAME
user_name = NRG_DB_USER_NAME
schema = NRG_DB_SCHEMA
else:
db_name = CASD_DB_NAME
user_name = CASD_DB_USER_NAME
csql = CsqlAlchemy(user=user_name, db=db_name, schema=schema)
self.assertFalse(csql.connect())
if not csql.conn:
nTerror("Failed to connect in %s" % getCallerFileName())
return
nTmessage("Connected to RDB now.")
if True:
return # Just be sure to not continue here.
# users_table = Table('users', csql.metadata,
# Column('id', Integer, primary_key = True),
# Column('name', String(50)),
# Column('fullname', String(50)),
# Column('password', String(50))
# )
# csql.metadata.create_all(csql.engine) # may be called multiple times.
# mapper(User, users_table)
# ed_user = User('ed', 'Ed Jones', 'edspassword')
# _user_table = User.__table__
metadata = Base.metadata
ed_user = User('ed', 'Ed Jones', 'edspassword')
csql.session.add(ed_user) # does NOT flush yet. Not autocommiting.
our_user = csql.session.query(User).filter_by(name='ed').first()
nTdebug(str(our_user))
nTdebug(
"ed_user is our_user: %s" %
(ed_user is our_user)) # Will be false only if it already existed?
csql.session.add_all([
User('wendy', 'Wendy Williams', 'foobar'),
User('mary', 'Mary Contrary', 'xxg527'),
User('fred', 'Fred Flinstone', 'blah')
])
ed_user.password = '******'
nTdebug(str(csql.session.dirty)) # shows bad data.je
nTdebug(str(csql.session.new)) # shows unflushed data.
csql.session.commit()
nTdebug(str(ed_user)) # shows unflushed data.
# column = Column('extraCol', String(50))
# user_table.append_column(column) # fails to stick around. but can be done outside??
metadata.create_all(csql.engine)
# ed_user.extraCol = 'xyz'
# csql.session.commit() # fails to make the append stick.
nTdebug(str(ed_user))
def _test_SqlAlchemy_2(self):
"""Testing SqlAlchemy setup.
Only enable when db is installed, configured and running"""
pdb_id = '1brv'
res_number = 171
csql = CsqlAlchemy()
self.assertFalse(csql.connect())
csql.autoload()
execute = csql.conn.execute
centry = csql.entry
cchain = csql.chain
cresidue = csql.residue
#result = csql.conn.execute(centry.delete())
result = execute(centry.delete().where(centry.c.pdb_id == pdb_id))
if result.rowcount:
nTdebug("Removed original entries numbering: %s" % result.rowcount)
else:
nTdebug("No original entry present yet.")
#ins = centry.insert().values(entry_id=1,pdb_id='1brv')
#nTdebug( ins )
#ins.compile().params # show the set parameters/values.
#result = csql.conn.execute(centry.insert().values(entry_id=1,pdb_id='1brv'))
result = csql.conn.execute(centry.insert().values(pdb_id=pdb_id,
name=pdb_id))
entry_id = result.last_inserted_ids()[0]
nTdebug("Inserted entry %s" % entry_id)
result = csql.conn.execute(cchain.insert().values(entry_id=entry_id))
chain_id = result.last_inserted_ids()[0]
nTdebug("Inserted chain %s" % chain_id)
#for j in range(1):
# for i in range(1):
result = csql.conn.execute(cresidue.insert().values(entry_id=entry_id,
chain_id=chain_id),
number=res_number)
residue_id = result.last_inserted_ids()[0]
nTdebug("Inserted residue %s" % residue_id)
result = csql.conn.execute(cresidue.update().where(
cresidue.c.residue_id == residue_id).values(number=res_number +
999))
nTdebug("Updated residues numbering %s" % result.rowcount)
# Needed for the above hasn't been autocommitted.
csql.session.commit()
for residue in csql.session.query(cresidue):
nTdebug("New residue number %s" % residue.number)
for instance in csql.session.query(centry):
nTdebug("Retrieved entry instance: %s" % instance.pdb_id)
def _test_storeCING2db(self): #DEFAULT disabled because it's a specific test for services not commonly used.
cingDirTmpTest = os.path.join( cingDirTmp, getCallerName() )
mkdirs( cingDirTmpTest )
self.failIf(os.chdir(cingDirTmpTest), msg =
"Failed to change to test directory for files: " + cingDirTmpTest)
entry_code = '1brv'
pdb_id = entry_code
schema = NRG_DB_NAME
archive_id = ARCHIVE_NRG_ID
db_name = PDBJ_DB_NAME
user_name = PDBJ_DB_USER_NAME
nTdebug("Starting doStoreCING2db using:")
nTdebug("entry_code: %s" % entry_code)
# nTdebug("inputDir: %s" % inputDir)
nTdebug("archive_id: %s" % archive_id)
nTdebug("user_name: %s" % user_name)
nTdebug("db_name: %s" % db_name)
nTdebug("schema: %s" % schema)
csql = CsqlAlchemy(user=user_name, db=db_name,schema=schema)
self.assertFalse( csql.connect(), "Failed to connect to DB")
csql.autoload()
execute = csql.conn.execute
centry = csql.cingentry
result = execute(centry.delete().where(centry.c.pdb_id == pdb_id))
if result.rowcount:
nTdebug("Removed original entries numbering: %s" % result.rowcount)
if result.rowcount > 1:
nTerror("Removed more than the expected ONE entry; this could be serious.")
return True
else:
nTdebug("No original entry present yet.")
# end if
datetime_first = datetime.datetime(2011, 4, 7, 11, 12, 26)
nTdebug("Trying datetime_first %s" % datetime_first)
result = execute(centry.insert().values(
pdb_id=pdb_id,
name=entry_code,
rev_first = 9,
rev_last = 99,
timestamp_first = datetime_first
# datetime_last = datetime_last
))
entry_id_list = execute(select([centry.c.entry_id]).where(centry.c.pdb_id==pdb_id)).fetchall()
self.assertTrue( entry_id_list, "Failed to get the id of the inserted entry but got: %s" % entry_id_list)
self.assertEqual( len( entry_id_list ), 1, "Failed to get ONE id of the inserted entry but got: %s" % entry_id_list)
def _test_SqlAlchemyWithPdbjCing(self):
'Watch out with executing this test routine. It kills.'
pdb_id = '1brv'
if True: # default: True
db_name = PDBJ_DB_NAME
user_name = DEV_NRG_DB_USER_NAME
schema = DEV_NRG_DB_SCHEMA
else:
db_name = CASD_DB_NAME
user_name = CASD_DB_USER_NAME
csql = CsqlAlchemy(user=user_name, db=db_name, schema=schema)
if csql.connect():
nTerror("Failed to connect to DB")
return True
csql.autoload()
execute = csql.conn.execute
centry = csql.cingentry
# WATCH OUT WITH THE BELOW COMMANDS.
#result = csql.conn.execute(centry.delete())
result = execute(centry.delete().where(centry.c.pdb_id == pdb_id))
if result.rowcount:
nTdebug("Removed original entries numbering: %s" % result.rowcount)
else:
nTdebug("No original entry present yet.")
result = csql.conn.execute(centry.insert().values(pdb_id=pdb_id,
name=pdb_id,
is_multimeric=False))
# entry_id_list = result.last_inserted_ids() # fails for postgres version I have.
# entry_id_list = result.inserted_primary_key() # wait for this new feature
# nTdebug( "Last row id: " + str(result.lastrowid) ) # Always one
entry_id_list = execute(
select([centry.c.entry_id
]).where(centry.c.pdb_id == pdb_id)).fetchall()
self.assertNotEqual(
entry_id_list, None,
"Failed to get the id of the inserted entry but got: %s" %
entry_id_list)
self.assertEqual(
len(entry_id_list), 1,
"Failed to get ONE id of the inserted entry but got: %s" %
entry_id_list)
entry_id = entry_id_list[0][0]
nTdebug("Inserted entry id %s" % entry_id)
def linkdataNRG():
#connection to the database:
csql = CsqlAlchemy(host=HOST,
user=PDBJ_DB_USER_NAME,
db=PDBJ_DB_NAME,
schema=NRG_DB_SCHEMA)
csql.connect()
execute = csql.conn.execute
jsql = CgenericSql(host=HOST,
user=PDBJ_DB_USER_NAME,
db=PDBJ_DB_NAME,
schema=PDBJ_DB_SCHEMA)
jsql.connect()
jsql.autoload()
#These few lines will load the tables brief_summary and cingentry.
#jsql.loadTable('brief_summary')
#bs=jsql.brief_summary.alias()
csql.loadTable('cingentry')
e1 = csql.cingentry.alias()
csql.loadTable('cingresidue')
r1 = csql.cingresidue.alias()
#The file opdracht_geerten_november2010.txt contains all input.
in_file = open(filename, "r")
text = in_file.read()
in_file.close()
i = 0 # i indicates a new block with information in the input file.
pdbid = ''
pdbid2 = ''
pdbidlist0 = []
pdbidlist1 = []
pdbidlist2 = []
extrainfolist = []
m = 0 # m indicates a new line.
# The following script reads the pdbid's in the text file and put them in four different columns.
# one for all different pdbids in the first column,
# one for all pdbid's in the first column,
# one for pdbids in the second column and
# one for the extra information. The // between the blocks are
# deleted in order to make it easier to add some information later.
pdbidlist0.append(text[0:4]) #the file starts with an pdbid.
ltext = len(text)
for n in range(ltext):
if n < (ltext - 2) and text[n:n + 2] == '//':
if not (n < (ltext - 3) and text[n + 4] == '/'):
pdbid = text[n + 3:n + 7]
if not is_pdb_code(pdbid):
# at the end of the file an empty pdbid is selected. I haven't removed it yet, so you'll see an error.
nTerror('[%s] is not a pdb/localpdbid.' % pdbid)
#os._exit(1)
pdbidlist0.append(pdbid)
i = 1
if n == (ltext - 1) or text[n:n + 4] == pdbid:
pdbidlist1.append(text[n:n + 4])
if m != 0:
if n == (
ltext - 1
): # at the end of the document there is for one entry extra information left.
extrainfo = text[m + 9:n - 3]
elif i == 1: # if the extra information is followed by an //,these signs are excluded from the extra information
extrainfo = text[m + 9:n - 4]
i = 0
else: # if it is a random 'line' in the text file, just take the part between m+9 and n-1 (before the new pdbid)
extrainfo = text[m + 9:n - 1]
extrainfokort = extrainfo.replace(
'/', '') #all extra enters and / are deleted
extrainfokort = extrainfokort.replace('\n', '')
extrainfolist.append(extrainfokort)
m = 0
# end if
pdbid2 = text[n + 5:n + 9]
pdbidlist2.append(pdbid2)
m = n #m indicates the beginning of a new line with information.
# end if
# end for
pdbidlist2 = pdbidlist2[0:-1] # removes the '' at the end
pdbidlist1 = pdbidlist1[0:-1]
pdbidlisttotal = pdbidlist0 + pdbidlist2 #this includes all pdbid's in the file.
#Create some empty dictionaries
pc_rama_coredict = NTdict()
ramchkdict = NTdict()
bbcchkdict = NTdict()
rotchkdict = NTdict()
perEntryRogdict = NTdict()
#The ROG percentages are load from the database and put in a dictionary.
s5 = select(
[e1.c.pdb_id, r1.c.rog, 100.0 * func.count(r1.c.rog) / e1.c.res_count],
from_obj=[e1.join(r1)]).group_by(e1.c.pdb_id, r1.c.rog, e1.c.res_count)
nTdebug("SQL: %s" % s5)
result = execute(s5).fetchall()
#nTdebug("ROG percentage per entry: %s" % result)
for row in result:
k = str(row[0])
if not perEntryRogdict.has_key(k):
perEntryRogdict[k] = nTfill(0.0, 3)
perEntryRogdict[k][int(row[1])] = float(row[2])
for d in range(len(pdbidlisttotal)):
if not perEntryRogdict.has_key(str(pdbidlisttotal[d])):
perEntryRogdict[pdbidlisttotal[d]] = ['', '', '']
#Below is a script that will select the pdb_id column, pc_gf, wi_ramchk, wi_bbcchk and wi_rotchk columns from the entry table.
s1 = select([
e1.c.pdb_id, e1.c.pc_rama_core, e1.c.wi_ramchk, e1.c.wi_bbcchk,
e1.c.wi_rotchk
])
for n in range(len(pdbidlisttotal)):
localpdbid = pdbidlisttotal[n]
s2 = s1.where(e1.c['pdb_id'] == localpdbid)
s2 = execute(s2).fetchall()
if s2 == []:
s2 = [
(localpdbid, '', '', '', '', '')
] # if there is no information, the pdbid is shown and the others are set empty.
if not pc_rama_coredict.has_key(localpdbid):
pc_rama_coredict.appendFromTable(s2, 0, 1)
ramchkdict.appendFromTable(s2, 0, 2)
bbcchkdict.appendFromTable(s2, 0, 3)
rotchkdict.appendFromTable(s2, 0, 4)
#Below, the final text is composed.
#First the original 4 columns are set back. After that, some other information is added. All information is
#separated by a comma.
finaltext = (
'pdbid1,pdbid2,length_total,length_match,matchfraction,experimental_meth_pdb2,Perc_most_fav_1,Ramchk_1,Bbcchk_1,Rotchk_1,'
+
'ROG_Green_1,ROG_Orange_1,ROG_Red_1,Perc_most_fav_2,Ramchk_2,Bbcchk_2,C12chk_2,ROG_Green_2,ROG_Orange_2,ROG_Red_2\n'
)
dictList = [
pc_rama_coredict, ramchkdict, bbcchkdict, rotchkdict, perEntryRogdict
]
for k in range(len(pdbidlist1)):
if k > 1 and pdbidlist1[k] != pdbidlist1[k - 1]:
finaltext += '\n'
pdb_id1 = pdbidlist1[k]
pdb_id2 = pdbidlist2[k]
finaltext += pdb_id1 + ',' + pdb_id2 + extrainfolist[k]
for g in [pdb_id1, pdb_id2]:
strList = [str(x[str(g)]) for x in dictList]
finaltext += ',' + ','.join(strList)
finaltext += '\n'
#for g in [pdb_id1,pdb_id2]:
# strList = []
# for x in dictList:
# print x
# y = str(g)
# print y
# z = x[y]
# z_str = str(z)
# strList.append( z_str )
# finaltext += ',' + ','.join(strList)
#finaltext +='\n'
#Some corrections to make it a parsable csv-file.
finaltext = finaltext.replace('None', '')
finaltext = finaltext.replace('\t', ',')
finaltext = finaltext.replace(']', '')
finaltext = finaltext.replace('[', '')
finaltext = finaltext.replace("''", '')
finaltext = finaltext.replace(' ', '')
sys.exit(1)
# end if.
# Needs to be copied because the open method doesn't take a directory argument..
project = Project.open(pdb_id, status='old')
if not project:
nTerror("Failed to init old project")
sys.exit(1)
# shortcuts
p = project
molecule = project.molecule
p.validate(parseOnly=True, htmlOnly=True)
csql = CsqlAlchemy()
if csql.connect():
nTerror("Failed to connect to DB")
sys.exit(1)
csql.autoload()
execute = csql.conn.execute
centry = csql.entry
cchain = csql.chain
cresidue = csql.residue
catom = csql.atom
# end def
p.validate(htmlOnly=True, doProcheck=False, doWhatif=False, doWattos=True)
