Cannot generate models for sqlite db that uses sequence for PK

[Mark Aquino]

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

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

[Mike Bayer]

On Thu, Sep 13, 2018 at 1:03 PM, Mark Aquino <maqui...@gmail.com> 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

> --
> 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 post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.