Re: [sqlalchemy] SQLAlchemy 1.0.12 use inner JOIN instead of LEFT OUTER JOIN

[Mike Bayer]

On 04/26/2016 12:22 PM, Alex Dev wrote:

These two work. However I think that I cannot do that in my real query
because it is a bit more complex. For the sake of simplifying my
question, I reduced the query to the minimum but I realize it is now
hard so see why I was using contains_eager or joinedload in different
parts of the query. Indeed, I need to do more joins in the real query
(see below).

I can have the expected result by adding the |innerjoin=False| but you
wrote that this is a wrong usage so I wonder what a correct usage would
be in my real case.

|
# -*- coding: utf-8 -*-
fromsqlalchemy import*
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm import*


Base=declarative_base()

_plant_table =Table('plant',Base.metadata,
Column('id',Integer,primary_key=True)
)

_dimensionsseriestype_table =Table('dimensionsseriestype',Base.metadata,
Column('id',Integer,primary_key=True),
Column('sortindex',Integer),
Column('designation',String),
)

_plant_dimensionsseries_table =Table('plant_dimensionsseries',Base.metadata,
Column('plant_id',Integer,primary_key=True),
Column('dimensionsseriestype_id',Integer,primary_key=True),
ForeignKeyConstraint(['plant_id'],['plant.id']),
ForeignKeyConstraint(['dimensionsseriestype_id'],['dimensionsseriestype.id']),
)

_view_plant_dimensionsseries_table
=Table('view_plant_dimensionsseries',Base.metadata,
Column('plant_id',Integer,primary_key=True),
Column('dimensionsseriestype_id',Integer,primary_key=True),
ForeignKeyConstraint(
['plant_id','dimensionsseriestype_id'],
['plant_dimensionsseries.plant_id','plant_dimensionsseries.dimensionsseriestype_id'])
)

classPlant(Base):
 __table__ =_plant_table

classDimensionsseriestype(Base):
 __table__ =_dimensionsseriestype_table
 _id =__table__.c.id

classPlantDimensionsseries(Base):
 __table__ =_plant_dimensionsseries_table
 _plant_id =__table__.c.plant_id
 _dimensionsseriestype_id =__table__.c.dimensionsseriestype_id

 plant =relationship('Plant',
 innerjoin=True,
 backref=backref('plant_dimensionsseries'))

 dimensionsseriestype =relationship('Dimensionsseriestype',
 innerjoin=True,
 backref=backref('plant_dimensionsseries'))

classPlantDimensionsseriesDataComputed(Base):
 __table__ =_view_plant_dimensionsseries_table
 _plant_id =__table__.c.plant_id
 _dimensionsseriestype_id =__table__.c.dimensionsseriestype_id

# One-to-one relationship
 dimensionsseries =relationship('PlantDimensionsseries',
 innerjoin=True,
 backref=backref('data_computed',
 innerjoin=True))

if__name__ =='__main__':

 engine
=create_engine('postgresql://nurseryacme_employee@localhost:5432/nurseryacme')
Session=sessionmaker(bind=engine)
 session =Session()

# real query (almost)
printsession.query(Plant).\
 outerjoin(Plant.plant_dimensionsseries).\

outerjoin(Dimensionsseriestype,PlantDimensionsseries.dimensionsseriestype).\

options(contains_eager(Plant.plant_dimensionsseries,PlantDimensionsseries.dimensionsseriestype)).\

options(joinedload(Plant.plant_dimensionsseries,PlantDimensionsseries.data_computed)).\
 order_by(Dimensionsseriestype.sortindex)


if you're looking to joinedload() just 
PlantDimentionssseries.data_computed and have the contains_eager() take 
effect for the Plant.plant_dimensionseries part, and the issue is that 
you want this to be two separate sets of options, you can do this:


options(contains_eager(Plant.plant_dimensionsseries)).
options(defaultload(Plant.plant_dimensionseries).joinedload(PlantDimensionsSeries.data_computed, 
innerjoin=False))


if you need to use contains_eager() for one segment and joinedload() for 
the second, then you need to give it innerjoin=False, this won't be 
auto-detected.







|

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


--
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] SQLAlchemy 1.0.12 use inner JOIN instead of LEFT OUTER JOIN

[Alex Dev]

Thank you for your quick answer Mike.

Le mardi 26 avril 2016 00:28:10 UTC+2, Mike Bayer a écrit :
>
>
>
> well this usage above is wrong.  You can't have contains_eager() and 
> joinedload() along the same paths at the same time like that.   Also, 
> chaining joinedload() from contains_eager() is not a very typical thing 
> to do, it works, but joinedload() does not coordinate with 
> contains_eager() in any way, and it has no idea that you are using 
> outerjoin() to the left of it. 
>
> The two correct ways to do this are: 
>
>  print session.query(Plant).\ 
>  join(Plant.plant_dimensionsseries).\ 
>   
> options(contains_eager(Plant.plant_dimensionsseries).joinedload(PlantDimensionsseries.data_computed,
>  
>
> )) 
>

I cannot use a inner JOIN between plant and plant_dimensionsseries because 
a plant does not necessarily have any corresponding plant_dimensionsseries 
so give me different result. 
 

>
> and 
>
>  print session.query(Plant).\ 
>  outerjoin(Plant.plant_dimensionsseries).\ 
>   
> options(contains_eager(Plant.plant_dimensionsseries).joinedload(PlantDimensionsseries.data_computed,
>  
>
> innerjoin=False)) 
>

>
> or of course: 
>
>
>  print session.query(Plant).\ 
>  outerjoin(Plant.plant_dimensionsseries).\ 
>   
> options(joinedload(Plant.plant_dimensionsseries).joinedload(PlantDimensionsseries.data_computed))
>  
>
>
 
These two work. However I think that I cannot do that in my real query 
because it is a bit more complex. For the sake of simplifying my question, 
I reduced the query to the minimum but I realize it is now hard so see why 
I was using contains_eager or joinedload in different parts of the query. 
Indeed, I need to do more joins in the real query (see below).

I can have the expected result by adding the innerjoin=False but you wrote 
that this is a wrong usage so I wonder what a correct usage would be in my 
real case.

# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *


Base = declarative_base()

_plant_table = Table('plant', Base.metadata,
Column('id', Integer, primary_key=True)
)

_dimensionsseriestype_table = Table('dimensionsseriestype', Base.metadata,
Column('id', Integer, primary_key=True),
Column('sortindex', Integer),
Column('designation', String),
)

_plant_dimensionsseries_table = Table('plant_dimensionsseries', Base.
metadata,
Column('plant_id', Integer, primary_key=True),
Column('dimensionsseriestype_id', Integer, primary_key=True),
ForeignKeyConstraint(['plant_id'], ['plant.id']),
ForeignKeyConstraint(['dimensionsseriestype_id'], [
'dimensionsseriestype.id']),
)

_view_plant_dimensionsseries_table = Table('view_plant_dimensionsseries', 
Base.metadata,
Column('plant_id', Integer, primary_key=True),
Column('dimensionsseriestype_id', Integer, primary_key=True),
ForeignKeyConstraint(
['plant_id', 'dimensionsseriestype_id'],
['plant_dimensionsseries.plant_id', 
'plant_dimensionsseries.dimensionsseriestype_id'])
)

class Plant(Base):
__table__ = _plant_table

class Dimensionsseriestype(Base):
__table__ = _dimensionsseriestype_table
_id = __table__.c.id

class PlantDimensionsseries(Base):
__table__ = _plant_dimensionsseries_table
_plant_id = __table__.c.plant_id
_dimensionsseriestype_id = __table__.c.dimensionsseriestype_id

plant = relationship('Plant',
innerjoin=True,
backref=backref('plant_dimensionsseries'))

dimensionsseriestype = relationship('Dimensionsseriestype',
innerjoin=True,
backref=backref('plant_dimensionsseries'))

class PlantDimensionsseriesDataComputed(Base):
__table__ = _view_plant_dimensionsseries_table
_plant_id = __table__.c.plant_id
_dimensionsseriestype_id = __table__.c.dimensionsseriestype_id

# One-to-one relationship
dimensionsseries = relationship('PlantDimensionsseries',
innerjoin=True,
backref=backref('data_computed',
innerjoin=True))

if __name__ == '__main__':

engine = create_engine(
'postgresql://nurseryacme_employee@localhost:5432/nurseryacme')
Session = sessionmaker(bind=engine)
session = Session()

# real query (almost)
print session.query(Plant).\
outerjoin(Plant.plant_dimensionsseries).\
outerjoin(Dimensionsseriestype, PlantDimensionsseries.
dimensionsseriestype).\
options(contains_eager(Plant.plant_dimensionsseries, 
PlantDimensionsseries.dimensionsseriestype)).\
options(joinedload(Plant.plant_dimensionsseries, 
PlantDimensionsseries.data_computed)).\
order_by(Dimensionsseriestype.sortindex)


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

Re: [sqlalchemy] Bug in BufferedColumnResultProxy class?

[Piotr Dobrogost]

On Tue, Apr 26, 2016 at 12:18 AM, Mike Bayer  wrote:
>
> On 04/25/2016 11:04 AM, Piotr Dobrogost wrote:
>>
>> Is caching using dogpile what you call "Query cache extension"? If so
>> we don't use it.
>
> this extension:
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/baked.html?highlight=baked#module-sqlalchemy.ext.baked

Ok. I see this being used in Kotti (the framework I use). For example
here 
https://github.com/Kotti/Kotti/blob/0d162332e369dedb1b4936935e43de89e9665f8e/kotti/resources.py#L812

> the stack trace shown here illustrates an INSERT statement in the context of
> an ORM flush, which does use the compiled_cache feature; so to that degree,
> this part of the puzzle makes sense.

I'm curious what in the log shows that we deal with "INSERT statement
in the context of an ORM flush"?

> However, in cx_oracle, an INSERT statement does not return rows there's no
> data to be returned; cursor.description should be None and the
> BufferredColumnResultProxy will not be used.An INSERT statement in
> Oracle that uses "RETURNING", as is usually the case for the ORM INSERT
> statement, uses OUT parameters to achieve this and will make use of the
> ReturningResultProxy to work around what's needed here.

Ok.

> Since we're working without a test case of any kind, are you sure that the
> result proxy indicating the problem is not a ReturningResultProxy ?   Are we
> dealing with a column that has a server-generated default value that is of
> type BLOB, CLOB, or similar ?

There is ReturningResultProxy in the call stack for example here
https://gist.github.com/piotr-dobrogost/6d57cacb9e77f59748353b2b6c1334d7#file-call-stack-while-executing-line-519-of-result-py-run-for-the-first-time-ever-during-app-startup-L58

and it's the type of "self" which I showed here

https://gist.github.com/piotr-dobrogost/6d57cacb9e77f59748353b2b6c1334d7#file-call-stack-while-executing-line-519-of-result-py-run-for-the-first-time-ever-during-app-startup-L74

It occurred to me that one can reproduce this rather easily installing
Kotti framework alone (or rather slightly modified version adjusted to
work with Oracle which you can find at
https://github.com/piotr-dobrogost/Kotti/tree/sfx). Kotti has
requirements.txt file where you can replace Kotti==1.3.0-alpha.4 with
git+https://github.com/piotr-dobrogost/Kotti.git@sfx and install
everything with `pip install --no-deps -r requirements.txt`. Running
is simple with `pserve development.ini` command (as Kotti is based on
Pyramid). Before running you have to edit development.ini and change
sqlalchemy.url to point to your Oracle db. When run Kotti creates and
populates db and when you try to access app at http://localhost:5000/
the error happens.


Regards,
Piotr Dobrogost

-- 
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] Declaring column NOT NULL only for specific dialect (Oracle)?

[Mike Bayer]

On 04/26/2016 11:19 AM, Piotr Dobrogost wrote:

Questions I asked in my last post are concerned more with implementing
IDENTITY for Oracle. Here the situation is not so simple and simply
replacing text is not possible as "GENERATE AS IDENTITY" phrase must
be placed in right order with regard to other keywords. That's what
made me looking at DDLCompiler.get_column_specification() and what
lead me to asking the questions I asked. I assure you I've read the
link you sent before asking my questions. This example just doesn't
seem to answer those questions.

I'd like you to know that I'm treating our conversation as occasion to
learn more about SA and to know how things should be done. Replacing
NOT NULL with NULL or vice versa is just very specific way of solving
particular problem and does not bring me closer to knowing how to
manipulate DDL for column creation in general.


OK so for the "GENERATE AS IDENTITY", first off we'd like to support 
that for Oracle, and that would involve lots of version detection / 
switches / new logic added to oracle/base.py, as it changes not only the 
DDL but a lot of the specific workings of the dialect in how it renders 
an INSERT statement.


As far as getting just the DDL right now, the "text.replace()" approach 
is still close to the most expedient approach, which is to get at that 
text, then use a regular expression replace in order to add "GENERATE AS 
IDENTITY".   I'd do it like this:


from sqlalchemy.schema import CreateColumn

@compiles(CreateColumn, "oracle")
def _do_thing(element, compiler, **kw):
text = compiler.visit_create_column(element, **kw)
if element.element.info.get('use_identity', False):
text = re.sub(r'(NUMBER|INTEGER) ', r'\1 GENERATED ALWAYS AS 
IDENTITY', text)

return text

then in Column I'd set up a flag as follows:

Column('mycol', Numeric, info={"use_identity": True})

For your approach with NULL, I'd still advise using @compiles for that 
as well.  Looking at the engine.name and changing the column.nullable is 
fine but it means you need to coordinate the engine with the table 
metadata itself which is awkward and wouldn't work if your application 
had multiple engines in one process.







Regards,
Piotr Dobrogost



--
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] Declaring column NOT NULL only for specific dialect (Oracle)?

[Piotr Dobrogost]

On Tue, Apr 26, 2016 at 4:42 PM, Mike Bayer  wrote:
>
> So first we'll take a breathbreathe...and we're back.  The best thing
> about air is that it's in most places we live and it's free.

Breathing is very important indeed :) I highly recommend taking
freediving course to anyone – 5 minutes without breathing is really
unique experience.

>  So going back
> to the link I sent,
> http://docs.sqlalchemy.org/en/rel_1_0/core/ddl.html?highlight=createtable#sqlalchemy.schema.CreateColumn
> , scroll down and the second example illustrates how to get the default DDL
> for the column, as rendered by the current compiler, here is a 5 second
> adaptation of that:
>
> from sqlalchemy.schema import CreateColumn
>
> @compiles(CreateColumn, "oracle")
> def _do_thing(element, compiler, **kw):
> text = compiler.visit_create_column(element, **kw)
> text = text.replace("NOT NULL", "NULL")
> return text
>
> I hope this helps.

I own you explanation which I should have written in my last post. In
the end the need to change nullability concerned only one table so I
went with

elif engine.dialect.name == 'oracle':  # pragma: no cover
Node.name.prop.columns[0].nullable = True

in Kotti framework which works.

Questions I asked in my last post are concerned more with implementing
IDENTITY for Oracle. Here the situation is not so simple and simply
replacing text is not possible as "GENERATE AS IDENTITY" phrase must
be placed in right order with regard to other keywords. That's what
made me looking at DDLCompiler.get_column_specification() and what
lead me to asking the questions I asked. I assure you I've read the
link you sent before asking my questions. This example just doesn't
seem to answer those questions.

I'd like you to know that I'm treating our conversation as occasion to
learn more about SA and to know how things should be done. Replacing
NOT NULL with NULL or vice versa is just very specific way of solving
particular problem and does not bring me closer to knowing how to
manipulate DDL for column creation in general.


Regards,
Piotr Dobrogost

-- 
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] relationship joined to max() on a foreign table - help needed

[Mike Bayer]

On 04/26/2016 08:09 AM, Rob Fowler wrote:

I have a complete example here:

https://gist.github.com/mianos/42cf15928f27cc9dfde9996d2e593e78


Ideas? I am sure it's possible. At the moment I am just using a "orderby
desc" on the relationship and using [0] to get the first.



this example helps a lot for me to just fill in how to make it work. 
The relationship system isn't sophisticated enough to locate the 
"User.id" column buried within a correlated subquery in order to 
manipulate it for lazyloads or eager loads.   So we have to organize the 
primaryjoin so that "User.id" is not inside a subquery.  Here's a way to 
do that:


class User(Base):
__tablename__ = 'tbl_users'

id = Column(types.Integer, primary_key=True)

mobile = relationship(
"Mobile",
primaryjoin=(
"and_("
"User.id == Mobile.user_id,"

"Mobile.id.in_(select([func.max(Mobile.id)]).group_by(Mobile.user_id))"
")"
),
uselist=False
)


class Mobile(Base):
__tablename__ = 'tbl_mobiles'

id = Column(types.Integer, primary_key=True)

user_id = Column(types.Integer, ForeignKey(User.id, 
ondelete="CASCADE"), nullable=False)



the other way to go here is to use "relationship to non primary mapper" 
as detailed at 
http://docs.sqlalchemy.org/en/rel_1_0/orm/join_conditions.html#relationship-to-non-primary-mapper, 
which in fact will get you a more optimized query for this, but is more 
cumbersome to use on the Python side:


stmt = select([func.max(Mobile.id).label('id'), Mobile.user_id]).\
group_by(Mobile.user_id).alias()

user_mobile = mapper(
Mobile, stmt, primary_key=[stmt.c.id],
non_primary=True
)

User.mobile = relationship(
user_mobile,
primaryjoin=User.id == stmt.c.user_id,
viewonly=True,
uselist=False
)


for u, m in session.query(User, user_mobile) \
.join(User.mobile) \
.filter(User.id == 1082):
print u.id, m.id

for u in session.query(User).filter(User.id == 1082):
print u.mobile.id


I've attached my example scripts for both as a reference.






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


--
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.
from sqlalchemy import create_engine, types, Column, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, backref, remote, foreign
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import select, func, desc
from sqlalchemy.orm import mapper
from sqlalchemy.orm import aliased

connection = "sqlite://"

Base = declarative_base()


class User(Base):
__tablename__ = 'tbl_users'

id = Column(types.Integer, primary_key=True)

mobile = relationship(
"Mobile",
primaryjoin=(
"and_("
"User.id == Mobile.user_id,"
"Mobile.id.in_(select([func.max(Mobile.id)]).group_by(Mobile.user_id))"
")"
),
uselist=False
)


class Mobile(Base):
__tablename__ = 'tbl_mobiles'

id = Column(types.Integer, primary_key=True)

user_id = Column(types.Integer, ForeignKey(User.id, ondelete="CASCADE"), nullable=False)


if __name__ == '__main__':
engine = create_engine(connection, echo='debug')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

session.add_all([
User(id=1082),
User(id=1083),
Mobile(id=5, user_id=1082),
Mobile(id=6, user_id=1082),
Mobile(id=7, user_id=1082),
Mobile(id=8, user_id=1083),
Mobile(id=9, user_id=1083),
])

for u, m in session.query(User, Mobile) \
.join(User.mobile) \
.filter(User.id == 1082):
print u.id, m.id

for u in session.query(User).filter(User.id == 1082):
print u.mobile.id

m1 = session.query(Mobile).get(7)
assert m1 is u.mobile
from sqlalchemy import create_engine, types, Column, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, backref, remote, foreign
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import select, func, desc
from sqlalchemy.orm import mapper
from 

[sqlalchemy] Re: relationship joined to max() on a foreign table - help needed

[Jonathan Vanasco]

Michael helped me with a few similar threads recently.

One of them is here:

https://groups.google.com/forum/#!topic/sqlalchemy/Vw1iBXSLibI

That uses a "last 5" correlated subquery, but you could modify that to be 
max-1 and uselist=false

-- 
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] Declaring column NOT NULL only for specific dialect (Oracle)?

[Mike Bayer]

On 04/26/2016 06:27 AM, Piotr Dobrogost wrote:



Do I see right, that using @compiles(schema.CreateColumn, 'oracle') is
not good as it's being invoked too late to have access to colspec?

It seems I have to override DDLCompiler.get_column_specification() and
then I have to copy & paste code from this method because there's no
way to reuse code which generates DEFAULT and NULL statements, yes?
Overriding DDLCompiler.get_column_specification() means I subsequently
have to "register" my DDLCompiler's subclass somehow. How do I do it?
I did not find any information on this in docs. Looking at PGDialect
in source, there's ddl_compiler attribute but I don't see any API to
change it. You don't want me to monkey patch OracleDialect, do you?.


So first we'll take a breathbreathe...and we're back.  The best 
thing about air is that it's in most places we live and it's free.   So 
going back to the link I sent, 
http://docs.sqlalchemy.org/en/rel_1_0/core/ddl.html?highlight=createtable#sqlalchemy.schema.CreateColumn 
, scroll down and the second example illustrates how to get the default 
DDL for the column, as rendered by the current compiler, here is a 5 
second adaptation of that:


from sqlalchemy.schema import CreateColumn

@compiles(CreateColumn, "oracle")
def _do_thing(element, compiler, **kw):
text = compiler.visit_create_column(element, **kw)
text = text.replace("NOT NULL", "NULL")
return text

I hope this helps.






Regards,
Piotr Dobrogost

ps.
I have an impression there's hardly any traffic on #sqlalchemy during
Europe's working hours :(



--
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] Trying to create string array columns with Alembic gives "NameError: name 'String' is not defined"

[Mike Bayer]

On 04/26/2016 10:07 AM, Simon King wrote:


Can you show us the contents
of alembic/versions/dd9e391f807f_issues_is_behind.py ?


just a heads up this user is likely hitting 
https://bitbucket.org/zzzeek/alembic/issues/85/using-postgresqlarray-unicode-breaks, 
where the "sa." prefix is missing from the types inside of ARRAY when 
rendered in the migration file.






Simon

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


--
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: Running Migrations Simulataneously

[Mike Bayer]

On 04/26/2016 01:22 AM, Amit Saha wrote:

On Tue, Apr 26, 2016 at 12:33 PM, Mike Bayer  wrote:



On 04/25/2016 08:30 PM, Amit Saha wrote:


Hi all,

In my scenario, DB migrations (via alembic) will be run as part of the
app deployment and multiple app deployments will happen near
simultaneously (multiple EC2 instances talking to the same MySQL DB).
Let's, for simplicity's sake assume two instances:

Instance #1: Alembic sees alembic_version table and finds that we do
need the migrations to happen, and starts the migration.

Instance #2: Alembic sees alembic_version table and finds that we do
need the migrations to happen, and starts the migration.  This doesn't
obviously happen since MySQL will lock the table during the migration.



this scenario is entirely unsupported.   MySQL does not support
transactional DDL and migration #1 will be hitting some tables and migration
#2 another.The version table is not updated until after a particular
migration takes place so concurrent processes will both be doing the same
migration at the same time.



Now, let's say instance #1 finishes the migration first and updates the
alembic_version table. Then, the migration triggered by instance #2
starts to run, and errors out because the schema changes are already in
place.

Is this what will happen or does alembic get a read/write lock on
alembic_version table for the entire migration?



there are no "read/write locks" on the alembic_version table.  What you're
doing will not work at all.   Only one process should be running migrations
against a single target database at a time.


Thanks for the reply, Mike. I am wondering what are my options -
should I just have to ensure that I don't run migrations in more than
one process? Which would mean, I cannot really use alembic for my DB
migrations (I wonder how people would usually do it).


How this is usually done is that the decision to "migrate" is initiated 
manually.  I'm guessing this is a web application that wants to just 
automigrate when it starts.Usually what apps like that do is check 
that the DB is not up to date, and present a web form with a button to 
"upgrade" - so the user pushes the button only once.Or, if this is 
some kind of service that spawns multiple processes, the lead process 
does the check against the table and does the upgrade.


The thing about schema upgrades is that they require code changes to 
have any upgrades to apply.  So therefore, you're starting *something* 
to first run that new code; that's where the migration step should 
happen, before everything else starts up.









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

Re: [sqlalchemy] Trying to create string array columns with Alembic gives "NameError: name 'String' is not defined"

[Simon King]

On Tue, Apr 26, 2016 at 1:28 PM, Duke Dougal  wrote:

> Hi folks,
>
>
> Model is below plus error message below that.
>
>
> I am trying to create some array columns using Alembic but getting errors.
>
>
> Any help valued.
>
>
> thanks!
>
>
>
> from sqlalchemy import Column, String, Integer, DateTime
> from serve_spec.db_global import db
> import datetime
> from time import time
> from sqlalchemy.dialects.postgresql import JSON
> from sqlalchemy.dialects.postgresql import ARRAY
>
> class Issues(db.Base):
>
> __tablename__ = 'issues'
>
> id = Column(String, primary_key=True)
> thread_id   = Column(String, nullable=False)
> created = Column(DateTime(timezone=False), 
> nullable=False, default=datetime.datetime.utcnow)
> created_timestamp   = Column(Integer, nullable=False, 
> default=time)
> created_by_user_name= Column(String, nullable=False)
> is_parent   = Column(Integer, nullable=False)
> parent_title= Column(String)
> subscribed  = Column(ARRAY(String))
> unsubscribed= Column(ARRAY(String))
> pending_notifications_web   = Column(ARRAY(String))
> pending_notifications_email = Column(ARRAY(String))
> markdown_text   = Column(String, nullable=False, )
> kernel_id   = Column(String, nullable=False)
> state   = Column(String, nullable=False, 
> default='open')
> labels  = Column(JSON())
>
>
>
>
> (venv3.4.2) ubuntu@ip-172-31-8-128:/var/www/www.example.org/src/crowdwave$
> PYTHONPATH=. alembic upgrade head
> INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
> INFO  [alembic.runtime.migration] Will assume transactional DDL.
> INFO  [alembic.runtime.migration] Running upgrade d9bc97e175aa ->
> dd9e391f807f, Issues is behind
> Traceback (most recent call last):
>   File "/var/www/www.example.org/venv3.4.2/bin/alembic", line 9, in
> 
> load_entry_point('alembic==0.8.5', 'console_scripts', 'alembic')()
>   File "/var/www/
> www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/config.py",
> line 479, in main
> CommandLine(prog=prog).main(argv=argv)
>   File "/var/www/
> www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/config.py",
> line 473, in main
> self.run_cmd(cfg, options)
>   File "/var/www/
> www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/config.py",
> line 456, in run_cmd
> **dict((k, getattr(options, k)) for k in kwarg)
>   File "/var/www/
> www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/command.py",
> line 174, in upgrade
> script.run_env()
>   File "/var/www/
> www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/script/base.py",
> line 397, in run_env
> util.load_python_file(self.dir, 'env.py')
>   File "/var/www/
> www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/util/pyfiles.py",
> line 81, in load_python_file
> module = load_module_py(module_id, path)
>   File "/var/www/
> www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/util/compat.py",
> line 68, in load_module_py
> module_id, path).load_module(module_id)
>   File "", line 539, in _check_name_wrapper
>   File "", line 1614, in load_module
>   File "", line 596, in _load_module_shim
>   File "", line 1220, in load
>   File "", line 1200, in _load_unlocked
>   File "", line 1129, in _exec
>   File "", line 1471, in exec_module
>   File "", line 321, in
> _call_with_frames_removed
>   File "alembic/env.py", line 82, in 
> run_migrations_online()
>   File "alembic/env.py", line 77, in run_migrations_online
> context.run_migrations()
>   File "", line 8, in run_migrations
>   File "/var/www/
> www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/runtime/environment.py",
> line 797, in run_migrations
> self.get_context().run_migrations(**kw)
>   File "/var/www/
> www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/runtime/migration.py",
> line 312, in run_migrations
> step.migration_fn(**kw)
>   File "/var/www/
> www.example.org/src/crowdwave/alembic/versions/dd9e391f807f_issues_is_behind.py",
> line 21, in upgrade
> op.add_column('issues', sa.Column('pending_notifications_email',
> postgresql.ARRAY(String()), nullable=True))
> NameError: name 'String' is not defined
>

Can you show us the contents
of alembic/versions/dd9e391f807f_issues_is_behind.py ?

Simon

-- 
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] Trying to create string array columns with Alembic gives "NameError: name 'String' is not defined"

[Duke Dougal]

Hi folks,


Model is below plus error message below that.


I am trying to create some array columns using Alembic but getting errors.


Any help valued.


thanks!



from sqlalchemy import Column, String, Integer, DateTime
from serve_spec.db_global import db
import datetime
from time import time
from sqlalchemy.dialects.postgresql import JSON
from sqlalchemy.dialects.postgresql import ARRAY

class Issues(db.Base):

__tablename__ = 'issues'

id = Column(String, primary_key=True)
thread_id   = Column(String, nullable=False)
created = Column(DateTime(timezone=False), 
nullable=False, default=datetime.datetime.utcnow)
created_timestamp   = Column(Integer, nullable=False, default=time)
created_by_user_name= Column(String, nullable=False)
is_parent   = Column(Integer, nullable=False)
parent_title= Column(String)
subscribed  = Column(ARRAY(String))
unsubscribed= Column(ARRAY(String))
pending_notifications_web   = Column(ARRAY(String))
pending_notifications_email = Column(ARRAY(String))
markdown_text   = Column(String, nullable=False, )
kernel_id   = Column(String, nullable=False)
state   = Column(String, nullable=False, default='open')
labels  = Column(JSON())




(venv3.4.2) ubuntu@ip-172-31-8-128:/var/www/www.example.org/src/crowdwave$ 
PYTHONPATH=. alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade d9bc97e175aa -> 
dd9e391f807f, Issues is behind
Traceback (most recent call last):
  File "/var/www/www.example.org/venv3.4.2/bin/alembic", line 9, in 
load_entry_point('alembic==0.8.5', 'console_scripts', 'alembic')()
  File 
"/var/www/www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/config.py",
 
line 479, in main
CommandLine(prog=prog).main(argv=argv)
  File 
"/var/www/www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/config.py",
 
line 473, in main
self.run_cmd(cfg, options)
  File 
"/var/www/www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/config.py",
 
line 456, in run_cmd
**dict((k, getattr(options, k)) for k in kwarg)
  File 
"/var/www/www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/command.py",
 
line 174, in upgrade
script.run_env()
  File 
"/var/www/www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/script/base.py",
 
line 397, in run_env
util.load_python_file(self.dir, 'env.py')
  File 
"/var/www/www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/util/pyfiles.py",
 
line 81, in load_python_file
module = load_module_py(module_id, path)
  File 
"/var/www/www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/util/compat.py",
 
line 68, in load_module_py
module_id, path).load_module(module_id)
  File "", line 539, in _check_name_wrapper
  File "", line 1614, in load_module
  File "", line 596, in _load_module_shim
  File "", line 1220, in load
  File "", line 1200, in _load_unlocked
  File "", line 1129, in _exec
  File "", line 1471, in exec_module
  File "", line 321, in 
_call_with_frames_removed
  File "alembic/env.py", line 82, in 
run_migrations_online()
  File "alembic/env.py", line 77, in run_migrations_online
context.run_migrations()
  File "", line 8, in run_migrations
  File 
"/var/www/www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/runtime/environment.py",
 
line 797, in run_migrations
self.get_context().run_migrations(**kw)
  File 
"/var/www/www.example.org/venv3.4.2/lib/python3.4/site-packages/alembic/runtime/migration.py",
 
line 312, in run_migrations
step.migration_fn(**kw)
  File 
"/var/www/www.example.org/src/crowdwave/alembic/versions/dd9e391f807f_issues_is_behind.py",
 
line 21, in upgrade
op.add_column('issues', sa.Column('pending_notifications_email', 
postgresql.ARRAY(String()), nullable=True))
NameError: name 'String' is not defined
(venv3.4.2) ubuntu@ip-172-31-8-128:/var/www/www.example.org/src/crowdwave$

-- 
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] relationship joined to max() on a foreign table - help needed

[Rob Fowler]

Can anyone help me with this interesting (to me) relationship definition in 
sqla.


I have some already defined data, not by me, that has, for each user, a 
foreign table of contact numbers, where the highest contact number is the 
one to use. 


For example, users:

Harry, 1081, and Bob 1082

mobiles:

1081;"0412590410", 1082;"0426236245", 1082;"0416253647"


With:

SELECT u.id, u.first_name, m1.mobile

  FROM model.tbl_users as u

  JOIN model.tbl_mobiles as m1

  ON m1.id = (select max(m2.id) from model.tbl_mobiles as m2 where 
m2.user_id = u.id)


gives:


1081;"Harry";"0412590410"

1082;"Bob";"0416253647"


What I want to do is model a relationship on the user (eventually with 
no_list) where the field is simply the highest number.


I have been hacking away at a try:


mobile = relationship("Mobile",

   primaryjoin="Mobile.id == 
select([func.max(Mobile.id)]).where(Mobile.user_id == User.id).as_scalar()",

  viewonly=True)


but I can't seem to get the join right. I always get:


ArgumentError: Relationship User.mobile could not determine any unambiguous 
local/remote column pairs based on join condition and remote_side 
arguments. Consider using the remote() annotation to accurately mark those 
elements of the join condition that are on the remote side of the 
relationship.


I have tried all sorts of somewhat random remote() and foriegn() bits 
around the query to no avail.


I have a complete example here:

https://gist.github.com/mianos/42cf15928f27cc9dfde9996d2e593e78


Ideas? I am sure it's possible. At the moment I am just using a "orderby 
desc" on the relationship and using [0] to get the first.

-- 
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] Declaring column NOT NULL only for specific dialect (Oracle)?

[Piotr Dobrogost]

On Fri, Apr 22, 2016 at 5:57 PM, Mike Bayer  wrote:
>
> On 04/22/2016 11:51 AM, Piotr Dobrogost wrote:
>>
>>  >Column('some_col', nullable=False, info={"oracle_not_null": False})
>>  >
>>  >then in your @compiles recipe look for column.info
>> ['oracle_not_null']
>>  >while at the same time looking at compiler.dialect.name
>>  == 'oracle'.
>>
>> I get it. However as we want this for every autoincrement column I guess
>> we can check for already provided "autoincrement" flag and avoid passing
>> column.info altogether. Nice.
>>
>> It seems like this could be used to render IDENTITY keyword for
>> autoincrement columns for Oracle 12c which I asked about recently in my
>> post titled "Support for Oracle 12c auto increment (IDENTITY) columns?"
>> (https://groups.google.com/forum/#!topic/sqlalchemy/Jg_kV6VF0_E).
>
>
> yes, you can write whatever rendering you want in there.


Do I see right, that using @compiles(schema.CreateColumn, 'oracle') is
not good as it's being invoked too late to have access to colspec?

It seems I have to override DDLCompiler.get_column_specification() and
then I have to copy & paste code from this method because there's no
way to reuse code which generates DEFAULT and NULL statements, yes?
Overriding DDLCompiler.get_column_specification() means I subsequently
have to "register" my DDLCompiler's subclass somehow. How do I do it?
I did not find any information on this in docs. Looking at PGDialect
in source, there's ddl_compiler attribute but I don't see any API to
change it. You don't want me to monkey patch OracleDialect, do you?.

Regards,
Piotr Dobrogost

ps.
I have an impression there's hardly any traffic on #sqlalchemy during
Europe's working hours :(

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