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

[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 

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

[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
 
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.dobrzan...@gmail.com 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 
 mailto:sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com 
 mailto:sqlalchemy@googlegroups.com.
 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+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.

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

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

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]

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

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