Re: [sqlalchemy] database operation fails after succsefull multi-processing.
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
[sqlalchemy] database operation fails after succsefull multi-processing.
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.
[sqlalchemy] Re: Filling up a field in a database from a text file, flask
You should ask this in a Flask discussion group or stackoverflow. This is
a sqlalchemy group and most users here have no experience with Flask.
On Friday, August 19, 2022 at 4:13:50 PM UTC-4 nand...@gmail.com wrote:
> I am trying to fill up a field in a table database with contents of a text
> file, but get None as the response when I run the code. Any assistance will
> be appreciated. Here is my code:
>
> # view function - routes.py
> ...
> @app.route('/add_vlans', methods = ['GET', 'POST'])
> @login_required
> def add_vlans():
> form = AddVlanForm(current_user.routername)
> if form.validate_on_submit():
> with open("show_vlans", "r") as vlans:
> vlan_output = vlans.read()
> rt = Router(raw_vlans=vlan_output) #raw_vlans - field variable
> name
> db.session.add(rt)
> db.session.commit()
> return render_template('_show_vlans.html', title='Router Vlans')
>
> #forms.py
> class AddVlanForm(FlaskForm):
> raw_vlans = TextAreaField('Router vlan output:',
> validators=[Length(min=0, max=140)])
> submit = SubmitField('Get Vlans on Router')
>
> #templates - router.html
> {% extends "base.html" %}
>
> {% block content %}
>
> Router: {{ router.routername }}
>
> {% if router.about_router %} About router: {{ router.about_router
> }} {% endif %}
>
> Vlans on {{ router.routername }}
> {% for vlan in vlans %}
>
> {% include '_vlan.html' %}
>
> {% endfor %}
> {% if router == current_user %}
> Edit Router
> {% endif %}
> Vlan Configurations
> Show Router Vlans
>
> {% include '_show_vlans.html' %}
>
> {% endblock %}
>
> #sub-template - _show_vlans.html
>
>
> Vlans on router {{ current_user.routername }}:
> {{ current_user.raw_vlans }}
>
>
>
>
> I get the response:
> Vlans on router router20:None
>
--
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/f5b7ed8b-4c65-4e5d-a274-f8f8460e96fbn%40googlegroups.com.
