Re: [sqlalchemy] SELECT ... INTO Contribution

[Mike Bayer]

On Tue, Nov 6, 2018 at 10:33 PM Paul Becotte  wrote:
>
> Hi!
>
> I recently needed to use the select ... into ... construct to build a temp 
> table in Redshift. I used the recipe 
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SelectInto here to 
> get a good idea of how to go about this, and was wondering if I should 
> contribute the final working version back to the project. The biggest thing 
> that gives me pause is that the recipe says that this would be added in 2014, 
> and it hasn't been, so I feel like you probably have a good reason that I am 
> ignorant about! The other, less of a blocker, is that I am not actually sure 
> of a comprehensive list of database engines that support don't support this- 
> I know Postgres, Snowflake, Redshift, and MSSQL do... but I am out of 
> expertise at that point. For something like this that may or may not be 
> supported in all database engines, are there good examples of how that should 
> be handled in the existing code base?

We have INSERT FROM SELECT, which is kind of where that went.  The
difference between these two constructs is that one of them creates
the table automatically as well.   But SQLAlchemy can already automate
the creation of the table since it can generate CREATE TABLE
statements as easily as it generates the INSERT, so the urgency of
SELECT INTO has never been realized.This SO answer tries to get at
what might be nicer about SELECT INTO:
https://stackoverflow.com/a/6948075 .As far as adding it, it's
probably syntactially a lot simpler than INSERT FROM SELECT to render,
but from a SQLAlchemy perspective, it's awkward, because we've just
run SELECT INTO.  Now we want to get a Table back for that.  So...we
have to figure that out from the select() that we gave it and create a
copy, also is this Table associated with a MetaData()?  stuff like
that. As far as where it goes, if it's in the SQL standard it can
be part of the non-dialect-specific SQL code which I think is the case
here though we should check.


>
> --
> 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] SELECT ... INTO Contribution

[Paul Becotte]

Hi!

I recently needed to use the select ... into ... construct to build a temp 
table in Redshift. I used the 
recipe https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SelectInto 
here to get a good idea of how to go about this, and was wondering if I 
should contribute the final working version back to the project. The 
biggest thing that gives me pause is that the recipe says that this would 
be added in 2014, and it hasn't been, so I feel like you probably have a 
good reason that I am ignorant about! The other, less of a blocker, is that 
I am not actually sure of a comprehensive list of database engines that 
support don't support this- I know Postgres, Snowflake, Redshift, and MSSQL 
do... but I am out of expertise at that point. For something like this that 
may or may not be supported in all database engines, are there good 
examples of how that should be handled in the existing code base?

-- 
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] Custom type compilers interplace with with_variant

[Mike Bayer]

On Tue, Nov 6, 2018 at 6:11 PM 'Van Klaveren, Brian N.' via sqlalchemy
 wrote:
>
> Hi,
>
> I want to create a custom type for TINYINT and DOUBLE.
>
> I've defined them as custom types.
>
> I want to use with_variant for them, so that in sqlite they print out as 
> TINYINT and DOUBLE.
>
> But I also want them to use the variants defined for other databases, like 
> Oracle and Postgres.
>
> The first part works fine, the second part, not so much.
>
> What's really the best way to do this?
>
> Code is attached below.
>
> Brian
>
>
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy import SmallInteger, Float
> from sqlalchemy import types
> from sqlalchemy.dialects import mysql, oracle, postgresql
> from sqlalchemy import create_engine, MetaData, Column, Boolean
> from sqlalchemy.schema import Table
>
> MYSQL = "mysql"
> ORACLE = "oracle"
> POSTGRES = "postgresql"
> SQLITE = "sqlite"
>
> class TINYINT(SmallInteger):
> """The non-standard TINYINT type."""
> __visit_name__ = 'TINYINT'
>
>
> class DOUBLE(Float):
> """The non-standard DOUBLE type."""
> __visit_name__ = 'DOUBLE'
>
> @compiles(TINYINT)
> def compile_tinyint(type_, compiler, **kw):
> return "TINYINT"
>
>
> @compiles(DOUBLE)
> def compile_double(type_, compiler, **kw):
> return "DOUBLE"
>
> byte_map = {
> MYSQL: mysql.TINYINT(),
> ORACLE: oracle.NUMBER(3),
> POSTGRES: postgresql.SMALLINT(),
> }
>
> double_map = {
> MYSQL: mysql.DOUBLE(),
> ORACLE: oracle.BINARY_DOUBLE(),
> POSTGRES: postgresql.DOUBLE_PRECISION(),
> }
>
> def byte(**kwargs):
> return _vary(TINYINT(), byte_map, kwargs)
>
>
> def double(**kwargs):
> return _vary(DOUBLE(), double_map, kwargs)
>
> def _vary(type, variant_map, overrides):
> for dialect, variant in overrides.items():
> variant_map[dialect] = variant
> for dialect, variant in variant_map.items():
> type.with_variant(variant, dialect)

I think you need to reassign to type there, "type = type.with_variant(...)"

> return type
>
>
> metadata = MetaData()
>
> t = Table("MyTable", metadata,
>   Column("my_byte", byte()),
>   Column("my_double", double())
>   )
>
>
> def metadata_dump(sql, *multiparams, **params):
> # print or write to log or file etc
> print(sql.compile(dialect=engine.dialect))
>
> engine = create_engine("sqlite://", strategy='mock', executor=metadata_dump)
> metadata.create_all(engine)
>
>
> def metadata_dump(sql, *multiparams, **params):
> # print or write to log or file etc
> print(sql.compile(dialect=engine.dialect))
>
> engine = create_engine("oracle://", strategy='mock', executor=metadata_dump)
> metadata.create_all(engine)
>
>
> --
> 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] Custom type compilers interplace with with_variant

['Van Klaveren, Brian N.' via sqlalchemy]

Hi,

I want to create a custom type for TINYINT and DOUBLE.

I've defined them as custom types. 

I want to use with_variant for them, so that in sqlite they print out as 
TINYINT and DOUBLE. 

But I also want them to use the variants defined for other databases, like 
Oracle and Postgres.

The first part works fine, the second part, not so much.

What's really the best way to do this?

Code is attached below.

Brian


from sqlalchemy.ext.compiler import compiles
from sqlalchemy import SmallInteger, Float
from sqlalchemy import types
from sqlalchemy.dialects import mysql, oracle, postgresql
from sqlalchemy import create_engine, MetaData, Column, Boolean
from sqlalchemy.schema import Table

MYSQL = "mysql"
ORACLE = "oracle"
POSTGRES = "postgresql"
SQLITE = "sqlite"

class TINYINT(SmallInteger):
"""The non-standard TINYINT type."""
__visit_name__ = 'TINYINT'


class DOUBLE(Float):
"""The non-standard DOUBLE type."""
__visit_name__ = 'DOUBLE'

@compiles(TINYINT)
def compile_tinyint(type_, compiler, **kw):
return "TINYINT"


@compiles(DOUBLE)
def compile_double(type_, compiler, **kw):
return "DOUBLE"

byte_map = {
MYSQL: mysql.TINYINT(),
ORACLE: oracle.NUMBER(3),
POSTGRES: postgresql.SMALLINT(),
}

double_map = {
MYSQL: mysql.DOUBLE(),
ORACLE: oracle.BINARY_DOUBLE(),
POSTGRES: postgresql.DOUBLE_PRECISION(),
}

def byte(**kwargs):
return _vary(TINYINT(), byte_map, kwargs)


def double(**kwargs):
return _vary(DOUBLE(), double_map, kwargs)

def _vary(type, variant_map, overrides):
for dialect, variant in overrides.items():
variant_map[dialect] = variant
for dialect, variant in variant_map.items():
type.with_variant(variant, dialect)
return type


metadata = MetaData()

t = Table("MyTable", metadata,
  Column("my_byte", byte()),
  Column("my_double", double())
  )


def metadata_dump(sql, *multiparams, **params):
# print or write to log or file etc
print(sql.compile(dialect=engine.dialect))

engine = create_engine("sqlite://", strategy='mock', executor=metadata_dump)
metadata.create_all(engine)


def metadata_dump(sql, *multiparams, **params):
# print or write to log or file etc
print(sql.compile(dialect=engine.dialect))

engine = create_engine("oracle://", strategy='mock', executor=metadata_dump)
metadata.create_all(engine)


-- 
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] How to handle unique constraint for multiple columns containing null values using sqlachemy?

[Mike Bayer]

I should add, please specify which backend you're using and what DDL
sequence you are looking to emit.


On Tue, Nov 6, 2018 at 3:21 PM Mike Bayer  wrote:
>
> On Tue, Nov 6, 2018 at 2:54 PM  wrote:
> >
> > Hi,
> >
> > I find that `UniqueConstraint` from `sqlachemy` and 
> > `op.create_unique_constraint` by `alembic`, it cannot handle multiple 
> > columns that contain null values.
> >
> > I saw we can solve using  SQL commands via some tricky ways like creating 
> > unique index and coalesce, do we have some ways using `sqlachemy` instead 
> > of directly using `SQL` to solve this issue?
>
>
> the UniqueConstraint object spits out "CREATE UNIQUE CONSTRAINT" or
> equivalent DDL only. The "null values" issue is very different
> across backends - in particular, the limitation that NULL values
> aren't allowed is a peculiarity specific to Microsoft SQL Server.
>
> Given that, there are ways to create DDL that does what you need, and
> then if necessary, to have different kinds of DDL emit based on
> different backends if you are looking for cross-database
> compatibility.   For intricate edge cases like this, it's much better
> that SQLAlchemy gives the developer the tools to create exactly the
> DDL sequence that is tailored towards their needs, rather than it
> trying to pre-package a particular solution that probably won't work
> the same way across different backends or otherwise be flexible
> enough.
>
>
> >
> > Thanks!
> >
> > Lydia
> >
> > --
> > 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.

Re: [sqlalchemy] How to handle unique constraint for multiple columns containing null values using sqlachemy?

[Mike Bayer]

On Tue, Nov 6, 2018 at 2:54 PM  wrote:
>
> Hi,
>
> I find that `UniqueConstraint` from `sqlachemy` and 
> `op.create_unique_constraint` by `alembic`, it cannot handle multiple columns 
> that contain null values.
>
> I saw we can solve using  SQL commands via some tricky ways like creating 
> unique index and coalesce, do we have some ways using `sqlachemy` instead of 
> directly using `SQL` to solve this issue?


the UniqueConstraint object spits out "CREATE UNIQUE CONSTRAINT" or
equivalent DDL only. The "null values" issue is very different
across backends - in particular, the limitation that NULL values
aren't allowed is a peculiarity specific to Microsoft SQL Server.

Given that, there are ways to create DDL that does what you need, and
then if necessary, to have different kinds of DDL emit based on
different backends if you are looking for cross-database
compatibility.   For intricate edge cases like this, it's much better
that SQLAlchemy gives the developer the tools to create exactly the
DDL sequence that is tailored towards their needs, rather than it
trying to pre-package a particular solution that probably won't work
the same way across different backends or otherwise be flexible
enough.


>
> Thanks!
>
> Lydia
>
> --
> 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.

Re: [sqlalchemy] How to bind all class associated with an engine ?

[Mike Bayer]

I've updated the documentation and in a few hours it should be up at
https://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html#simple-vertical-partitioning
On Tue, Nov 6, 2018 at 3:04 PM  wrote:
>
> Oh, it's perfect for me if I can use bases for bindings.
>
> Thank you
>
> Le mardi 6 novembre 2018 21:27:19 UTC+2, Mike Bayer a écrit :
>>
>> On Tue, Nov 6, 2018 at 2:20 PM  wrote:
>> >
>> > I have many classes, so it's seems a better idea to use something like 
>> > that (but I don't know what exactly is this registry, and why it's a 
>> > WeakRef dict) :
>> >
>> > binding = {cls: engine for cls in Base._decl_class_registry.values()}
>>
>>
>> see I don't want you to use that because it's a private variable.  You
>> should make your own "registry" as you do the automap.
>>
>>
>> >
>> > If not, maybe I can use the second approach, overloading Session, but is 
>> > this safe ?
>> >
>> > class BindSession(Session):
>> > def get_bind(self, mapper=None, clause=None):
>> > if mapper:
>> > if issubclass(mapper.class_, SomeBase):
>> > return some_engine
>> > elif issubclass(mapper.class_, OtherBase):
>> > return other_engine
>>
>> oh is it based on two different bases?   Guess what, you can use those
>> base classes:
>>
>> session = Session(binds={SomeBase: engine1, OtherBase: engine2})
>>
>> docs seem to not be in-depth enough or adequately cross-linked, will fix
>>
>>
>>
>> ># correct ?
>> >return super().get_bind(mapper,clause)
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > Le mardi 6 novembre 2018 18:07:48 UTC+2, Mike Bayer a écrit :
>> >>
>> >> On Tue, Nov 6, 2018 at 9:41 AM  wrote:
>> >> >
>> >> > Hi,
>> >> >
>> >> > I'm using a schema with multiple DB, like :
>> >> >
>> >> > engine1 = 
>> >> > create_engine("mysql://localhost/{db}?charset=utf8".format(db=db1))
>> >> > engine2 = 
>> >> > create_engine("mysql://localhost/{db}?charset=utf8".format(db=db2))
>> >> >
>> >> > Then I use automap and reflection to generate mapping of all classes 
>> >> > needed.
>> >> >
>> >> > Sometime, I want to use classes from both databases, but I cannot bind 
>> >> > my session directly with two engines.
>> >> >
>> >> > I noticed that I can use the Session `binds` argument to decide which 
>> >> > class to bind to some engine, but I have many classes to include here, 
>> >> > and this is somewhat error prone.
>> >> >
>> >> > Is there any solution to automate the binding definition against each 
>> >> > class ? Or to bind a session directly to multiple engines ?
>> >>
>> >> you would use the "binds" argument as you saw in
>> >> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.binds.
>> >>
>> >> as you noticed though, you have to tell the Session which classes map
>> >> to which engine.   So when you use automap, each time you reflect()
>> >> for a particular engine, you need to gather all the classes that were
>> >> generated for that call and add them to a dictionary, which you can
>> >> then pass to session.binds.
>> >>
>> >> If you have some totally other way to tell the Session, given a class,
>> >> which engine to use, you can also make your own method to do whatever
>> >> you want and override it, by overriding get_bind:
>> >> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.get_bind
>> >> .you probably don't need to do it this way but there's an example
>> >> of how that looks at
>> >> http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/.
>> >>
>> >>
>> >>
>> >> >
>> >> > Thank you
>> >> >
>> >> >
>> >> > --
>> >> > 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+...@googlegroups.com.
>> > To 

Re: [sqlalchemy] How to bind all class associated with an engine ?

[yoch . melka]

Oh, it's perfect for me if I can use bases for bindings.

Thank you

Le mardi 6 novembre 2018 21:27:19 UTC+2, Mike Bayer a écrit :
>
> On Tue, Nov 6, 2018 at 2:20 PM > wrote: 
> > 
> > I have many classes, so it's seems a better idea to use something like 
> that (but I don't know what exactly is this registry, and why it's a 
> WeakRef dict) : 
> > 
> > binding = {cls: engine for cls in Base._decl_class_registry.values()} 
>
>
> see I don't want you to use that because it's a private variable.  You 
> should make your own "registry" as you do the automap. 
>
>
> > 
> > If not, maybe I can use the second approach, overloading Session, but is 
> this safe ? 
> > 
> > class BindSession(Session): 
> > def get_bind(self, mapper=None, clause=None): 
> > if mapper: 
> > if issubclass(mapper.class_, SomeBase): 
> > return some_engine 
> > elif issubclass(mapper.class_, OtherBase): 
> > return other_engine 
>
> oh is it based on two different bases?   Guess what, you can use those 
> base classes: 
>
> session = Session(binds={SomeBase: engine1, OtherBase: engine2}) 
>
> docs seem to not be in-depth enough or adequately cross-linked, will fix 
>
>
>
> ># correct ? 
> >return super().get_bind(mapper,clause) 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > Le mardi 6 novembre 2018 18:07:48 UTC+2, Mike Bayer a écrit : 
> >> 
> >> On Tue, Nov 6, 2018 at 9:41 AM  wrote: 
> >> > 
> >> > Hi, 
> >> > 
> >> > I'm using a schema with multiple DB, like : 
> >> > 
> >> > engine1 = 
> create_engine("mysql://localhost/{db}?charset=utf8".format(db=db1)) 
> >> > engine2 = 
> create_engine("mysql://localhost/{db}?charset=utf8".format(db=db2)) 
> >> > 
> >> > Then I use automap and reflection to generate mapping of all classes 
> needed. 
> >> > 
> >> > Sometime, I want to use classes from both databases, but I cannot 
> bind my session directly with two engines. 
> >> > 
> >> > I noticed that I can use the Session `binds` argument to decide which 
> class to bind to some engine, but I have many classes to include here, and 
> this is somewhat error prone. 
> >> > 
> >> > Is there any solution to automate the binding definition against each 
> class ? Or to bind a session directly to multiple engines ? 
> >> 
> >> you would use the "binds" argument as you saw in 
> >> 
> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.binds.
>  
>
> >> 
> >> as you noticed though, you have to tell the Session which classes map 
> >> to which engine.   So when you use automap, each time you reflect() 
> >> for a particular engine, you need to gather all the classes that were 
> >> generated for that call and add them to a dictionary, which you can 
> >> then pass to session.binds. 
> >> 
> >> If you have some totally other way to tell the Session, given a class, 
> >> which engine to use, you can also make your own method to do whatever 
> >> you want and override it, by overriding get_bind: 
> >> 
> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.get_bind
>  
> >> .you probably don't need to do it this way but there's an example 
> >> of how that looks at 
> >> 
> http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/.
>  
>
> >> 
> >> 
> >> 
> >> > 
> >> > Thank you 
> >> > 
> >> > 
> >> > -- 
> >> > 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+...@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

[sqlalchemy] How to handle unique constraint for multiple columns containing null values using sqlachemy?

[lydia]

Hi,

I find that `UniqueConstraint` from `sqlachemy` and 
`op.create_unique_constraint` by `alembic`, it cannot handle multiple 
columns that contain null values.

I saw we can solve using  SQL commands via some tricky ways like creating 
unique index and coalesce, do we have some ways using `sqlachemy` instead 
of directly using `SQL` to solve this issue?

Thanks!

Lydia

-- 
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] How to bind all class associated with an engine ?

[Mike Bayer]

On Tue, Nov 6, 2018 at 2:20 PM  wrote:
>
> I have many classes, so it's seems a better idea to use something like that 
> (but I don't know what exactly is this registry, and why it's a WeakRef dict) 
> :
>
> binding = {cls: engine for cls in Base._decl_class_registry.values()}


see I don't want you to use that because it's a private variable.  You
should make your own "registry" as you do the automap.


>
> If not, maybe I can use the second approach, overloading Session, but is this 
> safe ?
>
> class BindSession(Session):
> def get_bind(self, mapper=None, clause=None):
> if mapper:
> if issubclass(mapper.class_, SomeBase):
> return some_engine
> elif issubclass(mapper.class_, OtherBase):
> return other_engine

oh is it based on two different bases?   Guess what, you can use those
base classes:

session = Session(binds={SomeBase: engine1, OtherBase: engine2})

docs seem to not be in-depth enough or adequately cross-linked, will fix



># correct ?
>return super().get_bind(mapper,clause)
>
>
>
>
>
>
>
>
> Le mardi 6 novembre 2018 18:07:48 UTC+2, Mike Bayer a écrit :
>>
>> On Tue, Nov 6, 2018 at 9:41 AM  wrote:
>> >
>> > Hi,
>> >
>> > I'm using a schema with multiple DB, like :
>> >
>> > engine1 = 
>> > create_engine("mysql://localhost/{db}?charset=utf8".format(db=db1))
>> > engine2 = 
>> > create_engine("mysql://localhost/{db}?charset=utf8".format(db=db2))
>> >
>> > Then I use automap and reflection to generate mapping of all classes 
>> > needed.
>> >
>> > Sometime, I want to use classes from both databases, but I cannot bind my 
>> > session directly with two engines.
>> >
>> > I noticed that I can use the Session `binds` argument to decide which 
>> > class to bind to some engine, but I have many classes to include here, and 
>> > this is somewhat error prone.
>> >
>> > Is there any solution to automate the binding definition against each 
>> > class ? Or to bind a session directly to multiple engines ?
>>
>> you would use the "binds" argument as you saw in
>> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.binds.
>>
>> as you noticed though, you have to tell the Session which classes map
>> to which engine.   So when you use automap, each time you reflect()
>> for a particular engine, you need to gather all the classes that were
>> generated for that call and add them to a dictionary, which you can
>> then pass to session.binds.
>>
>> If you have some totally other way to tell the Session, given a class,
>> which engine to use, you can also make your own method to do whatever
>> you want and override it, by overriding get_bind:
>> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.get_bind
>> .you probably don't need to do it this way but there's an example
>> of how that looks at
>> http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/.
>>
>>
>>
>> >
>> > Thank you
>> >
>> >
>> > --
>> > 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  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 

Re: [sqlalchemy] How to bind all class associated with an engine ?

[yoch . melka]

I have many classes, so it's seems a better idea to use something like that 
(but I don't know what exactly is this registry, and why it's a WeakRef 
dict) :

binding = {cls: engine for cls in Base._decl_class_registry.values()}

If not, maybe I can use the second approach, overloading Session, but is 
this safe ?

class BindSession(Session):
def get_bind(self, mapper=None, clause=None):
if mapper:
if issubclass(mapper.class_, SomeBase):
return some_engine
elif issubclass(mapper.class_, OtherBase):
return other_engine
   # correct ?
   return super().get_bind(mapper,clause)








Le mardi 6 novembre 2018 18:07:48 UTC+2, Mike Bayer a écrit :
>
> On Tue, Nov 6, 2018 at 9:41 AM > wrote: 
> > 
> > Hi, 
> > 
> > I'm using a schema with multiple DB, like : 
> > 
> > engine1 = 
> create_engine("mysql://localhost/{db}?charset=utf8".format(db=db1)) 
> > engine2 = 
> create_engine("mysql://localhost/{db}?charset=utf8".format(db=db2)) 
> > 
> > Then I use automap and reflection to generate mapping of all classes 
> needed. 
> > 
> > Sometime, I want to use classes from both databases, but I cannot bind 
> my session directly with two engines. 
> > 
> > I noticed that I can use the Session `binds` argument to decide which 
> class to bind to some engine, but I have many classes to include here, and 
> this is somewhat error prone. 
> > 
> > Is there any solution to automate the binding definition against each 
> class ? Or to bind a session directly to multiple engines ? 
>
> you would use the "binds" argument as you saw in 
>
> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.binds.
>  
>
>
> as you noticed though, you have to tell the Session which classes map 
> to which engine.   So when you use automap, each time you reflect() 
> for a particular engine, you need to gather all the classes that were 
> generated for that call and add them to a dictionary, which you can 
> then pass to session.binds. 
>
> If you have some totally other way to tell the Session, given a class, 
> which engine to use, you can also make your own method to do whatever 
> you want and override it, by overriding get_bind: 
>
> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.get_bind
>  
> .you probably don't need to do it this way but there's an example 
> of how that looks at 
>
> http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/.
>  
>
>
>
>
> > 
> > Thank you 
> > 
> > 
> > -- 
> > 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.

Re: [sqlalchemy] How to bind all class associated with an engine ?

[Mike Bayer]

On Tue, Nov 6, 2018 at 9:41 AM  wrote:
>
> Hi,
>
> I'm using a schema with multiple DB, like :
>
> engine1 = create_engine("mysql://localhost/{db}?charset=utf8".format(db=db1))
> engine2 = create_engine("mysql://localhost/{db}?charset=utf8".format(db=db2))
>
> Then I use automap and reflection to generate mapping of all classes needed.
>
> Sometime, I want to use classes from both databases, but I cannot bind my 
> session directly with two engines.
>
> I noticed that I can use the Session `binds` argument to decide which class 
> to bind to some engine, but I have many classes to include here, and this is 
> somewhat error prone.
>
> Is there any solution to automate the binding definition against each class ? 
> Or to bind a session directly to multiple engines ?

you would use the "binds" argument as you saw in
https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.binds.

as you noticed though, you have to tell the Session which classes map
to which engine.   So when you use automap, each time you reflect()
for a particular engine, you need to gather all the classes that were
generated for that call and add them to a dictionary, which you can
then pass to session.binds.

If you have some totally other way to tell the Session, given a class,
which engine to use, you can also make your own method to do whatever
you want and override it, by overriding get_bind:
https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.get_bind
.you probably don't need to do it this way but there's an example
of how that looks at
http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/.



>
> Thank you
>
>
> --
> 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] How to bind all class associated with an engine ?

[yoch . melka]

Hi,

I'm using a schema with multiple DB, like :

engine1 = create_engine("mysql://localhost/{db}?charset=utf8".format(db=db1
))
engine2 = create_engine("mysql://localhost/{db}?charset=utf8".format(db=db2
))

Then I use automap and reflection to generate mapping of all classes needed.

Sometime, I want to use classes from both databases, but I cannot bind my 
session directly with two engines.

I noticed that I can use the Session `binds` argument to decide which class 
to bind to some engine, but I have many classes to include here, and this 
is somewhat error prone. 

Is there any solution to automate the binding definition against each class 
? Or to bind a session directly to multiple engines ?

Thank you


-- 
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: alembic vs system=True columns

[Riccardo Magliocchetti]

Thanks a lot Mike!

On Wednesday, October 31, 2018 at 7:43:51 PM UTC+1, Mike Bayer wrote:

> here's the issue: 
>
>
> https://bitbucket.org/zzzeek/alembic/issues/515/system-true-not-generated-for-column
>  
> 
>  
>
> and here is a patch to resolve: 
>
> https://gerrit.sqlalchemy.org/#/c/zzzeek/alembic/+/910 
>
> On Wed, Oct 31, 2018 at 11:49 AM Riccardo Magliocchetti 
> > wrote: 
> > 
> > Il 31/10/18 16:32, Mike Bayer ha scritto: 
> > > On Wed, Oct 31, 2018 at 11:00 AM Riccardo Magliocchetti 
> > > > wrote: 
> > >> 
> > >> Hello, 
> > >> 
> > >> we'd like to use Postgresql xmin column for optimistic concurrency, 
> so we declared the column as: 
> > >> 
> > >>  xmin = Column("xmin", Integer, system=True, 
> server_default=FetchedValue()) 
> > >> 
> > >> 
> > >> The problem is when autogenerating a migration with alembic (via 
> flask-migrate) the xmin column is added to 
> > >> the operations so the migrations fails. 
> > > 
> > > is the column generated inside the migration including the 
> > > "system=True" flag?   if that flag is present then the column won't be 
> > > created.Or are you referring to an alter table operation?Need 
> > > specifics on the failure and then we need to post a bug report if 
> > > there is in fact a problem. 
> > 
> > It's a create table, the flag is not there: 
> > 
> >  op.create_table('user', 
> >   ... 
> >  sa.Column('xmin', sa.Integer(), server_default=FetchedValue(), 
> nullable=True), 
> > 
> > 
> > 
> > -- 
> > Riccardo Magliocchetti 
> > @rmistaken 
> > 
> > http://menodizero.it 
> > 
> > -- 
> > You received this message because you are subscribed to the Google 
> Groups "sqlalchemy-alembic" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com . 
>
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.