Re: [sqlalchemy] SQLite supports generated column, but SQLAlchemy doesn't seem to know this
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. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a68dfffb-6f0d-4e43-968f-fdf999c0669f%40googlegroups.com.
Re: [sqlalchemy] SQLite supports generated column, but SQLAlchemy doesn't seem to know this
On Fri, May 1, 2020, at 10:55 AM, bb1898 wrote: > 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") 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.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 > 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 "", line 1, in > 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.state
[sqlalchemy] SQLite supports generated column, but SQLAlchemy doesn't seem to know this
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 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 "", line 1, in 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