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. On Monday, November 28, 2011 5:09:35 PM UTC-8, Michael Bayer wrote: > > 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 > > On Nov 28, 2011, at 5:38 PM, Ben Hayden wrote: > > 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 > > > -- > 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. > To post to this group, send email to [email protected]<javascript:> > . > To unsubscribe from this group, send email to > [email protected] <javascript:>. > 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 unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
