skipping MSSQL TIMESTAMP column on INSERT

[Matt Bodman]

Hi,

I am autoloading tables from an MSSQL db. A lot of the tables have
the MSSQL TIMESTAMP column. So, when inserting to the table, I get an
IntegrityError:

sqlalchemy.exc.IntegrityError: (IntegrityError) ('23000', '[23000]
[FreeTDS][SQL Server]Cannot insert an explicit value into a timestamp
column. Use INSERT with a column list to exclude the timestamp column,
or insert a DEFAULT into the timestamp column. (273) (SQLPrepare)'

Is there a way around this without having to map every column
explicitly?

Thanks,

Matt

[Michael Bayer]

Ideally this TIMESTAMP column would have a default declared at the server level:

CREATE TABLE mytable (
...

some_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

otherwise you're using python-side defaults, so you'd need to write a post-table processing function:

from sqlalchemy import TIMESTAMP

def add_defaults(table):
for c in table.c:
if isinstance(c.type, TIMESTAMP):
c.default = <some default>
# ... or whatever you'd want here, c.server_default=FetchedValue(), etc

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Matt Bodman]

Hi Michael,

Thanks for your reply.  Please be patient with me though as I don't quite get it.

Where and when is the add_default function called?  Won't I get the same error trying to insert 'some default' into the column? 

Any further explanation would be great.

Matt

[Michael Bayer]

you'd call it as soon as you reflect your table.

my_table = Table('some_table', metadata, autoload=True)

add_default(my_table)

probably worthwhile to make a function:

def reflect_table(name, metadata):

    my_table = Table('some_table', metadata, autoload=True)

    add_default(my_table)

    return my_table

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/05kepNetnrMJ.

[Michael Bayer]

On Sep 15, 2011, at 10:51 AM, Matt Bodman wrote:

...and, once you mark the column as having some kind of default, SQLAlchemy won't try to insert NULL into it. It's not clear here what kind of default is already on the column - though if there was one the reflection system should have gotten it.

Unless its the case that a TIMESTAMP column in SQL Server is inherently "default generating"? I haven't checked. If that's the case you'd want to set server_default=FetchedValue() on each Column. Also we'd probably want to add that to the dialect on our end at some point but it's not something I've researched.

[Matt Bodman]

Thanks so much Michael.. just to wrap up this thread, I got it working like this:

class Thing(Base):

    __tablename__ = 'tbThings'

    __table_args__ = (

            {'autoload':True,'autoload_with':engine,'extend_existing':True}

            )

    LastUpdated = Column('LastUpdated', TIMESTAMP, FetchedValue())

Thanks again,

Matt

[mdob]

Hi, 

It's been some time since this topic was created. Has anything changed on that matter or manually setting column.server_default=FetchedValue() is still the best way to do it?

Kind regards, 

Michał

[Michael Bayer]

probably (maybe we should improve on our end, though).  but when you’re autoloading, you can set this default up automatically using the column_reflect event:

http://docs.sqlalchemy.org/en/rel_0_9/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect

as you receive events here, look at the “type” to see if its TIMESTAMP, and if so, populate the column_info dictionary with column_info[‘default’] = FetchedValue().

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[mdob]

@event.listens_for(Table, 'column_reflect')
def receive_column_reflect(inspector, table, column_info):
    if isinstance(column_info['type'], TIMESTAMP):
        column_info['default'] = FetchedValue()



  table = Table(table_name, metadata, autoload=True, autoload_with=engine, include_columns=columns)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 416, in __new__
  File "build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py", line 60, in __exit__
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 411, in __new__
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 484, in _init
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 496, in _autoload
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1972, in run_callable
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1477, in run_callable
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 364, in reflecttable
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py", line 568, in reflecttable
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py", line 618, in _reflect_column
  File "<string>", line 2, in text
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 1425, in _create_text
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 1289, in __init__
TypeError: expected string or buffer

Something's not right.

[mdob]

Maybe this should go into server_default. Because cole belowe worked  fine.

for col in table.columns:
    if isinstance(col.type, TIMESTAMP):
        col.server_default = FetchedValue()

Updating column_info['server_default'] = FetchedValue() in event handler didn't work.

[Mike Bayer]

On 12/11/2015 01:12 PM, mdob wrote:
> |
>
> @event.listens_for(Table,'column_reflect')
> defreceive_column_reflect(inspector,table,column_info):
> ifisinstance(column_info['type'],TIMESTAMP):
> column_info['default']=FetchedValue()
>

the reflection wants to assume the FetchedValue() is a reflected default
string, so just make it look like one:


column_info['default'] = "some_default"



>

>
> table
> =Table(table_name,metadata,autoload=True,autoload_with=engine,include_columns=columns)
> File"build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py",line
> 416,in__new__
> File"build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py",line
> 60,in__exit__
> File"build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py",line
> 411,in__new__
> File"build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py",line
> 484,in_init
> File"build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py",line
> 496,in_autoload
> File"build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py",line
> 1972,inrun_callable
> File"build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py",line
> 1477,inrun_callable
> File"build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py",line
> 364,inreflecttable
>
> File"build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py",line
> 568,inreflecttable
>
> File"build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py",line
> 618,in_reflect_column
> File"<string>",line 2,intext
> File"build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py",line
> 1425,in_create_text
> File"build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py",line
> 1289,in__init__
> TypeError:expected stringorbuffer

> |
>
> Something's not right.
>
> On Friday, November 14, 2014 at 3:48:59 PM UTC+1, Michael Bayer wrote:
>
> probably (maybe we should improve on our end, though). but when
> you’re autoloading, you can set this default up automatically using
> the column_reflect event:
>
> http://docs.sqlalchemy.org/en/rel_0_9/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect
> <http://docs.sqlalchemy.org/en/rel_0_9/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect>
>
> as you receive events here, look at the “type” to see if its
> TIMESTAMP, and if so, populate the column_info dictionary with
> column_info[‘default’] = FetchedValue().
>
>
>> On Nov 14, 2014, at 7:04 AM, mdob <mike.do...@gmail.com

>> send an email to sqlalchemy+...@googlegroups.com <javascript:>.

>> To post to this group, send email to sqlal...@googlegroups.com

>> <javascript:>.

>> Visit this group at http://groups.google.com/group/sqlalchemy

>> <http://groups.google.com/group/sqlalchemy>.

>> For more options, visit https://groups.google.com/d/optout

>> <https://groups.google.com/d/optout>.

>
> --
> 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>.