[sqlalchemy] Nested Exists Query produces join

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

[sqlalchemy] Why does join() method not have join type as an argument?/Is left-join the default..

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

Re: [sqlalchemy] Re: Why does join() method not have join type as an argument?/Is left-join the default..

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,

Re: [sqlalchemy] Query a query in SQLAlchemy

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

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

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

Re: [sqlalchemy] Re: Why does join() method not have join type as an argument?/Is left-join the default..

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

Re: [sqlalchemy] Query a query in SQLAlchemy

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

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

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

[sqlalchemy] Re: Why does join() method not have join type as an argument?/Is left-join the default..

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

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

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 =

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

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

Re: [sqlalchemy] Query a query in SQLAlchemy

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

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

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:

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

>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

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

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

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

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

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

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

Re: [sqlalchemy] running py.test

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

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

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

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

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

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

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

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

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

Re: [sqlalchemy] Nested Exists Query produces join

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 =

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

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: