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

[tomfelder94]

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 'basic_units' 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]

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]

If this still of interest, please reopen.

[tuxiano]

@chrwm I have the very same issue. PostgreSQL/sqlite unfortunately is no option.

[nesnoj]

Hey, have you found a way to get this working? @tuxiano @tomfelder94
I understand your needs but this project runs on a very limited amount of time and we have to focus on the core functionalities. We are always happy about contributions :)

[tomfelder94]

@nesnoj

Unfortunately, not. Transferring the data to an SQL Server is not that straightforward, regarding the amount of data there is. Especially since we want to sync/update the tables on a regular basis (ideally on a daily basis). There are solutions with paywalls that are converting SQLite to SQL tables, but they are quite expensive and therefore not an option for us.

We are working with the SQLite DB at the moment but would be highly interested if anyone has a solution for migrating from SQLite to SQL in an automated low cost way.

[nesnoj]

Hey @tomfelder94!

The error message above reads like there's a problem with String columns as primary key on MS SQL (an index is created for each PK). String corresponds to SQL varchar, in this case with no predefined length in the ORM: EinheitMastrNummer = Column(String, primary_key=True).

This is ok for postgreSQL. However, a quick search revealed that MS SQL follows a more strict data typing, SQL Server requires a specified length for VARCHAR when creating indexes or primary keys to determine storage requirements and enforce uniqueness.

So to write the data into a MS SQL DB you would need to adjust the orm.py to meet these requirements: you could try to fix the lengths for the PKs beforehand and see what happens.