[sqlalchemy] mysql and python datetime
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 '-MM-DD HH:MM:SS' format. The
supported
range is '1000-01-01 00:00:00' to '-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
mysqlhttp://stackoverflow.com/questions/tagged/mysql
insert http://stackoverflow.com/questions/tagged/insert
sqlalchemyhttp://stackoverflow.com/questions/tagged/sqlalchemy
share
http://stackoverflow.com/q/15821953/668624|edithttp://stackoverflow.com/posts/15821953/edit
|close|deletehttp://stackoverflow.com/questions/15821953/mysql-and-python-datetime#
|flaghttp://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
linkhttp://stackoverflow.com/q/15821953/668624to this question via
email?subject=Stack%20Overflow%20Questionbody=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,
Twitterhttp://twitter.com/share?url=http%3a%2f%2fstackoverflow.com%2fq%2f15821953%2f668624%3fstw%3d2text=mysql%20and%20python%20datetime,
or
Facebookhttp://www.facebook.com/sharer.php?u=http%3a%2f%2fstackoverflow.com%2fq%2f15821953%2f668624%3fsfb%3d2t=mysql%20and%20python%20datetime.
Would you like to have responses to your questions sent to you via
emailhttp://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
Re: [sqlalchemy] python: sqlalchemy - how do I ensure connection not stale using new event system
I think you nailed it. This is the problem. I will follow the approach you
indicated. Thank you Michael. I appreciate it.
On Tuesday, April 2, 2013 1:35:07 PM UTC-7, Michael Bayer wrote:
if you're doing a query that is causing the database connection to simply
fail, then you'd need to address that issue primarily. you'd probably
want to perform multiple insert statements, chunking about 5-10K records at
at time.
On Apr 2, 2013, at 1:26 PM, algot...@gmail.com javascript: wrote:
To clarify my environment. I have a VM (Linux Redhat) which has MySQL
server running on it. My script runs locally on the same machine. It is a
simple script that is doing a insert many after processing/parsing a csv
file. I don't have any web apps or anything of that nature.
On Monday, April 1, 2013 7:04:48 PM UTC-7, algot...@gmail.com wrote:
I am using the sqlalchemy package in python. I have an operation that
takes some time to execute after I perform an autoload on an existing
table. This causes the following error when I attempt to use the connection:
sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has
gone away')
I have a simple utility function that performs an insert many:
def insert_data(data_2_insert, table_name):
engine = create_engine('mysql://blah:blah123@localhost/dbname')
# Metadata is a Table catalog.
metadata = MetaData()
table = Table(table_name, metadata, autoload=True, autoload_with=engine)
for c in mytable.c:
print c
column_names = tuple(c.name for c in mytable.c)
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
ins = mytable.insert()
conn = engine.connect()
conn.execute(ins, final_data)
conn.close()
It is the following line that times long time to execute since
'data_2_insert' has 677,161 rows.
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
I came across the following post below which refers to a similar problem,
however I am not sure how to implement the connection management suggested
as I am quite a newbie.
http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy
Note for SQLAlchemy 0.7 - PoolListener is deprecated, but The same solution
can be implemented using the new event system. – robots.jpg
Here is the link to the new event system described by one of the users:
http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
If someone can please show me a couple of pointers on how I could go
about integrating the suggestions into the way I use sqlalchemy I would be
very appreciative. Thank you.
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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.
[sqlalchemy] Re: python: sqlalchemy - how do I ensure connection not stale using new event system
Below is the traceback. When it attempts to perform the conn.execute(),
which is the db insert is where it seems to realize the connection is
stale.
Traceback (most recent call last):
File myscript.py, line 126, in module
main()
File myscript.py, line 33, in main
insert_data(final_data, table_name)
File myscript.py, line 122, 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) (2006, 'MySQL server
has gone away') u'INSERT INTO...
On Monday, April 1, 2013 7:04:48 PM UTC-7, algot...@gmail.com wrote:
I am using the sqlalchemy package in python. I have an operation that
takes some time to execute after I perform an autoload on an existing
table. This causes the following error when I attempt to use the connection:
sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has
gone away')
I have a simple utility function that performs an insert many:
def insert_data(data_2_insert, table_name):
engine = create_engine('mysql://blah:blah123@localhost/dbname')
# Metadata is a Table catalog.
metadata = MetaData()
table = Table(table_name, metadata, autoload=True, autoload_with=engine)
for c in mytable.c:
print c
column_names = tuple(c.name for c in mytable.c)
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
ins = mytable.insert()
conn = engine.connect()
conn.execute(ins, final_data)
conn.close()
It is the following line that times long time to execute since
'data_2_insert' has 677,161 rows.
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
I came across the following post below which refers to a similar problem,
however I am not sure how to implement the connection management suggested
as I am quite a newbie.
http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy
Note for SQLAlchemy 0.7 - PoolListener is deprecated, but The same solution
can be implemented using the new event system. – robots.jpg
Here is the link to the new event system described by one of the users:
http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
If someone can please show me a couple of pointers on how I could go about
integrating the suggestions into the way I use sqlalchemy I would be very
appreciative. Thank you.
--
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.
[sqlalchemy] Re: python: sqlalchemy - how do I ensure connection not stale using new event system
I tried to include pool_recycle = 10 (seconds) in my create_engine call but
that doesn't fix the problem. I still get the same error. Hmm
On Monday, April 1, 2013 7:04:48 PM UTC-7, algot...@gmail.com wrote:
I am using the sqlalchemy package in python. I have an operation that
takes some time to execute after I perform an autoload on an existing
table. This causes the following error when I attempt to use the connection:
sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has
gone away')
I have a simple utility function that performs an insert many:
def insert_data(data_2_insert, table_name):
engine = create_engine('mysql://blah:blah123@localhost/dbname')
# Metadata is a Table catalog.
metadata = MetaData()
table = Table(table_name, metadata, autoload=True, autoload_with=engine)
for c in mytable.c:
print c
column_names = tuple(c.name for c in mytable.c)
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
ins = mytable.insert()
conn = engine.connect()
conn.execute(ins, final_data)
conn.close()
It is the following line that times long time to execute since
'data_2_insert' has 677,161 rows.
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
I came across the following post below which refers to a similar problem,
however I am not sure how to implement the connection management suggested
as I am quite a newbie.
http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy
Note for SQLAlchemy 0.7 - PoolListener is deprecated, but The same solution
can be implemented using the new event system. – robots.jpg
Here is the link to the new event system described by one of the users:
http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
If someone can please show me a couple of pointers on how I could go about
integrating the suggestions into the way I use sqlalchemy I would be very
appreciative. Thank you.
--
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.
[sqlalchemy] Re: python: sqlalchemy - how do I ensure connection not stale using new event system
To clarify my environment. I have a VM (Linux Redhat) which has MySQL
server running on it. My script runs locally on the same machine. It is a
simple script that is doing a insert many after processing/parsing a csv
file. I don't have any web apps or anything of that nature.
On Monday, April 1, 2013 7:04:48 PM UTC-7, algot...@gmail.com wrote:
I am using the sqlalchemy package in python. I have an operation that
takes some time to execute after I perform an autoload on an existing
table. This causes the following error when I attempt to use the connection:
sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has
gone away')
I have a simple utility function that performs an insert many:
def insert_data(data_2_insert, table_name):
engine = create_engine('mysql://blah:blah123@localhost/dbname')
# Metadata is a Table catalog.
metadata = MetaData()
table = Table(table_name, metadata, autoload=True, autoload_with=engine)
for c in mytable.c:
print c
column_names = tuple(c.name for c in mytable.c)
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
ins = mytable.insert()
conn = engine.connect()
conn.execute(ins, final_data)
conn.close()
It is the following line that times long time to execute since
'data_2_insert' has 677,161 rows.
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
I came across the following post below which refers to a similar problem,
however I am not sure how to implement the connection management suggested
as I am quite a newbie.
http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy
Note for SQLAlchemy 0.7 - PoolListener is deprecated, but The same solution
can be implemented using the new event system. – robots.jpg
Here is the link to the new event system described by one of the users:
http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
If someone can please show me a couple of pointers on how I could go about
integrating the suggestions into the way I use sqlalchemy I would be very
appreciative. Thank you.
--
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.
[sqlalchemy] python: sqlalchemy - how do I ensure connection not stale using new event system
I am using the sqlalchemy package in python. I have an operation that takes
some time to execute after I perform an autoload on an existing table. This
causes the following error when I attempt to use the connection:
sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has
gone away')
I have a simple utility function that performs an insert many:
def insert_data(data_2_insert, table_name):
engine = create_engine('mysql://blah:blah123@localhost/dbname')
# Metadata is a Table catalog.
metadata = MetaData()
table = Table(table_name, metadata, autoload=True, autoload_with=engine)
for c in mytable.c:
print c
column_names = tuple(c.name for c in mytable.c)
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
ins = mytable.insert()
conn = engine.connect()
conn.execute(ins, final_data)
conn.close()
It is the following line that times long time to execute since
'data_2_insert' has 677,161 rows.
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
I came across the following post below which refers to a similar problem,
however I am not sure how to implement the connection management suggested
as I am quite a newbie.
http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy
Note for SQLAlchemy 0.7 - PoolListener is deprecated, but The same solution can
be implemented using the new event system. – robots.jpg
Here is the link to the new event system described by one of the users:
http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
If someone can please show me a couple of pointers on how I could go about
integrating the suggestions into the way I use sqlalchemy I would be very
appreciative. Thank you.
--
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.
Re: [sqlalchemy] python: sqlalchemy - how do I ensure connection not stale using new event system
Thank you Michael for your response.
So its not a long query that I think is the problem here because I don't
believe the execution gets that far. My suspicion is that it is the line
that computes the dictionary key/value pairs, which takes a long time since
it has to build 677,161 x 10 (columns) = 6.7 million key/value pairs. I
can't vpn into my machine right now for some reason so I will have to wait
until tomorrow to get the traceback but my hunch is the connection becomes
stale after the call to table = Table() because there is not activity while
the dictionary is being created.
Does this make sense?
engine = create_engine('mysql://blah:blah123@localhost/dbname')
# Metadata is a Table catalog.
metadata = MetaData()
table = Table(table_name, metadata, autoload=True, autoload_with=engine)
column_names = tuple(c.name for c in mytable.c)
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
# This is the line that takes time to complete
ins = mytable.insert()
conn = engine.connect()
conn.execute(ins, final_data)
conn.close()
On Monday, April 1, 2013 9:09:34 PM UTC-7, Michael Bayer wrote:
what is the actual reason that MySQL is disconnecting, is it actually
being restarted while the operation proceeds ? running a long query
shouldn't cause the connection to die off unless something goes wrong with
the operation.
On Apr 1, 2013, at 10:04 PM, algot...@gmail.com javascript: wrote:
I am using the sqlalchemy package in python. I have an operation that
takes some time to execute after I perform an autoload on an existing
table. This causes the following error when I attempt to use the connection:
sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has
gone away')
I have a simple utility function that performs an insert many:
def insert_data(data_2_insert, table_name):
engine = create_engine('mysql://blah:blah123@localhost/dbname')
# Metadata is a Table catalog.
metadata = MetaData()
table = Table(table_name, metadata, autoload=True, autoload_with=engine)
column_names = tuple(c.name for c in mytable.c)
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
ins = mytable.insert()
conn = engine.connect()
conn.execute(ins, final_data)
conn.close()
It is the following line that times long time to execute since
'data_2_insert' has 677,161 rows.
final_data = [dict(zip(column_names, x)) for x in data_2_insert]
I came across the following post below which refers to a similar problem,
however I am not sure how to implement the connection management suggested
as I am quite a newbie.
http://stackoverflow.com/questions/3033234/handle-mysql-restart-in-sqlalchemy
Note for SQLAlchemy 0.7 - PoolListener is deprecated, but The same solution
can be implemented using the new event system. – robots.jpg
Here is the link to the new event system described by one of the users:
http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
If someone can please show me a couple of pointers on how I could go about
integrating the suggestions into the way I use sqlalchemy I would be very
appreciative. Thank you.
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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.
