Re: [sqlalchemy] Session and optimistic disconnect handling

['Matt Zagrabelny' via sqlalchemy]

On Tue, Jun 8, 2021 at 11:58 AM Mike Bayer  wrote:

>
> Unknown network failures, I suppose. I have an application that is
> throwing an exception right now due to:
>
> psycopg2.OperationalError: terminating connection due to administrator
> command
> SSL connection has been closed unexpectedly
>
>
> right so if that happens on a connection that's been sitting in the pool
> when you first go to use it, pre_ping will solve that.
>

Okay. That sounds pretty good.

I've turned on logging (at the DEBUG level) per...

https://docs.sqlalchemy.org/en/14/core/engines.html#dbengine-logging

and I am trying to force an invalid connection by:

while True:
# read stdin
o = SQLObject(something_from_stdin)
session.add(o)
session.commit()
# Go to DB server and restart postgresql

However I don't see any indication that the pre_ping invalidated a
connection...

# Here is the first commit from SA...
INFO:sqlalchemy.engine.base.Engine:select version()
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('version',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('PostgreSQL 12.4 (Debian 12.4-3)
on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.0-13) 10.2.0, 64-bit',)
INFO:sqlalchemy.engine.base.Engine:select current_schema()
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('current_schema',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('public',)
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS
VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS
VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('standard_conforming_strings',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('on',)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO call_records (inserted_at,
acct_code, vdn) VALUES (%(inserted_at)s, %(acct_code)s, %(vdn)s) RETURNING
call_records.id
INFO:sqlalchemy.engine.base.Engine:{'inserted_at': 'now', 'acct_code':
'yuy', 'vdn': 'tyt'}
DEBUG:sqlalchemy.engine.base.Engine:Col ('id',)
DEBUG:sqlalchemy.engine.base.Engine:Row (18,)
INFO:sqlalchemy.engine.base.Engine:COMMIT

# DB restarted and the second pass through the while loop...

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO call_records (inserted_at,
acct_code, vdn) VALUES (%(inserted_at)s, %(acct_code)s, %(vdn)s) RETURNING
call_records.id
INFO:sqlalchemy.engine.base.Engine:{'inserted_at': 'now', 'acct_code':
'909', 'vdn': '909'}
DEBUG:sqlalchemy.engine.base.Engine:Col ('id',)
DEBUG:sqlalchemy.engine.base.Engine:Row (19,)
INFO:sqlalchemy.engine.base.Engine:COMMIT

Should I be seeing something in the logs about an invalidated connection?

Or am I not forcing an invalid connection correctly?

Thanks for the feedback!

-m

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAOLfK3W539EFhaDYAu3A7Gxr3WrwbdCGX12V_Y19gXZz3O74sA%40mail.gmail.com.

Re: [sqlalchemy] Session and optimistic disconnect handling

['Matt Zagrabelny' via sqlalchemy]

On Tue, Jun 8, 2021 at 10:28 AM Mike Bayer  wrote:

>
>
>
>   however, I would advise using pool_pre_ping instead which is much easier
> to use and has basically no downsides.this feature didn't exist when
> the docs for "optimistic disconnect" were written.
>
>
> Sure. I was only looking at doing the optimistic disconnect because it
> seemed a little more resilient to failures (if a DB error happens mid
> transaction) and because I felt I could control the number of retries and
> put in an exponential backoff.
>
> Do you suggest I use the custom pessimistic ping code:
>
>
> https://docs.sqlalchemy.org/en/14/core/pooling.html#custom-legacy-pessimistic-ping
>
> to add in exponential backoff or add additional retries?
>
>
> what's the real-world use case where exponential backoff is useful?
>

Unknown network failures, I suppose. I have an application that is throwing
an exception right now due to:

psycopg2.OperationalError: terminating connection due to administrator
command
SSL connection has been closed unexpectedly

I don't know exactly what is causing the failure. Our VM guy seems to think
it might be a network issue. Either way, I feel the code should retry a few
times just to see if the cause of the error goes away.



> do you expect databases to be shut down for a number of minutes without
> disabling the application ?
>

No. I don't know what the timing parameters are of my particular failure.
Pre ping doesn't seem like it allows for any sort of length of time before
failing.

I don't know if this offers any more clarity to what I'm experiencing and
what I'm trying to code around.

Thanks again for the help and dialogue!

-m

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAOLfK3XEJWahpk9WRCNG%2BHkGGeiM7wR5zgmNeJHG2ECYkk%3Dc0A%40mail.gmail.com.

Re: [sqlalchemy] Session and optimistic disconnect handling

['Matt Zagrabelny' via sqlalchemy]

Hi Mike,

Thanks for the reply!

On Mon, Jun 7, 2021 at 6:08 PM Mike Bayer  wrote:

> ORM Sessions ride on top of connections, but since the Session under it's
> default pattern of being bound to an Engine does the "connect" internally,
> it's probably inconvenient to adapt the optimistic disconnect approach to
> it. You would probably want to bind the Session to the Connection
> explicitly.
>

I searched the SA docs, but could not find how to bind the Session to the
Connection.


>   however, I would advise using pool_pre_ping instead which is much easier
> to use and has basically no downsides.this feature didn't exist when
> the docs for "optimistic disconnect" were written.
>

Sure. I was only looking at doing the optimistic disconnect because it
seemed a little more resilient to failures (if a DB error happens mid
transaction) and because I felt I could control the number of retries and
put in an exponential backoff.

Do you suggest I use the custom pessimistic ping code:

https://docs.sqlalchemy.org/en/14/core/pooling.html#custom-legacy-pessimistic-ping

to add in exponential backoff or add additional retries?

Thank you again for the help!

-m

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAOLfK3WYSinbhQLbMaBTf7sRRDZ8PtVcQ6u2-gV4ftjS36ZstA%40mail.gmail.com.

[sqlalchemy] Session and optimistic disconnect handling

['Matt Zagrabelny' via sqlalchemy]

Greetings SQLAlchemy folks,

I am following the guide at [0] for recovering from a database error in my
SQLAlchemy code.

I normally use sessions for my SA work and am wondering if sessions will
work with the aforementioned SA example. My initial attempt to combine the
example at [0] with sessions did not seem to work as expected. What do
folks think? Should it work?

Here is a code snippet of how I am creating sessions:

connection_string = self.get_connection_string()
engine= create_engine(connection_string)
Session   = sessionmaker(bind = engine)

If folks believe it should work, then I'll formulate a minimal working
example and post my error.

Thank you!

-m

[0]
https://docs.sqlalchemy.org/en/14/core/pooling.html#disconnect-handling-optimistic

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAOLfK3Ud5spwMWPQTJws4zJixu7FuS5bLTu3OCmpxFLUpYn62A%40mail.gmail.com.

Re: [sqlalchemy] correct usage of next_value for a sequence

['Matt Zagrabelny' via sqlalchemy]

On Tue, Apr 20, 2021 at 1:14 PM Mike Bayer  wrote:

>
>
> On Tue, Apr 20, 2021, at 1:52 PM, 'Matt Zagrabelny' via sqlalchemy wrote:
>
> Greetings SQLAlchemy,
>
> I'm attempting to use the next_value function to get the (next) value from
> a sequence:
>
> cycle_counter = next_value(Sequence('cycle_seq'))
> print(cycle_counter)
>
> However, the print statement yields:
>
> 
>
> Does anyone know the correct way to get the value of a sequence?
>
>
>
> you should execute that with a connection:
>
> with engine.connect() as conn:
> conn.scalar(seq.next_value())
>
>
>
Thanks for the tip, Mike!

Best,

-m

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAOLfK3WK0%3DF3gu_pouZhTx-Banwzyn90qT-WVa%3DJUiQW7x7VEA%40mail.gmail.com.

[sqlalchemy] correct usage of next_value for a sequence

['Matt Zagrabelny' via sqlalchemy]

Greetings SQLAlchemy,

I'm attempting to use the next_value function to get the (next) value from
a sequence:

cycle_counter = next_value(Sequence('cycle_seq'))
print(cycle_counter)

However, the print statement yields:



Does anyone know the correct way to get the value of a sequence?

Thanks for any help!

-m

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAOLfK3UWDoh4n%2BQpj%3DBDOK616TpOEfn46ZQ%2BCo88c5VQyVK%3DZA%40mail.gmail.com.

[sqlalchemy] Is there a built-in concept of model/schema versioning?

[Matt S]

Does Alembic has the concept of versioned schemas/models? In Alembic, I 
need to re-define a model which matches the database table for the current 
migration. Becomes verbose after several migrations, redefining the same 
out-of-date model. So I created a "legacy_models" module where I define old 
models that don't match the current version. But I'm wondering if I'm 
reinventing the wheel?

I'm referring to the `table(...)` stuff mentioned here: 
https://www.georgevreilly.com/blog/2016/09/06/AlembicDataMigrations.html

I have to repeat the "table" definition in every migration.

But, for example, in Django a migration can refer to the version of the 
model at that point in time (when the migration was created) rather than 
the current version of a model which might have different columns. These 
model versions are automatically managed by Django.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/515d89c3-f48c-42ba-a2ba-e54753306276%40googlegroups.com.

Re: [sqlalchemy] Re: converting row object to dict

[Matt Zagrabelny]

Thanks for the help Jonathan and Mike.

-m

On Sat, Aug 25, 2018 at 8:16 AM, Mike Bayer 
wrote:

> just as a note, that's also not a "row" object you are getting back, you
> are getting ORM mapped instances.  if you want rows which you can call
> dict() directly on them, use a Core expression, like result =
> session.execute(select([Station])).fetchall().
>
> On Sat, Aug 25, 2018 at 1:13 AM, Jonathan Vanasco 
> wrote:
>
>> here are 2 methods you can add to your base class:
>>
>>> def columns_as_dict(self):
>>> """
>>> Beware: this function will trigger a load of attributes if they have not
>>> been loaded yet.
>>> """
>>> return dict((col.name, getattr(self, col.name))
>>> for col
>>> in sa_class_mapper(self.__class__).mapped_table.c
>>> )
>>> def loaded_columns_as_dict(self):
>>> """
>>> This function will only return the loaded columns as a dict.
>>> """
>>> _dict = self.__dict__
>>> return {col.name: _dict[col.name]
>>> for col in sa_class_mapper(self.__class__).mapped_table.c
>>> if col.name in _dict
>>> }
>>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
SQLAlchemy - 
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] converting row object to dict

[Matt Zagrabelny]

Greetings,

I've searched the group archives and looked at StackOverflow (sorry!) and
couldn't come up with the canonical way to convert a row object to a dict.

Here are some code snippets:

Base = declarative_base()

class Station(
Base,
):
__tablename__ = 'stations'
id= Column(Integer, primary_key = True)
created_at_id = Column(Integer, ForeignKey('synchronizations.id'))
extension = Column(String, unique = True, nullable = False)

stations = db.session.query(Station)
for s in stations:
d = dict(s)

TypeError: 'Station' object is not iterable

If I try the _asdict()

stations = db.session.query(Station)
for s in stations:
d = s._asdict()

AttributeError: 'Station' object has no attribute '_asdict'

Clearly I am missing something.

Any pointers?

Thanks!

-m

-- 
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] standalone asc/desc function vs ColumnElement asc/desc function

[Matt Zagrabelny]

Greetings,

Are there any reasons (canonical, stylistic, etc.) that one would use

order_by(desc(Class.column))

vs

order_by(Class.column.desc())

?

Thanks!

-m

-- 
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] Performance of ORDER BY vs. list.sort() vs. sorted()

[Matt Zagrabelny]

On Wed, Feb 28, 2018 at 3:02 PM,  wrote:

> Hello,
>
> I’m curious about your experience with sorting the results of all()
> 
> queries which return a list. It seems to me that there are three ways of
> sorting such result lists:
>
>1. Use an ORDER BY
>
> 
>in the query and let the database do the sorting.
>
> I would guess that the DB would be the fastest if one were to benchmark
sorting speeds. I have not.


>
>1. Use Python’s list.sort()
> and sort
>the result list in-place.
>2. Use Python’s sorted()
> function and
>construct a new and sorted list from the result list.
>
>
I would guess that both Python sort incantations are of similar performance
values. Here is a Stack Overflow thread about sort() vs sorted()

https://stackoverflow.com/questions/22442378/what-is-the-difference-between-sortedlist-vs-list-sort-python

-m

-- 
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: load_only change in 1.2.0?

[Matt Schmidt]

The version I upgraded from was 1.1.9, and I originally started the project 
on 1.1.1. 

I saw that bug and thought that was it, but then noticed that was a number 
of versions ago.


On Wednesday, January 10, 2018 at 1:54:54 PM UTC-5, Jonathan Vanasco wrote:
>
> what version did you update from?
>
> If this is the issue I am thinking about, that feature was added a long 
> time ago... 
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3080
>
> the issue in the ticket was the load_only added in the primary keys on 
> joinedload but not subqueryload -- and the orm needs the primary key in 
> order to pull the correct items.  so michael's fix was to include it in 
> subqueryloads automatically.
>
>
> it looks to be 0.9.5 in 2014
>
>
> http://docs.sqlalchemy.org/en/latest/changelog/changelog_09.html#change-25bfed359ea9ffac545b3582739da0c4
>
> and it has been in every release since 1.0b1
>
> if you upgraded from something after 0.9.5, then this may be something 
> else.  if you upgraded from before 0.9.5, this is expected.
>

-- 
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] load_only change in 1.2.0?

[Matt Schmidt]

I have a small companion library that I wrote for model-to-dict 
serialization. I just updated to 1.2.0 and one of my tests is failing --

https://gitlab.com/sloat/SerialAlchemy/blob/master/tests/test_to_dict.py#L193

In 1.1.x, it worked as expected, but in 1.2, the primary-key is added to 
the serialized profile field. So the output is now:

{
'firstname': 'test',
'profile': {
'id': 3,
'somefield': 'somevalue'
},
}


I just wanted to make sure this is expected behavior from SQLAlchemy. It's 
not a problem for me to update the test, it won't impact the actual 
functionality of the library.

-Matt

-- 
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] SQLAlchemy dedicated read-server pooling

[Matt]

On Wednesday, December 14, 2016 at 3:58:19 PM UTC+1, Mike Bayer wrote:
>
>
> When you want to switch between "writer" and "reader", the "reader" must 
> be fine with using a totally clean transaction and that means totally 
> new objects and everything inside that Session as well.  For a web 
> application this usually means "reader" has to be selected at the start 
> of the request, and the whole request consults "reader" only.  You can't 
> switch between two different DB transactions mid-request, one of which 
> has been writing new rows, and expect to see the same rows between those 
> two transactions, the transactions will have some degree of isolation 
> from each other. 
>
> Short answer, you can't direct writer/reader at the "query" level, 
> unless you put the Session into autocommit mode and run COMMIT after 
> every statement.  Normally it has to be at the transaction level. 
>
>
It would be acceptable to select the read server at the start of a web 
request. I am still a little lost on how to get to that behavior, though. 
Any insights there? How do I bind the read server at the transaction level?

 

-- 
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 dedicated read-server pooling

[Matt]

 

I am currently writing a Flask web application that uses Flask-SQLAlchemy 
to communicate with a Postgres database. I want to have several 
read-replicas to which all database read queries can be directed, while all 
read+write queries can go to the master database instance. In other words, 
a master-slave setup.

A stackoverflow question from 2012 
 
discusses how this can be done in SQLAlchemy. I have implemented a variant 
of the accepted answer, one that uses the Flask-SQLAlchemy db object (which 
holds the session).

In the global scope, I have:


slave_engine = create_engine(app.config['SQLALCHEMY_DATABASE_SLAVE_URI'])

Then, as a function decorator for any method that wishes to read from a 
slave instance:


def with_slave(f):
@wraps(f)
def decorated_function(*args, **kwargs):
s = db.session
oldbind = s.bind
s.bind = slave_engine
try:
return f(*args, **kwargs)
finally:
s.bind = oldbind
return decorated_function

Here's my problem. My AWS RDS dashboard consistently tells me the there are 
no open connections to the read servers (even during heavy use), while the 
master server (which is the default) has a number of open connections 
(probably because of SQLAlchemy's pooling).

The question: am I correct to assume that the @with_slave decorated does 
not account for connection pooling? If so, I'm sure this affects 
performance, so the follow up would be: what could be changed to enable 
connection pooling to the read-servers?

-- 
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] Using sqlalchemy dburi with Oracle using External Password Store (Oracle Wallet)?

[Matt Vasquez]

I am able to get cx_Oracle to connect just fine using the wallet alias:

import cx_Oracle

db = cx_Oracle.connect("/@PROD")
print db.version

db.close()


I just need to know how to put "/@PROD" in a URI format for sqlalchemy..





On Friday, May 27, 2016 at 9:31:35 AM UTC-5, Mike Bayer wrote:
>
> get it working with regular cx_oracle first.   I'm not familiar with 
> external password store so you should ask on the cx_oracle mailing list 
> (https://sourceforge.net/p/cx-oracle/mailman/cx-oracle-users/) 
>
>
>
> On 05/27/2016 10:18 AM, Matt Vasquez wrote: 
> > 
> > down votefavorite 
> > <
> https://stackoverflow.com/questions/37471892/using-sqlalchemy-dburi-with-oracle-using-external-password-store#>
>  
>
> >  
> > 
> > I am trying to get a oracle sqlalchemy dburi working with an external 
> > password store (Oracle Wallet) 
> > 
> > I have tried using the standard sqlplus syntax for a external password 
> > store with no luck.. 
> > 
> > |sqlalchemy.dburi="oracle:///@PROD"| 
> > 
> > I can connect successfully using sqlplus as follows: 
> > 
> > |sqlplus /@PROD| 
> > 
> > I can't find any information on how to use external password stores with 
> > sqlalchemy or turbogears? 
> > 
> > -- 
> > 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  
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > <mailto:sqlal...@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.

[sqlalchemy] Using sqlalchemy dburi with Oracle using External Password Store (Oracle Wallet)?

[Matt Vasquez]

down votefavorite 


I am trying to get a oracle sqlalchemy dburi working with an external 
password store (Oracle Wallet)

I have tried using the standard sqlplus syntax for a external password 
store with no luck..

sqlalchemy.dburi="oracle:///@PROD"

I can connect successfully using sqlplus as follows:

sqlplus /@PROD

I can't find any information on how to use external password stores with 
sqlalchemy or turbogears?

-- 
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] "cache lookup failed" on reflection

[Matt Smith]

Hi sqlalchemy!

When using sqlalchemy (core) 1.0.8 with redshift-sqlalchemy 0.4.1, I 
encountered the following exception:

InternalError: (psycopg2.InternalError) cache lookup failed for relation 
3262644

This is the query it failed on:

SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE (pg_catalog.pg_table_is_visible(c.oid))
AND c.relname = %(table_name)s AND c.relkind in ('r', 'v', 'm', 'f')

Our hypothesis is that another process operating on the same Redshift 
cluster (which runs a table shuffling process and deletes tables), is 
causing our code to error during schema reflection. Does this sound correct 
to you, and if so, is there a way for our code to mitigate this issue?

Thanks, Matt Smith

Here's the relevant portion of the stack trace:

... snip ...
engine, meta = ENGINES.get('redshift'), ENGINES.get_meta('redshift')
  File "ourcode.py", line 38, in get_meta
meta.reflect(bind=self.get(name))
  File "sqlalchemy/sql/schema.py", line 3647, in reflect
Table(name, self, **reflect_opts)
  File "sqlalchemy/sql/schema.py", line 416, in __new__
metadata._remove_table(name, schema)
  File "sqlalchemy/util/langhelpers.py", line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File "sqlalchemy/sql/schema.py", line 411, in __new__
table._init(name, metadata, *args, **kw)
  File "sqlalchemy/sql/schema.py", line 484, in _init
self._autoload(metadata, autoload_with, include_columns)
  File "sqlalchemy/sql/schema.py", line 496, in _autoload
self, include_columns, exclude_columns
  File "sqlalchemy/engine/base.py", line 1477, in run_callable
return callable_(self, *args, **kwargs)
  File "sqlalchemy/engine/default.py", line 364, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
  File "sqlalchemy/engine/reflection.py", line 563, in reflecttable
table_name, schema, **table.dialect_kwargs):
  File "sqlalchemy/engine/reflection.py", line 369, in get_columns
**kw)
  File "", line 2, in get_columns
  File "sqlalchemy/engine/reflection.py", line 54, in cache
ret = fn(self, con, *args, **kw)
  File "sqlalchemy/dialects/postgresql/base.py", line 2325, in get_columns
info_cache=kw.get('info_cache'))
  File "", line 2, in get_table_oid
  File "sqlalchemy/engine/reflection.py", line 54, in cache
ret = fn(self, con, *args, **kw)
  File "sqlalchemy/dialects/postgresql/base.py", line 2220, in get_table_oid
c = connection.execute(s, table_name=table_name, schema=schema)
  File "sqlalchemy/engine/base.py", line 914, in execute
return meth(self, multiparams, params)
  File "sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
  File "sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
compiled_sql, distilled_params
  File "sqlalchemy/engine/base.py", line 1146, in _execute_context
context)
  File "sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
exc_info
  File "sqlalchemy/util/compat.py", line 199, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
  File "sqlalchemy/engine/base.py", line 1139, in _execute_context
context)
  File "sqlalchemy/engine/default.py", line 450, in do_execute
cursor.execute(statement, parameters)

-- 
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] Composite primary key with nullable columns

[Matt Zagrabelny]

On Tue, Jan 5, 2016 at 11:11 AM, Paul Johnston  wrote:
> Hi,
>
> I have a situation where I'm trying to us a composite primary key, where one
> of the columns can be null.
>
> However, when I try to update an object I get the following error:
> sqlalchemy.orm.exc.FlushError: Can't update table test using NULL for
> primary key value on column test.id2
>
> Sample code here: http://dpaste.com/3Q8T09T
>
> Is this something SQLAlchemy can do? From some quick tests, it seems SQLite
> can do it, not sure about other databases.

NULL columns can't be primary keys in Pg - not sure if it is an ANSI
SQL standard.

-m

-- 
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] passing a dictionary to a Base derived constructor?

[Matt Zagrabelny]

Greetings,

I've googled a bit, checked the sqlalchemy group archives, and looked
at the API for resolution to the following question, but came up short
on a definitive answer.

I've got a table with a number of fields. It is defined as such:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, BigInteger, Interval, DateTime, String

Base = declarative_base()

class CallRecord(Base):
__tablename__  = 'call_records'
id = Column(BigInteger, primary_key = True)
inserted_at= Column(DateTime(timezone = True), default = 'now')
billed_at  = Column(DateTime(timezone = True))
timestamp  = Column(DateTime(timezone = True))
duration   = Column(Interval)
acct_code  = Column(String)
attd_console   = Column(String)
[...]
[the rest of the fields are omitted]

And I have a dictionary with key value pairs of all the required
fields in the call_records table.

Is there a way to pass that dictionary to the CallRecords constructor
to get an instance of the object with the dictionary key/value pairs?

Thanks!

-m

-- 
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] from_statement, TextAsFrom and stored procedures

[Matt Phipps]

On Wed, Feb 5, 2014 at 7:28 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 OK great, added some more rules in 5c188f6c1ce85eaace27f052.


Awesome, thanks! My tests all passed on my end.

As far as “names line up with the result set names”, I’m not sure what you
 mean there, the .columns() method is always matching up names.  With that
 checkin, all the tests in your sample suite pass, so feel free to give it a
 check, I’d like to get this totally right for when 0.9.3 comes out.


Gotcha: I thought that even querying a plain text() object would give you
the right ORM objects back as long as the columns were in the right
positional order. Looks like that's not the case, which is probably for the
best; I think the more liberal behavior would have a large risk of causing
silent bugs.

As for *why* I thought that: I didn't realize until just now that ORM is
designed to handle labels when they're in the specific
form tablename_columnname. That's why I thought a text query with
result set names in that form was being mapped by position, because I
didn't know ORM was smart enough to find columns by name in that form :)

I wrote one more test that failed (but I'm pretty sure it doesn't matter):
I was under the impression that passing Label objects to .columns() would
allow you to map *arbitrary* result set column names to ORM attributes, and
that seems to not be the case (and was never the case, AFAIK). That kind of
mapping would be cool, and might not even be that hard since the columns in
the RowProxy ._keymap values seem to have the original ORM columns in their
.proxy_sets.

That said, the only reason I can think of for someone to try that is if
they did something truly nuts like a join with two columns with the same
name from two tables which *also* have the same name, from two different
schemas, with a stored procedure, into ORM. As long as the
tablename_columname form works, I think our use case is covered, so
feel free to say wontfix. But if you're interested, I added the new test to
my suite: https://gist.github.com/garaden/8835587

I hope I'm not harassing you too much about the TextAsFrom feature! I feel
like if I asked any other ORM to be this flexible they would either laugh
or cry. SQLAlchemy is the first ORM I've worked with since using Rails as
an intern, and I'm spoiled now with how awesome it is :)

-Matt

-- 
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] from_statement, TextAsFrom and stored procedures

[Matt Phipps]

Sounds great; I agree avoiding the naming convention is ideal. For my
project the only reason we're using a text clause is to call a stored
procedure, which definitely can't go in a subquery, so I'm not sure how
well I can weigh in on the aliasing stuff.

-Matt


On Fri, Feb 7, 2014 at 1:43 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Feb 7, 2014, at 1:00 PM, Matt Phipps matt.the.m...@gmail.com wrote:


 I wrote one more test that failed (but I'm pretty sure it doesn't matter):
 I was under the impression that passing Label objects to .columns() would
 allow you to map *arbitrary* result set column names to ORM attributes,
 and that seems to not be the case (and was never the case, AFAIK). That
 kind of mapping would be cool, and might not even be that hard since the
 columns in the RowProxy ._keymap values seem to have the original ORM
 columns in their .proxy_sets.


 yeah I thought this would work but it requires a proxy_set change, which
 I’d like to make but has me nervous:

 class A(Base):
 __tablename__ = 'a'

 id = Column(Integer, primary_key=True)
 data = Column(String)

 result = sess.query(A).from_statement(
 text(SELECT id AS x, data AS y FROM a).
 columns(A.id.label(x), A.data.label(y))
 ).all()

 I’ve added two different patches to
 http://www.sqlalchemy.org/trac/ticket/2932#comment:5 which is reopened.
  both patches work but i think the second one is more of the right idea.

 it works like this too but this renders the subquery, something else to
 think about maybe:

 A1 = aliased(text(SELECT id AS x, data AS y FROM
 a).columns(A.id.label(x), A.data.label(y)))

 result = sess.query(A1).all()

 as does this:

 stmt = text(SELECT id AS x, data AS y FROM a).columns(A.id.label(x),
 A.data.label(y))

 result = sess.query(A).select_entity_from(stmt).all()


 That said, the only reason I can think of for someone to try that is if
 they did something truly nuts like a join with two columns with the same
 name from two tables which *also* have the same name, from two different
 schemas, with a stored procedure, into ORM.


 well I really hate enforced naming conventions so making this work would
 be a breakthrough way of finally getting over that, I like it.  I think
 this can be done.

 also, the change greatly increases performance as the lookup in
 ResultProxy doesn’t need a KeyError now.   So I really want to try to make
 it work.  I’m just trying to think of, what are the implications if the
 text() is then transformed into an alias() and such, but I think it might
 be consistent with how a Table acts right now.   I think its cool:

 stmt = select([A.id, A.data])
 result = sess.query(A).from_statement(stmt).all()   # works

 stmt = select([A.id, A.data]).alias().select()
 result = sess.query(A).from_statement(stmt).all() # you get the same
 column error


 I hope I'm not harassing you too much about the TextAsFrom feature! I feel
 like if I asked any other ORM to be this flexible they would either laugh
 or cry. SQLAlchemy is the first ORM I've worked with since using Rails as
 an intern, and I'm spoiled now with how awesome it is :)


 its great, this feature is going to be much better and important than how
 it started a few months ago.  I’ve added a lot of new thoughts to that
 ticket.




-- 
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] from_statement, TextAsFrom and stored procedures

[Matt Phipps]

I've been investigating this a little further and think I found some other
issues. Our data team changed the stored procedure to stop aliasing the
column names, so passing the mapped columns right into .columns() is
working (in other words, the rest of this post doesn't reflect my use case
anymore :)).

However, labels no longer work as arguments to .columns() unless I go back
to 0.9.1 logic by setting ._textual=False and .use_labels = True. Also,
passing keyword arguments to .columns() only works if the names line up
with the result set names, i.e. using the position as a key seems to be
disabled for TextAsFrom objects.

Here's a gist of the nose test suite that helped me figure out what was
working and what wasn't: https://gist.github.com/garaden/8835587


On Sun, Feb 2, 2014 at 5:42 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Feb 2, 2014, at 4:31 PM, Matt Phipps matt.the.m...@gmail.com wrote:

 def _trackable_truckload_details():
 text = db.text(EXEC ODSQuery.SelectBridgeLoadBoard)
 cols = [col for col in LoadBoard.__table__.c]
 cols = map((lambda x: label('ODSQuery_tblLoadBoard_' + x.name, x)),
 cols)
 mobile_cols =
 LoadMobileTracking.load_mobile_tracking_id.property.columns
 mobile_cols = map((lambda x: label('LoadMobileTracking_' + x.name,
 x)), cols)
 cols.extend(mobile_cols)
 taf = text.columns(*cols)
 return db.session.query(
 LoadBoard.load,
 LoadBoard.orgn_stop,
 LoadBoard.dest_stop,

 LoadMobileTracking.load_mobile_tracking_id).from_statement(taf).all()


 Actually, I'm pretty surprised it worked at all before, without the
 labeling. How did it figure out which result set columns went to which ORM
 object?


 This is because what’s actually going on is more sophisticated than just
 matching up the names.  When the ORM looks for columns in a row, it uses
 the actual Column object to target the column.   If your class is mapped to
 a table “users”, for example, it would look like this:

 users = Table(‘users’, metadata, Column(‘id’, Integer), Column(‘name’,
 String))

 # … later

 for row in conn.execute(some_orm_statement):
user_id = row[users.c.id]
user_name = row[users.c.name]

 that is, we aren’t using strings at all.  When the Core select() object
 (or TextAsFrom in this case) is compiled for the backend, all the Column
 objects it SELECTs from are put into an internal collection called the
 “result_map”, which keys the result columns in several ways, including
 their positional index (0, 1, 2, ..) as well as the string name the
 statement knows they’ll have in the result set (e.g. the label name in this
 case) to all the objects that might be used to look them up.

 So using a label(), that adds another layer onto this.  The label() you
 create from an existing Column still refers to that Column, and we say the
 Label object “proxies” the Column.  if you look in mylabel.proxy_set()
 you’ll see that Column.

 So when we generate the result_map, we put as keys *all* of the things
 that each label() is a “proxy” for, including the Column objects that are
 in our mapping.  its this large and awkward dictionary structure I’ve had
 to stare at for many years as I often have to fix new issues that have
 arisen (such as this one).

 The result is generated, we link the columns in the cursor.description by
 string name to the string names we know are rendered in the final compiled
 construct, the result set now knows that all the Column/Label objects
 corresponding to “id” are linked to that column and that’s how the lookup
 proceeds.



 I’m committing 2932 in a moment and I’m super really hoping I can put out
 0.9.2 today but it’s easy for me to run out of time, but 0.9.2 is
 definitely due.


 That would be awesome! Incidentally though, would this labeling still work
 once the fix is in?


 all the existing mechanisms are maintained and I’ve just made some of the
 matching logic a bit more liberal here, so should be fine.  It’s all
 committed if you want to try out the git master.


-- 
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] AttributeError: 'CompositeProperty' object has no attribute 'props'

[Matt Phipps]

 this process:
 http://docs.sqlalchemy.org/en/latest/changelog/migration_07.html?highlight=configure_mappers#compile-mappers-renamed-configure-mappers-simplified-configuration-internals.

 Thanks,
 Matt

 --
 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 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] Help requested debugging a tricky query with join-dependent hybrid property

[Matt Schmidt]

So here's a simplified version of the models I 
have: http://pastie.org/private/smqzkvz4zj46skfmipruw
Python==3.3
SQLAlchemy==0.9.1
psycopg2==2.5.2
Postgresql 9.2

Running the following query,
session.query(Training).filter(Training.start_date  '2013-11-01')

Gives me the following SQL:
SELECT trainings.id AS trainings_id, trainings.title AS trainings_title, 
view_training_start_dates_1.start AS view_training_start_dates_1_start
FROM view_training_start_dates, trainings LEFT OUTER JOIN 
view_training_start_dates AS view_training_start_dates_1 ON trainings.id = 
view_training_start_dates_1.training_id
WHERE view_training_start_dates.start  '2013-11-01'

There are two problems. In the FROM clause, view_training_start_dates 
should not be there, and in the WHERE clause, 
`view_training_start_dates.start` should be 
`view_training_start_dates_1.start`.

Is what I'm trying possible with hybrid properties? Or should I be looking 
to alter the query instead?

Note: I tried doing a simple select expression for start_date, but it 
wasn't performing as well as I'd like.

Any help is much appreciated! Thanks!

-Matt

-- 
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] Help requested debugging a tricky query with join-dependent hybrid property

[Matt Schmidt]

Very good! Thank you!

On Wednesday, January 15, 2014 1:17:56 PM UTC-5, Michael Bayer wrote:


 On Jan 15, 2014, at 12:58 PM, Matt Schmidt slo...@gmail.com javascript: 
 wrote:

 So here's a simplified version of the models I have: 
 http://pastie.org/private/smqzkvz4zj46skfmipruw
 Python==3.3
 SQLAlchemy==0.9.1
 psycopg2==2.5.2
 Postgresql 9.2

 Running the following query,
 session.query(Training).filter(Training.start_date  '2013-11-01')

 Gives me the following SQL:
 SELECT trainings.id AS trainings_id, trainings.title AS trainings_title, 
 view_training_start_dates_1.start AS view_training_start_dates_1_start
 FROM view_training_start_dates, trainings LEFT OUTER JOIN 
 view_training_start_dates AS view_training_start_dates_1 ON trainings.id= 
 view_training_start_dates_1.training_id
 WHERE view_training_start_dates.start  '2013-11-01'

 There are two problems. In the FROM clause, view_training_start_dates 
 should not be there, and in the WHERE clause, 
 `view_training_start_dates.start` should be 
 `view_training_start_dates_1.start`.

 Is what I'm trying possible with hybrid properties? Or should I be looking 
 to alter the query instead?

 Note: I tried doing a simple select expression for start_date, but it 
 wasn't performing as well as I'd like.



 the issue here is that when you say 
 “query(Training).filter(Training.start_date  date), that is shorthand for 
 query(Training).filter(ViewTrainingStartDates.start  date)”.  That is, 
 it’s as though you wrote “SELECT * FROM table1, table2” without using JOIN, 
 hence your statement has “FROM view_training_start_dates, trainings” in it. 
  The “LEFT OUTER JOIN” is an entirely separate thing that is a result of 
 the lazy=“joined” (e.g. the eager load) on Training.training_start_date and 
 does not affect rows matched.  The “view_training_start_dates_1” table is 
 part of the eager load and is not accessible to the Query in any other way 
 (see below).

 So when you say query(A).filter(B.x  y)”, you need to JOIN, that is, 
 query(A).join(A.bs).filter(B.x  y)”.

 Background on this technique specific to the usage of hybrid attributes is 
 at:

  
 http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/hybrid.html#join-dependent-relationship-hybrid
 .

 As far as what the LEFT OUTER JOIN is all about and why you can’t touch 
 any of those columns directly, see:


 http://docs.sqlalchemy.org/en/rel_0_9/faq.html#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join
  

 which will then lead you to 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html as well as 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#the-zen-of-eager-loading
 .




-- 
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] Enforcing order of operations when using SQL Expression Language

[Matt Murphy]

I am defining a Comparator as follows:

class VersionComparator(CompositeProperty.Comparator):
def __eq__(self, other):
return and_(*[a == b for a, b in 
zip(self.__clause_element__().clauses, other.__composite_values__())])

def __lt__(self, other):
lhs = self.__clause_element__().clauses
lhs_major = lhs[0]
lhs_minor = lhs[1]
lhs_tiny = lhs[2]
lhs_phase = lhs[3]
lhs_build = lhs[4]

rhs = other.__composite_values__()
rhs_major = rhs[0]
rhs_minor = rhs[1]
rhs_tiny = rhs[2]
rhs_phase = rhs[3]
rhs_build = rhs[4]

return and_(
or_(
(lhs_major  rhs_major),
and_(lhs_major == rhs_major, lhs_minor  rhs_minor),
and_(lhs_major == rhs_major, lhs_minor == rhs_minor, 
lhs_tiny  rhs_tiny),
and_(lhs_major == rhs_major, lhs_minor == rhs_minor, 
lhs_tiny == rhs_tiny, lhs_phase  rhs_phase),
and_(lhs_major == rhs_major, lhs_minor == rhs_minor, 
lhs_tiny == rhs_tiny, lhs_phase == rhs_phase,
 lhs_build  rhs_build)
)
)

def __gt__(self, other):
return not_(self.__lt__(other))


When I look at the SQL that is created it looks like this:

FROM components
WHERE NOT (components.major  %(major_1)s OR components.major = %(major_2)s 
AND components.minor  %(minor_1)s OR components.major = %(major_3)s AND 
components.minor = %(minor_2)s AND components.tiny  %(tiny_1)s OR 
components.major = %(major_4)s AND components.minor = %(minor_3)s AND 
components.tiny = %(tiny_2)s AND components.phase  %(phase_1)s OR 
components.major = %(major_5)s AND components.minor = %(minor_4)s AND 
components.tiny = %(tiny_3)s AND components.phase = %(phase_2)s AND 
components.build  %(build_1)s)

My first assumption was that each argument in an and_ or an or_ would be 
wrapped in parenthesis, but they are not.  Is there any way to wrap my 
statements in parenthesis in the SQL?  This query does not work because all 
the statements are executed together.

Also, I am not too familiar with how to properly define comparators for a 
composite.  Am I doing this in an acceptable way? Or is there a better way 
to get the two sets of columns and compare them? I copied the base code 
from SQLAlchemy Docs.

Thanks,
Matt

-- 
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] quoting in a sub clause

[matt g]

Hi,
We are using sqlalchemy with Amazon Redshift. One of the things that I 
wanted to do was to create a custom construct for Redshift's unload 
statement. I modified the sample InsertFromSelect 
(http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html?highlight=insertfromselect).
 
However, we are having problems because the 
UNLOAD(http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html) 
statement requires that the internal select be wrapped in single quotes. 
What would be the best way to ensure that the bind params for the enclosing 
select clause be escaped properly?

thanks,
Matt

-- 
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] quoting in a sub clause

[matt g]

The literal_binds looks great. Do you know of a function that I can use 
that will take the string from compiler.process(select_statement, 
literal_binds=True) and escapes the single quotes in that string? 

For instance, if I have the following (assuming that the table object has 
already been defined):
select_statment = select([func.count(), table.c.year], 
from_obj=table).where(table.c.year.in_([2011,2012])).group_by(table.c.year)
sql_string = compiler.process(select_statement, literal_binds=True)

sql_string looks like this:
uSELECT count(*) AS count_2, inpatient.year \nFROM inpatient \nWHERE 
inpatient.year IN ('2011', '2012') GROUP BY inpatient.year

according to the redshift docs, i need to take that string and now escape 
single quotes it before adding it into the unload statement my 
UnloadFromSelect clause is creating: 

If your query contains quotes (enclosing literal values, for example), you 
need to escape them in the query text. For example:

('select * from venue where venuestate=\'NV\'')





On Tuesday, July 23, 2013 10:22:09 AM UTC-5, Michael Bayer wrote:


 On Jul 23, 2013, at 11:09 AM, matt g mge...@gmail.com javascript: 
 wrote: 

  Hi, 
  We are using sqlalchemy with Amazon Redshift. One of the things that I 
 wanted to do was to create a custom construct for Redshift's unload 
 statement. I modified the sample InsertFromSelect (
 http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html?highlight=insertfromselect).
  
 However, we are having problems because the UNLOAD(
 http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html) statement 
 requires that the internal select be wrapped in single quotes. What would 
 be the best way to ensure that the bind params for the enclosing select 
 clause be escaped properly? 

 why do bound parameters need to be escaped?  you mean they have to be 
 rendered out inline ?If that's the case, set the literal_binds keyword 
 arg to True when you pass along to compiler.process().

-- 
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] Retreiving datetime(6) value using sqlalchemy

[Matt]

Hi all,

I have been writing to my database using func.now(6) for datetime(6) valued 
columns and it has worked perfectly.  However, I've recently run into an 
issue with querying for these values.  It would appear that every time I 
query for the values in datetime(6) columns it returns None.

Examples of datetime(6) values: 


   - '2013-07-10 17:22:49.113604'
   - '2013-07-10 17:55:08.920235'
   

Attempts at retrieving datetime(6) values from the database:

*python:*
print self.engine.execute(select now()).scalar()
print self.engine.execute(select now(6)).scalar()

*output:*
2013-07-11 16:33:04
None

and same thing happens when retrieving an entire row from a table. I didn't 
think it was necessary to go into that sort of detail as I'm hoping this is 
an easy fix, but if need be I can show parts of my schema and datamodel 
along with queries and results for those records.

Any insight would be great.
Thanks,
Matt

-- 
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: Retreiving datetime(6) value using sqlalchemy

[Matt]

Sorry, forgot to mention this is for mysql.

http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

On Thursday, July 11, 2013 4:38:03 PM UTC-4, Matt wrote:

 Hi all,

 I have been writing to my database using func.now(6) for datetime(6) 
 valued columns and it has worked perfectly.  However, I've recently run 
 into an issue with querying for these values.  It would appear that every 
 time I query for the values in datetime(6) columns it returns None.

 Examples of datetime(6) values: 


- '2013-07-10 17:22:49.113604'
- '2013-07-10 17:55:08.920235'


 Attempts at retrieving datetime(6) values from the database:

 *python:*
 print self.engine.execute(select now()).scalar()
 print self.engine.execute(select now(6)).scalar()

 *output:*
 2013-07-11 16:33:04
 None

 and same thing happens when retrieving an entire row from a table. I 
 didn't think it was necessary to go into that sort of detail as I'm hoping 
 this is an easy fix, but if need be I can show parts of my schema and 
 datamodel along with queries and results for those records.

 Any insight would be great.
 Thanks,
 Matt


-- 
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] oracle cursor outparameters

[matt g]

For the parts I'm working on right now, this is perfect. Thank you.

Matt

On Friday, September 21, 2012 5:19:17 PM UTC-5, Michael Bayer wrote:


 On Sep 21, 2012, at 3:49 PM, matt g wrote:

 Hi, 
 I'm working with a client that does most of their work in stored 
 procedures on an oracle database. One of the things I'm trying to help them 
 with is transitioning to use sqlalchemy for calling said procedures.
 For simple input and output types i was able to follow threads in this 
 group and do something like this:

 db.execute(text('begin HELLO_WORLD.say_hi(:x_in, :x_out); 
 end;',bindparams=[bindparam('x_in',String),outparam('x_out',String)]),x_in='matt').

 However, for most of their procedures, they actually have cursors in the 
 outparams. The only way i have found to do it is to drop down into using a 
 raw_connection and the cx_oracle types like so:

 engine = create_engine(dsn)
 con = engine.raw_connection()
 cur = conn.cursor()
 people=cur.var(cx_Oracle.CURSOR)
 groups=cur.var(cx_Oracle.CURSOR)
 params = [12345, people, groups]
 #12345 is a input param
 r = cur.callproc('list_people', params)

 Is there a way to keep this more abstract and within sqlalchemy instead of 
 dropping down into cx_Oracle?


 surethe out parameter idea is pretty much an oracle-only thing these 
 days, so while we should be able to make this happen through the API, it 
 obviously isn't backend-agnostic.

 the usual out parameter routine is like you have above, where we get the 
 results back using the out_parameters member of the result:

 result = conn.execute(text('...', bindparams=[outparam('x', SomeType)]))

 result.out_parameters['x']

 this internally does what you're doing with cursor.var().  When you use 
 String, it knows to use cx_Oracle.STRING because the cx_oracle dialect 
 associates that DBAPI type with the String type.At result time it 
 applies the String type to the result of Variable.getvalue().

 So, *assuming* the way cx_Oracle.CURSOR behaves here is that you get some 
 kind of special object via getvalue(), no changes to SQLAlchemy would be 
 needed.   If you need access to other methods of Variable (see 
 http://cx-oracle.sourceforge.net/html/variable.html) then we might have 
 to come up with something more involved, like intercepting the parameters 
 using after_cursor_execute() or something.

 To do this we just create a type:

 from sqlalchemy.types import TypeEngine

 class OracleCursorType(TypeEngine):
 def get_dbapi_type(self, dbapi):
 return dbapi.CURSOR

 If you use OracleCursorType in your outparam() function, you should get 
 the cursor value back in result.out_parameters.

 If you want to give me a SQL expression that will actually return a 
 cursor type, I can test this locally to iron out anything I'm missing, in 
 case this doesn't work as is.









 thanks,
 Matt 

 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To view this discussion on the web visit 
 https://groups.google.com/d/msg/sqlalchemy/-/u38qIICSt5AJ.
 To post to this group, send email to sqlal...@googlegroups.comjavascript:
 .
 To unsubscribe from this group, send email to 
 sqlalchemy+...@googlegroups.com javascript:.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.




-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/EDfRZCZy0UIJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] oracle cursor outparameters

[matt g]

Hi, 
I'm working with a client that does most of their work in stored procedures 
on an oracle database. One of the things I'm trying to help them with is 
transitioning to use sqlalchemy for calling said procedures.
For simple input and output types i was able to follow threads in this 
group and do something like this:

db.execute(text('begin HELLO_WORLD.say_hi(:x_in, :x_out); 
end;',bindparams=[bindparam('x_in',String),outparam('x_out',String)]),x_in='matt').

However, for most of their procedures, they actually have cursors in the 
outparams. The only way i have found to do it is to drop down into using a 
raw_connection and the cx_oracle types like so:

engine = create_engine(dsn)
con = engine.raw_connection()
cur = conn.cursor()
people=cur.var(cx_Oracle.CURSOR)
groups=cur.var(cx_Oracle.CURSOR)
params = [12345, people, groups]
#12345 is a input param
r = cur.callproc('list_people', params)

Is there a way to keep this more abstract and within sqlalchemy instead of 
dropping down into cx_Oracle?

thanks,
Matt 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/u38qIICSt5AJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] MSSQL negative SMALLINT returned by SA as weird number

[Matt Bodman]

I have a SMALLINT column in MSSQL.  The value of the column is -2

SQLAlchemy also has the column as SMALLINT but the value is translated 
as 4294967294

I can't seem to correct this and I haven't found anything on SA and negative 
numbers.  Any help would be really great, thanks.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/F49ec3O3IGkJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] MSSQL negative SMALLINT returned by SA as weird number

[Matt Bodman]

makes sense.. I'm using pydobc. I do the test you suggested.. thanks!

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/4XlySuLtQxUJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: Softcoding .filter(...)

[Matt Bodman]

I think he means that if you only need an exact match on your query (instead 
of a 'like' or a '' etc) you can do this:

dict_from_web = {'Title':'The Book','Author':'Bob Smith'}

for b in session.query(Book).filter_by(**dict_from_web)

will return the books that have the exact Title 'The Book' and the exact 
author 'Bob Smith'

MB

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/8Yz-FVAb4TMJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[Matt Bodman]

Thanks so much Michael.. just to wrap up this thread, I got it working like 
this:

class Thing(Base):
__tablename__ = 'tbThings'
__table_args__ = (
{'autoload':True,'autoload_with':engine,'extend_existing':True}
)
LastUpdated = Column('LastUpdated', TIMESTAMP, FetchedValue())

Thanks again,

Matt

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/l8KrkR59HGQJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Autoloading ManyToMany association table

[Matt Bodman]

I am autoloading an MSSQL db.  There are a few ManyToMany assoc tables.  I'm 
not sure how to map everything.  Here's a typical example of how they look 
in the db:

Table:  tbUsersToGroups
PK: ID_UserToGroup
FK: User_ID
FK: Group_ID

So I can successfully autoload that assoc table and the Users and Groups 
tables like this per below, but everything I've tried to link them all has 
failed.

*class UserToGroup(Base):*
*__tablename__ = 'tbUsersToGroups'*
*__table_args__ = 
{'autoload':True,'extend_existing':True,'schema':'dbo'}*

and

*class User(Base):*
*__tablename__ = 'tbUsers'*
*__table_args__ = {'autoload':True,'schema':'dbo'}*

and

*class Group(Base):*
*__tablename__ = 'tbGoups'*
*__table_args__ = {'autoload':True,'schema':'dbo'}*
*
*
Any help would be great.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/nk_MNX6yBI8J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[Matt Bodman]

Hi,

I am autoloading tables from an MSSQL db.  A lot of the tables have
the MSSQL TIMESTAMP column.  So, when inserting to the table, I get an
IntegrityError:

sqlalchemy.exc.IntegrityError: (IntegrityError) ('23000', '[23000]
[FreeTDS][SQL Server]Cannot insert an explicit value into a timestamp
column. Use INSERT with a column list to exclude the timestamp column,
or insert a DEFAULT into the timestamp column. (273) (SQLPrepare)'

Is there a way around this without having to map every column
explicitly?

Thanks,

Matt

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] skipping MSSQL TIMESTAMP column on INSERT

[Matt Bodman]

Hi Michael,

Thanks for your reply.  Please be patient with me though as I don't quite 
get it.

Where and when is the add_default function called?  Won't I get the same 
error trying to insert 'some default' into the column? 

Any further explanation would be great.

Matt

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/05kepNetnrMJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Looking for examples of SQLAlchemy in WSGI apps

[Matt Wilson]

I'm writing a really tiny WSGI app just for fun.  Right now, it looks
like this:

from flup.server.scgi import WSGIServer
WSGIServer(s).run()

The s object is the an app that dispatches to other apps based on the
URL.

Are there any instructions written for using SQLAlchemy in this
context?  I'm looking for advice on a few things:

1.  How to wrap every page view in a transaction that is committed by
default or rolled back when the app raises an exception.

2.  How to set up the connections once and then reuse them on each
page load.

The first thing I looked for was some SQL Alchemy middleware.  I found
SQLAlchemyManager, but it explicitly states it is NOT the recommended
way.  Is it still safe to use?

More generally, all advice on using SQLAlchemy + homemade WSGI would
be helpful.

Thanks!

Matt

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Can I write this query with SQLAlchemy?

[Matt Wilson]

Right now, I'm writing this query as a string.  I want to know if it
can be expressed with SQLAlchemy's expressions instead.

Here's the query:

select sh.employee_id, sum(st.stop_time - st.start_time) as hours
from shift sh, shift_time st
where sh.employee_id in (28630, 28648)
and sh.shift_time_id = st.id
and st.start_time between timestamp '2008-11-02 00:00:00' and
timestamp '2008-11-09 00:00:00'
group by sh.employee_id
order by hours desc;

Any ideas?

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Can I write this query with SQLAlchemy?

[Matt Wilson]

Thanks!  This is really helpful.

On Dec 4, 11:43 am, Michael Bayer [EMAIL PROTECTED] wrote:
 hi matt -

 here's that query generated using lexical tables, which are just like  
 Table objects but require less boilerplace for SQL prototyping purposes:

 from sqlalchemy import *
 from sqlalchemy.sql import table, column
 import datetime

 shift = table('shift', column('employee_id', Integer),  
 column('shift_time_id', Integer))
 shift_time = table('shift_time', column('id', Integer),  
 column('start_time', DateTime), column('stop_time', DateTime))

 s = select([shift.c.employee_id, func.sum(shift_time.c.stop_time -  
 shift_time.c.start_time).label('hours')]).\
      where(shift.c.employee_id.in_([28630, 28648])).\
      where(shift.c.shift_time_id==shift_time.c.id).\
      where(shift_time.c.start_time.between(
          datetime.datetime(2008, 11, 2, 0, 0, 0),
          datetime.datetime(2008, 11, 9, 0, 0, 0)
      )).\
      group_by(shift.c.employee_id).\
      order_by(desc(hours))

 print s
 print s.compile().params

 On Dec 4, 2008, at 11:23 AM, Matt Wilson wrote:



  Right now, I'm writing this query as a string.  I want to know if it
  can be expressed with SQLAlchemy's expressions instead.

  Here's the query:

  select sh.employee_id, sum(st.stop_time - st.start_time) as hours
  from shift sh, shift_time st
  where sh.employee_id in (28630, 28648)
  and sh.shift_time_id = st.id
  and st.start_time between timestamp '2008-11-02 00:00:00' and
  timestamp '2008-11-09 00:00:00'
  group by sh.employee_id
  order by hours desc;

  Any ideas?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] circular model definitions

[Matt Haggard]

I'm stumped...

Setup: (Problem statement near the bottom)

In my Pylons app, I have three separate models: Customer, TPPAnswer,
SAQ
TPPAnswer is many-to-one Customer
SAQ is many-to-one Customer

Both have backreferences (so saq.customer and customer.saqs)

Currently, I have them defined in customer.py, tpp.py, saq.py.
Additionally, I have common.py which is where the metadata object
comes from -- all three import everything from common.py

In order to have the TPPAnswer many-to-one Customer reference, I
import customer_table and Customer so that I can do (w/i tpp.py):
from customer import customer_table, Customer
...
mapper(Answer, answers_t, properties={
'customer'  :relation(Customer, backref='tpp_answers'),
})

I have a similar setup for saq.py.


-
Problem:
in customer.py in the Customer class, I need to reference .tpp_answers
and .saqs... but because those references are created in tpp.py and
saq.py, they are not known to the Customer class.

If I have already imported the tpp model (in my controller), then the
Customer object is aware of self.tpp_answers but not
self.questionnaires:
  self.questionnaires
AttributeError: 'Customer' object has no attribute 'questionnaires'

If I have already imported the saq model, then the Customer object is
aware of self.questionnaires but not self.tpp_answers.

I can't import both saq and tpp in the controller:
  self._pre_existing_column = table._columns.get(self.key)
AttributeError: 'Column' object has no attribute '_columns'

How do I set up my model such that I can import the pieces I need when
I need them?  So if I call a certain method of the customer object
(adjust_grade() for example) it will be able to acquire all the
attributes it needs.  I don't want to have a massive file with all the
relations in them, because most of the time I only need a part of
them.  For instance, a lot of the things I do with the SAQ model never
even interact with the customer -- same with the TPP model.  It's the
occasional interaction amongst all three of them that's giving me
grief.

Sorry for such a wordy, confusing explanation.  I'm glad to clarify if
it helps.
-

class Customer(object):

def adjust_grade(self, *whatchanged):
self.tpp_answers
self.questionnaires
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] subtle AttributeError in 0.4.5

[matt harrison]

Folks-

I'm (seemingly) randomly running across this issue in my pylons/SA
app.

I'm generating a SQL where clause like so:

where = sa.and_(cal_table.c.adweekid == table.c.adweekid, )

and every so often I'll get an attribute error in sqlalchemy.util:494
in __getattr__

type 'exceptions.AttributeError': adweekid

I'll refresh the page and it will work.  I'll open the pylons debug
page and type:
cal_table.c.adweekid

and

table.c.adweekid

and neither will throw an AttributeError, but will just work.

Any ideas?!?

thanks,

matt
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: subtle AttributeError in 0.4.5

[matt harrison]

On Jun 30, 2:46 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jun 30, 2008, at 4:41 PM, matt harrison wrote:

  Folks-

  I'm (seemingly) randomly running across this issue in my pylons/SA
  app.

  I'm generating a SQL where clause like so:

  where = sa.and_(cal_table.c.adweekid == table.c.adweekid, )

  and every so often I'll get an attribute error in sqlalchemy.util:494
  in __getattr__

  type 'exceptions.AttributeError': adweekid

  I'll refresh the page and it will work.  I'll open the pylons debug
  page and type:
  cal_table.c.adweekid

  and

  table.c.adweekid

  and neither will throw an AttributeError, but will just work.

 its possible theres more than one Table def at play - depending on the  
 order of module initialization, you get one Table, or you get the other.

Hmmm, any table I reference there should have the adweekid column.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] ProgrammingError: (ProgrammingError) current transaction is aborted, commands ignored until end of transaction block

[Matt Haggard]

I'm getting a ProgrammingError (I've pasted the last part of the
traceback at the bottom of the page).

The error comes from my first heavy-AJAX page in Pylons (postgres
backend).  If I cause too many AJAX requests at a time, or even after
doing 3 non-overlapping AJAX requests, I get the error.  I wonder if
there's some sort of handle releasing I need to do... or transaction
releasing?

As a side note... I test using SQLite and put in production with
Postgres, and the page works wonderfully in SQLite -- it's only
Postgres that has the problem.

Thanks,

Matt



Here are the methods called during the AJAX request (it starts on
toggle_property(option_id, 'select')):

# session is the user's session (browser stuff)
# Session = scoped_session(sessionmaker(autoflush=True,
transactional=True, bind=config['pylons.g'].sa_engine))

def _single_row(self, option_id, message='', withrow=False):
opt = Session.query(Option).filter_by(id=option_id).first()
if opt:
return render('tpp_manager/option_row.mtl', option=opt,
updateid=option_%s%opt.id, message=message, withrow=withrow)
else:
if not message:
message = Doesn't exist
return render('tpp_manager/option_row.mtl',
message=message, withrow=withrow)

def _toggle_property(self, option_id, prop):
if prop == 'select':
option_id = int(option_id)
if session['tpp_select'].get(option_id, False):
del(session['tpp_select'][option_id])
else:
session['tpp_select'][option_id] = True
session.save()
return True
else:
opt =
Session.query(Option).filter_by(id=option_id).first()
if opt:
if prop == 'whitelisted':
opt.whitelisted = not opt.whitelisted
if opt.whitelisted and opt.blacklisted:
opt.blacklisted = False
elif prop == 'blacklisted':
opt.blacklisted = not opt.blacklisted
if opt.blacklisted and opt.whitelisted:
opt.whitelisted = False
elif prop == 'approved':
opt.approved = not opt.approved
else:
return False
Session.commit()
Session.refresh(opt)
else:
return False
return True

def toggle_property(self, option_id, prop):
message = ''
if not self._toggle_property(option_id, prop):
message = 'Failed to change flag.'
return self._single_row(option_id)


File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/query.py', line 719 in first
  ret = list(self[0:1])
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/query.py', line 748 in __iter__
  return self._execute_and_instances(context)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/query.py', line 751 in _execute_and_instances
  result = self.session.execute(querycontext.statement,
params=self._params, mapper=self.mapper,
instance=self._refresh_instance)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/orm/session.py', line 535 in execute
  return self.__connection(engine,
close_with_result=True).execute(clause, params or {})
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 844 in execute
  return Connection.executors[c](self, object, multiparams, params)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 895 in execute_clauseelement
  return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params)  1), distilled_params=params)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 907 in _execute_compiled
  self.__execute_raw(context)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 916 in __execute_raw
  self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 953 in _cursor_execute
  self._handle_dbapi_exception(e, statement, parameters, cursor)
File '/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.2-py2.4.egg/
sqlalchemy/engine/base.py', line 935 in _handle_dbapi_exception
  raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) current transaction is aborted,
commands ignored until end of transaction block
 'SELECT anon_1.tpp_options_question_id AS
anon_1_tpp_options_question_id, anon_1.tpp_options_option AS
anon_1_tpp_options_option, anon_1.tpp_options_id AS
anon_1_tpp_options_id, anon_1.tpp_options_approved

[sqlalchemy] Re: ProgrammingError: (ProgrammingError) current transaction is aborted, commands ignored until end of transaction block

[Matt Haggard]

INSERT or UPDATE?  I don't do any inserts with this code... only
changing what's already there.

Is an integrity constraint a PG thing, or SQLAlchemy model thing?

And can I do Session.rollback() ?

Thanks!

On Jun 27, 2:19 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jun 27, 2008, at 4:03 PM, Matt Haggard wrote:



  I'm getting a ProgrammingError (I've pasted the last part of the
  traceback at the bottom of the page).

  The error comes from my first heavy-AJAX page in Pylons (postgres
  backend).  If I cause too many AJAX requests at a time, or even after
  doing 3 non-overlapping AJAX requests, I get the error.  I wonder if
  there's some sort of handle releasing I need to do... or transaction
  releasing?

  As a side note... I test using SQLite and put in production with
  Postgres, and the page works wonderfully in SQLite -- it's only
  Postgres that has the problem.

 PG has this issue if you attempt to INSERT a row which throws an  
 integrity constraint; a rollback() is required after this occurs.  I  
 can't see it in your code below but it would imply that such an  
 exception is being caught and then thrown away.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: ProgrammingError: (ProgrammingError) current transaction is aborted, commands ignored until end of transaction block

[Matt Haggard]

I've fixed it by calling Session.clear() at the end of every
controller action (it's in __after__()).

I'm gonna go read about what that does -- right now it's just magic as
far as I can tell :)

Thanks again for the help, Michael

On Jun 27, 3:30 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jun 27, 2008, at 5:03 PM, Matt Haggard wrote:



  INSERT or UPDATE?  I don't do any inserts with this code... only
  changing what's already there.

  Is an integrity constraint a PG thing, or SQLAlchemy model thing?

 its a PG thing.  Other things can likely cause PG to get into this  
 state as well.   But unless you're squashing exceptions, SQLA can't  
 really let the DB get into this state without complaining loudly.

  And can I do Session.rollback() ?

 sure !
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Models split across files

[Matt Haggard]

In my various models, I recently noticed that I have been reusing my
customer table as it relates to many other models.  I've been making
CustomerPart objects in each of the model files and retyping the table
schema and object (only including the pieces I need for that
particular case)

Now I'd like to have one customer model and let the other models
access it -- rather than continually retyping everything.

But I get this error when I break it out (this is when I try to use it
in a controller in pylons):
  raise exceptions.InvalidRequestError(Could not find table '%s' with
which to generate a foreign key % tname)
InvalidRequestError: Could not find table 'customer' with which to
generate a foreign key

I've included a before and after (hooray for GIT) of the models:

Thanks!

Matt



saq.py before (this one works)

from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
func
from sqlalchemy.orm import mapper, relation

from datetime import datetime

from formencode import validators
from smmodels import fe_obj, NoHTML, SuperDateValidator, fe_setter

metadata = MetaData()

...

customer_table_part = Table('customer', metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('email', types.Unicode, unique=True, index=True),
Column('validation_type', types.Unicode),
)

questionnaire_table = Table('saq_questionnaire', metadata,
...
Column('customer_id', types.Integer, ForeignKey('customer.id'),
index=True),
...
)

questions_table = Table('saq_questions_new', metadata,
...
)


class Questionnaire(fe_setter):

def __str__(self):
return 'id: %s customer_id: %s' % (self.id, self.customer_id)

def __repr__(self):
return Questionnaire(%s, customer_id:%s) % (self.id,
self.customer_id)


class Question(fe_setter):
pass


class CustomerPart(fe_setter):

def __init__(self):
pass


mapper(Question, questions_table)
mapper(CustomerPart, customer_table_part)
mapper(Questionnaire, questionnaire_table, properties={
...
'customer'  :relation(CustomerPart, backref='questionnaires')
})





saq.py after (all the same except removed references to CustomerPart

...
from smmodels.customer import customer_table, Customer
...

mapper(Question, questions_table)
mapper(Questionnaire, questionnaire_table, properties={
...
'customer'  :relation(Customer, backref='questionnaires')
})


customer.py after (newly created)

from sqlalchemy import Column, MetaData, Table, types, ForeignKey
from sqlalchemy.orm import mapper, relation

from formencode import validators
from smmodels import fe_obj, NoHTML, fe_setter

from datetime import date

metadata = MetaData()

customer_table = Table('customer', metadata,
Column('id', types.Integer, primary_key=True, index=True),
Column('email', types.Unicode, unique=True, index=True),
Column('validation_type', types.Unicode),
)

class Customer(object):

def __init__(self):
pass

mapper(Customer, customer_table)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Triple Join Table

[Matt Haggard]

Thank you andrija and Micheal (especially recommending getting it
working first without associationproxy -- that really helped)

Here's a solution I've got that works (not exactly as I intended, but
I can live with it). I just keep the section and question paired
together.

from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
func
from sqlalchemy.orm import mapper, relation
from sqlalchemy.sql.expression import select, and_
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection

from datetime import datetime

from formencode import validators
from smmodels import NoHTML, SuperDateValidator

metadata = MetaData()

class SaqType:

def __init__(self):
pass

def __str__(self):
return 'id: %s name: %s' % (self.id, self.name)

def __repr__(self):
return Questionnaire Type(%s, name:'%s') % (self.id,
self.name)


class SaqJoin:

def __init__(self, type_obj=None, question_obj=None,
section_obj=None):
self.type = type_obj
self.question = question_obj
self.section = section_obj

def __repr__(self):
return 'SaqJoin type,question,section:(%s,%s,%s)' %
(self.type_id, self.question_id, self.section_id)



class Questionnaire:

def __str__(self):
return 'id: %s customer_id: %s' % (self.id, self.customer_id)

def __repr__(self):
return Questionnaire(%s, customer_id:%s) % (self.id,
self.customer_id)


question_types = ['yn','yn_na','label','comment']
class Question:
pass

class Section:

def __init__(self):
pass


class Answer:

def __init__(self):
pass


class CustomerPart:

def __init__(self):
pass


sections_by_type = select(
[join_table.c.type_id, join_table.c.section_id],
group_by=[join_table.c.section_id,
join_table.c.type_id]).alias('sections_by_type')

mapper(Question, questions_table)
mapper(Section, sections_table)
mapper(CustomerPart, customer_table_part)
mapper(Answer, answers_table, properties={
'question'  :relation(Question, backref='answer', uselist=False)
})
mapper(SaqJoin, join_table, properties={
'type'  :relation(SaqType),
'section'  :relation(Section, backref='parent',
order_by=join_table.c.ord),
'question' :relation(Question, backref='parent',
order_by=join_table.c.ord),
})
mapper(SaqType, types_table, order_by=types_table.c.id, properties={
'qs':relation(SaqJoin, order_by=join_table.c.ord),
'my_sections'   :relation(Section, secondary=sections_by_type,
primaryjoin = types_table.c.id == sections_by_type.c.type_id,
backref='type', order_by=sections_table.c.secnum),
})
mapper(Questionnaire, questionnaire_table, properties={
'answers'   :relation(Answer, backref='questionnaire'),
'type'  :relation(SaqType),
'customer'  :relation(CustomerPart, backref='questionnaires')
})


On Jun 17, 9:26 am, Michael Bayer [EMAIL PROTECTED] wrote:
 any table that has more than just two foreign keys to remote tables
 does not normally qualify as a secondary table (its only allowed for
 certain edge cases which are not present here).  The Join class
 mapped to the join_table is the right approach.  In which case, you
 *definitely* don't want to be using secondary or secondary_join in
 any case here.  Any access from A-(Join)-B where you don't want to
 see the Join object, you should use the associationproxy at that
 point - but get the entire thing to work first without using
 associationproxy, as its only a convenience extension.

 On Jun 16, 5:11 pm, Matt Haggard [EMAIL PROTECTED] wrote:

  I've got a triple join table (joining three things together) and I'm
  really struggling to get it to work as I intend.  I've pasted the full
  model at the bottom.  I've struggled with this off and on for months
  now... I don't really understand how I can get SQLAlchemy to do what I
  want.

  The join table has 4 columns:

  type_id | section_id | question_id | ord

  What I'd like is the following:

  Questionnaire object with:
     .sections list that contain
        .questions list that contain:
           .answer

  So, something like:
  q = Questionnaire()
  print q.sections[2].questions[1].answer
  # yield the answer to question 1 of section 2 (or question 2 of
  section 3 depending on your indexing :) )

  I encounter a problem because a Question object doesn't know what the
  type_id is because that is stored with the Questionnaire object.
  Likewise from a section object.  I'm pulling my hair out...

  Thanks,

  Matt Haggard

  
  from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
  func
  from sqlalchemy.orm import mapper, relation
  from sqlalchemy.sql.expression import select
  from sqlalchemy.ext.associationproxy import association_proxy

  from datetime import datetime

  from formencode import validators
  from smmodels import NoHTML

[sqlalchemy] appending an object through an 2-level association table

[Matt Haggard]

I'm trying to figure out how to add objects through the ORM.  (My
schema and mappings are below.)

In the shell, I can do the following:
newQ = Question()
# ... set the attributes of newQ
mytype = session.query(QType).first()
mytype.my_sections
# correctly gives all the sections that belong to the type
mytype.my_sections[0].questions
# correctly gives only the questions that belong to both the type and
the section (how does this work btw?)
mytype.my_sections[0].questions.append(newQ)
# inserts only the section_id and question_id into the jointable; it's
missing the type id

How can I get it to also insert the type_id?

Thanks,

Matt


-
questions_table : id | question
sections_table : id | name
types_table : id | name
join_table : type_id | question_id | section_id

class QType(object):
allquestions = association_proxy('joinObj', 'questions')
class QJoin(object): pass
class Question(object): pass
class Section(object): pass

sections_by_type = select(
[join_table.c.type_id, join_table.c.section_id],
group_by=[join_table.c.section_id]).alias('sections_by_type')

mapper(Question, questions_table)
mapper(Section, sections_table, properties={
'questions' :relation(Question, secondary=join_table,
primaryjoin = sections_table.c.id == join_table.c.section_id,
secondaryjoin = join_table.c.question_id ==
questions_table.c.id,
backref='section'),
})
mapper(QJoin, join_table, properties={
'type'  :relation(QType),
'sections'  :relation(Section, backref='parent'),
'questions' :relation(Question, backref='parent'),
})
mapper(QType, types_table, properties={
'joinObj'   :relation(QJoin),
'my_sections'   :relation(Section, secondary=sections_by_type,
primaryjoin = types_table.c.id == sections_by_type.c.type_id,
backref='type'),
})
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Goofy Association Table

[Matt Haggard]

Thank you Michael!

I've got a follow-up question if anyone's up to it.  I've changed my
classes and mappings to the following:

class QType(object):
...
questions = association_proxy('joinObj', 'questions',
creator=_create_joinObj)
sections = association_proxy('joinObj', 'sections')

class Question(object):
...
section = association_proxy('joinObj', 'section')

mapper(QJoin, join_table, properties={
'type'  :relation(QType),
'sections'  :relation(Section, backref='parent'),
'questions' :relation(Question, backref='parent')
})
mapper(Question, questions_table)
mapper(Section, sections_table)
mapper(QType, types_table, properties={
'joinObj'   :relation(QJoin)
})

And it's working, thanks to Michael's help.  Here's my question:  I
have three interrelated thing: Questions, Sections, Types.  I
struggling to do the mappings that would allow these:

1) Given a Type, what are all the Sections (ignoring the Questions;
grouping by Sections)?
   my_type = Type()
   my_sections = my_type.sections  ??

2) Given a Type and Section, what are the Questions?
   my_type = Type()
   my_questions = my_type.sections[0].questions  ??

3) Given a Type, what are all the Questions (ignoring the Sections;
grouping by Questions)?
   my_type = Type()
   all_questions = my_type.questions  ??

I appreciate the help,

Matt

On May 5, 3:32 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On May 5, 2008, at 5:11 PM, Matt Haggard wrote:





  I've got a kind of goofy schema, and I'm trying to map it.  I've got
  Questionnaire types, Sections and Questions all joined in a single
  association table:

  join_table : type_id | section_id | question_id
  questions_table : id | question_text
  sections_table : id | section_name
  types_table : id | type_name

  So, a single question can appear in different sections for different
  types.  How do I do the mapping?  This is what I've got, and it
  doesn't work.

  mapper(Question, questions_table)
  mapper(Section, sections_table, properties={
 'questions':relation(Question, backref='section',
  secondary=join_table)
  })
  mapper(QType, types_table, properties={
 'sections':relation(Section,
 backref = 'type',
 secondary = join_table
 primaryjoin = types_table.c.id==join_table.c.type_id,
 secondaryjoin = join_table.c.section_id==sections_table.id
 )
  })

 your table is not a many-to-many table, its just another entity table
 with associations to other entities.  secondary is not the
 appropriate construct in this case; use an association mapping :

 http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relatio...
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Goofy Association Table

[Matt Haggard]

I've got a kind of goofy schema, and I'm trying to map it.  I've got
Questionnaire types, Sections and Questions all joined in a single
association table:

join_table : type_id | section_id | question_id
questions_table : id | question_text
sections_table : id | section_name
types_table : id | type_name

So, a single question can appear in different sections for different
types.  How do I do the mapping?  This is what I've got, and it
doesn't work.

mapper(Question, questions_table)
mapper(Section, sections_table, properties={
'questions':relation(Question, backref='section',
secondary=join_table)
})
mapper(QType, types_table, properties={
'sections':relation(Section,
backref = 'type',
secondary = join_table
primaryjoin = types_table.c.id==join_table.c.type_id,
secondaryjoin = join_table.c.section_id==sections_table.id
)
})


I get this error when I try to save a type object:
AttributeError: 'PropertyLoader' object has no attribute
'_dependency_processor'

Thanks,

Matt


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] filter_by mapped class attribute?

[Matt]

I have 2 classes A and B which are mapped each to their own table.
There is a foreign key which defines a one to many relationship from A
to B.

Is it possible to query B, but filter on an attribute of A?

ctx.current.query(B).filter_by(A.c.name.like('%foo%'))

This seems to work, but the query to the DB seems to be missing the
join between the tables for A and B...

Sqlalchemy 0.3 BTW...

thx

Matt
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: filter_by mapped class attribute?

[Matt]

 ctx.current.query(B).filter_by(A.c.name.like('%foo%'))

added the join manually:

ctx.current.query(B).filter(B.c.xid ==
A.c.xid).filter_by(A.c.name.like('%foo%'))

m


On Apr 24, 2:33 pm, Matt [EMAIL PROTECTED] wrote:
 I have 2 classes A and B which are mapped each to their own table.
 There is a foreign key which defines a one to many relationship from A
 to B.

 Is it possible to query B, but filter on an attribute of A?

 ctx.current.query(B).filter_by(A.c.name.like('%foo%'))

 This seems to work, but the query to the DB seems to be missing the
 join between the tables for A and B...

 Sqlalchemy 0.3 BTW...

 thx

 Matt
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] 0.3.x eagerload error

[Matt]

I'm having some trouble configuring eager loading for a query:

Query:
domainq =
ctx.current.query(Domain).options(eagerload('company')).all()

Mapper:
domainmapper = mapper(Domain, domains, extension=ModifiedMapper(),
  properties={
'company'  : relation(Company, uselist=False),
  }
)

Error:
type 'exceptions.AttributeError': 'str' object has no attribute
'get_children'

It works fine if I remove the options(eagerload(...)) part...

thx

m
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Handling unique constraints

[Matt Haggard]

I'm using SQLAlchemy with Pylons and am having trouble validating
data.  I have an App object mapped to a table with a unique constraint
on App.number.

Here's some code:

q = Session.query(App)
if app_id:
q = q.filter_by(id=app_id).first()
if q:
c.app = q
number = request.params.get('number')
notes = request.params.get('notes')
if appmodel and number:
try:
q.number = number
q.notes = notes
Session.save(q)
Session.commit()
c.message = 'Record updated'
except:
# restore pre-form data ?? how??
c.message = 'Error updating record'
return render('index.mtl')
else:
return self.index()

My questions are:

1) When I do the try statement, the value of q.number changes to
whatever the user passed in via the form -- even if it's invalid, so
that when I render the page, the invalid value is used.  How do I
reset the object to have the values it had before I did the try?  Do I
have to get it afresh from the db?

2) How do I let the user know which value caused the record not to
update?  What information does SQLAlchemy provide back that I can use
to say: You're number must be unique... and such-and-such must be
greater than 0, etc..?

Thanks,

Matt Haggard
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: eagerload and joined table inheritance

[Matt]

Hey Michael,

I decided it would probably be easier/faster to just write a custom
query -- the frontend is a pylons page that is used to search for
content so I want it to be as responsive as possible:

fields = [content_table.c.content_id,
  content_table.c.type,
  accounts.c.user_name,
  content_table.c.status,
  content_table.c.creation_time,
  content_table.c.title,
  content_table.c.uuid,
  content_leaf.c.reviewed,
  content_leaf.c.flagged]

where = [content_table.c.account_id ==
accounts.c.account_id]

if c.content_title:

where.append(content_table.c.title.like(c.content_title))

yada yada yada...


s = select(fields, whereclause=and_(*where),
from_obj=[outerjoin(content_table,
content_leaf)]).order_by(content_table.c.content_id.desc())
rp = Session.execute(s)

This works pretty well and I think from the DB side, this is about as
fast as it can get since I'm pulling back the minimum amount of data
with a simple query.

thx

Matt

On Dec 30, 5:51 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Dec 30, 2007, at 8:01 PM, Matt wrote:





  Yeah, I didn't include the whole Content mapper, here are the
  polymorphic params for that mapper:

  polymorphic_on=content_table.c.type, polymorphic_identity='content'

  And ContentLeaf:

  mapper(ContentLeaf, content_leaf,
            properties = {
             '_copyright': content_leaf.c.copyright,'copyright' :
  synonym('_copyright'),
             '_grid     ': content_leaf.c.grid,'grid' :
  synonym('_grid'),
             '_gtin'     : content_leaf.c.gtin,'gtin' :
  synonym('_gtin'),
             '_iswc'     : content_leaf.c.iswc,'iswc' :
  synonym('_iswc'),
             '_isan'     : content_leaf.c.isan,'isan' :
  synonym('_isan'),
             '_isrc'     : content_leaf.c.isrc,'isrc' :
  synonym('_isrc'),
             '_reviewed' : content_leaf.c.reviewed,'reviewed' :
  synonym('_reviewed'),
             '_flagged'  : content_leaf.c.flagged,'flagged' :
  synonym('_flagged'),
             '_ingestion_type':
  content_leaf.c.ingestion_type,'ingestion_type' :
  synonym('_ingestion_type'),
            },
            inherits             = cm,
            polymorphic_on       = content_table.c.type,
            polymorphic_identity ='leaf')

  So, loading polymorphically is working for me.

  So maybe I can do something like:

  q =
  Session
  .query
  (Content
  ).add_entity(Account).select_from(content_table.outerjoin(accounts))

  To get the account?

 heres a random guess at what might make this actually work (its a  
 workaround).  Make the regular mappers, with the eagerload to  
 'accounts', and select_table.  Then, after you make your mappers, say  
 this:

 compile_mappers()
 class_mapper(Content).get_select_mapper().add_property('accounts',  
 Account, lazy=False)

 that might be all thats needed here.  basically just sticking the  
 property on the surrogate mapper, which is the mapper mapped to your  
 select_table.

 otherwise, same idea is just make a second mapper against the join  
 using non_primary=True.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Fancy transaction questions

[matt]

Thanks so much. flush() was exactly what I needed. I wasn't using
flush() because I was creating my session with autoflush=True, and I
was unclear on the semantics of that flag. I thought autoflush=True
meant it flushed everything right away, but it doesn't actually
flush() until required to do so by a subsequent database access.

-matt

On Dec 28, 5:46 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 Im assuming you're writing an ORM centric application for my answers
 below.  If not, the answers would be slightly different.

 On Dec 28, 2007, at 8:02 PM, Glypho Phobet wrote:



  Problem #1: I can't get the id of row1 until I commit the
  transaction. I'd like to be able to get, before I commit, the id that
  row1 will have after the commit, so that I can enter it in row2. Or
  is there some way of telling SQLAlchemy that a particular column in
  row2 should point to whatever the id of of row1 will be when the
  commit happens?

 why cant you get the id until commit happens ?  with the ORM you just
 issue a flush() anytime you want and it will insert records/get new
 ids.  im assuming you are also using session.begin() and commit() to
 frame the larger transaction; within those, you can issue as many
 flush() calls as you like and they participate in the same
 transactionso you can do any number of persists, loads, deletes,
 whatever, without ever having to commit anything.  you can also issue
 SQL if you wanted to execute postgres sequences or something like that.

  Problem #2: If I commit before creating row2, in order to get the id
  of row1, any changes I make to row1 after I commit are immediately
  reflected in the database -- regardless of whether I commit or
  rollback at the end. If I try to re-save row1 after making changes, I
  get a traceback saying that row1 is already persistent, like this:

  class 'sqlalchemy.exceptions.InvalidRequestError': Instance
  '[EMAIL PROTECTED]' is already persistent

  Is there a way to make this object non-persistent again?  I want to be
  able to rollback all changes made to row1 and row2 together, if
  there's an error.

 if a transaction fails, you need to remove (or repair, if thats
 feasable) whatever offending objects are present in the session.  if
 your process is going to re-do everything and recreate objects, you
 need to clear the whole session using session.clear()..this is
 typically the best approach after a transaction fails.   usually
 within a web application a failed transaction means youre going to
 report an error and end the request..if you are retrying wihtin one
 request and doing everything again (which is unusual), just do a
 clear().

  Problem #3: Even if I do this in two separate transactions, and
  re-query for row1 by id the second time I need it, SQLAlchemy returns
  an instance that is already persistent. Probably this is some sort
  of
  caching that SQLAlchemy is doing, which I will be very grateful in
  general,
  but is there a way to turn it off for one query?

 the session always returns the same instance for a particular primary
 key once its loaded or persisted.  if you want to remove that
 instance, use session.expunge(theinstance).  or as above
 session.clear() to clear the whole thing.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] eagerload and joined table inheritance

[Matt]

Hi all,

I'm having an issue where I'm doing a query that I want to be loaded
all in one select to the database.  I have a table ContentLeaf which
inherits from Content, I can get the ContentLeaf fields to eagerload
by using select_table in the mapper:

myjoin = content_table.outerjoin(content_leaf)
cm = mapper(Content, content_table,
   select_table = myjoin,
   properties = {
'account': relation(Account, cascade='all',
lazy=False),

Here I also define the account property to eagerload by specifying
lazy=False.  In this case, only the ContentLeaf eagerloads and I see
this in the debug logs:

DEBUG:sqlalchemy.orm.strategies.EagerLoader:Could not locate aliased
clauses for key: (sqlalchemy.orm.mapper.Mapper object at 0x2d95850,
'account')
DEBUG:sqlalchemy.orm.strategies.EagerLoader:eager loader Mapper|
Content|content.account degrading to lazy loader

If I remove the select_table, the account property will then
eagerload, but I really want both to eagerload in the same query.  Any
ideas on how to make this happen?

thx

Matt

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: eagerload and joined table inheritance

[Matt]

Yeah, I didn't include the whole Content mapper, here are the
polymorphic params for that mapper:

polymorphic_on=content_table.c.type, polymorphic_identity='content'

And ContentLeaf:

mapper(ContentLeaf, content_leaf,
   properties = {
'_copyright': content_leaf.c.copyright,'copyright' :
synonym('_copyright'),
'_grid ': content_leaf.c.grid,'grid' :
synonym('_grid'),
'_gtin' : content_leaf.c.gtin,'gtin' :
synonym('_gtin'),
'_iswc' : content_leaf.c.iswc,'iswc' :
synonym('_iswc'),
'_isan' : content_leaf.c.isan,'isan' :
synonym('_isan'),
'_isrc' : content_leaf.c.isrc,'isrc' :
synonym('_isrc'),
'_reviewed' : content_leaf.c.reviewed,'reviewed' :
synonym('_reviewed'),
'_flagged'  : content_leaf.c.flagged,'flagged' :
synonym('_flagged'),
'_ingestion_type':
content_leaf.c.ingestion_type,'ingestion_type' :
synonym('_ingestion_type'),
   },
   inherits = cm,
   polymorphic_on   = content_table.c.type,
   polymorphic_identity ='leaf')

So, loading polymorphically is working for me.

So maybe I can do something like:

q =
Session.query(Content).add_entity(Account).select_from(content_table.outerjoin(accounts))

To get the account?

thx

m


On Dec 30, 2:53 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Dec 30, 2007, at 3:20 PM, Matt wrote:



  Hi all,

  I'm having an issue where I'm doing a query that I want to be loaded
  all in one select to the database.  I have a table ContentLeaf which
  inherits from Content, I can get the ContentLeaf fields to eagerload
  by using select_table in the mapper:

     myjoin = content_table.outerjoin(content_leaf)
     cm = mapper(Content, content_table,
            select_table = myjoin,
            properties = {
             'account'    : relation(Account, cascade='all',
  lazy=False),

 I dont really understand what the desired behavior is here, unless you  
 are trying to load polymorphically - but I dont see any polymorphic  
 attributes on this mapper, so that won't work.

 the mapper for Content will map attributes to each column in the  
 content_table table.    when you specify  
 select_table=content_table.join(someothertable), the columns in  
 (someothertable) will be present in the result rows returned by the  
 database, but will have no impact whatsoever on the data which is  
 fetched from each row, since as far as I can tell nothing is mapped to  
 the content_leaf table.  if content_leaf is mapped to some other  
 class, the Content mapper above still does not have any polymorphic  
 attributes configured so its still not going to have any effect on  
 what gets returned.  so the select_table argument is really  
 meaningless when used with a mapper that has no polymorphic options  
 configured.  if you need to load more than one kind of object from a  
 select or a join, theres other ways to do that, discussed below.



  Here I also define the account property to eagerload by specifying
  lazy=False.  In this case, only the ContentLeaf eagerloads and I see
  this in the debug logs:

 So heres the next side effect of select_table - which is that eager  
 loading relations are not currently get included when you load from a  
 select_table.  Usually, select_table is used for a polymorphic all at  
 once load of a hierarchy of classes, so the joins issued are already  
 fairly complex.  We will eventually have select_table loads include  
 the eagerloading of relations attached to the base mapper, possibly in  
 0.4.3.  But in this case i dont think select_table is what youre  
 looking for unless theres some other detail missing here.



  If I remove the select_table, the account property will then
  eagerload, but I really want both to eagerload in the same query.  Any
  ideas on how to make this happen?

 So, im assuming that you have some other class mapped to  
 content_leaf.    Depending on how you have your ContentLeaf (or  
 whatever its called) class related to Content would determine what to  
 use.  Theres four general patterns you might use.  one is inheritance:

       mapper(Content, content_table,  
 polymorphic_on=content_table.c.type, polymorphic_identity='content')
       mapper(ContentLeaf, content_leaf, inherits=Content,  
 polymorphic_identity='content_leaf')

 another is  eagerloading relation():

      mapper(Content, content_table, properties={
         'leafs':relation(ContentLeaf, lazy=False),
         'account':relation(Account, lazy=False)
      })

 or you'd just like an ad-hoc join that loads both - this might be what  
 youre looking for in this case:

      results =  
 session
 .query
 (Content
 ).add_entity
 (ContentLeaf).select_from(content_table.outerjoin(content_leaf)).all()

 the results would be a list of tuples in the form (Content(),  
 ContentLeaf()).  with the above query the eager load from Content to  
 account should work as well

[sqlalchemy] sqlalchemy object serialization / deserialization

[Matt]

Hi all,

I'm trying to implement simple object serialization (ala the pickle
module) using JSON.  I'm pretty far along, but having one problem --
serializing a sqlalchemy object through the __reduce__ method produces
a circular reference through the InstanceState object.

pprint of the structures in question:

account.__reduce__():
(function _reconstructor at 0x42530,
 (class 'model.account.Account',
  type 'object',
  None),
 {'_comment': None,
  '_description': None,
  '_display_name': u'm',
  '_email': u'[EMAIL PROTECTED]',
  '_email_status': 1L,
  '_entity_name': None,
  '_instance_key': (class 'model.account.Account',
(5L,),
None),
  '_name_graphic': u'm',
  '_password_hash': u'bar',
  '_sa_session_id': 43005744,
  '_state': sqlalchemy.orm.attributes.InstanceState object at
0x286f978,   InstanceState object
  '_status': 1L,
  '_uri': None,
  '_user_name': u'm',
  'account_id': 5L,
  'avatar_exists': False,
  'creation_time': datetime.datetime(2006, 8, 23, 17, 43, 26),
  'modification_time': datetime.datetime(2007, 12, 19, 23, 3, 2),
  'newsletter': False,
  'reviewed': False,
  'site_updates': False})

account._state.__reduce__():
(function _reconstructor at 0x42530,
 (class 'sqlalchemy.orm.attributes.InstanceState', type 'object',
None),
 {'committed_state': {'_comment': None,
  '_description': None,
  '_display_name': u'm',
  '_email': u'[EMAIL PROTECTED]',
  '_email_status': 1L,
  '_name_graphic': u'm',
  '_password_hash': u'bar',
  '_status': 1L,
  '_uri': None,
  '_user_name': u'm',
  'account_id': 5L,
  'avatar_exists': False,
  'creation_time': datetime.datetime(2006, 8, 23,
17, 43, 26),
  'modification_time': datetime.datetime(2007, 12,
19, 23, 3, 2),
  'newsletter': False,
  'reviewed': False,
  'site_updates': False},
  'instance': model.account.Account object at 0x2971350,---
reference back to Account object
  'modified': False,
  'parents': {}})

So right now, I just break the circular reference the second time I
see the Account object, but this causes problems deserializing since
the InstanceState object is missing some data...

Any thoughts appreciated here...  pickle is able to handle this
structure fine, but I'm not sure exactly what algorithm it uses to do
this...

m

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: sqlalchemy object serialization / deserialization

[Matt]

On Dec 20, 5:04 pm, Rick Morrison [EMAIL PROTECTED] wrote:
 You're not going to be able to serialize Python class instances in JSON:
 json strings are simple object literals limited to basic Javascript types.
 Pickle does some pretty heavy lifting to serialize and reconstitute class
 instances.

I've already figured this out -- you can use class hinting as
outlined in this page:

http://json-rpc.org/wiki/specification

So a datetime using this format and converted to JSON might look like:

modification_time: {__jsonclass__: [datetime.datetime, [2007,
12, 19, 23, 3, 2, 2]]}

My deserialization routine loads the datetime module, then gets the
datetime attribute (which in this case is a type, but could be a
function too), and calls that with the arguments in the list.  This
sort of thing works for generic classes too using the pickle
__reduce__ hooks.  The JSON part I'm actually handling through cjson
or simplejson -- my serialization/deserialization is working all on
python objects.

 Easiest way to store JSON in the database is to limit the type of data you
 store in JSON strings to straightforward objects that only use primitive JS
 types, and then serialize back and forth to Python dictionaries. That's what
 libraries like SimpleJSON or cJSON do. Using Sqlalchemy, you can then store
 those JSON strings in database VARCHARS, TEXT and so on fields.

I don't really want to store JSON in the DB, but just use it as a
serialization format for sqlalchemy objects.  We want our frontend to
render data from the same type of object with a couple different
possible backend sources.  One being the database through the
sqlalchemy ORM and another being some sort of JSON/XML interface that
we can backend from whatever...
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Sum with Grouping

[Matt Haggard]

Oh, okay.

Thanks you.

On Nov 20, 5:23 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 the aggregate methods on Query, such as apply_sum(), apply_avg(),
 etc.,  are not in such great shape right now...they've been neglected
 and in fact aren't even working correctly with GROUP BY, etc...I've
 added trac ticket #876 for this.  If you know the exact SQL and
 columns you'd like to get back, typically its best just to issue that
 query without using an ORM construct.  your options for this are
 either just text:

 engine.execute(SELECT sum(amount), type from purchases group by
 type).fetchall()

 or the expression construct would look like:

 engine.execute(select([func.sum(Purchase.amount),
 Purchase.type]).group_by(Purchase.type)).fetchall()

 one important thing to be aware of is that SA has two distinct levels
 of SQL API; the ORM, which deals with Sessions and Query objects, and
 the SQL Expression Language, which deals with select(), update(),
 etc.  the expression language provides more direct access to SQL
 constructs.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Sum with Grouping

[Matt Haggard]

thank you for the response. I have a few more followup questions (I am
really a newbie to this...) :

1. Where does engine come from?  Is there anyway to do what you've
suggested with Session?  If it helps, I'm using this with pylons and
am trying to get stuff working in the controller of my app.

2. If I have to write SQL (or a pythonic version of SQL) to get info
out of the DB, why am I even using SQL Alchemy?  it seems a little
ridiculous.

On Nov 20, 5:23 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 the aggregate methods on Query, such as apply_sum(), apply_avg(),
 etc.,  are not in such great shape right now...they've been neglected
 and in fact aren't even working correctly with GROUP BY, etc...I've
 added trac ticket #876 for this.  If you know the exact SQL and
 columns you'd like to get back, typically its best just to issue that
 query without using an ORM construct.  your options for this are
 either just text:

 engine.execute(SELECT sum(amount), type from purchases group by
 type).fetchall()

 or the expression construct would look like:

 engine.execute(select([func.sum(Purchase.amount),
 Purchase.type]).group_by(Purchase.type)).fetchall()

 one important thing to be aware of is that SA has two distinct levels
 of SQL API; the ORM, which deals with Sessions and Query objects, and
 the SQL Expression Language, which deals with select(), update(),
 etc.  the expression language provides more direct access to SQL
 constructs.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Sum with Grouping

[Matt Haggard]

I'm very new to sqlalchemy and I'm still trying to wrap my head around
how it works.

I have a table with columns: type, amount.  I want to sum the amounts
grouped by type.  In SQL I would write:
SELECT sum(amount), type from purchases group by type;

How do I do this with SQLAlchemy?  This is what I have so far, but I
don't really understand what's going on:

pq = Session.query(Purchase).apply_sum(Purchase.amount)
for x in pq:
  ret += 'br' + str(x.type) + str(x.amount)
  # This prints out every item in the db... the sum seems to not have
done anything

bytypes = pq.group_by(Purchase.type)
for x in bytypes:
  ret += 'br' + str(x.type) + str(x.amount)
  # This prints out one of each type, but the amount is not the sum of
all the types, it's just the last one of each type

bytypes = bytypes.sum(Purchase.amount)
# This is the sum of everything.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Error with query and joined table inheritance

[Matt]

I have some classes that inherit from one another, here are the
mappers:

mapper(Content, content, polymorphic_on=content.c.type,
polymorphic_identity='content')
mapper(TvContent, content_tv_metadata, inherits=Content,
polymorphic_identity='tv show')
mapper(SoftwareContent, content_software_metadata, inherits=Content,
polymorphic_identity='software')
mapper(MusicContent, content_music_metadata, inherits=Content,
polymorphic_identity='music')
mapper(MovieContent, content_movie_metadata, inherits=Content,
polymorphic_identity='movie')

And the error I'm getting?

Traceback (most recent call last):
  File ./test_sahara_content.py, line 19, in ?
x = q.get(16634)
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
query.py, line 96, in get
return self._get(key, ident, **kwargs)
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
query.py, line 778, in _get
return q.all()[0]
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
query.py, line 608, in all
return list(self)
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
query.py, line 656, in __iter__
return self._execute_and_instances(context)
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
query.py, line 661, in _execute_and_instances
return iter(self.instances(result, querycontext=querycontext))
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
query.py, line 727, in instances
context.attributes.get(('populating_mapper', id(instance)),
object_mapper(instance))._post_instance(context, instance)
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
mapper.py, line 1549, in _post_instance
p(instance)
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
mapper.py, line 1568, in post_execute
self.populate_instance(selectcontext, instance, row, isnew=False,
instancekey=identitykey, ispostselect=True)
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
mapper.py, line 1519, in populate_instance
(newpop, existingpop, post_proc) =
selectcontext.exec_with_path(self, prop.key,
prop.create_row_processor, selectcontext, self, row)
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
query.py, line 1243, in exec_with_path
return func(*args, **kwargs)
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
interfaces.py, line 487, in create_row_processor
return
self._get_context_strategy(selectcontext).create_row_processor(selectcontext,
mapper, row)
  File /Library/Frameworks/Python.framework/Versions/2.4/lib/
python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
strategies.py, line 75, in create_row_processor
elif self.columns[0] in row:
TypeError: iterable argument required

Any ideas?  This is using 0.4.1
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Error with query and joined table inheritance

[Matt]

Thanks for the insight Michael -- some records in the content table
had a 'type' of one of the subclasses, but not a record in the
corresponding subclass table -- works like a charm once I insert
records for everything in the subclass tables.

Matt

On Nov 18, 7:58 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Nov 18, 2007, at 10:15 PM, Matt wrote:





  I have some classes that inherit from one another, here are the
  mappers:

  mapper(Content, content, polymorphic_on=content.c.type,
  polymorphic_identity='content')
  mapper(TvContent, content_tv_metadata, inherits=Content,
  polymorphic_identity='tv show')
  mapper(SoftwareContent, content_software_metadata, inherits=Content,
  polymorphic_identity='software')
  mapper(MusicContent, content_music_metadata, inherits=Content,
  polymorphic_identity='music')
  mapper(MovieContent, content_movie_metadata, inherits=Content,
  polymorphic_identity='movie')

  And the error I'm getting?

  Traceback (most recent call last):
   File ./test_sahara_content.py, line 19, in ?
 x = q.get(16634)
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  query.py, line 96, in get
 return self._get(key, ident, **kwargs)
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  query.py, line 778, in _get
 return q.all()[0]
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  query.py, line 608, in all
 return list(self)
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  query.py, line 656, in __iter__
 return self._execute_and_instances(context)
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  query.py, line 661, in _execute_and_instances
 return iter(self.instances(result, querycontext=querycontext))
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  query.py, line 727, in instances
 context.attributes.get(('populating_mapper', id(instance)),
  object_mapper(instance))._post_instance(context, instance)
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  mapper.py, line 1549, in _post_instance
 p(instance)
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  mapper.py, line 1568, in post_execute
 self.populate_instance(selectcontext, instance, row, isnew=False,
  instancekey=identitykey, ispostselect=True)
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  mapper.py, line 1519, in populate_instance
 (newpop, existingpop, post_proc) =
  selectcontext.exec_with_path(self, prop.key,
  prop.create_row_processor, selectcontext, self, row)
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  query.py, line 1243, in exec_with_path
 return func(*args, **kwargs)
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  interfaces.py, line 487, in create_row_processor
 return
  self
  ._get_context_strategy
  (selectcontext).create_row_processor(selectcontext,
  mapper, row)
   File /Library/Frameworks/Python.framework/Versions/2.4/lib/
  python2.4/site-packages/SQLAlchemy-0.4.1-py2.4.egg/sqlalchemy/orm/
  strategies.py, line 75, in create_row_processor
 elif self.columns[0] in row:
  TypeError: iterable argument required

  Any ideas?  This is using 0.4.1

 my idea is that the mapper is trying to load the row for a subclass
 table, such as content_software_metadata, and its getting back None
 for the rowwhich should ideally raise an assertion error.  but its
 not so its going much deeper before it craps out.   As to why the row
 is None, from what you've shown ive no idea (unless you've manipulated
 those tables externally to the ORM, that would do it).  Im looking at
 unit tests which have essentially the exact same code.   So, youd have
 to tell me if this error occurs with 0.4.0 as well, and you also need
 to send me the full structure of your tables and the code that exactly
 produces the error. if its actualy a bug which was introduced in
 0.4.1, you can look forward to 0.4.2 by tomorrow at the latest.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group

[sqlalchemy] Re: Can I remove a Column from the select() result set

[Matt Culbreth]

Thanks Michael.

To make a long story short, I'm appending a couple columns onto a
query, running it, and then using those two extra columns for some
client-side logic (involving calculations to be precise).  At the end
of that logic, I need to remove those two extra columns.  I'd like to
be able to do that and keep the RowProxy stuff.

There's a workaround though so it's not a big deal.  I've looked
through the code and it seems I'd be going too far outside of the core
design to keep doing this.


On Nov 17, 11:03 am, Michael Bayer [EMAIL PROTECTED] wrote:
 On Nov 17, 2007, at 12:26 AM, Matt Culbreth wrote:



  Howdy Group,

  Let's say I have a very simple query:  select person.id, person.name,
  person.age from person

  Can I remove one of the columns, say person.age, from this result
  set and still use the list as a RowProxy?  I'm trying to do it now and
  it's not working.  I'm creating a new list and appending all but the
  last (for example) columns, but it's then missing the RowProxy
  goodness.

  I realize I can do this in the original select, but I'm doing some
  client-side logic here and I need to manipulate the dataset.

 well if you use list operations etc. on the RowProxy or ResultProxy  
 youll get just a plain list (or dict, depending on what youre doing).

 within SA, we use a lot of decorator like approaches, not the python  
 @decorator thing but rather another collection class that wraps the  
 original RowProxy.

 you'd have to illustrate what you're specifically trying to do for us  
 to have some suggestions.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Can I remove a Column from the select() result set

[Matt Culbreth]

Howdy Group,

Let's say I have a very simple query:  select person.id, person.name,
person.age from person

Can I remove one of the columns, say person.age, from this result
set and still use the list as a RowProxy?  I'm trying to do it now and
it's not working.  I'm creating a new list and appending all but the
last (for example) columns, but it's then missing the RowProxy
goodness.

I realize I can do this in the original select, but I'm doing some
client-side logic here and I need to manipulate the dataset.

Thanks,

Matt
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: adding an object through association_proxy

[Matt]

I think I misunderstood the use of the association proxy -- I don't
think I want to use it in this case anymore...

thx

m

On Oct 31, 6:33 pm, Matt [EMAIL PROTECTED] wrote:
 Hi all,

 I have a table (Content) which relates to itself via a many-to-many
 relationship through a link table (ContentCollection).  I'm trying to
 setup parent/child relationships for the Content table using  the
 association_proxy since there are some fields I'll need to modify in
 ContentCollection:

 class Content(object):
 children = association_proxy('collection_children', 'child')
 parents  = association_proxy('parent_collections', 'parent')

 class ContentCollection(object):
 pass

 Session.mapper(Content, content,properties={
  'collection_children' : relation(ContentCollection,
 primaryjoin=content.c.id==content_collection.c.collection_id,
 cascade=all, delete, delete-orphan),
  'parent_collections'  : relation(ContentCollection,
 primaryjoin=content.c.id==content_collection.c.content_id,
 cascade=all, delete, delete-orphan)

 })

 Session.mapper(ContentCollection, content_collection, properties={
   'child' : relation(Content,
 primaryjoin=content.c.id==content_collection.c.content_id),
   'parent' : relation(Content,
 primaryjoin=content.c.id==content_collection.c.collection_id),

 })

 Read access seems to work as I'd expect, but when I add an item using
 Content.children.append(item), I get something like:

 sqlalchemy.exceptions.OperationalError: (OperationalError) (1048,
 Column 'content_id' cannot be null) u'INSERT INTO content_collection
 (priority, home, version, creation_time, modification_time,
 collection_id, content_id, is_primary) VALUES (%s, %s, %s,
 UTC_TIMESTAMP(), UTC_TIMESTAMP(), %s, %s, %s)' [0, None, None, 79940L,
 None, None]

 Seems to only be supplying the id for the child and not the parent
 when it's creating the intermediate table record...  Any ideas what
 I'm doing wrong here?

 thx

 Matt


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] adding an object through association_proxy

[Matt]

Hi all,

I have a table (Content) which relates to itself via a many-to-many
relationship through a link table (ContentCollection).  I'm trying to
setup parent/child relationships for the Content table using  the
association_proxy since there are some fields I'll need to modify in
ContentCollection:

class Content(object):
children = association_proxy('collection_children', 'child')
parents  = association_proxy('parent_collections', 'parent')

class ContentCollection(object):
pass

Session.mapper(Content, content,properties={
 'collection_children' : relation(ContentCollection,
primaryjoin=content.c.id==content_collection.c.collection_id,
cascade=all, delete, delete-orphan),
 'parent_collections'  : relation(ContentCollection,
primaryjoin=content.c.id==content_collection.c.content_id,
cascade=all, delete, delete-orphan)
})

Session.mapper(ContentCollection, content_collection, properties={
  'child' : relation(Content,
primaryjoin=content.c.id==content_collection.c.content_id),
  'parent' : relation(Content,
primaryjoin=content.c.id==content_collection.c.collection_id),
})

Read access seems to work as I'd expect, but when I add an item using
Content.children.append(item), I get something like:

sqlalchemy.exceptions.OperationalError: (OperationalError) (1048,
Column 'content_id' cannot be null) u'INSERT INTO content_collection
(priority, home, version, creation_time, modification_time,
collection_id, content_id, is_primary) VALUES (%s, %s, %s,
UTC_TIMESTAMP(), UTC_TIMESTAMP(), %s, %s, %s)' [0, None, None, 79940L,
None, None]

Seems to only be supplying the id for the child and not the parent
when it's creating the intermediate table record...  Any ideas what
I'm doing wrong here?

thx

Matt


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] advanced mapping help

[Matt]

Hi, I have a relation I'm mapping through an intermediate table and it
seems to work fine, it looks something like:

Content - ContentCollection - Content

'collection_children':relation(Content,
   secondary = content_collection,
   primaryjoin   = content.c.id ==
content_collection.c.collection_id,
   secondaryjoin =
content_collection.c.content_id == content.c.id,
   order_by  =
content_collection.c.priority,
  ),

The trouble is, I want to be able to set a couple fields on the
ContentCollection table, but I don't actually have a property for
those fields on the Content object anywhere since I'm mapping through
that table...  Any ideas on how I should handle this?

thx

Matt


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: advanced mapping help

[Matt]

Thanks I think I had tried something like this before...  Anyway, now
I'm defining the relation from Content to ContentCollection and
separately from ContentCollection back to Content, but I'm running
into the chicken and the egg problem...  The mapper expects a class,
but I can't define Content before ContentCollection and
ContentCollection before Content...

thx

m

On Jun 22, 11:32 pm, [EMAIL PROTECTED] wrote:
 u may need an explicit intermediate association object, see the docs
 about many to many relations.
 Instead of directly getting the children, u'll get the associations
 via which u can get the children, or whatever attributes of the link.
 Then if u want a direct children-list, u can make some python property
 to do that i.e. return [x.child for x in me.children_links].

  Hi, I have a relation I'm mapping through an intermediate table and
  it seems to work fine, it looks something like:

  Content - ContentCollection - Content

  'collection_children':relation(Content,
 secondary = content_collection,
 primaryjoin   = content.c.id ==
  content_collection.c.collection_id,
 secondaryjoin =
  content_collection.c.content_id == content.c.id,
 order_by  =
  content_collection.c.priority,
),

  The trouble is, I want to be able to set a couple fields on the
  ContentCollection table, but I don't actually have a property for
  those fields on the Content object anywhere since I'm mapping
  through that table...  Any ideas on how I should handle this?

  thx

  Matt


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Anybody seen--Exception: invalid byte sequence for encoding UTF8?

[Matt Culbreth]

Howdy All,

I've got some existing code that I'm trying on a new server.  The code
was formerly running with Python 2.4 and SA 0.36, but this new server
is running Python 2.5 and SA 0.37.

Anyway, I've got a small program which is loading a PostgreSQL 8.2 db
from a CSV file, and I'm getting this exception:

sqlalchemy.exceptions.SQLError: (ProgrammingError) invalid byte
sequence for encoding UTF8: 0xe16e69
HINT:  This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
client_encoding.

The particular (fake, generated) set of data doing this is shown
here.  It looks like that first element (city) is encoded as something
other than latin1:

{'city': 'Gu\xe1nica', 'first_name': 'Patricia', 'last_name':
'Wagner', 'zip': '25756', 'phone': '490.749.6157', 'state': 'KS',
'annual_salary': '72333', 'broker_id': 452L, 'date_hired':
datetime.date(2004, 1, 1), 'address': 'P.O. Box 815, 6723 Eget, Ave',
'commission_percentage': 0.080120064101811897}

Has anybody seen this?  Do I need to do a convert_unicode or anything
like that?

Thanks,

Matt


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Anybody seen--Exception: invalid byte sequence for encoding UTF8?

[Matt Culbreth]

Thanks Michael, I'll do this.

When I change the model's column types to Unicode() I still get the
same type in the DB--character varying(100).  I'm assuming that's
correct?  The DB is using a UTF8 encoding.

On Jun 2, 9:53 am, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jun 2, 2007, at 8:22 AM, Matt Culbreth wrote:





  Howdy All,

  I've got some existing code that I'm trying on a new server.  The code
  was formerly running with Python 2.4 and SA 0.36, but this new server
  is running Python 2.5 and SA 0.37.

  Anyway, I've got a small program which is loading a PostgreSQL 8.2 db
  from a CSV file, and I'm getting this exception:

  sqlalchemy.exceptions.SQLError: (ProgrammingError) invalid byte
  sequence for encoding UTF8: 0xe16e69
  HINT:  This error can also happen if the byte sequence does not match
  the encoding expected by the server, which is controlled by
  client_encoding.

  The particular (fake, generated) set of data doing this is shown
  here.  It looks like that first element (city) is encoded as something
  other than latin1:

  {'city': 'Gu\xe1nica', 'first_name': 'Patricia', 'last_name':
  'Wagner', 'zip': '25756', 'phone': '490.749.6157', 'state': 'KS',
  'annual_salary': '72333', 'broker_id': 452L, 'date_hired':
  datetime.date(2004, 1, 1), 'address': 'P.O. Box 815, 6723 Eget, Ave',
  'commission_percentage': 0.080120064101811897}

  Has anybody seen this?  Do I need to do a convert_unicode or anything
  like that?

 if youre parsing from the CSV file, your best bet is to parse the
 data into python unicode objects using the expected encoding of the
 filethat will detect any text in the file thats not in the
 expected encoding.  then just use the DB with convert_unicode=True
 either on create_engine() or within the individual String() types
 (String(convert_unicode=True) is now equivalent to Unicode()).


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Asynchronous SQLAlchemy--Anybody using Twisted, sAsync?

[Matt Culbreth]

Howdy Group,

I'm playing out with a few things now and I wanted to see if anyone
else has used SQLAlchemy in an asynchronous manner?  For example, you
could create a service which responded to asynchronous requests for
data, and could be used by a web client, desktop client, other types
of clients, etc.

The sAsync project at http://foss.eepatents.com/sAsync/ seems ideally
suited for this but I haven't seen any comments about it here.

Thanks,

Matt


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] How to determine if an instance has been populated?

[Matt Culbreth]

Hello Friends,

I'm working with the latest version of SQLAlchemy now and I have a
question: how do I determine if a particular mapped object instance
has been populated by the database?

The question originates because I have defined a __repr__() method on
one of my mapped objects.  It works fine if the object has been
loaded, but throws a TypeError exception until that time because one
of the statements in the __repr__() method is using an 'int' type.

I can easily handle this by checking for None of course, but is there
a more standard way people use the tool?

Thanks,

Matt


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: How to determine if an instance has been populated?

[Matt Culbreth]

That got it, thanks.

On Feb 12, 3:57 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 check for an _instance_key attribute.

 On Feb 12, 1:52 pm, Matt Culbreth [EMAIL PROTECTED] wrote:

  Hello Friends,

  I'm working with the latest version of SQLAlchemy now and I have a
  question: how do I determine if a particular mapped object instance
  has been populated by the database?


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---