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 have data of the following format: 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. This tells my that mysql is capable of accepting the format shown above. 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, which I need. 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() 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") python <http://stackoverflow.com/questions/tagged/python> mysql<http://stackoverflow.com/questions/tagged/mysql> insert <http://stackoverflow.com/questions/tagged/insert> sqlalchemy<http://stackoverflow.com/questions/tagged/sqlalchemy> share <http://stackoverflow.com/q/15821953/668624>|edit<http://stackoverflow.com/posts/15821953/edit> |close|delete<http://stackoverflow.com/questions/15821953/mysql-and-python-datetime#> |flag<http://stackoverflow.com/questions/15821953/mysql-and-python-datetime#> edited just now <http://stackoverflow.com/posts/15821953/revisions> asked 15 mins ago <http://stackoverflow.com/users/668624/algotr8der> algotr8der <http://stackoverflow.com/users/668624/algotr8der> 406211 add comment question eligible for bounty in 2 days <http://stackoverflow.com/faq#bounty> Know someone who can answer? Share a link<http://stackoverflow.com/q/15821953/668624>to this question via email<?subject=Stack%20Overflow%20Question&body=mysql%20and%20python%20datetime%0Ahttp%3a%2f%2fstackoverflow.com%2fq%2f15821953%2f668624%3fsem%3d2>, Google+<https://plus.google.com/share?url=http%3a%2f%2fstackoverflow.com%2fq%2f15821953%2f668624%3fsgp%3d2>, Twitter<http://twitter.com/share?url=http%3a%2f%2fstackoverflow.com%2fq%2f15821953%2f668624%3fstw%3d2&text=mysql%20and%20python%20datetime>, or Facebook<http://www.facebook.com/sharer.php?u=http%3a%2f%2fstackoverflow.com%2fq%2f15821953%2f668624%3fsfb%3d2&t=mysql%20and%20python%20datetime>. Would you like to have responses to your questions sent to you via email<http://stackoverflow.com/questions/15821953/mysql-and-python-datetime#>? tagged python <http://stackoverflow.com/questions/tagged/python> × 176908 mysql <http://stackoverflow.com/questions/tagged/mysql> × 156059 insert <http://stackoverflow.com/questions/tagged/insert> × 4685 sqlalchemy <http://stackoverflow.com/questions/tagged/sqlalchemy> × 2670 asked *today* viewed *15 times* <http://engine.adzerk.net/r?e=eyJ0cyI6MTM2NTExMTQxMzI3NywiYXYiOjQxNCwiYXQiOjE3LCJjbSI6ODQ3LCJjaCI6MTE3OCwiY3IiOjIzODU4LCJkbSI6MSwiZmMiOjM2NzQ4LCJmbCI6MjQ0NCwia3ciOiJweXRob24sbXlzcWwsaW5zZXJ0LHNxbGFsY2hlbXkseC11c2VyLXJlZ2lzdGVyZWQseC0yMDBwbHVzLXJlcCIsIm53IjoyMiwicmYiOiJodHRwOi8vc3RhY2tvdmVyZmxvdy5jb20vcG9zdHMvMTU4MjE5NTMvZWRpdCIsInJ2IjowLCJwciI6MTU2OCwic3QiOjgyNzcsInpuIjo0NSwiZGkiOiIyYjZkMTUzNzA1MzY0M2UwODYzOTIxNTg1OWIwYzE0ZCIsInVyIjoiaHR0cDovL2NhcmVlcnMuc3RhY2tvdmVyZmxvdy5jb20vIn0&s=KE7_z-C8gTDMygPpgz_bTub8NaU> - Chief Product Officer codename: cheesecake Palo Alto, CA / remote <http://careers.stackoverflow.com/jobs/32221/chief-product-officer-codename-cheesecake?a=C4ht3JC> - Senior Software Engineer Objectivity Sunnyvale, CA <http://careers.stackoverflow.com/jobs/31531/senior-software-engineer-objectivity?a=BfJJvJm> - Server Side Engineer NetApp Sunnyvale, CA <http://careers.stackoverflow.com/jobs/31441/server-side-engineer-netapp?a=B98Z8qs> - More jobs near Santa Clara...<http://careers.stackoverflow.com/jobs/location/santa%20clara%2c%20ca%2c%20united%20states?a=vYY> Related 0 <http://stackoverflow.com/q/5156915?rq=1> Sqlalchemy can't connect to a mysql server<http://stackoverflow.com/questions/5156915/sqlalchemy-cant-connect-to-a-mysql-server?rq=1> 0 <http://stackoverflow.com/q/14946666?rq=1> creating a table in mysql using pymysql and sqlalchemy<http://stackoverflow.com/questions/14946666/creating-a-table-in-mysql-using-pymysql-and-sqlalchemy?rq=1> 0 <http://stackoverflow.com/q/2649262?rq=1> ImportError with Pylons/SQLAlchemy and MySQL<http://stackoverflow.com/questions/2649262/importerror-with-pylons-sqlalchemy-and-mysql?rq=1> 0 <http://stackoverflow.com/q/14936319?rq=1> Flask App returning “MySQL server has gone away” after 60 seconds of idle time<http://stackoverflow.com/questions/14936319/flask-app-returning-mysql-server-has-gone-away-after-60-seconds-of-idle-time?rq=1> <http://stackoverflow.com/q/15170450?rq=1> -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.