TypeDecorator and autoincrement

[adriano...@gmail.com]

Hey everyone,

I've been experimenting with type decorations.

I have the following code snippet. Its goal is to provide a type that is INTEGER for sqlite, and BIGINTEGER for the other DBMSs. This is needed because, as far as I know, sqlite can enable AUTOINCREMENT only for INTEGER types.

class BigInteger(sqlalchemy.types.TypeDecorator):


    impl = sqlalchemy.types.TypeEngine  # Placeholder, there is `load_dialect_impl`


    def load_dialect_impl(self, dialect):
        if dialect.name == 'sqlite':
            return dialect.type_descriptor(sqlalchemy.Integer)
        else:
            return dialect.type_descriptor(sqlalchemy.BigInteger)

Then, I have the following schema definition:

_id = sqlalchemy.Column(

    BigInteger, primary_key=True, autoincrement=True

)

In case of SQLite, it works and it is an auto-incrementing primary key.

In case of PostgreSQL instead, it doesn't (no serial is added to the table).

What am I missing here?

Thanks!

[Mike Bayer]

the type itself must have integer affinity in order to be picked up as
compatible with the "autoincrement" column:

class BigInteger(sqlalchemy.types.TypeDecorator):

impl = sqlalchemy.types.Integer

def load_dialect_impl(self, dialect):
if dialect.name == 'sqlite':
return dialect.type_descriptor(sqlalchemy.Integer)
else:
return dialect.type_descriptor(sqlalchemy.BigInteger)

On 07/03/2016 03:50 PM, adriano...@gmail.com wrote:
> Hey everyone,
>
> I've been experimenting with type decorations.
> I have the following code snippet. Its goal is to provide a type that is
> INTEGER for sqlite, and BIGINTEGER for the other DBMSs. This is needed
> because, as far as I know, sqlite can enable AUTOINCREMENT only for
> INTEGER types.
>
> |
> classBigInteger(sqlalchemy.types.TypeDecorator):
>
>

> impl =sqlalchemy.types.TypeEngine # Placeholder, there is

> `load_dialect_impl`
>
>
> defload_dialect_impl(self,dialect):
> ifdialect.name =='sqlite':
> returndialect.type_descriptor(sqlalchemy.Integer)
> else:
> returndialect.type_descriptor(sqlalchemy.BigInteger)
> |
>
> Then, I have the following schema definition:
> |
> _id = sqlalchemy.Column(
> BigInteger, primary_key=True, autoincrement=True
> )
> |
>
> In case of SQLite, it works and it is an auto-incrementing primary key.
> In case of PostgreSQL instead, it doesn't (no serial is added to the table).
>
> What am I missing here?
> Thanks!
>

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

[mike bayer]

On 07/04/2016 03:34 PM, Mike Bayer wrote:
> the type itself must have integer affinity in order to be picked up as
> compatible with the "autoincrement" column:
>
> class BigInteger(sqlalchemy.types.TypeDecorator):
>
> impl = sqlalchemy.types.Integer
>
> def load_dialect_impl(self, dialect):
> if dialect.name == 'sqlite':
> return dialect.type_descriptor(sqlalchemy.Integer)
> else:
> return dialect.type_descriptor(sqlalchemy.BigInteger)
>

unfortunately this might give you SERIAL and not BIGSERIAL. Continuing
with the tradition of extremely rare issues always being reported in
twos on the same day, see
https://bitbucket.org/zzzeek/sqlalchemy/issues/3739/using-with_variant-on-a-type-disables
which is essentially the same thing.

[Adriano Di Luzio]

Thanks guys, for my use case I can live with SERIAL.
Then, when fixed upstream, I'll update.

Regards,
Adriano

> You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/lyDtydtqqVM/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.