Some glitches in between SQLAlchemy, Teradata and Flask

[DocPhilduP]

trafficstars

My deepest gratitude for this adaptation of SQLAlchemy. I am using it extensively to build a configuration web application using flask, flask-admin, sqlalchemy, sqlalchemy-teradata.

It was quite a learning experience to get it working nicely:

• Django and Teradata do not work. Fullstop. The guy that gets Django to work with Teradata deserves a medal. The only library that can help is django-teradata which is not even half compiled right now. For this reason, I chose to go with flask (flask-admin gives similar features)

• flask prefers flask-sqlalchemy. flask-sqlalchemy does not work on Teradata. So I had to use a more pure version of flask with sqlalchemy. I use sqlalchemy-teradata to handle unique Teradata scenarios (like byteint, multiset, primary index etc.). But in reality, if there could be a flask-sqlalchemy-teradata component, it would solve a lot of headaches.

• flask-migrate does not work on Teradata. flask-migrate is based on Alembic which doesnt support Teradata. No biggie as I can do the same with reflection (as soon as the folks of Teradata stop writing nonsense about that QVCI is not recommended).

• flask-security is all sqlalchemy. I manage to work around it by overriding some of their base classes with a sqlalchemy-teradata adaptation. But it limits me on the features. In specific, there is a feature around context datastores which is a horror show on Teradata. Context datastores allow one to introduce a context_processor and bring in far better security decorators along the way.

But the one which hurts the most (for which I have no way around right now):

It appears that SQLAlchemy doesnt handle Teradata identity inserts properly. The teradata-sqlalchemy dialect is pretty sweet for typecasting things nicely, but doestnt appear to cater for Teradata based identity inserts? Also, the create_engine is based on SQLAlchemy pure. In my 5 minutes of experience on SQLAlchemy - within their dialects there is no dialect for Teradata. I assume it means that in some way tdodbc is passed to SQLAlchemy. How can we influence this to rather use the API as opposed to odbc?

The following is a simple example to replicate the issue: Created an engine like this: from sqlalchemy import create_engine engine = create_engine(Config.ConfigTblConnStr, echo=True) Defined a test table class like this: class tstPhilAuto(Base): __tablename__ = "tstPhilAuto" id = Column(Integer, primary_key=True, nullable=False, **autoincrement=True**) firstname = Column(Varchar(255), nullable=False) lastname = Column(Varchar(255), nullable=False)

Note, the ddl on Teradata looks like this: create multiset table <dbname>.tstPhilAuto ( id int not null **generated always as identity (start with 1 increment by 1 minvalue 1 maxvalue 1000000 no cycle)**, firstname varchar(255), lastname varchar(255) );

Attempt to insert data using a python shell like this: `>>> from app.configiAdmin import dbsession

from app.models.tstmodels import tstPhilAuto u = tstPhilAuto(firstname='doc', lastname='phil') dbsession.add(u) dbsession.commit() ` This throws an error: sqlalchemy.orm.exc.FlushError: Instance <tstPhilAuto at 0x1eeca491ec8> has a NULL identity key. If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values. Ensure also that this flush() is not occurring at an inappropriate time, such as within a load() event.