Re: [sqlalchemy] group_by by the result of some other query
Hi Eduardo, group_by accepts strings, so this is certainly possible. You might do it like so: # build our column names query column_names = session.query(tab.c.name).filter(tab.c.value==354) # execute and a build a list of strings from our query column_names = [column_name for (column_name,) in column_names] # group the results of query1 by the list of column names we just created query1 = query1.group_by(*column_names) Cheers, Andrew Taumoefolau andrew.taumoefo...@gmail.com On 11/08/2011, at 2:43 AM, Eduardo wrote: Dear all, I am trying to find a way to limit group_by arguments of one query only to the values of some other query. Is this doable? If yes how to do that. This is an example how query looks like: query1.group_by(sesion.query(tab.columns['name']).filter(datab.columns['value']==354).all()) 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. -- 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] Feature request: Multilevel inheritance mapping
I was trying to configure a multilevel inheritance mapping (either using joint- table or single-table) but it didn't work as I was expecting it to. I understand that it probably has never been thought of, but in my particular use case, it would have been really handy. Any idea if this is possible||makes sense? -- Fayaz Yusuf Khan Cloud developer and designer Dexetra SS, Kochi, India fayaz.yusuf.khan_AT_gmail_DOT_com fayaz_AT_dexetra_DOT_com +91-9746-830-823 signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Ho do I syncronize ORM objects when working with multiple sessions?
Hi everyone, in my script I work with two different sessions (say session1 and session2) bounded to the same database and, consequently, with ORM objects obtained with queries issued on them. It can occur that an object related to session1 change a value of the corresponding mapped table record and, on the other hand, there exist an object related to session2 mapped to the same table record. In this case the second object becomes misaligned with respect to the mapped table. So, my question is...which is the best approach to handle this situation? Can a session object somehow become aware that it must be synchronized/ refreshed? Thanks in advance for your help! -- 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: group_by by the result of some other query
Hi Thanks for the answer, This is my query column_names = session.query(tab.c.name).filter(tab.c.value==354) column_names = [column_name for (column_name,) in column_names] query=sess.query(func.max(tab.columns['name']),datab.columns['article_id']).group_by(*column_names).all() I get an error here: ProgrammingError: (ProgrammingError) syntax error at or near .6 LINE 2: ... GROUP BY fde.ck1.LKUT.RAT-ES.vertic.6hpa.low.6hdfjks.rih What does this mean? Thanks ^ 'SELECT max(sometable.name) AS max_1, sometable.id AS sometable_id \nFROM sometable GROUP BY fde.ck1.LKUT.RAT-ES.vertic.6hpa.low. 6hdfjks.rihfjkdp1.fhsdjk00-1900.nhgtec,. On Aug 11, 7:34 am, Andrew Taumoefolau zen...@gmail.com wrote: Hi Eduardo, group_by accepts strings, so this is certainly possible. You might do it like so: # build our column names query # execute and a build a list of strings from our query # group the results of query1 by the list of column names we just created query1 = query1.group_by(*column_names) Cheers, Andrew Taumoefolau andrew.taumoefo...@gmail.com On 11/08/2011, at 2:43 AM, Eduardo wrote: Dear all, I am trying to find a way to limit group_by arguments of one query only to the values of some other query. Is this doable? If yes how to do that. This is an example how query looks like: query1.group_by(sesion.query(tab.columns['name']).filter(datab.columns['value']==354).all()) 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 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] Feature request: Multilevel inheritance mapping
multilevel sounds like you mean this: class A: class B(A): class C(B): class D(C): That's supported fully in all cases, and for single/joined should work without any special steps (for concrete its slightly more weird). If this is not working then we can get you going with some test cases. If OTOH you mean : class A: tablea class B: tableb class C(A, B): tablec + (tableb join tablea) That's multiple inheritance. that is not supported in terms of table mappings that are multiply inherited.(Mixin classes, that is where only one of each base is mapped to a table, is encouraged, though). That feature is not likely to be supported directly as it goes way beyond what the architecture does right now and is a really rare, arguably impractical (due to all the joins), use case.You can of course approximate setups like that using relationship(). On Aug 11, 2011, at 5:04 AM, Fayaz Yusuf Khan wrote: I was trying to configure a multilevel inheritance mapping (either using joint- table or single-table) but it didn't work as I was expecting it to. I understand that it probably has never been thought of, but in my particular use case, it would have been really handy. Any idea if this is possible||makes sense? -- Fayaz Yusuf Khan Cloud developer and designer Dexetra SS, Kochi, India fayaz.yusuf.khan_AT_gmail_DOT_com fayaz_AT_dexetra_DOT_com +91-9746-830-823 -- 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] joining to a from_statement
Hi list, I'm using SA 0.6.7, Python 2.6.5 and Postgres 8.4 on Ubuntu 10.04 LTS I'm trying to optimize my code against postgres by using the recursive statements CTE as documented here : http://www.postgresql.org/docs/8.4/interactive/queries-with.html My model is a group graph ( many to many relation on itself) I've pasted a self running test of my code : http://pastebin.com/1Vc2PFLx the syntax is elixir's but that is not relevant the pure SQL query only includes id and rank (in my real life object, I have many fields, and they evolve) as seen in comment of the code, when I get detailed information on the result, a new SELECT query is issued for every attribute not yet loaded What I want to achieve : have all the attributes eager loaded without having to explicitely declare them in the PG specific query (for code maintenability) An approach I can't finalize : the idea was to run the PG select and have the result stored in a 2 columns temp table (how ?) and then query the groups (to have the ORM) while joining to this temp table. something like hierarchy_q = session.query(Group.id, 'rank').from_statement(group_all_groups).params(GROUPID=self.id).subquery() session.query(Group).join((hierarchy_q, Group.id==hierarchy_q.c.id) but : *** AttributeError: 'Annotated_TextClause' object has no attribute 'alias' Ideally, I would like a way to have a session.query(Group,'rank') where all the groups' attributes are loaded. Moreover, I wish to have a way of joining this query through relationships. For instance, Groups will have users, I would like to efficiently be able to do something like session.query(User).join(hierarchy_query).order_by(rank) to get all the users of the group, ordered by the rank of the group they belong to. I've read https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/VAttoxkLlXw but I don't feel my question is exactly the same, as I wish to keep the rank information (only available in the text query) any enlightening idea would be very welcome thanks NiL -- 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/-/OIgzgCxD-rgJ. 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] joining to a from_statement
On Aug 11, 2011, at 12:31 PM, NiL wrote: Hi list, I'm using SA 0.6.7, Python 2.6.5 and Postgres 8.4 on Ubuntu 10.04 LTS I'm trying to optimize my code against postgres by using the recursive statements CTE as documented here : http://www.postgresql.org/docs/8.4/interactive/queries-with.html My model is a group graph ( many to many relation on itself) I've pasted a self running test of my code : http://pastebin.com/1Vc2PFLx the syntax is elixir's but that is not relevant the pure SQL query only includes id and rank (in my real life object, I have many fields, and they evolve) as seen in comment of the code, when I get detailed information on the result, a new SELECT query is issued for every attribute not yet loaded What I want to achieve : have all the attributes eager loaded without having to explicitely declare them in the PG specific query (for code maintenability) An approach I can't finalize : the idea was to run the PG select and have the result stored in a 2 columns temp table (how ?) and then query the groups (to have the ORM) while joining to this temp table. something like hierarchy_q = session.query(Group.id, 'rank').from_statement(group_all_groups).params(GROUPID=self.id).subquery() session.query(Group).join((hierarchy_q, Group.id==hierarchy_q.c.id) but : *** AttributeError: 'Annotated_TextClause' object has no attribute 'alias' Ideally, I would like a way to have a session.query(Group,'rank') where all the groups' attributes are loaded. Moreover, I wish to have a way of joining this query through relationships. For instance, Groups will have users, I would like to efficiently be able to do something like session.query(User).join(hierarchy_query).order_by(rank) to get all the users of the group, ordered by the rank of the group they belong to. I've read https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/VAttoxkLlXw but I don't feel my question is exactly the same, as I wish to keep the rank information (only available in the text query) any enlightening idea would be very welcome I think everything is on the mark here except you need a way to have your WITH RECURSIVE query as a FromClause, that is something with a .c. on it, rather than a TextClause which is just a string. We'd like to support WITH RECURSIVE directly but we need to make a decision on what to do about Oracle's CONNECT BY, i.e. do we just ignore it, or try to come up with a syntax that encompasses both.This problem needs to be approached carefully and I need to make sure I know every single corner of a CTE before making API decisions, which requires more commitment than I've had to tackle it fully. This is ticket #1859 at http://www.sqlalchemy.org/trac/ticket/1859 . But anyway right now I think the job is to make yourself a WITH RECURSIVE SQL element, so that you can join to it. So attached is a quick version of a CTE half hardcoded to the query you want to do. It's using with in Python too at the moment which seems very nice though studying the full syntax on PG's docs has me wondering if it actually makes sense. The ultimate SELECT query is mostly hardcoded for now. It renders the query I think you're looking for, though. I'd need to play with it some more to get it to be flexible enough to produce the example query at http://www.postgresql.org/docs/8.4/interactive/queries-with.html , but this should give you some building blocks to work with at least. thanks NiL -- 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/-/OIgzgCxD-rgJ. 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. thanksNiL -- 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/-/OIgzgCxD-rgJ. 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. from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import FromClause class CommonTableExpression(FromClause): Represent the 'inside' of a common table expression.
[sqlalchemy] mysql table creation errno 105 when using vertical partitioning with InnoDB
Hi there, I have been using ORM vertical partitioning for a while with MySQL 5.1.58 and MyISAM tables and SQLAlchemy 0.7.2. I have recently switched over to using InnoDB tables and my vertical partitioning table creation is failing part-way in with an errno 105 code. I have confirmed that all the tables that *did* get created are using an InnoDB engine, so having stray MyISAM tables does not appear to be the problem. I have found a fix for the SQL that SQLAlchemy generates (and that MySQL barfs on) that allows the table creation to succeed, which involves simply prefixing SQLAlchemy's CREATE TABLE foreign key references with the database name. SQLAlchemy generates this line below, which also fails with errno 105 when I paste it into MySQL monitor, just like when executed by SQLA directly: FOREIGN KEY(project_id) REFERENCES projects (id) When I change it to this, table creation succeeds in mySQL monitor: FOREIGN KEY(project_id) REFERENCES car_res.projects (id) Basically, ForeignKey(projects.id) seems sufficient for SQLA to define the foreign key relationship, but MySQL seems to be wanting car_res.projects(id) to appear in the FOREIGN KEY SQL, instead of projects(id). So I'm a bit confused. Is SQLA to blame for not including this prefix in vertical partitioning table creation scenarios, or is there some bug in my code somewhere that is causing MySQL to barf or SQLA to generate incorrect SQL? I have some basic vertical partitioning test code that uses InnoDB with two tables, and for my basic test, it seems like the database. prefix is *not* required by MySQL to successfully create tables. Anyone have any idea about what might be going on? Regards, Daniel -- 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] Ho do I syncronize ORM objects when working with multiple sessions?
On Aug 11, 2011, at 5:46 AM, Massi wrote: Hi everyone, in my script I work with two different sessions (say session1 and session2) bounded to the same database and, consequently, with ORM objects obtained with queries issued on them. It can occur that an object related to session1 change a value of the corresponding mapped table record and, on the other hand, there exist an object related to session2 mapped to the same table record. In this case the second object becomes misaligned with respect to the mapped table. So, my question is...which is the best approach to handle this situation? Can a session object somehow become aware that it must be synchronized/ refreshed? Thanks in advance for your help! The Session operates in a transaction which is assumed to be isolated from all other transactions. in practice, the level of isolation between transactions of course varies, but the Session model remains constant, in that data from other transactions is assumed to be available once a new local transaction begins - so Session expires all data at the end of a transaction. So the short answer is call commit() on session1 as well as session2. session1 will expire all of its data and start a new transaction; session 2 will also flush its changes and commit the transaction, so that those changes are now viewable by other transactions. Expiring from the Session perspective means that all requests for database-bound data will emit new SQL. See http://www.sqlalchemy.org/docs/orm/session.html#refreshing-expiring . Note that both SQLAlchemy *as well as* the isolation of the transaction itself on the database side play a role in concealing concurrent changes. Expiring the data in the SQLAlchemy session alone, which you can do via expire() or expire_all(), will still not show any changes from other sessions, if the database is isolating those transactions from each other until after commit. An overview of isolation is at: http://en.wikipedia.org/wiki/Isolation_%28database_systems%29 -- 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] mysql table creation errno 105 when using vertical partitioning with InnoDB
On Aug 11, 2011, at 2:04 PM, Daniel Robbins wrote:
Hi there,
I have been using ORM vertical partitioning for a while with MySQL 5.1.58 and
MyISAM tables and SQLAlchemy 0.7.2.
I have recently switched over to using InnoDB tables and my vertical
partitioning table creation is failing part-way in with an errno 105 code.
I have confirmed that all the tables that *did* get created are using an
InnoDB engine, so having stray MyISAM tables does not appear to be the
problem.
I have found a fix for the SQL that SQLAlchemy generates (and that MySQL
barfs on) that allows the table creation to succeed, which involves simply
prefixing SQLAlchemy's CREATE TABLE foreign key references with the database
name. SQLAlchemy generates this line below, which also fails with errno 105
when I paste it into MySQL monitor, just like when executed by SQLA directly:
FOREIGN KEY(project_id) REFERENCES projects (id)
When I change it to this, table creation succeeds in mySQL monitor:
FOREIGN KEY(project_id) REFERENCES car_res.projects (id)
Basically, ForeignKey(projects.id) seems sufficient for SQLA to define the
foreign key relationship, but MySQL seems to be wanting
car_res.projects(id) to appear in the FOREIGN KEY SQL, instead of
projects(id).
So I'm a bit confused. Is SQLA to blame for not including this prefix in
vertical partitioning table creation scenarios, or is there some bug in my
code somewhere that is causing MySQL to barf or SQLA to generate incorrect
SQL? I have some basic vertical partitioning test code that uses InnoDB with
two tables, and for my basic test, it seems like the database. prefix is
*not* required by MySQL to successfully create tables.
Anyone have any idea about what might be going on?
Vertical partitioning implies tables in separate databases or schemas.It
seems like you have it set up such that a table in partition A can refer to a
table in partition B using a schema qualifier. So if this is the case you'd
use the schema argument on each Table to establish these names, when you do
your CREATE, as well as schema-qualify the ForeignKey:
t1 = Table(table_a, metadata, Column('id', Integer, primary_key=True),
schema=schema_a)
t2 = Table(table_b, metadata, Column('table_a_id', Integer,
ForeignKey(schema_a.table_a.id)), schema=schema_b)
Another option would be to forego the usage of ForeignKey for table
relationships that span across two partitions, at least when CREATE TABLE is
emitted. This is effectively what you were doing when you were on MyISAM,
since REFERENCES is a no-op on MyISAM - it just gets thrown away hence you had
no error.
This is total conjecture since I don't know the details here nor do I know
MySQL's performance characteristics very deeply, but the existence of actual
cross-schema foreign key constraints in the MySQL database may be a performance
issue, if writing to table B means a disk operation on an entirely separate
schema for table A must occur in order to insert or update a row.
? I have some basic vertical partitioning test code that uses InnoDB with two
tables, and for my basic test, it seems like the database. prefix is *not*
required by MySQL to successfully create tables.
this part is confusing since you said earlier that REFERENCES projects (id)
emits errno 105 when InnoDB is in use. What is the exact CREATE TABLE syntax
you would like to see ?
--
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] mysql table creation errno 105 when using vertical partitioning with InnoDB
On Thu, Aug 11, 2011 at 12:20 PM, Michael Bayer mike...@zzzcomputing.comwrote:
Vertical partitioning implies tables in separate databases or schemas.
It seems like you have it set up such that a table in partition A can refer
to a table in partition B using a schema qualifier. So if this is the case
you'd use the schema argument on each Table to establish these names, when
you do your CREATE, as well as schema-qualify the ForeignKey:
t1 = Table(table_a, metadata, Column('id', Integer, primary_key=True),
schema=schema_a)
t2 = Table(table_b, metadata, Column('table_a_id', Integer, ForeignKey(
schema_a.table_a.id)), schema=schema_b)
Your answers raise more questions :) So it looks like I can use schema
prefixing as above and avoid using vertical partitioning altogether.
Another option would be to forego the usage of ForeignKey for table
relationships that span across two partitions, at least when CREATE TABLE is
emitted. This is effectively what you were doing when you were on MyISAM,
since REFERENCES is a no-op on MyISAM - it just gets thrown away hence you
had no error.
This is total conjecture since I don't know the details here nor do I know
MySQL's performance characteristics very deeply, but the existence of actual
cross-schema foreign key constraints in the MySQL database may be a
performance issue, if writing to table B means a disk operation on an
entirely separate schema for table A must occur in order to insert or update
a row.
OK, let me try to understand this.
The sample vertical partitioning code (this is my basic test that works,
shown below, based on the sample code in the O'Reilly book) works correctly
with InnoDB. It appears to emit ForeignKey for table relationships when
CREATE TABLE is emitted. Is this code supposed to fail? It's working.
from sqlalchemy import *
from sqlalchemy.orm import *
engine1 = create_engine('mysql://car:foo@localhost/car_fac')
engine2 = create_engine('mysql://car:foo@localhost/car_res')
engine1.echo = engine2.echo = True
metadata = MetaData()
product_table = Table( 'product', metadata,
Column('sku', String(20), primary_key=True),
Column('msrp', Numeric),
mysql_engine='InnoDB')
product_summary_table = Table( 'product_summary', metadata,
Column('sku', String(20), ForeignKey('product.sku'),
primary_key=True),
Column('name', Unicode(255)),
Column('description', Unicode(255)),
mysql_engine='InnoDB')
product_table.create(bind=engine1,checkfirst=True)
product_summary_table.create(bind=engine2,checkfirst=True)
class Product(object):
pass
class ProductSummary(object):
pass
mapper(ProductSummary, product_summary_table, properties=dict(
product=relation(Product, backref=backref('summary',
uselist=False
mapper(Product, product_table)
Session = sessionmaker(twophase=True)
Session.configure(binds={Product:engine1, ProductSummary:engine2})
session = Session()
As you can see, I'm using the same mysql account, but with two different
engines in a vertical partitioning configuration. MySQL is happy with the
foreign key relationships and creates the tables.
So... I don't understand your suggestion of not emitting ForeignKey at table
creation time. It appears to work in my basic test.
Shouldn't SQLA detect that the tables are vertically partitioned, treat the
ForeignKey relationships as NO-OPs to MySQL (because the tables may not be
able to *see* each other, since you could be using different MySQL accounts
with different permissions), and just use the ForeignKey definitions to help
set up the mappers properly?
I guess I don't understand the limitations/capabilities of vertical
partitioning in SQLA.
I have a more complex application that is basically doing the same thing as
this example code, actually using three engines to connect to three
different MySQL databases on the same server with the same account. This is
probably not the best way to do things, as schema prefixing would be better.
But as the above sample code shows, this *can* work. But my more complex app
is failing with the errno 105, which is what is confusing me. I can't figure
out the difference between my large application and this simple example, and
why the simple example works but my application does not, when they are
essentially doing the same thing. The sample code above emits SQL to MySQL
that defines the ForeignKey relationship and does not need a database.
prefix. But my big app seems to need that database. prefix. Maybe I have
the binds messed up?
-Daniel
--
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] mysql table creation errno 105 when using vertical partitioning with InnoDB
On Aug 11, 2011, at 2:57 PM, Daniel Robbins wrote:
based on the sample code in the O'Reilly book
Just FYI the Oreilly book is super, duper, extremely old and out of date.A
good read through the documentation on the SQLAlchemy site should be more
complete at this point and up to date, also stressing a whole set of new
techniques that weren't available when the OReilly book was written (it was
written against version 0.3).
engine1 = create_engine('mysql://car:foo@localhost/car_fac')
engine2 = create_engine('mysql://car:foo@localhost/car_res')
engine1.echo = engine2.echo = True
metadata = MetaData()
product_table = Table( 'product', metadata,
Column('sku', String(20), primary_key=True),
Column('msrp', Numeric),
mysql_engine='InnoDB')
product_summary_table = Table( 'product_summary', metadata,
Column('sku', String(20), ForeignKey('product.sku'),
primary_key=True),
Column('name', Unicode(255)),
Column('description', Unicode(255)),
mysql_engine='InnoDB')
product_table.create(bind=engine1,checkfirst=True)
product_summary_table.create(bind=engine2,checkfirst=True)
As you can see, I'm using the same mysql account, but with two different
engines in a vertical partitioning configuration. MySQL is happy with the
foreign key relationships and creates the tables.
So... I don't understand your suggestion of not emitting ForeignKey at table
creation time. It appears to work in my basic test.
that would mean that the car_res database has access to a table called
product. As I don't know how you've configured things there's no way to know
if its the same product table as that of car_fac or a different table local
to car_res.
Shouldn't SQLA detect that the tables are vertically partitioned, treat the
ForeignKey relationships as NO-OPs to MySQL (because the tables may not be
able to *see* each other, since you could be using different MySQL accounts
with different permissions), and just use the ForeignKey definitions to help
set up the mappers properly?
SQLAlchemy knows very little about any of that, it only knows that you told it
to use engine1 for table #1, engine2 for table #2 when using the Session. The
Session chooses which engine to use based on which entity your query is
against, and that's the extent of vertical partitioning in SQLAlchemy. If
you told it to write a JOIN between table #1 and table #2, and it was using
engine1, it would happily construct a JOIN between the two tables, send it off
to the first engine, and then whatever happens happens. Normally the operation
would be rejected by the database as Table 2 doesn't exist ! but your
configuration would appear like you might have the same tables in multiple DBs.
This all also has nothing to do with the table.create() command you're using,
where again you pass an Engine to it, the create command issues CREATE TABLE,
unconditionally, including all the foreign keys you've given it. schema is
just an extra word it prepends to the table name, if specified.
How your database reacts to these schema names is all MySQL configuration
stuff.Sometimes schema means query from some other physical database,
i.e. a database link sort of thing, I know Oracle does this but not sure what
MySQL can do in that regard, sometimes it just means query this other database
partition on the same server . You'd have to work out how you'd like
databases and SQL interaction with them to work.
I think the core insight here is that SQLAlchemy is much simpler than some
people would expect. It can only emit one SQL string at a time on one
connection.If you were to first work with your configuration given two
DBAPI connections to each database, that would make it more apparent what
SQLAlchemy has available in this regard.
I have a more complex application that is basically doing the same thing as
this example code, actually using three engines to connect to three different
MySQL databases on the same server with the same account. This is probably
not the best way to do things, as schema prefixing would be better. But as
the above sample code shows, this *can* work. But my more complex app is
failing with the errno 105, which is what is confusing me. I can't figure out
the difference between my large application and this simple example, and why
the simple example works but my application does not, when they are
essentially doing the same thing. The sample code above emits SQL to MySQL
that defines the ForeignKey relationship and does not need a database.
prefix. But my big app seems to need that database. prefix. Maybe I have
the binds messed up?
I think understanding what it is you'd like to achieve would be of help here,
why vertical partitioning is one of the requirements at all.If this is
just a set of tables that happen to be in different local schemas, just use
the schema argument on each
Re: [sqlalchemy] mysql table creation errno 105 when using vertical partitioning with InnoDB
On Thu, Aug 11, 2011 at 2:12 PM, Michael Bayer mike...@zzzcomputing.comwrote:
Just FYI the Oreilly book is super, duper, extremely old and out of date.
A good read through the documentation on the SQLAlchemy site should be more
complete at this point and up to date, also stressing a whole set of new
techniques that weren't available when the OReilly book was written (it was
written against version 0.3).
OK, I think I am getting the hang of this. My working sample code in fact
didn't work when you tried to do anything with it.
I got the sample code working with two SQLite engines by dropping twophase,
which is not supported in SQLite. And I got the sample vertical partitioning
code working with two MySQL engines by removing the ForeignKey()
relationship and specifying the relationship explicitly in the mapper.
At this point, I was feeling adventurous, so I decided to try one MySQL
engine and one SQLite engine. It seems to work. Pretty cool. Example code
with comments below for anyone who might want to do this in the future. Run
python vertical_test.py init to populate the databases with data. On
successive runs, just run python vertical_test.py:
#!/usr/bin/python
import sys
from sqlalchemy import *
from sqlalchemy.orm import *
engine1 = create_engine('mysql://car:cargofast@localhost/car_res')
engine2 = create_engine('sqlite:///x.db')
engine1.echo = engine2.echo = True
metadata = MetaData()
class Product(object):
__table__ = Table( 'product', metadata,
Column('sku', String(20), primary_key=True),
Column('msrp', Numeric))
class ProductSummary(object):
__table__ = Table( 'product_summary', metadata,
Column('sku', String(20), primary_key=True),
Column('name', Unicode(255)),
Column('description', Unicode(255)),
mysql_engine='InnoDB')
# create tables in different databases:
Product.__table__.create(bind=engine1,checkfirst=True)
ProductSummary.__table__.create(bind=engine2,checkfirst=True)
# map tables to classes and define relationships between the tables:
mapper(ProductSummary, ProductSummary.__table__,
properties=dict(
product=relationship(
Product,
# Since there is no database-level foreign key
relationship,
# we need to define primaryjoin and foreign_keys
explicitly
# so SQLAlchemy understands how the tables are
connected:
primaryjoin=Product.__table__.c.sku ==
ProductSummary.__table__.c.sku,
foreign_keys=[Product.__table__.c.sku],
backref=backref('summary', uselist=False)
)
)
)
mapper(Product, Product.__table__)
# Create session, and bind each class to the appropriate engine:
Session = sessionmaker()
Session.configure(binds={Product:engine1, ProductSummary:engine2})
session = Session()
# Run with init as first argument to create tables and populate them
# with data:
# Run with init as first argument to create tables and populate them
# with data:
if __name__ == __main__ and len(sys.argv) == 2 and sys.argv[1] == init:
# create records using statements:
stmt = Product.__table__.insert()
engine1.execute(
stmt,
[
dict(sku=123, msrp=12.34),
dict(sku=456, msrp=22.12)
])
stmt = ProductSummary.__table__.insert()
engine2.execute(
stmt,
[
dict(sku=123, name=Shoes, description=Some
Shoes),
dict(sku=456, name=Pants, description=Some
Pants),
])
# or create records using ORM:
a = Product()
a.sku = blarg
session.add(a)
b = ProductSummary()
b.sku = a.sku
b.name = blarg
b.description = some blarg
session.add(b)
session.commit()
# Query records and SQLAlchemy relationships will help you to grab related
records
# from totally disparate database engines:
for p in session.query(Product):
print(PRODUCT INFO:,p.sku, p.msrp, p.summary.name,
p.summary.description)
Enjoy,
Daniel
--
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] Relationship spanning on multiple tables
Sorry if I bother again but adding some others relationships like this
spanning on 5 tables:
mapper(UserLayer, inherits=base_user_layer_mapper,
polymorphic_identity=CLASS_LAYER,
properties={
...
'lca_transport': relationship(LCATransport,
primaryjoin=and_(
user_layers.c.id_mat==lca_transport.c.id_mat,
user_layers.c.id_user_str==user_stratigraphies.c.id,
user_stratigraphies.c.id_prov==provinces.c.id,
provinces.c.id_reg==regions.c.id,
regions.c.id_mr==lca_transport.c.id_mr),
foreign_keys=(user_layers.c.id_mat,
user_layers.c.id_user_str)),
})
I can get it working at first attempt while I can't get `transm_limit` to
work here:
http://pastebin.com/mjfgPrcB
As a sidenote if I add `viewonly=True` to relationship above I get this
error:
sqlalchemy.exc.ArgumentError: Remote column 'user_stratigraphies.id'
is not part of mapping Mapper|LCATransport|lca_transport.
Specify remote_side argument to indicate which column lazy join
condition should bind.
Thanks for your support
neurino
On Tue, Aug 2, 2011 at 9:43 AM, neurino neur...@gmail.com wrote:
Sorry Michael,
there was a typo in my code, due to some wrong copy/paste or search/replace
I guess, I'm afraid.
The error I reported comes up mapping to `TransmLimit` and not `SurfaceRes`
(which is the one already mapped above).
I edited the pastebin:
http://pastebin.com/mjfgPrcB
now with:
'transm_limit': relationship(TransmLimit, single_parent=True,
uselist=False,
primaryjoin=and_(
user_stratigraphies.c.id_prov==provinces.c.id,
provinces.c.id_cz==transm_limits.c.id_cz,
user_stratigraphies.c.id_str==stratigraphies.c.id,
stratigraphies.c.id_tec==tech_elements_classes.c.id,
tech_elements_classes.c.id_tu==transm_limits.c.id_tu,
),
foreign_keys=(
user_stratigraphies.c.id_prov,
),
),
I get the error I reported:
sqlalchemy.exc.ArgumentError: Could not locate any foreign-key-equated,
locally mapped column pairs for primaryjoin condition
'user_stratigraphies.id_prov = provinces.id AND provinces.id_cz =
transm_limits.id_cz AND user_stratigraphies.id_str = stratigraphies.id AND
stratigraphies.id_tec = tech_elements_classes.id AND
tech_elements_classes.id_tu = transm_limits.id_tu' on relationship
ustratatigraphy.transm_limit. For more relaxed rules on join conditions,
the relationship may be marked as viewonly=True.
Also there's the strange `viewonly=True` behavior that breakes
`UserStratigraphy.surface_res` relationship if uncommented.
Thanks for your patience.
neurino
On Mon, Aug 1, 2011 at 11:14 PM, Michael Bayer
mike...@zzzcomputing.comwrote:
'transm_limit': relationship(SurfaceRes, single_parent=True,
#uselist=False,
#primaryjoin=and_(
#user_stratigraphies.c.id_prov==provinces.c.id,
#provinces.c.id_cz==transm_limits.c.id_cz,
#user_stratigraphies.c.id_str==stratigraphies.c.id,
#stratigraphies.c.id_tec==tech_elements_classes.c.id,
#tech_elements_classes.c.id_tu==transm_limits.c.id_tu,
#),
this fails because you aren't joining to the table to which SurfaceRes is
mapped, surface_res.
On Aug 1, 2011, at 7:02 AM, neurino wrote:
I'm trying to get some relationships spanning on multiple tables (4 or
5).
While I got the `4 tables` one working on first attempt (I was
surpized I could...) I can't get the `5 tables` one to work while the
code is almost the same.
Moreover with the first relationship if I add adding `viewonly=True`
initialization fails.
I just need these to get (not even set) some values with convenience
of SA attributes.
I published a working example on pastebin:
http://pastebin.com/RsZ6GCRq
I hope someone can sort out this thing, thank you.
Greetings
neurino
--
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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to
