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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
