[sqlalchemy] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jonathan Vanasco]

that won't work as you expect.

You're applying the python character replacements to the arguments, and 
then emitting that in sql.  You'll end up with the same 
InstrumentedAttribute issue, because you're trying to operate on the Python 
column instance in the orm defintion.

the string returned by the custom compiler should be a sql function, 
something like:

"""REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13), 
'')  % compiler.process(args[0])

That would create sql from your example that reads:

SELECT REPLACE(REPLACE(REPLACE(, CHR(9), ''), CHR(10), ''), CHR(13), '') LIMIT 1;

If you just want to insert data, you could just use a python function that 
does that string cleanup.

-- 
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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jeremy Flowers]

So I tried following along with this.

1) created a file called *custfunc.py*

In it I placed this:

from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import String


class stripctrl(expression.FunctionElement):
type = String()
name = 'stripctrl'


@compiles(stripctrl)
def stripctrl_default(element, compiler, **kw):

args = list(element.clauses)
return "%s" % (compiler
   .process(args[0])
   .replace("\t", "")
   .replace("\n", "")
   .replace("\r", ""))

2) Imported class into my main code..

from custfunc import stripctrl
print(session.query( stripctrl(Jobdtl.jobdtl_cmd) ).first()

I think that's right


On Friday, 9 February 2018 20:05:59 UTC, Jonathan Vanasco wrote:
>
> I should have explained something above better...
>
> The result I assumed you wanted to achieve is "creating SQL that will 
> handle data transformations".  That would allow you to write concise python 
> that will compiled into SQL which will be evaluated on the database 
> server.  You could also use other approaches that transform data in python 
> before hitting the database server.
>
> IMHO, using a custom compiler approach is easier than trying to do 
> something with `sqlalchemy.func` because it gives you more room to 
> customize how the sql compiles under different databases.
>

-- 
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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jeremy Flowers]

I had hoped something as simple as:
  Jobdtl.jobdtl_cmd
  .replace("\t", "")
  .replace("\n", "")
  .replace("\r", "")
  .label('command'), 
Can you cast InstrumentedAttribute to a string and back?
As it stands you get:
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' 
object associated with Jobdtl.jobdtl_cmd has an attribute 'replace'

On Friday, 9 February 2018 19:18:55 UTC, Jeremy Flowers wrote:
>
> I'm aware with Oracle I could probably do something like this:
> REPLACE(REPLACE(REPLACE(o.jobdtl_cmd, CHR(9), ''), CHR(10), ''), CHR(13), 
> '') as COMMAND
>
> Is there a database agnostic way of doing this in SQLAlchemy?
>
> Would  it be possible to provide a list of numbers, like 9,10,13, that 
> would get iterated through to strip out the character and replace with an 
> empty string?
>
> I was looking under func, thinking it maybe something you could do there...
>
> Perhaps via Register function?
>
> http://docs.sqlalchemy.org/en/latest/core/functions.html#sqlalchemy.sql.functions.register_function
>
> Does anyone have an example of a register function?
>
> Many thanks again.
>

-- 
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] Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jeremy Flowers]

I'm aware with Oracle I could probably do something like this:
REPLACE(REPLACE(REPLACE(o.jobdtl_cmd, CHR(9), ''), CHR(10), ''), CHR(13), 
'') as COMMAND

Is there a database agnostic way of doing this in SQLAlchemy?

Would  it be possible to provide a list of numbers, like 9,10,13, that 
would get iterated through to strip out the character and replace with an 
empty string?

I was looking under func, thinking it maybe something you could do there...

Perhaps via Register function?
http://docs.sqlalchemy.org/en/latest/core/functions.html#sqlalchemy.sql.functions.register_function

Does anyone have an example of a register function?

Many thanks again.

-- 
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] Query a query in SQLAlchemy

[Jonathan Vanasco]

ColdFusion's "queryable results cache" is pretty unique.  I don't know of 
any database layers or ORMs that support that functionality out-of-the-box. 
They basically replace the upstream database with the results as a 
"materialized view", and act as the database server for that view.  

There is an official recipe/example for caching results with relations on 
dogpile 
(https://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.dogpile_caching),
 
but it doesn't really do what you want.  There may be a 3rd party library 
that does what you want.

-- 
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] Query a query in SQLAlchemy

[Charles Heizer]

Yes, thanks. It's not really what I want but I understand.

I'm porting a few old Coldfusion web apps and CF has a neat query object, 
if you query at data base, you are returned a query object. which you can 
show the results etc. but you can also run new queries on that query 
object. For example you may have a very large query, which you can then 
cache. Then you can run additional queries on that cache query to filter 
the results. 

Thanks,
Charles


On Friday, February 9, 2018 at 3:41:26 AM UTC-8, Simon King wrote:
>
> On Fri, Feb 9, 2018 at 6:06 AM, Charles Heizer  > wrote: 
> > Hello, does anyone know if it's possible to query a query result in 
> > SQLAlchemy? 
> > 
> > Thanks! 
> > 
>
> I'm not quite sure what you mean. If you've got a Query object with 
> some filter conditions already defined, you can add further 
> restrictions to it simply by calling its ".filter()" method. 
> Alternatively you can convert the query to a subquery and then build 
> another query around that. 
>
> http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-subqueries 
>
> Hope that helps, 
>
> Simon 
>

-- 
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] Re: Why does join() method not have join type as an argument?/Is left-join the default..

[Jeremy Flowers]

Hi Simon.
I did indeed get it working in the end. I did not know about isOuter=True. 
- That's a new one. I used the .outerjoin and was able to chain join 
followed by multiple outerjoins.
Many thanks once again for extra titbits of info. Much appreciated.
Regards, Jeremy

On Friday, 9 February 2018 17:26:33 UTC, Simon King wrote:
>
> As you say, .join() produces an inner join by default. You can specify 
> isouter=True to get a left outer join (or call the .outerjoin method 
> instead), and full=True to get a full outer join. I think you'd get a 
> cross join if you just didn't call the .join() method at all. 
>
> Simon 
>
> On Fri, Feb 9, 2018 at 4:52 PM, Jeremy Flowers 
>  wrote: 
> > I'm wondering if this is part of the secret sauce: 
> > http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.join 
> > Also I guess cross-join may be another join type. 
> > JOIN is INNER by default.. 
> > https://stackoverflow.com/a/19646594/495157 
> > 
> > 
> > On Friday, 9 February 2018 16:03:03 UTC, Jeremy Flowers wrote: 
> >> 
> >> I've got a domain model out of sqlacodegen. 
> >> I need to do a SQL a join between two tables, with no inferred 
> >> foreign-keys in the database I'm working on. 
> >> Consequently there isn't a relationship defined for the specific 
> columns I 
> >> need to join 
> >> 
> >> 1) I need a INNER JOIN for the first. 
> >> 2) But later I need multiple LEFT JOINs on other tables, I've not yet 
> >> introduced to the code.. 
> >> 
> >> (1) How would I go about doing the INNER JOIN first?: (What is the 
> >> default? left, inner etc?) 
> >> 
> >> print(session.query(Jobmst.jobmst_type, 
> >> Jobmst.jobmst_name, 
> >> Jobmst.jobmst_prntname, 
> >> Jobmst.jobmst_prntid, 
> >> Jobmst.jobmst_evntoffset, 
> >> Jobmst.evntmst_id, 
> >> Jobdtl.jobdtl_proxy, 
> >> Jobdtl.jobdtl_proxy2, 
> >> Jobdtl.jobdtl_inhagent, 
> >> Jobdtl.jobdtl_inhevent, 
> >> Jobdtl.jobdtl_cmd, 
> >> Jobdtl.jobdtl_params, 
> >> Jobdtl.jobdtl_fromtm, 
> >> Jobdtl.jobdtl_untiltm, 
> >> Jobdtl.jobdtl_timewin, 
> >> Jobdtl.jobdtl_interval, 
> >> Jobdtl.jobdtl_intervalcnt 
> >>).join(Jobdtl, Jobmst.jobmst_id==Jobdtl.jobdtl_id,) 
> >> .first()) 
> >> 
> >> 
> >> Why doesn't join have another parameter where I can say, 'inner', 
> 'left', 
> >> etc? 
> >> Per the join types listed here: 
> >> https://www.w3schools.com/sql/sql_join.asp 
> >> 
> >> I've seen stuff here 
> >> 
> >> 
> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join.params.isouter
>  
> >> And here... 
> >> 
> >> 
> http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.joinedload
>  
> >> But if I understand it correctly I'd need a relationship defined for 
> >> joinedload. 
> >> 
> >> Can someone advise. Thanks 
> >> 
> > -- 
> > 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] Re: Why does join() method not have join type as an argument?/Is left-join the default..

[Simon King]

As you say, .join() produces an inner join by default. You can specify
isouter=True to get a left outer join (or call the .outerjoin method
instead), and full=True to get a full outer join. I think you'd get a
cross join if you just didn't call the .join() method at all.

Simon

On Fri, Feb 9, 2018 at 4:52 PM, Jeremy Flowers
 wrote:
> I'm wondering if this is part of the secret sauce:
> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.join
> Also I guess cross-join may be another join type.
> JOIN is INNER by default..
> https://stackoverflow.com/a/19646594/495157
>
>
> On Friday, 9 February 2018 16:03:03 UTC, Jeremy Flowers wrote:
>>
>> I've got a domain model out of sqlacodegen.
>> I need to do a SQL a join between two tables, with no inferred
>> foreign-keys in the database I'm working on.
>> Consequently there isn't a relationship defined for the specific columns I
>> need to join
>>
>> 1) I need a INNER JOIN for the first.
>> 2) But later I need multiple LEFT JOINs on other tables, I've not yet
>> introduced to the code..
>>
>> (1) How would I go about doing the INNER JOIN first?: (What is the
>> default? left, inner etc?)
>>
>> print(session.query(Jobmst.jobmst_type,
>> Jobmst.jobmst_name,
>> Jobmst.jobmst_prntname,
>> Jobmst.jobmst_prntid,
>> Jobmst.jobmst_evntoffset,
>> Jobmst.evntmst_id,
>> Jobdtl.jobdtl_proxy,
>> Jobdtl.jobdtl_proxy2,
>> Jobdtl.jobdtl_inhagent,
>> Jobdtl.jobdtl_inhevent,
>> Jobdtl.jobdtl_cmd,
>> Jobdtl.jobdtl_params,
>> Jobdtl.jobdtl_fromtm,
>> Jobdtl.jobdtl_untiltm,
>> Jobdtl.jobdtl_timewin,
>> Jobdtl.jobdtl_interval,
>> Jobdtl.jobdtl_intervalcnt
>>).join(Jobdtl, Jobmst.jobmst_id==Jobdtl.jobdtl_id,)
>> .first())
>>
>>
>> Why doesn't join have another parameter where I can say, 'inner', 'left',
>> etc?
>> Per the join types listed here:
>> https://www.w3schools.com/sql/sql_join.asp
>>
>> I've seen stuff here
>>
>> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join.params.isouter
>> And here...
>>
>> http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.joinedload
>> But if I understand it correctly I'd need a relationship defined for
>> joinedload.
>>
>> Can someone advise. Thanks
>>
> --
> 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] Re: Why does join() method not have join type as an argument?/Is left-join the default..

[Jeremy Flowers]

I'm wondering if this is part of the secret sauce:
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.join
Also I guess cross-join may be another join type. 
JOIN is INNER by default..
https://stackoverflow.com/a/19646594/495157


On Friday, 9 February 2018 16:03:03 UTC, Jeremy Flowers wrote:
>
> I've got a domain model out of sqlacodegen.
> I need to do a SQL a join between two tables, with no inferred 
> foreign-keys in the database I'm working on.
> Consequently there isn't a relationship defined for the specific columns I 
> need to join
>
> 1) I need a INNER JOIN for the first.
> 2) But later I need multiple LEFT JOINs on other tables, I've not yet 
> introduced to the code..
>
> (1) How would I go about doing the INNER JOIN first?: (What is the 
> default? left, inner etc?)
>
> print(session.query(Jobmst.jobmst_type,
> Jobmst.jobmst_name,
> Jobmst.jobmst_prntname,
> Jobmst.jobmst_prntid,
> Jobmst.jobmst_evntoffset,
> Jobmst.evntmst_id,
> Jobdtl.jobdtl_proxy,
> Jobdtl.jobdtl_proxy2,
> Jobdtl.jobdtl_inhagent,
> Jobdtl.jobdtl_inhevent,
> Jobdtl.jobdtl_cmd,
> Jobdtl.jobdtl_params,
> Jobdtl.jobdtl_fromtm,
> Jobdtl.jobdtl_untiltm,
> Jobdtl.jobdtl_timewin,
> Jobdtl.jobdtl_interval,
> Jobdtl.jobdtl_intervalcnt
>).join(Jobdtl, Jobmst.jobmst_id==Jobdtl.jobdtl_id,)
> .first())
>
>
> Why doesn't join have another parameter where I can say, 'inner', 'left', 
> etc?
> Per the join types listed here:
> https://www.w3schools.com/sql/sql_join.asp
>
> I've seen stuff here
>
> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join.params.isouter
> And here...
>
> http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.joinedload
> But if I understand it correctly I'd need a relationship defined for 
> joinedload.
>
> Can someone advise. Thanks
>
>

-- 
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] Why does join() method not have join type as an argument?/Is left-join the default..

[Jeremy Flowers]

I've got a domain model out of sqlacodegen.
I need to do a SQL a join between two tables, with no inferred foreign-keys 
in the database I'm working on.
Consequently there isn't a relationship defined for the specific columns I 
need to join

1) I need a INNER JOIN for the first.
2) But later I need multiple LEFT JOINs on other tables, I've not yet 
introduced to the code..

(1) How would I go about doing the INNER JOIN first?: (What is the default? 
left, inner etc?)

print(session.query(Jobmst.jobmst_type,
Jobmst.jobmst_name,
Jobmst.jobmst_prntname,
Jobmst.jobmst_prntid,
Jobmst.jobmst_evntoffset,
Jobmst.evntmst_id,
Jobdtl.jobdtl_proxy,
Jobdtl.jobdtl_proxy2,
Jobdtl.jobdtl_inhagent,
Jobdtl.jobdtl_inhevent,
Jobdtl.jobdtl_cmd,
Jobdtl.jobdtl_params,
Jobdtl.jobdtl_fromtm,
Jobdtl.jobdtl_untiltm,
Jobdtl.jobdtl_timewin,
Jobdtl.jobdtl_interval,
Jobdtl.jobdtl_intervalcnt
   ).join(Jobdtl, Jobmst.jobmst_id==Jobdtl.jobdtl_id,)
.first())


Why doesn't join have another parameter where I can say, 'inner', 'left', 
etc?
Per the join types listed here:
https://www.w3schools.com/sql/sql_join.asp

I've seen stuff here
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join.params.isouter
And here...
http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.joinedload
But if I understand it correctly I'd need a relationship defined for 
joinedload.

Can someone advise. Thanks

-- 
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] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Mike Bayer]

On Fri, Feb 9, 2018 at 6:45 AM, Jeremy Flowers
 wrote:
> Also this didn't work for me:
>
> print(session.query(Jobmst)
>  .values(Jobmst.jobmst_type,
>  Jobmst.jobmst_name)
>  .first()
>   )
>
> yields:
> AttributeError: 'generator' object has no attribute 'first'

yes, as mentioned earlier, "values()" is a weird method from earlier
days, it produces an iterator directly:

for row in query.values(a, b, c):
   # etc

with_entities() is more modern and fits into the usual model where you
get a Query you can all all() upon


>
> Swapping first to earlier in the chain like so:
>
> print(session.query(Jobmst).first()
>  .values(Jobmst.jobmst_type,
>  Jobmst.jobmst_name)
>   )
>
> yields:
>
>
> AttributeError: 'Jobmst' object has no attribute 'values'

OK the confusion here is being aware of what kinds of objects
different methods return.   The ORM tutorial is a good place to start
to get a feel for it.   The first() / one() / and all() methods
execute the SQL query and return you a result - when you call those,
you no longer have a Query object, you've got your data back.   The
values() method, which doesn't fit into any of this very well, will
execute the SQL query and also return you a result, but it returns it
as an iterator, but still, when you call it, you no longer have a
Query object.If you used the with_entities() method, when you're
done calling that, you still have a Query object and you can still do
more things with it.


>
> I get confused as well, thinking that *columns (from here) infers a list of
> some kind.
>
> So if I don't have a columns variable assigned to a list, I should be able
> to embed one directly with this sort of syntax values([c1,c2])
> But you indicate something like values(c1,c2).
> So that confuses me as a newbie to Python (my background being predominantly
> Java, Groovy, Typescript, Javascript, Angular 2-4, Ionic 2-3).
> The * prefix immediately clicked for my from somewhere else. Maybe
> academically from reading about C++.. Something like a pointer to the whole
> array.

My impression is that yes the "*" was inspired by C notation like you
see for printf, but I'm sure the historians would know more about
that.  Google keeps returning this as the top hit for "python variadic
arguments": 
https://www.saltycrane.com/blog/2008/01/how-to-use-args-and-kwargs-in-python/



>
> I've been brushing up on things like *args *kwargs too - so understand their
> purpose.
> Duck typing, passing parameters as a dictionary is known to me from Groovy.
> It still confounds me though knowing what type to pass in the values().
> Have been reading here about it:
>
>
>
>
> On Friday, 9 February 2018 09:48:51 UTC, Jeremy Flowers wrote:
>>
>> I was thinking about this overnight..
>> Do values relates specifically to inserts and updates, not selects/deletes
>> perhaps, which would correlate to SQL syntax.
>> If, it would make sense to indicate that in the documentation
>>
>> On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote:
>>>
>>> I've seen you can do things like this:
>>> fields = ['jobmst_type', 'jobmst_name']
>>>  print(session.query(Jobmst).options(load_only(*fields)).first())
>>>
>>> But according to the documentation, you should be able to do something
>>> with Query values() too.
>>> But once again I seem to be dumbfounded by the syntatic sugar.
>>>
>>> What data type does the list of values need?
>>> Is there a way to introspect that ahead of time?
>>>
>>> Thought I was onto something with .base_columns, but that didn't work
>>> either...
>>>
>>> I ended up with something like an instrumentalAttributes mismatch.
>>> print(session.query(Jobmst)
>>>  .values([Jobmst.jobmst_type.base_columns,
>>>   Jobmst.jobmst_name.base_columns
>>>   ])
>>>  .first()
>>>   )
>>>
>>> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
>>> entity expected - got '[{Column('jobmst_type', Numeric(scale=0,
>>> asdecimal=False), table=, nullable=False)}, {Column('jobmst_name',
>>> String(length=256), table=, nullable=False)}]'
>>>
>>> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type,
>>> Jobmst.jobmst_name) too - but looking to understand what values expects.
>>> Mike, Can documentation not specify type?
>
> --
> 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 

Re: [sqlalchemy] Nested Exists Query produces join

[Mike Bayer]

On Fri, Feb 9, 2018 at 5:46 AM,   wrote:
> I'm trying to do a Many-to-Many query for all rows in `AModel` that don't
> have a relationship with every `BModel` row.
>
> # The schema that looks like this:
> class AModel(Base):
> __tablename__ = 'amodels'
> id = Column(Integer, primary_key=True)
> relationship('BModel', secondary='abjoinmodels',
> back_populates='relateds')
>
> class BModel(Base):
> __tablename__ = 'bmodels'
> id = Column(Integer, primary_key=True)
> relationship('AModel', secondary='abjoinmodels',
> back_populates='relateds')
>
> class ABJoinModel(Base):
> __tablename__ = 'abjoinmodels'
> a_id = Column(Integer, ForeignKey('amodels.id'), primary_key=True)
> b_id = Column(Integer, ForeignKey('bmodels.id'), primary_key=True)
>
>
> # And here's some mock data:
> b_instances = [BModel(), BModel()]
> a_instances = [
> AModel(relateds=b_instances),
> AModel(relateds=b_instances[0:1]),
> AModel(),
> ]
> dbsession.add_all(a_instances)
> dbsession.flush()
>
>
> # Now this query extracts all the rows appropriately:
> # -- at least one relationship missing
> # SELECT a.id
> # FROM a
> # WHERE EXISTS (
> #   SELECT 1
> #   FROM b
> #   WHERE NOT EXISTS (
> # SELECT 1
> # FROM ab_join
> # WHERE a.id = ab_join.a_id AND b.id = ab_join.b_id
> #   )
> # );
> # -- produces 2, 3
>
> # However, trying to map this to SQLAlchemy, I get this:
> query = Query(AModel).filter(
> Query(BModel).filter(
> Query(AModel.relateds.prop.secondary).filter(
> and_(
> BModel.relateds.prop.secondary.c.a_id == AModel.id,
> BModel.relateds.prop.secondary.c.b_id == BModel.id,
> )
> ).exists()
> ).exists()
> )

for this you need to use correlate() to give it a hint where it is correlating:

q = s.query(AModel.id).filter(
s.query(BModel).filter(
~s.query(ABJoinModel).filter(
and_(AModel.id == ABJoinModel.a_id, BModel.id)
).correlate(AModel, BModel).exists()
).exists()
)


produces:

SELECT amodels.id AS amodels_id
FROM amodels
WHERE EXISTS (SELECT 1
FROM bmodels
WHERE NOT (EXISTS (SELECT 1
FROM abjoinmodels
WHERE amodels.id = abjoinmodels.a_id AND bmodels.id)))


looks right




>
> # which produces the following statement:
> # SELECT amodels.id AS amodels_id
> # FROM amodels
> # WHERE EXISTS (SELECT 1
> # FROM bmodels
> # WHERE EXISTS (SELECT 1
> # FROM abjoinmodels, amodels
> # WHERE abjoinmodels.a_id = amodels.id AND abjoinmodels.b_id = bmodels.id))
>
> Query.with_session(dbsession).all()
> # []
>
> And, therefore doesn't return ANY rows.
>
> How should I be constructing this? I'm trying to generalize this, so I'd
> also like to be able to do this same query on a One-to-Many mapping too
> (where B.a_id = Column(Integer, ForeignKey('amodels.id'))).
>
> Thanks!
> Devin
>
> --
> 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] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Simon King]

Out of interest, why would you not write:

print(
session.query(Jobmst.jobmst_type, Jobmst.jobmst_name)
.first()
)

The call to with_entities seems unnecessary.

Simon

On Fri, Feb 9, 2018 at 12:27 PM, Jeremy Flowers
 wrote:
> And that can be simplified to:
> print(session.query()
>  .with_entities(Jobmst.jobmst_type, Jobmst.jobmst_name)
>  .first()
>   )
>
> On Friday, 9 February 2018 12:21:37 UTC, Jeremy Flowers wrote:
>>
>> Hi Simon.
>> Instead of using values(), I did this.
>> print(session.query(Jobmst)
>>  .with_entities(Jobmst.jobmst_type, Jobmst.jobmst_name)
>>  .first()
>>   )
>>
>> and that worked a treat too.
>> Thanks.
>>
>>
>> On Friday, 9 February 2018 11:58:18 UTC, Simon King wrote:
>>>
>>> The chaining-friendly method you are looking for is probably
>>> with_entities():
>>>
>>>
>>> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_entities
>>>
>>> Simon
>>>
>>> On Fri, Feb 9, 2018 at 11:52 AM, Jeremy Flowers
>>>  wrote:
>>> > From watching your videos I always thought some sort of query object
>>> > would
>>> > be returned from the query() operation, so method chaining (aka fluent
>>> > interface) could always be performed.. in a way that is analogous to
>>> > JQuery.
>>> > But what I'm doing seems to be breaking that paradigm. What am I doing
>>> > wrong
>>> > or not getting?
>>> >
>>> > On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote:
>>> >>
>>> >> I've seen you can do things like this:
>>> >> fields = ['jobmst_type', 'jobmst_name']
>>> >>  print(session.query(Jobmst).options(load_only(*fields)).first())
>>> >>
>>> >> But according to the documentation, you should be able to do something
>>> >> with Query values() too.
>>> >> But once again I seem to be dumbfounded by the syntatic sugar.
>>> >>
>>> >> What data type does the list of values need?
>>> >> Is there a way to introspect that ahead of time?
>>> >>
>>> >> Thought I was onto something with .base_columns, but that didn't work
>>> >> either...
>>> >>
>>> >> I ended up with something like an instrumentalAttributes mismatch.
>>> >> print(session.query(Jobmst)
>>> >>  .values([Jobmst.jobmst_type.base_columns,
>>> >>   Jobmst.jobmst_name.base_columns
>>> >>   ])
>>> >>  .first()
>>> >>   )
>>> >>
>>> >> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
>>> >> entity expected - got '[{Column('jobmst_type', Numeric(scale=0,
>>> >> asdecimal=False), table=, nullable=False)},
>>> >> {Column('jobmst_name',
>>> >> String(length=256), table=, nullable=False)}]'
>>> >>
>>> >> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type,
>>> >> Jobmst.jobmst_name) too - but looking to understand what values
>>> >> expects.
>>> >> Mike, Can documentation not specify type?
>>> >
>>> > --
>>> > 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 sqlalchemy@googlegroups.com.
Visit this group at 

Re: [sqlalchemy] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Jeremy Flowers]

And that can be simplified to:
print(session.query()
 .with_entities(Jobmst.jobmst_type, Jobmst.jobmst_name)
 .first()
  )

On Friday, 9 February 2018 12:21:37 UTC, Jeremy Flowers wrote:
>
> Hi Simon.
> Instead of using values(), I did this.
> print(session.query(Jobmst)
>  .with_entities(Jobmst.jobmst_type, Jobmst.jobmst_name)
>  .first()
>   )
>
> and that worked a treat too. 
> Thanks.
>
>
> On Friday, 9 February 2018 11:58:18 UTC, Simon King wrote:
>>
>> The chaining-friendly method you are looking for is probably 
>> with_entities(): 
>>
>>
>> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_entities
>>  
>>
>> Simon 
>>
>> On Fri, Feb 9, 2018 at 11:52 AM, Jeremy Flowers 
>>  wrote: 
>> > From watching your videos I always thought some sort of query object 
>> would 
>> > be returned from the query() operation, so method chaining (aka fluent 
>> > interface) could always be performed.. in a way that is analogous to 
>> JQuery. 
>> > But what I'm doing seems to be breaking that paradigm. What am I doing 
>> wrong 
>> > or not getting? 
>> > 
>> > On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote: 
>> >> 
>> >> I've seen you can do things like this: 
>> >> fields = ['jobmst_type', 'jobmst_name'] 
>> >>  print(session.query(Jobmst).options(load_only(*fields)).first()) 
>> >> 
>> >> But according to the documentation, you should be able to do something 
>> >> with Query values() too. 
>> >> But once again I seem to be dumbfounded by the syntatic sugar. 
>> >> 
>> >> What data type does the list of values need? 
>> >> Is there a way to introspect that ahead of time? 
>> >> 
>> >> Thought I was onto something with .base_columns, but that didn't work 
>> >> either... 
>> >> 
>> >> I ended up with something like an instrumentalAttributes mismatch. 
>> >> print(session.query(Jobmst) 
>> >>  .values([Jobmst.jobmst_type.base_columns, 
>> >>   Jobmst.jobmst_name.base_columns 
>> >>   ]) 
>> >>  .first() 
>> >>   ) 
>> >> 
>> >> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped 
>> >> entity expected - got '[{Column('jobmst_type', Numeric(scale=0, 
>> >> asdecimal=False), table=, nullable=False)}, 
>> {Column('jobmst_name', 
>> >> String(length=256), table=, nullable=False)}]' 
>> >> 
>> >> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type, 
>> >> Jobmst.jobmst_name) too - but looking to understand what values 
>> expects. 
>> >> Mike, Can documentation not specify type? 
>> > 
>> > -- 
>> > 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] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Jeremy Flowers]

Hi Simon.
Instead of using values(), I did this.
print(session.query(Jobmst)
 .with_entities(Jobmst.jobmst_type, Jobmst.jobmst_name)
 .first()
  )

and that worked a treat too. 
Thanks.


On Friday, 9 February 2018 11:58:18 UTC, Simon King wrote:
>
> The chaining-friendly method you are looking for is probably 
> with_entities(): 
>
>
> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_entities
>  
>
> Simon 
>
> On Fri, Feb 9, 2018 at 11:52 AM, Jeremy Flowers 
>  wrote: 
> > From watching your videos I always thought some sort of query object 
> would 
> > be returned from the query() operation, so method chaining (aka fluent 
> > interface) could always be performed.. in a way that is analogous to 
> JQuery. 
> > But what I'm doing seems to be breaking that paradigm. What am I doing 
> wrong 
> > or not getting? 
> > 
> > On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote: 
> >> 
> >> I've seen you can do things like this: 
> >> fields = ['jobmst_type', 'jobmst_name'] 
> >>  print(session.query(Jobmst).options(load_only(*fields)).first()) 
> >> 
> >> But according to the documentation, you should be able to do something 
> >> with Query values() too. 
> >> But once again I seem to be dumbfounded by the syntatic sugar. 
> >> 
> >> What data type does the list of values need? 
> >> Is there a way to introspect that ahead of time? 
> >> 
> >> Thought I was onto something with .base_columns, but that didn't work 
> >> either... 
> >> 
> >> I ended up with something like an instrumentalAttributes mismatch. 
> >> print(session.query(Jobmst) 
> >>  .values([Jobmst.jobmst_type.base_columns, 
> >>   Jobmst.jobmst_name.base_columns 
> >>   ]) 
> >>  .first() 
> >>   ) 
> >> 
> >> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped 
> >> entity expected - got '[{Column('jobmst_type', Numeric(scale=0, 
> >> asdecimal=False), table=, nullable=False)}, 
> {Column('jobmst_name', 
> >> String(length=256), table=, nullable=False)}]' 
> >> 
> >> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type, 
> >> Jobmst.jobmst_name) too - but looking to understand what values 
> expects. 
> >> Mike, Can documentation not specify type? 
> > 
> > -- 
> > 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] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Simon King]

The chaining-friendly method you are looking for is probably with_entities():

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_entities

Simon

On Fri, Feb 9, 2018 at 11:52 AM, Jeremy Flowers
 wrote:
> From watching your videos I always thought some sort of query object would
> be returned from the query() operation, so method chaining (aka fluent
> interface) could always be performed.. in a way that is analogous to JQuery.
> But what I'm doing seems to be breaking that paradigm. What am I doing wrong
> or not getting?
>
> On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote:
>>
>> I've seen you can do things like this:
>> fields = ['jobmst_type', 'jobmst_name']
>>  print(session.query(Jobmst).options(load_only(*fields)).first())
>>
>> But according to the documentation, you should be able to do something
>> with Query values() too.
>> But once again I seem to be dumbfounded by the syntatic sugar.
>>
>> What data type does the list of values need?
>> Is there a way to introspect that ahead of time?
>>
>> Thought I was onto something with .base_columns, but that didn't work
>> either...
>>
>> I ended up with something like an instrumentalAttributes mismatch.
>> print(session.query(Jobmst)
>>  .values([Jobmst.jobmst_type.base_columns,
>>   Jobmst.jobmst_name.base_columns
>>   ])
>>  .first()
>>   )
>>
>> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
>> entity expected - got '[{Column('jobmst_type', Numeric(scale=0,
>> asdecimal=False), table=, nullable=False)}, {Column('jobmst_name',
>> String(length=256), table=, nullable=False)}]'
>>
>> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type,
>> Jobmst.jobmst_name) too - but looking to understand what values expects.
>> Mike, Can documentation not specify type?
>
> --
> 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] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Simon King]

The main point you should take from Mike's original reply is:

.values() is a weird method and it's pretty old, usually people
just set the columns up front

You probably shouldn't use it.

On Fri, Feb 9, 2018 at 11:45 AM, Jeremy Flowers
 wrote:
> Also this didn't work for me:
>
> print(session.query(Jobmst)
>  .values(Jobmst.jobmst_type,
>  Jobmst.jobmst_name)
>  .first()
>   )
>
> yields:
> AttributeError: 'generator' object has no attribute 'first'

This is because .first() is a method of Query, but .values() doesn't
return a Query, it returns an iterator (in this case, a generator).

>
> Swapping first to earlier in the chain like so:
>
> print(session.query(Jobmst).first()
>  .values(Jobmst.jobmst_type,
>  Jobmst.jobmst_name)
>   )
>
> yields:
>
>
> AttributeError: 'Jobmst' object has no attribute 'values'
>

This is because Query.first() returns an instance of the thing that
you are querying for (in this case Jobmst).

> I get confused as well, thinking that *columns (from here) infers a list of
> some kind.
>
> So if I don't have a columns variable assigned to a list, I should be able
> to embed one directly with this sort of syntax values([c1,c2])
> But you indicate something like values(c1,c2).
> So that confuses me as a newbie to Python (my background being predominantly
> Java, Groovy, Typescript, Javascript, Angular 2-4, Ionic 2-3).

If your parameters are in a list, you can call a function like this:

params = [1, 2, 3, 4]
function(*params)
# is equivalent to function(1, 2, 3, 4)

I think a similar idea in Javascript is

params = [1, 2, 3, 4]
function.call(null, params)

(my JS is rusty though, so that might be wrong)

> The * prefix immediately clicked for my from somewhere else. Maybe
> academically from reading about C++.. Something like a pointer to the whole
> array.
>
> I've been brushing up on things like *args *kwargs too - so understand their
> purpose.
> Duck typing, passing parameters as a dictionary is known to me from Groovy.
> It still confounds me though knowing what type to pass in the values().
> Have been reading here about it:
>
>
>
>
> On Friday, 9 February 2018 09:48:51 UTC, Jeremy Flowers wrote:
>>
>> I was thinking about this overnight..
>> Do values relates specifically to inserts and updates, not selects/deletes
>> perhaps, which would correlate to SQL syntax.
>> If, it would make sense to indicate that in the documentation
>>
>> On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote:
>>>
>>> I've seen you can do things like this:
>>> fields = ['jobmst_type', 'jobmst_name']
>>>  print(session.query(Jobmst).options(load_only(*fields)).first())
>>>
>>> But according to the documentation, you should be able to do something
>>> with Query values() too.
>>> But once again I seem to be dumbfounded by the syntatic sugar.
>>>
>>> What data type does the list of values need?
>>> Is there a way to introspect that ahead of time?
>>>
>>> Thought I was onto something with .base_columns, but that didn't work
>>> either...
>>>
>>> I ended up with something like an instrumentalAttributes mismatch.
>>> print(session.query(Jobmst)
>>>  .values([Jobmst.jobmst_type.base_columns,
>>>   Jobmst.jobmst_name.base_columns
>>>   ])
>>>  .first()
>>>   )
>>>
>>> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
>>> entity expected - got '[{Column('jobmst_type', Numeric(scale=0,
>>> asdecimal=False), table=, nullable=False)}, {Column('jobmst_name',
>>> String(length=256), table=, nullable=False)}]'
>>>
>>> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type,
>>> Jobmst.jobmst_name) too - but looking to understand what values expects.
>>> Mike, Can documentation not specify type?
>
> --
> 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 

[sqlalchemy] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Jeremy Flowers]

>From watching your videos I always thought some sort of query object would 
be returned from the query() operation, so method chaining (aka fluent 
interface) could always be performed.. in a way that is analogous to 
JQuery. But what I'm doing seems to be breaking that paradigm. What am I 
doing wrong or not getting?

On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote:
>
> I've seen you can do things like this:
> fields = ['jobmst_type', 'jobmst_name']
>  print(session.query(Jobmst).options(load_only(*fields)).first())
>
> But according to the documentation, you should be able to do something 
> with Query values() 
> 
>  
> too.
> But once again I seem to be dumbfounded by the syntatic sugar.
>
> What data type does the list of values need?
> *Is there a way to introspect that ahead of time?*
>
> Thought I was onto something with *.base_columns*, but that didn't work 
> either...
>
> I ended up with something like an instrumentalAttributes mismatch.
> print(session.query(Jobmst)
>  .values([Jobmst.jobmst_type.base_columns,
>   Jobmst.jobmst_name.base_columns
>   ])
>  .first()
>   )
>
> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped 
> entity expected - got '[{Column('jobmst_type', Numeric(scale=0, 
> asdecimal=False), table=, nullable=False)}, {Column('jobmst_name', 
> String(length=256), table=, nullable=False)}]'
>
> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type, 
> Jobmst.jobmst_name) too - but looking to understand what values expects.
> Mike, Can documentation not specify type?
>

-- 
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] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Jeremy Flowers]

Also this didn't work for me:

print(session.query(Jobmst)
 .values(Jobmst.jobmst_type,
 Jobmst.jobmst_name)
 .first()
  )

yields:
AttributeError: 'generator' object has no attribute 'first'

Swapping first to earlier in the chain like so:

print(session.query(Jobmst).first()
 .values(Jobmst.jobmst_type,
 Jobmst.jobmst_name)
  )

yields:


AttributeError: 'Jobmst' object has no attribute 'values'

I get confused as well, thinking that *columns (from here 
)
 
infers a list of some kind. 

So if I don't have a columns variable assigned to a list, I should be able 
to embed one directly with this sort of syntax values([c1,c2])
But you indicate something like values(c1,c2). 
So that confuses me as a newbie to Python (my background being 
predominantly Java, Groovy, Typescript, Javascript, Angular 2-4, Ionic 2-3).
The * prefix immediately clicked for my from somewhere else. Maybe 
academically from reading about C++.. Something like a pointer to the whole 
array.

I've been brushing up on things like *args *kwargs too - so understand 
their purpose.
Duck typing, passing parameters as a dictionary is known to me from Groovy.
It still confounds me though knowing what type to pass in the values().
Have been reading here  about 
it:




On Friday, 9 February 2018 09:48:51 UTC, Jeremy Flowers wrote:
>
> I was thinking about this overnight.. 
> Do *values* relates specifically to inserts and updates, not 
> selects/deletes perhaps, which would correlate to SQL syntax. 
> If, it would make sense to indicate that in the documentation
>
> On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote:
>>
>> I've seen you can do things like this:
>> fields = ['jobmst_type', 'jobmst_name']
>>  print(session.query(Jobmst).options(load_only(*fields)).first())
>>
>> But according to the documentation, you should be able to do something 
>> with Query values() 
>> 
>>  
>> too.
>> But once again I seem to be dumbfounded by the syntatic sugar.
>>
>> What data type does the list of values need?
>> *Is there a way to introspect that ahead of time?*
>>
>> Thought I was onto something with *.base_columns*, but that didn't work 
>> either...
>>
>> I ended up with something like an instrumentalAttributes mismatch.
>> print(session.query(Jobmst)
>>  .values([Jobmst.jobmst_type.base_columns,
>>   Jobmst.jobmst_name.base_columns
>>   ])
>>  .first()
>>   )
>>
>> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped 
>> entity expected - got '[{Column('jobmst_type', Numeric(scale=0, 
>> asdecimal=False), table=, nullable=False)}, {Column('jobmst_name', 
>> String(length=256), table=, nullable=False)}]'
>>
>> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type, 
>> Jobmst.jobmst_name) too - but looking to understand what values expects.
>> Mike, Can documentation not specify type?
>>
>

-- 
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] Query a query in SQLAlchemy

[Simon King]

On Fri, Feb 9, 2018 at 6:06 AM, Charles Heizer  wrote:
> Hello, does anyone know if it's possible to query a query result in
> SQLAlchemy?
>
> Thanks!
>

I'm not quite sure what you mean. If you've got a Query object with
some filter conditions already defined, you can add further
restrictions to it simply by calling its ".filter()" method.
Alternatively you can convert the query to a subquery and then build
another query around that.

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-subqueries

Hope that helps,

Simon

-- 
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] running py.test

[Simon King]

On Wed, Feb 7, 2018 at 2:23 PM, su-sa  wrote:
> Hallo everyone,
>
> I am trying to run all the pytest of sqlalchemy. But I always get the error
> - SQLAlchemy requires Mock as of version 0.8.2. I already have the mock
> library installed. Could somebody please help me to solve the problem.
>

How have you installed these libraries? Are you using virtualenv? If
so, are pytest and mock both installed in the virtualenv?

Simon

-- 
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] Nested Exists Query produces join

[devin]

I'm trying to do a Many-to-Many query for all rows in `AModel` that don't 
have a relationship with every `BModel` row.

# The schema that looks like this:
class AModel(Base):
__tablename__ = 'amodels'
id = Column(Integer, primary_key=True)
relationship('BModel', secondary='abjoinmodels', 
back_populates='relateds')

class BModel(Base):
__tablename__ = 'bmodels'
id = Column(Integer, primary_key=True)
relationship('AModel', secondary='abjoinmodels', 
back_populates='relateds')

class ABJoinModel(Base):
__tablename__ = 'abjoinmodels'
a_id = Column(Integer, ForeignKey('amodels.id'), primary_key=True)
b_id = Column(Integer, ForeignKey('bmodels.id'), primary_key=True)


# And here's some mock data:
b_instances = [BModel(), BModel()]
a_instances = [
AModel(relateds=b_instances),
AModel(relateds=b_instances[0:1]),
AModel(),
]
dbsession.add_all(a_instances)
dbsession.flush()


# Now this query extracts all the rows appropriately:
# -- at least one relationship missing
# SELECT a.id
# FROM a
# WHERE EXISTS (
#   SELECT 1
#   FROM b 
#   WHERE NOT EXISTS (
# SELECT 1
# FROM ab_join
# WHERE a.id = ab_join.a_id AND b.id = ab_join.b_id
#   )
# );
# -- produces 2, 3

# However, trying to map this to SQLAlchemy, I get this:
query = Query(AModel).filter(
Query(BModel).filter(
Query(AModel.relateds.prop.secondary).filter(
and_(
BModel.relateds.prop.secondary.c.a_id == AModel.id,
BModel.relateds.prop.secondary.c.b_id == BModel.id,
)
).exists()
).exists()
)

# which produces the following statement:
# SELECT amodels.id AS amodels_id
# FROM amodels
# WHERE EXISTS (SELECT 1
# FROM bmodels
# WHERE EXISTS (SELECT 1
# FROM abjoinmodels, amodels
# WHERE abjoinmodels.a_id = amodels.id AND abjoinmodels.b_id = bmodels.id))

Query.with_session(dbsession).all() 
# []

And, therefore doesn't return ANY rows.

How should I be constructing this? I'm trying to generalize this, so I'd 
also like to be able to do this same query on a One-to-Many mapping too 
(where B.a_id = Column(Integer, ForeignKey('amodels.id'))).

Thanks!
Devin

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] MySQL's sql_mode (ORM)

[Simon King]

On Tue, Feb 6, 2018 at 7:08 PM,   wrote:
> If I were to go into my MySQL DB and
>
> mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES';
>
> would that have the same effect? I find the MySQL documentation somewhat
> lacking on that topic. What are the scope and lifetime of the above vs.
> using a listener as suggested by Michael?
>

I'm pretty sure variables set using "SET GLOBAL" will not survive a
database restart. You'd need to specify it on the server command line
or config file if you wanted it to be persistent.

Simon

-- 
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] Re: Ways with SQLAlchemy ORM to get back some of the columns for a table

[Jeremy Flowers]

I was thinking about this overnight.. 
Do *values* relates specifically to inserts and updates, not 
selects/deletes perhaps, which would correlate to SQL syntax. 
If, it would make sense to indicate that in the documentation

On Thursday, 8 February 2018 20:29:45 UTC, Jeremy Flowers wrote:
>
> I've seen you can do things like this:
> fields = ['jobmst_type', 'jobmst_name']
>  print(session.query(Jobmst).options(load_only(*fields)).first())
>
> But according to the documentation, you should be able to do something 
> with Query values() 
> 
>  
> too.
> But once again I seem to be dumbfounded by the syntatic sugar.
>
> What data type does the list of values need?
> *Is there a way to introspect that ahead of time?*
>
> Thought I was onto something with *.base_columns*, but that didn't work 
> either...
>
> I ended up with something like an instrumentalAttributes mismatch.
> print(session.query(Jobmst)
>  .values([Jobmst.jobmst_type.base_columns,
>   Jobmst.jobmst_name.base_columns
>   ])
>  .first()
>   )
>
> sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped 
> entity expected - got '[{Column('jobmst_type', Numeric(scale=0, 
> asdecimal=False), table=, nullable=False)}, {Column('jobmst_name', 
> String(length=256), table=, nullable=False)}]'
>
> BTW: I'm aware of querying with things like query(Jobmst.jobmst_type, 
> Jobmst.jobmst_name) too - but looking to understand what values expects.
> Mike, Can documentation not specify type?
>

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