package loading error & generating database schema from the base model

[cdesyoun]

I tried to create the database schema for sqlite and postgresql using sqlalchemy data model (models.py). My code is below:

import sys
sys.path.append('ODM2PythonAPI')
from sqlalchemy import create_engine
from src.api.base import modelBase
from sqlalchemy import event
from sqlalchemy.schema import CreateSchema

def createDBschema(db_type):
if db_type == 'postgresql':
event.listen(modelBase.metadata, 'before_create', CreateSchema('odm2'))
engine = create_engine('postgresql+psycopg2://............')
modelBase.metadata.create_all(engine,checkfirst=True)
if db_type == 'sqlite':
engine = create_engine('sqlite:///:memory:', echo=True)
modelBase.metadata.create_all(engine)
createDBschema('sqlite')

When running the above code, I got the error messages below related to "LikeODM1":
Traceback (most recent call last):
File "create_schema.py", line 8, in
from src.api.base import modelBase
File "ODM2PythonAPI/src/api/init.py", line 1, in
from .ODMconnection import SessionFactory, dbconnection
File "ODM2PythonAPI/src/api/ODMconnection.py", line 6, in
from .versionSwitcher import ODM, refreshDB #import Variable as Variable1
File "ODM2PythonAPI/src/api/versionSwitcher.py", line 8, in
import ODM2.LikeODM1.models as ODM2
File "ODM2PythonAPI/src/api/ODM2/LikeODM1/init.py", line 34, in
import models
File "ODM2PythonAPI/src/api/ODM2/LikeODM1/models.py", line 41, in
class Site(Base):
File "ODM2PythonAPI/src/api/ODM2/LikeODM1/models.py", line 46, in Site
id = site_join.c.odm2_sites_samplingfeatureid
File "/Users/cyoun/PycharmProjects/venv_odm/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 211, in getattr
raise AttributeError(key)
AttributeError: odm2_sites_samplingfeatureid

After commenting out these parts in "ODMconnection.py" file, I could remove this error.

Based on this sqlachemy data model for ODM2, there are some issues for generating database schema directly below:

1. For "sqlite" db, the current model uses the schema, "odm2" in the database and "geometry" type as the custom type for "featuregeometry" column in "samplingfeatures" table. There is a problem for generating the database schema from the model. The "sqlite" db does not support the schema method. I overrode "get_col_spec" function in Geometry type to accept the column object. But, even if this table was created, there is a failure to create index. After commenting out this schema name, "odm2" and "featuregeometry" column in this file, models.py, I could generate db schema.
2. For "postgresql", we assume "postgis" tables should be installed. And the current model classes use the SQL collation, "SQL_Latin1_General_CP1_CI_AS" which specializes in using for ms sql server. Postgresql server does matter, spawning errors for generating db schema. After deleting this collation, I could generate db schema on this db server.

if we can have generic sqlalchemy data model for ODM2 that solves these above issues, using simple codes above, we can generate db schema simply.

[horsburgh]

Why not use the DDL SQL scripts in the ODM2 repository for generating the ODM2 database?

[cdesyoun]

Jeff, Yes, it is one of ways for creating DB schema in the database. As other way, since we have ODM2 python API to access the ODM2 data, that defined ODM2 model classes can be used for generating DB schema in the ODM2 database as well. In the python programming level, we can generate DB schema automatically among different types of databases.