[sqlalchemy] Re: Compound Join
Michael, Thanks for the quick reply. The one thing I hadn't tried was doing a separate join for each of the primary table joins and once I did that it worked. It dawned on me this morning that I forgot to mention I was using ORM syntax, but you got the answer out before I had a chance to update the original post. Thanks again, - Eric On Jan 27, 7:02 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 27, 2011, at 8:06 PM, Eric N wrote: I'm trying to construct a query where in the from clause I would end up with something like SELECT foo FROM table1 JOIN table2 ON table1.id1 = table2.id1 JOIN table3 ON table1.id1=table3.id1 JOIN table4 ON table2.id2=table4.id2 AND table3.id3=table4.id3 I have tried various join combinations but I can only get it to join table4 to table2 or table 3, not both. the and_() function would be used as the onclause: from sqlalchemy import and_ select = select.select_from( table1.join(table2, table2.c.id1==table1.c.id1).\ join(table3, table1.c.id1==table3.c.id1).\ join(table4, and_(table2.c.id2==table4.c.id2, table3.c.id3==table4.c.id3)) ) You didn't say if you were using ORM or expression language, that above is expression language. Same idea applies to ORM, use and_() in the ON clause. -- 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] Compound Join
I'm trying to construct a query where in the from clause I would end up with something like SELECT foo FROM table1 JOIN table2 ON table1.id1 = table2.id1 JOIN table3 ON table1.id1=table3.id1 JOIN table4 ON table2.id2=table4.id2 AND table3.id3=table4.id3 I have tried various join combinations but I can only get it to join table4 to table2 or table 3, not both. Thanks, - Eric -- 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: Accessing multiple databases with same schema
I had a similar issue that I resolved by creating a set_data_model
function that when I called in it would set some global variable to
the table objects based on the product passed in to the function. I'm
using multiple schemas in a Postgres database and wanted to try to
limit the number of connections and this was the best way to do that.
The other benefit is that none of my external scripts need to know
anything about the schemas or keeping track of different sessions. The
basic idea is shown below.
Table1 = None
table1_table = None
Table2 = None
table2_table = None
def set_data_model(product):
global Table1
global table1_table
global Table2
global table2_table
if product == 'A':
Table1 = productA.Table1
table1_table = productA.table1_table
Table2 = productA.Table2
table2_table = productA.table2_table
if product == 'B':
...
On Dec 2, 11:28 am, g3 N tunetog3...@gmail.com wrote:
Thanks for the suggestions.
I'll try them.
Thanks,
Gayatri
On Thu, Dec 2, 2010 at 8:19 PM, Nagy Viktor viktor.n...@toolpart.hu wrote:
I would say that if no connection between the databases are required then
two sessions are pretty fine.
e.g no query like db1.table1 join db2.table2 exists
otherwise, it might still work to use one session by importing/definig your
table classes twice, and adding all of them to your session with
Session.configure(binds={db1.table1: engine1, db2.table1: engine2})
On Thu, Dec 2, 2010 at 3:29 PM, gayatri tunetog3...@gmail.com wrote:
Hello All,
We have a client-server application. On server side we have two
databases with same set of tables.
Based on the client request, server has to get data from the
corresponding database.
So, in this scenario, I would like to know which of the following
approaches is better?
1.To have two sessions and have a lookup based on the client request.
2.Have a single session and use sharding.
Thanks,
Gayatri
--
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.comsqlalchemy%2bunsubscr...@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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@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 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] Dynamically changing the model
This problem involves Pylons as well, but I believe the problem
involves how I'm using SQLAlchemy and therfore I'm posting here first.
I have a peculiar situation where I am trying to dynamically flip back
and forth between data models and running into problems. I am using
PostgreSQL with multiple identically named tables in different schemas
with a single master table in the public schema with the tables linked
by a serial foreign key.
Here are the basics:
public.master_table.master_serial = producta.sub_table.master_serial
public.master_table.master_serial = productb.sub_table.master_serial
All tables are defined using the declarative model and use
__table_args__ = {'schema':'schema name'}. I have tried importing the
master_table into the __init__ functions of the separate product
models and leaving it static but just re-mapping the foreign key to
the product each time I switch products. Neither method works.
Inside of my processing code I call function set_product_model which
does a simple assignment operation:
model.master_table = producta.master_table
model.sub_table = producta.sub_table
Everything works beautifully until I try to go back and use a product
that has already been processed. So in a sequence of:
1) process producta file
2) process productb file
3) process productc file
4) process producta file
step 4 fails when I try to append the sub_table object to the
master_table object with an AssertionError(Attribute sub_table on
class MasterTable doesn't handle objects of type class SubTable
Thanks for the help!
--
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] OID usage
I apologize in advance for the size of this post, but I want to
provide as much info as possible. I work for a large company and I am
trying to use SqlAlchemy to implement a cleaner interface to one of
our databases.
The database is Sql Server 2005, the app runs on RHEL 3 under Python
2.5. I am using SqlAlchemy 0.4.0beta5.
Our corporate DBAs use OIDs, Binary(8) fields as key fields on our
tables. I am pulling this information for use by various applications
and need to be able to query the database based on these keys.
I have tried a couple different means of pulling the keys and re-
querying, and have yet to have a query succeed.
Here is the custom class I created to try and have the value display
in an intermediate format that is legible within the app and then
converted back when it comes time to query based on the key.
dbutils.py:
---
class OID(types.TypeDecorator):
Uninterprets the OID key value into a readable string
impl = types.Binary
def convert_bind_param(self, value, engine):
bytes = []
for x in range(0, len(value), 2):
bytes.append( chr( int (value[x:x+2], 16 ) ) )
return %s % ''.join( bytes )
def convert_result_value(self, value, engine):
return %s % ''.join([%02X % ord(x) for x in value])
This is the mapper file for the table in question. Notice the
commented out line for using my custom class and the line where I
tried to use the built-in Binary type.
specsections.py
# Define spec_configuration_options table
spec_sections_table = Table('spec_sections', metadata,
#Column( 'section_OID', dbutils.OID(), primary_key=True ),
Column( 'section_OID', types.Binary(8), primary_key=True ),
Column( 'product_name', types.String(30), nullable=False ),
Column( 'density_value', types.String(8), nullable=False ),
Column( 'datasheet_type', types.String(50), nullable=False ),
Column( 'temp_range_min', types.String(10), nullable=False ),
Column( 'temp_range_max', types.String(10), nullable=False ),
Column( 'section_name', types.String(100), nullable=False ),
Column( 'design_id', types.String(50), nullable=True ),
Column( 'interface_type', types.String(50), nullable=True)
)
class SpecSections(object):
def __init__(self, section_OID, product_name, density_value,
datasheet_type,
temp_range_min, temp_range_max, section_name,
design_id, interface_type):
self.section_OID = section_OID
self.product_name = product_name
self.density_value = density_value
self.datasheet_type = datasheet_type
self.temp_range_min = temp_range_min
self.temp_range_max = temp_range_max
self.section_name = section_name
self.design_id = design_id
self.interface_type = interface_type
def __repr__(self):
return ::.join([self.section_OID, self.product_name,
self.density_value, self.datasheet_type,
self.temp_range_min,
self.temp_range_max, self.section_name, self.design_id,
self.interface_type] )
specsections_mapper = Session.mapper(SpecSections,
spec_sections_table)
Here is an example of a properly interpreted row using the dbutils.OID
class:
08C82B7C6A844743::SDRAM::64Mb::Marketing::0C::70C::DC Electrical
Characteristics
Here is the binding statement being generated by SqlAlchemy:
2007-09-27 13:32:12,444 INFO sqlalchemy.engine.base.Engine.0x..cL
{'spec_section_symbols_section_OID': '\x08\xc8+|j\x84GC'}
I am not receiving any type of error, just an empty result set. I have
verified that the OID is being interpreted correctly by running an
interactive sql statement on the interpreted value shown.
I know in Perl I have to format the query without quotes or I don't
get any results. Something like this:
select * from table where section_OID=0x08C82B7C6A844743
I'm wondering if SqlAlchemy is quoting the parameter when it is bound
or if anyone has any other suggestions as to why I am not getting any
results.
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
