def test_multi_table():
fn1 = _rand_db_filename()
fn2 = _rand_db_filename()
sql.makedb(filename=fn1,
lists=list(zip([0, 1], ['a', 'b'], [1.0, 2.0])),
colnames=['idx', 'c1', 'c2'],
mode='w',
table='tab1')
sql.makedb(filename=fn2,
lists=list(zip([0, 1, 2], ['c', 'd', 'e'], [3.0, 4.0, 5.0])),
colnames=['idx', 'c1', 'c2'],
mode='w',
table='tab1')
# in memory db
db = sql.SQLiteDB(':memory:')
db.executescript("attach '%s' as db1; attach '%s' as db2" % (fn1, fn2))
cmd = """
select
db1.tab1.idx,
db1.tab1.c1 as db1_c1,
db2.tab1.c1 as db2_c1,
db2.tab1.c2 as db2_c2
from
db1.tab1,db2.tab1
where
db1.tab1.idx==db2.tab1.idx;
"""
dct = db.get_dict(cmd)
assert dct['idx'] == [0, 1]
assert dct['db1_c1'] == ['a', 'b']
assert dct['db2_c1'] == ['c', 'd']
assert dct['db2_c2'] == [3.0, 4.0]
# multi table
db = sql.SQLiteDB(fn1)
header1 = [('idx', 'INTEGER'), ('c1', 'TEXT'), ('c2', 'REAL')]
assert db.get_header(table='tab1') == header1
header2 = [('x', 'REAL'), ('y', 'TEXT')]
db.create_table(header2, table='tab2')
db.execute("insert into tab2 ('x', 'y') values (1.0, 'a')")
db.execute("insert into tab2 ('x', 'y') values (2.0, 'b')")
db.attach_column(col='z', values=[88, 99], table='tab2')
header2 += [('z', 'INTEGER')]
assert db.get_header(table='tab2') == header2
assert db.has_table('tab2')
assert db.has_column('idx', table='tab1')
assert db.has_column('x', table='tab2')
assert db.get_max_rowid('tab2') == 2
db.set_table('tab1')
assert db.has_column('idx')
assert db.get_header() == header1
assert db.get_max_rowid() == 2
db.set_table('tab2')
assert db.get_header() == header2
assert db.has_column('x')
assert db.get_max_rowid() == 2
def test_makedb():
lists = list(zip(['a', 'b'], [1.0, 2.0]))
colnames = ['foo', 'bar']
dbfn = _rand_db_filename()
table = dbfn.split('/')[-1].replace('.db', '')
sql.makedb(filename=dbfn, lists=lists, colnames=colnames, mode='w')
db = sql.SQLiteDB(dbfn, table=table)
dct = db.get_dict("select * from %s" % table)
assert dct['foo'] == ['a', 'b']
assert dct['bar'] == [1.0, 2.0]
sql.makedb(filename=dbfn, lists=lists, colnames=colnames, mode='a')
db = sql.SQLiteDB(dbfn, table=table)
dct = db.get_dict("select * from %s" % table)
assert dct['foo'] == ['a', 'b'] * 2
assert dct['bar'] == [1.0, 2.0] * 2
# makedb, set table name, close=False, return open db
dbfn = _rand_db_filename()
table = dbfn.split('/')[-1].replace('.db', '')
db = sql.makedb(dbfn, lists, colnames, mode='w', table=table, close=False)
dct = db.get_dict("select * from %s" % table)
assert dct['foo'] == ['a', 'b']
assert dct['bar'] == [1.0, 2.0]
def check_generated(calc_root, machine_dct, params_lst, revision):
"""Check consistency of calc database values, replacement params in
`params_lst` and all written files.
"""
dbfn = pj(calc_root, 'calc.db')
db = sql.SQLiteDB(dbfn, table='calc')
print("database content:")
print(db.get_dict("select * from calc"))
db_colnames = [x[0] for x in db.get_header()]
for idx, hostname_str in db.execute(
"select idx,hostname from calc \
where revision==?",
(revision, )).fetchall():
for hostname in hostname_str.split(','):
machine = machine_dct[hostname]
calc_dir = pj(calc_root, 'calc_%s' % machine.hostname, str(idx))
for base in ['pw.in', machine.get_jobfile_basename()]:
fn = pj(calc_dir, base)
assert os.path.exists(fn)
lines = common.file_readlines(fn)
# assemble all possible replacements in one list of SQLEntry
# instances, some things are redundantely checked twice ...
sql_lst = params_lst[idx] + list(
machine.get_sql_record().values())
for db_key in db_colnames:
db_val = db.get_single(
"select %s from calc "
"where idx==?" % db_key, (idx, ))
if db_val is not None:
sql_lst.append(sql.SQLEntry(key=db_key, sqlval=db_val))
# for each replacement key, check if they are correctly placed
# in the database (if applicable) and in the written files
for sqlentry in sql_lst:
if sqlentry.key in db_colnames:
db_val = db.get_single("select %s from calc "
"where idx==?" \
%sqlentry.key, (idx,))
assert_all_types_equal(db_val, sqlentry.sqlval)
else:
db_val = 'NOT_DEFINED_IN_DB'
print("check_generated: idx={0}, sqlentry.key={1}, "
"sqlentry.sqlval={2}, db_val={3}".format(
idx, sqlentry.key, sqlentry.sqlval, db_val))
check_key_in_file(lines, sqlentry.key, sqlentry.sqlval)
db.finish()
#!/usr/bin/python
# Print result of convergence study: differences of etot, pressure
from pwtools import sql, batch, mpl
db = sql.SQLiteDB('calc.db')
etot_fac = 1000.0 / 4 # eV -> meV/atom, 4 atoms
data = db.get_array("select ecutwfc,etot,pressure from calc order by ecutwfc")
print("ecutwfc, diff(etot) [meV/atom], diff(pressure) [GPa]")
print(
batch.conv_table(data[:, 0], [data[:, 1] * etot_fac, data[:, 2]],
mode='last',
orig=False))
# plotting
fig, ax = mpl.fig_ax()
ax.plot(data[:, 0], (data[:, 1] - data[-1, 1]) * etot_fac,
label='etot',
color='b')
ax.set_ylabel('diff(etot) [meV/atom]')
ax.set_xlabel('ecutwfc [Ry]')
ax.legend()
mpl.plt.show()
#!/usr/bin/python
# Load parsed results and put some values in the database.
from pwtools import sql, io, num
db = sql.SQLiteDB('calc.db', table='calc')
idx_lst = db.get_list1d("select idx from calc")
cols = [
('etot', 'float'), # eV
('pressure', 'float'), # GPa
('volume', 'float'), # Ang**3
('forces_rms', 'float'), # eV / Ang
('sxx', 'float'), # GPa
('syy', 'float'), # GPa
('szz', 'float'), # GPa
]
db.add_columns(cols)
for idx in idx_lst:
print(idx)
struct = io.cpickle_load('results/%i/traj.pk' % idx)[-1]
db.execute("update calc set etot=? where idx==?", (struct.etot, idx))
db.execute("update calc set volume=? where idx==?", (struct.volume, idx))
db.execute("update calc set pressure=? where idx==?",
(struct.pressure, idx))
db.execute("update calc set sxx=? where idx==?",
(struct.stress[0, 0], idx))
db.execute("update calc set syy=? where idx==?",
(struct.stress[1, 1], idx))