date:20200225

[sqlalchemy] Re: Bitwise AND operation in a select statement support in sqlalchemy

2020-02-25 Thread Balukrishnan

Hi, I need one more help.
By using *select([testa.c.id.op("&")(15)]) *this query i got the result 
object but the return value can only accessed by the key '*anon_1'.*
I tried to label (and anon_lalel) this query but most of the times query 
fails and the rest the same result(that is label is not getting applied.
I tried these

   - *select([testa.c.id.op.label("Id").("&")(15)]) .  => No changes still 
   the value can only accesses by the key anon_1*
   - 
*select([testa.c.id.op("&")(15).label("Id")]) .  => Error (AttributeError: 
   Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 
   'table') *
   - *select([testa.c.id.anon_label("Id").op("&")(15)]) .  => Error 
   (TypeError: '_anonymous_label' object is not callable)  *
   - *select([testa.c.id.op("&")(15).anon_label("Id")]) .  **=> Error 
   (TypeError: '_anonymous_label' object is not callable)*

How can I label that column with a known name

On Tuesday, February 25, 2020 at 10:32:22 PM UTC+5:30, Balukrishnan wrote:
>
> Table definition
>
> *from sqlalchemy import *
> testa = Table(
> "testa",
> metadata,
> Column("id", BigInteger, primary_key=True),
> Column("str_var_a", String, nullable=True),
> Colmn("bool_var_a", Boolean, nullable=True),
> )
> *
>
> and I need to execute a query like.
>
> *select([testa.c.id  & 15])*
>
> But while executing this query getting an error
>
> Traceback (most recent call last):
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/operators.py",
>  line 81, in __and__
> return self.operate(and_, other)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 731, in operate
> return op(self.comparator, *other, **kwargs)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/operators.py",
>  line 81, in __and__
> return self.operate(and_, other)
>   File "", line 1, in 
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py",
>  line 67, in operate
> return o[0](self.expr, op, *(other + o[1:]), **kwargs)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py",
>  line 147, in _conjunction_operate
> return and_(expr, other)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 2098, in and_
> return cls._construct(operators.and_, True_, False_, *clauses)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 2028, in _construct
> clauses = [
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 2029, in 
> _expression_literal_as_text(clause)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 4569, in _expression_literal_as_text
> return _literal_as_text(element)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 4592, in _literal_as_text
> return _literal_as(element, _no_text_coercion)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 4582, in _literal_as
> raise exc.ArgumentError(
> sqlalchemy.exc.ArgumentError: SQL expression object expected, got object of 
> type  instead
>
>
> But in Postgres using psql command I can perform the query *SELECT id & 
> 15 FROM testa;*. Is there any *support* for this in *sqlalchemy*.
>

-- 
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/7665b7ce-44fc-4a9d-baf6-5175b689e2ab%40googlegroups.com.

Re: [sqlalchemy] Re: Help with configuring connect_timeout setting for when we a high available postgres database drops

2020-02-25 Thread Karim Gillani

More information:

I tested by going into the python pod, and running looped psql select 1 
command with and without PGCONNECT_TIMEOUT=2.  Without the PGCONNECT, I 
also saw 2 minute delays but with PGCONNECT set, the error came back in 2 
secs.  I expected the same results by setting the connect_timeout but I 
don't see them.

Thanks

On Tuesday, 25 February 2020 21:11:56 UTC-8, Karim Gillani wrote:
>
> We are using openshift which uses HAProxy.  What I am looking for is why 
> the connect_timeout may not being applied so that we can retry the call 
> after re-connect to the other database pod.  
>
> It is taking *up to two minutes* before I get 
> the psycopg2.OperationalError.  It's very strange.  The code will 
> automatically retry and work after this error shows up.  I am guessing but 
> I don't think my setup is passing the connect_timeout to libpq.
>
> Thanks
>
> On Tuesday, 25 February 2020 10:37:19 UTC-8, Mike Bayer wrote:
>>
>> "could not connect" means your server is unreachable from a network 
>> standpoint.  You want to make sure the server is running and that it is 
>> routable over the network you are trying to connect from.
>>
>> in an HA scenario, depending on what software is in use and how it does 
>> switchover, there can be long network lags, such as if it is moving a VIP 
>> from one machine to another.  You'd want to be using a proxy server such as 
>> HAProxy or PGBouncer rather than relying upon changing network routing.
>>
>>
>>
>> On Tue, Feb 25, 2020, at 12:07 PM, Karim Gillani wrote:
>>
>> Please note I am using a Pool.  And some other connections seem to 
>> recover fine.  After two minutes I get this message and things start 
>> working again:
>>
>> ==> AE K:11oh392; T:1; E:(psycopg2.OperationalError) could not connect to 
>> server: Connection timed out> Is the server running on host 
>> "patroni-mastertheq" (172.50.46.180) and accepting> TCP/IP connections on 
>> port 5432?>>(Background on this error at: http://sqlalche.me/e/e3q8 ) 
>> Thanks
>> Karim
>>
>> On Tuesday, 25 February 2020 08:49:19 UTC-8, Karim Gillani wrote:
>>
>> I am having an issue where when my HA postgres database drops and is 
>> automatically moved to a backup database, the timeout doesn't seem to be 
>> working.
>>
>> My config.py shows the following:
>>
>> SQLALCHEMY_DATABASE_URI = 
>> '{engine}://{user}:{password}@{host}:{port}/{name}'.format(
>>
>> engine=DB_ENGINE,
>>
>> user=DB_USER,
>>
>> password=DB_PASSWORD,
>>
>> host=DB_HOST,
>>
>> port=DB_PORT,
>>
>> name=DB_NAME,
>>
>> )
>> SQLALCHEMY_ENGINE_OPTIONS
>> = { 'pool_size' : pool_size, 'max_overflow' : max_overflow, 
>> 'pool_pre_ping' : True, 'pool_timeout': DB_POOL_TIMEOUT, 'pool_recycle': 
>> 3600, 'connect_args': { 'connect_timeout': 5, 'options' : '-c 
>> statement_timeout=5000' } }
>>
>> Is there something I set wrong?  My timeout seems to be about 2 minutes 
>> instead of 5 secs.
>>
>> Any help would be appreciated,
>>
>> Thanks
>> Karim
>>
>>
>> --
>> 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 sqlal...@googlegroups.com.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/sqlalchemy/ab3e5681-d9be-468f-8160-97bddcca57fb%40googlegroups.com
>>  
>> 
>> .
>>
>>
>>

-- 
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/778d34b5-463a-4561-8339-b72ec077cc62%40googlegroups.com.

Re: [sqlalchemy] Re: Help with configuring connect_timeout setting for when we a high available postgres database drops

2020-02-25 Thread Karim Gillani

We are using openshift which uses HAProxy.  What I am looking for is why 
the connect_timeout may not being applied so that we can retry the call 
after re-connect to the other database pod.  

It is taking *up to two minutes* before I get 
the psycopg2.OperationalError.  It's very strange.  The code will 
automatically retry and work after this error shows up.  I am guessing but 
I don't think my setup is passing the connect_timeout to libpq.

Thanks

On Tuesday, 25 February 2020 10:37:19 UTC-8, Mike Bayer wrote:
>
> "could not connect" means your server is unreachable from a network 
> standpoint.  You want to make sure the server is running and that it is 
> routable over the network you are trying to connect from.
>
> in an HA scenario, depending on what software is in use and how it does 
> switchover, there can be long network lags, such as if it is moving a VIP 
> from one machine to another.  You'd want to be using a proxy server such as 
> HAProxy or PGBouncer rather than relying upon changing network routing.
>
>
>
> On Tue, Feb 25, 2020, at 12:07 PM, Karim Gillani wrote:
>
> Please note I am using a Pool.  And some other connections seem to recover 
> fine.  After two minutes I get this message and things start working again:
>
> ==> AE K:11oh392; T:1; E:(psycopg2.OperationalError) could not connect to 
> server: Connection timed out> Is the server running on host 
> "patroni-mastertheq" (172.50.46.180) and accepting> TCP/IP connections on 
> port 5432?>>(Background on this error at: http://sqlalche.me/e/e3q8 ) 
> Thanks
> Karim
>
> On Tuesday, 25 February 2020 08:49:19 UTC-8, Karim Gillani wrote:
>
> I am having an issue where when my HA postgres database drops and is 
> automatically moved to a backup database, the timeout doesn't seem to be 
> working.
>
> My config.py shows the following:
>
> SQLALCHEMY_DATABASE_URI = 
> '{engine}://{user}:{password}@{host}:{port}/{name}'.format(
>
> engine=DB_ENGINE,
>
> user=DB_USER,
>
> password=DB_PASSWORD,
>
> host=DB_HOST,
>
> port=DB_PORT,
>
> name=DB_NAME,
>
> )
> SQLALCHEMY_ENGINE_OPTIONS
> = { 'pool_size' : pool_size, 'max_overflow' : max_overflow, 
> 'pool_pre_ping' : True, 'pool_timeout': DB_POOL_TIMEOUT, 'pool_recycle': 
> 3600, 'connect_args': { 'connect_timeout': 5, 'options' : '-c 
> statement_timeout=5000' } }
>
> Is there something I set wrong?  My timeout seems to be about 2 minutes 
> instead of 5 secs.
>
> Any help would be appreciated,
>
> Thanks
> Karim
>
>
> --
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/ab3e5681-d9be-468f-8160-97bddcca57fb%40googlegroups.com
>  
> 
> .
>
>
>

-- 
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/06c8e91e-4f36-4192-90d0-acbe840478ef%40googlegroups.com.

Re: [sqlalchemy] Bitwise AND operation in a select statement support in sqlalchemy

2020-02-25 Thread Balukrishnan

Thank you sir, it's working.

On Tuesday, February 25, 2020 at 10:52:21 PM UTC+5:30, Simon King wrote:
>
> SQLAlchemy overrides the & operator: 
>
> 
> https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.and_
>  
>
> You can use the "op" function to get at the postgres & operator: 
>
> 
> https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.op
>  
>
> Something like this: 
>
> select([testa.c.id.op("&")(15)]) 
>
> Hope that helps, 
>
> Simon 
>
> On Tue, Feb 25, 2020 at 5:02 PM Balukrishnan  > wrote: 
> > 
> > Table definition 
> > 
> > from sqlalchemy import * 
> > testa = Table( 
> > "testa", 
> > metadata, 
> > Column("id", BigInteger, primary_key=True), 
> > Column("str_var_a", String, nullable=True), 
> > Colmn("bool_var_a", Boolean, nullable=True), 
> > ) 
> > 
> > and I need to execute a query like. 
> > 
> > select([testa.c.id & 15]) 
> > 
> > But while executing this query getting an error 
> > 
> > Traceback (most recent call last): 
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/operators.py",
>  
> line 81, in __and__ 
> > return self.operate(and_, other) 
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  
> line 731, in operate 
> > return op(self.comparator, *other, **kwargs) 
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/operators.py",
>  
> line 81, in __and__ 
> > return self.operate(and_, other) 
> >   File "", line 1, in  
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py",
>  
> line 67, in operate 
> > return o[0](self.expr, op, *(other + o[1:]), **kwargs) 
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py",
>  
> line 147, in _conjunction_operate 
> > return and_(expr, other) 
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  
> line 2098, in and_ 
> > return cls._construct(operators.and_, True_, False_, *clauses) 
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  
> line 2028, in _construct 
> > clauses = [ 
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  
> line 2029, in  
> > _expression_literal_as_text(clause) 
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  
> line 4569, in _expression_literal_as_text 
> > return _literal_as_text(element) 
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  
> line 4592, in _literal_as_text 
> > return _literal_as(element, _no_text_coercion) 
> >   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  
> line 4582, in _literal_as 
> > raise exc.ArgumentError( 
> > sqlalchemy.exc.ArgumentError: SQL expression object expected, got object 
> of type  instead 
> > 
> > But in Postgres using psql command I can perform the query SELECT id & 
> 15 FROM testa;. Is there any support for this in sqlalchemy. 
> > 
> > -- 
> > 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 sqlal...@googlegroups.com . 
> > To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/37691a3d-826a-4311-bbdb-6ba3eb67b95a%40googlegroups.com.
>  
>
>

-- 
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/6c5f2781-d1ca-45b1-b847-41c44377b3b8%40googlegroups.com.

Re: [sqlalchemy] Re: Help with configuring connect_timeout setting for when we a high available postgres database drops

2020-02-25 Thread Mike Bayer

"could not connect" means your server is unreachable from a network standpoint. 
You want to make sure the server is running and that it is routable over the 
network you are trying to connect from.

in an HA scenario, depending on what software is in use and how it does 
switchover, there can be long network lags, such as if it is moving a VIP from 
one machine to another. You'd want to be using a proxy server such as HAProxy 
or PGBouncer rather than relying upon changing network routing.



On Tue, Feb 25, 2020, at 12:07 PM, Karim Gillani wrote:
> Please note I am using a Pool. And some other connections seem to recover 
> fine. After two minutes I get this message and things start working again:
> 
> ==> AE K:11oh392; T:1; E:(psycopg2.OperationalError) could not connect to 
> server: Connection timed out> Is the server running on host 
> "patroni-mastertheq" (172.50.46.180) and accepting> TCP/IP connections on 
> port 5432?>>(Background on this error at: http://sqlalche.me/e/e3q8 ) 
> Thanks
> Karim
> 
> On Tuesday, 25 February 2020 08:49:19 UTC-8, Karim Gillani wrote:
>> I am having an issue where when my HA postgres database drops and is 
>> automatically moved to a backup database, the timeout doesn't seem to be 
>> working.
>> 
>> My config.py shows the following:
>> 
>> SQLALCHEMY_DATABASE_URI = 
>> '{engine}://{user}:{password}@{host}:{port}/{name}'.format(
>> 
>> engine=DB_ENGINE,
>> 
>> user=DB_USER,
>> 
>> password=DB_PASSWORD,
>> 
>> host=DB_HOST,
>> 
>> port=DB_PORT,
>> 
>> name=DB_NAME,
>> 
>> )
>> SQLALCHEMY_ENGINE_OPTIONS
>> = { 'pool_size' : pool_size, 'max_overflow' : max_overflow, 'pool_pre_ping' 
>> : True, 'pool_timeout': DB_POOL_TIMEOUT, 'pool_recycle': 3600, 
>> 'connect_args': { 'connect_timeout': 5, 'options' : '-c 
>> statement_timeout=5000' } } 
>> 
>> Is there something I set wrong? My timeout seems to be about 2 minutes 
>> instead of 5 secs.
>> 
>> Any help would be appreciated,
>> 
>> Thanks
>> Karim
>> 
> 

> --
>  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/ab3e5681-d9be-468f-8160-97bddcca57fb%40googlegroups.com
>  
> .

-- 
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/78c2b7d9-32a2-4ffa-9c46-f746f5206335%40www.fastmail.com.

Re: [sqlalchemy] Bitwise AND operation in a select statement support in sqlalchemy

2020-02-25 Thread Simon King

SQLAlchemy overrides the & operator:


https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.and_

You can use the "op" function to get at the postgres & operator:


https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.op

Something like this:

select([testa.c.id.op("&")(15)])

Hope that helps,

Simon

On Tue, Feb 25, 2020 at 5:02 PM Balukrishnan  wrote:
>
> Table definition
>
> from sqlalchemy import *
> testa = Table(
> "testa",
> metadata,
> Column("id", BigInteger, primary_key=True),
> Column("str_var_a", String, nullable=True),
> Colmn("bool_var_a", Boolean, nullable=True),
> )
>
> and I need to execute a query like.
>
> select([testa.c.id & 15])
>
> But while executing this query getting an error
>
> Traceback (most recent call last):
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/operators.py",
>  line 81, in __and__
> return self.operate(and_, other)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 731, in operate
> return op(self.comparator, *other, **kwargs)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/operators.py",
>  line 81, in __and__
> return self.operate(and_, other)
>   File "", line 1, in 
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py",
>  line 67, in operate
> return o[0](self.expr, op, *(other + o[1:]), **kwargs)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py",
>  line 147, in _conjunction_operate
> return and_(expr, other)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 2098, in and_
> return cls._construct(operators.and_, True_, False_, *clauses)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 2028, in _construct
> clauses = [
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 2029, in 
> _expression_literal_as_text(clause)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 4569, in _expression_literal_as_text
> return _literal_as_text(element)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 4592, in _literal_as_text
> return _literal_as(element, _no_text_coercion)
>   File 
> "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
>  line 4582, in _literal_as
> raise exc.ArgumentError(
> sqlalchemy.exc.ArgumentError: SQL expression object expected, got object of 
> type  instead
>
> But in Postgres using psql command I can perform the query SELECT id & 15 
> FROM testa;. Is there any support for this in sqlalchemy.
>
> --
> 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/37691a3d-826a-4311-bbdb-6ba3eb67b95a%40googlegroups.com.

-- 
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/CAFHwexeLhoePjd2ibbTDxBtKhqE3AxwvBEGj4wKckm4PiesgpA%40mail.gmail.com.

[sqlalchemy] Re: Help with configuring connect_timeout setting for when we a high available postgres database drops

2020-02-25 Thread Karim Gillani

Please note I am using a Pool.  And some other connections seem to recover 
fine.  After two minutes I get this message and things start working again:

==> AE K:11oh392; T:1; E:(psycopg2.OperationalError) could not connect to 
server: Connection timed out> Is the server running on host 
"patroni-mastertheq" (172.50.46.180) and accepting> TCP/IP connections on 
port 5432?>>(Background on this error at: http://sqlalche.me/e/e3q8 ) 
Thanks
Karim

On Tuesday, 25 February 2020 08:49:19 UTC-8, Karim Gillani wrote:
>
> I am having an issue where when my HA postgres database drops and is 
> automatically moved to a backup database, the timeout doesn't seem to be 
> working.
>
> My config.py shows the following:
>
> SQLALCHEMY_DATABASE_URI = 
> '{engine}://{user}:{password}@{host}:{port}/{name}'.format(
> engine=DB_ENGINE,
> user=DB_USER,
> password=DB_PASSWORD,
> host=DB_HOST,
> port=DB_PORT,
> name=DB_NAME,
> )SQLALCHEMY_ENGINE_OPTIONS = { 'pool_size' : pool_size, 'max_overflow' : 
> max_overflow, 'pool_pre_ping' : True, 'pool_timeout': DB_POOL_TIMEOUT, 
> 'pool_recycle': 3600, 'connect_args': { 'connect_timeout': 5, 'options' : '-c 
> statement_timeout=5000' } }
>
> Is there something I set wrong?  My timeout seems to be about 2 minutes 
> instead of 5 secs.
>
> Any help would be appreciated,
>
> Thanks
> Karim
>
>

-- 
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/ab3e5681-d9be-468f-8160-97bddcca57fb%40googlegroups.com.

[sqlalchemy] Bitwise AND operation in a select statement support in sqlalchemy

2020-02-25 Thread Balukrishnan



Table definition

*from sqlalchemy import *
testa = Table(
"testa",
metadata,
Column("id", BigInteger, primary_key=True),
Column("str_var_a", String, nullable=True),
Colmn("bool_var_a", Boolean, nullable=True),
)
*

and I need to execute a query like.

*select([testa.c.id & 15])*

But while executing this query getting an error

Traceback (most recent call last):
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/operators.py",
 line 81, in __and__
return self.operate(and_, other)
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
 line 731, in operate
return op(self.comparator, *other, **kwargs)
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/operators.py",
 line 81, in __and__
return self.operate(and_, other)
  File "", line 1, in 
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py",
 line 67, in operate
return o[0](self.expr, op, *(other + o[1:]), **kwargs)
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py",
 line 147, in _conjunction_operate
return and_(expr, other)
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
 line 2098, in and_
return cls._construct(operators.and_, True_, False_, *clauses)
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
 line 2028, in _construct
clauses = [
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
 line 2029, in 
_expression_literal_as_text(clause)
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
 line 4569, in _expression_literal_as_text
return _literal_as_text(element)
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
 line 4592, in _literal_as_text
return _literal_as(element, _no_text_coercion)
  File 
"/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py",
 line 4582, in _literal_as
raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: SQL expression object expected, got object of 
type  instead


But in Postgres using psql command I can perform the query *SELECT id & 15 
FROM testa;*. Is there any *support* for this in *sqlalchemy*.

-- 
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/37691a3d-826a-4311-bbdb-6ba3eb67b95a%40googlegroups.com.

Re: Re: Re: Integrate PyHive and Alembic

2020-02-25 Thread Ke Zhu - k...@us.ibm.com

According to Apache Hive, Update/Delete can only be performed on tables that 
supports ACID. see 
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Update

> this is a database that can change schema structures but not 
> insert/update/delete rows? is there some kind of SQL layer that has to be in 
> use that isn't there when you create structures?

Yes! Basically it separate data store and data model so that you can update 
table schema as metadata while the actual data can be loaded/streamed into 
external data store w/o SQL interface.

> that wouldn't be very easy but also it would be way better to store the 
> version info in the target DB itself.

totally understand this. I see this design as advantage as well just like other 
schema management tool like sqitch. I'm just looking for possiblilities since 
I've seen it uses the same connection to execute schema changes and versioning 
change in a migration context.

> I don't know anything about Hive/Presto, but they *are* databases so I'd 
> assume you can put data in them

Yes to Hive, when using PyHive, it just needs to fix 
https://github.com/dropbox/PyHive/issues/315

While, the answer will be no to PrestoSQL which is just a SQL query engine that 
delegates data model and data store to query targets 
(MySQL/Postgres/Kafka/Elasticsearch etc) via connectors.

On Mon, 2020-02-24 at 18:28 -0500, Mike Bayer wrote:

On Mon, Feb 24, 2020, at 3:44 PM, Ke Zhu - k...@us.ibm.com wrote:
Mike,

Thanks for the pointers. I've figured out the programming part and discovers 
more things when integrating PyHive:

1. It requires the table alembic_version to be transactional so that it can do 
Update/Delete when upgrade/downgrade. which is challege for Hive3 which has 
limited ACID support. Since I could not figure out a way to program a 
transactional table via SQLAlechemy API (see 
https://github.com/dropbox/PyHive/issues/314),
 it ended up a decoration to patch sqlalchemy.schema.CreateTable by appending 
table properties.

it's not a hard requirement that there's a DB transaction in use, you could run 
alembic in an "autocommit" mode and that shouldn't cause any problem.  you just 
won't be able to roll back if something fails.   im not completely sure what 
you mean by "the table to be transactional so that it can do update/delete" but 
from Alembic's point of view it just needs to run INSERT/UPDATE/DELETE but 
there doesn't have to be any ACID guarantees.

2. PyHive doesn't fully support Update/Delete for Hive/Presto yet. it's easy to 
patch PyHive but the key problem is it doesn't support transactional DDL like 
rollback in Hive3.

Although I've managed to get `alembic upgrade` and `alembic downgrade` to work 
on Hive 3, it's still not a fully transactional experience (e.g., changed 
schema sccessfully but failed to update table alembic_version).

so..this is a database that can change schema structures but not 
insert/update/delete rows?  im not following.  is there some kind of SQL layer 
that has to be in use that isn't there when you create structures?

I wonder if there's any design direction in Alembic to allowing storing the 
version table `alembic_version` in another db when dealing with non-RDBMS SQL 
engine (Hive/Presto). e.g., supporting a postgres db to store table alembic 
while delivering the actual changes to Hive. I had a PoC to using multi-db 
template to manage the table `alembic_version` in a RDBMS while denying any 
operation on table `alembic_version` in Hive/Presto. it works now but does it 
sound right?

that wouldn't be very easy but also it would be way better to store the version 
info in the target DB itself.   I don't know anything about Hive/Presto, but 
they *are* databases so I'd assume you can put data in them.

On Sat, 2020-01-25 at 18:19 -0500, Mike Bayer wrote:

On Fri, Jan 24, 2020, at 1:56 PM, Ke Zhu wrote:
Just discovered this post when trying to do exact same thing (besides planning 
to support one more dialect).

> Anywhere in your  hive dialect, simply put the above code that you have 
> (using the correct imports of course).

Does it mean it must introduce dependency to alembic (since it uses 
alembic.ddl.impl.DefaultImpl) in a package (.e.g, pyHive) that supports 
sqlalchemy interfaces?

well you have to put it in a try/except ImportError block so that if alembic 
isn't installed, it silently passes.   there's a github issue to add support 
for real entrypoints but it hasn't been that critical.

If not, is there any guidance to support this at alembic level in a plug-gable 
way? E.g., declare a HiveImpl class in `env.py` of a project uses alembic?

you could put one in your env.py also but if you are the person working on the 
dialect you can have

[sqlalchemy] Re: Bitwise AND operation in a select statement support in sqlalchemy

Re: [sqlalchemy] Re: Help with configuring connect_timeout setting for when we a high available postgres database drops

Re: [sqlalchemy] Re: Help with configuring connect_timeout setting for when we a high available postgres database drops

Re: [sqlalchemy] Bitwise AND operation in a select statement support in sqlalchemy

Re: [sqlalchemy] Re: Help with configuring connect_timeout setting for when we a high available postgres database drops

Re: [sqlalchemy] Bitwise AND operation in a select statement support in sqlalchemy

[sqlalchemy] Re: Help with configuring connect_timeout setting for when we a high available postgres database drops

[sqlalchemy] Bitwise AND operation in a select statement support in sqlalchemy

Re: Re: Re: Integrate PyHive and Alembic

9 matches

Site Navigation

Mail list logo

Footer information