Connect Spinetoolbox to a database server

[daspartha]

Currently the data-connection tool in the Spinetoolbox provides connections to text, csv, excel files.

In the MOPO project we are trying to connect directly AidRES database (PostgreSQL) to Spinetoolbox by creating specific 'Views' so that we do not need to manage a lots of R scripts to create flat files in specific formats.

Is it a good idea to develop the connections for PostgreSQL and also Microsoft SQL?

[manuelma]

Yes, it is an excellent idea and it should be possible with not too much effort.

[soininen]

It is possible to provide a general-purpose URL in Data connection that could connect to a PostgreSQL database. That is just a way to get the URL into Toolbox, though. What other functionality we may be missing to better support different databases?

[manuelma]

Postgre is disallowed at the moment, you cannot select it in the combobox. I'm not sure what we were thinking, probably it's just a mistake (because we don't support postgre in spinedb_api, but we should support it in Data Connection). So it's very easy but somebody probably needs to test it and check it works...

[soininen]

Postgre is disallowed at the moment, you cannot select it in the combobox.

True, I should have checked it first. I can select postgresql from the URL dialog but it says that it cannot generate URL. mssql on the other hand gives a warning "Please create an SQL Server ODBC Data Source first".

[daspartha]

Is there any update to the Spinetoolbox planned regarding this issue in upcoming days?

[soininen]

I can have a look at this.

[soininen]

All the database dialects listed in the Select URL dialog should now work. However, the protocol field of the URL gets modified such that the URL works with SqlAlchemy and Importers/Exporters. If "raw" URLs are desired, we need to add another reference type to Data connection.

In any case, @daspartha could you please update Spine Toolbox and check if the postgresql works for you? I do not have access to PostgreSQL databases so I cannot test it.

The changes were backported from 0.8-dev to main.

[daspartha]

Hi @soininen , sorry to reply late. I am trying the connection now. I have updated the Spinetoolbox to 0.8-dev and setup AidRES PSQL database. AidRES database runs at localhost with port no. 5432.

When I am trying to establish connection from Spinetoolbox using URL in data connection tool I am facing a problem. Even after filling up all necessary fields, the 'OK' button is greyed out and a message displayed below saying "Unable to generate URL from selections: 'postgresql'".

Could you please help me how can I establish the connection?

[soininen]

This should have been fixed on master branch as well --- I don't recommend using 0.8-dev branch since it's under heavy development and isn't really stable.

That being said, did you update Toolbox requirements as well? The command would be python -mpip install --upgrade -r requirements.txt

Do you see any error messages or Tracebacks in the terminal where you started Toolbox?

I just tried this out on my system and the Select URL dialog works without issues here.

[daspartha]

Hi @soininen,

I removed all the previous envs and spinetoolbox installations and created a fresh env and spinetoolbox installation of the master branch.

It seems the connection to the database works but in the importer specification editor, I do not see any tables that are in the database. I am using SqlAlchemy connector. Therefore I do not know how to proceed. In the source data tab I see many columns with heading header_1, header_2 and values are like item_1_1.

Could you please guide me how can I import specific tables from the PSQL database?

[soininen]

I got postgresql installed on my system and discovered a bunch of issues with it and Toolbox. I will try to get those fixed so hopefully Importer will show actual data from the database sometime soon.

Apologies for the issues! I do not think we have ever had anyone using a remote database as a data source before so much of the related functionality in Toolbox is untested.

[soininen]

The issues plaguing Data connection and Importer have now been fixed. Importer's Specification editor should show the tables of the database properly once you select the SqlAlchemy connector. Importing the data seemed to work fine, too.

Next, I will fix the issues in the 0.8-dev branch, as well.

[daspartha]

Hi @soininen,

Thank you very much for taking time to solve the issue. However this still doesn't work for me.

I did a fresh spinetoolbox installation. When I click on the data importer tool and select SqlAlchemy connector, the importer specification editor doesn't show any tables. I also attached the messages in the cmd when I close the import spec editor. For me the tables of AidRES database are under aidres Schema.

How does it look at your end? If it is working smoothly at your end, is it possible to do a short online meeting so that I can replicate the same?

Best, Partha

[soininen]

Importer is working smoothly on my end. Here is the Source data preview from a postgresql tutorial database:

Importer is database schema agnostic. It just reads data from the tables as-is.

The Tracebacks on the command line need fixing (at least in the 0.8-dev branch) but are not linked to the issue at hand.

We can do a support call. You can reach me over Element (click the 'chat on gitter' badge at the top of our README) where we can sort out the details.

[soininen]

@daspartha Database schemas should now work after you pull the latest changes. I'm not sure how you installed Toolbox and I didn't publish anything to PyPI yet, so let me know if you don't get the updates.

[daspartha]

Hi @soininen I did a fresh installation using 'python -m pipx install spinetoolbox'. However I do not see the tables of database in the spinetoolbox still. For a database with only public schema I see the tables in spinetoolbox. Please let me know how I can install it differrently to get the updates.

[soininen]

@daspartha I have now released a new Toolbox version 0.7.2 to PyPI, so updating/reinstalling spinetoolbox should give you the changes needed to deal with database schemas. What I forgot to tell in my previous comment above was that you will need to recreate the database reference in the Data connection (existing references cannot be modified at the moment). The URL selection dialog should now have a Schema field that lets you enter the database schema.

[daspartha]

Hi @soininen, It works now. Many thanks.