mysql and python datetime

Question

I am trying to insert records to a mysql database via python using the sqlalchemy package.

I have columns that are datetime type in mysql that already accepted data of the following format via a load table inline command:

'2013-04-03 00:05:00-05:00'

Note this is produced by the pytz 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 load table inline <file_name> 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.

This is as per mysql's documentation:

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

The thing is the pytz module provides information on the time zone in the datetime stamp that I am trying to insert. I actually have 4 columns (EST, UTC, EST/EDT and CST/CDT) so all have their time zone information embedded in the datetime stamp.

Note I am not using a custom INSERT query. This is the default way sqlalchemy performs an insert many:

The function that performs the insert looks like so:

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()

The error message:

   Traceback (most recent call last):
      File "script.py", line 191, in <module>
        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")

The last part seems broken: 2013-04-03 00:05:00-05:00, there shouldn't be a -05:00 there, right? I suppose that sqlalchemy doesn't do much type checking in this case. — Wolph
– Wolph, Commented Apr 4, 2013 at 22:09
Initially, my data is in EST. But I need that time in 2 other time zones (EST/EDT and CST/CDT). I don't want to perform a conversation in sql every time I need to run a date query because I will be doing that a lot. So its a storage vs performance trade off. So in python I take the datetime string, assign it to a EST time zone via pytz.timezone('EST') and then perform 3 conversions- to 'US/Eastern', 'US/Central' and utc. So the '-05:00' is to show that it is EST. Similarly, +00:00 is UTC, -04:00 for EST/EDT and -05:00 for CST/CDT. — codingknob
– codingknob, Commented Apr 4, 2013 at 22:34
the '-05:00' extension is present in the object that I try to insert into mysql. Note, mysql does not complain about this because I am successful via the load table. So this is a problem with sqlalchemy. How do I go about achieving my objective without re-writing my approach. I thank you for your help. — codingknob
– codingknob, Commented Apr 4, 2013 at 22:36
Maybe I should abandon sqlalchemy and just communicate to mysql directly via mysqldb package? — codingknob
– codingknob, Commented Apr 4, 2013 at 22:37
No need to abandon sqlalchemy, but you do have to tell it that you want to use timestamps: docs.sqlalchemy.org/en/rel_0_8/core/… Also, I cannot see it from the stacktrace, but is, what you are trying to insert, actually a datetime object or is it a string? — Wolph
– Wolph, Commented Apr 4, 2013 at 22:45

Swarf · Accepted Answer · 2015-05-20 02:04:10Z

0

It appears that SQLAlchemy does not respect the MySQL DATETIME format. SQLAlchemy is trying to put a timezone-aware format into MySQL DATETIME which doesn't support timezone on a per-row basis AFAIK.

The solution is to strip timezone info away from your datetime objects. After converting them into a timezone matching your MySQL default (UTC, EST, whatever), then do the following:

date_value = date_value.replace(tzinfo=None)

where date_value is the datetime object which ends up being passed to your SQLAlchemy object.

answered May 20, 2015 at 2:04

Swarf

663 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql and python datetime

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related