How to stop SQLAlchemy from adding an ON UPDATE clause to a TIMESTAMP column by default

[Ben Hayden]

Say I have a model (running on MySQL):

class Foo(DeclarativeBase):

    __tablename__ = 'foo'

    #column definitions

    id = Column(u'id', CHAR(length=36), default=uuid, primary_key=True, nullable=False)

    date_added = Column(u'dateAdded', TIMESTAMP(), nullable=False)

    reason = Column(u'reason', TEXT())

                                                                                                                                                   

The Create Table SQL being generated is:

CREATE TABLE `foo` (

  `id` char(36) NOT NULL,

  `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `reason` text,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'd like for the dateAdded column to be a dateAdded - not dateUpdated, which would leave the Create Table SQL to look like this:

CREATE TABLE `foo` (

  `id` char(36) NOT NULL,

  `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `reason` text,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Any ideas on how to do this? I keep googlin' around, but setting onupdate or server_onupdate doesn't seem to work for me (yet...) Thanks!

[Michael Bayer]

SQLA doesn't generate those defaults without being told explicitly.   Are you sure you don't have some other table metadata, or perhaps schema events in place which are adding defaults ?

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Table, CHAR, TIMESTAMP, TEXT, schema, Column

from uuid import uuid4 as uuid

Base = declarative_base()

class Foo(Base):

    __tablename__ = 'foo'

    #column definitions

    id = Column(u'id', CHAR(length=36), default=uuid, primary_key=True, nullable=False)

    date_added = Column(u'dateAdded', TIMESTAMP(), nullable=False)

    reason = Column(u'reason', TEXT())

from sqlalchemy.dialects import mysql

print schema.CreateTable(Foo.__table__).compile(dialect=mysql.dialect())

output:

CREATE TABLE foo (

id CHAR(36) NOT NULL, 

`dateAdded` TIMESTAMP, 

reason TEXT, 

PRIMARY KEY (id)

)

--
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/-/36ZXHV5mZMIJ.
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.

[Ben Hayden]

Hmm... well this is a weird problem then. I ran the provided code, and got the same result you did, with the DEFAULT & ON UPDATE missing. However, I added a couple lines:

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Table, CHAR, TIMESTAMP, TEXT, schema, Column

from uuid import uuid4 as uuid

Base = declarative_base()

class Foo(Base):

    __tablename__ = 'foo'

    #column definitions

    id = Column(u'id', CHAR(length=36), default=uuid, primary_key=True, nullable=False)

    date_added = Column(u'dateAdded', TIMESTAMP(), nullable=False)

    reason = Column(u'reason', TEXT())

from sqlalchemy.dialects import mysql

print schema.CreateTable(Foo.__table__).compile(dialect=mysql.dialect())

Base.metadata.bind = db.generate_engine()

Base.metadata.drop_all()

Base.metadata.create_all()                                                 

The create table that was actually generated in the db is still:

CREATE TABLE `foo` (

  `id` char(36) NOT NULL,

  `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `reason` text,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My generate_engine method is a little helper method that returns an engine with the following params:

create_engine('mysql://%s:%s@%s/%s' % (

                     config.get('database', 'user'),

                     urllib.quote_plus(config.get('database', 'pass')),

                     config.get('database', 'host'),

                     config.get('database', 'name')),

                     convert_unicode=True, pool_size=20, pool_recycle=60,

                     connect_args={'use_unicode': True, 'charset': 'utf8', 'compress': True})

Am I unknowingly passing a default I shouldn't to SQLA that is causing the generation of the table to add those defaults? Or is there an option in MySQL that I unknowingly have turned on?

My versions:

Python 2.7

SQLA 0.7.*

MySQL version 5.5.11

[Ben Hayden]

Well - I don't know what's going on, but I did find a workaround (for me at least, your mileage may vary), I set server_default to this:

date_added = Column(u'dateAdded', TIMESTAMP(), server_default=text('CURRENT_TIMESTAMP'), nullable=False)

Which gives me:

CREATE TABLE foo (

id CHAR(36) NOT NULL, 

`dateAdded` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 

reason TEXT, 

PRIMARY KEY (id)

)

Which is what I want. Still a weird issue though. Thanks!

[Michael Bayer]

There's some more happening on your end.   Rest assured DEFAULT and ON UPDATE are not generated without very specific and explicit instructions - the "default" and "onupdate" keywords would need to be passed to your Column - engine arguments have nothing to do with it.    If it were me I'd stick a pdb into Column to intercept it happening.

Here is the output of your program:

2011-11-28 20:00:28,203 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()

2011-11-28 20:00:28,203 INFO sqlalchemy.engine.base.Engine ()

2011-11-28 20:00:28,205 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'

2011-11-28 20:00:28,205 INFO sqlalchemy.engine.base.Engine ()

2011-11-28 20:00:28,206 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'

2011-11-28 20:00:28,206 INFO sqlalchemy.engine.base.Engine ()

2011-11-28 20:00:28,207 INFO sqlalchemy.engine.base.Engine SHOW COLLATION

2011-11-28 20:00:28,207 INFO sqlalchemy.engine.base.Engine ()

2011-11-28 20:00:28,211 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

2011-11-28 20:00:28,211 INFO sqlalchemy.engine.base.Engine ()

2011-11-28 20:00:28,212 INFO sqlalchemy.engine.base.Engine DESCRIBE `foo`

2011-11-28 20:00:28,212 INFO sqlalchemy.engine.base.Engine ()

2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine 

DROP TABLE foo

2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine ()

2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine COMMIT

2011-11-28 20:00:28,216 INFO sqlalchemy.engine.base.Engine DESCRIBE `foo`

2011-11-28 20:00:28,216 INFO sqlalchemy.engine.base.Engine ()

2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine ROLLBACK

2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine 

CREATE TABLE foo (

id CHAR(36) NOT NULL, 

`dateAdded` TIMESTAMP, 

reason TEXT, 

PRIMARY KEY (id)

)

2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine ()

2011-11-28 20:00:28,275 INFO sqlalchemy.engine.base.Engine COMMIT

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

[Steve Johnson]

I realize this thread is ancient, but I'm resurrecting it for Googleable posterity since I just ran across the same issue.

The problem is that MySQL "helpfully" inserts the ON UPDATE cheese unless you specify a default and/or a NULL/NOT NULL value in the CREATE TABLE query.

http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

Unfortunately, I haven't yet been able to get sqlalchemy to actually send such a query, this being my attempt:

    time = Column(
        TIMESTAMP(), primary_key=True,
        default=datetime.min, nullable=False)

Will reply again if I manage to get a TIMESTAMP column without the ON UPDATE stuff.

[Steve Johnson]

My solution, since sqlalchemy seems to be ignoring the nullable and default kwargs, is this:

    time = Column(
        TIMESTAMP(), primary_key=True,

        server_default=text("'0000-00-00 00:00:00'"))

The default is just never used.