Re: [sqlalchemy] Nested bundles, labels and "Ambiguous column name"

[Jacob Magnusson]

Michael,

Is there any chance we might see support for *with_labels* in this use 
case? Even though my "solution" works, it's somewhat of an annoyance with 
all these warnings being spit out:

*SAWarning: Column 'id' on table  0x110cd5d30; Select object> being replaced by Column('id', Integer(), 
> table=, primary_key=True, nullable=False), which has the 
> same key.  Consider use_labels for select() statements.*



On Friday, April 10, 2015 at 12:19:31 AM UTC+2, Michael Bayer wrote:
>
>
>
> On 4/9/15 1:50 PM, Jacob Magnusson wrote:
>
> I have this case with a bundle that looks something like this:
>
> primate_bundle = Bundle(
> 'primate',
> Primate.name,
> Bundle('wooden_tool', *[
> WoodenTool.id,
> WoodenTool.name,
> WoodenToolCategory.name.label('category'),
> ]),
> Bundle('solid_tool', *[
> SolidTool.id,
> SolidTool.name,
> SolidToolCategory.name.label('category'),
> ])
> )
>
> Then I query it like this:
>
> session.query(primate_bundle)
> .select_from(Primate)
> .join(WoodenTool, Primate.main_tool)
> .join(WoodenToolCategory, WoodenTool.category_id == WoodenToolCategory.id)
> .join(SolidTool, Primate.secondary_tool)
> .join(SolidToolCategory, SolidTool.category_id == SolidToolCategory.id)
> .all()
>
> However, since the label for category name is the same within both 
> sub-bundles it will throw Ambiguous column name (because the compiled SQL 
> labels will be exactly the same). Adding .with_labels() doesn’t fix it. 
> Full traceback can be seen by running the included examples. Commenting out 
> one of the .label() lines in the example makes it runnable. Do you guys 
> have a clean solution to support this use case? I really like this feature 
> of creating your own custom made results so it would be a shame to not be 
> able to do this.
>
> OK well here's one hacky way, that we could better accommodate by adding a 
> "key" parameter to label(), this is sort of like your other approach:
>
> ay = A.y.label(None)
> ay.key = 'foobar'
> bp = B.p.label(None)
> bp.key = 'foobar'
>
> ab = Bundle(
> 'ab',
> Bundle('a', *[
> A.id,
> A.x,
> ay,
> ]),
> Bundle('b', *[
> B.id,
> B.q,
> bp,
> ])
> )
>
> The other way is to provide create_row_processor() as we describe here: 
> http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_columns.html?highlight=bundle#column-bundles.
>   
> That way you could just make this work any way you wanted:
>
>
> from sqlalchemy import util
>
>
> class LabelBundle(Bundle):
> def __init__(self, *arg, **kw):
> self.extra_labels = kw.pop('extra_labels', {})
> super(LabelBundle, self).__init__(*arg, **kw)
>
> def create_row_processor(self, query, procs, labels):
> # or use a NamedTuple here
> keyed_tuple = util.lightweight_named_tuple(
> 'result', [self.extra_labels.get(l, l) for l in labels])
>
> def proc(row):
> return keyed_tuple([proc(row) for proc in procs])
> return proc
>
>
> ab = LabelBundle(
> 'ab',
> LabelBundle(
> 'a',
> extra_labels={'y': 'foobar'},
> *[A.id, A.x, A.y]),
> LabelBundle(
> 'b',
> extra_labels={'p': 'foobar'},
> *[B.id, B.q, B.p])
> )
>
>
>
>
>
>
>
> Tested on SQLAlchemy 1.0.0b5 and 0.9.9. Python 3.
>
> Thank you so much for any potential help you can give me on this. I’ve 
> followed the source code for Bundle but I can’t think of a clean way to 
> this…
> ​
> -- 
> 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/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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Question about FAQ entry titled "I’m getting a warning or error about “Implicitly combining column X under attribute Y”"

[Piotr Dobrogost]

Hi!

In the FAQ there's entry titled "I’m getting a warning or error about 
“Implicitly combining column X under attribute Y”" with the following 
example:

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)

class B(A):
__tablename__ = 'b'
# probably not what you want, but this is a demonstration
id = column_property(Column(Integer, primary_key=True), A.id)
a_id = Column(Integer, ForeignKey('a.id'))


However trying to add yet another class just to see what happens...

class C(B):
__tablename__ = 'c'

id = column_property(Column(Integer, primary_key=True), B.id)
b_id = Column(Integer, ForeignKey('b.id'))


...results in the same kind of error again:
"sqlalchemy.exc.InvalidRequestError: Implicitly combining column b.id with 
column a.id under attribute 'id'.  Please configure one or more attributes 
for these same-named columns explicitly."

How to avoid this?

ps.
This is a follow-up to my recent post 
https://groups.google.com/forum/#!topic/sqlalchemy/n_JEgKYshnE

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Question about FAQ entry titled "I’m getting a warning or error about “Implicitly combining column X under attribute Y”"

[Richard Gerd Kuesters]

is your "A" class abstract and/or are you using them with polymorphism?


regards,
richard.


On 09/08/2015 07:00 AM, Piotr Dobrogost wrote:

Hi!

In the FAQ there's entry titled "I’m getting a warning or error about 
“Implicitly combining column X under attribute Y”" with the following 
example:


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)

class B(A):
__tablename__ = 'b'
# probably not what you want, but this is a demonstration
id = column_property(Column(Integer, primary_key=True), A.id)
a_id = Column(Integer, ForeignKey('a.id'))


However trying to add yet another class just to see what happens...

class C(B):
__tablename__ = 'c'

id = column_property(Column(Integer, primary_key=True), B.id)
b_id = Column(Integer, ForeignKey('b.id'))


...results in the same kind of error again:
"sqlalchemy.exc.InvalidRequestError: Implicitly combining column b.id 
with column a.id under attribute 'id'.  Please configure one or more 
attributes for these same-named columns explicitly."


How to avoid this?

ps.
This is a follow-up to my recent post 
https://groups.google.com/forum/#!topic/sqlalchemy/n_JEgKYshnE


--
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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
<>

Re: [sqlalchemy] Question about FAQ entry titled "I’m getting a warning or error about “Implicitly combining column X under attribute Y”"

[Piotr Dobrogost]

On Tuesday, September 8, 2015 at 1:37:05 PM UTC+2, Richard Kuesters wrote:

| is your "A" class abstract and/or are you using them with polymorphism?

Thank you for taking time to look at this.
If by abstract you mean abstract as defined at 
http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html?highlight=__abstract__#abstract
 
then no, it's not abstract. As to polymorphism the answer is not so simple. 
Basically using this example I tried to depict situation which I believe 
takes place in SQLAlchemy-Continuum extension. This extension for every 
model creates another one recording all changes to the original one. 
Original models are polymorphic (and use joined table inheritance) and 
create simple hierarchy Node -> Content -> Document which can be seen at 
https://github.com/Kotti/Kotti/blob/9a8684c10fbb3c6fbf6b1265c86b732e1c416c4a/kotti/resources.py
 
I'm not sure if models created by versioning extension (NodeVersion, 
ContentVersion and DocumentVersion) are meant to be polymorphic (supposedly 
as a consequence of original models being polymorphic) or not. Each of 
these models has "transaction_id" attribute mapped to "transaction_id" 
column in mapped table. The error is "sqlalchemy.exc.InvalidRequestError: 
Implicitly combining column contents_version.transaction_id with column 
nodes_version.transaction_id under attribute 'transaction_id'. Please 
configure one or more attributes for these same-named columns explicitly." 
By extending example from FAQ I wanted to understand under what 
circumstances could same-named columns be made to work.

Regards,
Piotr

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Question about FAQ entry titled "I’m getting a warning or error about “Implicitly combining column X under attribute Y”"

[Richard Gerd Kuesters]

well, i'm sorry if i'm pouring could water on you but continuum never 
worked as expected (at least for me) and i always used history_meta for 
audit, which comes packaged with sqlalchemy as an example and is much 
more friendly if you need to add functionalities on your own :)


cheers,
richard.

link to it: 
http://docs.sqlalchemy.org/en/rel_1_0/_modules/examples/versioned_history/history_meta.html


On 09/08/2015 11:24 AM, Piotr Dobrogost wrote:

On Tuesday, September 8, 2015 at 1:37:05 PM UTC+2, Richard Kuesters wrote:

| is your "A" class abstract and/or are you using them with polymorphism?

Thank you for taking time to look at this.
If by abstract you mean abstract as defined at 
http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html?highlight=__abstract__#abstract 
then no, it's not abstract. As to polymorphism the answer is not so 
simple. Basically using this example I tried to depict situation which 
I believe takes place in SQLAlchemy-Continuum extension. This 
extension for every model creates another one recording all changes to 
the original one. Original models are polymorphic (and use joined 
table inheritance) and create simple hierarchy Node -> Content -> 
Document which can be seen at 
https://github.com/Kotti/Kotti/blob/9a8684c10fbb3c6fbf6b1265c86b732e1c416c4a/kotti/resources.py 
I'm not sure if models created by versioning extension (NodeVersion, 
ContentVersion and DocumentVersion) are meant to be polymorphic 
(supposedly as a consequence of original models being polymorphic) or 
not. Each of these models has "transaction_id" attribute mapped to 
"transaction_id" column in mapped table. The error is 
"sqlalchemy.exc.InvalidRequestError: Implicitly combining column 
contents_version.transaction_id with column 
nodes_version.transaction_id under attribute 'transaction_id'. Please 
configure one or more attributes for these same-named columns 
explicitly." By extending example from FAQ I wanted to understand 
under what circumstances could same-named columns be made to work.


Regards,
Piotr

--
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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
<>

Re: [sqlalchemy] Question about FAQ entry titled "I’m getting a warning or error about “Implicitly combining column X under attribute Y”"

[Mike Bayer]

On 9/8/15 6:00 AM, Piotr Dobrogost wrote:

Hi!

In the FAQ there's entry titled "I’m getting a warning or error about 
“Implicitly combining column X under attribute Y”" with the following 
example:


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)

class B(A):
__tablename__ = 'b'
# probably not what you want, but this is a demonstration
id = column_property(Column(Integer, primary_key=True), A.id)
a_id = Column(Integer, ForeignKey('a.id'))


However trying to add yet another class just to see what happens...

class C(B):
__tablename__ = 'c'

id = column_property(Column(Integer, primary_key=True), B.id)
b_id = Column(Integer, ForeignKey('b.id'))


...results in the same kind of error again:
"sqlalchemy.exc.InvalidRequestError: Implicitly combining column b.id 
with column a.id under attribute 'id'.  Please configure one or more 
attributes for these same-named columns explicitly."


How to avoid this?
Place each column explicitly under the attribute that you want; B.id 
does not expand into "B.id, A.id" automatically here:


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)


class B(A):
__tablename__ = 'b'
id = column_property(Column(Integer, primary_key=True), A.id)
a_id = Column(Integer, ForeignKey('a.id'))

class C(B):
__tablename__ = 'c'

id = column_property(Column(Integer, primary_key=True), B.id, A.id)
b_id = Column(Integer, ForeignKey('b.id'))









ps.
This is a follow-up to my recent post 
https://groups.google.com/forum/#!topic/sqlalchemy/n_JEgKYshnE


--
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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Nested bundles, labels and "Ambiguous column name"

[Mike Bayer]

On 9/8/15 4:13 AM, Jacob Magnusson wrote:

Michael,

Is there any chance we might see support for /with_labels/ in this use 
case? Even though my "solution" works, it's somewhat of an annoyance 
with all these warnings being spit out:


/SAWarning: Column 'id' on table  being replaced by Column('id',
Integer(), table=, primary_key=True,
nullable=False), which has the same key.  Consider use_labels for
select() statements./



It's not clear how with_labels() can be made to work with this without a 
lot of re-thinking of many of the internal aspects of the Query and 
related objects.OTOH, the Bundle object is fully customizable and as 
I recommended, a custom approach that applies labels to Column objects 
as they are passed and translates back as needed on the result side is 
feasible right now; I'd rather add a labeling/nesting feature to Bundle 
itself based on the discoveries gained by working with that approach as 
a recipe.








On Friday, April 10, 2015 at 12:19:31 AM UTC+2, Michael Bayer wrote:



On 4/9/15 1:50 PM, Jacob Magnusson wrote:


I have this case with a bundle that looks something like this:

|primate_bundle = Bundle( 'primate', Primate.name,
Bundle('wooden_tool', *[ WoodenTool.id, WoodenTool.name,
WoodenToolCategory.name.label('category'), ]),
Bundle('solid_tool', *[ SolidTool.id, SolidTool.name,
SolidToolCategory.name.label('category'), ]) ) |

Then I query it like this:

|session.query(primate_bundle) .select_from(Primate)
.join(WoodenTool, Primate.main_tool) .join(WoodenToolCategory,
WoodenTool.category_id == WoodenToolCategory.id) .join(SolidTool,
Primate.secondary_tool) .join(SolidToolCategory,
SolidTool.category_id == SolidToolCategory.id) .all() |

However, since the label for category name is the same within
both sub-bundles it will throw |Ambiguous column name| (because
the compiled SQL labels will be exactly the same). Adding
|.with_labels()| doesn’t fix it. Full traceback can be seen by
running the included examples. Commenting out one of the
|.label()| lines in the example makes it runnable. Do you guys
have a clean solution to support this use case? I really like
this feature of creating your own custom made results so it would
be a shame to not be able to do this.


OK well here's one hacky way, that we could better accommodate by
adding a "key" parameter to label(), this is sort of like your
other approach: ay = A.y.label(None) ay.key = 'foobar' bp =
B.p.label(None) bp.key = 'foobar' ab = Bundle( 'ab',
Bundle('a', *[ A.id, A.x, ay, ]),
Bundle('b', *[ B.id, B.q, bp, ]) ) The

other way is to provide create_row_processor() as we describe
here:

http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_columns.html?highlight=bundle#column-bundles

.
That way you could just make this work any way you wanted: from
sqlalchemy import util class LabelBundle(Bundle): def
__init__(self, *arg, **kw): self.extra_labels =
kw.pop('extra_labels', {}) super(LabelBundle,
self).__init__(*arg, **kw) def create_row_processor(self,
query, procs, labels): # or use a NamedTuple here
keyed_tuple = util.lightweight_named_tuple( 'result',

[self.extra_labels.get(l, l) for l in labels]) def
proc(row): return keyed_tuple([proc(row) for proc in
procs]) return proc ab = LabelBundle( 'ab',
LabelBundle( 'a', extra_labels={'y': 'foobar'},
*[A.id, A.x, A.y]), LabelBundle( 'b',
extra_labels={'p': 'foobar'}, *[B.id, B.q, B.p]) )


Tested on SQLAlchemy 1.0.0b5 and 0.9.9. Python 3.

Thank you so much for any potential help you can give me on this.
I’ve followed the source code for |Bundle| but I can’t think of a
clean way to this…

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

Re: [sqlalchemy] alembic.op.create_index algorithm=inplace lock=none

[Mike Bayer]

On 9/8/15 9:32 AM, murray.was...@lightspeedretail.com wrote:

Hi there,

Is there any way I can set additional params on 
alembic.op.create_index. We are using python and MYSQL.


Need to add :

algorithm=inplace
lock=none

trying to produce:

CREATEINDEX[index_name]ON[table_name] 
([index_fields])*algorithm**=inplace lock=**none*;


NOTE: I have tried passing them in via **kwargs but was not able to 
use mysql_algorithm or mysql_lock, did not recognize those keywords.
op.create_index( index_name, table_name, columns, schema=None, 
unique=unique, quote=None, **kwargs)


Any assistance would be greatly appreciated.


simply render the text directly using op.execute():

op.execute("CREATE INDEX [index_name] ON [table_name] ([index_fields]) 
algorithm=inplace lock=none")







Cheers
Murray
--
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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Tips for schema based db traversal and building

[Mike Bayer]

On 9/8/15 12:57 PM, Steve Murphy wrote:


A bit difficult is grabbing just certain columns in the select, given
that we have only a list of column names. That would be real nice
if such a method were available in the core API.

For example, a method for select whereby I could supply a simple
list of column names to fetch in the select would be very handy:

tablename = "user"
tab = meta1.tables[tablename]
collist = [ "id", "name", "address" ] ## just a few of many more
q = tab.select(colnames=collist)

this is pretty simple:

q = select([tab.c[name] for name in ["id", "name", "address"]])


Also, if I could get a column object from a table by name
it would really make life easier at times:

q = select([tab]).where(tab.colname(colnamevar) == col_var_val)


yeah, tab.c[name]

this is here:

http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html#accessing-tables-and-columns




Another rough spot with the API is setting up a simple set of where
clauses for a select, given a map of column names vs. values.
if the map contained {"user": "cat", "city": "Gotham"} it would
be cool if we could get the desired select:

select * from table where user='cat' and city='Gotham';


that is:

select = select.where(and_(*[tab.c[key] == value for key, value in 
mymap.items()]))




Another rough spot is getting a list of constraints. I note that
postgresql has sql to create constraints, and remove them, but
not to get a list of them.

you can get these like this:

from sqlalchemy import inspect
insp = inspect(my_engine)

fk_constraints = insp.get_foreign_keys('mytable')
uq_constraints = insp.get_unique_constraints('mytable')
indexes = insp.get_indexes('mytable')

docs: 
http://docs.sqlalchemy.org/en/rel_1_0/core/reflection.html#fine-grained-reflection-with-inspector


there's no listing of CHECK constraints right now.

they are also available on the Table as fully constructed constraint 
objects:


table = Table('mytable', somemetadata, autoload=True)
for constraint in table.constraints:
#   ...


docs for the table.constraints accessor are not in good shape right now, 
the API docs aren't generating for it and there's only a few mentions of 
it, but inspector is usually more straightforward if you are just 
getting raw information about them.





I do see the "\d " command,
and you get a list of all the foreign keys and constraints that way,
all in a big block of text I'm sure that, without supporting sql 
syntax,
any further sqlalchemy constraint support will be very hard, if not 
impossible,

to implement.
well \d is just part of your psql shell, it is ultimately querying the 
pg_catalog tables which is what the SQLA postgresql dialect queries as well.





The fact that sqlalchemy does what it does has saved me a ton of time 
writing

a full-blown SQL parser. Many congrats, thanks, ataboys,  and kudu's!!!


thanks glad its working out!




murf



--
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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Tips for schema based db traversal and building

[Steve Murphy]

This message concerns using sqlAlchemy for schema based traversal and 
manipulation.
It is the result of a project to transfer data from on database to another, 
where objects
refer to each other, and must be copied to new rows in the target db, and 
have all the
foreign references updated in the new db.

I'm using the 1.0 release of sqlalchemy on python 2.7.9 (ubuntu 15), btw.

I've gotten thru a very large percentage of the project, but see some places
where a few "shortcuts" could be supplied that would shorten the code
and make programming it a bit simpler... just in case anyone is interested.

dynamic lists vs. the declarative approach:

I needed to form the list of variables and values to insert into the target
db at run time. I had success here:

vals = {}
from_tab = meta1.tables[table_name]
q = select([from_tab]).where(from_tab.c.id == from_id)
result = con1.execute(q)
res_colnames = result.keys()
res_row = result.first()
for colname, val in zip(res_colnames, res_row) :
   ...
   vals[colname] = val;
   ...
to_tab = meta2.tables[table_name]
ins = to_tab.insert().values(**vals)   ## here use a map instead of 
declarative code
result = self.con2.execute(ins)

The code above is a bit simplified from real life. the table_name is 
supplied,
the meta1, con1,  are all gotten from easily imaginable function call 
results,
and so are meta2, con2 for the target db.
We can assume the from_id is the id in the "from" db, of the row in
the "from" table_name that we want to copy the data.
We fetch the row from the "from" db, and we (with some filtering, like
removing the id of the originating row) then insert the filtered values
into the "to" db's table of the same name. We will obtain the id of the
new row created, and set up a simple mapping, after the insert is
executed.

So much for the task description!

In the below, if you notice I'm missing something in the way of 
knowledge, feel free to enlighten me!

A bit difficult is grabbing just certain columns in the select, given
that we have only a list of column names. That would be real nice
if such a method were available in the core API.

For example, a method for select whereby I could supply a simple
list of column names to fetch in the select would be very handy:

tablename = "user"
tab = meta1.tables[tablename]
collist = [ "id", "name", "address" ] ## just a few of many more
q = tab.select(colnames=collist)

 In the meantime,
it is a bit simpler to just grab the contents of the whole row, and
sift thru the results for the columns actually needed.


Also, if I could get a column object from a table by name
it would really make life easier at times:

q = select([tab]).where(tab.colname(colnamevar) == col_var_val)

where a colname method would do the lookup for you, and return with
the equivalent of what tab.c.fixedcolname would give.



Another rough spot with the API is setting up a simple set of where
clauses for a select, given a map of column names vs. values.
if the map contained {"user": "cat", "city": "Gotham"} it would
be cool if we could get the desired select:

select * from table where user='cat' and city='Gotham';

via maybe something like this:

wheremap = {"user": "cat", "city": "Gotham"}
q = select([from_tab]).where_from_map(wheremap)


Another rough spot is getting a list of constraints. I note that
postgresql has sql to create constraints, and remove them, but
not to get a list of them. I do see the "\d " command,
and you get a list of all the foreign keys and constraints that way,
all in a big block of text I'm sure that, without supporting sql syntax,
any further sqlalchemy constraint support will be very hard, if not 
impossible,
to implement.

The fact that sqlalchemy does what it does has saved me a ton of time 
writing
a full-blown SQL parser. Many congrats, thanks, ataboys,  and kudu's!!!

murf



-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Nested bundles, labels and "Ambiguous column name"

[Mike Bayer]

On 9/8/15 12:03 PM, Mike Bayer wrote:



On 9/8/15 4:13 AM, Jacob Magnusson wrote:

Michael,

Is there any chance we might see support for /with_labels/ in this 
use case? Even though my "solution" works, it's somewhat of an 
annoyance with all these warnings being spit out:


/SAWarning: Column 'id' on table

being replaced by Column('id', Integer(), table=,
primary_key=True, nullable=False), which has the same key.
 Consider use_labels for select() statements./



It's not clear how with_labels() can be made to work with this without 
a lot of re-thinking of many of the internal aspects of the Query and 
related objects.OTOH, the Bundle object is fully customizable and 
as I recommended, a custom approach that applies labels to Column 
objects as they are passed and translates back as needed on the result 
side is feasible right now; I'd rather add a labeling/nesting feature 
to Bundle itself based on the discoveries gained by working with that 
approach as a recipe.


I'm looking at this again, with_labels() doesn't even apply here because 
that doesn't override a call to label(). the Query already invokes 
with_labels on all select() statements anyway:


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
x = Column(Integer)
y = Column(Integer)

s = Session()
print s.query(Bundle('q', A.x, A.y))

produces:

SELECT a.x AS a_x, a.y AS a_y
FROM a

the original request referred to the use of label() explicitly:

print s.query(Bundle('q', A.x, A.y.label('y')))

which of course, has to render *exactly* that label(), anything else 
would be a super surprise:


SELECT a.x AS a_x, a.y AS y
FROM a

Like i said originally, you just need a "key" argument on label(), so 
you can say:


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
x = Column(Integer)
y = Column(Integer)


class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
x = Column(Integer)
y = Column(Integer)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([A(x=1, y=2), B(x=3, y=4)])
s.commit()

for row in s.query(
Bundle('root', Bundle('a', A.x, A.y.label(None, key='y')), 
Bundle('b', B.x, B.y.label(None, key='y')))

):
print row.root.a.y, row.root.b.y

where you get a query like:

SELECT a.x AS a_x, a.y AS y_1, b.x AS b_x, b.y AS y_2
FROM a, b


and the result:

2 4


the patch is below.   This is an easy feature add, if desired.

diff --git a/lib/sqlalchemy/orm/attributes.py 
b/lib/sqlalchemy/orm/attributes.py

index 5440d6b..ef44d71 100644
--- a/lib/sqlalchemy/orm/attributes.py
+++ b/lib/sqlalchemy/orm/attributes.py
@@ -168,8 +168,8 @@ class QueryableAttribute(interfaces._MappedAttribute,
 self._parententity,
 of_type=cls)

-def label(self, name):
-return self._query_clause_element().label(name)
+def label(self, name, key=None):
+return self._query_clause_element().label(name, key=key)

 def operate(self, op, *other, **kwargs):
 return op(self.comparator, *other, **kwargs)
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 618b987..4bcb897 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -856,7 +856,7 @@ class ColumnElement(operators.ColumnOperators, 
ClauseElement):

 """
 return Cast(self, type_)

-def label(self, name):
+def label(self, name, key=None):
 """Produce a column label, i.e. `` AS ``.

 This is a shortcut to the :func:`~.expression.label` function.
@@ -864,7 +864,7 @@ class ColumnElement(operators.ColumnOperators, 
ClauseElement):

 if 'name' is None, an anonymous label name will be generated.

 """
-return Label(name, self, self.type)
+return Label(name, self, self.type, key=key)

 @util.memoized_property
 def anon_label(self):
@@ -3274,7 +3274,7 @@ class Label(ColumnElement):

 __visit_name__ = 'label'

-def __init__(self, name, element, type_=None):
+def __init__(self, name, element, type_=None, key=None):
 """Return a :class:`Label` object for the
 given :class:`.ColumnElement`.

@@ -3304,7 +3304,11 @@ class Label(ColumnElement):
 '%%(%d %s)s' % (id(self), getattr(element, 'name', 
'anon'))

 )

-self.key = self._label = self._key_label = self.name
+self._label = self.name
+if key is not None:
+self.key = self._key_label = key
+else:
+self.key = self._key_label = self.name
 self._element = element
 self._type = type_
 self._proxies = [element]
diff --git a/test/orm/test_bulk.py b/test/orm/test_bulk.py
index e2a1464..c209096 100644
--- a/test/orm/test_bulk.py
+++ b/test/orm/test_bulk.py
@@ -60,7 +60,7 @@ class BulkInsertUpdateTest(BulkTest, 
_fixtures.FixtureTest):

 objects = [
 

Re: [sqlalchemy] Question about FAQ entry titled "I’m getting a warning or error about “Implicitly combining column X under attribute Y”"

[Piotr Dobrogost]

On Tuesday, September 8, 2015 at 4:36:38 PM UTC+2, Richard Kuesters wrote:
>
> well, i'm sorry if i'm pouring could water on you but continuum never 
> worked as expected (at least for me) and i always used 
>

Cold shower it is indeed :(
Nevertheless thank you for your time and interest.

I keep hoping someone has tried continuum with joined table inheritance and 
could point out what's wrong here...

Regards,
Piotr

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] alembic.op.create_index algorithm=inplace lock=none

[murray . wasley]

Hi there,

Is there any way I can set additional params on alembic.op.create_index. We 
are using python and MYSQL.

Need to add :

algorithm=inplace
lock=none

trying to produce:

CREATE INDEX [index_name] ON [table_name] ([index_fields])* algorithm**=inplace 
lock=**none*;

NOTE: I have tried passing them in via **kwargs but was not able to use 
mysql_algorithm or mysql_lock, did not recognize those keywords.
op.create_index( index_name, table_name, columns, schema=None, 
unique=unique, quote=None, **kwargs)

Any assistance would be greatly appreciated.

Cheers
Murray

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.