Re: [sqlalchemy] Baked Query with Bound Entity/FromClause
Hi Mike, On Wednesday, March 31, 2021 at 1:36:33 PM UTC-4 Mike Bayer wrote: > so this is not what bound parameters are used for in SQL; bound parameters > are a specific construct in the DBAPI driver that only applies to literal > parameters in a statement, that is, strings, numbers and other values > inside of comparisons, values to be passed. they never correspond to > database objects like table or column names nor do they refer to parts of a > SQL statement. > Thanks for the quick response, and thanks for helping me to improve my understanding of bindparams. In my mind, they were a general way of "templating" the query from SQLAlchemy's point of view, but it makes more sense that they map directly to the bound parameter concept from the DBAPI's perspective. > If you are experimenting with baked query, I would strongly advise > bypassing them entirely and upgrading to SQLAlchemy 1.4, where queries are > now cached automatically and the awkwardness of baked queries is no longer > needed. > As you intuited, I'm still on SQLAlchemy 1.3 at the moment; we have existing baked queries that I was doing some maintenance on and I noticed a repeating query in multiple functions and wanted to factor it out. Once it was not working, I had to figure out how to make it work, even though it's not strictly necessary, just out of my own curiosity. > With baked queries, to include your incoming "model" as part of the cache > key, you can add it up front as one of the arguments to cache on: > > baked_query = BAKERY(lambda session: session.query(model), > args=(model, )) > > that will include the class mentioned by "model" as part of the cache > key. I would ensure that "model" is a long lived object , e.g. a mapped > class. If it's an aliased() object, I'd make sure to use the same > aliased() object each time. > Adding an additional positional argument to augment the cache key worked perfectly for my use case. I am indeed using a subclass of my ORM's declarative base, so it is long-lived. Thanks! Scott -- 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/323d7a55-7246-4b23-9de7-54bf7d1a7779n%40googlegroups.com.
[sqlalchemy] Baked Query with Bound Entity/FromClause
Hello,
I have a bunch of tables with created_at columns and I would like to bake
queries to retrieve counts of rows from them.
def _entities_created(model: Model, before: datetime) -> int:
baked_query = BAKERY(lambda session: session.query(model))
baked_query += lambda q: q.with_entities(func.count())
baked_query += lambda q: q.filter(model.created_at <
bindparam("before"))
return baked_query(session()).params(before=before).scalar()
foos_created = partial(_entities_created, Foo)
bars_created = partial(_entities_created, Bar)
This doesn't work, and upon a minute of reflection, it's clear why: the
passed-in value of model is cached in the baked query. If you call
foos_created(...) first and then call bars_created(...), you'll get the
count of the Foos, and vice versa. I've tried a few things to fix this:
- baked_query = BAKERY(lambda session: session.query(bindparam("model")))
- This "works" in that it runs, but shows the same problem as the
original version.
- baked_query = BAKERY(lambda session: session.query()); baked_query +=
lambda q: q.select_from(bindparam("entity"))
- This raises an "ArgumentError: argument is not a mapped class,
mapper, aliased(), or FromClause instance." at query compilation time
(in _as_query())
Is what I'm trying to do here possible? Is there a way to get an aliased() or
FromClause from a bindparam? Is there an alternative approach that would
allow me to continue to use baked queries while not having to implement a
separate *_entities_created() for every model I'm interested in?
The next problem would then be how to refer to model.created_at within the
filter clause. Can that be handled with a bindparam too?
Thanks,
Scott
--
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/fb1efc22-ab63-482a-bb45-e4291c624bf8n%40googlegroups.com.
Re: [sqlalchemy] Create Sqlalchemy ORM class from regular class gets "has no attribute ''_sa_instance_state''"
Yes, sorry I didn't follow up
On Mon, Mar 15, 2021 at 12:28 PM Simon King wrote:
> I haven't followed your code in detail, but I think the problem might be
> here:
>
> clazz = school.Class('12', 'A')
>
> students = [
> Student("Name1", "Sname1", clazz=clazz, code='aa7'),
> Student("Name2", "Sname2", clazz=clazz, code='bb7'),
> Student("Name3", "Sname3", clazz=clazz, code='cc7')
> ]
>
> You are creating an instance of "school.Class", which is the
> non-sqlalchemy base class. You probably meant to create an instance of
> "Class", which is the SQLAlchemy-mapped subclass, didn't you?
>
> Simon
>
> On Fri, Mar 12, 2021 at 11:10 AM ScottBot wrote:
> >
> > I have a game that I am coding for school (not a project or homework)
> and I am trying to use a SQLite database with sqlalchemy to store the game
> info. I don't know what the problem is or how I can fix it. Any help is
> appreciated.
> >
> https://stackoverflow.com/questions/66591466/sqlalchemy-orm-from-regular-class
> >
> > --
> > 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/b3e6fb67-6cab-4484-8c39-a01999640e67n%40googlegroups.com
> .
>
> --
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/lpZVfrXIiy4/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexcFYWK7yu9hW42LgUDB3LL%2BFAuCJqogpvYDjBe1MHigNQ%40mail.gmail.com
> .
>
--
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/CAA0UvfCNWiDNijDgkHrijCYhvLVsFicGH4qw_2_-UCsMUau60Q%40mail.gmail.com.
Snowflake odities and ReplaceableObject
I am using alembic with Snowflake. In terms of DDL migrations I need to cater for SECURED VIEWS, it is pretty trivial to extend ReplaceableObject to deal with this. The other thing I am finding is that Snowflake insists that DROP FUNCTION be provided with a list of the argument types along with the function name, so with a function: CREATE FUNCTION myfunc(TABLENAME VARCHAR) RETURNS VARCHAR this does not work: DROP FUNCTION myfunc I need to go DROP FUNCTION myfunc(VARCHAR) But I cannot go DROP FUNCTION myfunc(TABLENAME VARCHAR) So in terms of using ReplaceableObject, to CREATE I need: ReplaceableObject( "myfunc(TABLENAME VARCHAR)", """ RETURNS VARCHAR LANGUAGE javascript ... But to DROP I need: ReplaceableObject( "myfunc(VARCHAR)", """ RETURNS VARCHAR LANGUAGE javascript ... I can hack something together with a superclass, but though I would mention here in case someone has a cleaner solution. Cheers, Scott -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/38d5e79a-2e50-4924-a77a-c2def597a132%40googlegroups.com.
Re: [sqlalchemy] Creating Namespaced/Gapless Sequences Using Event Listeners
Thank you for the help! The approach with the context-sensitive default function worked out well for me. I especially like how it keeps the "magic" close by to the rest of the code, hopefully reducing surprises when I have to come back to this code eventually. I have an idea for a "namespaced sequence factory" which I think might be cool. I’ll post it to the group here if it works out. Thanks again, Scott Colby On Oct 4, 2019, 19:12 -0400, Mike Bayer , wrote: > > > On Fri, Oct 4, 2019, at 3:57 PM, Scott Colby wrote: > > Hello, > > > > I am attempting to implement a "gapless" sequence using SQLAlchemy's > > event listeners instead of DB-level triggers inspired by > > [this](https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences) > > and similar approaches. (In fact, I don't particularly care about > > the gapless property but more about a "namespaced" sequence: my > > application has many parents, each with many children. I want to > > give the children numbers grouped by their parent.) > > > > I have created a minimal example which I have uploaded as a gist > > [here](https://gist.github.com/scolby33/e09624839a2b422312113d22b4e56da5). > > It can be run as `python namespaced_sequences.py` or provided with > > an optional argument specifying a DB URL, e.g., > > `python namespaced_sequences.py "postgresql://..."`. > > > > The example contains the declarative classes, the necessary boilerplate > > for setting up SQLAlchemy, my event listeners, and a main function > > that exercises the code with asserts specifying my desired behavior. > > > > The event listener `insert_child_number_sequence` seems to work > > correctly: upon the insertion of a row in the `parent` table, a > > matching row is inserted in the `child_number_sequence` table. > > > > My problem arises with the `set_child_number` listener. As can be > > seen when running the example, the ORM properly updates the > > `child_number_sequence` row with SQL like > > > > UPDATE child_number_sequence > > SET last_child_number=(child_number_sequence.last_child_number + 1) > > WHERE child_number_sequence.parent_id = 1; > > > > This is what I would expect for an atomic increment in SQL. > > > > But the insert of the new child issues SQL like > > > > INSERT INTO child (parent_id, child_number) > > VALUES (1, child_number_sequence.last_child_number + 1)); > > > > which fails with `no such column: child_number_sequence.last_child_number` > > (on SQLite; `missing FROM-clause entry for table "child_number_sequence"` > > on PostgreSQL). > > > > I think I can see what is going on here--my assignment in Python > > is propagating directly to the parameters used in the `INSERT` > > statement. In PostgreSQL, I would instead use a `RETURNING` clause > > to get the new value and in SQLite, I would use `BEGIN IMMEDIATE` > > to ensure that I have a write lock on the DB before reading anything. > > > > I attempted several approaches to have SQLAlchemy use the new value > > as computed by the database. For instance, adding > > `session.add(child_number_sequence); session.refresh(child_number_sequence)` > > between the increment and the assignment of the value to > > `instance.child_number`. Interestingly, this issues two `SELECT`s > > but completely swallows the `UPDATE`; the child gets assigned child > > number 0. > > > > I considered using the statements API to use the PostgreSQL `RETURNING` > > clause or `SELECT FOR UPDATE` but I would prefer to only have one > > code path for PostgresSQL and SQLite (and others?) if possible. > > > > well you definitely have to have slightly different code for RETURNING vs. > non. I would also lose the ORM stuff for this sequence (e.g. the > ChildNumberSequenceClass) as it's making things complicated and instead do > what you need to do with the before_insert handler, where you have access to > the Connection being used to emit SQL. Run the UPDATE (or INSERT) that you > need on the sequence table using Core > connection.execute(table.update().values(...)), and use RETURNING if the > dialect supports it (use connection.dialect.implicit_returning), otherwise > retrieve it using a SELECT. Then populate that value on the object that is > passed to before_insert(). > > Alternatively, you could do this whole thing using a Python side default as > well. the approach at > https://docs.sqlalchemy.org/en/13/core/defaults.html#context-sensitive-default-functions > illustrates a "context", which has a cursor right there, how
[sqlalchemy] Creating Namespaced/Gapless Sequences Using Event Listeners
Hello, I am attempting to implement a "gapless" sequence using SQLAlchemy's event listeners instead of DB-level triggers inspired by [this]( https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences) and similar approaches. (In fact, I don't particularly care about the gapless property but more about a "namespaced" sequence: my application has many parents, each with many children. I want to give the children numbers grouped by their parent.) I have created a minimal example which I have uploaded as a gist [here](https://gist.github.com/scolby33/e09624839a2b422312113d22b4e56da5). It can be run as `python namespaced_sequences.py` or provided with an optional argument specifying a DB URL, e.g., `python namespaced_sequences.py "postgresql://..."`. The example contains the declarative classes, the necessary boilerplate for setting up SQLAlchemy, my event listeners, and a main function that exercises the code with asserts specifying my desired behavior. The event listener `insert_child_number_sequence` seems to work correctly: upon the insertion of a row in the `parent` table, a matching row is inserted in the `child_number_sequence` table. My problem arises with the `set_child_number` listener. As can be seen when running the example, the ORM properly updates the `child_number_sequence` row with SQL like UPDATE child_number_sequence SET last_child_number=(child_number_sequence.last_child_number + 1) WHERE child_number_sequence.parent_id = 1; This is what I would expect for an atomic increment in SQL. But the insert of the new child issues SQL like INSERT INTO child (parent_id, child_number) VALUES (1, child_number_sequence.last_child_number + 1)); which fails with `no such column: child_number_sequence.last_child_number` (on SQLite; `missing FROM-clause entry for table "child_number_sequence"` on PostgreSQL). I think I can see what is going on here--my assignment in Python is propagating directly to the parameters used in the `INSERT` statement. In PostgreSQL, I would instead use a `RETURNING` clause to get the new value and in SQLite, I would use `BEGIN IMMEDIATE` to ensure that I have a write lock on the DB before reading anything. I attempted several approaches to have SQLAlchemy use the new value as computed by the database. For instance, adding `session.add(child_number_sequence); session.refresh(child_number_sequence)` between the increment and the assignment of the value to `instance.child_number`. Interestingly, this issues two `SELECT`s but completely swallows the `UPDATE`; the child gets assigned child number 0. I considered using the statements API to use the PostgreSQL `RETURNING` clause or `SELECT FOR UPDATE` but I would prefer to only have one code path for PostgresSQL and SQLite (and others?) if possible. Unfortunately, I'm stumped as how to do this. What is the right approach to implement this pattern? Thank you, Scott Colby -- 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/4a1d8ea8-fbeb-4158-a791-3c4780602c5a%40googlegroups.com.
Re: Alembic: Varying database names per environment?
On Monday, August 19, 2019 at 9:58:19 AM UTC+10, Mike Bayer wrote: > > > > On Sun, Aug 18, 2019, at 6:50 PM, Scott wrote: > > Looking to use Alembic to manage migrations. > > We currently have different database names in each environment, so for > dev, test and prod we have db_dev, db_test and db_prod respectively. > > Is this database naming scheme going to be compatible with Alembic or am I > better off looking to drop the environment suffix? > > > these are three different URLs and if the concern is putting them into one > alembic.ini file, easy enough using separate sections: > https://alembic.sqlalchemy.org/en/latest/cookbook.html#run-multiple-alembic-environments-from-one-ini-file > > though usually staging and production DBs have a password you're looking > to keep private, and you'd have a separate alembic.ini on your staging and > prod servers. but either way it's all doable > Thanks for your reply. The databases in question will in fact contain the same schema, table, view, etc. objects. We develop in dev, then promote code and database changes to test and then on to prod. This seems like a pretty straightforward use case for Alembic; each DB will have its own version and when we promote code from dev to test and then on to prod the relevant head would be retrieved from git (along with application code) and can be applied to the target database in order to bring it up to the correct version. In our case however, with manual deployment we included a variable in the database name and change this per environment. So when we promote code we need the changes we made to db_*dev*.schema1.table1 to be made to db_*test*.schema1.table1. I think this is different concept to the what "sections" provides. If I was going to manually create the upgrade/downgrade scripts every time I could continue to use a variable to compute the database name, but I could never use autogenerate as this would bring in a specific database name and I would no longer be able to move my code between environments. I suspect the safest approach will be if we drop the environment suffix from our table names. This will be easier all around. Happy to receive any further advice others may have to offer. Wondering for example if *render_item* can be used. Cheers, Scott -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/878a46b2-9e3c-4f40-8369-dd05f45e872f%40googlegroups.com.
Alembic: Varying database names per environment?
Looking to use Alembic to manage migrations. We currently have different database names in each environment, so for dev, test and prod we have db_dev, db_test and db_prod respectively. Is this database naming scheme going to be compatible with Alembic or am I better off looking to drop the environment suffix? Thanks, Scott -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/e61e70a9-c2dc-4880-a839-f36272cbfad7%40googlegroups.com.
Re: [sqlalchemy] ROLLBACK not happening with transaction in test suite
Thanks for the information! Indeed, the workaround solved my problem.
Is there a downside to unconditionally activating the workaround, or should
I check for use of pysqlite?
from sqlite3 import Connection as _sqlite3_Connection
from sqlalchemy import event as _event
from sqlalchemy.engine import Engine as _Engine
@_event.listens_for(_Engine, 'connect')
def do_connect(dbapi_connection, connection_record):
if isinstance(dbapi_connection, _sqlite3_Connection):
# disable pysqlite's emitting of the BEGIN statement entirely.
# also stops it from emitting COMMIT before any DDL.
logger.debug('setting connection isolation level to `None` to work
around pysqlite bug')
dbapi_connection.isolation_level = None
@_event.listens_for(_Engine, 'begin')
def do_begin(connection):
if isinstance(connection._Connection__connection.connection,
_sqlite3_Connection):
# emit our own BEGIN
logger.debug('emitting our own BEGIN to work around pysqlite bug')
connection.execute('BEGIN')
My only other question is, for future debugging, where should I log to get
*all* the SQL that is ultimately passed to the database?
Thanks,
Scott
On Monday, May 28, 2018 at 6:29:14 PM UTC-7, Mike Bayer wrote:
>
>
>
> On Mon, May 28, 2018, 8:00 PM Mike Bayer > wrote:
>
>> On Mon, May 28, 2018 at 7:23 PM, Scott Colby > > wrote:
>> > Hello all,
>> >
>> > I am working on testing a project that uses SQLalchemy with the pytest
>> > testing framework. I have adapted the code from the docs to pytest as
>> shown.
>> >
>> > logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
>> >
>> > cursor_handler = logging.FileHandler('cursor.log')
>> > cursor_log = logging.getLogger('cursor_log')
>> > cursor_log.addHandler(cursor_handler)
>> > cursor_log.setLevel(logging.DEBUG)
>> > cursor_log.propagate = false
>> >
>> >
>> > @pytest.fixture(scope='session')
>> > def created_database_path(tmpdir_factory):
>> > db_path = tmpdir_factory.mktemp('ocspdash').join('ocspdash.db')
>> >
>> > engine = create_engine(f'sqlite:///{db_path}')
>> > Base.metadata.create_all(engine)
>> >
>> > yield db_path
>> >
>> > @pytest.fixture(scope='session')
>> > def manager_session(created_database_path):
>> > engine = create_engine(f'sqlite:///{created_database_path}')
>> >
>> > @event.listens_for(engine, 'before_cursor_execute')
>> > def receive_before_cursor_execute(connection, cursor, statement,
>> > parameters, context, executemany):
>> > cursor_log.debug(statement)
>> > cursor_log.debug(parameters)
>> >
>> > connection = engine.connect()
>> >
>> > session_maker = sessionmaker(bind=connection)
>> > session = scoped_session(session_maker)
>> >
>> > @event.listens_for(session, 'after_transaction_end')
>> > def restart_savepoint(session, transaction):
>> > if transaction.nested and not transaction._parent.nested:
>> > # ensure that state is expired the way
>> > # session.commit() normally does
>> > session.expire_all()
>> >
>> > session.begin_nested()
>> >
>> > transaction = connection.begin()
>> > session.begin_nested()
>> >
>> > manager = Manager(
>> > engine=engine,
>> > session=session,
>> > )
>> >
>> > yield manager, connection
>> >
>> > session.close()
>> > transaction.rollback()
>> >
>> > connection.close()
>> >
>> >
>> > Pytest fixtures are sort of like context managers: everything before the
>> > `yield` is the set up and after the `yield` is the tear down.
>> >
>> > I have separated the `create_all` part because SQLalchemy seems to issue
>> > unnecessary COMMITs in the process of creating the tables:
>>
>>
>> the create_all() needs to be called with the connection that you've
>> started the transaction within:
>>
>> trans = connection.begin()
>> metadata.create_all(connection)
>>
>> then no COMMITs will be emitted.
>>
>>
>> >
>> >
>> > These commits would prematurely end the transaction created by
>> > `connection.begin()`.
>>
>> is that when using a file-based SQLIte database or :memory: ? if a
>> file based SQLite database, it would be using a separate connection.
>>
>>
>> >
>> > But here is the real problem. Consider the output in `cursor.log` of one
>> > test function
[sqlalchemy] ROLLBACK not happening with transaction in test suite
ty; 1|Test Authority|2345|2018-05-28 22:59:28 sqlite> It's still in there! So here are my questions: - why does `engine.create_all` issue unnecessary COMMITs? - why is the final ROLLBACK not showing up in the 'before_cursor_execute' event? - what am I doing wrong that the transaction doesn't actually get rolled back? (I know that `scoped_session` might not be the most appropriate thing to use here, but that is how the real code works and I want to test in a similar environment; additionally, when I tried this whole exercise with a normal `sessionmaker`, all these problems happened in an identical manner.) Thanks, Scott Colby -- 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] Possible bug with populating backref collections
I think it's easier to explain in an example than for me to waffle on
trying to explain myself :)
from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, Session
Base = declarative_base()
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
bs = relationship("B", backref='a')
class B(Base):
__tablename__ = "b"
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e, autoflush=False, autocommit=True)
a1 = A()
b1 = B()
b2 = B()
b3 = B()
a1.bs.append(b1)
s.add(a1)
s.flush()
a1.bs.append(b2)
s.expire_all()
b3.a = a1
assert(b3 in a1.bs) # Yep, as you might expect
b2.a = a1
assert(b2 in a1.bs) # Nope
--
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 sessions in a GUI application
> > > It's possible that if you're using the session in "autocommit" mode and > just want to occasionally "broadcast" your object state to a quick > "flush everything", that in fact turning off the "accounting" feature > with this flag is appropriate. I'm looking at this code and it really > is all about the thing you don't want, that is, anytime the transaction > goes away, erasing all the object state because we no longer know the > state of the DB. > Thanks Mike, having read the source some more myself I agree :) > Trying to recall why I have expire_on_commit=False but there's no > expire_on_rollback, it's because after a commit, the only thing that can > make the database state change vs. what your objects have are other > transactions outside of what you have in front of you. but with > rollback, there could have been any number of insert/update/delete > within the last transaction that are gone. The state of the objects is > going to be a combination of data that was loaded from the DB that might > have been rolled back, data that was the result of server-side defaults > and sequences from INSERT statements that might have been rolled back, > and then values that came from the application, but those values are > organized onto an object structure that itself might not exist anymore > because the transaction was rolled back. I think the idea is that to > really try flushing again, you have to start from zero when the > transaction first started and replay everything, otherwise things will > not be the same as they were before. > That make complete sense. I guess the only way to avoid it would be to somehow take a snapshot of the state of all of the mapped objects and their status in the session when a transaction begins, and restore that after the corresponding rollback. I bet that could actually be implemented with some event listeners, though I guess that is just a more convoluted way of achieving the same thing as _enable_transaction_accounting=False. Would it make sense, do you think, to allow disabling of transaction accounting on a per transaction basis, instead of for the entire session? e.g. if I could say session.flush(enable_transaction_accounting=False) to disable it for the transaction it creates to do the flushing? -- 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 sessions in a GUI application
Hi Mike Thank you for your quick and thoughtful response as usual! > so the quick and dirty approach is a flag I'm not enthused about, > nevertheless I don't plan on removing, called > _enable_transaction_accounting, > Yes I saw this when poking around in the source code - it does seem like a bit of a backwards step; I will try and solve my problem without that :) > The more architectural answer is that the ORM considers these objects to > be proxies for state within a database transaction. > Understood. I guess we are going a bit outside the intended SQLAlchemy use-case here. > > You can do this kind of pattern by having the ORM objects represent the > GUI state as "detached" objects, then copy the state of those objects > into the session using a method like Session.merge. > I think this is what I will probably end up doing. There's a quite a bit of business logic attached to the mapped classes - for example the 'Actions' are polymorphic and contain methods that are 'recipes' for generating a standard set of steps based on the attributes of the action. > > The best way would be that the GUI has its own dedicated data structure > that is distinct from your ORM model. Yes, I think you are right here - though the business logic I described above makes this a bit troublesome. In the past I have done this sort of thing, created a throwaway mapped class and populated it with data so that I could make use of its methods. A more high-end way of representing this is to create "action" objects > which represent things the user does, Deluxe indeed.. I'll have a think about that, though it is probably overkill for the simple app I am writing. Every time I do a GUI with SQLAlchemy I try a slightly different approach - it is tantalising because each time I think I have cracked the perfect pattern but it always ends up being slightly more complicated than I thought! I can't come up with any suggestions on how to do it better though. Keep up the good work - we love SQLAlchemy at my firm. It is directly responsible for keeping track of several billion dollars worth of financial products and I sleep soundly at night having selected it as a platform :) -- 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] Using sessions in a GUI application
Hello all, Short summary of my question: Is it possible to stop rollback() from expiring dirty objects? Specifically, in the case where there are - local changes made outside to mapped objects outside of a transaction - flush() is called - the flush fails In this case SQLAlchemy will expire any objects that had local changes, and those changes are lost. I would ideally like get objects & session to be in the same state as they were before I called flush(). Longer explanation of my approach, in case I am just doing it wrong :) I have a GUI application where users can load, view, and edit some database mapped objects. Stripped down, it's pretty simple stuff like this: class Action(Base): __tablename__ = "action" id = Column(Integer, primary_key=True) description= Column(Text) class Step(Base, ActionSchemaMixin): __tablename__ = "step" id = Column(Integer, primary_key=True) id_action = Column(Integer, ForeignKey(Action.id)) action = relationship(Action, backref='steps') So the action has some attributes, and a number of steps, which have their own attributes. Users can select an action (or create a new one) - then add, remove, and update steps for that action from the GUI. I have found it to be an extremely useful pattern to couple these widgets directly to the mapped objects they are dealing with, and let the various event handlers to update the mapped objects whenever the user alters the GUI. The following pseudo-code should give you an idea class ActionWidget(Widget): def __init__(action): self.action = action self.description_edit_widget = TextEditWidget(on_changed=self. on_description_widget_changed) def on_description_widget_changed(self, new_description): self.action.description = new_description I also have a widget for a list-of-steps that shows a summary for each, and allows you to add/remove new ones - and a widget for editing an individual step. Because I have no control over how long the user will faff about when editing one of these actions, and we do not want long open transactions to the database, I have set up my session with the slightly controversial (autoflush=False, autocommit=True, expire_on_commit=False) options. I load up the action and all the steps, the user can take as long as they like mucking about with it - and when they press the 'Save' button, I just flush all the changes. It works great, except if something goes wrong with the flushing. If some DB constraint is violated for example, the transaction created in flush() is rolled back. All my modified objects are expired, and any new ones are expunged, as faithfully described in the docs. In this case though, it is unfortunate, because now I have a mismatch between what is in my GUI and the objects they are supposed to be displaying. I want to be able to warn my user about the problem and let them fix the one field that was wrong, without forcing them to loose all their changes and start again. It is quite difficult for me to recreate the mapped objects changed state from the GUI elements alone, as there are temporary widgets created at times to edit certain attributes. The only way around this that I can think of is to detach all the objects before coupling them to the GUI elements - then in my save function, somehow copying all of the updated/new objects before adding them to a session and attempting to commit the changes. I could get this to work, but think it might be fiddly when there are a mixture of new and dirty persisted objects. Interestingly when creating a set of brand new objects, things are fine too - although they are expunged, they can be re-added to the session when the user is ready to try again. hoping there is a simple answer but suspecting there isn't :) All the best, Philip -- 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] Nested transaction rollback does not undo changes to instances
(Chris's colleague here) On Thursday, 2 April 2015 17:23:13 UTC+1, Michael Bayer wrote: this issue is fixed for 0.9.10 and 1.0.0b5, you can test now using either latest master or the rel_0_9 branch. Just wanted to say thank you very much for the prompt fix; I continue to be amazed at your efforts :) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Automatically Adding M2M on append causing UNIQUE constraint violation
I have an intermediate model which I am using to keep track of which labs
to include since individual labs can be removed.
So when you add a new intermediate model (AgendaModel) it automatically
adds all of the labs.
with
@event.listens_for(Agenda.modules, 'append')
But there is a UNIQUE constraint violation.
I'm using Flask and Flask-SQLAlchemy
and
psql (PostgreSQL) 9.3.4
Traceback (most recent call last):
File db.py, line 102, in module
db.session.commit()
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py,
line 149, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
line 768, in commit
self.transaction.commit()
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
line 370, in commit
self._prepare_impl()
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
line 350, in _prepare_impl
self.session.flush()
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
line 1907, in flush
self._flush(objects)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
line 2025, in _flush
transaction.rollback(_capture_exception=True)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
line 57, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
line 1989, in _flush
flush_context.execute()
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
line 371, in execute
rec.execute(self)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
line 480, in execute
self.dependency_processor.process_saves(uow, states)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/dependency.py,
line 1087, in process_saves
secondary_update, secondary_delete)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/dependency.py,
line 1130, in _run_crud
connection.execute(statement, secondary_insert)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
line 727, in execute
return meth(self, multiparams, params)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py,
line 322, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
line 824, in _execute_clauseelement
compiled_sql, distilled_params
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
line 954, in _execute_context
context)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
line 1116, in _handle_dbapi_exception
exc_info
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py,
line 189, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
line 924, in _execute_context
context)
File
/home/siecje/Desktop/db/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py,
line 432, in do_executemany
cursor.executemany(statement, parameters)sqlalchemy.exc.IntegrityError:
(IntegrityError) UNIQUE constraint failed: agenda_labs.lab_id,
agenda_labs.agenda_id, agenda_labs.module_id u'INSERT INTO agenda_labs (lab_id,
agenda_id, module_id) VALUES (?, ?, ?)' ((1, 1, 1), (1, 1, 1))
from flask import Flaskfrom flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)app.config['SQLALCHEMY_DATABASE_URI'] =
'sqlite:///test.db'db = SQLAlchemy(app)
from sqlalchemy import funcfrom sqlalchemy.ext.orderinglist import ordering_list
# Many-To-Many tablesmodule_labs = db.Table('module_labs',
db.Column('lab_id', db.Integer, db.ForeignKey('labs.id'), primary_key=True),
db.Column('module_id', db.Integer, db.ForeignKey('modules.id'),
primary_key=True))
agenda_labs = db.Table('agenda_labs',
db.Column('lab_id', db.Integer, db.ForeignKey('labs.id'), primary_key=True),
db.Column('agenda_id', db.Integer, primary_key=True),
db.Column('module_id', db.Integer, primary_key=True),
db.ForeignKeyConstraint(['agenda_id', 'module_id'],
['agenda_modules.agenda_id', 'agenda_modules.module_id']))
class AgendaModule(db.Model):
__tablename__ = 'agenda_modules'
module_id = db.Column(db.Integer, db.ForeignKey('modules.id'),
primary_key=True)
agenda_id =
Re: [sqlalchemy] List Objects To Be Deleted
fabulous, this works great. thanks!
now I've got something like this in my code:
def delete_cascade_preview(instance):
keys = [rel.key for rel in inspect(instance.__class__).relationships if
rel.cascade.delete == True]
return {key:getattr(instance, key).all() for key in keys if
getattr(instance, key).count() 0}
On Tuesday, July 15, 2014 3:59:21 PM UTC-4, Michael Bayer wrote:
I’d look at inspect(MyClass).relationships to see where the linkages are
to other classes. Then you can probe those for the features you need -
once you have one, it has a string “key”. you can then see that by just
getattr(someobj, “somekey”).
On Jul 15, 2014, at 3:40 PM, Scott Meisburger smeis...@gmail.com
javascript: wrote:
I've got an app where through an admin interface, a user can delete
objects in the database. This happens via:
db.session.delete(obj)
db.session.commit()
There are cascade rules defined in Python for these objects (using the
ORM). What I want to do is display to the user a list of related objects
that will be deleted along with the parent and have them confirm Yes or No.
At the very least, I'd like to be able to say whether or not there are ANY
related objects which will be deleted. I assume this can be done via some
introspection feature?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Automatically populate Association Object Field
I have a Many-to-Many relationship that I would like to keep track of the
order. I am wondering if I can automatically populate the module_position
field based on the current number of AgendaModule's for an Agenda.
class AgendaModule(db.Model):
__tablename__ = 'agenda_modules'
module_id = db.Column(db.Integer, db.ForeignKey('modules.id'),
primary_key=True)
agenda_id = db.Column(db.Integer, db.ForeignKey('agendas.id'),
primary_key=True)
module_position = db.Column(db.Integer)
# module_position = db.Column(db.Integer,
default=len(Agenda.query.get(agenda_id).modules) + 1) # NameError: name
'Agenda' is not defined
module = db.relationship('Module')
class Agenda(db.Model):
__tablename__ = 'agendas'
modules = db.relationship('AgendaModule', backref='agendas',
order_by=AgendaModule.module_position)
a = Agenda(name='Test') mod = Module.query.all()[0] am =
AgendaModule() am.module = mod am.module_position = len(a.modules) +
1 a.modules.append(am) a.modules[app.agendas.models.AgendaModule
object at 0x7fe7ccfccd50] am.module_position1
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] unable to open database file
Hi, A user of my applicable is getting a unable to open database file None None error because the file path to their database has a Á character in it. It works fine if the character is removed, but that is not a good solution. Does anyone know how to solve this? Thanks, Scott -- 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] unable to open database file
Michael, Thanks for the hint about python's sqlite3. I'll just point out that I can work around the issue directly with sqlite3 by providing a relative path that does not include the character: import sqlite3, os os.chdir(/path/with/non/ascii/character) conn = sqlite3.connect(file.db) However if I take this same approach with sqlalchemy, it does not fix the issue. It appears that this is because sqlalchemy always provides the absolute path to sqlite3. If I comment out these lines in sqlalchemy/dialects/sqlite/pysqlite.py's create_connect_args() method, then the above workaround works: if filename != ':memory:': filename = os.path.abspath(filename) I am going to file a bug report to see if this should/could be changed (I'm not sure if there are any downsides to removing these lines). Scott On Thursday, June 19, 2014 12:48:57 PM UTC-6, Michael Bayer wrote: no but this is more of a pysqlite/sqlite3 issue, you should ask on the Python users list, and refer to the sqlite3.connect() function: import sqlite3 conn = sqlite3.connect(/path/to/file.db) On 6/19/14, 2:28 PM, Scott Horowitz wrote: Hi, A user of my applicable is getting a unable to open database file None None error because the file path to their database has a Á character in it. It works fine if the character is removed, but that is not a good solution. Does anyone know how to solve this? Thanks, Scott -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] unable to open database file
Ah great, that is much better than having to modify sqlalchemy code. Indeed, the below code works for me: os.chdir(os.path.dirname(db_path)) e = create_engine(sqlite:///, creator=lambda: sqlite3.connect(os.path.basename(db_path))) Thanks! Scott On Thu, Jun 19, 2014 at 2:09 PM, Mike Bayer mike...@zzzcomputing.com wrote: On 6/19/14, 3:37 PM, Scott Horowitz wrote: Michael, Thanks for the hint about python's sqlite3. I'll just point out that I can work around the issue directly with sqlite3 by providing a relative path that does not include the character: import sqlite3, os os.chdir(/path/with/non/ascii/character) conn = sqlite3.connect(file.db) However if I take this same approach with sqlalchemy, it does not fix the issue. It appears that this is because sqlalchemy always provides the absolute path to sqlite3. well if sqlite3.connect(os.path.abspath(relative/path)) is failing, that's something for the Python core / SQLite folks regardless. if you need a workaround right now you can pass creator to the engine: e = create_engine(sqlite://, creator=lambda: sqlite3.connect(whatever)) -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/Q6dAkM0y0Yo/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Oracle with sequence for primary key and that sequence out of sync
Hello,
I am using SQLAlchemy 0.9.4 with Python 2.6.6 on RHEL 6.5 with
Oracle 11.2.0.3.
My simple application queries an LDAP directory to find
particular records and then reflects the information into an
Oracle table. The application is designed to keep the database
table version of the information in sync with the LDAP
directory as the system of record.
Here is some pseudocode that shows the basic outline of what I
am doing:
class AdminGroup(Base):
__tablename__ = 'admin_groups'
id = Column(Integer, Sequence('seq_admin_groups_id'), primary_key = True)
dn = Column(String)
name = Column(String)
display_name = Column(String)
primary_mail = Column(String)
def __repr__(self):
return
AdminGroup(dn='%s',name='%s',display_name='%s',primary_mail='%s')
% (
self.dn, self.name, self.display_name, self.primary_mail)
db_engine = create_engine(...)
SessionClass = sessionmaker(bind = db_engine)
# loop every N minutes
session = SessionClass()
ldap_groups = ldap_connection.search_ext_s(...)
for dn, attr_dict in ldap_groups:
# see if group is represented in database table
admin_group = session.query(AdminGroup).filter(AdminGroup.dn
== %s % dn).first()
if admin_group:
# group exists so see if any updates needed
else:
# create the group in the database table
admin_group = AdminGroup(dn = dn, name = name, ...)
session.add(admin_group)
# done with all groups so commit and sleep until next iteration
session.commit()
Note that the class AdminGroup() is written to use the
sequence seq_admin_groups_id to generate the primary key.
I created the sequence in the Oracle database using sqlplus
and the command:
CREATE SEQUENCE seq_admin_groups_id START WITH 1 INCREMENT BY 1;
COMMIT;
I then ran the Python code and it performed as I expected,
creating roughly 500 rows in the table. It continued to run
fine for a few days, creating and deleting rows now and then.
After a few days the code began throwing this exception:
2014-05-30 16:32:24,337 ERROR Master: Caught database
exception while provisioning: (raised as a result of
Query-invoked autoflush; consider using a session.no_autoflush
block if this flush is occuring prematurely) (IntegrityError)
ORA-1: unique constraint (MYSCHEMA.SYS_C0015706) violated
'INSERT INTO admin_groups (id, dn, name, display_name,
primary_mail) VALUES (seq_admin_groups_id.nextval, :dn, :name,
:display_name, :primary_mail) RETURNING admin_groups.id
INTO :ret_0' {'dn':
'cn=somename,ou=groups,dc=bc,dc=edu',
'primary_mail': 'somen...@my.edu',
'display_name': 'somename', 'name':
'somename', 'ret_0': cx_Oracle.NUMBER with value None}
I investigated and found that the sequence seq_admin_groups_id
was now at the value 68 after having been used previously to
insert rows with IDs in the 500s.
I stopped the code and used sqlplus to change the sequence
back to a value in the high 500s. I then restarted the code
and the exception no longer occurred.
I am unable to explain how the sequence seq_admin_groups_id
went from in the 500s and working fine to suddenly being 68.
The only place in the Python code where the sequence is used
explicitly is in the definition of the AdminGroup() class.
I would be grateful for any insights on how the sequence might
have become out of sync or anything I can change in the code
to prevent it from happening again.
Thank you for your consideration.
Scott
P.S. I should add that I am using cx_Oracle and the connection
string
oracle+cx_oracle://user:password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=somehost)(PORT=1521)))(CONNECT_DATA=(SID=MYSID)))
--
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] joinedload()
Ah, so, it turns out to be more subtle than I first thought. It took me
quite a while to narrow it down to an easily reproducible case. To trigger
the behavior you need to be: joinedloading() along a backref, and also I
think it matters that I am joining back onto the same table and returning a
bunch of objects. Quite a corner case I think.
This code demonstrates the behavior - it issues a second query in the for
loop for the 'A' which has no child.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
join_table = Table('parent_child', Base.metadata,
Column('id_a', ForeignKey('a.id')),
Column('id_b', ForeignKey('a.id')))
parent = relationship(A,
secondary=join_table,
primaryjoin = (id == join_table.c.id_a),
secondaryjoin = (id == join_table.c.id_b),
uselist=False,
backref=backref(child, uselist=False))
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
a2 = A(parent=a1)
sess.add(a1)
sess.add(a2)
sess.commit()
sess.close()
results = sess.query(A).options(joinedload(child)).all()
print
for a in results:
print a.child is None
On Mon, Mar 24, 2014 at 8:34 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Mar 24, 2014, at 2:09 PM, Philip Scott safetyfirstp...@gmail.com
wrote:
Is this a bug, or perhaps some expected side effect of the joined load?
seemed like something that might be possible but the scalar loader is
initializing the attribute to None to start with, here's a simple test that
doesn't show your behavior, so see if you can just modify this one to show
what you are seeing. note we only need to see that 'bs' is in a1.__dict__
to prevent a lazyload.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B, secondary=Table('atob', Base.metadata,
Column('aid', ForeignKey('a.id')),
Column('bid', ForeignKey('b.id'))
),
uselist=False)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add(A())
sess.commit()
sess.close()
a1 = sess.query(A).options(joinedload(bs)).first()
assert 'bs' in a1.__dict__
assert a1.__dict__['bs'] is None
assert a1.bs is None
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] joinedload()
I understand, I had a feeling it would be something like that. Don't worry,
I can work around it by using a subqueryload() instead (which I guess fixes
it by changing the order things are loaded?)
Thank you very much for taking the time to answer; still loving SQLAlchemy!
On Tue, Mar 25, 2014 at 1:06 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Mar 25, 2014, at 7:48 AM, Philip Scott safetyfirstp...@gmail.com
wrote:
Ah, so, it turns out to be more subtle than I first thought. It took me
quite a while to narrow it down to an easily reproducible case. To trigger
the behavior you need to be: joinedloading() along a backref, and also I
think it matters that I am joining back onto the same table and returning a
bunch of objects. Quite a corner case I think.
This code demonstrates the behavior - it issues a second query in the for
loop for the 'A' which has no child.
this is very difficult to resolve and it may have been something I've said
was unfixable in the past. The a2 object here is loaded in two different
contexts, one is as the joined loaded child of a1, the other as a first
class result. Because when the query orders by a1, a2, we hit a1 first,
a2 is necessarily loaded as the child of a1. The joined loading only goes
one level deep, that is, it doesn't load the children of children, unless
you told it to by saying joinedload(child).joinedload(child). So
a2.child's loader is declared as not loaded. then on the next row it
comes in as a first class result, but the a2 object we get there is only an
identity map lookup - this object is already loaded.
Adjusting this behavior would require the loading logic figure out
mid-results that the context for a particular object is changing. pretty
complicated.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
join_table = Table('parent_child', Base.metadata,
Column('id_a', ForeignKey('a.id')),
Column('id_b', ForeignKey('a.id')))
parent = relationship(A,
secondary=join_table,
primaryjoin = (id == join_table.c.id_a),
secondaryjoin = (id == join_table.c.id_b),
uselist=False,
backref=backref(child, uselist=False))
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
a2 = A(parent=a1)
sess.add(a1)
sess.add(a2)
sess.commit()
sess.close()
results = sess.query(A).options(joinedload(child)).all()
print
for a in results:
print a.child is None
On Mon, Mar 24, 2014 at 8:34 PM, Michael Bayer
mike...@zzzcomputing.comwrote:
On Mar 24, 2014, at 2:09 PM, Philip Scott safetyfirstp...@gmail.com
wrote:
Is this a bug, or perhaps some expected side effect of the joined load?
seemed like something that might be possible but the scalar loader is
initializing the attribute to None to start with, here's a simple test that
doesn't show your behavior, so see if you can just modify this one to show
what you are seeing. note we only need to see that 'bs' is in a1.__dict__
to prevent a lazyload.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B, secondary=Table('atob', Base.metadata,
Column('aid', ForeignKey('a.id')),
Column('bid', ForeignKey('b.id'))
),
uselist=False)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
sess = Session(e)
sess.add(A())
sess.commit()
sess.close()
a1 = sess.query(A).options(joinedload(bs)).first()
assert 'bs' in a1.__dict__
assert a1.__dict__['bs'] is None
assert a1.bs is None
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https
[sqlalchemy] joinedload()
Hello all, I am trying to optimize a query I have, and have noticed some strange behavior. I have a table called 'Trade'. A trade may optionally have a commission, which itself is a trade. This is the core of it: class Trade(Base, CCPTableMixin): __tablename__= trade id = Column(Integer, primary_key=True) trade_commission = Table(trade_commission, Base.metadata, Column(id_trade_victim, Integer, ForeignKey(id), primary_key=True), Column(id_trade_cash, Integer, ForeignKey(id), primary_key=True)) commission = relationship(trade_class_name, secondary=trade_commission, primaryjoin=(id == trade_commission.c.id_trade_victim), secondaryjoin=(id == trade_commission.c.id_trade_cash), uselist=False, backref=backref(commission_for, uselist=False)) Now, in a particular query, if a trade happens to be a commission trade, I wish to eagerly load the original 'victim' trade, so I have something like this: session().query(Trade) query = query.options(joinedload(commission_for)) I noticed things were going a bit slowly, and to my surprise when I turned on query tracing I could see many individual queries. It turns out that the joined loading was working in general, however, if 'commission_for' was None (e.g. the trade is not a commission trade) SQLAlchemy was issuing a query to re-populate the (empty) collection. Interestingly, when I switched it to 'subqueryload' - it didn't do this and correctly eagerly loaded the empty collection. However, I have quite an expensive query to select the list of trades in the first place which I would rather not do twice, which is required for a subquery eager load. (It would be really cool if you could select the primary keys into a temporary table or something when doing a subquery load, but I guess that would be quite difficult to do in a DB independant way) Is this a bug, or perhaps some expected side effect of the joined load? I am using SQLAlchemy 0.9.3. If it help I could knock up a working example in short order. Cheers, and keep up the fine work! - Philip -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] matches_any: an extension to the Query object, and a HSTORE attribute access property
Hi folks, SQLAlchemy is at the heart of what we do at work (UK based hedge fund); so first of all a big thank you; I am not quite sure where we'd be without you. We would like to give back to the community as much as we can (I tried to get some of the developers on the company xmas present list this year but was too late.. cross your fingers for next year). We have extended SQLAlchemy in a few places, though it is quite intermingled with our domain specific stuff I keep an eye out for little snippets that might be useful to others. So here's a trivial one; take it or leave it (and feel free to think of a better name). Knowing my luck it already exists; though I have looked hard through the docs! class QueryEnhanced(Query): ''' Add a few extra bells and whistles to the standard Query object ''' def matches_any(self): ''' Returns true if your query would return 1 or more rows; false otherwise. The following two statements ask the same question; but matches_any is _much_ quicker on large tables: my_query.matches_any() my_query.count() != 0 ''' return self.session.scalar(select([self.exists()])) The other bit of technology we have that could be unpicked without _too_ much trouble is a sort of reverse CompositeProperty; many attributes of different types, including collections, out of one HSTORE column (with a sort of side-loaded instrumentation for mutation tracking that I think could have been done in a more idiosyncratic way). Paraphrasing a bit but you can do things like: class Animal(Base): data = Column(MutableDict.as_mutable(HSTORE)) colour = HsProperty(data, String) legs = HsProperty(data, Integer) discovered = HsProperty(data, Date) fun_facts = HsProperty(data, JSONEncoded(list)) 'colour', 'legs', 'discovered', and 'fun_facts' end up as keys in the HSTORE and the values are strings, integers, dates and lists on the python side but stored as strings in the HSTORE such a way that they can be CAST-ed in a server query [where possible]: session().query(Animal).filter(Animal.legs 2) and get a query like SELECT ... FROM animal WHERE CAST(animal.data - legs AS INTEGER) 2 You can also put an arbitrary JSONEncodable object in there too. Collections get converted to Mutable counterparts for change-tracking. In many ways it is similar to ColumnProperty except that - the properties are writable (and when written only trigger the relevant bits of the hstore to be updated). Also on object instances the values in HsProperties are fetched as part of the query; we lazily de-serialise them directly from the hstore dictionary. Before spend a couple of days removing our corporate clutter from that, getting permission to license it etc.. and posting either as a patch or extension I thought I would see if there is any interest (or if someone has already done it better?). It's implemented as a custom metaclass right now, but I think I might be able to do it fully with events. Code aside, if you can think of ways in which we as a company could support SQLAlchemy (bear in mind I am not in charge of the purse strings, but I can make a pitch on your behalf; we are still awaiting the fruits of our donation to the PyPy http://morepypy.blogspot.co.uk/2012/01/py3k-and-numpy-first-stage-thanks-to.htmlguys :).** Then do let me know. I don't check this email account all that regularly but my work address is my firstname.lastname at cantabcapital dot com Keep up the good work! ** [shameless plug] Or if you are keen, enthusiastic, mostly competent, and looking for a well paid job where you get to do loads of Python SQLAlchemy -- 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] load events with polymorphic_on
this is normal, loading for the base class only hits those columns which are defined for that base class - it does not automatically fan out to all columns mapped by all subclasses. to do so, you can specify with_polymorphic: Ahh, thank you very much Michael that does do exactly what I want. So many times in SQLAlchemy I have been rummaging and hacking for days and then there's a simple one liner that does exactly what I was after all along :) One problem remains though. I use a Query.from_self() which seems to cause SA to forget about the with_polymorphic setting I have given in mapper_args. If I try to remind it, by explicitly saying my_query.with_polymorphic(*) I get errors like this: Query.with_polymorphic() being called on a Query with existing criterion. Which seems to be related in some way to having a .distinct or .order_by clause in the query. Is this expected/understood? Thank you so much for your help! All the best, Philip -- 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] What is declarative_base() exactly?
declarative_base is just a function that returns a class. In python, a class is a first class object just like any other. You can do things like this: class MyClass(object): pass def foo() return MyClass my_class_instance = foo()() In normal use of SQLAlchemy you don't need to think too hard about what actually goes on inside declarative_base; it's part of the magical alchemy that takes a class full of Column() objects and lets you build queries and look at data in instances of your mapped classes. For more information on that sort of design pattern, punch 'python metaclass' into your favourite search engine and allow your mind to be boggled. - Phil On Mon, Sep 23, 2013 at 2:22 PM, Edward Kim onward.ed...@gmail.com wrote: Hi all, I have a really short experience of python so it can be really stupid question. I tried to understanding about declarative_base(). Example below: Base = declarative_base() class Bus(Base): __tablename__ = 'bus' In my understanding, That python code look like function or class. So it will be return some value or instance. In SQLAlchemy, declarative_base() return something and then, Bus class inherit that Base. I saw the code in SQLAlchemy, But I can't understand what exactly supposed to be. Base = declarative_base() Base class 'sqlalchemy.ext.declarative.api.Base' How this function is return class, not instance? Is it kind of design pattern? I know It is not a big deal for just using SQLAlchemy, but I can't explain what it is and how can return the class. Please let me know what I need to know about this pattern or style. Thanks, Edward. -- 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.
Re: [sqlalchemy] another postgresql distinct on question
I went though the exact same process of discovery that you did Jonathan :)
It does work perfectly but does not get rendered properly when printing out
the queries (possibly even when I set echo=True on the connection, if I
remember correctly)
On Sun, Sep 22, 2013 at 5:44 PM, Jonathan Vanasco jonat...@findmeon.comwrote:
ah ha! yes!
that is right.
query = select( (these,columns,) ).distinct( this.column )
this was an even tricker problem... and I might have been executing
correct queries last night without realizing it.
i just noticed that i was getting a correct query in my database, while I
was seeing the wrong query on screen.
sqlalchemy didn't know that that 'core' commands I was using were for
postgresql, so it rendered the query not using that dialect.
when i actually did query the database, it was compiling with the right
dialect :
_query_EXT = dbSession.query( model.Table.data )
_query_INT = dbSession.query( model.Table.data )
_slurped = sqlalchemy.union( _query_EXT , _query_INT )
_slurped = sqlalchemy.sql.expression.alias( _slurped , name='slurped')
_deduped = sqlalchemy.select(\
(\
_slurped.c.object_id.label('object_id') ,
_slurped.c.event_timestamp.label('event_timestamp')
),
)\
.distinct( _slurped.c.object_id )\
.order_by(\
_slurped.c.object_id.desc() ,
_slurped.c.event_timestamp.desc()
)
_deduped = sqlalchemy.sql.expression.alias( _deduped , name='deduped')
_culled = sqlalchemy.select( (_deduped.c.object_id,) )\
.order_by(\
_deduped.c.event_timestamp.desc()
)
_query = _culled
# this executes a DISTINCT ON ( slurped.object_id ) slurped.object_id
, event_timestamp
yay = dbSession.execute( _query )
# this renders a DISTINCT slurped.object_id , event_timestamp
nay = str( _query )
--
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.
Re: [sqlalchemy] Mutable column_properties
Yes, obviously :) But I meant in general for any python type - native postgresql type; I guess there are not that many really I could just handle all the cases I want to use.. On Fri, Sep 20, 2013 at 5:05 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Sep 20, 2013, at 10:35 AM, Philip Scott safetyfirstp...@gmail.com wrote: Without actually querying the DB or enumerating the types and their conversions myself which seems a bit naff; psycopg2.extensions.adapt almost does it but not quite (e.g. you get '2013-09-10'::date when CAST() gives you 2013-09-10). Any ideas? It's not vital, I don't do much up dating of these guys really so I have it doing a supplemental SQL query like the one above for every update/insert :) date to string without SQL accesshow about strftime() ? http://docs.python.org/2/library/datetime.html#strftime-strptime-behavior -- 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] Mutable column_properties
| Philip - does this help you at all ? I used the ORM to access the connection and table data in the underlying engine. Thank you for your help guys, it was very helpful! HSTORE updating does work nicely; I've ended up using events as Michael suggested, except that I have hooked into the insert/update events instead of the validation. Since my values are often not strings on the python side I have to CAST() them to strings before putting them in the hstore as values. And it is very nice for them to be strings that postgres itself can cast back to types like dates/times etc.. so I can filter nicely server-side (another great side effect of using column_property) I spent many hours trying to work out how to get sqlalchemy/psycopg2 to do the equivalent of: session.execute(select([cast(my_data, String)])).scalar() Without actually querying the DB or enumerating the types and their conversions myself which seems a bit naff; psycopg2.extensions.adapt almost does it but not quite (e.g. you get '2013-09-10'::date when CAST() gives you 2013-09-10). Any ideas? It's not vital, I don't do much up dating of these guys really so I have it doing a supplemental SQL query like the one above for every update/insert :) I have actually taken it one step further, and allowed for JSON encoded data to be a value type in the hstore too, and with a modified version of the Mutable extension everything actually works as you would expect. As in, I have a mapped mutable attribute on my class which maps to an arbitary JSON object whose text resides in the VALUE of a postgres HSTORE. I had to make some modifications to ext.Mutable so that it supports the notion of something *optionally* being a mutable type. E.g. when I decode my JSON string, I don't know whether or not it is going to be a dictionary or an integer. If anyone is interested I can post the code, it is based on this idea: https://gist.github.com/dbarnett/1730610 Thanks again; and keep up the great work - I love SQLAlchemy! Philip -- 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] load events with polymorphic_on
Hello again dear list,
I have a mapped class is polymorphic with single table inheritance. I have
a function wired up to the load() SA event. The dictionary in the
InstanceState which is passed to the load event depends on whether I am
querying on the base class or a derived one, which I don't _think_ it
should be.
Here is a concrete example, which is a bit contrived - I am actually knee
deep inside mapping mutable JSON objects stored in values of HSTOREs; this
is a distilled version of the problem:
class PolyBase(Base):
name= Column(String, primary_key=True)
type= Column(String)
__mapper_args__ = {'polymorphic_on': type}
class Cake(PolyBase):
topping = Column(String)
__mapper_args__ = {'polymorphic_identity': 'Cake'}
def load_event(state, *args):
print state.dict.keys()
sqlalchemy.event.listen(Cake, 'load', load_event, raw=True, propagate=True)
Now:
session().query(Cake).filter_by(name='mycake').one()
['date', '_sa_instance_state', 'type', 'name']
However, in a fresh session:
session().query(PolyBase).filter_by(name='mycake').one()
['_sa_instance_state', 'type', 'name']
See 'date' is missing when I query on PolyBase, even though in both cases
state.obj is a Cake instance. I've been looking at the object loading code
in SQLAlchemy and it is rather fierce; is this expected behavior?
Either way can anyone think of a fix/workaround?
Cheers for reading this far!
- Philip
--
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] Mutable column_properties
Thanks Michael a column_property() against a SQL expression by definition is not writable. Your table doesn't have a CAST trigger inside of it for when an int is written to it that would convert it back to a string. in this case since data is already loaded fully as a single column you might as well just use a hybrid. I see what you mean about column_property not being writable. In my actual application I won't be loading the whole 'data'; what I am really trying to do is make a sort of psuedo-column so that the SQL that gets generated is something like SELECT id, data-foo AS foo FROM thing And 'foo' gets mapped as if it were a normal column so it's update-able too. I can deal with the casting on top of that I think. Does that make any sense? Perhaps I am trying to be too much of an Alchemist for my own good here :) All the best, Philip -- 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] Mutable column_properties
Hi Folks, So we have a sort of generic table; let's call it 'Thing'. For the sake of example, let it have two columns. An integer 'id', and a hstore 'data': from sqlalchemy.dialects.postgresql import HSTORE from Column, Integer class Thing(Base): __tablename__ = 'thing' id = Column(Integer, primary_key=True) data= Column(MutableDict.as_mutable(HSTORE)) Now what I want to do is have some more mapped attributes that peek inside the HSTORE. I am assuming I know something about the HSTORE structure when I add this to the class definition: foo = column_property(expression.cast(data[foo], Integer)) And this works pretty well; when I get an instance of Thing, I can ask it for it's 'foo' and I get an integer back. However it doesn't work if I *assign* to foo. The session notices the object is dirty but doesn't know how to write back my changes. In an earlier attempt at this I just made 'foo' a hybrid property which wrote and read directly into the data 'hstore', doing the casting at each read/write. This was OK for integers and strings but what I really want to say is something like: bar = column_property(expression.cast(data[bar], *JSONEncoded*)) And be able to do: mything.bar.append(lalala) (Here I am assuming that I had a HSTORE with a key 'bar' with a value that was a JSON encoded list) (JSONEncoded is a custom mutable type similar to examples in the docs). So I'd like to be able to use mutable types; not just reassignment. Perhaps I need some custom types and a reimplementation of column_expression to fetch/write to the HSTORE but I am getting a bit lost! Anyone tried anything like this before? All the best, Philip -- 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] Difficulty using dynamic schemas (ENUM is schema-stubborn). Patch?
Hi Micheal,
I tried the before_parent_attach event but it passes the column as the
parent, and column.table is still None at that point. Also I'm not sure if
that would work since events can be replicated to copies of objects but the
event handlers themselves will still refer to the original objects, so I
would need to attach the event to each new ENUM instance.
The before_parent_attach event works if you register it on the column:
for table in metadata.tables.values():
for _, column in dict(table.columns).items():
if isinstance(column.type, postgresql.ENUM):
event.listens_for(column, 'before_parent_attach',
propagate=True)(propagate_schema)
but you still _have_ to copy the type in Column._construct otherwise the
schemaless instance of the type binds to the before_create event of the new
table.
Regards
On Sunday, 27 January 2013 23:10:20 UTC, Michael Bayer wrote:
On Jan 27, 2013, at 4:42 PM, Scott Sadler wrote:
Hi, I wanted to post here since I had to hack around an issue in order
to use SQLAlchemy with a dynamic Postgres schema, and I wanted to see if
there's a way I can get the workarounds out of my code.
In my app I'm separating the data by dynamically managing the schemas.
The basic way this is achieved is:
new_meta = MetaData(schema='newschema')
for table in old_meta.tables.items():
table.tometadata(new_meta)
new_meta.create_all()
This worked nicely until I tried to use postgresql.ENUM type. The ENUM
is a first class schema object so it needs to be created separately, but it
does this by registering an event on the first table it's bound to (doesnt
transfer to copies) and it doesn't inherit the schema of the table it's
bound to
the ENUM type accepts an argument schema for this purpose, which is part
of the base sqlalchemy.types.Enum contract:
Table('mytable', metadata, Column('data', postgresql.ENUM(name='myenum',
schema='someschema')), schema='someschema')
Whether that schema should automatically be copied from the parent table,
I'm not sure. It would likely be confusing to change it now, though.
You can affect this result yourself without any subclassing, using the
after_parent_attach event:
my_enum = ENUM(...)
@event.listens_for(my_enum, after_parent_attach)
def associate_schema(target, parent):
target.schema = parent.schema
http://docs.sqlalchemy.org/en/rel_0_8/core/events.html?highlight=after_parent_attach#sqlalchemy.events.DDLEvents.after_parent_attach
Enum also has a create() method of its own, so that you can emit the
create any time:
mytable.c.data.type.create(engine, checkfirst=True)
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html?highlight=enum#sqlalchemy.types.Enum.create
as for tometadata() not creating a copy of the type, I can see that being
an issue. The fact that PG has these types created separately implies the
use case of the same type being used for multiple tables, so it's not clear
if tometadata(schema) should unconditionally suggest copying the enum type
out to that schema as well. Both behaviors could potentially be indicated
by adding a new flag to types.Enum called inherit_table_schema,
indicating the Enum should attach itself to any table and adopt its schema,
and also be copied during a tometadata() operation with the new schema.
--
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.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Difficulty using dynamic schemas (ENUM is schema-stubborn). Patch?
Hi, I wanted to post here since I had to hack around an issue in order to use SQLAlchemy with a dynamic Postgres schema, and I wanted to see if there's a way I can get the workarounds out of my code. In my app I'm separating the data by dynamically managing the schemas. The basic way this is achieved is: new_meta = MetaData(schema='newschema') for table in old_meta.tables.items(): table.tometadata(new_meta) new_meta.create_all() This worked nicely until I tried to use postgresql.ENUM type. The ENUM is a first class schema object so it needs to be created separately, but it does this by registering an event on the first table it's bound to (doesnt transfer to copies) and it doesn't inherit the schema of the table it's bound to (in fact, I'm also working around this behaviour in order to apply DDL's to each schema). Potentially this is correct but in my case I don't want dependencies between schemas so I had to subclass Column and postgresql.ENUM to copy the type and assign the schema at the correct time (code below). I'd very much like to not have these hacks/workarounds in my program, is there a cleaner way to do this or would the developers be open to a patch? I would suggest an argument to the ENUM type to indicate that it inherits the schema from the table, or perhaps allowing the registered events to be transferred somehow. Code for the workaround I implemented: class Column(sqlalchemy.Column): def _constructor(self, *args, **kwargs): # Copy type so we can mutate it safely without impacting other schemas kwargs['type_'] = kwargs['type_'].adapt(type(kwargs['type_'])) return super(Column, self)._constructor(*args, **kwargs) class ENUM(postgresql.ENUM): def _set_table(self, column, table): # Inherit the schema of the table self.schema = table.schema return super(ENUM, self)._set_table(column, table) If we can agree on something I'm willing to write a patch. Regards -- 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. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] generalized polymorphic mixin
Is it possible to make a generalized declarative mixin class that
abstracts away all of the syntax of inheritance? I've seen examples
that set up the __mapper_args__ but not the discriminator column, and
examples that set up the discriminator column but not the
__mapper_args__, but none with both.
This is roughly how I imagine it should work, but when I tried this,
rows were created with null values for the discriminator. A full
example is here:
https://gist.github.com/797893
class PolymorphicMixin(object):
@declared_attr
def discriminator(cls):
if Base in cls.__bases__:
return Column('discriminator', types.String(50))
for b in cls.__bases__:
if hasattr(b, 'discriminator'):
return b.discriminator
@declared_attr
def __mapper_args__(cls):
ret = {'polymorphic_identity': cls.__name__}
if Base in cls.__bases__:
ret['polymorphic_on'] = PolymorphicMixin.discriminator
return ret
Thanks,
Scott
--
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] Reflection fails on binary columns in sqlite
I'm trying to reflect a sqlite database with the hg tip, and it seems
to fail whenever a BINARY column is present in the database. The same
error happens whether reflecting the entire database at once (using
metadata.reflect()) or just reflecting the specific table. A reduction
with a traceback is below.
I don't really know enough about the types system to know if this is
something I'm doing wrong or something sqlalchemy is doing wrong, but
it seems to have been introduced in r7154:
http://hg.sqlalchemy.org/sqlalchemy/rev/e409e3adb99f
Thanks!
Scott
$ sqlite3 foo.db
SQLite version 3.6.12
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite CREATE TABLE foo (bar BINARY(16));
sqlite .exit
$ python
Python 2.6.1 (r261:67515, Jun 24 2010, 21:47:49)
[GCC 4.2.1 (Apple Inc. build 5646)] on darwin
Type help, copyright, credits or license for more information.
from sqlalchemy import create_engine, MetaData
engine = create_engine('sqlite:///foo.db')
meta = MetaData()
meta.reflect(bind=engine)
.../sqlalchemy/lib/sqlalchemy/engine/reflection.py:46: SAWarning: Did
not recognize type 'BINARY' of column 'bar'
ret = fn(self, con, *args, **kw)
Traceback (most recent call last):
File stdin, line 1, in module
File .../sqlalchemy/lib/sqlalchemy/schema.py, line 2094, in
reflect
Table(name, self, **reflect_opts)
File .../sqlalchemy/lib/sqlalchemy/schema.py, line 212, in __new__
table._init(name, metadata, *args, **kw)
File .../sqlalchemy/lib/sqlalchemy/schema.py, line 259, in _init
include_columns=include_columns)
File .../sqlalchemy/lib/sqlalchemy/engine/base.py, line 1947, in
reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File .../sqlalchemy/lib/sqlalchemy/engine/default.py, line 247, in
reflecttable
return insp.reflecttable(table, include_columns)
File .../sqlalchemy/lib/sqlalchemy/engine/reflection.py, line 382,
in reflecttable
for col_d in self.get_columns(table_name, schema, **tblkw):
File .../sqlalchemy/lib/sqlalchemy/engine/reflection.py, line 229,
in get_columns
**kw)
File string, line 1, in lambda
File .../sqlalchemy/lib/sqlalchemy/engine/reflection.py, line 46,
in cache
ret = fn(self, con, *args, **kw)
File .../sqlalchemy/lib/sqlalchemy/dialects/sqlite/base.py, line
534, in get_columns
coltype = coltype(*[int(a) for a in args])
TypeError: __init__() takes exactly 1 argument (2 given)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: filtering with an association_proxy
Great, thanks, I found a ticket that was already open for this and
added to it.
http://www.sqlalchemy.org/trac/ticket/1372
On Nov 17, 7:23 am, Michael Bayer mike...@zzzcomputing.com wrote:
scott wrote:
Is there a way to filter a query involving an association_proxy?
For example, say I have a one to many relation between Pages and Tags,
and an association_proxy like this to let me represent tags as a list
of strings.
tag_objects = orm.relation('Tag')
tags = association_proxy('tag_objects', 'name')
Now I want to find all the pages tagged with 'foo'. As far as I know I
have to break the abstraction barrier provided by the
association_proxy and do something like:
sess.query(Page.tag_objects.any(name='foo'))
Is there any mechanism for doing something like this instead?
sess.query(Page.tags.any('foo'))
If there's nothing similar already existing, is this functionality
desirable? It seems like it could be really useful for clarifying
complex filtering, especially involving many to many relations with
association objects. I wrote an example patch implementing this
for .any() and .has(), with tests. I'm happy to post a ticket and
flesh it out more if it seems reasonable.
http://web.mit.edu/storborg/Public/better-associationproxy-filtering
we absolutely would want associationproxy to provide the standard
comparison functions for free - right now its a do-it-yourself thing. If
you want to work on that that would be great !
Thanks,
Scott
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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=.
[sqlalchemy] filtering with an association_proxy
Is there a way to filter a query involving an association_proxy?
For example, say I have a one to many relation between Pages and Tags,
and an association_proxy like this to let me represent tags as a list
of strings.
tag_objects = orm.relation('Tag')
tags = association_proxy('tag_objects', 'name')
Now I want to find all the pages tagged with 'foo'. As far as I know I
have to break the abstraction barrier provided by the
association_proxy and do something like:
sess.query(Page.tag_objects.any(name='foo'))
Is there any mechanism for doing something like this instead?
sess.query(Page.tags.any('foo'))
If there's nothing similar already existing, is this functionality
desirable? It seems like it could be really useful for clarifying
complex filtering, especially involving many to many relations with
association objects. I wrote an example patch implementing this
for .any() and .has(), with tests. I'm happy to post a ticket and
flesh it out more if it seems reasonable.
http://web.mit.edu/storborg/Public/better-associationproxy-filtering.patch
Thanks,
Scott
--~--~-~--~~~---~--~~
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] filtering/ordering with composite columns
Sorry, for the stupid question, but I'm out of coffee. When using a composite column type, how can I use the individual columns for filtering and ordering operations? Based off the example from the ORM docs: http://gist.github.com/129457 What if I want to order by the x values of the composite column 'start'? My instinct would be something like session.query(Vertex).order_by(Vertex.start.x) But Vertex.start.x doesn't exist. Is there a clean way to do this? One possible workaround is to just get the column object directly from vertices.c., but that seems non-ideal, since you don't really know what the underlying column name is going to be for each instance of a composite column type. session.query(Vertex).order_by(vertices.c.x1) Thanks, Scott --~--~-~--~~~---~--~~ 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] deferred columns can't be added to declarative class after creation?
Hi-
I'm trying to add a deferred column to a declarative class after the
class has been created (but before tables are created, obviously).
This works fine with none-deferred columns, as documented on
declarative.py:48, but deferred columns added in this way don't get
added to the table definition in SQL.
Here's how I'm adding the attribute:
Bar.deferme = deferred(Column('deferme', String(30)))
I've also tried without the column name--that fails in a different
way, but still doesn't add the column definition.
A full example demonstrating this is below. Is there any workaround
for this?
Thanks!
Scott
example of (bug?) in adding deferred columns to a declarative class
after the
class's creation.
Foo is a normal declarative class with a deferred column. Bar is the
same class,
but with the deferred column added afterwards.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
metadata = MetaData('sqlite://')
metadata.bind.echo = True
Base = declarative_base(metadata=metadata)
class Foo(Base):
__tablename__ = 'foos'
id = Column(Integer(unsigned=True), primary_key=True)
text = Column(String(20))
deferme = deferred(Column(String(30)))
def __init__(self, text, deferme):
self.text = text
self.deferme = deferme
def __repr__(self):
return Foo: id %d, text %s % (self.id, self.text)
class Bar(Base):
__tablename__ = 'bars'
id = Column(Integer(unsigned=True), primary_key=True)
text = Column(String(20))
def __init__(self, text, deferme):
self.text = text
self.deferme = deferme
def __repr__(self):
return Bar: id %d, text %s % (self.id, self.text)
Bar.deferme = deferred(Column('deferme', String(30)))
# This doesn't work either:
# Bar.deferme = deferred(Column(String(30)))
# populate
metadata.create_all()
sess = create_session()
foo1 = Foo('sqlalchemy', 'rocks')
foo2 = Foo('but', 'I')
bar1 = Bar('want', 'better')
bar2 = Bar('deferred', 'columns')
sess.add(foo1)
sess.add(foo2)
sess.add(bar1)
sess.add(bar2)
sess.flush()
sess.clear()
# query objects, get their addresses
print all foos
for f in sess.query(Foo).all():
print f.text, f.deferme
print all bars
for b in sess.query(Bar).all():
print b.text, b.deferme
--~--~-~--~~~---~--~~
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] Re: Using order_by in an association many-to-many relationship with columns from the association object
When I do this...
cpt_codes = ManyToMany(
...
order_by = procedure_cpt_codes.c.cpt_codes_idx
)
# procedure_cpt_codes is the JOIN table in between the parent
(procedure) --- children (cpt_codes)
# procedure_cpt_codes has 3 columns...procedure_id (foreign key),
cpt_code_id (foreign key) and cpt_codes_idx that's sorted
...I get the following error:
TypeError: 'Column' object is not iterable
I had tried passing order_by several configurations including the
column object as you suggested to no avail. It seemed when I looked
through the documentation and source (which was a few days ago now so
my memory may be fuzzy) for ManyToMany order_by was expecting a string
that was the name of a column on the secondary table (CptCode in my
example).
I've since started to try to shoe horn the relationship with an
association object representing the join table which seemed the only
way to access a non-foreign key column on the join table to order the
collection by. If there's a better way to do this with the order_by
parameter I'd love to figure it out b/c association object syntax with
ORM gets REALLY messy.
Thanks, Scott
On Mar 10, 12:03 pm, Michael Bayer mike...@zzzcomputing.com wrote:
order_by accepts a Column object, i.e. table.c.whatever, so pass that in.
Scott wrote:
Is there a way with the current iteration of SQLAlchemy to add a
column to the association table in a many-to-many relationship with
that column used to order the join? I looked at the order_by attribute
of the ManyToMany() relationship definition, but it seems that this is
expecting a string naming the column in the related entity. I'm using
Elixir on top of alchemy, but here are my relevant class and table
definitions:
procedure_cpt_codes = Table('procedure_cpt_codes', metadata,
autoload=True)
class CptCode(Entity):
using_options(tablename='cpt_codes', autosetup=True)
name = Field(Unicode)
code = Field(Unicode)
description= Field(Unicode)
class Procedure(Entity):
using_options(tablename='procedures', autosetup=True)
complications = OneToMany('Complication')
cpt_codes = ManyToMany(
'CptCode',
table = procedure_cpt_codes, lazy=False,
foreign_keys = lambda: [ procedure_cpt_codes.c.procedure_id,
procedure_cpt_codes.c.cpt_code_id ],
primaryjoin = lambda: Procedure.id ==
procedure_cpt_codes.c.procedure_id,
secondaryjoin = lambda: CptCode.id ==
procedure_cpt_codes.c.cpt_code_id,
order_by = procedure_cpt_codes.c.cpt_codes_idx
)
procedure_date = Field(Date)
I get the following exception when run as listed:
Traceback (most recent call last):
File /System/Library/Frameworks/Python.framework/Versions/Current/
Extras/lib/python/PyObjC/PyObjCTools/AppHelper.py, line 235, in
runEventLoop
main(argv)
File /Users/tswall/Documents/workspace/Cocoa/python/Epdb/build/
Debug/Epdb.app/Contents/Resources/MyController.py, line 15, in
buttonPushed_
for instance in Patient.query.all():
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/entity.py, line 641, in __get__
elixir.setup_all()
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/__init__.py, line 145, in setup_all
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/entity.py, line 816, in setup_entities
method()
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/entity.py, line 421, in setup_properties
self.call_builders('create_properties')
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/entity.py, line 433, in call_builders
getattr(builder, what)()
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/relationships.py, line 417, in create_properties
self.target._descriptor.translate_order_by(kwargs['order_by'])
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/entity.py, line 322, in translate_order_by
for colname in order_by:
TypeError: 'Column' object is not iterable
When I change the order_by above to
order_by = 'procedure_cpt_codes.c.cpt_codes_idx' #or 'cpt_codes_idx'
I get an error that it can't find column 'cpt_codes_idx' on relation
table 'CptCode'.
Any advice would be appreciated!
Scott
--~--~-~--~~~---~--~~
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] Using order_by in an association many-to-many relationship with columns from the association object
Is there a way with the current iteration of SQLAlchemy to add a
column to the association table in a many-to-many relationship with
that column used to order the join? I looked at the order_by attribute
of the ManyToMany() relationship definition, but it seems that this is
expecting a string naming the column in the related entity. I'm using
Elixir on top of alchemy, but here are my relevant class and table
definitions:
procedure_cpt_codes = Table('procedure_cpt_codes', metadata,
autoload=True)
class CptCode(Entity):
using_options(tablename='cpt_codes', autosetup=True)
name = Field(Unicode)
code = Field(Unicode)
description= Field(Unicode)
class Procedure(Entity):
using_options(tablename='procedures', autosetup=True)
complications = OneToMany('Complication')
cpt_codes = ManyToMany(
'CptCode',
table = procedure_cpt_codes, lazy=False,
foreign_keys = lambda: [ procedure_cpt_codes.c.procedure_id,
procedure_cpt_codes.c.cpt_code_id ],
primaryjoin = lambda: Procedure.id ==
procedure_cpt_codes.c.procedure_id,
secondaryjoin = lambda: CptCode.id ==
procedure_cpt_codes.c.cpt_code_id,
order_by = procedure_cpt_codes.c.cpt_codes_idx
)
procedure_date = Field(Date)
I get the following exception when run as listed:
Traceback (most recent call last):
File /System/Library/Frameworks/Python.framework/Versions/Current/
Extras/lib/python/PyObjC/PyObjCTools/AppHelper.py, line 235, in
runEventLoop
main(argv)
File /Users/tswall/Documents/workspace/Cocoa/python/Epdb/build/
Debug/Epdb.app/Contents/Resources/MyController.py, line 15, in
buttonPushed_
for instance in Patient.query.all():
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/entity.py, line 641, in __get__
elixir.setup_all()
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/__init__.py, line 145, in setup_all
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/entity.py, line 816, in setup_entities
method()
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/entity.py, line 421, in setup_properties
self.call_builders('create_properties')
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/entity.py, line 433, in call_builders
getattr(builder, what)()
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/relationships.py, line 417, in create_properties
self.target._descriptor.translate_order_by(kwargs['order_by'])
File /Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
elixir/entity.py, line 322, in translate_order_by
for colname in order_by:
TypeError: 'Column' object is not iterable
When I change the order_by above to
order_by = 'procedure_cpt_codes.c.cpt_codes_idx' #or 'cpt_codes_idx'
I get an error that it can't find column 'cpt_codes_idx' on relation
table 'CptCode'.
Any advice would be appreciated!
Scott
--~--~-~--~~~---~--~~
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] SqlSoup many-to-many relation causes insert into association table?
I am using SqlSoup to read from an MS SQL database and I'm having some
issues building a many-to-many relation on the mapping. The database
doesn't have any foreign keys, so I'm manually specifying the join
conditions and keys.
Specifying the relation returns without exceptions, but as soon as I
query the primary object, even with something as simple as .first(),
SQLAlchemy tries to insert a row into the association table. This
seems like incorrect behavior.
db = SqlSoup(url)
# A Product has many Categories, via the ProductCategory association
table.
db.Products.relate('categories', db.Categories,
secondary=db.ProductCategory,
primaryjoin=db.Products.c.ManProdCode ==
db.ProductCategory.c.ManProdCode,
secondaryjoin=db.ProductCategory.c.Category1 ==
db.Categories.c.CatCode,
foreign_keys=[db.ProductCategory.c.ManProdCode,
db.ProductCategory.c.Category1])
db.Products.first() # Fails!
I am using SQLAlchemy 0.5.2 with pymssql 0.8.0 connecting through
FreeTDS 1.12 to MS SQL Server 9.0.2047. I also checked it with the svn
trunk (r5823) and the behavior is the same.
The association table is nothing special, basically:
(
ManProdCode CHAR(5) NOT NULL,
Category1 CHAR(2),
Category2 CHAR(2)
)
The user I'm connecting with does not have INSERT privileges, so a
DatabaseError is raised when db.Products.first() is called and SA
tries to do the INSERT. Any idea why SA is trying to do this?
Thanks!
Scott
Traceback (most recent call last):
File stdin, line 1, in module
File sqlalchemy/orm/query.py, line 1219, in first
ret = list(self[0:1])
File sqlalchemy/orm/query.py, line 1140, in __getitem__
return list(res)
File sqlalchemy/orm/query.py, line 1279, in __iter__
self.session._autoflush()
File sqlalchemy/orm/session.py, line 902, in _autoflush
self.flush()
File sqlalchemy/orm/session.py, line 1347, in flush
self._flush(objects)
File sqlalchemy/orm/session.py, line 1417, in _flush
flush_context.execute()
File sqlalchemy/orm/unitofwork.py, line 244, in execute
UOWExecutor().execute(self, tasks)
File sqlalchemy/orm/unitofwork.py, line 707, in execute
self.execute_save_steps(trans, task)
File sqlalchemy/orm/unitofwork.py, line 722, in execute_save_steps
self.save_objects(trans, task)
File sqlalchemy/orm/unitofwork.py, line 713, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File sqlalchemy/orm/mapper.py, line 1352, in _save_obj
c = connection.execute(statement.values(value_params), params)
File sqlalchemy/engine/base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File sqlalchemy/engine/base.py, line 874, in
_execute_clauseelement
return self.__execute_context(context)
File sqlalchemy/engine/base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File sqlalchemy/engine/base.py, line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File sqlalchemy/engine/base.py, line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.DatabaseError: (DatabaseError) internal error: SQL
Server message 229, severity 14, state 5, line 1:
INSERT permission denied on object 'ProductCategory', database
'SomeDB', schema 'dbo'.
DB-Lib error message 229, severity 14:
General SQL Server error: Check messages from the SQL Server
'INSERT INTO [ProductCategory] ([Category1], [Category2]) VALUES (%
(Category1)s, %(Category2)s)' {'Category1': None, 'Category2': None}
--~--~-~--~~~---~--~~
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] Re: SqlSoup many-to-many relation causes insert into association table?
Sorry for the trouble, after much stepping through source I figured it
out.
`secondary` needs to be a Table object, not a SqlSoup Entity. This
fixes it:
db.Products.relate('categories', db.Categories,
secondary=db.ProductCategory._table, _table is your friend!
primaryjoin=db.Products.c.ManProdCode ==
db.ProductCategory.c.ManProdCode,
secondaryjoin=db.ProductCategory.c.Category1 ==
db.Categories.c.CatCode,
foreign_keys=[db.ProductCategory.c.ManProdCode,
db.ProductCategory.c.Category1])
Cheers,
Scott
On Mar 6, 2:49 pm, Scott Torborg storb...@gmail.com wrote:
I am using SqlSoup to read from an MS SQL database and I'm having some
issues building a many-to-many relation on the mapping. The database
doesn't have any foreign keys, so I'm manually specifying the join
conditions and keys.
Specifying the relation returns without exceptions, but as soon as I
query the primary object, even with something as simple as .first(),
SQLAlchemy tries to insert a row into the association table. This
seems like incorrect behavior.
db = SqlSoup(url)
# A Product has many Categories, via the ProductCategory association
table.
db.Products.relate('categories', db.Categories,
secondary=db.ProductCategory,
primaryjoin=db.Products.c.ManProdCode ==
db.ProductCategory.c.ManProdCode,
secondaryjoin=db.ProductCategory.c.Category1 ==
db.Categories.c.CatCode,
foreign_keys=[db.ProductCategory.c.ManProdCode,
db.ProductCategory.c.Category1])
db.Products.first() # Fails!
I am using SQLAlchemy 0.5.2 with pymssql 0.8.0 connecting through
FreeTDS 1.12 to MS SQL Server 9.0.2047. I also checked it with the svn
trunk (r5823) and the behavior is the same.
The association table is nothing special, basically:
(
ManProdCode CHAR(5) NOT NULL,
Category1 CHAR(2),
Category2 CHAR(2)
)
The user I'm connecting with does not have INSERT privileges, so a
DatabaseError is raised when db.Products.first() is called and SA
tries to do the INSERT. Any idea why SA is trying to do this?
Thanks!
Scott
Traceback (most recent call last):
File stdin, line 1, in module
File sqlalchemy/orm/query.py, line 1219, in first
ret = list(self[0:1])
File sqlalchemy/orm/query.py, line 1140, in __getitem__
return list(res)
File sqlalchemy/orm/query.py, line 1279, in __iter__
self.session._autoflush()
File sqlalchemy/orm/session.py, line 902, in _autoflush
self.flush()
File sqlalchemy/orm/session.py, line 1347, in flush
self._flush(objects)
File sqlalchemy/orm/session.py, line 1417, in _flush
flush_context.execute()
File sqlalchemy/orm/unitofwork.py, line 244, in execute
UOWExecutor().execute(self, tasks)
File sqlalchemy/orm/unitofwork.py, line 707, in execute
self.execute_save_steps(trans, task)
File sqlalchemy/orm/unitofwork.py, line 722, in execute_save_steps
self.save_objects(trans, task)
File sqlalchemy/orm/unitofwork.py, line 713, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File sqlalchemy/orm/mapper.py, line 1352, in _save_obj
c = connection.execute(statement.values(value_params), params)
File sqlalchemy/engine/base.py, line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File sqlalchemy/engine/base.py, line 874, in
_execute_clauseelement
return self.__execute_context(context)
File sqlalchemy/engine/base.py, line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File sqlalchemy/engine/base.py, line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File sqlalchemy/engine/base.py, line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.DatabaseError: (DatabaseError) internal error: SQL
Server message 229, severity 14, state 5, line 1:
INSERT permission denied on object 'ProductCategory', database
'SomeDB', schema 'dbo'.
DB-Lib error message 229, severity 14:
General SQL Server error: Check messages from the SQL Server
'INSERT INTO [ProductCategory] ([Category1], [Category2]) VALUES (%
(Category1)s, %(Category2)s)' {'Category1': None, 'Category2': None}
--~--~-~--~~~---~--~~
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] Re: Confused about relations
u.Xs is going to have exactly those X's which have u's id as their user_id attribute. the X which you appended to u.Ys[0] is from a different relationship. so yeah you have to set the user_id attribute on the X, which is entirely legal. However the legit way to do it is to just add the X to u's Xs collection and have the ORM take care of the user_id attribute for you. just like: x = X() u.Ys[0].Xs.append(x) u.Xs.append(x) Ah, I see, thanks. I hadn't thought of doing it that way since it sort of looks like it'd be inserting it twice. But, makes sense now that I see it. no you dont need to do all that. even if you are just setting user_id as you are now, just expire the attribute: session.expire(u, ['Xs']), and it will reload when you touch it again. but if you think in terms of collections instead of foreign keys like above, then you dont even need that, it would just all work out. Thanks again, I should have rtfm'd better for this one. scott --~--~-~--~~~---~--~~ 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] Confused about relations
Hi
(I apologize in advance if this is a silly question, but I'm having
some trouble figuring out why this isn't working as expected.)
I have 3 tables: Users, Xs, and Ys. Each has an id, and relevant data.
Each user can have a bunch of Xs as well as a bunch of Ys, for which I
have a ForeignKey(users.id) in Xs and Ys. In addition, each Y can
have 0 or more Xs. For this, I made an auxiliary table X_Ys containing
X and Y keys.
Tables:
usersTable = Table('users', metadata,
Column('id', Integer, primary_key=True),
...
)
ysTable = Table('ys', metadata,
Column('id', Integer, primary_key=True),
...
Column('user_id', Integer, ForeignKey('users.id'), nullable=False)
)
X_Ys = Table('x_ys', metadata,
Column('y_id', Integer, ForeignKey('ys.id'),
nullable=False, primary_key=True),
Column('x_id', Integer, ForeignKey('xs.id'),
nullable=False, primary_key=True))
XsTable = Table('xs', metadata,
Column('id', Integer, primary_key=True),
...
Column('user_id', Integer, ForeignKey('users.id'), nullable=False)
)
Mappers are:
mapper(User, usersTable, properties = {
'Ys': relation(Y, backref='user'),
'Xs': relation(X, backref='user'),
})
mapper(X, xsTable, properties = {
'Ys': relation(Y, secondary = X_Ys)
})
mapper(Y, ysTable, properties={
'Xs': relation(X, secondary = X_Ys)
})
then:
u = User(...)
u.Ys.append(Y(...))
u.Ys[0].Xs.append(X(...))
u.Ys
[Y(...)]
u.Ys[0].Xs
[X(...)]
u.Xs
[]
I was expecting/hoping that u.Xs would have the X that was appended to
u.Ys[0]. I can sort of make it work as long as I make the id columns
in X_Ys non-nullable. In that case, session.commit() throws an
IntegrityError if I don't manually set the user_id in any X objects to
a user id (Y objects get their user id ok). However, when I do that:
u.Ys[0].Xs[0].user_id = u.id
u.commit()
I seem to have to make a new session before the changes show up in
the user. ie. I must do:
sess.close()
sess = Session()
u = s.query(User).first()
before u.Xs has the element I'd expect. It does, however, seem to be
ok at that point.
Is there something dumb that I've set up wrong that someone could
point me at? Or is this just a gotcha to be avoided?
thanks,
scott
--~--~-~--~~~---~--~~
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] Two MSSQL databases on one engine?
Hi Alchemists, Do have to define two engines to access two databases on the same database server? I am using MSSQL and pylons with the following: sqlalchemy.default.url = mssql://login:[EMAIL PROTECTED]:1272/database1 I would like to autoload a table that is on that same MSSQL server, but in database2 not database1. Is there a way to do this without defining another engine? It looks like Table will take a schema in the table name argument but not another database name. Thank you, Scott --~--~-~--~~~---~--~~ 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] MSSQL connection url format?
Hi Alchemists,
I am starting a project in Pylons that will need to access a MSSQL
database. I plan to develop on Windows and deploy on Linux. It sounds
like pyodbc is the best option and that it is reasonably well
supported. I've done a lot of Google searching but I haven't been able
to find answers to the following issues.
1. What is the correct format for the connection url?
My guess was the following:
engine = create_engine('mssql://username:[EMAIL PROTECTED]:port/database',
echo=True, module=pyodbc)
but engine.connect() fails.
Should the hostname be the IP of the server or SQL Server's
host\instance combination?
Isn't the port normally 1433?
2. I saw an archived email that said there was better support for
MSSQL in 0.3 than in 0.4. Is that still the case?
3. I'll be piggy backing on an existing ERP system and I'm trying to
decide what would be the best way to store new tables
- in the DB used by the ERP system
- in a new DB on the same MSSQL server
- in a SQLite DB
How easy does SA make it to use data from multiple DBs?
Thank you very much for your help and work on SQLAlchemy,
Scott
--~--~-~--~~~---~--~~
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: alias table names with a self-ref join
On 9/9/07, Michael Bayer [EMAIL PROTECTED] wrote: youre trying to do nested sets. Ya, I'm not up to date on my DB terminology. Im also not familiar with any ORM in any language that supports nested set object-relational mappings. OK. I'll likely implement a tree using the example in the docs then (luckily I have that discretion and am not working with a legacy system). It makes sense in the ORM context. http://groups.google.com/group/sqlalchemy/browse_thread/thread/ 9d61479133ffd6ad/334cb8a0cb5a9de0?#334cb8a0cb5a9de0 Thanks for the link and comments. You've helped me a ton. Scott --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
