[sqlalchemy] Re: How can I do this relation?
On Nov 30, 3:15 pm, Alvaro Reinoso alvrein...@gmail.com wrote: One region can just contain one channel, but one channel could be in many regions. A region will never be accessed by a channel. However, Channel could be accessed by a region, so I need that relationship in region. Is that relationship OK? If not, how can I make it? It's not entirely clear what you are trying to do here. It sounds like a classic one-to-many relationship without a backref. All you have to do is get rid of the backref=regions bit and you should be good to go. BTW, you probably want to drop the idea of A is accessed by/through B. It doesn't quite fit with what's really going on in the relational world. Instead, think of how two independent objects may (or may not) be related to one another. -- 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] Managing transactions from Session using raw_connections
Hi everybody,
we have the problem that transactions embedded in
'with Session.begin()'
are not committing statements made in raw connections. However
'with engine.begin()' (in 0.5.8)
works fine.
The reason we need to use Session.begin() is that we want to cover
transactions over multiple engines. Raw connections are needed because
we have to run oracle plsql functions and hence need to call
'cursor.call(...)'.
Below you find a simple example. It doesn't call a stored procedure,
but the problem is the same.
By using 'strategy=threadlocal' I would have assumed that I always
operate on the same low level db connection. This seems to be true
with 'engine.begin()', but not with 'session.begin()'.
Any idea why?
Thanks for your help,
Ralph
ps: Why did the 'engine.begin()' context manager disappear in 0.6.x?
---
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine('sqlite:///s.db', strategy='threadlocal',
echo=True)
Session = scoped_session(sessionmaker(autoflush=True, autocommit=True))
engine.execute('create table testtable (id int, name varchar(20))')
engine.execute(insert into testtable (id, name) values (1, 'otto'))
engine.execute(insert into testtable (id, name) values (2, 'gustav'))
with Session.begin():# does not work
#with engine.begin():# does work !
engine.execute(update testtable set name='ottox28' where id=1)
# the next line returns a low level DBAPI connection obj:
raw_conn = engine.contextual_connect().connection
cur = raw_conn.cursor()
cur.execute(update testtable set name='gustav2' where id=2)
(if you look into s.db with sqlite3 record id=2 is still 'gustav')
--
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] dynamic modification of foreign keys sqlalchemy
Hi!
I'm creating a structure of elements dynamically with declarative,
and I'm not sure if I can modify the mapper to get the new elements
related with a FK. Sorry about my english, is very poor.
This is my function class factory:
def nuevaEtiqueta(self, nombre_etiqueta):
etiqueta = nombre_etiqueta.lower().strip()
nombre_etiqueta = etiquetas_ + etiqueta
dicc = {'__tablename__':nombre_etiqueta,'__table_args__ ':
{'autoload':True}}
id = Column(Integer,primary_key=True);dicc.update({'id':id})
tipo = Column(Text);dicc.update({'tipo':tipo})
contenido_texto =
Column(Text);dicc.update({'contenido_texto':contenido_texto})
contenido_binario =
Column(LargeBinary);dicc.update({'contenido_binario':contenido_binario})
fichero = Column(Text);dicc.update({'fichero':fichero})
ref_elemento = Column(Integer,ForeignKey('elementos_'+etiqueta
+'.id'));dicc.update({'ref_elemento':ref_elemento})
def __init__(self, diccio):
self.tipo = diccio[tipo]
if self.tipo == BIN:
self.contenido_binario = diccio[contenido_binario]
self.fichero = diccio[fichero]
else:
self.contenido_texto = diccio[contenido_texto]
dicc.update({'__init__':__init__})
def __repr__(self):
aux = ETIQUETA
if self.tipo == 'BIN':
return aux + BINARIA
else:
return aux + %s - CONTENIDO: %s %
(self.tipo,self.contenido_texto)
dicc.update({'__repr__':__repr__})
clase_etiqueta = type(str(nombre_etiqueta),(Base,),dicc)
self.etiquetas_map.update({nombre_etiqueta:clase_etiqueta})
Now, when a new class is created, I want the mapper adds the FK
I'm trying to do this with session mappers but with no success.
Can you help?
Thanks
--
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.
Re: [sqlalchemy] Managing transactions from Session using raw_connections
On Dec 1, 2010, at 5:50 AM, Ralph Heinkel wrote:
Hi everybody,
we have the problem that transactions embedded in
'with Session.begin()'
are not committing statements made in raw connections. However
'with engine.begin()' (in 0.5.8)
works fine.
The reason we need to use Session.begin() is that we want to cover
transactions over multiple engines. Raw connections are needed because we
have to run oracle plsql functions and hence need to call 'cursor.call(...)'.
Below you find a simple example. It doesn't call a stored procedure, but the
problem is the same.
By using 'strategy=threadlocal' I would have assumed that I always operate on
the same low level db connection. This seems to be true with
'engine.begin()', but not with 'session.begin()'.
Any idea why?
Thanks for your help,
The Session.begin() statement doesn't touch any of its engines until it
accesses one of them in order to procure a connection, so engine.execute()
isn't related to that interaction, hence threadlocal not really of use when
the Session is used to manage transactions. See
http://www.sqlalchemy.org/docs/core/connections.html#using-the-threadlocal-execution-strategy
for details.
Here you'd use Session.execute() and Session.connection() to get at the
Connection you'd normally get from engine.contextual_connect()
(http://www.sqlalchemy.org/docs/orm/session.html#using-sql-expressions-with-sessions)
.execute() and connection() accept a mapper argument for the case where
individual engines are associated with individual mappers, and both ultimately
call get_bind() which you can override via subclass if desired to accept other
kinds of arguments.
Ralph
ps: Why did the 'engine.begin()' context manager disappear in 0.6.x?
---
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine('sqlite:///s.db', strategy='threadlocal', echo=True)
Session = scoped_session(sessionmaker(autoflush=True, autocommit=True))
engine.execute('create table testtable (id int, name varchar(20))')
engine.execute(insert into testtable (id, name) values (1, 'otto'))
engine.execute(insert into testtable (id, name) values (2, 'gustav'))
with Session.begin():# does not work
#with engine.begin():# does work !
engine.execute(update testtable set name='ottox28' where id=1)
# the next line returns a low level DBAPI connection obj:
raw_conn = engine.contextual_connect().connection
cur = raw_conn.cursor()
cur.execute(update testtable set name='gustav2' where id=2)
(if you look into s.db with sqlite3 record id=2 is still 'gustav')
--
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.
--
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.
Re: [sqlalchemy] finding if a table is already join in a query
On Dec 1, 2010, at 1:28 AM, James Neethling wrote:
if you would like multiple references to Address to all work from the same
join, your routine needs to track which entities have already been joined as
a destination in a separate collection:
def search(columns):
already_joined = set()
...
if class_ not in already_joined:
q = q.join(destination)
already_joined.add(class_)
Hi Michael,
Thank you for the quick response.
Unfortunately we don't always know where this query comes from (my
example was a little contrived :( )
Is there any way to get the tables that are currently in the join for a
query?
You can iterate through q._from_obj(), and for each object that is a join(),
recursively descend through j.left and j.right looking for Table objects.
Table objects can be embedded in subqueries and alias objects too but I'm
assuming your query buildup here is simple enough that gray areas like that
aren't expected.
If it were me, I'd not be passing a raw Query around, I'd have it wrapped
inside a facade that is doing the abovementioned tracking of important state
explicitly (and also ensuring that those more grayish areas aren't occurring
with this particular Query). That way any other interesting facts about the
query as built so far can be tracked as well. Also easier to unit test.
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
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.
--
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.
--
James Neethling
Development Manager
XO Africa Safari
(t) +27 21 486 2700 (ext. 127)
(e) jam...@xoafrica.com
--
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.
--
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
Re: [sqlalchemy] dynamic modification of foreign keys sqlalchemy
On Dec 1, 2010, at 5:40 AM, slothy wrote:
Hi!
I'm creating a structure of elements dynamically with declarative,
and I'm not sure if I can modify the mapper to get the new elements
related with a FK. Sorry about my english, is very poor.
This is my function class factory:
def nuevaEtiqueta(self, nombre_etiqueta):
etiqueta = nombre_etiqueta.lower().strip()
nombre_etiqueta = etiquetas_ + etiqueta
dicc = {'__tablename__':nombre_etiqueta,'__table_args__ ':
{'autoload':True}}
id = Column(Integer,primary_key=True);dicc.update({'id':id})
tipo = Column(Text);dicc.update({'tipo':tipo})
contenido_texto =
Column(Text);dicc.update({'contenido_texto':contenido_texto})
contenido_binario =
Column(LargeBinary);dicc.update({'contenido_binario':contenido_binario})
fichero = Column(Text);dicc.update({'fichero':fichero})
ref_elemento = Column(Integer,ForeignKey('elementos_'+etiqueta
+'.id'));dicc.update({'ref_elemento':ref_elemento})
def __init__(self, diccio):
self.tipo = diccio[tipo]
if self.tipo == BIN:
self.contenido_binario = diccio[contenido_binario]
self.fichero = diccio[fichero]
else:
self.contenido_texto = diccio[contenido_texto]
dicc.update({'__init__':__init__})
def __repr__(self):
aux = ETIQUETA
if self.tipo == 'BIN':
return aux + BINARIA
else:
return aux + %s - CONTENIDO: %s %
(self.tipo,self.contenido_texto)
dicc.update({'__repr__':__repr__})
clase_etiqueta = type(str(nombre_etiqueta),(Base,),dicc)
self.etiquetas_map.update({nombre_etiqueta:clase_etiqueta})
Now, when a new class is created, I want the mapper adds the FK
I'm trying to do this with session mappers but with no success.
you can add a Foreign key to a Table using:
table.append_constraint(ForeignKeyConstraint(('local_col', ),
('remote_table.remote_col', ))
you'd build functionality which does this at the same time as creating mappers.
Can you help?
Thanks
--
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.
--
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] Weird behavior
Hello, I have a system to manage users in my application, but I'm getting some troubles with it. Every user has to belong to a group of users. One user can only be in one group. I have those tables (inheriting from rdb.Model is basically the same thing than using sqlalchemy's declarative model) class User(rdb.Model): Represents the user rdb.metadata(metadata) rdb.tablename(users) id = Column(id, Integer, primary_key=True) name = Column(name, String(50)) userGroup = relationship(UserGroup, uselist=False) . class UserGroup(rdb.Model): Represents a group of users with the same features rdb.metadata(metadata) rdb.tablename(user_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(50)) users = relationship(User, order_by=User.name, cascade=all, delete, delete-orphan, backref=user_groups) I have a script which migrate users from a pre-existing Zope DB (object-oriented): def migrateUsers(): Migrate all the users to the database session = rdb.Session() rScreens = session.query(Screen).all() rUserGroups = session.query(UserGroup).all() . for oldUser in grok.getSite()['Users'].values(): user = User() .. for newGroup in rUserGroups: if newGroup.title == superadmins: newGroup.users.append(user) .. return When I execute the script, the user_groups are properly created and the users are properly added to the user_groups they should belong to, but I get empty group entries in the database, and I don't know why I have made some tests, and I've realized that I get an empty entry (an empty user_group) every time I try to add a user to a user_group, but I don't know what is causing this behavior. Any hint will be appreciated. -- 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.
Re: [sqlalchemy] Weird behavior
On Dec 1, 2010, at 5:46 PM, Alvaro Reinoso wrote: Hello, I have a system to manage users in my application, but I'm getting some troubles with it. Every user has to belong to a group of users. One user can only be in one group. I have those tables (inheriting from rdb.Model is basically the same thing than using sqlalchemy's declarative model) class User(rdb.Model): Represents the user rdb.metadata(metadata) rdb.tablename(users) id = Column(id, Integer, primary_key=True) name = Column(name, String(50)) userGroup = relationship(UserGroup, uselist=False) . class UserGroup(rdb.Model): Represents a group of users with the same features rdb.metadata(metadata) rdb.tablename(user_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(50)) users = relationship(User, order_by=User.name, cascade=all, delete, delete-orphan, backref=user_groups) I have a script which migrate users from a pre-existing Zope DB (object-oriented): def migrateUsers(): Migrate all the users to the database session = rdb.Session() rScreens = session.query(Screen).all() rUserGroups = session.query(UserGroup).all() . for oldUser in grok.getSite()['Users'].values(): user = User() .. for newGroup in rUserGroups: if newGroup.title == superadmins: newGroup.users.append(user) .. return When I execute the script, the user_groups are properly created and the users are properly added to the user_groups they should belong to, but I get empty group entries in the database, and I don't know why I have made some tests, and I've realized that I get an empty entry (an empty user_group) every time I try to add a user to a user_group, but I don't know what is causing this behavior. theres no instantiation of UserGroup indicated above so no indication of what would be creating extra group rows in your database. Any hint will be appreciated. -- 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. -- 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.
