your program runs completely (thanks), and I can't reproduce any problem.
changed line 1 as written to:
# Line 1.
# If nothing happens on line 1, line 3 fails.
insert_integer(0) # If I do this (insert-only) line 3 fails.
#database_action(0) # If I do this (query, insert) line 3 works.
and no issue. output is below. Send along a stack trace for more hints.
2022-08-22 19:16:02,322 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2022-08-22 19:16:02,322 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-22 19:16:02,322 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2022-08-22 19:16:02,322 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-22 19:16:02,323 INFO sqlalchemy.engine.Engine SELECT
@@lower_case_table_names
2022-08-22 19:16:02,323 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-22 19:16:02,323 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,324 INFO sqlalchemy.engine.Engine SELECT COUNT(*) FROM
information_schema.tables WHERE table_schema = %s AND table_name = %s
2022-08-22 19:16:02,324 INFO sqlalchemy.engine.Engine [generated in 0.00016s]
('test', 'mytable')
2022-08-22 19:16:02,325 INFO sqlalchemy.engine.Engine
DROP TABLE mytable
2022-08-22 19:16:02,325 INFO sqlalchemy.engine.Engine [no key 0.00012s] ()
2022-08-22 19:16:02,330 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,330 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,331 INFO sqlalchemy.engine.Engine SELECT COUNT(*) FROM
information_schema.tables WHERE table_schema = %s AND table_name = %s
2022-08-22 19:16:02,331 INFO sqlalchemy.engine.Engine [cached since 0.006941s
ago] ('test', 'mytable')
2022-08-22 19:16:02,332 INFO sqlalchemy.engine.Engine
CREATE TABLE mytable (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
2022-08-22 19:16:02,333 INFO sqlalchemy.engine.Engine [no key 0.00140s] ()
2022-08-22 19:16:02,342 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,344 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,345 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,345 INFO sqlalchemy.engine.Engine [generated in 0.00021s]
(0,)
2022-08-22 19:16:02,346 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,362 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,362 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,365 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,365 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,365 INFO sqlalchemy.engine.Engine [generated in 0.00024s]
(1,)
2022-08-22 19:16:02,365 INFO sqlalchemy.engine.Engine [generated in 0.00024s]
(3,)
2022-08-22 19:16:02,366 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,366 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine [cached since 0.0218s
ago] (3,)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine [cached since 0.02189s
ago] (1,)
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,367 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,368 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,369 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,369 INFO sqlalchemy.engine.Engine [cached since 0.003665s
ago] (4,)
2022-08-22 19:16:02,369 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,369 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine [cached since 0.004593s
ago] (2,)
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,370 INFO sqlalchemy.engine.Engine [cached since 0.0254s
ago] (4,)
2022-08-22 19:16:02,371 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,371 INFO sqlalchemy.engine.Engine [cached since 0.02555s
ago] (2,)
2022-08-22 19:16:02,371 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,371 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,372 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,372 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,372 INFO sqlalchemy.engine.Engine [cached since 0.007373s
ago] (5,)
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine [cached since 0.008211s
ago] (7,)
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,373 INFO sqlalchemy.engine.Engine [cached since 0.02819s
ago] (5,)
2022-08-22 19:16:02,374 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,374 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,374 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,374 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,374 INFO sqlalchemy.engine.Engine [cached since 0.0291s
ago] (7,)
2022-08-22 19:16:02,375 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,375 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,375 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,375 INFO sqlalchemy.engine.Engine [cached since 0.01013s
ago] (6,)
2022-08-22 19:16:02,375 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine [cached since 0.03086s
ago] (6,)
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine [cached since 0.01102s
ago] (8,)
2022-08-22 19:16:02,376 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,377 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,377 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,377 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,377 INFO sqlalchemy.engine.Engine [cached since 0.03192s
ago] (8,)
2022-08-22 19:16:02,377 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,378 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,378 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,378 INFO sqlalchemy.engine.Engine [cached since 0.01299s
ago] (9,)
2022-08-22 19:16:02,378 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,379 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,379 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,379 INFO sqlalchemy.engine.Engine [cached since 0.03381s
ago] (9,)
2022-08-22 19:16:02,379 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,381 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,381 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,381 INFO sqlalchemy.engine.Engine [cached since 0.01605s
ago] (10,)
2022-08-22 19:16:02,381 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,382 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,382 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,382 INFO sqlalchemy.engine.Engine [cached since 0.03694s
ago] (10,)
2022-08-22 19:16:02,383 INFO sqlalchemy.engine.Engine COMMIT
2022-08-22 19:16:02,387 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,388 INFO sqlalchemy.engine.Engine SELECT mytable.id
FROM mytable
WHERE mytable.id = %s
2022-08-22 19:16:02,388 INFO sqlalchemy.engine.Engine [generated in 0.00011s]
(20,)
2022-08-22 19:16:02,389 INFO sqlalchemy.engine.Engine ROLLBACK
2022-08-22 19:16:02,389 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-22 19:16:02,389 INFO sqlalchemy.engine.Engine INSERT INTO mytable (id)
VALUES (%s)
2022-08-22 19:16:02,389 INFO sqlalchemy.engine.Engine [cached since 0.04428s
ago] (20,)
2022-08-22 19:16:02,390 INFO sqlalchemy.engine.Engine COMMIT
On Mon, Aug 22, 2022, at 4:21 PM, Geert Jan Talens wrote:
> Hi,
>
> I am trying to perform some database actions after multiprocessing some
> database manipulations but in most cases I get:
> sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError)
> (2006, 'MySQL server has gone away')
>
> The code below is a working example of the problem I'm having, where the
> success of line 3 (in main) for some reason depends on what happens on line 1.
>
> Any help would be appreciated!
>
> Regards,
> Geert Jan
>
> from sqlalchemy import create_engine, select
> from sqlalchemy import Column, Integer
> from sqlalchemy.orm import declarative_base, sessionmaker
>
> import multiprocessing as mp
> import numpy as np
>
> Base = declarative_base()
> Session = sessionmaker()
>
>
> class MyTable(Base):
> """This table lists all observed frames."""
>
> __tablename__ = 'mytable'
>
> # Columns in the table.
> id = Column(Integer, primary_key=True, autoincrement=False)
>
>
> def insert_integer(integer):
>
> with Session() as session:
>
> record = MyTable(id=int(integer))
> session.add(record)
> session.commit()
>
> return
>
>
> def query_integer(integer):
>
> with Session() as session:
>
> statement = select(MyTable).where(MyTable.id == int(integer))
> result = session.execute(statement).scalar_one_or_none()
>
> return result
>
>
> def database_action(integer):
>
> result = query_integer(integer)
> if result is None:
> insert_integer(integer)
> else:
> print("integer already in database.")
>
> return
>
>
> def initializer(engine):
> # Doesn't matter if I use on or the other.
> engine.dispose(close=False)
> # engine.pool = engine.pool.recreate()
>
>
> def main():
>
> # Database setup.
> engine = create_engine("mysql+mysqldb://user:password@127.0.0.1/testdb")
> Session.configure(bind=engine)
> Base.metadata.drop_all(bind=engine)
> Base.metadata.create_all(bind=engine)
>
> # Line 1.
> # If nothing happens on line 1, line 3 fails.
> # insert_integer(0) # If I do this (insert-only) line 3 fails.
> database_action(0) # If I do this (query, insert) line 3 works.
>
> # Line 2.
> with mp.Pool(2, initializer, (engine,)) as pool:
> pool.map(database_action, np.arange(1, 11))
>
> # Line 3.
> database_action(20) # Fails if line 1 is insert_integer.
>
> return
>
>
> if __name__ == '__main__':
> main()
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/5597b069-3725-44de-88c4-4964d95d84cen%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/5597b069-3725-44de-88c4-4964d95d84cen%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/d4255029-5593-413d-a128-ee36505b46cd%40www.fastmail.com.
