title: mysql and python datetime
body: <p>I am trying to insert records to a <code>mysql</code> database via <code>python</code> using the <code>sqlalchemy</code> package.</p>
  
  <p>I have columns that are <code>datetime</code> type in mysql that already accepted data of the following format via a load table inline  command:</p>
  
  <pre><code>'2013-04-03 00:05:00-05:00'
  </code></pre>
  
  <p>Note this is produced by the <code>pytz</code> module in python. I had no problem loading 600,000 rows with datetime stamps with the exact same format through the mysql console using the <code>load table inline &lt;file_name&gt;</code> command. MySql accepts them on the load but I'm not sure if it maintains the time-zone information. It really doesn't matter to me because I have3 other columns, which represent datetimes in other time zones that I am interested in. So I know which column represents EST/EDT or CST/CDT and I can query based on that if I desire. I won't be doing any time zone conversions in mysql because I already have columns that represent the time zones of interest.  </p>
  
  <p>This is as per mysql's documentation:</p>
  
  <pre><code>The DATETIME type is used for values that contain both date and time parts. MySQL
  retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported
  range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. 
  </code></pre>
  
  <p>The thing is the <code>pytz</code> module provides information on the time zone in the datetime stamp that I am trying to insert. I actually have 4 columns <code>(EST, UTC, EST/EDT and CST/CDT)</code> so all have their time zone information embedded in the datetime stamp. </p>
  
  <p>Note I am not using a custom <code>INSERT</code> query. This is the default way sqlalchemy performs an insert many:</p>
  
  <p>The function that performs the insert looks like so:</p>
  
  <pre><code>def insert_data(data_2_insert, table_name):
      # Connect to database using SQLAlchemy's create_engine()
      engine = create_engine('mysql://blah:blah@localhost/db_name')
      # Metadata is a Table catalog. 
      metadata = MetaData()
      my_table = Table(table_name, metadata, autoload=True, autoload_with=engine)
      column_names = tuple(c.name for c in my_table.c)
      final_data = [dict(zip(column_names, x)) for x in data_2_insert]
      ins = my_table.insert()
      conn = engine.connect()
      conn.execute(ins, final_data)
      conn.close()
  </code></pre>
  
  <p>The error message: </p>
  
  <pre><code>   Traceback (most recent call last):
        File "script.py", line 191, in &lt;module&gt;
          main()
        File "script.py", line 39, in main
          insert_data(file_csv, table_name)
        File "script.py", line 58, in insert_data
          conn.execute(ins, final_data)
        File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 824, in execute
          return Connection.executors[c](self, object, multiparams, params)
        File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement
          return self.__execute_context(context)
        File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 894, in __execute_context
          self._cursor_executemany(context.cursor, context.statement, context.parameters, context=context)
        File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 960, in _cursor_executemany
          self._handle_dbapi_exception(e, statement, parameters, cursor, context)
        File "/usr/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
          raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
      sqlalchemy.exc.OperationalError: (OperationalError) (1292, "Incorrect datetime value: '2013-04-03 00:05:00-05:00' for column 'rtc_date_est' at row 1")
  </code></pre>