[sqlalchemy] The right way to use gevent with sqlalchemy

2017-07-14 Thread Tony Wang 


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


More details for 
error 
https://stackoverflow.com/questions/45113145/the-right-way-to-use-gevent-with-sqlalchemy


Thanks!

-- 
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] Re: Python 3.5 mysql-connector==2.1.6 SQLAlchemy==1.1.11 queue Empty

2017-07-14 Thread josip povreslo 
Thanks!

On Saturday, July 15, 2017 at 12:08:19 AM UTC+2, Mike Bayer wrote:
>
> I've set up 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4028/move-cant-connect-handling-outside-of
>  
> to deal with the misleading "pool.Empty" error for 1.2, though that is 
> not the problem you're having here. 
>
> On Fri, Jul 14, 2017 at 5:57 PM, 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

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

2017-07-14 Thread josip povreslo 
Thanks Mike!

Point valid, it's mysqlclient==1.3.9, we don't have MySQL-Python installed, 
inherited the system so I assumed it's mysql-connector from some reason, 
nevertheless. 

Hmmm, I was afraid that's the actual case (connection error). We were 
checking the connection limits and that's not the case, will try to switch 
everything over to mysql-connector then and get back with the results. Will 
do it on Monday (not pushing things live over the weekend :)). 

Best Regards,
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) 
>

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

2017-07-14 Thread Mike Bayer 
I've set up 
https://bitbucket.org/zzzeek/sqlalchemy/issues/4028/move-cant-connect-handling-outside-of
to deal with the misleading "pool.Empty" error for 1.2, though that is
not the problem you're having here.

On Fri, Jul 14, 2017 at 5:57 PM, 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 81, in Connect
>>> return Connection(*args, **kwargs)
>>>   File "/usr/local/lib64/python3.5/site-packages/MySQLdb/connections.py",
>>> line 191, in __init__
>>> super(Connection, self).__init__(*args, **kwargs2)
>>> _mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server
>>> on 'dbhost_obviously' (4)")
>>>
>>> We haven't been seeing this error up until recently when we ported our
>>> service to

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

2017-07-14 Thread Mike Bayer 
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 81, in Connect
>> return Connection(*args, **kwargs)
>>   File "/usr/local/lib64/python3.5/site-packages/MySQLdb/connections.py",
>> line 191, in __init__
>> super(Connection, self).__init__(*args, **kwargs2)
>> _mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server
>> on 'dbhost_obviously' (4)")
>>
>> We haven't been seeing this error up until recently when we ported our
>> service to Python 3.5. I've read most of the articles related to this queue
>> empty error and most of those are from '12 or '13 and mentioning immature
>> mysql drivers. Is this still the case, should we search for an alternative
>> to mysql-connector or alternative to something else? Any help or guidance is
>> appreciated!
>>
>> Thanks!
>>
>> Best,
>> Josip
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper

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

2017-07-14 Thread josip povreslo 
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 81, in Connect
> return Connection(*args, **kwargs)
>   File "/usr/local/lib64/python3.5/site-packages/MySQLdb/connections.py", 
> line 191, in __init__
> super(Connection, self).__init__(*args, **kwargs2)
> _mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server 
> on 'dbhost_obviously' (4)")
>
> We haven't been seeing this error up until recently when we ported our 
> service to Python 3.5. I've read most of the articles related to this *queue 
> empty *error and most of those are from '12 or '13 and mentioning 
> immature mysql drivers. Is this still the case, should we search for an 
> alternative to mysql-connector or alternative to something else? Any help 
> or guidance is appreciated!
>
> Thanks!
>
> Best,
> Josip
>

-- 
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.

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

2017-07-14 Thread josip povreslo 
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 
81, in Connect
return Connection(*args, **kwargs)
  File "/usr/local/lib64/python3.5/site-packages/MySQLdb/connections.py", 
line 191, in __init__
super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server 
on 'dbhost_obviously' (4)")

We haven't been seeing this error up until recently when we ported our 
service to Python 3.5. I've read most of the articles related to this *queue 
empty *error and most of those are from '12 or '13 and mentioning immature 
mysql drivers. Is this still the case, should we search for an alternative 
to mysql-connector or alternative to something else? Any help or guidance 
is appreciated!

Thanks!

Best,
Josip

-- 
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] session.add() neglecting some of my objects to be added

2017-07-14 Thread Mike Bayer 
On Fri, Jul 14, 2017 at 1:24 AM, David Laredo Razo
 wrote:


this code is the problem:

>
> new_object = copy.copy(reading)

copy() will copy the _sa_instance_state and prevent the session from
tracking the object correctly.

Correct pattern should be:

new_object = ThermafuserReading(None, componentId)

Only when you call the constructor (e.g. ThermafuserReading.__init__)
do you get a new InstanceState object dedicated to that object. So
don't use copy().

There *are* ways to use copy() here instead but they are non-obvious
and not necessary for a simple case like this.




> new_object.timestamp = timestamp
>
> readings.append(new_object)
>
> #print(new_object, mapper.identity_key_from_instance(new_object))
> #session.add(new_object)
>
> row_format = "{:>15}" * (len(header) + 1)
>
> print("Before adding to the session")
> print(row_format.format("", *header))
> for reading in readings:
> insp = inspect(reading)
> row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent,
> insp.detached, insp.deleted, reading in session]
> print(row_format.format("", *row))
>
> session.add_all(readings)
>
> print("\n#Elements in the session")
> print(session)
> for element in session:
> print(element)
>
> print("\nAfter adding to the session")
> print(row_format.format("", *header))
> for reading in readings:
> insp = inspect(reading)
> row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent,
> insp.detached, insp.deleted, reading in session]
> print(row_format.format("", *row))
>
> These are some results I obtained by comparing wheter the objects in my list
> are in the session or not
>
>
>
>
> As you can observe, according to the results above the objects are indeed
> inside the session but for some reason when I try to print whats contained
> in the session by doing
>
> for element in session:
>print(element)
>
> I just get a None, what am I doing wrong? I dont see anything wrong in my
> code, I hope you can help me clarify this. Thanks in advance.
>
> I will attach both my code and the tests data in case you want to try it by
> yourself.
>
>
>
>
>
> On Thursday, July 13, 2017 at 8:27:04 AM UTC-5, Mike Bayer wrote:
>>
>> On Thu, Jul 13, 2017 at 12:31 AM, David Laredo Razo
>>  wrote:
>> > Hello, I am using SQLAlchemy version 1.2.0b1
>> >
>> >
>> >
>> > So far so go, the problem arises when I add readings to the session via
>> > session.add_all(readings). I only get the last element in my list added,
>> > e.g.
>>
>> there's no reason at all that would happen, other than what's in
>> "readings" is not what you'd expect.
>>
>> try iterating through every element in "readings" after the add_all(),
>> and do "obj in session".
>>
>> If some of these objects were from a different session, then they may
>> be "detached" as you put them in in which case they'd go into
>> session.identity_map, not session.new.
>>
>>
>>
>>
>> >
>> > for new in session.new:
>> >print(new, mapper.identity_key_from_instance(new_object))
>> >
>> > 
>> > (, (datetime.datetime(2017, 1, 1,
>> > 0,
>> > 0), 1))
>> >
>> >
>> > Why is this behavior? I have a test code and the test data in case its
>> > needed to reproduce this behavior
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > --
>> > 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+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>
> --
> 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.

-- 
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

Re: [sqlalchemy] Re: inline polymorphic_load for single table inheritance

2017-07-14 Thread Mike Bayer 
On Fri, Jul 14, 2017 at 10:40 AM, Shane Carey  wrote:
> Okay, I understand that. My example isnt actually the best for the question
> I am asking.
>
> Say I have three subclasses ChildOne, ChildTwo, ChildThree
>
> is there a way to query for ChildOne and ChildTwo in one query, or do I need
> to filter on the discriminator?
>
> something like session.query(ChildOne, ChildTwo).all()

you could actually say query(ChildOne, ChildTwo) but that means you're
selecting from the table twice in one SELECT, you'd need to use
aliases.

if you wanted to do it purely relationally (which I don't recommend
here), you'd need to say
session.query(ChildOne).union(session.query(ChildTwo)), since that's
really what you're doing from a relational perspective.

However, filter on the discriminator is from a SQL perspective a lot
more direct, which would be my recommendation.



>
> --
> 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.

-- 
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.

[sqlalchemy] Re: inline polymorphic_load for single table inheritance

2017-07-14 Thread Shane Carey 
Okay, I understand that. My example isnt actually the best for the question 
I am asking.

Say I have three subclasses ChildOne, ChildTwo, ChildThree

is there a way to query for ChildOne and ChildTwo in one query, or do I 
need to filter on the discriminator?

something like session.query(ChildOne, ChildTwo).all()

-- 
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.