On Thu, Sep 13, 2018 at 1:03 PM, Mark Aquino <[email protected]> wrote: > Took me a little while to figure out what was going on, but it seems the > code breaks when you try to use a primary key column that uses a sequence in > sqlite, > > e.g. if my schema is this: > > > create table if not exists generic_sequence ( > id integer primary key autoincrement, > visible_id integer, > alias text not null, > content text not null, > annotation_json clob not null, > checksum text not null > ); > > > if I tell the constructor of "Model" to print the column and column type for > each column in the table (line 83 in codegen.py), you can see what happens > in the output/stacktrace below
So I'm not the sqlacodegen person (he is usually on IRC, not sure if he reads this list regularly), but this "sqlite_sequence" table was a surprise to me, but yes it seems to be generated when you happen to use the special "autoincrement" keyword within the SQLite model. The two options here are: 1. don't use that "autoincrement" keyword in your database schema, if possible or 2. whereever you are telling your program to reflect all the tables in the database (again I don't know sqlacodegen's API), you need to exclude the table named "sqlite_sequence" as this seems to be a fixed table that SQLite is generating. For example, if you were using metadata.reflect(), you'd want to pass a callable to "only" as: metadata.reflect(only=lambda name, m: name != 'sqlite_sequence') this parameter is described at http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=metadata%20reflect#sqlalchemy.schema.MetaData.reflect.params.only > > > class Model(object): > def __init__(self, table): > super(Model, self).__init__() > self.table = table > self.schema = table.schema > print(table) > > # Adapt column types to the most reasonable generic types (ie. VARCHAR -> > String) > for column in table.columns: > print(column) > print(column.type) > column.type = self._get_adapted_type(column.type, column.table.bind) > > > > sqlacodegen sqlite:///sqlite.db > > > generic_sequence.id > INTEGER > > > generic_sequence.visible_id > INTEGER > > generic_sequence.alias > TEXT > > > generic_sequence.content > TEXT > > > generic_sequence.annotation_json > TEXT > > > generic_sequence.checksum > TEXT > > sqlite_sequence > sqlite_sequence.name > Traceback (most recent call last): > File "/Users/aquinmx3/venv/gbrs-seq-anno3.7/bin/sqlacodegen", line 11, in > <module> > sys.exit(main()) > File > "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlacodegen/main.py", > line 51, in main > args.noinflect, args.noclasses) > File > "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlacodegen/codegen.py", > line 418, in __init__ > model = self.table_model(table) > File > "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlacodegen/codegen.py", > line 93, in __init__ > print(column.type) > File > "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlalchemy/sql/type_api.py", > line 589, in __str__ > return str(self.compile()) > File > "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlalchemy/sql/type_api.py", > line 573, in compile > return dialect.type_compiler.process(self) > File > "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", > line 293, in process > return type_._compiler_dispatch(self, **kw) > File > "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", > line 81, in _compiler_dispatch > return meth(self, **kw) > File > "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", > line 2969, in visit_null > "type on this Column?" % type_) > sqlalchemy.exc.CompileError: Can't generate DDL for NullType(); did you > forget to specify a type on this Column? > > > So it looks like a sqlite_sequence object gets implicitly added to the table > (when you use autoincrement with primary key in sqlite dialect) and it is > passed to the Model but the code doesn't know how to handle it / what it is. > > I assume since the model doesn't need to know about the sequence in sqlite > (the database will handle the pk generation it doesn't affect the models in > any way), that just checking for if column == sqlite_sequence then pass > would fix this but that's just a guess > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
