Support for postgresql #6

New issue

Open

opened 2025-10-14 16:59:35 -06:00 by navan · 0 comments

navan commented

2025-10-14 16:59:35 -06:00

Owner

Originally created by @marcostenta on 5/14/2025

Hi Folks,
I was trying to use the postgres database and I observe a strange behavior and an error.
Can you spot anything I am doing wrong?
Thanks in advance.

mmpdb = 3.1.3
pgresql = 13
pythpon = 3.10

I am using the example / test data coming with the code.
I create a local sqlite fragment db.

Then I try to index
mmpdb index test_data.fragdb -o postgres://default:default@localhost:5432/postgres

if I i list the content I get nothing: is this expected? should we have the content of the tables that have been populated there?
mmpdb list postgres://default:default@localhost:5432/postgres

Name #cmpds #rules #pairs #envs #stats |Title| Properties

I I repeat the same "index" command an error is raised: is this expected?
mmpdb index test_data.fragdb -o postgres://default:default@localhost:5432/postgres

raise DatabaseAlreadyExists("Postgres database", self.uri, msg)
mmpdblib.index_algorithm.DatabaseAlreadyExists: Requires tables already exist: dataset, compound, property_name, compound_property, rule_smiles, rule, rule_environment, environment_fingerprint, pair, constant_smiles, rule_environment_statistics
If i do not repeat the command and move to the insert of data i get an error
mmpdb loadprops postgres://default:default@localhost:5432/postgres --properties test_data.csv

Using dataset: MMPs from 'test_data.fragdb'
Reading properties from 'test_data.csv'
Read 2 properties for 9 compounds from 'test_data.csv'
Imported 9 'MW' records (9 new, 0 updated).
Imported 8 'MP' records (8 new, 0 updated).
Computing and updating aggregate statistics 0/342 (0.0%)Traceback (most recent call last):
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\runpy.py", line 196, in _run_module_as_main
return _run_code(code, main_globals, None,
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\runpy.py", line 86, in run_code
exec(code, run_globals)
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\Scripts\mmpdb.exe_main.py", line 7, in
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\core.py", line 1442, in call
return self.main(*args, **kwargs)
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\core.py", line 1363, in main
rv = self.invoke(ctx)
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\core.py", line 1830, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\core.py", line 1226, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\core.py", line 794, in invoke
return callback(*args, **kwargs)
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\mmpdblib\cli\click_utils.py", line 339, in wrapped_command
return command(**kwargs)
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\decorators.py", line 46, in new_func
return f(get_current_context().obj, *args, **kwargs)
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\mmpdblib\cli\loadprops.py", line 210, in loadprops
dbutils.reaggregate_properties(
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\mmpdblib\dbutils.py", line 396, in reaggregate_properties
dataset.add_rule_environment_statistics(rule_environment_id, property_name_id, stats)
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\mmpdblib\schema.py", line 975, in add_rule_environment_statistics
self.mmpa_db.execute(
File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\mmpdblib\schema.py", line 258, in execute
cursor.execute(sql, args)
psycopg2.errors.InvalidSchemaName: schema "np" does not exist
LINE 1: ...0, -69.0, 1.5, 72.0, 72.0, 0.021276595744680854, np.float64...
^

*Originally created by @marcostenta on 5/14/2025* Hi Folks, I was trying to use the postgres database and I observe a strange behavior and an error. Can you spot anything I am doing wrong? Thanks in advance. mmpdb = 3.1.3 pgresql = 13 pythpon = 3.10 I am using the example / test data coming with the code. I create a local sqlite fragment db. 1) Then I try to index **mmpdb index test_data.fragdb -o postgres://default:default@localhost:5432/postgres** if I i list the content I get nothing: is this expected? should we have the content of the tables that have been populated there? mmpdb list postgres://default:default@localhost:5432/postgres _Name #cmpds #rules #pairs #envs #stats |Title| Properties_ 2) I I repeat the same "index" command an error is raised: is this expected? **mmpdb index test_data.fragdb -o postgres://default:default@localhost:5432/postgres** _raise DatabaseAlreadyExists("Postgres database", self.uri, msg) mmpdblib.index_algorithm.DatabaseAlreadyExists: Requires tables already exist: dataset, compound, property_name, compound_property, rule_smiles, rule, rule_environment, environment_fingerprint, pair, constant_smiles, rule_environment_statistics_ 3) If i do not repeat the command and move to the insert of data i get an error **mmpdb loadprops postgres://default:default@localhost:5432/postgres --properties test_data.csv** Using dataset: MMPs from 'test_data.fragdb' Reading properties from 'test_data.csv' Read 2 properties for 9 compounds from 'test_data.csv' Imported 9 'MW' records (9 new, 0 updated). Imported 8 'MP' records (8 new, 0 updated). Computing and updating aggregate statistics 0/342 (0.0%)Traceback (most recent call last): File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\runpy.py", line 196, in _run_module_as_main return _run_code(code, main_globals, None, File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\runpy.py", line 86, in _run_code exec(code, run_globals) File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\Scripts\mmpdb.exe\__main__.py", line 7, in <module> File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\core.py", line 1442, in __call__ return self.main(*args, **kwargs) File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\core.py", line 1363, in main rv = self.invoke(ctx) File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\core.py", line 1830, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\core.py", line 1226, in invoke return ctx.invoke(self.callback, **ctx.params) File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\core.py", line 794, in invoke return callback(*args, **kwargs) File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\mmpdblib\cli\click_utils.py", line 339, in wrapped_command return command(**kwargs) File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\click\decorators.py", line 46, in new_func return f(get_current_context().obj, *args, **kwargs) File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\mmpdblib\cli\loadprops.py", line 210, in loadprops dbutils.reaggregate_properties( File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\mmpdblib\dbutils.py", line 396, in reaggregate_properties dataset.add_rule_environment_statistics(rule_environment_id, property_name_id, stats) File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\mmpdblib\schema.py", line 975, in add_rule_environment_statistics self.mmpa_db.execute( File "C:\Users\u776341\AppData\Local\miniforge3\envs\mmpdb_exploration\lib\site-packages\mmpdblib\schema.py", line 258, in execute cursor.execute(sql, args) psycopg2.errors.InvalidSchemaName: schema "np" does not exist LINE 1: ...0, -69.0, 1.5, 72.0, 72.0, 0.021276595744680854, np.float64... ^