Re: [sqlalchemy] Configuring pre_ping causes exception on error with psycopg2

[Mike Bayer]

On Tue, Feb 6, 2018 at 1:36 PM, André Cruz  wrote:
> Hello Mike.
>
>
> On 6 Feb 2018, at 16:36, Mike Bayer  wrote:
>
> So it's only using SQLAlchemy''s pool functionality to manage Django DB
> connections. I guess I just have to pass the "dbapi=psycopg2" parameter when
> creating the dialect?
>
>
> yes or report it as a bug over to that project, they can just fix it.
>
>
> Apparently this is not enough. The ping command will throw a
> "django.db.utils.OperationalError", which is not caught by the except clause
> "except self.dbapi.Error as err:".

this sounds like some kind of integration package so perhaps they can
catch and re-raise that?  but yes, pool_pre_ping is designed with
SQLAlchemy dialects in mind :)


>
> It seems I will not be able to use the "pre_ping" functionality with this
> pool of Django DB connections.
>
> Can I just subclass postgresql.dialect and override the do_ping() method or
> will that leave me with even more problems?
>
> Thank you and best regards,
> André
>
> --
> 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] pass additional kwargs to execute into BulkUD _do_exec method

[Антонио Антуан]

Hello Mike!
First of all, thank you for your help with this 
 problem. 
Me and my crew appreciate it.


I have a question in case of `identity_token` for `update` and `delete` 
methods of `Query` instances.

If we take a look on sqlalchemy.orm.persistence.BulkDelete._do_exec, we 
see, that there is no additonal kwargs passed to `execute` method. So I 
don't see any way to pass additional kwargs to Session.get_bind. Here is 
code of 1.0 version:

def _do_exec(self):
delete_stmt = sql.delete(self.primary_table,
 self.context.whereclause)

self.result = self.query.session.execute(
delete_stmt,
params=self.query._params,
mapper=self.mapper,
# need to pass here additional kwargs
)
self.rowcount = self.result.rowcount

Code from master branch (almost the same):

def _execute_stmt(self, stmt):
self.result = self.query.session.execute(
stmt, params=self.query._params,
mapper=self.mapper)
self.rowcount = self.result.rowcount




As you may remember, I have some kind of `CustomQuery` with `set_bind` 
method (like the same into sqlalchemy.ext.horizontal_shard.ShardedQuery). 
So I have `_bind_id` attribute within instances of that class.
My question is: Is there any way to pass `self.query._bind_id` to 
`self.query.session.execute` (see above)?

-- 
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] MySQL's sql_mode (ORM)

[jens . troeger]

If I were to go into my MySQL DB and

mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES';

would that have the same effect? I find the MySQL documentation 
 somewhat lacking on 
that topic. What are the scope and lifetime of the above vs. using a 
listener as suggested by Michael?



On Wednesday, February 5, 2014 at 2:08:00 PM UTC-8, Michael Bayer wrote:
>
> here’s a recipe for emitting that SQL on every connection, as well as 
> right up front on first connect which is optional, though if you plan on 
> changing ANSI_QUOTES would need to happen before the dialect checks on 
> sql_mode: 
>
> from sqlalchemy import create_engine, event 
>
> eng = create_engine("mysql://scott:tiger@localhost/test", echo='debug') 
>
> @event.listens_for(eng, "first_connect", insert=True)  # make sure we're 
> the very first thing 
> @event.listens_for(eng, "connect") 
> def connect(dbapi_connection, connection_record): 
> cursor = dbapi_connection.cursor() 
> cursor.execute("SET sql_mode = 'STRICT_ALL_TABLES'") 
>
> conn = eng.connect() 
>
>
>
> On Feb 5, 2014, at 3:20 PM, Staszek  > wrote: 
>
> > Hi 
> > 
> > How do you set sql_mode when using SQLAlchemy ORM with MySQL? 
> > 
> > For example, I would like to be able to do something equivalent to this: 
> > 
> > SET sql_mode = 'STRICT_ALL_TABLES'; 
> > 
> > so as to get an error (instead of a warning) when string length exceeds 
> > column size on INSERT. 
> > 
> > Ideally I would like to be able to combine several SQL modes together. 
> > List of available MySQL modes: 
> > 
> http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode
>  
> > . 
> > 
> > Thanks! 
> > 
> > -- 
> > http://people.eisenbits.com/~stf/ 
> > http://www.eisenbits.com/ 
> > 
> > OpenPGP: 80FC 1824 2EA4 9223 A986  DB4E 934E FEA0 F492 A63B 
> > 
> > -- 
> > 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 http://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/groups/opt_out. 
>
>

-- 
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] Configuring pre_ping causes exception on error with psycopg2

[André Cruz]

Hello Mike.


> On 6 Feb 2018, at 16:36, Mike Bayer  wrote:
> 
>> So it's only using SQLAlchemy''s pool functionality to manage Django DB
>> connections. I guess I just have to pass the "dbapi=psycopg2" parameter when
>> creating the dialect?
> 
> yes or report it as a bug over to that project, they can just fix it.

Apparently this is not enough. The ping command will throw a 
"django.db.utils.OperationalError", which is not caught by the except clause 
"except self.dbapi.Error as err:".

It seems I will not be able to use the "pre_ping" functionality with this pool 
of Django DB connections.

Can I just subclass postgresql.dialect and override the do_ping() method or 
will that leave me with even more problems?

Thank you and best regards,
André

-- 
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] Configuring pre_ping causes exception on error with psycopg2

[Mike Bayer]

On Tue, Feb 6, 2018 at 11:32 AM, André Cruz  wrote:
> Hello Mike.
>
> On 6 Feb 2018, at 16:24, Mike Bayer  wrote:
>
> it is unusual for people to use the pool without an engine like that.
> You can always get a pool by creating an engine normally and then
> grabbing engine.pool.   When the engine is created one of the things
> it does is load up the DBAPI for the dialect.  if you want to do that
> manually, it looks like:
>
>
> Thanks for the help. I'm using the package django_postgrespool2 so I'm
> currently not in control of how the pool is instantiated. The package does
> this:
>
> In
> https://github.com/lcd1232/django-postgrespool2/blob/master/django_postgrespool2/base.py
>
> from sqlalchemy.pool import manage
> ...
> POOL_SETTINGS = 'DATABASE_POOL_ARGS'
> pool_args = getattr(settings, POOL_SETTINGS, {})
> db_pool = manage(Database, **pool_args)
>
>
> So it's only using SQLAlchemy''s pool functionality to manage Django DB
> connections. I guess I just have to pass the "dbapi=psycopg2" parameter when
> creating the dialect?

yes or report it as a bug over to that project, they can just fix it.


>
> Best regards,
> André
>
> --
> 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] Configuring pre_ping causes exception on error with psycopg2

[andre]

Hello.

I'm using a connection pool backed by SQLAlchemy (1.2.1) to access a 
Postgresql (9.6) server using the psycopg2 (2.7.3.2) driver. I have the 
following pool configuration options set:

from sqlalchemy.dialects import postgresql
DATABASE_POOL_ARGS = {
'max_overflow': 0,
'pool_size': 3,
'recycle': 3600,
'timeout': 10,
'pre_ping': True,
'dialect': postgresql.dialect(),
}

Whenever an error occurs during the initial ping I get an exception:

'NoneType' object has no attribute 'Error'

on line 477 of file sqlalchemy/engine/default.py which is:

except self.dbapi.Error as err:

"self" in this case is an object of type 
"sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2". Instances of 
this class do not seem to have the "dbapi" attribute, only the class itself 
has it. Should I be passing the class and not an instance for the "dialect" 
option? I've tried that and got a different kind of error:

  File 
"//anaconda/envs/opp/lib/python3.6/site-packages/sqlalchemy/pool.py", line 
812, in _checkout
result = pool._dialect.do_ping(fairy.connection)
TypeError: do_ping() missing 1 required positional argument: 
'dbapi_connection'

Any help would be appreciated, thanks.

André

-- 
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] Discriminator on association

[Edu Ferreira]

It works. Thanks.

Em segunda-feira, 5 de fevereiro de 2018 14:29:29 UTC-2, Mike Bayer 
escreveu:
>
> On Mon, Feb 5, 2018 at 8:40 AM, Edu Ferreira  > wrote: 
> > Hello, i implemented a generic foreign key/relationship, using this 
> example: 
> > 
> http://docs.sqlalchemy.org/en/latest/_modules/examples/generic_associations/discriminator_on_association.html
>  
> > 
> > In the example addresses is passing in constructor: 
> > 
> > session.add_all([ 
> > Customer( 
> > name='customer 1', 
> > addresses=[ 
> > Address( 
> > street='123 anywhere street', 
> > city="New York", 
> > zip="10110"), 
> > Address( 
> > street='40 main street', 
> > city="San Francisco", 
> > zip="95732") 
> > ] 
> > ), 
> > Supplier( 
> > company_name="Ace Hammers", 
> > addresses=[ 
> > Address( 
> > street='2569 west elm', 
> > city="Detroit", 
> > zip="56785") 
> > ] 
> > ), 
> > ]) 
> > 
> > 
> > 
> > But if i try to do something like this: 
> > 
> > _cust = Customer('name') 
> > session.add(_cust) 
> > session.commit() 
> > 
> > #And After try to use "addresses" 
> > 
> > _cust.addresses.append(Address(street='2569 west elm',city="Detroit", 
> > zip="56785")) 
> > #It doens't work, "addresses" is None. 
> > 
> > 
> > Is there any way to use this without pass "addresses" in the 
> constructor? 
> > I'm using mysql and sqlachemy version is '1.1.15'. 
>
> the proxy is being used slightly oddly here, in that 
> Customer.address_association is a scalar but then 
> Customer.address_association.addresses is a list.  The proxy isn't 
> smart enough to figure out that it needs to initialize a new 
> CustomerAddressAssociation when you say something like 
> customer.addresses.append.  So you need to set one up by assigning 
> first:  customer.addresses = [], now there is a 
> customer.address_association set up and append() will work .You 
> can do this assignment in the constructor of Customer if you want. 
>
> I never use this style, I always use table_per_related or 
> table_per_association. 
>
>
>
>
>
> > 
> > -- 
> > 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.