[sqlalchemy] Re: Fetching last insert id from MySQL.
session imported from Meta ? If use Meta.Session.execute it's returns
RowProxy, which has no lastrowid parameter.
On 16 сен, 02:59, Michael Bayer mike...@zzzcomputing.com wrote:
no its not a column on a row, its on the ResultProxy:
result = session.execute('...')
id = result.lastrowid
http://www.sqlalchemy.org/docs/core/connections.html?highlight=result...
On Sep 15, 2010, at 5:51 PM, phasma wrote:
Lastrowid return: Could not locate column in row for column
'lastrowid'. I try to use transaction:
trans = meta.Session.begin()
try:
meta.Session.execute(INSERT statement)
result = meta.Session.execute(SELECT LAST_INSERT_ID())
trans.commit()
except:
trans.rollback()
raise
Now, I'm testing this, think it helps to stop loosing session between
INSERT and SELECT.
On 15 ÓÅÎ, 21:45, Michael Bayer mike...@zzzcomputing.com wrote:
SELECT LAST_INSERT_ID() is ultimately where the value comes from - the
raw .lastrowid is present on the ResultProxy for those DBAPIs which
support it, so try using that. Perhaps you're getting 0 because the
transaction is going away, in which case .lastrowid should solve that
issue.
On Sep 15, 2010, at 12:45 PM, phasma wrote:
I've got database with auto increment column called `id` and INSERT
query, whom I need to execute without model declaration in project.
meta.Session.execute() returns ResultProxy, but last_inserted_ids()
doesn't work with execute() and SELECT LAST_INSERT_ID() statement
sometimes return 0. Is there any other way to fetch last inserted id ?
--
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
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.
Re: [sqlalchemy] Re: Fetching last insert id from MySQL.
On 16/09/2010 11:49, phasma wrote: session imported from Meta ? If use Meta.Session.execute it's returns RowProxy, which has no lastrowid parameter. Try this: with meta.Session: result = meta.Session.execute(INSERT statement) print result.lastrowid cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] Re: Fetching last insert id from MySQL.
On Sep 16, 2010, at 6:49 AM, phasma wrote:
session imported from Meta ? If use Meta.Session.execute it's returns
RowProxy, which has no lastrowid parameter.
execute() does not return a RowProxy. All execute() methods return a
ResultProxy which consists of metadata about a result as well as an interator
interface that produces RowProxy instances.
On 16 сен, 02:59, Michael Bayer mike...@zzzcomputing.com wrote:
no its not a column on a row, its on the ResultProxy:
result = session.execute('...')
id = result.lastrowid
http://www.sqlalchemy.org/docs/core/connections.html?highlight=result...
On Sep 15, 2010, at 5:51 PM, phasma wrote:
Lastrowid return: Could not locate column in row for column
'lastrowid'. I try to use transaction:
trans = meta.Session.begin()
try:
meta.Session.execute(INSERT statement)
result = meta.Session.execute(SELECT LAST_INSERT_ID())
trans.commit()
except:
trans.rollback()
raise
Now, I'm testing this, think it helps to stop loosing session between
INSERT and SELECT.
On 15 ÓÅÎ, 21:45, Michael Bayer mike...@zzzcomputing.com wrote:
SELECT LAST_INSERT_ID() is ultimately where the value comes from - the
raw .lastrowid is present on the ResultProxy for those DBAPIs which
support it, so try using that. Perhaps you're getting 0 because the
transaction is going away, in which case .lastrowid should solve that
issue.
On Sep 15, 2010, at 12:45 PM, phasma wrote:
I've got database with auto increment column called `id` and INSERT
query, whom I need to execute without model declaration in project.
meta.Session.execute() returns ResultProxy, but last_inserted_ids()
doesn't work with execute() and SELECT LAST_INSERT_ID() statement
sometimes return 0. Is there any other way to fetch last inserted id ?
--
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
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.
--
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] Re: Fetching last insert id from MySQL.
I've found a solution. meta.Session.execute returns RowProxy instead of ResultProxy. Example: query = meta.engine.text(INSERT [...] VALUES(:text, :text1)) result = query.execute(text=123, text1=123) print result.lastrowid On 16 сен, 16:18, Chris Withers ch...@simplistix.co.uk wrote: On 16/09/2010 11:49, phasma wrote: session imported from Meta ? If use Meta.Session.execute it's returns RowProxy, which has no lastrowid parameter. Try this: with meta.Session: result = meta.Session.execute(INSERT statement) print result.lastrowid cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting -http://www.simplistix.co.uk -- 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] Re: Fetching last insert id from MySQL.
On Sep 16, 2010, at 11:15 AM, Michael Bayer wrote:
On Sep 16, 2010, at 10:47 AM, Chris Withers wrote:
On 16/09/2010 14:32, Michael Bayer wrote:
session imported from Meta ? If use Meta.Session.execute it's returns
RowProxy, which has no lastrowid parameter.
execute() does not return a RowProxy. All execute() methods return a
ResultProxy which consists of metadata about a result as well as an
interator interface that produces RowProxy instances.
Right, but I wonder if this mirrors what the OP reports:
return self.cursor.lastrowid
AttributeError: 'NoneType' object has no attribute 'lastrowid'
thats a bug, a regression introduced in 0.6.4. will fix.
this bug is fixed in r1668e771ad16 . r.lastrowid should work with 0.6.3 and
previous, as does SELECT LAST_INSERT_ID() if you are correctly calling it on
the same transaction.
Here is an example:
from sqlalchemy import *
from sqlalchemy.orm import *
m = MetaData()
t = Table(t, m,
Column(x, Integer, primary_key=True),
Column('data', String(50))
)
e = create_engine('mysql://scott:ti...@localhost/test')
t.drop(e)
t.create(e)
s = Session(e)
for i in xrange(10):
r = s.execute(insert into t (data) values ('foo'))
print s.scalar(SELECT LAST_INSERT_ID())
output:
1
2
3
4
5
6
7
8
9
10
execute() still doenst return a RowProxy tho
type(result)
class 'sqlalchemy.engine.base.ResultProxy'
session.commit()
2010-09-16 15:43:06,034 INFO sqlalchemy.engine.base.Engine.0x...e610 COMMIT
result.lastrowid
Traceback (most recent call last):
File console, line 1, in module
File SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/engine/base.py, line 2310, in
lastrowid
return self.cursor.lastrowid
AttributeError: 'NoneType' object has no attribute 'lastrowid'
I would have thought MySQL would support lastrowid?
Chris
--
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.
--
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] Re: internationalization of content
Hello again, I published an updated version of acts_as_localized on http://code.google.com/p/elixirlocalized/ It is an Elixir's Entity builder that will manage several translations for DB contents If anyone cares to review or use it. NiL -- 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] sql.expression.text in clause how to?
How can I use a list , tuple or any iterable in an in clause using the
plain sql expression and bindparams.
e.g.
engine.execute(sql.expression.text('''
select * from users where id in :ids
'''), ids=[1,2,3,4])
what do I need to change to make this work.
I tried this, and it generates not sql:
engine.execute(sql.expression.text('''
select * from users where id in (:ids)
'''), ids='1,2,3,4')
Any ideas to make this just work? I would prefer to use the bindparams
as above.
--
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] semantics of tometadata
Hi All, As part of looking into #1919, I see that if a table of the same name as the one passed to tometadata already exists in the destination metadata, then the table object passed in is ignored and the one already there is returned. That feels wrong to me. In the event there's already a table there, chances are it's not going to match the table I'm trying to copy to that metadata. As such, I'd expect an exception to be raised rather than the other table object being returned. What do people feel about this? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] semantics of tometadata
On Sep 16, 2010, at 2:56 PM, Chris Withers wrote: Hi All, As part of looking into #1919, I see that if a table of the same name as the one passed to tometadata already exists in the destination metadata, then the table object passed in is ignored and the one already there is returned. That feels wrong to me. In the event there's already a table there, chances are it's not going to match the table I'm trying to copy to that metadata. As such, I'd expect an exception to be raised rather than the other table object being returned. What do people feel about this? Im fine with tometadata raising for 0.7. a warning for 0.6 perhaps. note that tometadata has never been a real mainstreamy kind of function. I thought it does copy constraints though. cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] semantics of tometadata
On 16/09/2010 20:26, Michael Bayer wrote: As such, I'd expect an exception to be raised rather than the other table object being returned. What do people feel about this? Im fine with tometadata raising for 0.7. a warning for 0.6 perhaps. Cool, done for 0.6. Where should I make the changes for 0.7? note that tometadata has never been a real mainstreamy kind of function. It isn't for me either, but I do need to use it for gathering a bunch of tables from several packages into one place in order to do a .create_all and some schemadiff checks on them. The package I'm doing this is going to be open sourced at some point, so you can tell me how evil I am then ;-) I thought it does copy constraints though. Constraints yes, indexes no, also fixed now ;-) Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] Re: sql.expression.text in clause how to?
It seems it is not possible to do this with bindparams, my workaround
was as follows
On Sep 16, 2:01 pm, Pykler hnass...@gmail.com wrote:
engine.execute(sql.expression.text('''
select * from users where id in %s
'''% SqlTuple([1,2,3,4]))
Where SqlTuple is a tuple with a custom repr method to print without
the trailing ,
--
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] Re: sql.expression.text in clause how to?
On 09/16/2010 04:28 PM, Pykler wrote:
It seems it is not possible to do this with bindparams, my workaround
was as follows
On Sep 16, 2:01 pm, Pykler hnass...@gmail.com wrote:
engine.execute(sql.expression.text('''
select * from users where id in %s
'''% SqlTuple([1,2,3,4]))
Where SqlTuple is a tuple with a custom repr method to print without
the trailing ,
If you are using PostgreSQL with psycopg2, you can pass arrays as
bindparams in certain situations. Basically, if you can put an
ARRAY[...]::some_type[] literal in the SQL text, then you use a
bindparam there instead.
engine.execute(
text('''select * from users where id = ANY (:ids)'''),
ids=[1,2,3,4])
I would imagine that other databases that support arrays would work
similarly. There is also a ghetto way where you format ids as a
delimited string, e.g. ,1,2,3,4, and use select * from users where
:ids LIKE '%,' || id || ',%'. But you are probably better off
formatting the SQL directly like your workaround.
-Conor
--
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] Re: PostgreSQL, cascading and non-nullable ForeignKeys
I had misunderstood the documentation on relationships and then tied
myself in a knot, I thought that relationship() defined a strictly
parent to child relationship and that all the other parameters were
from the parents point of view.
My runs and is a lot cleaner!
Thank you for helping me,
BEN
On Sep 15, 3:20 pm, Conor conor.edward.da...@gmail.com wrote:
On 09/15/2010 05:04 PM, BenH wrote:
Hi,
I'm using SqlAlchemy 0.6.3 and PostgreSQL 8.4 and I'm trying to setup
a cascading delete between several levels of tables. The problem seems
to be that I can't have a relationship with cascade=all and a column
with ForeignKey that has nullable=False.
Your cascade clause is on the wrong side of the relationship. It means
you want to cascade TO the remote object, not FROM the remote object.
Here is my example:
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship
engine = create_engine('postgresql://tsq:passt%qw...@localhost:5432/
ce_cascade_test', echo=True)
Session = scoped_session(sessionmaker())
Base = declarative_base()
Session.configure(bind=engine)
s = Session()
class User(Base):
__tablename__ = users
id = Column(Integer, primary_key=True)
name = Column(String)
device = relationship(Device, uselist=False)
This should be:
device = relationship(Device, cascade=all, passive_deletes=True,
uselist=False)
Also, including uselist=False indicates that the User-Device
relationship is one-to-one. The lack of a unique constraint on
devices.user_id suggests a one-to-many relationship.
@classmethod
def create(cls, user_name, device_name, manufacturer):
new_user = User()
new_user.name = user_name
new_user.device = Device.create(device_name)
return new_user
class Device(Base):
__tablename__ = devices
id = Column(Integer, primary_key=True)
name = Column(String)
user_id = Column(Integer,
ForeignKey('users.id'),
nullable=False)
manufacturer_id = Column(Integer,
ForeignKey('manufacturers.id'),
nullable=False)
user = relationship(User,
uselist=False,
cascade=all)
This should be:
user = relationship(User)
Adding uselist=False here is redundant, since that is the default for
the side which contains the foreign key. Also, by including
'cascade=all', you are telling SQLAlchemy to implicitly delete the
user when the device is deleted. This is probably not what you want.
@classmethod
def create(cls, name):
new_device = Device()
new_device.name = name
return new_device
Base.metadata.create_all(engine)
user = User.create(bob, iphone)
s.add(user)
s.commit()
s.delete(user)
s.commit()
If I run this then I get an Integrity error during the delete (because
it is updating the user_id to null before deleting it). I would like
to keep the nullable=False on the ForeignKey.
I've tried adding ondelete=CASCADE to the ForeignKey and adding
passive_deletes=True to the relationship but it always throw the same
Integrity error.
I know I'm missing something but I can't find it in the docs, what am
I missing?
Thanks for your help,
BEN
-Conor
--
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.
