Re: [sqlalchemy] Another Parent instance is not bound to a Session; lazy load...
First of all thanks to all of you for your answers and time. Michael let me say that I agree 100% with all you wrote and my will/wish is to work as you wrote, but when you are inside the ORM its easy, other is if you want to interact with the ORM from outside. In my actual system I have more than 500 tables automatically generated from a UML model. Not all of them have a central role but all of them do something in the data model. If I want to fill one of the highest level object its attributes are classes their self with other attributes which are classes again; because I cannot simply send a few primitive parameters to the a function-ORM-aware and make the function create that object I am forced to create locally (in the GUI application) such object and their children (some of them can be NOT NULL so I cannot avoid it) then send the full object to the ORM interface. Apart the great advantages that an ORM offers it should be possible to install it as a service, that is separate it from the any other application: in other words I can have a library of objects (like I have) shared among several GUI/WEB applications and an ORM. The communication between the applications and the ORM should be done serializing and deserializing instances of objects in the shared library. As example you can take the remote interface for an EJB in Java (btw I am thinking about the stateless interface). In this way the ORM does have not to expose to other applicaitons it's session (or EntityManager in EJB) but just an interface. Still in other words the rules that I impose on how I manage my database (that is one or more method using session/transaction), should be independent from the way I display/control them. Again: I would like to use SA as the MODEL of a Model-View-Controller strategy and because of this I can have a myriad of views/controlles but just one separate model application acting as service. Now I am aware that my application is not decoupled as I want (Django initialize the SQL engine) so I cannot avoid SA to inject instruments in new instances but nonetheless I would expect that I may be able to work with objects in two separate universes: the GUI and the ORM. When I wrote that I feel to have missed something I was referring to this: it seems to me that I cannot use SA in a remote-like way, creating an object in the GUI and then send it to the ORM, being sure that because the ORM maps such object it knows how to persist it or eventually retrieve it because it know where to catch the key for each mapped object. For all the rest, thanks for your great software. Maurizio P.S. If you are curious about the system I am working on just take a brief look at it here http://cedadocs.badc.rl.ac.uk/905/ or herehttp://jenkins.badc.rl.ac.uk/cedaManager/cov/1 On Thursday, May 31, 2012 4:50:38 AM UTC+1, Michael Bayer wrote: On May 30, 2012, at 8:53 PM, Claudio Freire wrote: Thing is, in order to work with a large volume of objects, you're forced to do this, otherwise the session can grow uncontrollably. flush periodically, and don't maintain references to things you're done with. The Session does not strongly reference objects that have no pending changes, and they'll be garbage collected. When you separate the operation to work in batches, you almost always have some objects that have a lifespan larger than a single batch, and then a single session. Working in batches is fine. You only need a single Session for all those batches, and a single transaction.If you want several transactions, also fine, call commit() periodically. In none of these cases does the Session need to be closed, and all objects worked with thus far which are still referenced in memory can remain attached to that Session, and you wont have any detachment errors. The problems you're having are from unnecessary detachment of objects, from calling Session.close() and continuing to work with objects that have lost their owning Session, within the context of a new Session they have no association with. Another case in which an object's lifespan can exceed the session's, is when you want to implement caching with objects of your data model - cached values will have come from other sessions than the current one, and things get horribly messy. There are documented patterns for caching - see the example in examples/beaker_caching in the distro. This pattern is designed to cleanly handle the pattern of detached objects becoming re-associated with a particular session at once. The pattern is along the lines of, session is created to work with a field of objects, a set of objects is retrieved from the cache, then re-associated with the cache en-masse using the merge_result() method illustrated in the example. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this
[sqlalchemy] Transplanting _order_by_clause when wrapping into subquery
Hi,
When wrapping a query with an enclosing query to add columns computed from
the original query's columns, I'm blatantly doing this:
order_clause_list = ClauseList(*fact_query._order_by_clause)
fact_query._order_by_clause = ClauseList()
subq = fact_query.alias('forperc')
return select(
[subq.c[k] for k in subq.c.keys()] + [
extra columns based on subq's columns
for dim in percentage_dims
]
).order_by(*order_clause_list)
since I want the original query's ordering, and subquery ordering is not
guaranteed to be maintained.
This works (in 0.7.5). But it messes with internals (_order_by_clause). So
is there a more proper way to do this (for general queries), against the
public sql.expression API?
Regards,
Gulli
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/HlBjhxwBMxQJ.
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] Joinedload and polymorphic problem
Hello everyone, I have a big problem that alone can not solve. There are tens of days I try to figure out how to solve this problem. I created a little test to make you understand better. http://pastebin.com/RGXmJWVj I need to know the value of d.CODE with a single query on ClassA. Is there anyone who can help me? Best regards, Francesco -- 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] Joinedload and polymorphic problem
Hello everyone, I have a big problem that alone can not solve. There are tens of days I try to figure out how to solve this problem. I created a little test to make you understand better. http://pastebin.com/hdqR5P6G I need to know the value of d.CODE with a single query on ClassA. I need get a list of ClassA object but eager load d.CODE in polymorphic parent. Is there anyone who can help me? Best regards, Francesco -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] mssql and specifying a schema name?
Am 30.05.2012 20:03, schrieb Michael Bayer:
the default schema name is determined by:
SELECT default_schema_name FROM sys.database_principals WHERE name =
(SELECT user_name()) AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd
want to fix that so that it comes up with dbo.
Default Schema Name can be empty if the user logs in via an AD Group.
(in fact a Group user cannot be assigned a default schema name before
SQL Server 2012...).
See for example
http://dba.stackexchange.com/questions/8318/sql-2008-r2-creates-user-schema-when-windows-user-creates-tables
for some other instance of this happening.
Michael
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello, I'm trying to autolaod my table image but it keeps
complaining that the table doesn't exists.
I've enabled the echo = true and I see that you specify in the
query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM
[INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE
[COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
ORDER BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT
[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM
[INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE
[COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
ORDER BY [COLUMNS_1].[ORDINAL_POSITION] 2012-05-30 12:39:06,193
INFO sqlalchemy.engine.base.Engine ('image', 'MyDatabase' )
But my schema name is dbo?
Where do I specify that? On create_engine? or?
import sqlalchemy
e =
sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker Session =
sessionmaker(bind=e, autoflush=True, autocommit=False) session =
Session()
from sqlalchemy.orm import mapper
#--- image_table = sqlalchemy.Table('image', metadata,
autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks, Lucas
-- 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.
--
Michael Schlenker
Software Architect
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: m...@contact.de
Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Another Parent instance is not bound to a Session; lazy load...
Seems like you have a monumental problem to overcome. I'm glad you mentioned EJB and have a Java background. In EJB, at least back when I used the very early version 1.0, the concept of the transactional nature of various service methods is defined separate from the implementation of the method itself. And again, the example of using Hibernate with Spring (or even without Spring) has a similar concept going on - the demarcation of a transaction is most commonly external to the methods that do the work. There's a great section on this in Hibernate's docs at https://community.jboss.org/wiki/SessionsAndTransactions?_sscc=t . You can see there's an emphasis on doing *many* things in a transaction, keeping the transaction/session as a resource that is present externally to a large series of operations. So SQLAlchemy hasn't made any of this up, it is emulating the same patterns that come from the J2EE world.The Session is modeled from that of Hibernate. In the Python community, designing applications via UML and then generating classes/tables from that completed design is mostly unheard of.The 500-table, automatically generated model, which as you describe uses tables on tables to represent attributes further nested on themselves using more tables, is something I've been exposed to years ago, though I never actually saw such a model achieve any success as up-front, UML design has little to do with relational database best practices. An RDBMS does best with a hand-designed schema, following standard normalization techniques but at the same time only using as many tables as are necessary to model the problem, mostly agnostic of how an object model may want to represent it and certainly removed from attempts to genericize the modeling of data in an OO sense. SQLAlchemy is designed for this latter model; while there are ways to make it work with models that have hundreds or thousands of tables, these kinds of setups are challenging, and less than ideal in any case as the database spends far too much effort querying and updating across too many tables for operations to be efficient. On May 31, 2012, at 4:10 AM, Maurizio Nagni wrote: First of all thanks to all of you for your answers and time. Michael let me say that I agree 100% with all you wrote and my will/wish is to work as you wrote, but when you are inside the ORM its easy, other is if you want to interact with the ORM from outside. In my actual system I have more than 500 tables automatically generated from a UML model. Not all of them have a central role but all of them do something in the data model. If I want to fill one of the highest level object its attributes are classes their self with other attributes which are classes again; because I cannot simply send a few primitive parameters to the a function-ORM-aware and make the function create that object I am forced to create locally (in the GUI application) such object and their children (some of them can be NOT NULL so I cannot avoid it) then send the full object to the ORM interface. Apart the great advantages that an ORM offers it should be possible to install it as a service, that is separate it from the any other application: in other words I can have a library of objects (like I have) shared among several GUI/WEB applications and an ORM. The communication between the applications and the ORM should be done serializing and deserializing instances of objects in the shared library. As example you can take the remote interface for an EJB in Java (btw I am thinking about the stateless interface). In this way the ORM does have not to expose to other applicaitons it's session (or EntityManager in EJB) but just an interface. Still in other words the rules that I impose on how I manage my database (that is one or more method using session/transaction), should be independent from the way I display/control them. Again: I would like to use SA as the MODEL of a Model-View-Controller strategy and because of this I can have a myriad of views/controlles but just one separate model application acting as service. Now I am aware that my application is not decoupled as I want (Django initialize the SQL engine) so I cannot avoid SA to inject instruments in new instances but nonetheless I would expect that I may be able to work with objects in two separate universes: the GUI and the ORM. When I wrote that I feel to have missed something I was referring to this: it seems to me that I cannot use SA in a remote-like way, creating an object in the GUI and then send it to the ORM, being sure that because the ORM maps such object it knows how to persist it or eventually retrieve it because it know where to catch the key for each mapped object. For all the rest, thanks for your great software. Maurizio P.S. If you are curious about the system I am working on just take a brief look at it
Re: [sqlalchemy] Another Parent instance is not bound to a Session; lazy load...
On Thu, May 31, 2012 at 12:50 AM, Michael Bayer mike...@zzzcomputing.com wrote: Thing is, in order to work with a large volume of objects, you're forced to do this, otherwise the session can grow uncontrollably. flush periodically, and don't maintain references to things you're done with. The Session does not strongly reference objects that have no pending changes, and they'll be garbage collected. Problem is, I'm stuck with strongly-referencing sessions. The app comes from SA 0.3, and is heavily relying on the session as a kind of L1 cache - removing that assumption is a really huge task we haven't gotten to. We managed to upgrade it to SA 0.5, but we kept strongly-referencing sessions. The problems you're having are from unnecessary detachment of objects, from calling Session.close() and continuing to work with objects that have lost their owning Session, within the context of a new Session they have no association with. I've been solving those problems by reattaching objects to the session. Only with caches I haven't been able to do that, since cached objects will be used by many threads at once, so no single session can own them. Another case in which an object's lifespan can exceed the session's, is when you want to implement caching with objects of your data model - cached values will have come from other sessions than the current one, and things get horribly messy. There are documented patterns for caching - see the example in examples/beaker_caching in the distro. This pattern is designed to cleanly handle the pattern of detached objects becoming re-associated with a particular session at once. The pattern is along the lines of, session is created to work with a field of objects, a set of objects is retrieved from the cache, then re-associated with the cache en-masse using the merge_result() method illustrated in the example. Interesting, I hadn't seen that example. Bookmarked already :-) Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Another Parent instance is not bound to a Session; lazy load...
On May 31, 2012, at 10:35 AM, Claudio Freire wrote: On Thu, May 31, 2012 at 12:50 AM, Michael Bayer mike...@zzzcomputing.com wrote: Thing is, in order to work with a large volume of objects, you're forced to do this, otherwise the session can grow uncontrollably. flush periodically, and don't maintain references to things you're done with. The Session does not strongly reference objects that have no pending changes, and they'll be garbage collected. Problem is, I'm stuck with strongly-referencing sessions. The app comes from SA 0.3, and is heavily relying on the session as a kind of L1 cache - removing that assumption is a really huge task we haven't gotten to. We managed to upgrade it to SA 0.5, but we kept strongly-referencing sessions. well you'd really need to change that, sorry.I get very close to removing the strongidentity map on each release, you're the first user I've ever encountered with a dependency on it... -- 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: Can't make an association table use InnoDB
The tables don't exist yet. The Base.metadata.create_all(engine) is to
create them.
Thanks!
On May 30, 11:52 pm, Michael Bayer mike...@zzzcomputing.com wrote:
This might be because the tables you're trying to reference are themselves
not InnoDB. Try running DESCRIBE on the referenced tables at the MySQL
console to help confirm this, as well as the same CREATE TABLE statement
below.
On May 30, 2012, at 11:31 PM, Jeff wrote:
Having difficulty creating a database that includes the following
plumbing:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
Doing Base.metadata.create_all(engine) yields:
OperationalError: (OperationalError) (1005, Can't create table
'alstottj.Avalanche_Event_Association' (errno: 150)) '\nCREATE TABLE
`Avalanche_Event_Association` (\n\tavalanche_id INTEGER, \n\tevent_id
INTEGER, \n\tFOREIGN KEY(avalanche_id) REFERENCES `Avalanche` (id), \n
\tFOREIGN KEY(event_id) REFERENCES `Event` (id)\n)ENGINE=InnoDB\n
\n' ()
Commenting out the line mysql_engine='InnoDB' removes the error and
the tables are all created, but the association table is now MyISAM.
I have some feelings on what could be causing the error, but they all
seem improbable. Thoughts?
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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] Oracle Function Out Params
I'm having difficulty determining how to correctly call an oracle package function which returns a numeric value. I want to call this: BEGIN :out := my_schema.my_package.test_function(); END; I tried calling that using sqlalchemy.text() but I don't understand how to tell the procedure I want to use an out parameter. I also tried using the func module but couldn't get that to work either. Can someone point me to an example of the correct syntax? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/-TPZ33REargJ. 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: Can't make an association table use InnoDB
Perhaps it's relevant (though I suspect not) that the class Avalanche
actually contains:
class Avalanche(Base):
events = relationship(Event,
secondary=Avalanche_Event_Association)
This is what prevents us from writing the classes in the following
order in the database definition .py file:
class Event(Base):
.
class Avalanche(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Because Avalanche needs to reference Avalanche_Event_Association. I
hope, however, that the the create_all function is able to
appropriately create the tables anyway, regardless of their order in
the database definition .py file.
Thanks!
On May 31, 2:21 pm, Jeff jeffalst...@gmail.com wrote:
The tables don't exist yet. The Base.metadata.create_all(engine) is to
create them.
Thanks!
On May 30, 11:52 pm, Michael Bayer mike...@zzzcomputing.com wrote:
This might be because the tables you're trying to reference are themselves
not InnoDB. Try running DESCRIBE on the referenced tables at the MySQL
console to help confirm this, as well as the same CREATE TABLE statement
below.
On May 30, 2012, at 11:31 PM, Jeff wrote:
Having difficulty creating a database that includes the following
plumbing:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
Doing Base.metadata.create_all(engine) yields:
OperationalError: (OperationalError) (1005, Can't create table
'alstottj.Avalanche_Event_Association' (errno: 150)) '\nCREATE TABLE
`Avalanche_Event_Association` (\n\tavalanche_id INTEGER, \n\tevent_id
INTEGER, \n\tFOREIGN KEY(avalanche_id) REFERENCES `Avalanche` (id), \n
\tFOREIGN KEY(event_id) REFERENCES `Event` (id)\n)ENGINE=InnoDB\n
\n' ()
Commenting out the line mysql_engine='InnoDB' removes the error and
the tables are all created, but the association table is now MyISAM.
I have some feelings on what could be causing the error, but they all
seem improbable. Thoughts?
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Can't make an association table use InnoDB
create_all() only can determine the order of tables if you use ForeignKey and
ForeignKeyConstraint objects correctly on the source Table objects and/or
declarative classes.
See http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#many-to-many
and
http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#association-object
for examples of these configurations. Note that mixing a fully mapped
association object and secondary is a bit unusual and you'll want
viewonly=True if you're doing that.
On May 31, 2012, at 2:32 PM, Jeff wrote:
Perhaps it's relevant (though I suspect not) that the class Avalanche
actually contains:
class Avalanche(Base):
events = relationship(Event,
secondary=Avalanche_Event_Association)
This is what prevents us from writing the classes in the following
order in the database definition .py file:
class Event(Base):
.
class Avalanche(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Because Avalanche needs to reference Avalanche_Event_Association. I
hope, however, that the the create_all function is able to
appropriately create the tables anyway, regardless of their order in
the database definition .py file.
Thanks!
On May 31, 2:21 pm, Jeff jeffalst...@gmail.com wrote:
The tables don't exist yet. The Base.metadata.create_all(engine) is to
create them.
Thanks!
On May 30, 11:52 pm, Michael Bayer mike...@zzzcomputing.com wrote:
This might be because the tables you're trying to reference are themselves
not InnoDB. Try running DESCRIBE on the referenced tables at the MySQL
console to help confirm this, as well as the same CREATE TABLE statement
below.
On May 30, 2012, at 11:31 PM, Jeff wrote:
Having difficulty creating a database that includes the following
plumbing:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
Doing Base.metadata.create_all(engine) yields:
OperationalError: (OperationalError) (1005, Can't create table
'alstottj.Avalanche_Event_Association' (errno: 150)) '\nCREATE TABLE
`Avalanche_Event_Association` (\n\tavalanche_id INTEGER, \n\tevent_id
INTEGER, \n\tFOREIGN KEY(avalanche_id) REFERENCES `Avalanche` (id), \n
\tFOREIGN KEY(event_id) REFERENCES `Event` (id)\n)ENGINE=InnoDB\n
\n' ()
Commenting out the line mysql_engine='InnoDB' removes the error and
the tables are all created, but the association table is now MyISAM.
I have some feelings on what could be causing the error, but they all
seem improbable. Thoughts?
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Oracle Function Out Params
There's an outparam() construct specifically for Oracle OUT parameters.
Here's an example:
from sqlalchemy import text, bindparam, outparam
result = \
db.execute(text('begin foo(:x_in, :x_out, :y_out, '
':z_out); end;',
bindparams=[bindparam('x_in', Float),
outparam('x_out', Integer),
outparam('y_out', Float),
outparam('z_out', String)]), x_in=5)
print result.out_parameters
On May 31, 2012, at 12:17 PM, Justin Valentini wrote:
I'm having difficulty determining how to correctly call an oracle package
function which returns a numeric value. I want to call this:
BEGIN :out := my_schema.my_package.test_function(); END;
I tried calling that using sqlalchemy.text() but I don't understand how to
tell the procedure I want to use an out parameter. I also tried using the
func module but couldn't get that to work either. Can someone point me to an
example of the correct syntax?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/-TPZ33REargJ.
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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Joinedload and polymorphic problem
Joining to ClassA.b does not include an automatic upgrade of ClassB to also load it's joined inheritance table ClassC.This is actually something SQLAlchemy can't quite do yet unless you hardwired a with_polymorphic onto your ClassB, which means it would join to ClassC all the time. 0.8 has a new with_polymorphic() feature which gets closer, but still contains_eager() is not smart enough to do it yet - this is ticket #1106 related to ticket #2438. May or not be doable in 0.8 depending on how things go. The only option for now, short of hardwiring ClassB to be with_polymorphic-ClassC, is to load ClassB + ClassC separately with query(ClassB).with_polymorphic(ClassC), using a subqueryload approach, then affixing them to each parent - see the recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading . On May 31, 2012, at 6:47 AM, Francesco wrote: Hello everyone, I have a big problem that alone can not solve. There are tens of days I try to figure out how to solve this problem. I created a little test to make you understand better. http://pastebin.com/hdqR5P6G I need to know the value of d.CODE with a single query on ClassA. I need get a list of ClassA object but eager load d.CODE in polymorphic parent. Is there anyone who can help me? Best regards, Francesco -- 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. -- 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: Can't make an association table use InnoDB
Thanks! I don't quite follow the statement about fully mapped
association table being unusual. The first Many-to-Many example you
linked was the structure I copied when making my own tables here. Have
I deviated from it in some way? Or should the example on the site have
viewonly=True, if being used with InnoDB? Perhaps I just wasn't being
clear in my reproducing them here. Just once again now, with the
additional relevant bits in:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
events = relationship(Event,
secondary=Avalanche_Event_Association)
Doing Base.metadata.create_all(engine) yields an error creating the
Avalanche_Event_Association table.
On May 31, 3:28 pm, Michael Bayer mike...@zzzcomputing.com wrote:
create_all() only can determine the order of tables if you use ForeignKey and
ForeignKeyConstraint objects correctly on the source Table objects and/or
declarative classes.
Seehttp://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#many-to-...andhttp://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#associat...for
examples of these configurations. Note that mixing a fully mapped
association object and secondary is a bit unusual and you'll want
viewonly=True if you're doing that.
On May 31, 2012, at 2:32 PM, Jeff wrote:
Perhaps it's relevant (though I suspect not) that the class Avalanche
actually contains:
class Avalanche(Base):
events = relationship(Event,
secondary=Avalanche_Event_Association)
This is what prevents us from writing the classes in the following
order in the database definition .py file:
class Event(Base):
.
class Avalanche(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Because Avalanche needs to reference Avalanche_Event_Association. I
hope, however, that the the create_all function is able to
appropriately create the tables anyway, regardless of their order in
the database definition .py file.
Thanks!
On May 31, 2:21 pm, Jeff jeffalst...@gmail.com wrote:
The tables don't exist yet. The Base.metadata.create_all(engine) is to
create them.
Thanks!
On May 30, 11:52 pm, Michael Bayer mike...@zzzcomputing.com wrote:
This might be because the tables you're trying to reference are
themselves not InnoDB. Try running DESCRIBE on the referenced tables at
the MySQL console to help confirm this, as well as the same CREATE TABLE
statement below.
On May 30, 2012, at 11:31 PM, Jeff wrote:
Having difficulty creating a database that includes the following
plumbing:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
Doing Base.metadata.create_all(engine) yields:
OperationalError: (OperationalError) (1005, Can't create table
'alstottj.Avalanche_Event_Association' (errno: 150)) '\nCREATE TABLE
`Avalanche_Event_Association` (\n\tavalanche_id INTEGER, \n\tevent_id
INTEGER, \n\tFOREIGN KEY(avalanche_id) REFERENCES `Avalanche` (id), \n
\tFOREIGN KEY(event_id) REFERENCES `Event` (id)\n)ENGINE=InnoDB\n
\n' ()
Commenting out the line mysql_engine='InnoDB' removes the error and
the tables are all created, but the association table is now MyISAM.
I have some feelings on what could be causing the error, but they all
seem improbable. Thoughts?
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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
Re: [sqlalchemy] Re: Can't make an association table use InnoDB
On May 31, 2012, at 3:49 PM, Jeff wrote:
Thanks! I don't quite follow the statement about fully mapped
association table being unusual.
your name Avalanche_Event_Association with CamelCase made me think it was
mapped class, but this is not the case as you have it as a Table.
the problem might be those uppercase names you're using in your ForeignKey
declarations, as your MySQL may or may not actually be case sensitive. The
attached script works for me on OSX, however MySQLs case sensitivity is
platform-dependent. Keep all the tablenames totally lower case with MySQL as
its a nightmare with case sensitivity. Note SQLAlchemy treats names that
aren't all lower case as case sensitive.
The first Many-to-Many example you
linked was the structure I copied when making my own tables here. Have
I deviated from it in some way? Or should the example on the site have
viewonly=True, if being used with InnoDB? Perhaps I just wasn't being
clear in my reproducing them here. Just once again now, with the
additional relevant bits in:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
events = relationship(Event,
secondary=Avalanche_Event_Association)
Doing Base.metadata.create_all(engine) yields an error creating the
Avalanche_Event_Association table.
On May 31, 3:28 pm, Michael Bayer mike...@zzzcomputing.com wrote:
create_all() only can determine the order of tables if you use ForeignKey
and ForeignKeyConstraint objects correctly on the source Table objects
and/or declarative classes.
Seehttp://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#many-to-...andhttp://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#associat...for
examples of these configurations. Note that mixing a fully mapped
association object and secondary is a bit unusual and you'll want
viewonly=True if you're doing that.
On May 31, 2012, at 2:32 PM, Jeff wrote:
Perhaps it's relevant (though I suspect not) that the class Avalanche
actually contains:
class Avalanche(Base):
events = relationship(Event,
secondary=Avalanche_Event_Association)
This is what prevents us from writing the classes in the following
order in the database definition .py file:
class Event(Base):
.
class Avalanche(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Because Avalanche needs to reference Avalanche_Event_Association. I
hope, however, that the the create_all function is able to
appropriately create the tables anyway, regardless of their order in
the database definition .py file.
Thanks!
On May 31, 2:21 pm, Jeff jeffalst...@gmail.com wrote:
The tables don't exist yet. The Base.metadata.create_all(engine) is to
create them.
Thanks!
On May 30, 11:52 pm, Michael Bayer mike...@zzzcomputing.com wrote:
This might be because the tables you're trying to reference are
themselves not InnoDB. Try running DESCRIBE on the referenced tables at
the MySQL console to help confirm this, as well as the same CREATE TABLE
statement below.
On May 30, 2012, at 11:31 PM, Jeff wrote:
Having difficulty creating a database that includes the following
plumbing:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
Doing Base.metadata.create_all(engine) yields:
OperationalError: (OperationalError) (1005, Can't create table
'alstottj.Avalanche_Event_Association' (errno: 150)) '\nCREATE TABLE
`Avalanche_Event_Association` (\n\tavalanche_id INTEGER, \n\tevent_id
INTEGER, \n\tFOREIGN KEY(avalanche_id) REFERENCES `Avalanche` (id), \n
\tFOREIGN KEY(event_id) REFERENCES `Event` (id)\n)ENGINE=InnoDB\n
\n' ()
Commenting out the line mysql_engine='InnoDB' removes the error and
the tables are all created, but the association table is now MyISAM.
I have some feelings on what could be causing the error, but they all
seem improbable. Thoughts?
--
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,
[sqlalchemy] Re: Can't make an association table use InnoDB
Well, one of the worst things that can happen in programming has
happened: It now works, and I don't know why _ I didn't change
anything that I know of, and I definitely didn't change the
capitalization. Guess I'll just slowly back away from the machine and
hope everything stays that way.
Thanks for the tip on capitalization, though. Good to know!
On May 31, 3:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On May 31, 2012, at 3:49 PM, Jeff wrote:
Thanks! I don't quite follow the statement about fully mapped
association table being unusual.
your name Avalanche_Event_Association with CamelCase made me think it was
mapped class, but this is not the case as you have it as a Table.
the problem might be those uppercase names you're using in your ForeignKey
declarations, as your MySQL may or may not actually be case sensitive. The
attached script works for me on OSX, however MySQLs case sensitivity is
platform-dependent. Keep all the tablenames totally lower case with MySQL
as its a nightmare with case sensitivity. Note SQLAlchemy treats names that
aren't all lower case as case sensitive.
test.py
1KViewDownload
The first Many-to-Many example you
linked was the structure I copied when making my own tables here. Have
I deviated from it in some way? Or should the example on the site have
viewonly=True, if being used with InnoDB? Perhaps I just wasn't being
clear in my reproducing them here. Just once again now, with the
additional relevant bits in:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
events = relationship(Event,
secondary=Avalanche_Event_Association)
Doing Base.metadata.create_all(engine) yields an error creating the
Avalanche_Event_Association table.
On May 31, 3:28 pm, Michael Bayer mike...@zzzcomputing.com wrote:
create_all() only can determine the order of tables if you use ForeignKey
and ForeignKeyConstraint objects correctly on the source Table objects
and/or declarative classes.
Seehttp://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#many-to-...examples
of these configurations. Note that mixing a fully mapped association
object and secondary is a bit unusual and you'll want viewonly=True if
you're doing that.
On May 31, 2012, at 2:32 PM, Jeff wrote:
Perhaps it's relevant (though I suspect not) that the class Avalanche
actually contains:
class Avalanche(Base):
events = relationship(Event,
secondary=Avalanche_Event_Association)
This is what prevents us from writing the classes in the following
order in the database definition .py file:
class Event(Base):
.
class Avalanche(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Because Avalanche needs to reference Avalanche_Event_Association. I
hope, however, that the the create_all function is able to
appropriately create the tables anyway, regardless of their order in
the database definition .py file.
Thanks!
On May 31, 2:21 pm, Jeff jeffalst...@gmail.com wrote:
The tables don't exist yet. The Base.metadata.create_all(engine) is to
create them.
Thanks!
On May 30, 11:52 pm, Michael Bayer mike...@zzzcomputing.com wrote:
This might be because the tables you're trying to reference are
themselves not InnoDB. Try running DESCRIBE on the referenced tables
at the MySQL console to help confirm this, as well as the same CREATE
TABLE statement below.
On May 30, 2012, at 11:31 PM, Jeff wrote:
Having difficulty creating a database that includes the following
plumbing:
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
Avalanche_Event_Association = Table('Avalanche_Event_Association',
Base.metadata,
Column('avalanche_id', Integer, ForeignKey('Avalanche.id')),
Column('event_id', Integer, ForeignKey('Event.id')),
mysql_engine='InnoDB')
class Avalanche(Base):
Doing Base.metadata.create_all(engine) yields:
OperationalError: (OperationalError) (1005, Can't create table
'alstottj.Avalanche_Event_Association' (errno: 150)) '\nCREATE TABLE
`Avalanche_Event_Association` (\n\tavalanche_id INTEGER, \n\tevent_id
INTEGER, \n\tFOREIGN KEY(avalanche_id) REFERENCES `Avalanche` (id), \n
\tFOREIGN KEY(event_id) REFERENCES `Event` (id)\n)ENGINE=InnoDB\n
\n' ()
Commenting out the line mysql_engine='InnoDB' removes the error and
the tables are all created,
[sqlalchemy] Calling stored procedures in SQLAlchemy
Hello all!
I'm having this *exact* bug from a few years ago wrt. calling stored
procedures.
https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/qA_ypVgJ1B0
What makes it worse, however, is that adding the autocommit execution
option or explicitly starting and stopping a transaction do nothing.
session.execute(text('call add_logentry(:username, :hostname, :action,
\'-00-00 00:00:00\')'), {
'username': username,
'hostname': hostname,
'action' : action
})
There's the code. It should insert some values into some tables, however
those values are never inserted, though the primary key counter is
incremented.
I'm using SQLAlchemy with ZopeTransactionExtension.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/si1vqn5kmjoJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Calling stored procedures in SQLAlchemy
did you call Session.commit() ? otherwise you're still in an open transaction,
assuming default settings.
Session.execute() is not the same as engine.execute(), where the latter is
autocommitting (assuming you also called execution_options(autocommit=True) for
this particular text() construct).
On May 31, 2012, at 9:23 PM, Will Orr wrote:
Hello all!
I'm having this *exact* bug from a few years ago wrt. calling stored
procedures.
https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/qA_ypVgJ1B0
What makes it worse, however, is that adding the autocommit execution option
or explicitly starting and stopping a transaction do nothing.
session.execute(text('call add_logentry(:username, :hostname, :action,
\'-00-00 00:00:00\')'), {
'username': username,
'hostname': hostname,
'action' : action
})
There's the code. It should insert some values into some tables, however
those values are never inserted, though the primary key counter is
incremented.
I'm using SQLAlchemy with ZopeTransactionExtension.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/si1vqn5kmjoJ.
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.
--
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.
