Mixing
SqlAlchemy sessions get stuck when not using them
I'm having a hard time implementing a "MySqlClient" class for my application. My application consists of several modules which have to make use of my database & some of the modules are running on other threads.
My intention is to make an instance for every module that needs to communicate with my MySql database. For example: every client connecting to a websocket server creates his own instance, a telegram bot client has its own instance, ..
I've been searching for days now, I've read the docs, searched the forums .. but somehow I'm missing something or I'm not implementing it the right way.
This is my class:
class MySqlClient():
engine = None
Session = None
def __init__(self):
# create engine
if MySqlClient.engine == None:
MySqlClient.engine = sqlalchemy.create_engine("mysql+mysqlconnector://{0}:{1}@{2}/{3}".format(
state.config["mysql"]["user"],
state.config["mysql"]["password"],
state.config["mysql"]["host"],
state.config["mysql"]["database"]
))
MySqlClient.Session = scoped_session(sessionmaker(bind=MySqlClient.engine))
Base.metadata.create_all(MySqlClient.engine)
self.session = MySqlClient.Session()
def get_budget(self, budget_id):
try:
q = self.session.query(
(Budget.amount).label("budgetAmount"),
func.sum(BudgetRecord.amount).label("total")
).all().filter(Budget.id == budget_id).join(BudgetRecord).filter(extract("month", BudgetRecord.ts) == datetime.datetime.now().month)
self.session.close()
return { "budgetAmount": q[0].budgetAmount, "total": 0.0 if q[0].total == None else q[0].total }
except Exception as ex:
logging.error(ex)
return None
When I start my application everything runs fine, I can execute the method "get_budget" returning the data. However, if after this I wait for 5 minutes, the method won't run again (if I don't wait, it still works). After about 15 minutes after I made the call, the query finally fails saying the MySql connection has dropped:
(mysql.connector.errors.OperationalError) MySQL Connection not available.
I also tried getting a new session before executing new queries. That didn't help either.
I've done things like this before but it's the first time I'm using an ORM & I'd like to keep the benefits of using ORM.
Any help would be greatly appreciated,
Regards
python-3.x sqlalchemy
asked Nov 21 '18 at 8:14
Alexander CornillieAlexander Cornillie
61
add a comment |
I'm having a hard time implementing a "MySqlClient" class for my application. My application consists of several modules which have to make use of my database & some of the modules are running on other threads.
My intention is to make an instance for every module that needs to communicate with my MySql database. For example: every client connecting to a websocket server creates his own instance, a telegram bot client has its own instance, ..
I've been searching for days now, I've read the docs, searched the forums .. but somehow I'm missing something or I'm not implementing it the right way.
This is my class:
class MySqlClient():
engine = None
Session = None
def __init__(self):
# create engine
if MySqlClient.engine == None:
MySqlClient.engine = sqlalchemy.create_engine("mysql+mysqlconnector://{0}:{1}@{2}/{3}".format(
state.config["mysql"]["user"],
state.config["mysql"]["password"],
state.config["mysql"]["host"],
state.config["mysql"]["database"]
))
MySqlClient.Session = scoped_session(sessionmaker(bind=MySqlClient.engine))
Base.metadata.create_all(MySqlClient.engine)
self.session = MySqlClient.Session()
def get_budget(self, budget_id):
try:
q = self.session.query(
(Budget.amount).label("budgetAmount"),
func.sum(BudgetRecord.amount).label("total")
).all().filter(Budget.id == budget_id).join(BudgetRecord).filter(extract("month", BudgetRecord.ts) == datetime.datetime.now().month)
self.session.close()
return { "budgetAmount": q[0].budgetAmount, "total": 0.0 if q[0].total == None else q[0].total }
except Exception as ex:
logging.error(ex)
return None
When I start my application everything runs fine, I can execute the method "get_budget" returning the data. However, if after this I wait for 5 minutes, the method won't run again (if I don't wait, it still works). After about 15 minutes after I made the call, the query finally fails saying the MySql connection has dropped:
(mysql.connector.errors.OperationalError) MySQL Connection not available.
I also tried getting a new session before executing new queries. That didn't help either.
I've done things like this before but it's the first time I'm using an ORM & I'd like to keep the benefits of using ORM.
Any help would be greatly appreciated,
Regards
python-3.x sqlalchemy
asked Nov 21 '18 at 8:14
Alexander CornillieAlexander Cornillie
61
Not relevant to your question but you should consider putting a lock around theif MySqlClient.engine == None:part as there is a race condition there.
– SuperShoot
Nov 21 '18 at 11:29
Does it fail when a single thread running? Or only when multiple threads running?
– SuperShoot
Nov 21 '18 at 11:48
I tried to replicate the issue. Ran 2 threads, each would instantiate aMySqlClientinstance (once only), callget_budget()and sleep for 5 minutes before callingget_budget()again. Both threads successfully returned a result from the query 5 times before I killed it. Why are you so sure that it is the session causing the problem? If you can provide a copy/paste example that reproduces the issue, I'll have another go at it.
– SuperShoot
Nov 21 '18 at 12:14
1
It also fails when I only use 1 client in the application (i commented out the others). Because you asked why I thought this is session related. I started to think it might be network related. I just changed the mysql connection string to a local IP address instead of a dyndns adress and that solved the issue! I thought session objects get recycled when faulty? Also, I tried making new sessions before each query, so this would make it a mysql server problem i guess. Anyhow thanks for your help on this!
– Alexander Cornillie
Nov 22 '18 at 12:13
add a comment |
I'm having a hard time implementing a "MySqlClient" class for my application. My application consists of several modules which have to make use of my database & some of the modules are running on other threads.
My intention is to make an instance for every module that needs to communicate with my MySql database. For example: every client connecting to a websocket server creates his own instance, a telegram bot client has its own instance, ..
I've been searching for days now, I've read the docs, searched the forums .. but somehow I'm missing something or I'm not implementing it the right way.
This is my class:
class MySqlClient():
engine = None
Session = None
def __init__(self):
# create engine
if MySqlClient.engine == None:
MySqlClient.engine = sqlalchemy.create_engine("mysql+mysqlconnector://{0}:{1}@{2}/{3}".format(
state.config["mysql"]["user"],
state.config["mysql"]["password"],
state.config["mysql"]["host"],
state.config["mysql"]["database"]
))
MySqlClient.Session = scoped_session(sessionmaker(bind=MySqlClient.engine))
Base.metadata.create_all(MySqlClient.engine)
self.session = MySqlClient.Session()
def get_budget(self, budget_id):
try:
q = self.session.query(
(Budget.amount).label("budgetAmount"),
func.sum(BudgetRecord.amount).label("total")
).all().filter(Budget.id == budget_id).join(BudgetRecord).filter(extract("month", BudgetRecord.ts) == datetime.datetime.now().month)
self.session.close()
return { "budgetAmount": q[0].budgetAmount, "total": 0.0 if q[0].total == None else q[0].total }
except Exception as ex:
logging.error(ex)
return None
When I start my application everything runs fine, I can execute the method "get_budget" returning the data. However, if after this I wait for 5 minutes, the method won't run again (if I don't wait, it still works). After about 15 minutes after I made the call, the query finally fails saying the MySql connection has dropped:
(mysql.connector.errors.OperationalError) MySQL Connection not available.
I also tried getting a new session before executing new queries. That didn't help either.
I've done things like this before but it's the first time I'm using an ORM & I'd like to keep the benefits of using ORM.
Any help would be greatly appreciated,
Regards
python-3.x sqlalchemy
asked Nov 21 '18 at 8:14
Alexander CornillieAlexander Cornillie
61
I'm having a hard time implementing a "MySqlClient" class for my application. My application consists of several modules which have to make use of my database & some of the modules are running on other threads.
My intention is to make an instance for every module that needs to communicate with my MySql database. For example: every client connecting to a websocket server creates his own instance, a telegram bot client has its own instance, ..
I've been searching for days now, I've read the docs, searched the forums .. but somehow I'm missing something or I'm not implementing it the right way.
This is my class:
class MySqlClient():
engine = None
Session = None
def __init__(self):
# create engine
if MySqlClient.engine == None:
MySqlClient.engine = sqlalchemy.create_engine("mysql+mysqlconnector://{0}:{1}@{2}/{3}".format(
state.config["mysql"]["user"],
state.config["mysql"]["password"],
state.config["mysql"]["host"],
state.config["mysql"]["database"]
))
MySqlClient.Session = scoped_session(sessionmaker(bind=MySqlClient.engine))
Base.metadata.create_all(MySqlClient.engine)
self.session = MySqlClient.Session()
def get_budget(self, budget_id):
try:
q = self.session.query(
(Budget.amount).label("budgetAmount"),
func.sum(BudgetRecord.amount).label("total")
).all().filter(Budget.id == budget_id).join(BudgetRecord).filter(extract("month", BudgetRecord.ts) == datetime.datetime.now().month)
self.session.close()
return { "budgetAmount": q[0].budgetAmount, "total": 0.0 if q[0].total == None else q[0].total }
except Exception as ex:
logging.error(ex)
return None
When I start my application everything runs fine, I can execute the method "get_budget" returning the data. However, if after this I wait for 5 minutes, the method won't run again (if I don't wait, it still works). After about 15 minutes after I made the call, the query finally fails saying the MySql connection has dropped:
(mysql.connector.errors.OperationalError) MySQL Connection not available.
I also tried getting a new session before executing new queries. That didn't help either.
I've done things like this before but it's the first time I'm using an ORM & I'd like to keep the benefits of using ORM.
Any help would be greatly appreciated,
Regards
python-3.x sqlalchemy
python-3.x sqlalchemy
asked Nov 21 '18 at 8:14
Alexander CornillieAlexander Cornillie
61
asked Nov 21 '18 at 8:14
Alexander CornillieAlexander Cornillie
61
asked Nov 21 '18 at 8:14
Alexander CornillieAlexander Cornillie
61
asked Nov 21 '18 at 8:14
Alexander CornillieAlexander Cornillie
61
asked Nov 21 '18 at 8:14
Alexander CornillieAlexander Cornillie
61
61
Not relevant to your question but you should consider putting a lock around theif MySqlClient.engine == None:part as there is a race condition there.
– SuperShoot
Nov 21 '18 at 11:29
Does it fail when a single thread running? Or only when multiple threads running?
– SuperShoot
Nov 21 '18 at 11:48
I tried to replicate the issue. Ran 2 threads, each would instantiate aMySqlClientinstance (once only), callget_budget()and sleep for 5 minutes before callingget_budget()again. Both threads successfully returned a result from the query 5 times before I killed it. Why are you so sure that it is the session causing the problem? If you can provide a copy/paste example that reproduces the issue, I'll have another go at it.
– SuperShoot
Nov 21 '18 at 12:14
1
It also fails when I only use 1 client in the application (i commented out the others). Because you asked why I thought this is session related. I started to think it might be network related. I just changed the mysql connection string to a local IP address instead of a dyndns adress and that solved the issue! I thought session objects get recycled when faulty? Also, I tried making new sessions before each query, so this would make it a mysql server problem i guess. Anyhow thanks for your help on this!
– Alexander Cornillie
Nov 22 '18 at 12:13
add a comment |
Not relevant to your question but you should consider putting a lock around theif MySqlClient.engine == None:part as there is a race condition there.
– SuperShoot
Nov 21 '18 at 11:29
Does it fail when a single thread running? Or only when multiple threads running?
– SuperShoot
Nov 21 '18 at 11:48
I tried to replicate the issue. Ran 2 threads, each would instantiate aMySqlClientinstance (once only), callget_budget()and sleep for 5 minutes before callingget_budget()again. Both threads successfully returned a result from the query 5 times before I killed it. Why are you so sure that it is the session causing the problem? If you can provide a copy/paste example that reproduces the issue, I'll have another go at it.
– SuperShoot
Nov 21 '18 at 12:14
1
It also fails when I only use 1 client in the application (i commented out the others). Because you asked why I thought this is session related. I started to think it might be network related. I just changed the mysql connection string to a local IP address instead of a dyndns adress and that solved the issue! I thought session objects get recycled when faulty? Also, I tried making new sessions before each query, so this would make it a mysql server problem i guess. Anyhow thanks for your help on this!
– Alexander Cornillie
Nov 22 '18 at 12:13
Not relevant to your question but you should consider putting a lock around the
if MySqlClient.engine == None: part as there is a race condition there.– SuperShoot
Nov 21 '18 at 11:29
Not relevant to your question but you should consider putting a lock around the
if MySqlClient.engine == None: part as there is a race condition there.– SuperShoot
Nov 21 '18 at 11:29
Does it fail when a single thread running? Or only when multiple threads running?
– SuperShoot
Nov 21 '18 at 11:48
Does it fail when a single thread running? Or only when multiple threads running?
– SuperShoot
Nov 21 '18 at 11:48
I tried to replicate the issue. Ran 2 threads, each would instantiate a
MySqlClient instance (once only), call get_budget() and sleep for 5 minutes before calling get_budget() again. Both threads successfully returned a result from the query 5 times before I killed it. Why are you so sure that it is the session causing the problem? If you can provide a copy/paste example that reproduces the issue, I'll have another go at it.– SuperShoot
Nov 21 '18 at 12:14
I tried to replicate the issue. Ran 2 threads, each would instantiate a
MySqlClient instance (once only), call get_budget() and sleep for 5 minutes before calling get_budget() again. Both threads successfully returned a result from the query 5 times before I killed it. Why are you so sure that it is the session causing the problem? If you can provide a copy/paste example that reproduces the issue, I'll have another go at it.– SuperShoot
Nov 21 '18 at 12:14
1
1
It also fails when I only use 1 client in the application (i commented out the others). Because you asked why I thought this is session related. I started to think it might be network related. I just changed the mysql connection string to a local IP address instead of a dyndns adress and that solved the issue! I thought session objects get recycled when faulty? Also, I tried making new sessions before each query, so this would make it a mysql server problem i guess. Anyhow thanks for your help on this!
– Alexander Cornillie
Nov 22 '18 at 12:13
It also fails when I only use 1 client in the application (i commented out the others). Because you asked why I thought this is session related. I started to think it might be network related. I just changed the mysql connection string to a local IP address instead of a dyndns adress and that solved the issue! I thought session objects get recycled when faulty? Also, I tried making new sessions before each query, so this would make it a mysql server problem i guess. Anyhow thanks for your help on this!
– Alexander Cornillie
Nov 22 '18 at 12:13
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53407729%2fsqlalchemy-sessions-get-stuck-when-not-using-them%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53407729%2fsqlalchemy-sessions-get-stuck-when-not-using-them%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Not relevant to your question but you should consider putting a lock around the
if MySqlClient.engine == None:part as there is a race condition there.– SuperShoot
Nov 21 '18 at 11:29
Does it fail when a single thread running? Or only when multiple threads running?
– SuperShoot
Nov 21 '18 at 11:48
I tried to replicate the issue. Ran 2 threads, each would instantiate a
MySqlClientinstance (once only), callget_budget()and sleep for 5 minutes before callingget_budget()again. Both threads successfully returned a result from the query 5 times before I killed it. Why are you so sure that it is the session causing the problem? If you can provide a copy/paste example that reproduces the issue, I'll have another go at it.– SuperShoot
Nov 21 '18 at 12:14
1
It also fails when I only use 1 client in the application (i commented out the others). Because you asked why I thought this is session related. I started to think it might be network related. I just changed the mysql connection string to a local IP address instead of a dyndns adress and that solved the issue! I thought session objects get recycled when faulty? Also, I tried making new sessions before each query, so this would make it a mysql server problem i guess. Anyhow thanks for your help on this!
– Alexander Cornillie
Nov 22 '18 at 12:13