[sqlalchemy] Re: sqlalchemy with turbogears and mapper-part3 relation and flow control
Hello,
ok so I got the connection, select statements working using
turbogears, assign_mapper and sqlalchemy.
Now i need to work out the relation.
User has a one to many relation with address, email, accounts.
In reverse: address, email and accounts have many to one...
#here is my table
user_table = sqlalchemy.Table('user', metadata, autoload=True)
uaddress_table = sqlalchemy.Table('uaddress', metadata, autoload=True)
uemail_table = sqlalchemy.Table('uemail', metadata, autoload=True)
uaccounts_table = sqlalchemy.Table('uaccounts', metadata, autoload=True)
#my python class
class User(object):
pass
class Address(object):
pass
class Email(object):
pass
class Accounts(object):
pass
#mapper for table to class
#1 to many
The Primary key on:
User is 'User_Sid'
on Address is 'User_Sid' and 'Address_Sid'
on Email is 'User_Sid' and 'email_Sid'
on Accounts is 'User_Sid' and 'accounts_Sid'
Is this done correctly?
usermapper=assign_mapper(session.context,User,user_table, properties={
'address':sqlalchemy.relation(Address),
'email':sqlalchemy.relation(Email),
'accounts':sqlalchemy.relation(Accounts),
})
Do I have to create a relation on these mappers?
uaddressmapper=assign_mapper(session.context,Address,uaddress_table)
uemailmapper=assign_mapper(session.context,Email,uemail_table)
uaccountsmapper=assign_mapper(session.context,Account,uaccount_table)
What if the relation was many to many? How would the properties={??} look like?
In controller.py how do I use my mapper with relations?
u=model.User()
Do I do:
a=u.address()
or
a=model.address()
u.address=a
How do I save them? Do I save one by one? u.flush() then a.flush() or
u.flash() saves it all???
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy with turbogears and mapper-part2 connect and select
Lukasz Szybalski ha scritto: Got another error here. but I guess its fixed in a newer version of sqlalchemy via ticket 482 Yes. I wasn't aware of 482 because I usually try to avoid table names that _must_ be escaped (mixed caps, reserved words, etc). put assign_mapper() in place of mapper() Are these two the same? assign_mapper in TG, is the same as mapper in SA? No, SA provides both mapper and assign_mapper. Assign mapper does the work of the former, plus attached several methods to your mapped classes and binds a default session context. SA 0.4 deprecates this and uses a scoped_session, but you should not worry about it now. How can I iterate through myuser fields? It's columns, btw. If you call them fields, baby Jesus cries for column in myuser.c.keys(): print getattr(myuser,column) Thanks for the help. I have moved ahead in these few emails more then I was able in a week. I know how it feels ;-) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy with turbogears and mapper-part2 connect and select
Lukasz Szybalski ha scritto:
bind_meta_data()
users_table = Table('users', metadata, autoload=True)
class Users(object):
pass
usersmapper=mapper(Users,users_table)
assign_mapper() in place of mapper()
mysession=session.query(Users)
1. What would be the code from now on to query all Users? Does
'mysession' have a connection to a database already?
Yes, if you used assign_mapper instead, your model's classes will be
implicitly bound to the session context.
So...
2. How do I select a user where User_Sid=100?
What is the schema of the table?
If User_Sid is the primary key:
User.get(100)
If it's not:
User.select_by(User_Sid=100)
Why doesn't this work?
jj=[]
for users in session.query(Users):
jj.append(users.Users_Sid)
ehm, weird use of plural for a loop variable.
You are using an explicit session here, you don't need to do that in
TurboGears since the classes have an implicit session bound to the
request, that it cleaned after each served page.
Anyway, it should work more or less (I note Users_Sid in place of User_sid)
What is the simples way to query my database to get user with user_sid=100?
What is the simples way to query my database to get user
last_name='Smith' with user_sid=100?
Again, are filtering by both last_name and user_sid? So I suppose
user_sid is not the primary key after all.
User.select_by(User_sid=100, last_name='Smith')
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy with turbogears and mapper-part2 connect and select
hello -
you generally use mapper() and relation() to set up how you'd like
your classes to correspond to your table relationships. as far as
compound keys, if they are defined with a primary key constraint you
shouldn't have to worry about them.
---
Ok. So we are using mapper() function to map a python class to a database.
In my model.py I have
#Initial import packages...
from sqlalchemy import *
from turbogears.database import metadata, session,bind_meta_data
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.activemapper import *
from turbogears import widgets, validators
bind_meta_data()
users_table = Table('users', metadata, autoload=True)
class Users(object):
pass
usersmapper=mapper(Users,users_table)
mysession=session.query(Users)
1. What would be the code from now on to query all Users? Does
'mysession' have a connection to a database already?
2. How do I select a user where User_Sid=100?
Why doesn't this work?
jj=[]
for users in session.query(Users):
jj.append(users.Users_Sid)
What is the simples way to query my database to get user with user_sid=100?
What is the simples way to query my database to get user
last_name='Smith' with user_sid=100?
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy with turbogears and mapper-part2 connect and select
ok...correct me if I'm wrong.
#we start by importing
from turbogears.database import metadata, session,bind_meta_data
from sqlalchemy.ext.assignmapper import assign_mapper
from turbogears import widgets, validators
import sqlalchemy
#Then we bound to database
bind_meta_data()
#create a table object
users_table = sqlalchemy.Table('users', metadata, autoload=True)
or you can use:
tables = []
for name in engine.execute(SHOW TABLES):
tables[name] = sa.Table(name, metadata, autoload=True)
Got another error here. but I guess its fixed in a newer version of
sqlalchemy via ticket 482
File /usr/lib/python2.4/site-packages/sqlalchemy/databases/mysql.py,
line 320, in reflecttable
raise exceptions.NoSuchTableError(table.name)
sqlalchemy.exceptions.NoSuchTableError: ('users',)
Next...
class Users(object):
pass
usersmapper=assign_mapper(Users,users_table)
No need to create a session because:
assign_mapper on TurboGears Is using SessionContext.
Yes, if you used assign_mapper instead, your model's classes will be
implicitly bound to the session context.
put assign_mapper() in place of mapper()
Are these two the same?
assign_mapper in TG, is the same as mapper in SA?
2. How do I select a user where User_Sid=100?
What is the schema of the table?
User_Sid - Primary key, int
first_name
last_name
If User_Sid is the primary key:
User.get(100)
If it's not:
User.select_by(User_Sid=100)
or
User.select_by(User_sid=100, last_name='Smith')
Ok, now I have the object for my User.get(100) query.
quote.model.User object at 0x40f039ec
I can display it by
myuser=User.get(100)
myuser.USERS_SID
myuser.LAST
myuser.FIRST
How can I iterate through myuser fields?
This doesn't work:
for field in User.get(100):
print field
TypeError: iteration over non-sequence
Thanks for the help.
I have moved ahead in these few emails more then I was able in a week.
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy with turbogears and mapper
hello - you generally use mapper() and relation() to set up how you'd like your classes to correspond to your table relationships. as far as compound keys, if they are defined with a primary key constraint you shouldn't have to worry about them. if you need to tell a mapper about some other columns that compose its primary key other than what the actual table has set up as its primary key, you can use the primary_key keyword option on mapper(), such as mapper(SomeClass, sometable, primary_key=[mytable.c.col1]) As for foreign key relations, these are usually represented at the ORM level using the relation() function. the easiest ones are the one-to-many, many-to-one, and many-to-many relationships, which most of your foreign keys are probably defining. for 0.3 these are described here: http://www.sqlalchemy.org/docs/03/datamapping.html#datamapping_relations_onetomany http://www.sqlalchemy.org/docs/03/datamapping.html#datamapping_morerelations a more complex relationship is a self-referential relationship, otherwise known as adjacency list. use this when a single table contains a foreign key relationship pointing to itself: http://www.sqlalchemy.org/docs/03/adv_datamapping.html#advdatamapping_selfreferential finally, the other common mapping is inheritance, usually joined table inheritance, where a set of rows are referenced by a base table, but each row also contains additional properties that are added by one of several joined tables, but only one of those tables for each parent row: http://www.sqlalchemy.org/docs/03/adv_datamapping.html#advdatamapping_inheritance_joined good luck ! - mike On Aug 16, 11:59 am, Lukasz Szybalski [EMAIL PROTECTED] wrote: Hello, I am setting up a website with existing database using turbogears. Database is in mysql. I am using sqlalchemy but I'm having problems with choosing the mapper? There is around 20 tables with a lot of foreign key, compound keys and alternative keys. Which mapper do I use? Can you point me to the documentation for that as well? 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
