search

OpenEnergyPlatform / open-MaStR

A collaborative software to download the energy database Marktstammdatenregister (MaStR)
https://open-mastr.readthedocs.io/en/latest/
GNU Affero General Public License v3.0
83 stars 17 forks source link

How to create an MS SQL copy of the MaStR Database? #503

Closed tomfelder94 closed 4 months ago

tomfelder94 commented 4 months ago

I would like to have our own copy of the MaStR database, hosted on a MS SQL server. Did I understand correctly, that this should be possible by passing the respective engine to the Mastr() instance?

I tried the following below:

from open_mastr import Mastr from sqlalchemy import create_engine

connection_string = 'mssql+pyodbc://@<SERVER>/MarketAnalysis?driver=ODBC+Driver+17+for+SQL+Server' ma_engine = create_engine(connection_string) db = Mastr(engine=ma_engine)

But then I get the following error: _sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'EinheitMastrNummer' in table 'basicunits' is of a type that is invalid for use as a key column in an index. (1919) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not create constraint or index. See previous errors. (1750)")

Is that a bug or am I trying to do something that is not possible? What would be the best solution for me to create my own copy of the MaStR database, hosted on an MS SQL server?

FlorianK13 commented 4 months ago

Hi @tomfelder94 - we only maintain code for building either an sqlite or a postgres database. So I think there are two options:

  1. you can either use one of those two options as a database.
  2. or you download the data and write it to sqlite, and then write some own code to transfer the data from sqlite to MS SQL server.
chrwm commented 4 months ago

If this still of interest, please reopen.