[sqlalchemy] Re: modifying a field
In this example I used:
DB.password = 'hello' where passwod is the column name. Can I pass
the column name in a variable? It does not seem that I could. I have
tried different ways but none seems to work.
Thanks.
On Jun 20, 5:18 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 20, 2010, at 5:53 PM, Aref wrote:
db = SqlSoup('sqlite:///c:\\tutorial.db3')
db_dynamic = 'tf_user'
DB = db.entity(db_dynamic)
print DB
ColHeader = DB.c.keys()
conn = db.connection()
#modify a field
DB.password = 'hello'
db.flush()
It appears you're using a method called entity(), which would return a handle
to the current table called tf_user in this case. To modify a row, you
need to load the row first. You do this with filter(), first(), one(),
etc. It seems here you're tacking on the word hello to the query object
itself which would have no effect. Examples of loading actual rows
athttp://www.sqlalchemy.org/docs/reference/ext/sqlsoup.html?highlight=s
The tutorial here seems to use the special variable _, which is unfortunate
(I didn't write any of this), but it implies the row that was previously
loaded via one(). thats the thing you change.
#get the table data
data = select([db.tf_user],)
listdata=[]
for row in conn.execute(data):
row = [%s % el for el in row]
listdata.append(row)
print listdata
which is supposed to modify the value of the password field in a
database to 'hello'. However, when I examine the database nothing has
changed, the old value is still there. What am I doing wrong? What is
the best way to update or modify fields? I can't seem to find anything
substantial regarding SQLSoup.
you need to commit() the transaction. The current 0.6 docs describe
this:http://www.sqlalchemy.org/docs/reference/ext/sqlsoup.html?highlight=s...quoted
text -
- Show quoted text -
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.-Hide quoted text -
- Show quoted text -
--
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
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 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] Referential integrity actions are not doing what I want
Hi,
I have a table of items, where each item can be owned by one
person, and held by someone else. I want the owner to be
compulsory (not nullable), and the holder to be optional (nullable).
To model this I have two tables, one for contacts and one for items.
The item table has two fields owner_id and holder_id. Bot are
references to the contact table and have the on delete rule set to
restrict and set null respectively.
The problem is that when I want to delete the contact attached to the
holder_id column, it seems that SA tries to set *both* references to
null. It should not do this! For example: If you have an item which
has an owner_id 1 and a holder_id 2, then deleting the contact
with ID 2 will cause the following query:
'UPDATE item SET owner_id=%(owner_id)s, holder_id=%(holder_id)s WHERE
item.item_id = %(item_item_id)s' {'holder_id': None, 'item_item_id':
10, 'owner_id': None}
First of all, *why* is SA issuing this query at all? A delete query
would suffice. The ref. integrity should be handled by the DB,
shouldn't it? More importantly, it updates both owner_id and
holder_id. But as previously said, owner_id=1 and holder_id=2. So
deleting contact #2 should only trigger - if at all - an update query
to set holder_id to null.
Any ideas as to what I am doing wrong here?
Here's my model for those two tables:
contact = Table( 'contact', metadata,
Column( 'contact_id', Integer, primary_key=True,
nullable=False),
Column( 'label', Unicode(64), nullable=False ),
)
item = Table( 'item', metadata,
Column( 'item_id', Integer, nullable=False, primary_key=True ),
Column( 'label', Unicode(64) ),
Column( 'barcode', String(64) ),
Column( 'comment', UnicodeText() ),
Column( 'owner_id', Integer, ForeignKey('contact.contact_id',
onupdate=CASCADE, ondelete=RESTRICT), nullable=False),
Column( 'holder_id', Integer, ForeignKey('contact.contact_id',
onupdate=CASCADE, ondelete=SET NULL), nullable=True),
)
--
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] problem with select([table.c.column1, table.c.column1])
On Jun 21, 2010, at 7:52 PM, Jon Nelson wrote:
On Mon, Jun 21, 2010 at 5:55 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
not a bug, that's the correct behavior.
Can you explain how it is correct? I asked for two things but I only
got one. Why should it matter if they happen to be the /same/ thing?
The expression language and result systems target result row columns based on
name, not position. Additionally, select() objects are themselves
selectable, like a Table, and contain a .c. attribute which provides access
to its columns clause by name. Both of these systems would not be possible if
the construct did not represent names uniquely.
If you'd like the same column to be represented twice in the columns clause,
you can use labels. select([table.c.col1.label('a'), table.c.col1.label('b']).
The possibility of multiple result elements with the same name is sometimes
mentioned as a shortcoming in SQL, in that its not truly relational
(relational tuples require that every element be uniquely named).
SQLAlchemy's pushing of SQL tuples to be slightly more relational helps a
great deal on the Python side.In any case, I'm not aware of any use case
that benefits from having the same name multiple times in a result row.
As far as I asked for two things, from the select() perspective you only
asked for one thing, twice - column c1.We wouldn't make the same critique
of a set() or dict(), where it's natural that putting the same thing in
multiple times has no effect - in this case the select() list is mostly like a
dict.
In this particular case, I need it for what ultimately becomes an
insert into () select (this, that, theother, col1, col1)
On Jun 21, 2010, at 5:35 PM, Jon Nelson wrote:
I'm encountering a problem with SA 0.6.1
I have a select statement which selects the same column twice (and
some other stuff, too).
However, when the query is run (or printed), the column only shows up once.
This seems like a bug.
Example:
from sqlalchemy import create_engine, select, Table, Column, INT, MetaData
e = create_engine('sqlite://')
meta = MetaData(bind=e)
t = Table( 'test', meta, Column( 'c1', INT() ) )
meta.create_all()
print select([t.c.c1, t.c.c1])
I expected:
SELECT test.c1, test.c1
FROM test
I got:
SELECT test.c1
FROM test
--
Jon
--
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.
--
Jon
--
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] Referential integrity actions are not doing what I want
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of exhuma.twn
Sent: 22 June 2010 14:27
To: sqlalchemy
Subject: [sqlalchemy] Referential integrity actions are not
doing what I want
Hi,
I have a table of items, where each item can be owned by one
person, and held by someone else. I want the owner to be
compulsory (not nullable), and the holder to be optional (nullable).
To model this I have two tables, one for contacts and one for items.
The item table has two fields owner_id and holder_id. Bot are
references to the contact table and have the on delete rule set to
restrict and set null respectively.
The problem is that when I want to delete the contact attached to the
holder_id column, it seems that SA tries to set *both* references to
null. It should not do this! For example: If you have an item which
has an owner_id 1 and a holder_id 2, then deleting the contact
with ID 2 will cause the following query:
'UPDATE item SET owner_id=%(owner_id)s, holder_id=%(holder_id)s WHERE
item.item_id = %(item_item_id)s' {'holder_id': None, 'item_item_id':
10, 'owner_id': None}
First of all, *why* is SA issuing this query at all? A delete query
would suffice. The ref. integrity should be handled by the DB,
shouldn't it? More importantly, it updates both owner_id and
holder_id. But as previously said, owner_id=1 and holder_id=2. So
deleting contact #2 should only trigger - if at all - an update query
to set holder_id to null.
Any ideas as to what I am doing wrong here?
There are various ways of configuring SA's behaviour when you delete
objects with relationships. You may want to refer to these pages in the
docs:
http://www.sqlalchemy.org/docs/mappers.html#using-passive-deletes
http://www.sqlalchemy.org/docs/session.html#cascades
http://www.sqlalchemy.org/docs/ormtutorial.html#deleting
Hope that helps,
Simon
--
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] problem with select([table.c.column1, table.c.column1])
On Tue, Jun 22, 2010 at 8:55 AM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 21, 2010, at 7:52 PM, Jon Nelson wrote:
On Mon, Jun 21, 2010 at 5:55 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
not a bug, that's the correct behavior.
Can you explain how it is correct? I asked for two things but I only
got one. Why should it matter if they happen to be the /same/ thing?
The expression language and result systems target result row columns based on
name, not position. Additionally, select() objects are themselves
selectable, like a Table, and contain a .c. attribute which provides access
to its columns clause by name. Both of these systems would not be possible
if the construct did not represent names uniquely.
If you'd like the same column to be represented twice in the columns clause,
you can use labels. select([table.c.col1.label('a'),
table.c.col1.label('b']).
Aha! I hadn't thought of that.
The possibility of multiple result elements with the same name is sometimes
mentioned as a shortcoming in SQL, in that its not truly relational
(relational tuples require that every element be uniquely named).
SQLAlchemy's pushing of SQL tuples to be slightly more relational helps a
great deal on the Python side. In any case, I'm not aware of any use case
that benefits from having the same name multiple times in a result row.
In this case, I'm taking the select query and turning into a SELECT ... INTO.
However, frequently I'd like to be able to stream results *without*
post-processing them, because the consumer needs results in a
particular format, etc...
Thanks for the more verbose response - it really helped me understand
the issues (and including a solution - label() - was also very
useful).
--
Jon
--
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] Referential integrity actions are not doing what I want
On Jun 22, 2010, at 9:27 AM, exhuma.twn wrote:
Hi,
I have a table of items, where each item can be owned by one
person, and held by someone else. I want the owner to be
compulsory (not nullable), and the holder to be optional (nullable).
To model this I have two tables, one for contacts and one for items.
The item table has two fields owner_id and holder_id. Bot are
references to the contact table and have the on delete rule set to
restrict and set null respectively.
The problem is that when I want to delete the contact attached to the
holder_id column, it seems that SA tries to set *both* references to
null. It should not do this! For example: If you have an item which
has an owner_id 1 and a holder_id 2, then deleting the contact
with ID 2 will cause the following query:
'UPDATE item SET owner_id=%(owner_id)s, holder_id=%(holder_id)s WHERE
item.item_id = %(item_item_id)s' {'holder_id': None, 'item_item_id':
10, 'owner_id': None}
First of all, *why* is SA issuing this query at all?
I can't answer very specifically to what the case here is since you didn't
provide mappings or example code. I'm assuming you're using the ORM and we're
talking about relationship() here. It appears like holder_id and owner_id are
handled by different relationships() altogether. Assuming that's the case,
affecting the holder relationship would have no affect on owner_id.
So if the case is that owner is being detached and you'd like item to be
deleted, you need to be using delete, delete-orphan cascade so that the row
is deleted, rather than dereferenced from the parent.
http://www.sqlalchemy.org/docs/session.html#cascades
However, I see you have RESTRICT set on owner_id. If you would like to instead
entirely prevent the modification of owner_id when the parent object is
deleted, and allow referential integrity to raise an error, you should set
passive_deletes to all, which will disable the nulling out on delete of the
parent. This is documented under relationship at
http://www.sqlalchemy.org/docs/reference/orm/mapping.html?#sqlalchemy.orm.relationship
.
A delete query
would suffice. The ref. integrity should be handled by the DB,
shouldn't it? More importantly, it updates both owner_id and
holder_id. But as previously said, owner_id=1 and holder_id=2. So
deleting contact #2 should only trigger - if at all - an update query
to set holder_id to null.
Any ideas as to what I am doing wrong here?
Here's my model for those two tables:
contact = Table( 'contact', metadata,
Column( 'contact_id', Integer, primary_key=True,
nullable=False),
Column( 'label', Unicode(64), nullable=False ),
)
item = Table( 'item', metadata,
Column( 'item_id', Integer, nullable=False, primary_key=True ),
Column( 'label', Unicode(64) ),
Column( 'barcode', String(64) ),
Column( 'comment', UnicodeText() ),
Column( 'owner_id', Integer, ForeignKey('contact.contact_id',
onupdate=CASCADE, ondelete=RESTRICT), nullable=False),
Column( 'holder_id', Integer, ForeignKey('contact.contact_id',
onupdate=CASCADE, ondelete=SET NULL), nullable=True),
)
--
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] orm.joinedload() bug ?
Hello, I wondered why when using orm.joinedload() in an .options() on a Query it returns an AppenderQuery object, but when I user lazy='joined' it returns a mapped object (on 0.6.1) ? for example : ### with : http://pastebin.com/7NHeL8kd (lazy = dynamic) a = model.Content.query.options( orm.joinedload(model.Content.owner) ).get(6) a amnesia.model.event.Event object at 0x922192c a.owner sqlalchemy.orm.dynamic.AppenderQuery object at 0x92213cc ### with : http://pastebin.com/USyfGwF7 (lazy = joined) a = model.Content.query.get(6) a amnesia.model.event.Event object at 0x921fa8c a.owner amnesia.model.auth.Human object at 0x921fb2c Is it a bug or a normal behavior ? Thanks, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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. attachment: jcigar.vcf
Re: [sqlalchemy] orm.joinedload() bug ?
On Jun 22, 2010, at 9:25 AM, Julien Cigar wrote: Hello, I wondered why when using orm.joinedload() in an .options() on a Query it returns an AppenderQuery object, but when I user lazy='joined' it returns a mapped object (on 0.6.1) ? for example : ### with : http://pastebin.com/7NHeL8kd (lazy = dynamic) a = model.Content.query.options( orm.joinedload(model.Content.owner) ).get(6) a amnesia.model.event.Event object at 0x922192c a.owner sqlalchemy.orm.dynamic.AppenderQuery object at 0x92213cc ### with : http://pastebin.com/USyfGwF7 (lazy = joined) a = model.Content.query.get(6) a amnesia.model.event.Event object at 0x921fa8c a.owner amnesia.model.auth.Human object at 0x921fb2c Is it a bug or a normal behavior ? You're just changing the loader strategy of a relationship() from one that returns a Query to one that doesn't, so yes that is normal behavior. There's also no reason you'd want to use dynamic on a many-to-one relationship. dynamic is to enable retrieval of slices of a large collection. -- 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] orm.joinedload() bug ?
On 06/22/2010 18:13, Michael Bayer wrote: On Jun 22, 2010, at 9:25 AM, Julien Cigar wrote: Hello, I wondered why when using orm.joinedload() in an .options() on a Query it returns an AppenderQuery object, but when I user lazy='joined' it returns a mapped object (on 0.6.1) ? for example : ### with : http://pastebin.com/7NHeL8kd (lazy = dynamic) a = model.Content.query.options( orm.joinedload(model.Content.owner) ).get(6) a amnesia.model.event.Event object at 0x922192c a.owner sqlalchemy.orm.dynamic.AppenderQuery object at 0x92213cc ### with : http://pastebin.com/USyfGwF7 (lazy = joined) a = model.Content.query.get(6) a amnesia.model.event.Event object at 0x921fa8c a.owner amnesia.model.auth.Human object at 0x921fb2c Is it a bug or a normal behavior ? You're just changing the loader strategy of a relationship() from one that returns a Query to one that doesn't, so yes that is normal behavior. There's also no reason you'd want to use dynamic on a many-to-one relationship. dynamic is to enable retrieval of slices of a large collection. Thanks for clarification, is setting lazy = dynamic on a orm.relationship() the same as creating an orm.dynamic_loader() .. ? (that's what I used in 0.5.x) -- 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. attachment: jcigar.vcf
[sqlalchemy] ALLCAPS datatypes
Hi everyone, I've been going through my app to prepare for an upgrade to SA 0.6 and was wondering what the deal is with the ALLCAPS vs. Capitalized data types. It looks like the ALLCAPS are designed to have the same name as the dialect specific types, while the Capitalized versions are designed to be generic. Am I right in that? For example, I'm on MySQL with enumeration columns. I used to use MSEnum with strict=True. It looks like I want to move to ENUM to continue using the strict option. But will an Enum automatically support the ENUM features if the database supports it? I guess I wonder this - which am I supposed to use? Ross -- Ross Vandegrift r...@kallisti.us If the fight gets hot, the songs get hotter. If the going gets tough, the songs get tougher. --Woody Guthrie signature.asc Description: Digital signature
Re: [sqlalchemy] ALLCAPS datatypes
On Jun 22, 2010, at 5:56 PM, Ross Vandegrift wrote: Hi everyone, I've been going through my app to prepare for an upgrade to SA 0.6 and was wondering what the deal is with the ALLCAPS vs. Capitalized data types. It looks like the ALLCAPS are designed to have the same name as the dialect specific types, while the Capitalized versions are designed to be generic. Am I right in that? For example, I'm on MySQL with enumeration columns. I used to use MSEnum with strict=True. It looks like I want to move to ENUM to continue using the strict option. But will an Enum automatically support the ENUM features if the database supports it? I guess I wonder this - which am I supposed to use? your assumptions about the ALLCAPS and such are correct.You should probably use the lowercase type as long as it does what you want, unless you want to enforce that an actual ENUM is used regardless of backend (or an error is raised). Enum as far as all the types is the thorniest since the Postgresql and MySQL implementations are so dramatically different. -- 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.
