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.
On Friday, January 10, 2014 12:20:45 PM UTC-8, Steve Johnson wrote:
>
> 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].
>> To unsubscribe from this group, send email to
>> [email protected].
>> 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.
