Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[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"",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
> 
> 
> 
> 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 > > wrote:
>>
>> 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ł
>>
>> On Thursday, September 15, 2011 7:58:49 AM UTC+2, Matt Bodman wrote:
>>
>> 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 
>>
>>
>> -- 
>> 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
>> .
> 
> -- 
> 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+unsubscr...@googlegroups.com
> .
> To post to this group, send email to sqlalchemy@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit 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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
Fo

Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

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



On Friday, December 11, 2015 at 7:12:31 PM UTC+1, mdob wrote:
>
>
> @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 "", 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.
>
> 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
>>
>> 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  wrote:
>>
>> 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ł
>>
>> On Thursday, September 15, 2011 7:58:49 AM UTC+2, Matt Bodman wrote:
>>>
>>> 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 
>>>
>>>
>> -- 
>> 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.
>>
>>
>>

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

Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[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 "", 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.

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
>
> 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 > 
> wrote:
>
> 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ł
>
> On Thursday, September 15, 2011 7:58:49 AM UTC+2, Matt Bodman wrote:
>>
>> 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 
>>
>>
> -- 
> 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.
>
>
>

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

Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[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().


> On Nov 14, 2014, at 7:04 AM, mdob  wrote:
> 
> 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ł
> 
> On Thursday, September 15, 2011 7:58:49 AM UTC+2, Matt Bodman wrote:
> 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 
> 
> 
> -- 
> 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+unsubscr...@googlegroups.com 
> .
> To post to this group, send email to sqlalchemy@googlegroups.com 
> .
> Visit this group at http://groups.google.com/group/sqlalchemy 
> .
> For more options, visit 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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[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

-- 
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/-/l8KrkR59HGQJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[Michael Bayer]

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

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


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



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

Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[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



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

> 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
> 
> -- 
> 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.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at 
> http://groups.google.com/group/sqlalchemy?hl=en.

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

Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[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

-- 
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.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[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 = 
# ... or whatever you'd want here, c.server_default=FetchedValue(), 
etc



On Sep 15, 2011, at 1:58 AM, Matt Bodman wrote:

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

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

[sqlalchemy] 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

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