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, [email protected] <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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]<javascript:>
> .
> 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 [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.
