SQLite supports generated column, but SQLAlchemy doesn't seem to know this

[bb1898]

Using Python 3.8.2, SQLAlchemy 1.3.16 on Windows 10.

From version 3.31.0 (2020-01-22) SQLite supports generated columns; documentation: https://sqlite.org/gencol.html
So I tried to create a table in a SQLite database using this table definition:

class MieterRechnung(Base):
   
    __tablename__ = "mieter_rechnung"
   
    idv = sa.Column(sa.Integer, primary_key=True)
    idr = sa.Column(sa.Integer, sa.ForeignKey("rechnung.idr"))
    idp = sa.Column(sa.Integer, sa.ForeignKey("person.idp"))
    monate = sa.Column(sa.Numeric(4, 1, asdecimal=False), nullable=False)
    status = sa.Column(sa.String, nullable=False, default="durch")
    basis = sa.Column(sa.Numeric(8, 2, asdecimal=False), nullable=False)
    gezahlt = sa.Column(sa.Numeric(8, 2, asdecimal=False), nullable=False)
    drittelpreis = sa.Column(sa.Computed("basis / 3"))
    diff = sa.Column(sa.Computed("gezahlt - drittelpreis"))
   
    rechnung = orm.relationship("Rechnung", back_populates="personen")
    person = orm.relationship("Person", back_populates="rechnungen")

   
I did wonder why SQLite isn't mentioned in the list of database systems supporting computed columns, but tried anyway. It really doesn't work, I get this exception:

File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\compiler.py", line 2894, in visit_create_table
  processed = self.process(
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\compiler.py", line 350, in process
  return obj._compiler_dispatch(self, **kwargs)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\visitors.py", line 95, in _compiler_dispatch
  return meth(self, **kw)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\compiler.py", line 2928, in visit_create_column
  text = self.get_column_specification(column, first_pk=first_pk)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\dialects\sqlite\base.py", line 1077, in get_column_specification
  raise exc.CompileError("SQLite does not support computed columns")

sqlalchemy.exc.CompileError: SQLite does not support computed columns

The above exception was the direct cause of the following exception:

File "D:\Sibylle\Src\PythonVersuche\sqlalchemy\serientest_start.py", line 92, in <module>
  main()
File "D:\Sibylle\Src\PythonVersuche\sqlalchemy\serientest_start.py", line 87, in main
  scl.create_database()
File "D:\Sibylle\Src\PythonVersuche\sqlalchemy\serientest_pyp\serientest_classes.py", line 95, in create_database
  sclass = _create_connection(_TEST_CONNSTR, createdb=True)
File "D:\Sibylle\Src\PythonVersuche\sqlalchemy\serientest_pyp\serientest_classes.py", line 82, in _create_connection
  Base.metadata.create_all(engine)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\schema.py", line 4320, in create_all
  bind._run_visitor(
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\engine\base.py", line 2058, in _run_visitor
  conn._run_visitor(visitorcallable, element, **kwargs)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\engine\base.py", line 1627, in _run_visitor
  visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\visitors.py", line 144, in traverse_single
  return meth(obj, **kw)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\ddl.py", line 777, in visit_metadata
  self.traverse_single(
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\visitors.py", line 144, in traverse_single
  return meth(obj, **kw)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\ddl.py", line 821, in visit_table
  self.connection.execute(
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\engine\base.py", line 984, in execute
  return meth(self, multiparams, params)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\ddl.py", line 72, in _execute_on_connection
  return connection._execute_ddl(self, multiparams, params)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\engine\base.py", line 1035, in _execute_ddl
  compiled = ddl.compile(
File "<string>", line 1, in <lambda>
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\elements.py", line 468, in compile
  return self._compiler(dialect, bind=bind, **kw)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\ddl.py", line 29, in _compiler
  return dialect.ddl_compiler(dialect, self, **kw)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\compiler.py", line 319, in __init__
  self.string = self.process(self.statement, **compile_kwargs)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\compiler.py", line 350, in process
  return obj._compiler_dispatch(self, **kwargs)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\visitors.py", line 95, in _compiler_dispatch
  return meth(self, **kw)
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\sql\compiler.py", line 2904, in visit_create_table
  util.raise_(
File "c:\Program Files\Python38\Lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
  raise exception

sqlalchemy.exc.CompileError: (in table 'mieter_rechnung', column 'diff'): SQLite does not support computed columns

[Mike Bayer]

OK well, 2020-1-22 was just four months ago, so SQLAlchemy certainly needs some time to add support for new database features.  Please open an issue at github.com/sqlalchemy/sqlalchemy/issues .

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/35c5806d-5e5e-4991-8d64-ae1639e32378%40googlegroups.com.

[bb1898]

Am Freitag, 1. Mai 2020 17:05:27 UTC+2 schrieb Mike Bayer:

OK well, 2020-1-22 was just four months ago, so SQLAlchemy certainly needs some time to add support for new database features.  Please open an issue at github.com/sqlalchemy/sqlalchemy/issues .

True - moreover I just saw that sqlite3 still uses SQLite 3.28.0 and can't access any table in a database with computed columns in one of the tables. Opened a feature request anyway, hoping that this state of affairs is temporary.