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
--
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/35c5806d-5e5e-4991-8d64-ae1639e32378%40googlegroups.com.
