[sqlalchemy] Security Implications of Directly Executing Queries Compiled to Strings

[Seth P]

Someone wrote a server that blindly executes SQL strings? Oy. Google around for 
why that is a terrible idea.

-- 
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: ORM: read from view but write into separate table

[Seth P]

My 2c about table design (not SQLA): I would suggest having a child with 
just id, a parent table with just id and child_id (foreign key to 
child.id), and then store additional "versioned" data in separate 
parent_history and child_history tables that have foreign keys only to 
their respective parent and child tables. If using Postgresql, each of 
parent_history and child_history would have an id (pointing back to 
parent.id or child.id, respectively) and a tsrange, and an EXCLUDE USING 
gist (id WITH =, range WITH &&) constraint 
(see https://www.postgresql.org/docs/9.4/rangetypes.html).

On Wednesday, December 5, 2018 at 12:07:01 PM UTC-5, Stanislav Lobanov 
wrote:
>
> I'm very sorry but I absolutely forgot to mention a problem with primary 
> keys.
>
> As i said, all objects in the database have id, start and end primary keys.
>
> CREATE TABLE parent (
> id SERIAL NOT NULL, 
> start TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
> end TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
> data VARCHAR, 
> child_id INTEGER, 
> PRIMARY KEY (id, start, end)
> )
>
> CREATE TABLE child (
> id SERIAL NOT NULL, 
> start TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
> end TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
> data VARCHAR, 
> PRIMARY KEY (id, start, end)
> )
>
> Using single table versioning pattern i'm stuck with relationship 
> definition:
>
> 1. Relation always should relate to only "current" rows
> 2. Relation is represented as a single scalar column (child_id in this 
> example), without extra start and end foreign key columns.
>
> This is my models:
>
>
> class Parent(Base):
> __tablename__ = 'parent'
> id = Column(Integer, primary_key=True, autoincrement=True)
> start = Column(DateTime, primary_key=True)
> end = Column(DateTime, primary_key=True)
> data = Column(String)
>
> child_n = Column(Integer, ForeignKey('child.n'))
> child = relationship("Child", backref=backref('parent', uselist=False))
>
>
> class Child(Versioned, Base):
> __tablename__ = 'child'
>
> id = Column(Integer, primary_key=True, autoincrement=True)
> start = Column(DateTime, primary_key=True)
> end = Column(DateTime, primary_key=True)
> data = Column(String)
>
>
> Of course this models definition gives me various errors about primary and 
> foreign keys, because foreign key must consist of (id, start, end).
>
> But, as i said, we consider that relation always targets to current rows 
> only. We are avoiding cascade re-assigning of changed children to parents.
>
> "primaryjoin" can be used for that:
>
> relation(..., primaryjoin="and_(Parent.child_id=Child.id, 
> func.now().between(Child.start, Child.end))"
>
> Something like that. I tried that approach and it did not work for me 
> (maybe i did not configured mappers or relations correctly — i was able to 
> read, but not write.)
>
> So, the point is that i do not know how to make it work in a way that:
>
> 1. when reading, relation is joined on func.now() between start and end
> 2. when writing to this relation new version of child is created with new 
> start and end, *preserving id.*
>
> Let me show data in the tables to explain myself better:
>
> Parent
> id  |  start | end | data| child_id
> 1   |  2018-01-01| 2018-01-11  | c1  | 1   # -> this 
> should reference to child with id=1 and func.now() between start and end
>
> Child
> id  |  start | end | data   |
> 1   |  2018-01-01| 2018-01-02  | child 1|
> 1   |  2018-01-02| 2018-01-11  | child 1.1  | # -> this row considered 
> current if func.now() is 2018-01-05 for example
>
>
>
> I want to avoid declaring complex code/join conditions, that is why i 
> thought about using views or dual-tables-versioning.
>
> среда, 5 декабря 2018 г., 18:13:11 UTC+3 пользователь Mike Bayer написал:
>>
>> On Wed, Dec 5, 2018 at 2:42 AM Stanislav Lobanov  
>> wrote: 
>> > 
>> > First of all, thank you for such a detailed answer. Thank you for 
>> sharing your experience and continued support. 
>> > 
>> > Now i understand that using database views is not the best approach. In 
>> the previous project we used an approach with Query objects that were using 
>> WHERE statements to filter needed objects and it worked well. 
>> > 
>> > The problem i want to workaround is that using primary key of (id, 
>> start, end) prevents me from using (lazy loaded) relations out of the box. 
>> > 
>> > Using examples from official sqlalchemy documentation, please think of 
>> Parent and Child as a related business entities: 
>> > 
>> > class Parent(Base): 
>> > __tablename__ = 'parent' 
>> > id = Column(Integer, primary_key=True) 
>> > child_id = Column(Integer, ForeignKey('child.id')) 
>> > child = relationship("Child", back_populates="parents") 
>> > 
>> > class Child(Base): 
>> > __tablename__ = 'child' 
>> > id = Column(Integer, primary_key=True) 
>> > parents = relationship("Parent", back_populates="child") 
>> > 
>> > 
>> > 
>> > There are 

Re: [sqlalchemy] Re: Relationship to child with 2 foreginKey from same Parent column

[Seth P]

In relationship(), foreign_keys refers to the field in the source table, 
not the destination.

-- 
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] SQLAlchemy and Python 3.7 data classes

[Seth P]

I don't have a well-formulated question, but reading PEP 557 
(https://www.python.org/dev/peps/pep-0557/) made me wonder if SQLAlchemy 
declarative models would/should in some way be Python 3.7 data classes.

-- 
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: SQLAlchemy 1.2.0 released

[Seth P]

Perhaps this isn't the right place to ask, but do you know when 
https://anaconda.org/conda-forge/sqlalchemy will be updated to 1.2.0?

-- 
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] Postgres 10 identity keyword

[Seth P]

Apologies if I missed something, but does SQLAlchemy (1.2.0?) support the new 
Postgres 10 identity keyword 
(https://blog.2ndquadrant.com/postgresql-10-identity-columns/)?

-- 
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: DELETE CASCADE does not work when foreing key can be nulleable

[Seth P]

You're naming both tables 'parent_table'. Perhaps that is messing things up?

-- 
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] quote=False for all columns in table

[Seth P]

Perfect. Thank you!

On Friday, June 30, 2017 at 5:41:33 PM UTC-4, Mike Bayer wrote:
>
>
> from sqlalchemy import Column, String, Integer, create_engine 
> from sqlalchemy.orm import Session 
> from sqlalchemy.ext.declarative import declarative_base 
> from sqlalchemy import event 
>
> Base = declarative_base() 
>
>
> @event.listens_for(Column, "before_parent_attach") 
> def _attach_column(column, table, **kw): 
> column.name = column.name.lower() 
>
>
> class A(Base): 
> __tablename__ = 'a' 
> myId = Column(Integer, primary_key=True) 
> someData = Column(String) 
> maxInterval = Column(Integer) 
>
>
> e = create_engine("sqlite://", echo=True) 
> Base.metadata.create_all(e) 
>
> s = Session(e) 
>
> s.add(A(someData='sasefd', maxInterval=5)) 
> s.commit() 
>
> print(s.query(A.someData, A.maxInterval)).all() 
>
>

-- 
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] quote=False for all columns in table

[Seth P]

I'm just accessing a vendor-provided Oracle database. The database itself 
is all upper(lower?) case (I'm new to Oracle), whereas I want my 
declarative model columns to be camelCase (and the emitted SQL to be 
unquoted camelCase) to match the vendor's documentation. I could make the 
column names all lower case, but would like to use camelCase if there's an 
easy way to do it. Only annoying thing about a custom my_column() is that 
I'd have to specify the name argument explicitly for each column, right?

On Friday, June 30, 2017 at 5:05:15 PM UTC-4, Mike Bayer wrote:
>
> On Fri, Jun 30, 2017 at 4:31 PM, Seth P <set...@outlook.com > 
> wrote: 
> > Is there a way (when using declarative) to specify that all the columns 
> of a 
> > table should use quote=False without specifying it explicitly for each 
> > column? 
>
> Easiest is just to call your own my_column(...) function that sets the 
> flag as you'd like, but setting quote=False unconditionally is a bad 
> idea and you shouldn't ever have to do that.   This sounds like you 
> are communicating with some special database backend for which you'd 
> be better off building a real dialect for it. 
>
>
> > I've tried setting __table_args__ = { 'quote_schema': False, 'quote': 
> False 
> > }, but that just affects the schema and table name, not the column name. 
> > 
> > -- 
> > 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] quote=False for all columns in table

[Seth P]

Is there a way (when using declarative) to specify that all the columns of 
a table should use quote=False without specifying it explicitly for each 
column?
I've tried setting __table_args__ = { 'quote_schema': False, 'quote': False 
}, but that just affects the schema and table name, not the column name.

-- 
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] ORM and table with no primary key

[Seth P]

On Monday, October 17, 2016 at 3:58:36 PM UTC-4, Mike Bayer wrote:
>
> that error is there right now because we don't emit the "col IS NULL" 
> SQL within that section of the persistence code. 
>
> took me a long time to find the history on this because I thought it had 
> been discussed but looks like the answer was "no immediate plans": 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/2127#comment-9011753 
>
> that is, we can't just drop that assertion without adding logic to alter 
> the SQL for some parameters. 
>
 
I see. Makes sense. 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] ORM and table with no primary key

[Seth P]

I realize that the orm really wants/needs a table to have a primary key: 
http://docs.sqlalchemy.org/en/rel_1_1/faq/ormconfiguration.html?#how-do-i-map-a-table-that-has-no-primary-key

Alas I have to deal with an existing table with no primary key. That said, 
it does have a unique constraint on a subset of the columns, though some of 
them are nullable (and indeed contain nulls). I've set up a declarative 
model with these columns labeled as primary_key=True, and things seem to 
work... until they don't, e.g. when calling delete() on some objects 
followed by flush():
sqlalchemy.orm.exc.FlushError: Can't delete from table MY_SCHEMA.my_table 
using NULL for primary key value on column my_table.MY_COLUMN_1

I know this is a long shot, but is there any way to tell the orm not to be 
such a stickler for detail, and just go ahead and emit the sql? I dread not 
being able to use the orm...

-- 
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] Sequence schema

[Seth P]

On Monday, October 17, 2016 at 11:24:43 AM UTC-4, Mike Bayer wrote:
>
> However, I don't see how the ordered attributes fixes anything in terms 
> of mixins.   If a mixin wants its columns at the beginning, or the end, 
> all of that can be upended by the presence of other mixins and those 
> mixins are necessarily traversed in __mro__ order.  It would be awkward 
> to link __mro__ to declared attribute order to the order the expressions 
> that work out to be Column objects go into a Table.   IMO it is a lot 
> easier for a mixin or whatever to simply set "Column(... 
> sort_order=-1000)" to deal with simple aesthetic concerns such as these. 
>

 I just mean that *within* a mixin (or table class), a set of columns 
defined as

col1 = sa.Column(sa.Integer)

@declared_attr
def col2(cls):
return sa.Column(sa.Integer)

col3 = sa.Column(sa.Integer)

@declared_attr
def col4(cls):
return sa.Column(sa.Integer)

col5 = sa.Column(sa.Integer)

would by default appear in order col1, col2, col3, col4, col5.

-- 
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] Sequence schema

[Seth P]

On a related note, is there something like after_create events for indexes 
and sequences? There doesn't seem to be.

-- 
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] Sequence schema

[Seth P]

On Sunday, October 16, 2016 at 10:09:00 AM UTC-4, Mike Bayer wrote:
>
>
>
> The simplest way is probably to set the creation order of the column to 
> be at the top: 
>
> col = Column(...) 
> col._creation_order = -10


Great. I will use _creation_order. Thanks.

By the way, in view of PEP 520 
(https://docs.python.org/3.6/whatsnew/3.6.html#whatsnew-deforder), in 
Python 3.6 will delcared_attr columns appear in their order of definition 
among all columns rather than after all explicitly defined columns?

-- 
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] Sequence schema

[Seth P]

I have a mixin of the following form

class MyMixin(object):
idx = sa.Column(sa.Integer, sa.Sequence('idx_seq', schema=???, 
optional=True), primary_key=True)
...


I would like the sequence to have the same schema as the table into which 
MyMixin will be mixed. I realize I could make idx a declared_attr, and then 
extract the schema from the provided cls -- and this is probably the 
"right" solution -- but that would lead to the idx column going at the end 
of the column list, and I really want it at the beginning. (Yes, this is 
just for silly aesthetics, but *all* my tables start with idx, and I'd like 
ones using MyMix to do so too.)

Is it possible to define the idx column without the sequence, and then 
afterwards create the sequence and add it to the column? Or alternatively 
define the sequence's schema at some later stage? Or better yet, is there 
some magic setting of the sequence's schema that will have it "inherit" 
from the containing table (I realize that in general sequences don't have 
"containing tables")?

-- 
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] Feedback appreciated

[Seth P]

You're right, of course. Adding has_type() to OracleDialect is more a matter of 
taste than a necessity.
Thanks again for all your help. I'm actually amazed at how well/transparent it 
works, given the cx_Oracle limitations.



_
From: Mike Bayer <mike...@zzzcomputing.com>
Sent: Monday, October 3, 2016 5:54 PM
Subject: Re: [sqlalchemy] Feedback appreciated
To:  <sqlalchemy@googlegroups.com>




On 10/03/2016 05:21 PM, Seth P wrote:
>
>
> On Friday, September 30, 2016 at 7:09:09 PM UTC-4, Mike Bayer wrote:
>
> the bind_expression() hook is here to allow you to re-render the
> expression.  assuming value-bound bindparam() objects (e.g. not like
> you'd get with an INSERT or UPDATE usually), the value should be
> present
> and you can do this (had to work up a POC):
>
> from sqlalchemy import *
> from sqlalchemy.types import UserDefinedType
>
>
> class T(UserDefinedType):
>
>  def bind_expression(self, colexpr):
>  return literal_column(colexpr.value)  # or whatever is
> needed here
>
> t = table('t', column('x', T()))
>
> print t.select().where(t.c.x == 'hi')
>
>
>
>
> >
> > Also, is there a way, inside VARRAY.__init__() or some other place
> that
> > is called before table creation to specify the
> sa.event.listen(,
> > "before_create", self.create_ddl().execute_if(dialect='oracle'))?
>
>
> look into adding SchemaType as a mixin, it signals to the owning Column
> that it should receive events.   You can then add the events to your
> type itself like before_parent_attach which should fire for the Column.
>
>
>
> OK, I got it working pretty much as desired.
>
> Adding
>
> def bind_expression(self, bindvalue):
> return sa.literal_column(self.process_literal_param(bindvalue.value, 
> None), self)
>
> makes insert and update statements work.
>
> I also got the drop/create business working automatically by copying
> code from the Postgresql ENUM implementation, though it seems like an
> excessive amount of boilerplate. To keep the code as-is I had to
> monkey-patch OracleDialect to add a has_type() method -- any chance
> you'd want to add that for 1.1?
>
> import six
> import sqlalchemy as sa
>
>
> # Moneky-patch OracleDialect to have has_type() mehtod
> from sqlalchemy.dialects.oracle.base import OracleDialect
>
> def has_type(self, connection, type_name, schema=None):
> if not schema:
> schema = self.default_schema_name
> cursor = connection.execute(
> sa.sql.text("SELECT type_name FROM all_types "
> "WHERE type_name = :name AND owner = :schema_name"),
> name=self.denormalize_name(type_name),
> schema_name=self.denormalize_name(schema))
> return cursor.first() is not None
>
> OracleDialect.has_type = has_type
>


I can see that a full implementation in SQLA would benefit from an 
OracleDialect.has_type() method but I don't see why, in the case here of 
external implementation, why has_type() has to be on the OracleDialect? 
You've got the bind right there and you're calling other SQL on it 
inline within your type.


>
> class VARRAY(sa.types.UserDefinedType, sa.types.SchemaType):
>
> def __init__(self, name, size_limit, item_type, nullable=True, 
> as_tuple=False,
>  inherit_schema=True, create_type=True, **kw):
> sa.types.UserDefinedType.__init__(self)
> sa.types.SchemaType.__init__(self, name=name, 
> inherit_schema=inherit_schema, **kw)
> self.size_limit = size_limit
> self.item_type = item_type
> self.nullable = nullable
> self.as_tuple = as_tuple
> self.create_type = create_type
>
> def get_col_spec(self):
> return (self.schema + '.' + self.name) if self.schema else self.name
>
> def compile(self, dialect=None):
> return (self.schema + '.' + self.name) if self.schema else self.name
>
> def create(self, bind=None, checkfirst=False):
> if not checkfirst or \
> not bind.dialect.has_type(
> bind, self.name, schema=self.schema):
> sql = "CREATE TYPE {} AS VARRAY({}) OF 
> {}".format(self.compile(dialect=bind.dialect),
>   self.size_limit,
>   
> self.item_type.compile(dialect=bind.dialect))
> if not self.nullable:
> sql += " NOT NULL"
> bind.execute(sql)
>
> def drop(self, bind=None, checkfirst=False):
>

Re: [sqlalchemy] Feedback appreciated

[Seth P]

On Friday, September 30, 2016 at 7:09:09 PM UTC-4, Mike Bayer wrote:
>
> the bind_expression() hook is here to allow you to re-render the 
> expression.  assuming value-bound bindparam() objects (e.g. not like 
> you'd get with an INSERT or UPDATE usually), the value should be present 
> and you can do this (had to work up a POC): 
>
> from sqlalchemy import * 
> from sqlalchemy.types import UserDefinedType 
>
>
> class T(UserDefinedType): 
>
>  def bind_expression(self, colexpr): 
>  return literal_column(colexpr.value)  # or whatever is needed 
> here 
>
> t = table('t', column('x', T())) 
>
> print t.select().where(t.c.x == 'hi') 
>
>
>
>
> > 
> > Also, is there a way, inside VARRAY.__init__() or some other place that 
> > is called before table creation to specify the sa.event.listen(, 
> > "before_create", self.create_ddl().execute_if(dialect='oracle'))? 
>
>
> look into adding SchemaType as a mixin, it signals to the owning Column 
> that it should receive events.   You can then add the events to your 
> type itself like before_parent_attach which should fire for the Column. 
>


OK, I got it working pretty much as desired.

Adding 

def bind_expression(self, bindvalue):
return sa.literal_column(self.process_literal_param(bindvalue.value, 
None), self)

makes insert and update statements work.

I also got the drop/create business working automatically by copying code 
from the Postgresql ENUM implementation, though it seems like an excessive 
amount of boilerplate. To keep the code as-is I had to monkey-patch 
OracleDialect to add a has_type() method -- any chance you'd want to add 
that for 1.1?

import six
import sqlalchemy as sa


# Moneky-patch OracleDialect to have has_type() mehtod
from sqlalchemy.dialects.oracle.base import OracleDialect

def has_type(self, connection, type_name, schema=None):
if not schema:
schema = self.default_schema_name
cursor = connection.execute(
sa.sql.text("SELECT type_name FROM all_types "
"WHERE type_name = :name AND owner = :schema_name"),
name=self.denormalize_name(type_name),
schema_name=self.denormalize_name(schema))
return cursor.first() is not None

OracleDialect.has_type = has_type


class VARRAY(sa.types.UserDefinedType, sa.types.SchemaType):

def __init__(self, name, size_limit, item_type, nullable=True, 
as_tuple=False,
 inherit_schema=True, create_type=True, **kw):
sa.types.UserDefinedType.__init__(self)
sa.types.SchemaType.__init__(self, name=name, 
inherit_schema=inherit_schema, **kw)
self.size_limit = size_limit
self.item_type = item_type
self.nullable = nullable
self.as_tuple = as_tuple
self.create_type = create_type

def get_col_spec(self):
return (self.schema + '.' + self.name) if self.schema else self.name

def compile(self, dialect=None):
return (self.schema + '.' + self.name) if self.schema else self.name

def create(self, bind=None, checkfirst=False):
if not checkfirst or \
not bind.dialect.has_type(
bind, self.name, schema=self.schema):
sql = "CREATE TYPE {} AS VARRAY({}) OF 
{}".format(self.compile(dialect=bind.dialect),
  self.size_limit,
  
self.item_type.compile(dialect=bind.dialect))
if not self.nullable:
sql += " NOT NULL"
bind.execute(sql)

def drop(self, bind=None, checkfirst=False):
if not checkfirst or \
bind.dialect.has_type(bind, self.name, schema=self.schema):
bind.execute("DROP TYPE " + self.compile(dialect=bind.dialect))

def _check_for_name_in_memos(self, checkfirst, kw):
"""Look in the 'ddl runner' for 'memos', then
note our name in that collection.

This to ensure a particular named enum is operated
upon only once within any kind of create/drop
sequence without relying upon "checkfirst".
"""
if not self.create_type:
return True
if '_ddl_runner' in kw:
ddl_runner = kw['_ddl_runner']
if '_oc_varrays' in ddl_runner.memo:
pg_enums = ddl_runner.memo['_oc_varrays']
else:
pg_enums = ddl_runner.memo['_oc_varrays'] = set()
present = self.name in pg_enums
pg_enums.add(self.name)
return present
else:
return False

def _on_table_create(self, target, bind, checkfirst, **kw):
if checkfirst or (
not self.metadata and
not kw.get('_is_metadata_operation', False)) and \
not self._check_for_name_in_memos(checkfirst, kw):
self.create(bind=bind, checkfirst=checkfirst)

def _on_table_drop(self, target, bind, checkfirst, **kw):
 

Re: [sqlalchemy] Feedback appreciated

[Seth P]

On Thursday, September 29, 2016 at 9:45:24 AM UTC-4, Mike Bayer wrote:
>
> you can add your own types to do these things also, especially 
> read-only, just make any subclass of UserDefinedType and apply whatever 
> result-row handling is needed for how cx_Oracle is returning the data. 
>
> The hard part about types is the elaborate expression support (e.g. like 
> JSON foo ->> bar vs. foo -> bar in PG for example).   Reading and 
> writing a value is not that hard and especially if the type is just 
> specific to what you need right now, you don't have the burden of making 
> sure your type works for all versions / flags / settings of Oracle / 
> cx_Oracle etc. 
>


The following seems to work fine for my purposes. (It uses alchy, 
https://github.com/dgilland/alchy).

Is there a way to specify that any column of type VARRAY should always be 
bound literally, so that one doesn't need to specify explicitly 
.compile(compile_kwargs={"literal_binds": 
True}) (and thereby literally binding all fields)?

Also, is there a way, inside VARRAY.__init__() or some other place that is 
called before table creation to specify the sa.event.listen(, 
"before_create", self.create_ddl().execute_if(dialect='oracle'))?


import six
import sqlalchemy as sa


class VARRAY(sa.types.UserDefinedType):

def __init__(self, type_name, size_limit, item_type, nullable=True, 
as_tuple=False):
super(VARRAY, self).__init__()
self.type_name = type_name
self.size_limit = size_limit
self.item_type = item_type
self.nullable = nullable
self.as_tuple = as_tuple

def compile(self, dialect=None):
return self.type_name

def get_col_spec(self, **kw):
return self.type_name

def create_ddl(self, dialect=None, or_replace=True):
sql = "CREATE "
if or_replace:
sql += "OR REPLACE "
sql += "TYPE %(schema)s.{} AS VARRAY({}) OF {}".format(self.type_name, 
self.size_limit,
   
self.item_type.compile(dialect=dialect))
if not self.nullable:
sql += " NOT NULL"
return sa.DDL(sql)

def process_literal_param(self, value, dialect):
return "{}({})".format(self.type_name,
   ','.join("NULL" if x is None else
("'%s'" % x) if isinstance(x, 
six.string_types) else str(x)
for x in value))

def literal_processor(self, dialect):
def processor(value):
return self.process_literal_param(value, dialect)
return processor

def process_result_value(self, value, dialect):
if self.as_tuple:
value = tuple(value)
return value

def result_processor(self, dialect, coltype):
def processor(value):
return self.process_result_value(value, dialect)
return processor

def copy(self):
return VARRAY(self.type_name, self.size_limit, self.item_type,
  nullable=self.nullable, as_tuple=self.as_tuple)




if __name__ == '__main__':

uri = "oracle://user:password@host"

import alchy
import sqlalchemy.dialects.oracle as oc

db = alchy.Manager(config={'SQLALCHEMY_DATABASE_URI': uri})

class TestVarray(db.Model):
__tablename__ = 'test_varray'
__table_args__ = { 'schema': 'myschema' }
idx = sa.Column(sa.Integer, primary_key=True)
label = sa.Column(sa.String(20), nullable=False)
words = sa.Column(VARRAY("tp_test_varray_words", 3000, sa.String(8), 
nullable=True), nullable=False)
numbers = sa.Column(VARRAY("tp_test_varray_numbers", 3000, oc.NUMBER(), 
nullable=True), nullable=False)

sa.event.listen(TestVarray.__table__, "before_create",

TestVarray.column_attrs()['words'].columns[0].type.create_ddl().execute_if(dialect='oracle'))

sa.event.listen(TestVarray.__table__, "before_create",

TestVarray.column_attrs()['numbers'].columns[0].type.create_ddl().execute_if(dialect='oracle'))

db.drop_all()
db.create_all()

db.engine.execute(TestVarray.__table__.insert({'idx': 1,
   'label': 'One',
   'words': ['Once', 'upon', 
'a', 'time'],
   'numbers': [1.1, 1.2]}).
  compile(compile_kwargs={"literal_binds": True}))
db.engine.execute(TestVarray.__table__.insert({'idx': 2,
   'label': 'Two',
   'words': ['To', 'be', 'or', 
'not'],
   'numbers': [2.1, 2.2]}).
  compile(compile_kwargs={"literal_binds": True}))

print TestVarray.query.all()
print db.session().query(TestVarray.label, TestVarray.words, 

Re: [sqlalchemy] Feedback appreciated

[Seth P]

On Wednesday, September 28, 2016 at 5:43:04 PM UTC-4, Mike Bayer wrote:
>
> looks incredibly difficult.   I'm not really about to have the resources 
> to work with a type that awkward anytime soon, unfortunately.   If it 
> could be made to be a drop-in for 1.1's ARRAY feature, that would be 
> helpful but it at least needs bound parameter support to be solid. 
>

Would it be possible to add read-only support? It looks like cx_Oracle 
returns selected varray values in a pretty straightforward form.
That would still be very useful (at least in my case, where I would be 
populating the database using SQL*Loader anyway).

-- 
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] Feedback appreciated

[Seth P]

On Wednesday, September 28, 2016 at 10:16:20 AM UTC-4, Mike Bayer wrote:
>
>
> So illustrating VARRAY round trip on cx_oracle is the first step. 
>

It looks like cx_Oracle supports reading varrays, but supports writing them 
only as column literals, not as bound parameters. The following code tests 
a bunch of plausible formats for bound parameters, none of which work. This 
is consistent with 
https://sourceforge.net/p/cx-oracle/mailman/message/26769899/ .

 

if __name__ == '__main__':

import logging
logging.basicConfig(level='DEBUG')

def format_args(args):
return ', '.join(['"%s"' % arg if isinstance(arg, str) else str(arg) 
for arg in execute_args])

import cx_Oracle
with cx_Oracle.Connection(user, password, host) as connection:
cursor = connection.cursor()
for drop_object in ("TABLE tb_aaa", "TYPE tp_str_vec", "TYPE 
tp_num_vec"):
try:
cursor.execute("DROP " + drop_object)
except:
pass
for execute_args in [("CREATE OR REPLACE TYPE tp_str_vec as VARRAY(3) 
of VARCHAR2(8)",),
 ("CREATE OR REPLACE TYPE tp_num_vec as VARRAY(3) 
of NUMBER",),
 ("CREATE TABLE tb_aaa ( strvec tp_str_vec, numvec 
tp_num_vec )",),]:
logging.info(format_args(execute_args) + "\n")
cursor.execute(*execute_args)

for i, execute_args in enumerate([
(("INSERT INTO tb_aaa VALUES ( tp_str_vec(%(strvec1)s), 
tp_num_vec(%(numvec1)s) )" %
  {"strvec1": str(['A1', 'A2', 'A3'])[1:-1], "numvec1": str([1.1, 
1.2, 1.3])[1:-1]}),),
("INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), 
tp_num_vec(:numvec1) )",
 {"strvec1": ['B1', 'B2', 'B3'], "numvec1": [2.1, 2.2, 2.3]}),
("INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), 
tp_num_vec(:numvec1) )",
 {"strvec1": str(['C1', 'C2', 'C3'])[1:-1], "numvec1": str([3.1, 
3.2, 3.3])[1:-1]}),
("INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), 
tp_num_vec(:numvec1) )",
 {"strvec1": str(['D1', 'D2', 'D3']), "numvec1": str([4.1, 4.2, 
4.3])}),
("INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), 
tp_num_vec(:numvec1) )",
 {"strvec1": '(%s)' % str(['E1', 'E2', 'E3'])[1:-1], "numvec1": 
'(%s)' % str([5.1, 5.2, 5.3])[1:-1]}),
("INSERT INTO tb_aaa VALUES ( :strvec1, :numvec1 )",
 {"strvec1": ['F1', 'F2', 'F3'], "numvec1": [6.1, 6.2, 6.3]}),
(("INSERT INTO tb_aaa VALUES ( (%(strvec1)s), (%(numvec1)s) )" %
  {"strvec1": str(['G1', 'G2', 'G3'])[1:-1], "numvec1": str([7.1, 
7.2, 7.3])[1:-1]}),),
(("INSERT INTO tb_aaa VALUES ( %(strvec1)s, %(numvec1)s )" %
  {"strvec1": str(['H1', 'H2', 'H3']), "numvec1": str([8.1, 8.2, 
8.3])}),),
]):
try:
logging.info("INSERT #%d: %s" % (i, format_args(execute_args)))
cursor.execute(*execute_args)
logging.info("INSERT #%d succeeded.\n" % i)
except cx_Oracle.DatabaseError as e:
logging.info("INSERT #%d failed: " % i + str(e))

cursor.execute("SELECT * FROM tb_aaa")
result = cursor.fetchall()
logging.info("SELECT returns:\n" + str(result))


INFO:root:"CREATE OR REPLACE TYPE tp_str_vec as VARRAY(3) of VARCHAR2(8)"

INFO:root:"CREATE OR REPLACE TYPE tp_num_vec as VARRAY(3) of NUMBER"

INFO:root:"CREATE TABLE tb_aaa ( strvec tp_str_vec, numvec tp_num_vec )"

INFO:root:INSERT #0: "INSERT INTO tb_aaa VALUES ( tp_str_vec('A1', 'A2', 'A3'), 
tp_num_vec(1.1, 1.2, 1.3) )"
INFO:root:INSERT #0 succeeded.

INFO:root:INSERT #1: "INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), 
tp_num_vec(:numvec1) )", {'numvec1': [2.1, 2.2, 2.3], 'strvec1': ['B1', 'B2', 
'B3']}
INFO:root:INSERT #1 failed: ORA-01484: arrays can only be bound to PL/SQL 
statements

INFO:root:INSERT #2: "INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), 
tp_num_vec(:numvec1) )", {'numvec1': '3.1, 3.2, 3.3', 'strvec1': "'C1', 'C2', 
'C3'"}
INFO:root:INSERT #2 failed: ORA-22814: attribute or element value is larger 
than specified in type

INFO:root:INSERT #3: "INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), 
tp_num_vec(:numvec1) )", {'numvec1': '[4.1, 4.2, 4.3]', 'strvec1': "['D1', 
'D2', 'D3']"}
INFO:root:INSERT #3 failed: ORA-22814: attribute or element value is larger 
than specified in type

INFO:root:INSERT #4: "INSERT INTO tb_aaa VALUES ( tp_str_vec(:strvec1), 
tp_num_vec(:numvec1) )", {'numvec1': '(5.1, 5.2, 5.3)', 'strvec1': "('E1', 
'E2', 'E3')"}
INFO:root:INSERT #4 failed: ORA-22814: attribute or element value is larger 
than specified in type

INFO:root:INSERT #5: "INSERT INTO tb_aaa VALUES ( :strvec1, :numvec1 )", 
{'numvec1': [6.1, 6.2, 6.3], 'strvec1': ['F1', 'F2', 'F3']}
INFO:root:INSERT #5 failed: ORA-01484: arrays can only be bound to PL/SQL 
statements

INFO:root:INSERT #6: "INSERT INTO tb_aaa VALUES ( 

[sqlalchemy] Re: SQLAlchemy - Bulk update using sqlalchemy core table.update() expects all columns in the values data

[Seth P]

Oops, I missed that this is an UPDATE rather than an INSERT. Setting the 
missing columns to None probably isn't what you want.

On Wednesday, September 28, 2016 at 9:08:00 AM UTC-4, Seth P wrote:
>
> Can't you include the missing columns in your dictionary with None values?
>

-- 
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] Feedback appreciated

[Seth P]

On Friday, August 23, 2013 at 3:52:54 PM UTC-4, Konsta Vesterinen wrote:
>
>
>
> On Friday, August 23, 2013 1:52:41 AM UTC+3, Michael Bayer wrote:
>  
>
>> 2. ScalarListType vs. Postgresql ARRAY ?   same/better?  should SLT use 
>> ARRAY on a PG backend ?
>>
>
> Hmm I'm not sure about this yet. Its definately not better than using 
> PostgreSQL ARRAY. ARRAY is better in many ways but its PostgreSQL specific. 
> Maybe we could make ScalarListType use ARRAY on PostgreSQL by default (as 
> you suggested).
>

I was wondering if there are any plans for SQLAlchemy to support Oracle's 
VARRAY column type? I've recently had the misfortune of having to use 
Oracle, and would like to have columns of the form 
sa.Column(sa.ARRAY(pt.String(8), 
dimensions=1)). I just looked at SQLAlchemy-Utils's ScalarListType 
(https://github.com/kvesteri/sqlalchemy-utils/blob/master/sqlalchemy_utils/types/scalar_list.py),
 
but I think then I'd bump into Oracle's 4000-byte limit 
(https://docs.oracle.com/cd/B19306_01/server.102/b14237/limits001.htm#i287903). 
(It's not clear to me what Oracle's VARRAY length limit is when used as a 
column type, but that's a separate issue.) It's also not clear to me if 
cx_Oracle supports VARRAYs nicely.

-- 
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: SQLAlchemy - Bulk update using sqlalchemy core table.update() expects all columns in the values data

[Seth P]

Can't you include the missing columns in your dictionary with None values?

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

Re: [sqlalchemy] How to bulk-insert millions of values into Oracle

[Seth P]

Thanks. I guess my confusion is that the example at 
http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements
 
uses an array of dictionaries, not of unlabeled tuples. Meanwhile I ended 
up using Oracle's sqlldr, which seems to get the job done, though is much 
more convoluted than Postgresql's COPY FROM...

On Saturday, September 24, 2016 at 12:03:01 AM UTC-4, Mike Bayer wrote:

>
> you don't need to use values().  insert() will construct the VALUES 
> clause automatically from a consistent set of parameters.  Basic example 
> is in the Core Tutorial at 
>
> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements
>  
> . 
>

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

[sqlalchemy] How to bulk-insert millions of values into Oracle

[Seth P]

The answer to this is probably RTFM, but I can't figure it out.

Suppose I have a declarative model of the form

class MyModel(Model):
idx = sa.Column(sa.Integer, primary_key=True)
c1 = sa.Column(sa.Float)
c2 = sa.Column(sa.Integer)
...
c10 = sa.Column(sa.Float)

And a list of (millions of) tuples of (c1, c2, ..., c10) values, e.g.
data = [
(1.1, 1, ..., 1.0),
(2.9, 2, , 2.345),
...
(14312.2, 12412, ..., 1251.15)
]

What is the fastest way to insert this data into the database?

With Postgresql I would get a cursor from a raw connection, and call
cursor.copy_expert("COPY  () FROM STDIN 
()",
   StringIO(''.join(','.join(row) + '\n' for row in data)))

For other databases, e.g. Oracle, what's the best way to accomplish this? I 
don't see a similar function in cx_Oracle.
I imagine I should use core, and not the ORM. I've read 
http://docs.sqlalchemy.org/en/rel_1_0/core/dml.html#sqlalchemy.sql.expression.Insert.values,
 
but it's not clear to me how to go in this case.

Thanks.

-- 
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] sa.DateTime(timezone=True) not emitting TIMESTAMP WITH TIMEZONE for Oracle

[Seth P]

The documentation for DateTime, 
http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=datetime#sqlalchemy.types.DateTime,
 
states
Parameters:*timezone* – boolean. If True, and supported by the backend, 
will produce ‘TIMESTAMP WITH TIMEZONE’. For backends that don’t support 
timezone aware timestamps, has no effect.
But this does not appear to be the case. In SQLAlchemy 1.0.15 with 
oracle+cx_Oracle,

class FooBar(BaseModel):
idx = sa.Column(sa.Integer, sa.Sequence('idx_seq'), primary_key=True)
my_oracle_DATE = sa.Column(oracle.DATE(timezone=False))
my_oracle_DATE_tz = sa.Column(oracle.DATE(timezone=True))
my_DateTime = sa.Column(sa.DateTime(timezone=False))
*my_DateTime_tz = sa.Column(sa.DateTime(timezone=True))*
my_TIMESTAMP = sa.Column(sa.TIMESTAMP(timezone=False))
my_TIMESTAMP_tz = sa.Column(sa.TIMESTAMP(timezone=True))


emits

CREATE TABLE barra.foo_bar (
idx INTEGER NOT NULL,
"my_oracle_DATE" DATE,
"my_oracle_DATE_tz" DATE,
"my_DateTime" DATE,
*"my_DateTime_tz" DATE,*
"my_TIMESTAMP" TIMESTAMP,
"my_TIMESTAMP_tz" TIMESTAMP WITH TIME ZONE,
PRIMARY KEY (idx)
)


-- 
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] Using literals in ORM update/delete query

[Seth P]

FWIW, this sounds similar to the problems you and I had (separately) a 
couple of years ago:
https://groups.google.com/forum/#!topic/sqlalchemy/kv7BqWZr9KQ
https://groups.google.com/forum/#!topic/sqlalchemy/k_9ZGI-e85E

On Monday, September 12, 2016 at 5:39:37 PM UTC-4, Thierry Florac wrote:
>
> I don't think it's so easy to produce a test case...
> Generaly speaking, indexes are used in a normal way!
> There's only in a single case, for a single table, that they are not used 
> even for common operations. Maybe it's a statistics problem or something 
> else I don't understand; and AFAIK the problem didn't occured at start 
> while table was containing fewer records...
>
> 2016-09-12 22:00 GMT+02:00 Mike Bayer  >:
>
>> I'd try to see on the cx_oracle list if you can produce a self-contained 
>> cx_Oracle-only test case and illustrate that indexes are not being used...I 
>> think this comes up a lot over there it's like an OCI thing.
>>
>>
>>
>> On 09/12/2016 03:20 PM, Thierry Florac wrote:
>>
>>> Hi Mike,
>>>
>>> I really agrea with you on all the points you mentionned. And I won't
>>> add anything about the cost of an Oracle database license and
>>> administrators compared with their performances! But I actually don't
>>> have the choice...  :-/
>>> My problem is clearly a database problem at first! But as my DBA can't
>>> find any solution to it, I'm just trying to find a bypass on the ORM 
>>> side.
>>> I definitively agree with SA ORM's way of handling all this. I was quite
>>> sure in fact that there was no easy way to bypass the ORM...
>>>
>>> Thanks anyway,
>>> Thierry
>>>
>>> 2016-09-12 20:53 GMT+02:00 Mike Bayer >> 
>>> >:
>>>
>>>
>>>
>>>
>>> On 09/12/2016 09:11 AM, Thierry Florac wrote:
>>>
>>> Hi,
>>>
>>> I'm working with an Oracle database which, for a reason I can't
>>> understand, doesn't want to use indexes when working on a given
>>> table
>>> (which is quite large) with prepared statements!
>>> I know I can use literal columns when running "select" queries
>>> (using
>>> the "literal_column" function), so that selects performances are
>>> good,
>>> but is there a way to use such literals while performing updates 
>>> ou
>>> deletes through the ORM using sessions?
>>>
>>>
>>> Three general stanzas on this:
>>>
>>>
>>> If we are talking about the UPDATE / DELETE statements within the
>>> ORM flush(), that would be extremely hard to trick the ORM into
>>> doing that correctly; getting the bound parameters to render as
>>> literals would be doable but the ORM really wants to batch those
>>> statements together, which would have to also be turned off and
>>> there's no public entryway to that.
>>>
>>> Next, brute force via the do_executemany() / do_execute() dialect
>>> events which give you direct access to the cx_Oracle cursor.  You'd
>>> need to break the list of parameters sent to executemany() into
>>> multiple single execute() calls and rewrite all the parameters.
>>>  This will get you the effect you want at the expense of much 
>>> ugliness.
>>>
>>> Third.  The ORM's UPDATE/DELETE statements only use the primary key
>>> in the WHERE clause, not any other columns.   if your Oracle DB
>>> can't even index by primary key on prepared statement that is really
>>> something that is likely fixable on the database side.   Your
>>> employer (or yourself, if this is your own company) is likely paying
>>> hundreds of thousands of $$ per year to be able to use Oracle in the
>>> first place, and if your Oracle DB can't look up a record by primary
>>> key using the index, something is seriously broken with it.   Oracle
>>> is pretty bad in this area but this one should not be the case; I'd
>>> advise at least checking on the cx_Oracle list why your Oracle DB is
>>> bypassing indexes for prepared statements.
>>>
>>>
>>>
>>>
>>>
>>>
>>> Best regards,
>>> Thierry
>>> --
>>> http://www.imagesdusport.com -- http://www.ztfy.org
>>>
>>> --
>>> 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
>>>  

[sqlalchemy] kdb+/q dialect

[Seth P]

Has anyone written a SQLAlchemy dialect for kdb+/q? I realize q isn't 
exactly SQL, but I figure if it's possible to write a dialect for Pandas 
tables (https://bitbucket.org/zzzeek/calchipan) it should be possible to do 
so for q.

-- 
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] Column order with declarative base and @declared_attr

[Seth P]

If I understand what you're saying, the problem is that set _creation_order() 
specifies an ordering across all columns from all mixins. But shouldn't it 
suffice to specify the "local" ordering of the columns within a given mixin, 
and then process the mixins in __mro__ order?
(FWIW I worked around my particular problem by making the Columns 
non-@declared_attr, eliminating ForeignKey parameters (which are 
cls-dependent), and creating cls-dependent ForeignKeyConstraints in a 
cls-dependent __table_args__.)






On Thu, Aug 25, 2016 at 4:24 PM -0400, "Mike Bayer" <mike...@zzzcomputing.com> 
wrote:










the @declarative_attr object would need util.set_creation_order() 
applied and the _MapperConfig._scan_attributes() would need to take this 
into account.  However, it would not behave well across multiple mixins. 
  The mixins must be scanned in __mro__ order first.   So it would be of 
limited use for there to be an ordering under @declared_attr.


On 08/25/2016 02:46 PM, Seth P wrote:
> I was just bitten by this issue. Is it still the case that there is no way to 
> specify the order of two columns declared in a mixin using @declared_attr?
>

-- 
You received this message because you are subscribed to a topic in the Google 
Groups "sqlalchemy" group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/sqlalchemy/OA-n_pY0tuM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to 
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.





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

Re: [sqlalchemy] Column order with declarative base and @declared_attr

[Seth P]

I was just bitten by this issue. Is it still the case that there is no way to 
specify the order of two columns declared in a mixin using @declared_attr?

-- 
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] Implicit join condition

[Seth P]

That works!

Obviously I need to know that the joining field is called a_id, and I can 
live with that, since in practice it's uniform. But I'm just curious if 
there's an automated way to figure out which entity/column is related to A. 
(There could be more than one entity in q, though just one that has a 
(unique) ForeignKey to A.)

On Wednesday, July 13, 2016 at 2:06:55 PM UTC-4, Mike Bayer wrote:
>
>
>
> On 07/13/2016 01:04 PM, Seth P wrote: 
> > Thank you, as always, for the quick and detailed response. 
> > 
> > With the join to the subquery that's on func.max(A.id), once you use 
> > that function, the column loses it's "A.id-ness", because SQLA 
> doesn't 
> > know anything about func.max() and for all it knows it could be 
> turning 
> > it into anything. 
> > 
> > 
> > I figured as much, and obviously this is the correct behavior. I was 
> > hoping there was some way I could tell SQLAlchemy that subquery.id 
> > "possesses A.id-ness" (e.g. via .select_from()), but that doesn't appear 
> > to be the case. 
> > 
> > 3. build yourself a function, if you want it to look nice you can 
> use 
> > with_transformation() 
> > 
> > 
> > This is exactly what I'm doing (minus the with_transformation(), which 
> > I'm about to look up...). The problem is that there are additional 
> > classes C, D, E, F, etc. that all point to A (via a ForeignKey and a 
> > relationship), and the query q could involve any one of them. (Obviously 
> > if it involved more than one, I would need to specify the join 
> > explicitly.) So it's not at all straightforward (at least to me) to 
> > figure out on what to join -- unless I require that it be explicitly 
> > provided as an argument to the function, i.e. join_to_min_a(q, 
> > field_to_join_to_A_id). 
>
> OK then how about this: 
>
>  def join_to_min_a(q): 
>  subquery_returning_one_id = 
> session.query(func.min(A.id).label('id')).subquery('max_a_id') 
>  joining_to = q.column_descriptions[0]['entity'].a_id 
>  q = q.join(subquery_returning_one_id, 
> subquery_returning_one_id.c.id == joining_to) 
>  return q 
>
>
>
>
>
>
> > 
> > 
> > On Wednesday, July 13, 2016 at 12:16:52 PM UTC-4, Mike Bayer wrote: 
> > 
> > 
> > 
> > On 07/13/2016 02:29 AM, Seth P wrote: 
> > > [Apologies for posting an incomplete version of this post earlier. 
> > > Please ignore it.] 
> > > 
> > > If B has a ForeignKey (and relationship) to A (e.g. B.a_id -> 
> A.id), 
> > > then I can write query(B.b_num).join(A) without specifying the 
> > > condition, and SQLAlchemy will figure out the join automatically. 
> > [See 
> > > query 0 in the code below.] 
> > > 
> > > It will similarly figure out the join of B with a "direct" query 
> > ofA.id, 
> > > e.g. 
> > > 
> query(A.id).order_by(A.a_num).limit(1).subquery('first_a_id_by_num'). 
> > > [See query 1 in the code below.] 
> > > 
> > > However, it will not work with a more complicated query of A.id, 
> e.g. 
> > > query(func.min(A.id).label('id')).subquery('max_a_id'). [See query 
> > 2 in 
> > > the code below.] 
> > > 
> > > Of course, I can make it work by specifying the join condition 
> > B.a_id == 
> > > subquery_returning_one_id.c.id 
> > <http://subquery_returning_one_id.c.id>. [See query 3 in the code 
> > below.] 
> > > 
> > > I can get the implicit join to work with such a subquery by 
> > joining with 
> > > a separate A.id and using the subquery to filter this A.id, but 
> this 
> > > seems more convoluted than necessary. [See query 4 in the code 
> > below.] 
> > > 
> > > I can also get it to work with 
> > > 
> > 
> query(A.id).select_from(subquery_returning_one_id).subquery('a_id_from_max_a_id'),
>  
>
> > 
> > > but like query 4, this also introduces an extra reference to A.id. 
> > [See 
> > > query 5 in the code below.] 
> > > 
> > > Is there any way to get an implicit join like query 2 to produce 
> > sql as 
> > > in query 3, without introducing (explicitly as in query 4 or 
> > implicitly 
> > > as in query 5) an extra reference to A.id? Or is the extra copy of 
> > A.id 
> > > in quer

Re: [sqlalchemy] Implicit join condition

[Seth P]

Thank you, as always, for the quick and detailed response.

With the join to the subquery that's on func.max(A.id), once you use 
> that function, the column loses it's "A.id-ness", because SQLA doesn't 
> know anything about func.max() and for all it knows it could be turning 
> it into anything. 
>

I figured as much, and obviously this is the correct behavior. I was hoping 
there was some way I could tell SQLAlchemy that subquery.id "possesses 
A.id-ness" 
(e.g. via .select_from()), but that doesn't appear to be the case.

3. build yourself a function, if you want it to look nice you can use 
> with_transformation() 
>

This is exactly what I'm doing (minus the with_transformation(), which I'm 
about to look up...). The problem is that there are additional classes C, D, 
E, F, etc. that all point to A (via a ForeignKey and a relationship), and 
the query q could involve any one of them. (Obviously if it involved more 
than one, I would need to specify the join explicitly.) So it's not at all 
straightforward (at least to me) to figure out on what to join -- unless I 
require that it be explicitly provided as an argument to the function, i.e. 
join_to_min_a(q, 
field_to_join_to_A_id).


On Wednesday, July 13, 2016 at 12:16:52 PM UTC-4, Mike Bayer wrote:
>
>
>
> On 07/13/2016 02:29 AM, Seth P wrote: 
> > [Apologies for posting an incomplete version of this post earlier. 
> > Please ignore it.] 
> > 
> > If B has a ForeignKey (and relationship) to A (e.g. B.a_id -> A.id), 
> > then I can write query(B.b_num).join(A) without specifying the 
> > condition, and SQLAlchemy will figure out the join automatically. [See 
> > query 0 in the code below.] 
> > 
> > It will similarly figure out the join of B with a "direct" query ofA.id, 
> > e.g. 
> > query(A.id).order_by(A.a_num).limit(1).subquery('first_a_id_by_num'). 
> > [See query 1 in the code below.] 
> > 
> > However, it will not work with a more complicated query of A.id, e.g. 
> > query(func.min(A.id).label('id')).subquery('max_a_id'). [See query 2 in 
> > the code below.] 
> > 
> > Of course, I can make it work by specifying the join condition B.a_id == 
> > subquery_returning_one_id.c.id. [See query 3 in the code below.] 
> > 
> > I can get the implicit join to work with such a subquery by joining with 
> > a separate A.id and using the subquery to filter this A.id, but this 
> > seems more convoluted than necessary. [See query 4 in the code below.] 
> > 
> > I can also get it to work with 
> > 
> query(A.id).select_from(subquery_returning_one_id).subquery('a_id_from_max_a_id'),
>  
>
> > but like query 4, this also introduces an extra reference to A.id. [See 
> > query 5 in the code below.] 
> > 
> > Is there any way to get an implicit join like query 2 to produce sql as 
> > in query 3, without introducing (explicitly as in query 4 or implicitly 
> > as in query 5) an extra reference to A.id? Or is the extra copy of A.id 
> > in queries 4 and 5 pretty harmless performance-wise, and I should just 
> > deal with it as the cost of not providing an explicit join condition? 
> > 
> > Yes, I realize that I can avoid this problem by providing an explicit 
> > join condition, but I'd prefer to avoid that if possible. (Also, in case 
> > it matters, my actual subquery is more complicated than the 
> > func.min(A.id) example here, but in the end returns a single column 
> > labeled id with values from A.id.) 
>
> So, when you do a thing like query(B).join(A), it is using the foreign 
> keys between B and A to figure that out, but *not* the relationship 
> "B.a".  If OTOH you do query(B).join(B.a), then you *are* using the 
> relationship. 
>
> With the join to the subquery that's on func.max(A.id), once you use 
> that function, the column loses it's "A.id-ness", because SQLA doesn't 
> know anything about func.max() and for all it knows it could be turning 
> it into anything.  So neither a join on FKs nor on the existing 
> relationship can figure that out immediately. 
>
> It depends here on where you are OK doing the explicit mentioning of 
> A.id and B.a_id.  it has to be somewhere.  It can be: 
>
> 1. in a new relationship() that you put on A or B, that doesn't normally 
> load but you can use it here 
>
> 2. when you make the subquery, include B.a_id in it somehow, like either 
> select from B.a_id instead of A.id (if that applies), or pre-fabricate 
> your join condition: 
>
>  q = session.query(B.b_num) 
>  subquery_returning_one_id = 
> session.query(func.min(A.id).label('id')).subquery('max_a_id') 
>
>  j = subquery_returning_one

[sqlalchemy] Re: Implicit join condition

[Seth P]

Actually, taking a closer look, the sql generated for query 5 doesn't look 
correct (or at least not what I want), since it isn't joining max_a_id with 
anything.

On Wednesday, July 13, 2016 at 2:29:34 AM UTC-4, Seth P wrote:
>
> [Apologies for posting an incomplete version of this post earlier. Please 
> ignore it.]
>
> If B has a ForeignKey (and relationship) to A (e.g. B.a_id -> A.id), then 
> I can write query(B.b_num).join(A) without specifying the condition, and 
> SQLAlchemy will figure out the join automatically. [See query 0 in the code 
> below.]
>
> It will similarly figure out the join of B with a "direct" query of A.id, 
> e.g. query(A.id).order_by(A.a_num).limit(1).subquery('first_a_id_by_num'). 
> [See query 1 in the code below.]
>
> However, it will not work with a more complicated query of A.id, e.g. 
> query(func.min(A.id).label('id')).subquery('max_a_id'). [See query 2 in 
> the code below.]
>
> Of course, I can make it work by specifying the join condition B.a_id == 
> subquery_returning_one_id.c.id. [See query 3 in the code below.]
>
> I can get the implicit join to work with such a subquery by joining with a 
> separate A.id and using the subquery to filter this A.id, but this seems 
> more convoluted than necessary. [See query 4 in the code below.]
>
> I can also get it to work with 
> query(A.id).select_from(subquery_returning_one_id).subquery(
> 'a_id_from_max_a_id'), but like query 4, this also introduces an extra 
> reference to A.id. [See query 5 in the code below.]
>
> Is there any way to get an implicit join like query 2 to produce sql as in 
> query 3, without introducing (explicitly as in query 4 or implicitly as in 
> query 5) an extra reference to A.id? Or is the extra copy of A.id in 
> queries 4 and 5 pretty harmless performance-wise, and I should just deal 
> with it as the cost of not providing an explicit join condition?
>
> Yes, I realize that I can avoid this problem by providing an explicit join 
> condition, but I'd prefer to avoid that if possible. (Also, in case it 
> matters, my actual subquery is more complicated than the func.min(A.id) 
> example here, but in the end returns a single column labeled id with 
> values from A.id.)
>
>
>
> from sqlalchemy import create_engine, func, Column, Integer, ForeignKey
> from sqlalchemy.orm import relationship, sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
>
> sqlite = 'sqlite:///test.db'
> engine = create_engine(sqlite, echo=True)
> Base = declarative_base(bind=engine)
>
>
> class A(Base):
> __tablename__ = 'a'
> id = Column(Integer, primary_key=True)
> a_num = Column(Integer)
>
>
> class B(Base):
> __tablename__ = 'b'
> id = Column(Integer, primary_key=True)
> b_num = Column(Integer)
> a_id = Column(Integer, ForeignKey(A.id))
> a = relationship(A)
>
>
> if __name__ == '__main__':
> Base.metadata.drop_all()
> Base.metadata.create_all()
> session = sessionmaker(bind=engine)()
> session.add(B(b_num=2, a=A(a_num=1)))
> session.commit()
>
> q = session.query(B.b_num)
> subquery_returning_one_A_id = 
> session.query(A.id).order_by(A.a_num).limit(1).subquery('first_a_id_by_num')
> subquery_returning_one_id = 
> session.query(func.min(A.id).label('id')).subquery('max_a_id')
>
> i = 0
> print("\n%d" % i)
> try:
> query = q.join(A)
> print(query.one())
> except Exception as e:
> print("Exception:", e)
>
> i = 1
> print("\n%d" % i)
> try:
> query = q.join(subquery_returning_one_A_id)
> print(query.one())
> except Exception as e:
> print("Exception:", e)
>
> i = 2
> print("\n%d" % i)
> try:
> query = q.join(subquery_returning_one_id)
> print(query.one())
> except Exception as e:
> print("Exception:", e)
>
> i = 3
> print("\n%d" % i)
> try:
> query = q.join(subquery_returning_one_id, B.a_id == 
> subquery_returning_one_id.c.id)
> print(query.one())
> except Exception as e:
> print("Exception:", e)
>
> i = 4
> print("\n%d" % i)
> try:
> query = q.join(session.query(A.id).filter(A.id ==
>   
> subquery_returning_one_id.c.id).subquery('a_id_equal_to_max_a_id'))
> print(query.one())
> except Exception as e:
> print("Exception:", e)
>
> i = 5
> print("\n%d" % i)
> try:
> query = 
> q.j

[sqlalchemy] Implicit join condition

[Seth P]

[Apologies for posting an incomplete version of this post earlier. Please 
ignore it.]

If B has a ForeignKey (and relationship) to A (e.g. B.a_id -> A.id), then I 
can write query(B.b_num).join(A) without specifying the condition, and 
SQLAlchemy will figure out the join automatically. [See query 0 in the code 
below.]

It will similarly figure out the join of B with a "direct" query of A.id, 
e.g. query(A.id).order_by(A.a_num).limit(1).subquery('first_a_id_by_num'). 
[See query 1 in the code below.]

However, it will not work with a more complicated query of A.id, e.g. 
query(func.min(A.id).label('id')).subquery('max_a_id'). [See query 2 in the 
code below.]

Of course, I can make it work by specifying the join condition B.a_id == 
subquery_returning_one_id.c.id. [See query 3 in the code below.]

I can get the implicit join to work with such a subquery by joining with a 
separate A.id and using the subquery to filter this A.id, but this seems 
more convoluted than necessary. [See query 4 in the code below.]

I can also get it to work with 
query(A.id).select_from(subquery_returning_one_id).subquery(
'a_id_from_max_a_id'), but like query 4, this also introduces an extra 
reference to A.id. [See query 5 in the code below.]

Is there any way to get an implicit join like query 2 to produce sql as in 
query 3, without introducing (explicitly as in query 4 or implicitly as in 
query 5) an extra reference to A.id? Or is the extra copy of A.id in 
queries 4 and 5 pretty harmless performance-wise, and I should just deal 
with it as the cost of not providing an explicit join condition?

Yes, I realize that I can avoid this problem by providing an explicit join 
condition, but I'd prefer to avoid that if possible. (Also, in case it 
matters, my actual subquery is more complicated than the func.min(A.id) 
example here, but in the end returns a single column labeled id with values 
from A.id.)



from sqlalchemy import create_engine, func, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

sqlite = 'sqlite:///test.db'
engine = create_engine(sqlite, echo=True)
Base = declarative_base(bind=engine)


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


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


if __name__ == '__main__':
Base.metadata.drop_all()
Base.metadata.create_all()
session = sessionmaker(bind=engine)()
session.add(B(b_num=2, a=A(a_num=1)))
session.commit()

q = session.query(B.b_num)
subquery_returning_one_A_id = 
session.query(A.id).order_by(A.a_num).limit(1).subquery('first_a_id_by_num')
subquery_returning_one_id = 
session.query(func.min(A.id).label('id')).subquery('max_a_id')

i = 0
print("\n%d" % i)
try:
query = q.join(A)
print(query.one())
except Exception as e:
print("Exception:", e)

i = 1
print("\n%d" % i)
try:
query = q.join(subquery_returning_one_A_id)
print(query.one())
except Exception as e:
print("Exception:", e)

i = 2
print("\n%d" % i)
try:
query = q.join(subquery_returning_one_id)
print(query.one())
except Exception as e:
print("Exception:", e)

i = 3
print("\n%d" % i)
try:
query = q.join(subquery_returning_one_id, B.a_id == 
subquery_returning_one_id.c.id)
print(query.one())
except Exception as e:
print("Exception:", e)

i = 4
print("\n%d" % i)
try:
query = q.join(session.query(A.id).filter(A.id ==
  
subquery_returning_one_id.c.id).subquery('a_id_equal_to_max_a_id'))
print(query.one())
except Exception as e:
print("Exception:", e)

i = 5
print("\n%d" % i)
try:
query = 
q.join(session.query(A.id).select_from(subquery_returning_one_id).subquery('a_id_from_max_a_id'))
print(query.one())
except Exception as e:
print("Exception:", e)

session.close_all()


Relevant output:

0
2016-07-13 02:17:41,901 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-07-13 02:17:41,902 INFO sqlalchemy.engine.base.Engine SELECT b.b_num 
AS b_b_num
FROM b JOIN a ON a.id = b.a_id
2016-07-13 02:17:41,902 INFO sqlalchemy.engine.base.Engine ()
(2,)

1
2016-07-13 02:17:41,902 INFO sqlalchemy.engine.base.Engine SELECT b.b_num 
AS b_b_num
FROM b JOIN (SELECT a.id AS id
FROM a ORDER BY a.a_num
 LIMIT ? OFFSET ?) AS first_a_id_by_num ON first_a_id_by_num.id = b.a_id
2016-07-13 02:17:41,908 INFO sqlalchemy.engine.base.Engine (1, 0)
(2,)

2
Exception: Could not find a FROM clause to join from.  Tried joining to 
SELECT min(a.id) AS id
FROM a, but got: Can't find any foreign key relationships between 'b' and 
'max_a_id'.

3

[sqlalchemy] Implicit join conditions

[Seth P]

Apologies if this is documented and I missed it.

If B has a ForeignKey (and relationship) to A (e.g. B.a_id -> A.id), then I 
can write query(B.b_num).join(A) without specifying the condition, and 
SQLAlchemy will figure out the join automatically. [See query 0 in the code 
below.]

It will similarly figure out the join of B with a direct query of A.id, 
e.g. query(A.id).order_by(A.a_num).limit(1).subquery('first_a_id_by_num') 
rather than with A. [See query 1 in the code below.]

However, it will not work with a more complicated query of A.id, e.g. 
query(func.min(A.id).label('id')).subquery('max_a_id') rather than with A. 
[See query 2 in the code below.]

I can get the implicit join to work with such a subquery by joining with a 
separate A.id and using the subquery to filter this A.id, but this seems 
more convoluted than necessary. [See query 3 in the code below.]

Yes, I realize that I can avoid this problem by providing an explicit join 
condition, but I'd prefer to avoid that if possible. (Also, in case it 
matters, my actual subquery is more complicated than the func.min(A.id) 
example here, as it involves windowing functions, but in the end returns a 
single id column with values from A.id.)

Is there any way to get an implicit join like query 2 below to work without 
resorting to the "hack" in query 3?
Is it possible to do something along the lines of 
query(b.b_num).join(A).replace(A, 
subquery_returning_one_id)?

Apologies if this is documented


from sqlalchemy import create_engine, func, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

sqlite = 'sqlite:///test.db'
engine = create_engine(sqlite, echo=True)
Base = declarative_base(bind=engine)


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


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


if __name__ == '__main__':
Base.metadata.drop_all()
Base.metadata.create_all()
session = sessionmaker(bind=engine)()
session.add(B(b_num=2, a=A(a_num=1)))
session.commit()

q = session.query(B.b_num)
subquery_returning_one_A_id = 
session.query(A.id).order_by(A.a_num).limit(1).subquery('first_a_id_by_num')
subquery_returning_one_id = 
session.query(func.min(A.id).label('id')).subquery('max_a_id')

i = 0
print("\n%d" % i)
try:
query = q.join(A)
print(query.one())
except Exception as e:
print("Exception:", e)

i = 1
print("\n%d" % i)
try:
query = q.join(subquery_returning_one_A_id)
print(query.one())
except Exception as e:
print("Exception:", e)

i = 2
print("\n%d" % i)
try:
query = q.join(subquery_returning_one_id)
print(query.one())
except Exception as e:
print("Exception:", e)

i = 3
print("\n%d" % i)
try:
query = q.join(session.query(A.id).filter(A.id ==
  
subquery_returning_one_id.c.id).subquery('a_id_equal_to_max_a_id'))
print(query.one())
except Exception as e:
print("Exception:", e)


i = 4
print("\n%d" % i)
try:
query = 
q.join(session.query(A.id).select_from(subquery_returning_one_id).subquery('foo'))
print(query.one())
except Exception as e:
print("Exception:", e)
session.close_all()



Relevant output:

0
2016-07-13 01:23:41,530 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-07-13 01:23:41,531 INFO sqlalchemy.engine.base.Engine SELECT b.b_num 
AS b_b_num
FROM b JOIN a ON a.id = b.a_id
2016-07-13 01:23:41,532 INFO sqlalchemy.engine.base.Engine ()
(2,)

1
2016-07-13 01:23:41,532 INFO sqlalchemy.engine.base.Engine SELECT b.b_num 
AS b_b_num
FROM b JOIN (SELECT a.id AS id
FROM a ORDER BY a.a_num
 LIMIT ? OFFSET ?) AS first_a_id_by_num ON first_a_id_by_num.id = b.a_id
2016-07-13 01:23:41,539 INFO sqlalchemy.engine.base.Engine (1, 0)
(2,)

2
Exception: Could not find a FROM clause to join from.  Tried joining to 
SELECT min(a.id) AS id
FROM a, but got: Can't find any foreign key relationships between 'b' and 
'max_a_id'.

3
2016-07-13 01:23:41,578 INFO sqlalchemy.engine.base.Engine SELECT b.b_num 
AS b_b_num
FROM b JOIN (SELECT a.id AS id
FROM a, (SELECT min(a.id) AS id
FROM a) AS max_a_id
WHERE a.id = max_a_id.id) AS a_id_equal_to_max_a_id ON 
a_id_equal_to_max_a_id.id = b.a_id
2016-07-13 01:23:41,581 INFO sqlalchemy.engine.base.Engine ()
(2,)

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

Re: [sqlalchemy] engine.execute() slow with parameterized queries?

[Seth P]

Yep, literal_column() fixed my performance problem.

Thanks again for all your help

On Tuesday, May 13, 2014 6:15:39 AM UTC-4, Michael Bayer wrote:

 OK well of course also, as we have the exact same thing being asked in 
 regards to Oracle right now in another thread, you can of course always 
 bypass a “bound” value in the most direct way, using text() or 
 literal_column():

 q = s.query(Something).filter(Something.foo = literal_column(“‘my value’”))



 On May 12, 2014, at 8:38 PM, Michael Bayer 
 mik...@zzzcomputing.comjavascript: 
 wrote:

 well or a FreeTDS issue, more likely, if that’s what you’re using.

 the SQL compiler has a parameter called “literal_binds” that will make it 
 render a bound parameter as an inline string, but it only supports a few 
 very basic types.As far as getting this parameter set for a general 
 class of queries, it depends on when you’d want it to happen and how.   It 
 likely would require some subclassing and possibly monkey patching.


 On May 12, 2014, at 8:23 PM, Seth P spad...@gmail.com javascript: 
 wrote:

 pymssql produces the same results as pyodbc. So it looks like a SQL Server 
 issue.

 On Monday, May 12, 2014 8:06:08 PM UTC-4, Seth P wrote:

 Fair enough. I'll take a look at pymssql, though I suspect it may be a 
 SQL Server rather than a driver issue.


 On Monday, May 12, 2014 7:50:03 PM UTC-4, Michael Bayer wrote:


 On May 12, 2014, at 7:35 PM, Seth P spad...@gmail.com wrote:

 Looks like other people have encountered similar problems with indices 
 being ignored by prepared sql statements: 
 http://www.postgresql.org/message-id/43250afa.7010...@arbash-meinel.com. 
 (If the diagnosis there is correct, then I'm guessing the server would use 
 a unique index where all the columns of the index are specified.) Also, 
 Thierry Florac's post 
 https://groups.google.com/forum/#!topic/sqlalchemy/k_9ZGI-e85E sounds 
 similar.
 (I suspect my earlier hypothesis about int vs varchar is a red herring.)

 I think it would be useful (albeit risky, if not careful) to have an 
 option to plug in parameters client-side. I presume not trivial to add to 
 SQLAlchemy? I don't see such an option for pyodbc.


 there’s mechanisms for this but they aren’t very widely advertised since 
 as you know allowing people to do such would be an *enormous* security 
 hole, and I don’t have the resources to be responsible for parameter 
 escaping.   It would be better if you could try pymssql (much more actively 
 maintained than pyodbc from what i can tell) and/or file a bug with pyodbc.



 On Monday, May 12, 2014 7:09:08 PM UTC-4, Seth P wrote:

 Yep, it's not a SQLAlchemy issue. The following code demonstrates the 
 problem with direct pyodbc access.

 import pyodbc
 import time

 def print_timing(func):
 def wrapper(*arg):
 t1 = time.time()
 rows = func(*arg)
 t2 = time.time()
 print(%30s() len=%d, last=%s, runtime=%0.3fs % 
 (str(func).split(' at')[0][10:], len(rows), rows[-1], t2 - t1))
 return t2 - t1
 return wrapper

 if __name__ == '__main__':
 cnxn = pyodbc.connect('DRIVER={SQL 
 Server};SERVER=Compustat;DATABASE=Compustat')
 cursor = cnxn.cursor()
 sql_select_statement_base = SELECT datadate, prcod FROM sec_dprc 
 WHERE gvkey = ? ORDER BY datadate
 key = '001045'

 @print_timing
 def execute_explicit_query():
 sql_select_statement_explicit = 
 sql_select_statement_base.replace(?, '%s' % key)
 rows = cursor.execute(sql_select_statement_explicit).fetchall()
 return rows

 @print_timing
 def execute_parameterized_query():
 rows = cursor.execute(sql_select_statement_base, key).fetchall()
 return rows

 num_iterations = 5
 explicit_runtime = 0.0
 parameterized_runtime = 0.0
 for i in range(num_iterations):
 explicit_runtime += execute_explicit_query()
 parameterized_runtime += execute_parameterized_query()
 print(Total runtime for %d explicit queries = %0.3fs. % 
 (num_iterations, explicit_runtime))
 print(Total runtime for %d parameterized queries = %0.3fs. % 
 (num_iterations, parameterized_runtime))


 On Monday, May 12, 2014 6:40:48 PM UTC-4, Michael Bayer wrote:


 On May 12, 2014, at 6:33 PM, Seth P spad...@gmail.com wrote:

 Is it possible that the (primary key index (which is a composite index 
 that begins with gvkey, and is the only index on the table) isn't being 
 used because the the gvkey parameter is somehow passed as an integer 
 rather 
 than as a string?


 There’s nothing in SQLAlchemy that coerces strings to integers.  If 
 the actual type of the column on the DB is an integer, then there might 
 be 
 some conversion within pyodbc or the ODBC driver.

 if you’ve got it narrowed down this much the next step is to figure 
 out a raw pyodbc script that illustrates what the problem is. 


 The first EXEC below is pretty much instantaneous, whereas the second 
 takes about 8 seconds (and produces

[sqlalchemy] engine.execute() slow with parameterized queries?

[Seth P]

After tracking down some extreme slowness in loading a one-to-many 
relationship (e.g. myobject.foobars), I seem to have isolated the issue to 
engine.execute() being much slower with parameterized queries than with 
explicit queries. The following is actual code and output for loading 
10,971 rows from a database table. (The actual table has more columns than 
I'm including here, and is not designed by me.) Note that each explicit 
query (where I explicitly set the WHERE clause parameter and pass the 
resulting SQL statement to engine.execute()) runs in under 0.1 seconds, 
whereas each parameterized query (where I let SQLAlchemy bind the WHERE 
clause parameter) takes over 8 seconds.

The difference in runtimes is smaller when the number of rows returned is 
smaller, which seems odd since I would have thought that the binding of the 
WHERE clause parameters is just done once and would be virtually 
instantaneous.

Any thoughts?

Thanks,

Seth


import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import time

engine = sa.create_engine('mssql+pyodbc://Compustat/Compustat')
session = sessionmaker(bind=engine, autoflush=False, 
expire_on_commit=False)()

class FooBar(declarative_base()):
__tablename__ = 'sec_dprc'
gvkey = sa.Column(sa.String(6), primary_key=True)
datadate = sa.Column(sa.DateTime, primary_key=True)
value = sa.Column(sa.Float, name='prcod')

def print_timing(func):
def wrapper(*arg):
t1 = time.time()
rows = func(*arg)
t2 = time.time()
print(%30s() len=%d, last=%s, runtime=%0.3fs % (str(func).split(' 
at')[0][10:], len(rows), rows[-1], t2 - t1))
return t2 - t1
return wrapper

if __name__ == '__main__':

key = '001045'
query = session.query(FooBar.datadate, 
FooBar.value).filter(sa.and_(FooBar.gvkey == key)).order_by(FooBar.datadate)
sql_select_statement_base = str(query)
print(sql_select_statement_base)

@print_timing
def execute_explicit_query():
sql_select_statement_explicit = 
sql_select_statement_base.replace(:gvkey_1, '%s' % key)
rows = 
engine.execute(sa.text(sql_select_statement_explicit)).fetchall()
return rows

@print_timing
def execute_parameterized_query():
rows = engine.execute(sa.text(sql_select_statement_base), 
{'gvkey_1':key}).fetchall()
return rows

num_iterations = 5
explicit_runtime = 0.0
parameterized_runtime = 0.0
for i in range(num_iterations):
explicit_runtime += execute_explicit_query()
parameterized_runtime += execute_parameterized_query()
print(Total runtime for %d explicit queries = %0.3fs. % 
(num_iterations, explicit_runtime))
print(Total runtime for %d parameterized queries = %0.3fs. % 
(num_iterations, parameterized_runtime))


SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
sec_dprc_prcod 
FROM sec_dprc 
WHERE sec_dprc.gvkey = :gvkey_1 ORDER BY sec_dprc.datadate
execute_explicit_query() len=10971, last=(datetime.datetime(2014, 
5, 9, 0, 0), 37.96), runtime=0.082s
   execute_parameterized_query() len=10971, last=(datetime.datetime(2014, 
5, 9, 0, 0), 37.96), runtime=8.852s
execute_explicit_query() len=10971, last=(datetime.datetime(2014, 
5, 9, 0, 0), 37.96), runtime=0.032s
   execute_parameterized_query() len=10971, last=(datetime.datetime(2014, 
5, 9, 0, 0), 37.96), runtime=8.754s
execute_explicit_query() len=10971, last=(datetime.datetime(2014, 
5, 9, 0, 0), 37.96), runtime=0.039s
   execute_parameterized_query() len=10971, last=(datetime.datetime(2014, 
5, 9, 0, 0), 37.96), runtime=9.182s
execute_explicit_query() len=10971, last=(datetime.datetime(2014, 
5, 9, 0, 0), 37.96), runtime=0.028s
   execute_parameterized_query() len=10971, last=(datetime.datetime(2014, 
5, 9, 0, 0), 37.96), runtime=9.416s
execute_explicit_query() len=10971, last=(datetime.datetime(2014, 
5, 9, 0, 0), 37.96), runtime=0.080s
   execute_parameterized_query() len=10971, last=(datetime.datetime(2014, 
5, 9, 0, 0), 37.96), runtime=8.425s
Total runtime for 5 explicit queries = 0.260s.
Total runtime for 5 parameterized queries = 44.629s.

-- 
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] Re: engine.execute() slow with parameterized queries?

[Seth P]

Forgot to mention that I'm running SQLAlchemy 0.9.4  on 64-bit Python 3.4.0 
on Windows.

On Monday, May 12, 2014 3:48:44 PM UTC-4, Seth P wrote:

 After tracking down some extreme slowness in loading a one-to-many 
 relationship (e.g. myobject.foobars), I seem to have isolated the issue to 
 engine.execute() being much slower with parameterized queries than with 
 explicit queries. The following is actual code and output for loading 
 10,971 rows from a database table. (The actual table has more columns than 
 I'm including here, and is not designed by me.) Note that each explicit 
 query (where I explicitly set the WHERE clause parameter and pass the 
 resulting SQL statement to engine.execute()) runs in under 0.1 seconds, 
 whereas each parameterized query (where I let SQLAlchemy bind the WHERE 
 clause parameter) takes over 8 seconds.

 The difference in runtimes is smaller when the number of rows returned is 
 smaller, which seems odd since I would have thought that the binding of the 
 WHERE clause parameters is just done once and would be virtually 
 instantaneous.

 Any thoughts?

 Thanks,

 Seth


 import sqlalchemy as sa
 from sqlalchemy.orm import sessionmaker
 from sqlalchemy.ext.declarative import declarative_base
 import time

 engine = sa.create_engine('mssql+pyodbc://Compustat/Compustat')
 session = sessionmaker(bind=engine, autoflush=False, 
 expire_on_commit=False)()

 class FooBar(declarative_base()):
 __tablename__ = 'sec_dprc'
 gvkey = sa.Column(sa.String(6), primary_key=True)
 datadate = sa.Column(sa.DateTime, primary_key=True)
 value = sa.Column(sa.Float, name='prcod')

 def print_timing(func):
 def wrapper(*arg):
 t1 = time.time()
 rows = func(*arg)
 t2 = time.time()
 print(%30s() len=%d, last=%s, runtime=%0.3fs % 
 (str(func).split(' at')[0][10:], len(rows), rows[-1], t2 - t1))
 return t2 - t1
 return wrapper

 if __name__ == '__main__':

 key = '001045'
 query = session.query(FooBar.datadate, 
 FooBar.value).filter(sa.and_(FooBar.gvkey == key)).order_by(FooBar.datadate)
 sql_select_statement_base = str(query)
 print(sql_select_statement_base)

 @print_timing
 def execute_explicit_query():
 sql_select_statement_explicit = 
 sql_select_statement_base.replace(:gvkey_1, '%s' % key)
 rows = 
 engine.execute(sa.text(sql_select_statement_explicit)).fetchall()
 return rows

 @print_timing
 def execute_parameterized_query():
 rows = engine.execute(sa.text(sql_select_statement_base), 
 {'gvkey_1':key}).fetchall()
 return rows

 num_iterations = 5
 explicit_runtime = 0.0
 parameterized_runtime = 0.0
 for i in range(num_iterations):
 explicit_runtime += execute_explicit_query()
 parameterized_runtime += execute_parameterized_query()
 print(Total runtime for %d explicit queries = %0.3fs. % 
 (num_iterations, explicit_runtime))
 print(Total runtime for %d parameterized queries = %0.3fs. % 
 (num_iterations, parameterized_runtime))


 SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
 sec_dprc_prcod 
 FROM sec_dprc 
 WHERE sec_dprc.gvkey = :gvkey_1 ORDER BY sec_dprc.datadate
 execute_explicit_query() len=10971, last=(datetime.datetime(2014, 
 5, 9, 0, 0), 37.96), runtime=0.082s
execute_parameterized_query() len=10971, last=(datetime.datetime(2014, 
 5, 9, 0, 0), 37.96), runtime=8.852s
 execute_explicit_query() len=10971, last=(datetime.datetime(2014, 
 5, 9, 0, 0), 37.96), runtime=0.032s
execute_parameterized_query() len=10971, last=(datetime.datetime(2014, 
 5, 9, 0, 0), 37.96), runtime=8.754s
 execute_explicit_query() len=10971, last=(datetime.datetime(2014, 
 5, 9, 0, 0), 37.96), runtime=0.039s
execute_parameterized_query() len=10971, last=(datetime.datetime(2014, 
 5, 9, 0, 0), 37.96), runtime=9.182s
 execute_explicit_query() len=10971, last=(datetime.datetime(2014, 
 5, 9, 0, 0), 37.96), runtime=0.028s
execute_parameterized_query() len=10971, last=(datetime.datetime(2014, 
 5, 9, 0, 0), 37.96), runtime=9.416s
 execute_explicit_query() len=10971, last=(datetime.datetime(2014, 
 5, 9, 0, 0), 37.96), runtime=0.080s
execute_parameterized_query() len=10971, last=(datetime.datetime(2014, 
 5, 9, 0, 0), 37.96), runtime=8.425s
 Total runtime for 5 explicit queries = 0.260s.
 Total runtime for 5 parameterized queries = 44.629s.



-- 
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] engine.execute() slow with parameterized queries?

[Seth P]

Is it possible that the (primary key index (which is a composite index that 
begins with gvkey, and is the only index on the table) isn't being used 
because the the gvkey parameter is somehow passed as an integer rather than 
as a string?
The first EXEC below is pretty much instantaneous, whereas the second takes 
about 8 seconds (and produces the same results).

EXEC sp_executesql
N'SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
sec_dprc_prcod
FROM sec_dprc WHERE sec_dprc.gvkey = @gvkey ORDER BY sec_dprc.datadate',
N'@gvkey VARCHAR(6)', '001045'

EXEC sp_executesql
N'SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
sec_dprc_prcod
FROM sec_dprc WHERE sec_dprc.gvkey = @gvkey ORDER BY sec_dprc.datadate',
N'@gvkey INT', 001045



On Monday, May 12, 2014 5:00:27 PM UTC-4, Michael Bayer wrote:


 well there’s only one parameter being processed here so there is clearly 
 negligible difference in time spent within Python as far as getting the 
 statement ready to execute and then executing it.

 So the time is either in what SQL Server spends fetching the rows, or the 
 number of rows being fetched (which seems to be the same).   Which leaves 
 pretty much that SQL Server is making a different choice about the query 
 plan for this SELECT statement, this is typically due to an INDEX being 
 used or not.You’d need to analyze the plan being used.   With SQL 
 Server, the option to get a plan within programmatic execution seems to be 
 per this answer 
 http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-planto
  execute “SET SHOWPLAN_TEXT ON” ahead of time.

 Besides that, you can confirm where the time is being spent exactly using 
 Python profiling.   A description on how to achieve that is here: 
 http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677



 On May 12, 2014, at 3:48 PM, Seth P spad...@gmail.com javascript: 
 wrote:

 After tracking down some extreme slowness in loading a one-to-many 
 relationship (e.g. myobject.foobars), I seem to have isolated the issue to 
 engine.execute() being much slower with parameterized queries than with 
 explicit queries. The following is actual code and output for loading 
 10,971 rows from a database table. (The actual table has more columns than 
 I'm including here, and is not designed by me.) Note that each explicit 
 query (where I explicitly set the WHERE clause parameter and pass the 
 resulting SQL statement to engine.execute()) runs in under 0.1 seconds, 
 whereas each parameterized query (where I let SQLAlchemy bind the WHERE 
 clause parameter) takes over 8 seconds.

 The difference in runtimes is smaller when the number of rows returned is 
 smaller, which seems odd since I would have thought that the binding of the 
 WHERE clause parameters is just done once and would be virtually 
 instantaneous.

 Any thoughts?

 Thanks,

 Seth


 import sqlalchemy as sa
 from sqlalchemy.orm import sessionmaker
 from sqlalchemy.ext.declarative import declarative_base
 import time

 engine = sa.create_engine('mssql+pyodbc://Compustat/Compustat')
 session = sessionmaker(bind=engine, autoflush=False, 
 expire_on_commit=False)()

 class FooBar(declarative_base()):
 __tablename__ = 'sec_dprc'
 gvkey = sa.Column(sa.String(6), primary_key=True)
 datadate = sa.Column(sa.DateTime, primary_key=True)
 value = sa.Column(sa.Float, name='prcod')

 def print_timing(func):
 def wrapper(*arg):
 t1 = time.time()
 rows = func(*arg)
 t2 = time.time()
 print(%30s() len=%d, last=%s, runtime=%0.3fs % 
 (str(func).split(' at')[0][10:], len(rows), rows[-1], t2 - t1))
 return t2 - t1
 return wrapper

 if __name__ == '__main__':

 key = '001045'
 query = session.query(FooBar.datadate, 
 FooBar.value).filter(sa.and_(FooBar.gvkey == key)).order_by(FooBar.datadate)
 sql_select_statement_base = str(query)
 print(sql_select_statement_base)

 @print_timing
 def execute_explicit_query():
 sql_select_statement_explicit = 
 sql_select_statement_base.replace(:gvkey_1, '%s' % key)
 rows = 
 engine.execute(sa.text(sql_select_statement_explicit)).fetchall()
 return rows

 @print_timing
 def execute_parameterized_query():
 rows = engine.execute(sa.text(sql_select_statement_base), 
 {'gvkey_1':key}).fetchall()
 return rows

 num_iterations = 5
 explicit_runtime = 0.0
 parameterized_runtime = 0.0
 for i in range(num_iterations):
 explicit_runtime += execute_explicit_query()
 parameterized_runtime += execute_parameterized_query()
 print(Total runtime for %d explicit queries = %0.3fs. % 
 (num_iterations, explicit_runtime))
 print(Total runtime for %d parameterized queries = %0.3fs. % 
 (num_iterations, parameterized_runtime))


 SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
 sec_dprc_prcod 
 FROM

Re: [sqlalchemy] engine.execute() slow with parameterized queries?

[Seth P]

Yep, it's not a SQLAlchemy issue. The following code demonstrates the 
problem with direct pyodbc access.

import pyodbc
import time

def print_timing(func):
def wrapper(*arg):
t1 = time.time()
rows = func(*arg)
t2 = time.time()
print(%30s() len=%d, last=%s, runtime=%0.3fs % (str(func).split(' 
at')[0][10:], len(rows), rows[-1], t2 - t1))
return t2 - t1
return wrapper

if __name__ == '__main__':
cnxn = pyodbc.connect('DRIVER={SQL 
Server};SERVER=Compustat;DATABASE=Compustat')
cursor = cnxn.cursor()
sql_select_statement_base = SELECT datadate, prcod FROM sec_dprc WHERE 
gvkey = ? ORDER BY datadate
key = '001045'

@print_timing
def execute_explicit_query():
sql_select_statement_explicit = 
sql_select_statement_base.replace(?, '%s' % key)
rows = cursor.execute(sql_select_statement_explicit).fetchall()
return rows

@print_timing
def execute_parameterized_query():
rows = cursor.execute(sql_select_statement_base, key).fetchall()
return rows

num_iterations = 5
explicit_runtime = 0.0
parameterized_runtime = 0.0
for i in range(num_iterations):
explicit_runtime += execute_explicit_query()
parameterized_runtime += execute_parameterized_query()
print(Total runtime for %d explicit queries = %0.3fs. % 
(num_iterations, explicit_runtime))
print(Total runtime for %d parameterized queries = %0.3fs. % 
(num_iterations, parameterized_runtime))


On Monday, May 12, 2014 6:40:48 PM UTC-4, Michael Bayer wrote:


 On May 12, 2014, at 6:33 PM, Seth P spad...@gmail.com javascript: 
 wrote:

 Is it possible that the (primary key index (which is a composite index 
 that begins with gvkey, and is the only index on the table) isn't being 
 used because the the gvkey parameter is somehow passed as an integer rather 
 than as a string?


 There’s nothing in SQLAlchemy that coerces strings to integers.  If the 
 actual type of the column on the DB is an integer, then there might be some 
 conversion within pyodbc or the ODBC driver.

 if you’ve got it narrowed down this much the next step is to figure out a 
 raw pyodbc script that illustrates what the problem is. 


 The first EXEC below is pretty much instantaneous, whereas the second 
 takes about 8 seconds (and produces the same results).

 EXEC sp_executesql
 N'SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
 sec_dprc_prcod
 FROM sec_dprc WHERE sec_dprc.gvkey = @gvkey ORDER BY sec_dprc.datadate',
 N'@gvkey VARCHAR(6)', '001045'

 EXEC sp_executesql
 N'SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
 sec_dprc_prcod
 FROM sec_dprc WHERE sec_dprc.gvkey = @gvkey ORDER BY sec_dprc.datadate',
 N'@gvkey INT', 001045



 On Monday, May 12, 2014 5:00:27 PM UTC-4, Michael Bayer wrote:


 well there’s only one parameter being processed here so there is clearly 
 negligible difference in time spent within Python as far as getting the 
 statement ready to execute and then executing it.

 So the time is either in what SQL Server spends fetching the rows, or the 
 number of rows being fetched (which seems to be the same).   Which leaves 
 pretty much that SQL Server is making a different choice about the query 
 plan for this SELECT statement, this is typically due to an INDEX being 
 used or not.You’d need to analyze the plan being used.   With SQL 
 Server, the option to get a plan within programmatic execution seems to be 
 per this answer 
 http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-planto
  execute “SET SHOWPLAN_TEXT ON” ahead of time.

 Besides that, you can confirm where the time is being spent exactly using 
 Python profiling.   A description on how to achieve that is here: 
 http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677



 On May 12, 2014, at 3:48 PM, Seth P spad...@gmail.com wrote:

 After tracking down some extreme slowness in loading a one-to-many 
 relationship (e.g. myobject.foobars), I seem to have isolated the issue to 
 engine.execute() being much slower with parameterized queries than with 
 explicit queries. The following is actual code and output for loading 
 10,971 rows from a database table. (The actual table has more columns than 
 I'm including here, and is not designed by me.) Note that each explicit 
 query (where I explicitly set the WHERE clause parameter and pass the 
 resulting SQL statement to engine.execute()) runs in under 0.1 seconds, 
 whereas each parameterized query (where I let SQLAlchemy bind the WHERE 
 clause parameter) takes over 8 seconds.

 The difference in runtimes is smaller when the number of rows returned is 
 smaller, which seems odd since I would have thought that the binding of the 
 WHERE clause parameters is just done once and would be virtually 
 instantaneous.

 Any thoughts?

 Thanks,

 Seth


 import sqlalchemy as sa
 from sqlalchemy.orm import

Re: [sqlalchemy] engine.execute() slow with parameterized queries?

[Seth P]

Looks like other people have encountered similar problems with indices 
being ignored by prepared sql statements: 
http://www.postgresql.org/message-id/43250afa.7010...@arbash-meinel.com. 
(If the diagnosis there is correct, then I'm guessing the server would use 
a unique index where all the columns of the index are specified.) Also, 
Thierry Florac's post 
https://groups.google.com/forum/#!topic/sqlalchemy/k_9ZGI-e85E sounds 
similar.
(I suspect my earlier hypothesis about int vs varchar is a red herring.)

I think it would be useful (albeit risky, if not careful) to have an option 
to plug in parameters client-side. I presume not trivial to add to 
SQLAlchemy? I don't see such an option for pyodbc.

On Monday, May 12, 2014 7:09:08 PM UTC-4, Seth P wrote:

 Yep, it's not a SQLAlchemy issue. The following code demonstrates the 
 problem with direct pyodbc access.

 import pyodbc
 import time

 def print_timing(func):
 def wrapper(*arg):
 t1 = time.time()
 rows = func(*arg)
 t2 = time.time()
 print(%30s() len=%d, last=%s, runtime=%0.3fs % 
 (str(func).split(' at')[0][10:], len(rows), rows[-1], t2 - t1))
 return t2 - t1
 return wrapper

 if __name__ == '__main__':
 cnxn = pyodbc.connect('DRIVER={SQL 
 Server};SERVER=Compustat;DATABASE=Compustat')
 cursor = cnxn.cursor()
 sql_select_statement_base = SELECT datadate, prcod FROM sec_dprc 
 WHERE gvkey = ? ORDER BY datadate
 key = '001045'

 @print_timing
 def execute_explicit_query():
 sql_select_statement_explicit = 
 sql_select_statement_base.replace(?, '%s' % key)
 rows = cursor.execute(sql_select_statement_explicit).fetchall()
 return rows

 @print_timing
 def execute_parameterized_query():
 rows = cursor.execute(sql_select_statement_base, key).fetchall()
 return rows

 num_iterations = 5
 explicit_runtime = 0.0
 parameterized_runtime = 0.0
 for i in range(num_iterations):
 explicit_runtime += execute_explicit_query()
 parameterized_runtime += execute_parameterized_query()
 print(Total runtime for %d explicit queries = %0.3fs. % 
 (num_iterations, explicit_runtime))
 print(Total runtime for %d parameterized queries = %0.3fs. % 
 (num_iterations, parameterized_runtime))


 On Monday, May 12, 2014 6:40:48 PM UTC-4, Michael Bayer wrote:


 On May 12, 2014, at 6:33 PM, Seth P spad...@gmail.com wrote:

 Is it possible that the (primary key index (which is a composite index 
 that begins with gvkey, and is the only index on the table) isn't being 
 used because the the gvkey parameter is somehow passed as an integer rather 
 than as a string?


 There’s nothing in SQLAlchemy that coerces strings to integers.  If the 
 actual type of the column on the DB is an integer, then there might be some 
 conversion within pyodbc or the ODBC driver.

 if you’ve got it narrowed down this much the next step is to figure out a 
 raw pyodbc script that illustrates what the problem is. 


 The first EXEC below is pretty much instantaneous, whereas the second 
 takes about 8 seconds (and produces the same results).

 EXEC sp_executesql
 N'SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
 sec_dprc_prcod
 FROM sec_dprc WHERE sec_dprc.gvkey = @gvkey ORDER BY sec_dprc.datadate',
 N'@gvkey VARCHAR(6)', '001045'

 EXEC sp_executesql
 N'SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
 sec_dprc_prcod
 FROM sec_dprc WHERE sec_dprc.gvkey = @gvkey ORDER BY sec_dprc.datadate',
 N'@gvkey INT', 001045



 On Monday, May 12, 2014 5:00:27 PM UTC-4, Michael Bayer wrote:


 well there’s only one parameter being processed here so there is clearly 
 negligible difference in time spent within Python as far as getting the 
 statement ready to execute and then executing it.

 So the time is either in what SQL Server spends fetching the rows, or 
 the number of rows being fetched (which seems to be the same).   Which 
 leaves pretty much that SQL Server is making a different choice about the 
 query plan for this SELECT statement, this is typically due to an INDEX 
 being used or not.You’d need to analyze the plan being used.   With SQL 
 Server, the option to get a plan within programmatic execution seems to be 
 per this answer 
 http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-planto
  execute “SET SHOWPLAN_TEXT ON” ahead of time.

 Besides that, you can confirm where the time is being spent exactly 
 using Python profiling.   A description on how to achieve that is here: 
 http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677



 On May 12, 2014, at 3:48 PM, Seth P spad...@gmail.com wrote:

 After tracking down some extreme slowness in loading a one-to-many 
 relationship (e.g. myobject.foobars), I seem to have isolated the issue to 
 engine.execute() being much slower with parameterized queries than with 
 explicit

Re: [sqlalchemy] engine.execute() slow with parameterized queries?

[Seth P]

Fair enough. I'll take a look at pymssql, though I suspect it may be a SQL 
Server rather than a driver issue.


On Monday, May 12, 2014 7:50:03 PM UTC-4, Michael Bayer wrote:


 On May 12, 2014, at 7:35 PM, Seth P spad...@gmail.com javascript: 
 wrote:

 Looks like other people have encountered similar problems with indices 
 being ignored by prepared sql statements: 
 http://www.postgresql.org/message-id/43250afa.7010...@arbash-meinel.com. 
 (If the diagnosis there is correct, then I'm guessing the server would use 
 a unique index where all the columns of the index are specified.) Also, 
 Thierry Florac's post 
 https://groups.google.com/forum/#!topic/sqlalchemy/k_9ZGI-e85E sounds 
 similar.
 (I suspect my earlier hypothesis about int vs varchar is a red herring.)

 I think it would be useful (albeit risky, if not careful) to have an 
 option to plug in parameters client-side. I presume not trivial to add to 
 SQLAlchemy? I don't see such an option for pyodbc.


 there’s mechanisms for this but they aren’t very widely advertised since 
 as you know allowing people to do such would be an *enormous* security 
 hole, and I don’t have the resources to be responsible for parameter 
 escaping.   It would be better if you could try pymssql (much more actively 
 maintained than pyodbc from what i can tell) and/or file a bug with pyodbc.



 On Monday, May 12, 2014 7:09:08 PM UTC-4, Seth P wrote:

 Yep, it's not a SQLAlchemy issue. The following code demonstrates the 
 problem with direct pyodbc access.

 import pyodbc
 import time

 def print_timing(func):
 def wrapper(*arg):
 t1 = time.time()
 rows = func(*arg)
 t2 = time.time()
 print(%30s() len=%d, last=%s, runtime=%0.3fs % 
 (str(func).split(' at')[0][10:], len(rows), rows[-1], t2 - t1))
 return t2 - t1
 return wrapper

 if __name__ == '__main__':
 cnxn = pyodbc.connect('DRIVER={SQL 
 Server};SERVER=Compustat;DATABASE=Compustat')
 cursor = cnxn.cursor()
 sql_select_statement_base = SELECT datadate, prcod FROM sec_dprc 
 WHERE gvkey = ? ORDER BY datadate
 key = '001045'

 @print_timing
 def execute_explicit_query():
 sql_select_statement_explicit = 
 sql_select_statement_base.replace(?, '%s' % key)
 rows = cursor.execute(sql_select_statement_explicit).fetchall()
 return rows

 @print_timing
 def execute_parameterized_query():
 rows = cursor.execute(sql_select_statement_base, key).fetchall()
 return rows

 num_iterations = 5
 explicit_runtime = 0.0
 parameterized_runtime = 0.0
 for i in range(num_iterations):
 explicit_runtime += execute_explicit_query()
 parameterized_runtime += execute_parameterized_query()
 print(Total runtime for %d explicit queries = %0.3fs. % 
 (num_iterations, explicit_runtime))
 print(Total runtime for %d parameterized queries = %0.3fs. % 
 (num_iterations, parameterized_runtime))


 On Monday, May 12, 2014 6:40:48 PM UTC-4, Michael Bayer wrote:


 On May 12, 2014, at 6:33 PM, Seth P spad...@gmail.com wrote:

 Is it possible that the (primary key index (which is a composite index 
 that begins with gvkey, and is the only index on the table) isn't being 
 used because the the gvkey parameter is somehow passed as an integer rather 
 than as a string?


 There’s nothing in SQLAlchemy that coerces strings to integers.  If the 
 actual type of the column on the DB is an integer, then there might be some 
 conversion within pyodbc or the ODBC driver.

 if you’ve got it narrowed down this much the next step is to figure out 
 a raw pyodbc script that illustrates what the problem is. 


 The first EXEC below is pretty much instantaneous, whereas the second 
 takes about 8 seconds (and produces the same results).

 EXEC sp_executesql
 N'SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
 sec_dprc_prcod
 FROM sec_dprc WHERE sec_dprc.gvkey = @gvkey ORDER BY sec_dprc.datadate',
 N'@gvkey VARCHAR(6)', '001045'

 EXEC sp_executesql
 N'SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
 sec_dprc_prcod
 FROM sec_dprc WHERE sec_dprc.gvkey = @gvkey ORDER BY sec_dprc.datadate',
 N'@gvkey INT', 001045



 On Monday, May 12, 2014 5:00:27 PM UTC-4, Michael Bayer wrote:


 well there’s only one parameter being processed here so there is 
 clearly negligible difference in time spent within Python as far as 
 getting 
 the statement ready to execute and then executing it.

 So the time is either in what SQL Server spends fetching the rows, or 
 the number of rows being fetched (which seems to be the same).   Which 
 leaves pretty much that SQL Server is making a different choice about the 
 query plan for this SELECT statement, this is typically due to an INDEX 
 being used or not.You’d need to analyze the plan being used.   With 
 SQL 
 Server, the option to get a plan within programmatic execution seems to be 
 per this answer 
 http://stackoverflow.com/questions

Re: [sqlalchemy] engine.execute() slow with parameterized queries?

[Seth P]

pymssql produces the same results as pyodbc. So it looks like a SQL Server 
issue.

On Monday, May 12, 2014 8:06:08 PM UTC-4, Seth P wrote:

 Fair enough. I'll take a look at pymssql, though I suspect it may be a SQL 
 Server rather than a driver issue.


 On Monday, May 12, 2014 7:50:03 PM UTC-4, Michael Bayer wrote:


 On May 12, 2014, at 7:35 PM, Seth P spad...@gmail.com wrote:

 Looks like other people have encountered similar problems with indices 
 being ignored by prepared sql statements: 
 http://www.postgresql.org/message-id/43250afa.7010...@arbash-meinel.com. 
 (If the diagnosis there is correct, then I'm guessing the server would use 
 a unique index where all the columns of the index are specified.) Also, 
 Thierry Florac's post 
 https://groups.google.com/forum/#!topic/sqlalchemy/k_9ZGI-e85E sounds 
 similar.
 (I suspect my earlier hypothesis about int vs varchar is a red herring.)

 I think it would be useful (albeit risky, if not careful) to have an 
 option to plug in parameters client-side. I presume not trivial to add to 
 SQLAlchemy? I don't see such an option for pyodbc.


 there’s mechanisms for this but they aren’t very widely advertised since 
 as you know allowing people to do such would be an *enormous* security 
 hole, and I don’t have the resources to be responsible for parameter 
 escaping.   It would be better if you could try pymssql (much more actively 
 maintained than pyodbc from what i can tell) and/or file a bug with pyodbc.



 On Monday, May 12, 2014 7:09:08 PM UTC-4, Seth P wrote:

 Yep, it's not a SQLAlchemy issue. The following code demonstrates the 
 problem with direct pyodbc access.

 import pyodbc
 import time

 def print_timing(func):
 def wrapper(*arg):
 t1 = time.time()
 rows = func(*arg)
 t2 = time.time()
 print(%30s() len=%d, last=%s, runtime=%0.3fs % 
 (str(func).split(' at')[0][10:], len(rows), rows[-1], t2 - t1))
 return t2 - t1
 return wrapper

 if __name__ == '__main__':
 cnxn = pyodbc.connect('DRIVER={SQL 
 Server};SERVER=Compustat;DATABASE=Compustat')
 cursor = cnxn.cursor()
 sql_select_statement_base = SELECT datadate, prcod FROM sec_dprc 
 WHERE gvkey = ? ORDER BY datadate
 key = '001045'

 @print_timing
 def execute_explicit_query():
 sql_select_statement_explicit = 
 sql_select_statement_base.replace(?, '%s' % key)
 rows = cursor.execute(sql_select_statement_explicit).fetchall()
 return rows

 @print_timing
 def execute_parameterized_query():
 rows = cursor.execute(sql_select_statement_base, key).fetchall()
 return rows

 num_iterations = 5
 explicit_runtime = 0.0
 parameterized_runtime = 0.0
 for i in range(num_iterations):
 explicit_runtime += execute_explicit_query()
 parameterized_runtime += execute_parameterized_query()
 print(Total runtime for %d explicit queries = %0.3fs. % 
 (num_iterations, explicit_runtime))
 print(Total runtime for %d parameterized queries = %0.3fs. % 
 (num_iterations, parameterized_runtime))


 On Monday, May 12, 2014 6:40:48 PM UTC-4, Michael Bayer wrote:


 On May 12, 2014, at 6:33 PM, Seth P spad...@gmail.com wrote:

 Is it possible that the (primary key index (which is a composite index 
 that begins with gvkey, and is the only index on the table) isn't being 
 used because the the gvkey parameter is somehow passed as an integer 
 rather 
 than as a string?


 There’s nothing in SQLAlchemy that coerces strings to integers.  If the 
 actual type of the column on the DB is an integer, then there might be 
 some 
 conversion within pyodbc or the ODBC driver.

 if you’ve got it narrowed down this much the next step is to figure out 
 a raw pyodbc script that illustrates what the problem is. 


 The first EXEC below is pretty much instantaneous, whereas the second 
 takes about 8 seconds (and produces the same results).

 EXEC sp_executesql
 N'SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
 sec_dprc_prcod
 FROM sec_dprc WHERE sec_dprc.gvkey = @gvkey ORDER BY sec_dprc.datadate',
 N'@gvkey VARCHAR(6)', '001045'

 EXEC sp_executesql
 N'SELECT sec_dprc.datadate AS sec_dprc_datadate, sec_dprc.prcod AS 
 sec_dprc_prcod
 FROM sec_dprc WHERE sec_dprc.gvkey = @gvkey ORDER BY sec_dprc.datadate',
 N'@gvkey INT', 001045



 On Monday, May 12, 2014 5:00:27 PM UTC-4, Michael Bayer wrote:


 well there’s only one parameter being processed here so there is 
 clearly negligible difference in time spent within Python as far as 
 getting 
 the statement ready to execute and then executing it.

 So the time is either in what SQL Server spends fetching the rows, or 
 the number of rows being fetched (which seems to be the same).   Which 
 leaves pretty much that SQL Server is making a different choice about the 
 query plan for this SELECT statement, this is typically due to an INDEX 
 being used or not.You’d need to analyze the plan being used.   With 
 SQL

[sqlalchemy] Problem with _compiler_dispatch() call?

[Seth P]

I get the following error when trying to create_all() in a sqlite database:
TypeError: _compiler_dispatch() missing 1 required positional argument: 
'visitor'

Looking at annotation.py and visitors.py, all instances of 
_compiler_dispatch() do indeed appear to expect a 'visitor' argument, which 
is not provided in the type_._compiler_dispatch() call from 
TypeCompiler.process() in line 266 of compiler.py. The end of the traceback 
is below.

I'm using SQLAlchemy 0.9.4, Python 3.4.0, and alchy 0.11.2.

Thanks,

Seth
...

C:\Python34\lib\site-packages\sqlalchemy\sql\visitors.py in 
_compiler_dispatch(self, visitor, **kw)
 77 raise exc.UnsupportedCompilationError(visitor, 
cls)
 78 else:
--- 79 return meth(self, **kw)
 80 else:
 81 # The optimization opportunity is lost for this case 
because the

C:\Python34\lib\site-packages\sqlalchemy\sql\compiler.py in 
visit_create_column(self, create, first_pk)
   2368 text = self.get_column_specification(
   2369 column,
- 2370 first_pk=first_pk
   2371 )
   2372 const =  .join(self.process(constraint) \

C:\Python34\lib\site-packages\sqlalchemy\dialects\sqlite\base.py in 
get_column_specification(self, column, **kwargs)
538
539 def get_column_specification(self, column, **kwargs):
-- 540 coltype = self.dialect.type_compiler.process(column.type)
541 colspec = self.preparer.format_column(column) +   + 
coltype
542 default = self.get_column_default_string(column)

C:\Python34\lib\site-packages\sqlalchemy\sql\compiler.py in process(self, 
type_)
264
265 def process(self, type_):
-- 266 return type_._compiler_dispatch(self)
267
268

C:\Python34\lib\site-packages\sqlalchemy\sql\visitors.py in 
_compiler_dispatch(self, visitor, **kw)
 77 raise exc.UnsupportedCompilationError(visitor, 
cls)
 78 else:
--- 79 return meth(self, **kw)
 80 else:
 81 # The optimization opportunity is lost for this case 
because the

C:\Python34\lib\site-packages\sqlalchemy\sql\compiler.py in 
visit_type_decorator(self, type_)
   2755
   2756 def visit_type_decorator(self, type_):
- 2757 return self.process(type_.type_engine(self.dialect))
   2758
   2759 def visit_user_defined(self, type_):

C:\Python34\lib\site-packages\sqlalchemy\sql\compiler.py in process(self, 
type_)
264
265 def process(self, type_):
-- 266 return type_._compiler_dispatch(self)
267
268

TypeError: _compiler_dispatch() missing 1 required positional argument: 
'visitor'

-- 
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] Re: Problem with _compiler_dispatch() call?

[Seth P]

Of course you are right. My problem code was basically the PythonEnum class 
in https://groups.google.com/d/topic/sqlalchemy/5yvdhl9ErMo/discussion. 
Deleting the *(m.name for m in enum_class), from the __init__ call seems 
to have fixed the problem.

Thanks again.

Seth


On Monday, May 5, 2014 3:07:50 PM UTC-4, Seth P wrote:

 I get the following error when trying to create_all() in a sqlite database:
 TypeError: _compiler_dispatch() missing 1 required positional argument: 
 'visitor'

 Looking at annotation.py and visitors.py, all instances of 
 _compiler_dispatch() do indeed appear to expect a 'visitor' argument, which 
 is not provided in the type_._compiler_dispatch() call from 
 TypeCompiler.process() in line 266 of compiler.py. The end of the traceback 
 is below.

 I'm using SQLAlchemy 0.9.4, Python 3.4.0, and alchy 0.11.2.

 Thanks,

 Seth
 ...

 C:\Python34\lib\site-packages\sqlalchemy\sql\visitors.py in 
 _compiler_dispatch(self, visitor, **kw)
  77 raise exc.UnsupportedCompilationError(visitor, 
 cls)
  78 else:
 --- 79 return meth(self, **kw)
  80 else:
  81 # The optimization opportunity is lost for this case 
 because the

 C:\Python34\lib\site-packages\sqlalchemy\sql\compiler.py in 
 visit_create_column(self, create, first_pk)
2368 text = self.get_column_specification(
2369 column,
 - 2370 first_pk=first_pk
2371 )
2372 const =  .join(self.process(constraint) \

 C:\Python34\lib\site-packages\sqlalchemy\dialects\sqlite\base.py in 
 get_column_specification(self, column, **kwargs)
 538
 539 def get_column_specification(self, column, **kwargs):
 -- 540 coltype = self.dialect.type_compiler.process(column.type)
 541 colspec = self.preparer.format_column(column) +   + 
 coltype
 542 default = self.get_column_default_string(column)

 C:\Python34\lib\site-packages\sqlalchemy\sql\compiler.py in process(self, 
 type_)
 264
 265 def process(self, type_):
 -- 266 return type_._compiler_dispatch(self)
 267
 268

 C:\Python34\lib\site-packages\sqlalchemy\sql\visitors.py in 
 _compiler_dispatch(self, visitor, **kw)
  77 raise exc.UnsupportedCompilationError(visitor, 
 cls)
  78 else:
 --- 79 return meth(self, **kw)
  80 else:
  81 # The optimization opportunity is lost for this case 
 because the

 C:\Python34\lib\site-packages\sqlalchemy\sql\compiler.py in 
 visit_type_decorator(self, type_)
2755
2756 def visit_type_decorator(self, type_):
 - 2757 return self.process(type_.type_engine(self.dialect))
2758
2759 def visit_user_defined(self, type_):

 C:\Python34\lib\site-packages\sqlalchemy\sql\compiler.py in process(self, 
 type_)
 264
 265 def process(self, type_):
 -- 266 return type_._compiler_dispatch(self)
 267
 268

 TypeError: _compiler_dispatch() missing 1 required positional argument: 
 'visitor'



-- 
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] How to order a many-to-many association_proxy?

[Seth P]

Apologies if I'm missing this is the docs somewhere, but I can't figure it 
out. Suppose I have a many-to-many relationship between A and B, and that 
I'd like have the various B's that a particular A points to ordered by 
B.ordinal (i.e. in the examples below, I'd like A.bs to be sorted to 
B.order). I can't figure out how to do that. I tried the following:

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = association_proxy(a_to_b, b, creator=lambda x: A_to_B(b=x))

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

class A_to_B(Base):
a_id = Column(Integer, ForeignKey(str(A.__table__) + .id), 
nullable=False, index=True)
b_id = Column(Integer, ForeignKey(str(B.__table__) + .id), 
nullable=False)
b = relationship(B, foreign_keys=[b_id])
a = relationship(A, foreign_keys=[a_id],
 backref=backref(a_to_b, lazy=subquery, 
cascade=all, delete-orphan, order_by=b.ordinal))
def __init__(self, a=None, b=None):
self.a = a
self.b = b

but get an error message:
AttributeError: 'RelationshipProperty' object has no attribute 'ordinal'

Is it possible to combine an association_proxy with an order_by clause?

Thanks!

-- 
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/groups/opt_out.

[sqlalchemy] Re: How to order a many-to-many association_proxy?

[Seth P]

Just noticed that I had a typo, where I wrote order_by=b.ordinal rather 
than order_by=b.order. But changing it to order_by=b.order still gives:
AttributeError: 'RelationshipProperty' object has no attribute 'order'

-- 
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/groups/opt_out.

[sqlalchemy] How to order many-to-many association_proxy?

[Seth P]

How do I get the objects pointed to by a many-to-many association proxy to 
be sorted? In the example below, adding order_by=b.order to the backref() 
produces AttributeError: 'RelationshipProperty' object has no attribute 
'order', and adding order_by=b.order produces AttributeError: 'Table' 
object has no attribute 'order'. Is it possible to somehow specify order_by 
so that the a.bs are automatically sorted by b.order?

Thanks!

Seth

from sqlalchemy import Column, Integer, ForeignKey, create_engine
from sqlalchemy.orm import create_session, relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy

sqlite = 'sqlite:///test_a_to_b.db'
e = create_engine(sqlite, echo=False)
Base = declarative_base(bind=e)

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = association_proxy(a_to_b, b, creator=lambda x: A_to_B(b=x))

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

class A_to_B(Base):
__tablename__ = 'a_to_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey(str(A.__table__) + .id), 
nullable=False, index=True)
b_id = Column(Integer, ForeignKey(str(B.__table__) + .id), 
nullable=False)
b = relationship(B, foreign_keys=[b_id])
a = relationship(A, foreign_keys=[a_id],
 backref=backref(a_to_b, lazy=subquery, 
cascade=all, delete-orphan))  # , order_by=b.order))
def __init__(self, a=None, b=None):
self.a = a
self.b = b

if __name__ == '__main__':
Base.metadata.drop_all()
Base.metadata.create_all()
session = create_session(bind=e, autocommit=False)
a = A(bs=[B(order=10), B(order=2)])
session.add(a)
session.commit()
a = session.query(A).one()
for b in a.bs:
print b.order
session.close_all()

-- 
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/groups/opt_out.

Re: [sqlalchemy] How to order a many-to-many association_proxy?

[Seth P]

Thank you. This was very helpful.

One non-trivial thing that stumped me for a while is that if B is derived 
from a B_base using joined-table inheritance, and the order variable is in 
the base table B_base, then it seems one must include B_base explicitly -- 
as highlighted below.

from sqlalchemy import Column, Integer, ForeignKey, create_engine, select, 
join
from sqlalchemy.orm import create_session, relationship, backref, mapper
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy

sqlite = 'sqlite:///test_a_to_b.db'
e = create_engine(sqlite, echo=True)
Base = declarative_base(bind=e)

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = association_proxy(a_to_bs, b, creator=lambda x: A_to_B(b=x))

class B_base(Base):
__tablename__ = 'b_base'
id = Column(Integer, primary_key=True)
order = Column(Integer)

class B(B_base):
__tablename__ = 'b'
id = Column(Integer, ForeignKey(str(B_base.__table__) + .id), 
primary_key=True)

class A_to_B(Base):
__tablename__ = 'a_to_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey(str(A.__table__) + .id), 
nullable=False, index=True)
a = relationship(A, foreign_keys=[a_id],
 backref=backref(a_to_bs, 
primaryjoin=and_(A_to_B.a_id == A.id, A_to_B.b_id == B.id*, B.id == 
B_base.id*), order_by=B.order))
b_id = Column(Integer, ForeignKey(str(B.__table__) + .id), 
nullable=False)
b = relationship(B, foreign_keys=[b_id], lazy=subquery)
def __init__(self, a=None, b=None):
self.a = a
self.b = b

if __name__ == '__main__':
# recreate database
Base.metadata.drop_all()
Base.metadata.create_all()
# populate
session = create_session(bind=e, autocommit=False)
a = A(bs=[B(order=10), B(order=2)])
session.add(a)
session.commit()
session.close_all()
# read
session = create_session(bind=e, autocommit=False)
a = session.query(A).one()
for b in a.bs:
print b.order
for a_to_b in a.a_to_bs:
print a_to_b.b.order
session.close_all()

-- 
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/groups/opt_out.

Re: [sqlalchemy] How to order a many-to-many association_proxy?

[Seth P]

Good point, but unfortunately, unless I'm missing something, including only 
B_base and removing B from the join doesn't seem to work when A is also 
derived (using joined-table inheritance) from B_base (which is my actual 
situation, despite what the nomenclature here suggest).

On Thursday, February 27, 2014 9:26:31 PM UTC-5, Michael Bayer wrote:


 On Feb 27, 2014, at 9:23 PM, Seth P spad...@gmail.com javascript: 
 wrote:

 Thank you. This was very helpful.

 One non-trivial thing that stumped me for a while is that if B is derived 
 from a B_base using joined-table inheritance, and the order variable is in 
 the base table B_base, then it seems one must include B_base explicitly -- 
 as highlighted below.


 OK well maybe you just include B_base in the primaryjoin and not “B” at 
 all?   you want to include as few tables as possible in these things to 
 keep the query plans simpler.




 from sqlalchemy import Column, Integer, ForeignKey, create_engine, select, 
 join
 from sqlalchemy.orm import create_session, relationship, backref, mapper
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.ext.associationproxy import association_proxy

 sqlite = 'sqlite:///test_a_to_b.db'
 e = create_engine(sqlite, echo=True)
 Base = declarative_base(bind=e)

 class A(Base):
 __tablename__ = 'a'
 id = Column(Integer, primary_key=True)
 bs = association_proxy(a_to_bs, b, creator=lambda x: A_to_B(b=x))

 class B_base(Base):
 __tablename__ = 'b_base'
 id = Column(Integer, primary_key=True)
 order = Column(Integer)

 class B(B_base):
 __tablename__ = 'b'
 id = Column(Integer, ForeignKey(str(B_base.__table__) + .id), 
 primary_key=True)

 class A_to_B(Base):
 __tablename__ = 'a_to_b'
 id = Column(Integer, primary_key=True)
 a_id = Column(Integer, ForeignKey(str(A.__table__) + .id), 
 nullable=False, index=True)
 a = relationship(A, foreign_keys=[a_id],
  backref=backref(a_to_bs, 
 primaryjoin=and_(A_to_B.a_id == A.id, A_to_B.b_id == B.id*, B.id == 
 B_base.id*), order_by=B.order))
 b_id = Column(Integer, ForeignKey(str(B.__table__) + .id), 
 nullable=False)
 b = relationship(B, foreign_keys=[b_id], lazy=subquery)
 def __init__(self, a=None, b=None):
 self.a = a
 self.b = b

 if __name__ == '__main__':
 # recreate database
 Base.metadata.drop_all()
 Base.metadata.create_all()
 # populate
 session = create_session(bind=e, autocommit=False)
 a = A(bs=[B(order=10), B(order=2)])
 session.add(a)
 session.commit()
 session.close_all()
 # read
 session = create_session(bind=e, autocommit=False)
 a = session.query(A).one()
 for b in a.bs:
 print b.order
 for a_to_b in a.a_to_bs:
 print a_to_b.b.order
 session.close_all()


 -- 
 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 javascript:.
 To post to this group, send email to sqlal...@googlegroups.comjavascript:
 .
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.




-- 
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/groups/opt_out.

[sqlalchemy] session.is_modified() and floating-point numbers

[Seth P]

I have an issue where, I believe due to floating-point representation 
issues, reassigning the same value to a floating-point field causes 
SQLAlchemy to think the value has been modified, and therefore emits a 
gratuitous UPDATE. (This is particularly problematic when using the 
versioning mixin, 
http://docs.sqlalchemy.org/en/rel_0_8/orm/examples.html?highlight=versioned.) 
This doesn't happen all the time, e.g. in the example below the problem 
disappears if exp is imported from math rather than from numpy.ma.core. Am 
I doing something wrong? If not, is there a way to tell SQLAlchemy that a 
particular Float column (or even all Float columns) should not be 
considered modified if the new and old values differ by less than some 
threshold? Is there a way to provide a custom comparison function used for 
this testing? Apologies if I missed something about this in the docs.

from sqlalchemy import Column, Integer, Float, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import create_session
from numpy.ma.core import exp

sqlite = 'sqlite:///C:\\temp1.db'
engine = create_engine(sqlite, echo=True)
Base = declarative_base(bind=engine)

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

if __name__ == '__main__':
x = exp(1.0)
print   x = %0.60f % x
Base.metadata.drop_all()
Base.metadata.create_all()
session = create_session(bind=engine, autocommit=False)
a = A(id=1, value=x)
session.add(a)
session.commit()
print a.value = %0.60f % a.value
print session.is_modified(a) = %s % session.is_modified(a)  # False
a.value = x
print a.value = %0.60f % a.value
print session.is_modified(a) = %s % session.is_modified(a)  # True
session.commit()  # UPDATE
session.close()

Thanks,

Seth

-- 
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/groups/opt_out.

Re: [sqlalchemy] session.is_modified() and floating-point numbers

[Seth P]

Huh. That is odd numpy behavior.
Thanks for the recipe.

On Tuesday, November 19, 2013 8:35:35 PM UTC-5, Michael Bayer wrote:

 heh, this is actually some dumb numpy thing, check this out:

  from numpy.ma.core import exp
  x = exp(1.0)
  a = x == x
  a
 True
  a is True
 False
  

 there’s your problem, the recipe fixes if you just say this:

 class InexactFloat(TypeDecorator):
 impl = Float

 def compare_values(self, x, y):
 return bool(x == y)




 On Nov 19, 2013, at 8:30 PM, Michael Bayer 
 mik...@zzzcomputing.comjavascript: 
 wrote:


 On Nov 19, 2013, at 8:10 PM, Seth P spad...@gmail.com javascript: 
 wrote:

 I have an issue where, I believe due to floating-point representation 
 issues, reassigning the same value to a floating-point field causes 
 SQLAlchemy to think the value has been modified, and therefore emits a 
 gratuitous UPDATE. (This is particularly problematic when using the 
 versioning mixin, 
 http://docs.sqlalchemy.org/en/rel_0_8/orm/examples.html?highlight=versioned.) 
 This doesn't happen all the time, e.g. in the example below the problem 
 disappears if exp is imported from math rather than from numpy.ma.core. 
 Am I doing something wrong? If not, is there a way to tell SQLAlchemy that 
 a particular Float column (or even all Float columns) should not be 
 considered modified if the new and old values differ by less than some 
 threshold? Is there a way to provide a custom comparison function used for 
 this testing? Apologies if I missed something about this in the docs.


 well floating points are an inexact representation, so you are hitting 
 that issue.  If you used Numeric() instead, the floats would be coerced to 
 Decimal objects on the Python side, these don’t perform as well as floats 
 but are an exact, immutable representation - but you still can have 
 variability in how these come back from SQLite.

 You can in fact change the comparison function used by the unit of work to 
 compare for a change, just use a TypeDecorator:

 class InexactFloat(TypeDecorator):
 impl = Float

def compare_values(self, x, y):
return round(x) == round(y)  # or whatever

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






 from sqlalchemy import Column, Integer, Float, create_engine
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import create_session
 from numpy.ma.core import exp

 sqlite = 'sqlite:///C:\\temp1.db'
 engine = create_engine(sqlite, echo=True)
 Base = declarative_base(bind=engine)

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

 if __name__ == '__main__':
 x = exp(1.0)
 print   x = %0.60f % x
 Base.metadata.drop_all()
 Base.metadata.create_all()
 session = create_session(bind=engine, autocommit=False)
 a = A(id=1, value=x)
 session.add(a)
 session.commit()
 print a.value = %0.60f % a.value
 print session.is_modified(a) = %s % session.is_modified(a)  # False
 a.value = x
 print a.value = %0.60f % a.value
 print session.is_modified(a) = %s % session.is_modified(a)  # True
 session.commit()  # UPDATE
 session.close()

 Thanks,

 Seth

 -- 
 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 javascript:.
 To post to this group, send email to sqlal...@googlegroups.comjavascript:
 .
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.





-- 
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/groups/opt_out.

[sqlalchemy] overriding lazy loading?

[Seth P]

Is it possible to override the default loading strategy of a relationship 
at run-time? For example, I have a relationship that I almost always want 
to load with lazy='subquery' -- and so I set that as the default loading 
strategy in the relationship definition  -- but in one instance, when I 
know I won't be accessing the related objects, I'd much rather load with 
lazy='select' (so as not to load the related objects). Is this possible?

Thanks,

Seth

-- 
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/groups/opt_out.

Re: [sqlalchemy] overriding lazy loading?

[Seth P]

D'oh! I did, though for some reason it didn't occur to me that I could 
specify .override(lazyload('points')) to override the relationship's 
default lazy='subquery'. Works like a charm. Thank you.

-- 
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/groups/opt_out.

[sqlalchemy] joined-table inheritance and ambiguous foreign keys

[Seth P]

The code below produces the error message below. How do I tell SQLAlchemy 
that the inheritance join condition should be b.id == a.id rather than 
b.parent_a_id 
== a.id? (I would think the primary_key=True could be a hint...) I can't 
figure it out from the documentation.

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

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

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'a' 
and 'b'; tables have more than one foreign key constraint relationship 
between them. Please specify the 'onclause' of this join explicitly.


Thanks,

Seth


-- 
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/groups/opt_out.

Re: [sqlalchemy] joined-table inheritance and ambiguous foreign keys

[Seth P]

Thank you.


On Wed, Jul 24, 2013 at 5:51 PM, Michael Bayer mike...@zzzcomputing.comwrote:

 its a mapper arg called inherit_condition:  __mapper_args__ =
 {inherit_condition: id==A.id}

 On Jul 24, 2013, at 3:42 PM, Seth P spadow...@gmail.com wrote:

 The code below produces the error message below. How do I tell SQLAlchemy
 that the inheritance join condition should be b.id == a.id rather than 
 b.parent_a_id
 == a.id? (I would think the primary_key=True could be a hint...) I can't
 figure it out from the documentation.

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

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

 sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'a'
 and 'b'; tables have more than one foreign key constraint relationship
 between them. Please specify the 'onclause' of this join explicitly.


 Thanks,

 Seth



 --
 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/groups/opt_out.




  --
 You received this message because you are subscribed to a topic in the
 Google Groups sqlalchemy group.
 To unsubscribe from this topic, visit
 https://groups.google.com/d/topic/sqlalchemy/AaSChCQOTSk/unsubscribe.
 To unsubscribe from this group and all its topics, 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/groups/opt_out.




-- 
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/groups/opt_out.

[sqlalchemy] Versioned mixin and backref

[Seth P]

The Versioned mixin described in 
http://docs.sqlalchemy.org/en/rel_0_8/orm/examples.html#versioned-objects 
(which I renamed VersionedMixin, but is otherwise the same) has what I 
would consider an unintuitive and undesirable interaction with backref: if 
C references A with a backref, adding a new C object referencing a 
particular A object will cause the version number of the target A object to 
increment, even though there are no changes to the A table. If the relation 
has no backref (as in the relationship from C to B below), then the target 
object version number is not incremented, as I would expect. It seems that 
the code is effectively using session.is_modified(a) to determine whether 
to increment the version number, whereas I would have thought 
session.is_modified(a, 
include_collections=False) would be more appropriate. Is there some use 
case I'm not considering that favors the current behavior?

Thanks,

Seth

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session, relationship, 
backref
from history_meta import VersionedMixin, versioned_session

Base = declarative_base(object)
metadata = Base.metadata

class A(VersionedMixin, Base):
__tablename__ = 'a'
__table_args__ = {}
id = Column(Integer, primary_key=True)
name = Column(String(3))
def __repr__(self):
return A(id=%d,name='%s',version=%d,cs=%s) % (self.id, self.name, 
self.version, [c.name for c in self.cs])

class B(VersionedMixin, Base):
__tablename__ = 'b'
__table_args__ = {}
id = Column(Integer, primary_key=True)
name = Column(String(3))
def __repr__(self):
return B(id=%d,name='%s',version=%d) % (self.id, self.name, 
self.version)

class C(VersionedMixin, Base):
__tablename__ = 'c'
__table_args__ = {}
id = Column(Integer, primary_key=True)
name = Column(String(3))
a_id = Column(Integer, ForeignKey('a.id'))
a_re = relationship(A, backref='cs')
b_id = Column(Integer, ForeignKey('b.id'))
b_re = relationship(B)

if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:', echo=False)
metadata.create_all(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
versioned_session(Session)
session = Session()

# populate tables with a single entry in each table
a = A(name='a')
b = B(name='b')
c1 = C(name='c1', a_re=a, b_re=b)
session.add_all([a, b, c1])
session.commit()
print '\nAfter initial commit'
print 'a=%s; is_modified(a)=%s; is_modified(a, 
include_collections=False)=%s' % (a, session.is_modified(a), 
session.is_modified(a, include_collections=False))
print 'b=%s; is_modified(b)=%s; is_modified(b, 
include_collections=False)=%s' % (b, session.is_modified(b), 
session.is_modified(b, include_collections=False))
# add another entry in b that points to a
c2 = C(name='c2', a_re=a, b_re=b)
session.add(c2)
print \nAfter adding C(name='c2', a_re=a, b_re=b), but before 
committing:
print 'a=%s; is_modified(a)=%s; is_modified(a, 
include_collections=False)=%s' % (a, session.is_modified(a), 
session.is_modified(a, include_collections=False))
print 'b=%s; is_modified(b)=%s; is_modified(b, 
include_collections=False)=%s' % (b, session.is_modified(b), 
session.is_modified(b, include_collections=False))
session.commit()
print '\nAfter final commit:'
print 'a=%s; is_modified(a)=%s; is_modified(a, 
include_collections=False)=%s' % (a, session.is_modified(a), 
session.is_modified(a, include_collections=False))
print 'b=%s; is_modified(b)=%s; is_modified(b, 
include_collections=False)=%s' % (b, session.is_modified(b), 
session.is_modified(b, include_collections=False))

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

[sqlalchemy] Bug in query with multiple joins when using joined inheritance?

[Seth P]

I've encountered what I believe to be a bug in SQLAlchemy (versions 0.8.0 
and 0.8.1) in a query that joins class/tables that use joined inheritance.

In the code below, I would expect the three queries to produce the same 
output, namely [u'CCC'], but the first one gives a different (incorrect) 
result, [u'BBB']. Is this a bug, or is the query malformed?
In the second query, adding a seemingly gratuitous join with D fixes the 
problem. And as the third query shows, replacing C by an aliased version 
also fixes the problem. So whatever is going on seems rather subtle.

Thanks,

Seth



from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship, sessionmaker, scoped_session
from sqlalchemy.orm.util import aliased

Base = declarative_base(object)
metadata = Base.metadata

class A(Base):
__tablename__ = 'A'
idx = Column(Integer, primary_key=True)
name = Column(String(20), nullable=False)
type_idx = Column(Integer, nullable=False)
__mapper_args__ = { 'polymorphic_on':type_idx }

class B(A):
__tablename__ = 'B'
idx = Column(Integer, ForeignKey(str(A.__table__) + .idx), 
primary_key=True)
__mapper_args__ = { 'polymorphic_identity':2 }

class C(A):
__tablename__ = 'C'
idx = Column(Integer, ForeignKey(str(A.__table__) + .idx), 
primary_key=True)
b_idx = Column(Integer, ForeignKey(str(B.__table__) + .idx), 
nullable=False)
b = relationship(B, foreign_keys=[b_idx])
__mapper_args__ = { 'polymorphic_identity':3 }

class D(A):
__tablename__ = 'D'
idx = Column(Integer, ForeignKey(str(A.__table__) + .idx), 
primary_key=True)
__mapper_args__ = { 'polymorphic_identity':4 }

class CtoD(Base):
__tablename__ = 'CtoD'
idx = Column(Integer, primary_key=True)
c_idx = Column(Integer, ForeignKey(str(C.__table__) + .idx), 
nullable=False)
c = relationship(C, foreign_keys=[c_idx])
d_idx = Column(Integer, ForeignKey(str(D.__table__) + .idx), 
nullable=False)
d = relationship(D, foreign_keys=[d_idx])

if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:', echo=False)
metadata.create_all(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
session = Session()

# populate tables with a single entry in each table
b = B(name='BBB')
c = C(name='CCC', b=b)
d = D(name='DDD')
c_to_d = CtoD(c=c, d=d)
session.add_all([b, c, d, c_to_d])
session.commit()

sql_query = session.query(B, C.name).join(C, B.idx == 
C.b_idx).join(CtoD, C.idx == CtoD.c_idx).join(D, CtoD.d_idx == D.idx)
print [name for (_, name) in sql_query.all()]  # [u'BBB']

sql_query = session.query(B, C.name).join(C, B.idx == 
C.b_idx).join(CtoD, C.idx == CtoD.c_idx)
print [name for (_, name) in sql_query.all()]  # [u'CCC']

aliased_C = aliased(C)
sql_query = session.query(B, aliased_C.name).join(aliased_C, B.idx == 
aliased_C.b_idx).join(CtoD, aliased_C.idx == CtoD.c_idx).join(D, CtoD.d_idx 
== D.idx).join(D, CtoD.d_idx == D.idx)
print [name for (_, name) in sql_query.all()]  # [u'CCC']

-- 
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/groups/opt_out.