[sqlalchemy] Bug report with JSON nulls in 1.1.x

2017-07-17 Thread vineet 
Hello, I'd like to report a bug report regarding JSON nulls and 
underscore-prefixed attributes as we're upgrading from 1.0.17 to 1.1.11.

I read through the behavior at 
http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#change-3514 
and 
http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#behavior-change-3514,
 
all of which makes sense to me.

However, this does not seem to work for attributes which are prefixed with 
an underscore. Example:

class A(db.Model):
__tablename__ = 'a'


id = db.Column(db.Integer, primary_key=True)
column1 = db.Column('column1', JSON, nullable=False)
_column2 = db.Column('column2', JSON, nullable=False)

# Succeeds
db.session.add(A(column1=[], _column2=[]))
db.session.flush()

# Succeeds
db.session.add(A(column1=None, _column2=[]))
db.session.flush()

# Fails with integrity error
db.session.add(A(column1=[], _column2=None))
db.session.flush()


(psycopg2.IntegrityError) null value in column "column2" violates not-null 
constraint
DETAIL:  Failing row contains (5, [], null).
 [SQL: '-- manage.py:1242 in \nINSERT INTO a (column1) VALUES 
(%(column1)s) RETURNING a.id'] [parameters: {'column1': '[]'}]

Using db.Column('column2', JSON, nullable=False, default=JSON.NULL) ends up 
sending a JSON NULL, so basically it looks like it is ignoring explicitly 
set None values for attributes that start with an underscore.

This is not workflow blocking for us (I will just change our usages to also 
use default=JSON.NULL), but wanted to file a bug report in case it's 
something others run into as well!

-- 
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] The right way to use gevent with sqlalchemy

2017-07-17 Thread Tony Wang 
Hi Mike

Great thanks, error seems gone.

On Sun, Jul 16, 2017 at 11:37 PM Mike Bayer 
wrote:

> it means like this (here, I adapt your SqlHelper into a recipe that is
> basically equivalent to the context manager at
>
> http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it
> )
>
>
> engine = create_engine(...)
> sessionmaker = sessionmaker(engine)
>
> class SqlHelper(object):
> def __init__(self):
> self.session = sessionmaker()
>
> def __enter__(self):
> return self
>
> def __exit__(self ,type, value, traceback):
> try:
> if type:
>self.session.rollback()
> else:
>self.session.commit()
> finally:
> self.session.close()
>
> def insert(self, object):
> self.session.add(object)
>
> def delete(self, object):
> self.session.delete(object)
>
> # ...
>
>
> def run_in_gevent():
> with SqlHelper() as helper:
> for item in things_to_do():
> helper.insert(...)
> helper.delete(...)
> # ...
>
> if __name__ = '__main__':
> for i in range(num_workers):
> spawn(run_in_gevent)
>
># .. etc
>
> Following the guidelines at
>
> http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it
> :
>
> "keep the lifecycle of the session separate and external from
> functions and objects that access and/or manipulate database data. "
> - we don't commit() the session in the same place that we are doing
> individual insert(), delete(), select() statements - we should have a
> single transaction surrounding a group of operations.
>
> "Make sure you have a clear notion of where transactions begin and
> end" - the SqlHelper() is used as a context manager, and that's when
> the transaction starts.  outside the "with:" block, the transaction is
> done.
>
> On Sat, Jul 15, 2017 at 9:16 PM, Tony Wang 
> wrote:
> > Hi Mike
> >
> > Thanks very much for your detailed reply. Very kind of you.
> >
> > For "threadlocal" strategy, I used without it for a long time but always
> > coming with "Runtime Error" that posted in BitBucket. I found some guys
> in
> > Gevent Group solved it by "threadlocal". I tried but error kept there.
> >
> > Little confusing about the second suggestion.  You mean put "sqlhelper =
> > SqlHelper()" outside of insert_data function, and let gevent workers
> share
> > the same engine and session? It doesn't work. If not,
> > could you share me some demo code?
> >
> > Thanks!
> >
> > On Sat, Jul 15, 2017 at 11:51 PM Mike Bayer 
> > wrote:
> >>
> >> On Fri, Jul 14, 2017 at 8:08 PM, Tony Wang 
> >> wrote:
> >> > I simplify a complex system of gevent with sqlachemy to a simple demo
> >> > code.
> >> >
> >> >
> >> > Before using of sqlachemy, pymysql is my best choice for communication
> >> > between MySql and gevent because of its easy and direct usage. But
> now I
> >> > feel a little confusing, what's the best way to make the two (gevent
> and
> >> > sqlachemy) work efficiently.
> >> >
> >> >
> >> > In pymysql case, though some "_io.BufferedReader " errors would
> happen,
> >> > it
> >> > doesn't affect the INSERT or UPDATE operations.
> >> >
> >> >
> >> > In sqlalchemy case, it's quite different in the opposite. Much more
> >> > errors
> >> > and little success.
> >> >
> >> >
> >> > After searching around for such errors, some
> >> >
> >> > solutions[
> https://groups.google.com/forum/#!searchin/gevent/SQLAlchemy/gevent/eGLfR7JV0kk/hg1kicBJeQkJ
> ]
> >> > for similar error didn't work.
> >> >
> >> >
> >> > Demo code :
> >> > https://gist.github.com/tonywangcn/6dadbd58d7778063b0c7969f62505537
> >>
> >> so first, do not use the "threadlocal" strategy.  It is legacy and
> >> should never be used:
> >>
> >>
> http://docs.sqlalchemy.org/en/latest/core/connections.html#using-the-threadlocal-execution-strategy
> >> . With gevent, I'd expect it to be disastrous:
> >>
> >> self.engine = create_engine(
> >> 'mysql+pymysql://root:password@localhost/test?charset=utf8',
> >> echo=True, pool_size=50, max_overflow=100, pool_recycle=3600,
> >> strategy='threadlocal')
> >>
> >> so remove that.
> >>
> >> Next, you are manipulating the private state of the QueuePool after
> >> its been constructed:
> >>
> >> self.engine.pool._use_threadlocal = True
> >>
> >> Never alter the value of an underscore variable in any library, the
> >> underscore means "private".   The effect of setting this flag after
> >> the fact is that the QueuePool will be in an essentially broken state
> >> since the constructor needs to know about this flag; in this specific
> >> case, the "threadlocal" engine strategy means the flag was already set
> >> in any case so it isn't having any effect, but this code should be
> >>

Re: [sqlalchemy] Re: Python 3.5 mysql-connector==2.1.6 SQLAlchemy==1.1.11 queue Empty

2017-07-17 Thread josip povreslo 
Yep, that's where I've stopped with my research for now. Mysqlclient 
handles relatively ok for now although I think as well it's a problem with 
network there, wouldn't be the first time. Eithter way, thanks for your 
support, I'll get back with full details if/when I completely understand 
what's happening and how to configure the whole thing. 

Best,
Josip

On Monday, July 17, 2017 at 3:30:16 PM UTC+2, Mike Bayer wrote:
>
> On Mon, Jul 17, 2017 at 4:23 AM, josip povreslo 
>  wrote: 
> > To add more information to it: 
> > 
> > We are running our dB on AWS RDS, this is what MySQL say in logs when we 
> > switched to mysql-connector: 
>
> Oh.  That is an *extremely* different situation.Amazon RDS is a 
> MySQL variant that runs in an entirely Amazon-specific environment. 
> They are likely performing some kind of simple rate limiting on your 
> service, or otherwise having their own kinds of outages.It is not 
> normal that they should have basic problems like this with off the 
> shelf MySQL client libraries, and issues like "can't connect" are not 
> normal in the MySQL world unless there are network issues.  I don't 
> have experience with RDS so you'd need to check with their support. 
>
>
>
> > 
> > [Note] Aborted connection 9191 to db: 'connstr' (Got an error reading 
> > communication packets) 
> > 
> > 
> > Best Regards, 
> > Josip 
> > 
> > 
> > On Monday, July 17, 2017 at 9:47:50 AM UTC+2, josip povreslo wrote: 
> >> 
> >> Mike, 
> >> 
> >> Just as an info, mysql-connector is a total NO for us, when I switched 
> to 
> >> mysql-connector we saw bigger problems and those happens much more 
> often. 
> >> PyMySQL is the last thing to switch to and try. For this processing 
> piece 
> >> I'm thinking to replace SQLAlchemy with a direct database driver usage, 
> do 
> >> you think that's the catch or this is a more general issue with the 
> drivers 
> >> itself? 
> >> 
> >> Best, 
> >> Josip 
> >> 
> >> On Friday, July 14, 2017 at 11:57:12 PM UTC+2, Mike Bayer wrote: 
> >>> 
> >>> that "mysqldb" URL is not mysql-connector - it's MySQL-Python (old and 
> >>> unmaintained) or mysqlclient (much better, if you have it installed 
> >>> and not conflicting w/ MySQL-Python).   You'd need to specify 
> >>> "mysql+mysqlconnector://" to use that DBAPI, but also I'd recommend 
> >>> trying "mysql+pymysql://" to see if you get better results. 
> >>> 
> >>> as far as "can't connect to server" you should make sure you are not 
> >>> surpassing your max_connections limit, though it should be emitting 
> >>> different error message for that. 
> >>> 
> >>> The Queue.Empty error is not the "error", it has to do with how the 
> >>> QueuePool works internally to test that the pool is empty, and Python 
> >>> 3 can't help but report every exception in the chain.   The error is 
> >>> the can't connect to server. 
> >>> 
> >>> 
> >>> 
> >>> On Fri, Jul 14, 2017 at 5:41 PM, josip povreslo 
> >>>  wrote: 
> >>> > In addition to the previous email, what I do is the following: 
> >>> > 
> >>> > When request comes in: 
> >>> > self.mysql_engine = 
> >>> > 
> >>> > 
> create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(self.db_params['dbuser'], 
>
> >>> > self.db_params['dbpass'], self.db_params['db_hosts'][db_host_index], 
> >>> > self.db_params['dbname']), pool_recycle=3600, 
> >>> > connect_args={'connect_timeout': 2}) 
> >>> > 
> >>> > And then when need to interact with the dB: 
> >>> > 
> >>> > connection = self.mysql_engine.connect() 
> >>> > connection.execute("raw query") 
> >>> > connection.close() 
> >>> > 
> >>> > 
> >>> > Best Regards, 
> >>> > Josip 
> >>> > 
> >>> > On Friday, July 14, 2017 at 11:36:26 PM UTC+2, josip povreslo wrote: 
> >>> >> 
> >>> >> Hello, 
> >>> >> 
> >>> >> As mentioned in the subject, we have the following packages 
> installed: 
> >>> >> 
> >>> >> Python 3.5 
> >>> >> mysql-connector==2.1.6 
> >>> >> SQLAlchemy==1.1.11 
> >>> >> 
> >>> >> Our service is working until we get a bit more traffic (40 - 50 
> >>> >> req/min), 
> >>> >> although I'm still not 100% if it's a pattern, however, then we get 
> >>> >> this 
> >>> >> error: 
> >>> >> 
> >>> >> Traceback (most recent call last): 
> >>> >>   File 
> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
> >>> >> line 
> >>> >> 1122, in _do_get 
> >>> >> return self._pool.get(wait, self._timeout) 
> >>> >>   File 
> >>> >> 
> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/queue.py", 
> >>> >> line 
> >>> >> 145, in get 
> >>> >> raise Empty 
> >>> >> sqlalchemy.util.queue.Empty 
> >>> >> During handling of the above exception, another exception occurred: 
> >>> >> Traceback (most recent call last): 
> >>> >>   File 
> >>> >> 
> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/base.py", 
> >>> >> line 
> >>> >> 2147, in _wrap_pool_connect 
> >>> >> return fn() 
> >>> >>   File 
> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py",

Re: [sqlalchemy] Re: Python 3.5 mysql-connector==2.1.6 SQLAlchemy==1.1.11 queue Empty

2017-07-17 Thread Mike Bayer 
On Mon, Jul 17, 2017 at 4:23 AM, josip povreslo
 wrote:
> To add more information to it:
>
> We are running our dB on AWS RDS, this is what MySQL say in logs when we
> switched to mysql-connector:

Oh.  That is an *extremely* different situation.Amazon RDS is a
MySQL variant that runs in an entirely Amazon-specific environment.
They are likely performing some kind of simple rate limiting on your
service, or otherwise having their own kinds of outages.It is not
normal that they should have basic problems like this with off the
shelf MySQL client libraries, and issues like "can't connect" are not
normal in the MySQL world unless there are network issues.  I don't
have experience with RDS so you'd need to check with their support.



>
> [Note] Aborted connection 9191 to db: 'connstr' (Got an error reading
> communication packets)
>
>
> Best Regards,
> Josip
>
>
> On Monday, July 17, 2017 at 9:47:50 AM UTC+2, josip povreslo wrote:
>>
>> Mike,
>>
>> Just as an info, mysql-connector is a total NO for us, when I switched to
>> mysql-connector we saw bigger problems and those happens much more often.
>> PyMySQL is the last thing to switch to and try. For this processing piece
>> I'm thinking to replace SQLAlchemy with a direct database driver usage, do
>> you think that's the catch or this is a more general issue with the drivers
>> itself?
>>
>> Best,
>> Josip
>>
>> On Friday, July 14, 2017 at 11:57:12 PM UTC+2, Mike Bayer wrote:
>>>
>>> that "mysqldb" URL is not mysql-connector - it's MySQL-Python (old and
>>> unmaintained) or mysqlclient (much better, if you have it installed
>>> and not conflicting w/ MySQL-Python).   You'd need to specify
>>> "mysql+mysqlconnector://" to use that DBAPI, but also I'd recommend
>>> trying "mysql+pymysql://" to see if you get better results.
>>>
>>> as far as "can't connect to server" you should make sure you are not
>>> surpassing your max_connections limit, though it should be emitting
>>> different error message for that.
>>>
>>> The Queue.Empty error is not the "error", it has to do with how the
>>> QueuePool works internally to test that the pool is empty, and Python
>>> 3 can't help but report every exception in the chain.   The error is
>>> the can't connect to server.
>>>
>>>
>>>
>>> On Fri, Jul 14, 2017 at 5:41 PM, josip povreslo
>>>  wrote:
>>> > In addition to the previous email, what I do is the following:
>>> >
>>> > When request comes in:
>>> > self.mysql_engine =
>>> >
>>> > create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(self.db_params['dbuser'],
>>> > self.db_params['dbpass'], self.db_params['db_hosts'][db_host_index],
>>> > self.db_params['dbname']), pool_recycle=3600,
>>> > connect_args={'connect_timeout': 2})
>>> >
>>> > And then when need to interact with the dB:
>>> >
>>> > connection = self.mysql_engine.connect()
>>> > connection.execute("raw query")
>>> > connection.close()
>>> >
>>> >
>>> > Best Regards,
>>> > Josip
>>> >
>>> > On Friday, July 14, 2017 at 11:36:26 PM UTC+2, josip povreslo wrote:
>>> >>
>>> >> Hello,
>>> >>
>>> >> As mentioned in the subject, we have the following packages installed:
>>> >>
>>> >> Python 3.5
>>> >> mysql-connector==2.1.6
>>> >> SQLAlchemy==1.1.11
>>> >>
>>> >> Our service is working until we get a bit more traffic (40 - 50
>>> >> req/min),
>>> >> although I'm still not 100% if it's a pattern, however, then we get
>>> >> this
>>> >> error:
>>> >>
>>> >> Traceback (most recent call last):
>>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py",
>>> >> line
>>> >> 1122, in _do_get
>>> >> return self._pool.get(wait, self._timeout)
>>> >>   File
>>> >> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/queue.py",
>>> >> line
>>> >> 145, in get
>>> >> raise Empty
>>> >> sqlalchemy.util.queue.Empty
>>> >> During handling of the above exception, another exception occurred:
>>> >> Traceback (most recent call last):
>>> >>   File
>>> >> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/base.py",
>>> >> line
>>> >> 2147, in _wrap_pool_connect
>>> >> return fn()
>>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py",
>>> >> line
>>> >> 328, in unique_connection
>>> >> return _ConnectionFairy._checkout(self)
>>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py",
>>> >> line
>>> >> 766, in _checkout
>>> >> fairy = _ConnectionRecord.checkout(pool)
>>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py",
>>> >> line
>>> >> 516, in checkout
>>> >> rec = pool._do_get()
>>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py",
>>> >> line
>>> >> 1138, in _do_get
>>> >> self._dec_overflow()
>>> >>   File
>>> >>
>>> >> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/langhelpers.py",
>>> >> line 66, in __exit__
>>> >> compat.reraise(exc_type, exc_value, exc_tb)
>>> >>   File
>>> >>

Re: [sqlalchemy] Re: Python 3.5 mysql-connector==2.1.6 SQLAlchemy==1.1.11 queue Empty

2017-07-17 Thread josip povreslo 
To add more information to it:

We are running our dB on AWS RDS, this is what MySQL say in logs when we 
switched to mysql-connector:

[Note] Aborted connection 9191 to db: 'connstr' (Got an error reading 
communication packets)


Best Regards,
Josip

On Monday, July 17, 2017 at 9:47:50 AM UTC+2, josip povreslo wrote:
>
> Mike,
>
> Just as an info, mysql-connector is a total NO for us, when I switched to 
> mysql-connector we saw bigger problems and those happens much more often. 
> PyMySQL is the last thing to switch to and try. For this processing piece 
> I'm thinking to replace SQLAlchemy with a direct database driver usage, do 
> you think that's the catch or this is a more general issue with the drivers 
> itself?
>
> Best,
> Josip
>
> On Friday, July 14, 2017 at 11:57:12 PM UTC+2, Mike Bayer wrote:
>>
>> that "mysqldb" URL is not mysql-connector - it's MySQL-Python (old and 
>> unmaintained) or mysqlclient (much better, if you have it installed 
>> and not conflicting w/ MySQL-Python).   You'd need to specify 
>> "mysql+mysqlconnector://" to use that DBAPI, but also I'd recommend 
>> trying "mysql+pymysql://" to see if you get better results. 
>>
>> as far as "can't connect to server" you should make sure you are not 
>> surpassing your max_connections limit, though it should be emitting 
>> different error message for that. 
>>
>> The Queue.Empty error is not the "error", it has to do with how the 
>> QueuePool works internally to test that the pool is empty, and Python 
>> 3 can't help but report every exception in the chain.   The error is 
>> the can't connect to server. 
>>
>>
>>
>> On Fri, Jul 14, 2017 at 5:41 PM, josip povreslo 
>>  wrote: 
>> > In addition to the previous email, what I do is the following: 
>> > 
>> > When request comes in: 
>> > self.mysql_engine = 
>> > 
>> create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(self.db_params['dbuser'], 
>>
>> > self.db_params['dbpass'], self.db_params['db_hosts'][db_host_index], 
>> > self.db_params['dbname']), pool_recycle=3600, 
>> > connect_args={'connect_timeout': 2}) 
>> > 
>> > And then when need to interact with the dB: 
>> > 
>> > connection = self.mysql_engine.connect() 
>> > connection.execute("raw query") 
>> > connection.close() 
>> > 
>> > 
>> > Best Regards, 
>> > Josip 
>> > 
>> > On Friday, July 14, 2017 at 11:36:26 PM UTC+2, josip povreslo wrote: 
>> >> 
>> >> Hello, 
>> >> 
>> >> As mentioned in the subject, we have the following packages installed: 
>> >> 
>> >> Python 3.5 
>> >> mysql-connector==2.1.6 
>> >> SQLAlchemy==1.1.11 
>> >> 
>> >> Our service is working until we get a bit more traffic (40 - 50 
>> req/min), 
>> >> although I'm still not 100% if it's a pattern, however, then we get 
>> this 
>> >> error: 
>> >> 
>> >> Traceback (most recent call last): 
>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
>> line 
>> >> 1122, in _do_get 
>> >> return self._pool.get(wait, self._timeout) 
>> >>   File 
>> >> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/queue.py", 
>> line 
>> >> 145, in get 
>> >> raise Empty 
>> >> sqlalchemy.util.queue.Empty 
>> >> During handling of the above exception, another exception occurred: 
>> >> Traceback (most recent call last): 
>> >>   File 
>> >> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/base.py", 
>> line 
>> >> 2147, in _wrap_pool_connect 
>> >> return fn() 
>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
>> line 
>> >> 328, in unique_connection 
>> >> return _ConnectionFairy._checkout(self) 
>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
>> line 
>> >> 766, in _checkout 
>> >> fairy = _ConnectionRecord.checkout(pool) 
>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
>> line 
>> >> 516, in checkout 
>> >> rec = pool._do_get() 
>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
>> line 
>> >> 1138, in _do_get 
>> >> self._dec_overflow() 
>> >>   File 
>> >> 
>> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/langhelpers.py", 
>> >> line 66, in __exit__ 
>> >> compat.reraise(exc_type, exc_value, exc_tb) 
>> >>   File 
>> >> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/compat.py", 
>> line 
>> >> 187, in reraise 
>> >> raise value 
>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
>> line 
>> >> 1135, in _do_get 
>> >> return self._create_connection() 
>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
>> line 
>> >> 333, in _create_connection 
>> >> return _ConnectionRecord(self) 
>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
>> line 
>> >> 461, in __init__ 
>> >> self.__connect(first_connect_check=True) 
>> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
>> line 
>> >> 651, in __connect 
>> >> connection =

Re: [sqlalchemy] Re: Python 3.5 mysql-connector==2.1.6 SQLAlchemy==1.1.11 queue Empty

2017-07-17 Thread josip povreslo 
Mike,

Just as an info, mysql-connector is a total NO for us, when I switched to 
mysql-connector we saw bigger problems and those happens much more often. 
PyMySQL is the last thing to switch to and try. For this processing piece 
I'm thinking to replace SQLAlchemy with a direct database driver usage, do 
you think that's the catch or this is a more general issue with the drivers 
itself?

Best,
Josip

On Friday, July 14, 2017 at 11:57:12 PM UTC+2, Mike Bayer wrote:
>
> that "mysqldb" URL is not mysql-connector - it's MySQL-Python (old and 
> unmaintained) or mysqlclient (much better, if you have it installed 
> and not conflicting w/ MySQL-Python).   You'd need to specify 
> "mysql+mysqlconnector://" to use that DBAPI, but also I'd recommend 
> trying "mysql+pymysql://" to see if you get better results. 
>
> as far as "can't connect to server" you should make sure you are not 
> surpassing your max_connections limit, though it should be emitting 
> different error message for that. 
>
> The Queue.Empty error is not the "error", it has to do with how the 
> QueuePool works internally to test that the pool is empty, and Python 
> 3 can't help but report every exception in the chain.   The error is 
> the can't connect to server. 
>
>
>
> On Fri, Jul 14, 2017 at 5:41 PM, josip povreslo 
>  wrote: 
> > In addition to the previous email, what I do is the following: 
> > 
> > When request comes in: 
> > self.mysql_engine = 
> > 
> create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(self.db_params['dbuser'], 
>
> > self.db_params['dbpass'], self.db_params['db_hosts'][db_host_index], 
> > self.db_params['dbname']), pool_recycle=3600, 
> > connect_args={'connect_timeout': 2}) 
> > 
> > And then when need to interact with the dB: 
> > 
> > connection = self.mysql_engine.connect() 
> > connection.execute("raw query") 
> > connection.close() 
> > 
> > 
> > Best Regards, 
> > Josip 
> > 
> > On Friday, July 14, 2017 at 11:36:26 PM UTC+2, josip povreslo wrote: 
> >> 
> >> Hello, 
> >> 
> >> As mentioned in the subject, we have the following packages installed: 
> >> 
> >> Python 3.5 
> >> mysql-connector==2.1.6 
> >> SQLAlchemy==1.1.11 
> >> 
> >> Our service is working until we get a bit more traffic (40 - 50 
> req/min), 
> >> although I'm still not 100% if it's a pattern, however, then we get 
> this 
> >> error: 
> >> 
> >> Traceback (most recent call last): 
> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
> line 
> >> 1122, in _do_get 
> >> return self._pool.get(wait, self._timeout) 
> >>   File 
> >> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/queue.py", 
> line 
> >> 145, in get 
> >> raise Empty 
> >> sqlalchemy.util.queue.Empty 
> >> During handling of the above exception, another exception occurred: 
> >> Traceback (most recent call last): 
> >>   File 
> >> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/base.py", 
> line 
> >> 2147, in _wrap_pool_connect 
> >> return fn() 
> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
> line 
> >> 328, in unique_connection 
> >> return _ConnectionFairy._checkout(self) 
> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
> line 
> >> 766, in _checkout 
> >> fairy = _ConnectionRecord.checkout(pool) 
> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
> line 
> >> 516, in checkout 
> >> rec = pool._do_get() 
> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
> line 
> >> 1138, in _do_get 
> >> self._dec_overflow() 
> >>   File 
> >> 
> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/langhelpers.py", 
> >> line 66, in __exit__ 
> >> compat.reraise(exc_type, exc_value, exc_tb) 
> >>   File 
> >> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/compat.py", 
> line 
> >> 187, in reraise 
> >> raise value 
> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
> line 
> >> 1135, in _do_get 
> >> return self._create_connection() 
> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
> line 
> >> 333, in _create_connection 
> >> return _ConnectionRecord(self) 
> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
> line 
> >> 461, in __init__ 
> >> self.__connect(first_connect_check=True) 
> >>   File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", 
> line 
> >> 651, in __connect 
> >> connection = pool._invoke_creator(self) 
> >>   File 
> >> 
> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/strategies.py", 
> >> line 105, in connect 
> >> return dialect.connect(*cargs, **cparams) 
> >>   File 
> >> 
> "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/default.py", 
> >> line 393, in connect 
> >> return self.dbapi.connect(*cargs, **cparams) 
> >>   File "/usr/local/lib64/python3.5/site-packages/MySQLdb/__init__.py", 
> >> line