autoincrement=True Column() kwarg does not work about sqlalchemy-redshift HOT 23 OPEN

Somehow related to this, I have a problem with a column I want to be auto-incremented, when I don't specify its value:

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class MyTable(Base):
    __tablename__ = "mytable"
    _id = Column(Integer, primary_key=True, info={"identity":(0,1)}) 
    name = Column(String(50))


mytable = MyTable().__table__
engine = sa.create_engine("redshift+psycopg2://username:password@host")
session = sessionmaker(bind=engine)()
mytable.create(bind=engine)
new_element = MyTable(name="hello")
session.add(new_element)
session.commit() #raises StatementError

The last command raises a StatementError:

StatementError: (sqlalchemy.exc.ProgrammingError) (psycopg2.ProgrammingError) relation "mytable__id_seq" does not exist
 [SQL: 'select nextval(\'"mytable__id_seq"\')'] [SQL: 'INSERT INTO mytable (_id) VALUES (%(_id)s)'] [parameters: [{}]]

The expected results should be if I query the table:
[(0, "hello")]

Can you please tell me how I can auto-increment a Column when the value is not specified ?

EDIT:
My question is related to this SO post. Apparently, there is no way to answer my question since redshift doesn't support sequence... ?

A possible answer has been layed out here, related to #48

from sqlalchemy-redshift.

Just got this working by specifying redshift_identity=(0, 1) as a kwarg in the sa.Column definition, which is the new syntax (replacing info={'identity': (0, 1)}), in SQLAlchemy > 1.3.

from sqlalchemy-redshift.

This should be closed by #91

from sqlalchemy-redshift.

There's no way to use autoincrement ID and get back the last inserted ID.

You have to use a natural key or some other pre-computed identifier

from sqlalchemy-redshift.

I solve it myself.
I just remove id column like following.

class Department(Base):
  __tablename__ = 'departments'  
  name = Column(String(256), nullable=False)

from sqlalchemy-redshift.

Redshift doesn't support autoincrement you probably want to use info={'identity': (0, 1)} instead.

Be warned redshift doesn't support RETURNING or any other way to get back the last inserted row ID

from sqlalchemy-redshift.

Thanks! Is this covered somewhere in the documentation?

from sqlalchemy-redshift.

@jseabold the tests don't pass, so I've not closed the issue.

from sqlalchemy-redshift.

You proposed how to make them pass and have access to the cluster. Do you
want me to make a patch to the tests and stick you as the author or do you
want to make the PR?

On Wed, Aug 24, 2016 at 11:47 AM Thomas Grainger [email protected]
wrote:

@jseabold https://github.com/jseabold the tests don't pass, so I've not
closed the issue.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#80 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AASE5DJ5xKu6kuTJT_SaljQmSvCNgKHoks5qjHW8gaJpZM4G4fHp
.

from sqlalchemy-redshift.

could you make the PR, I thought you had a cluster of your own?

from sqlalchemy-redshift.

Will do. Yeah, it works on my cluster, but I suspect the autoincrement
behavior is different with multiple slices vs the test cluster.

On Wed, Aug 24, 2016 at 12:09 PM Thomas Grainger [email protected]
wrote:

could you make the PR, I thought you had a cluster of your own?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#80 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AASE5IIYjYzQj-2fi3-CV1c8TFlsungrks5qjHrZgaJpZM4G4fHp
.

from sqlalchemy-redshift.

@jseabold if you're super cool you can add travis JWT support to https://github.com/sqlalchemy-redshift/bigcrunch so that instead of returning a database it returns a database and fresh limited user/password for any travis build.

travis-ci/docs-travis-ci-com#674

from sqlalchemy-redshift.

bigcrunch is what the travis workers use to boot Redshift workers to build the tests

from sqlalchemy-redshift.

@jseabold in the meantime you're a contributor now! If you push branches to this repo, you'll boot the redshift cluster.

from sqlalchemy-redshift.

Ha, thanks. I don't know what I've done to deserve such great power. Will
use it judiciously.

On Wed, Aug 24, 2016 at 12:17 PM Thomas Grainger [email protected]
wrote:

@jseabold https://github.com/jseabold in the meantime you're a
contributor now! If you push branches to this repo, you'll boot the
redshift cluster.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#80 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AASE5DgBWwN8UFKVu840u_jnqUcdU5byks5qjHyygaJpZM4G4fHp
.

from sqlalchemy-redshift.

I don't use redshift anymore, but I'd like to keep the project alive. I need more people who actually use Redshift to be contributors/maintainers.

Sadly at the moment, you have to have a commit-bit to run the tests, but if travis-ci/docs-travis-ci-com#674 gets integrated into bigcrunch I'll be less liberal with giving out commit-bits.

from sqlalchemy-redshift.

This is a pretty old thread but is there any chance there has been any additional work done on this? I'm having the same issue,

 class application(Base):
    __tablename__ = "application"
    __table_args__ = {}
    app_id = Column('app_id',Integer, primary_key=True, autoincrement=False, nullable=True, info={"identity": (0,1)})
    application_name = Column(String(20))

I can create the table using a the declarative base, this creates my app_id column as an integer with identity(0,1) which essentially simulates what a sequence does (automatically generates values on insert).

I can insert values into Redshift manually by specifying the columns ie:
insert into application ('application_name') values ('blah');

That works but I have tried a several different ways to replicate this in sqlalchemy (essentially I want to do an insert without specifying the ID column) to no avail.

from sqlalchemy-redshift.

@fzembow but, be warned there's no point in an autoincrement PK if you want to create useful FKs. I'd recommend a UUID generated locally

from sqlalchemy-redshift.

Is there a good reason not to set dialect_options["redshift"]["identity"] = (0, 1) when autoincrement is set to true on a column? It is what we ultimately chose to do in our project using a compiler hook on the CreateColumn object as a work around. It might not exactly match all behavior in other database systems such as being able to return the value, but it none-the-less can automatically generate guaranteed unique values for a primary key, which is all many applications require from an autoincrement column.

from sqlalchemy-redshift.

@graingert
Hello there.

Could you tell me how do you define model?

I defined like following, but, I got error.

class Department(Base):
  __tablename__ = 'departments'  
  id = Column(Integer, redshift_identity=(1, 1), primary_key=True, unique=True)   name = Column(String(256), nullable=False)
  name = Column(String(256), nullable=False)

Error.

[SQL: select nextval('"departments_id_seq"')]
(Background on this error at: http://sqlalche.me/e/14/f405)

Do you have any suggestion from above source code?

Regards,

from sqlalchemy-redshift.

@soseki-angfa sorry I don't work with redshift at the moment

from sqlalchemy-redshift.

@graingert
Okay.
Thank you for reply.

from sqlalchemy-redshift.

Wondering if there's now a solution for this?
I wasn't able to make anything work that is in this thread, but worked around it like this.

for _ in list_of_table_objects:
            
            _.id = (session.query(func.max(Table.id)).scalar() or 0) + 1
            session.merge(_)
            session.commit()

Hoping someone has something better

from sqlalchemy-redshift.