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.
Re: [sqlalchemy] python: sqlalchemy - how do I ensure connection not stale using new event system
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, algotr8...@gmail.com 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+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. -- 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
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, algotr8...@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. -- 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.