[sqlalchemy] Re: sql executemany and postgresql - probably bug
Hi,
this issue (#759) seems fixed with the SA0.4b5. Thanks, Michael.
One additional question concerning last_inserted_ids() - is it
supposed to work below:
...
isql = Insert( table_Manager, values= {'name':bindparam('name'),
'duties':bindparam('duties
r2 = con.execute( isql, [
dict( name= 'torencho', duties= 'bany'),
dict( name= 'mnogoVojdMalkoIndianec', duties= 'lany'),
])
ids = r2.last_inserted_ids()
...
or i am using it improperly?
as it is now on the trunk(r3449) it gives error: AttributeError:
'PGExecutionContext' object has no attribute '_last_inserted_ids'.
regards,
stefan
--~--~-~--~~~---~--~~
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: sql executemany and postgresql - probably bug
On Sep 3, 2007, at 4:38 AM, che wrote:
Hi,
this issue (#759) seems fixed with the SA0.4b5. Thanks, Michael.
One additional question concerning last_inserted_ids() - is it
supposed to work below:
...
isql = Insert( table_Manager, values= {'name':bindparam('name'),
'duties':bindparam('duties
r2 = con.execute( isql, [
dict( name= 'torencho', duties= 'bany'),
dict( name= 'mnogoVojdMalkoIndianec', duties= 'lany'),
])
ids = r2.last_inserted_ids()
...
or i am using it improperly?
as it is now on the trunk(r3449) it gives error: AttributeError:
'PGExecutionContext' object has no attribute '_last_inserted_ids'.
OK, well the code needs to be cleaned up such that you get None and
not an attribute error there, but the last_inserted_ids
functionality only works when you execute a single set of
arguments. the reason for this is that DBAPI doesnt even define any
way to get at list of last inserted ids for an executemany() (i.e. it
only has cursor.lastrowid at best). With PG we execute a sequence,
but we still dont go through the extra overhead of storing it for
every row of the executemany, and in the latest release ive optimized
executemany a lot more so that we arent even pre executing the
sequence.
the name of the method is actually not that great, its plural ids
because of the potentially mulitple PK columns from a single row.
So in this case it should be returing None.
--~--~-~--~~~---~--~~
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: sql executemany and postgresql - probably bug
Thanks Michael, it is not urgent to me. if i found time i'll look down what is causing this and eventually try to patch. regards, stefan On 25 Авг, 03:09, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 23, 2007, at 11:18 AM, che wrote: i cant reply to this issue for another week since im out of town. as a workaround, placing autoincrement=False on your primary key column for now may resolve the issue. --~--~-~--~~~---~--~~ 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: sql executemany and postgresql - probably bug
Ive added ticket #759 for this and I think before 0.4 is finalized, I'll be working out the full solution for Postgres inserts/updates, which will allow a flag inline=True on all Insert/Update constructs indicating that all SQL expressions should execute inline (i.e. no pre-execution), and I'll also make it more intelligent about PG in this case (i.e. where SERIAL can autoincrement the sequence for you, dont render any literal sequence expression at all). inline=True will also take effect automatically for all executemany() scenarios since the pre-execute thing there is totally wasteful. (the pre- execution logic is primarily so we can get the row's ID in the case of a sequence-oriented database, which includes postgres, oracle and firebird). On Aug 27, 2007, at 2:44 AM, che wrote: Thanks Michael, it is not urgent to me. if i found time i'll look down what is causing this and eventually try to patch. regards, stefan On 25 Авг, 03:09, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 23, 2007, at 11:18 AM, che wrote: i cant reply to this issue for another week since im out of town. as a workaround, placing autoincrement=False on your primary key column for now may resolve the issue. --~--~-~--~~~---~--~~ 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: sql executemany and postgresql - probably bug
On Aug 23, 2007, at 11:18 AM, che wrote:
Hi,
On 23 Авг, 17:47, Michael Bayer [EMAIL PROTECTED] wrote:
dont compile() the insert statement yourself here; since you are only
executing it once, theres nothing to be gained by manually compiling
first.
this was the minimal code demonstrating the issue.
i planned to do this many times for bulk insert into the table in
database-independent way.
Its also the source of the error. the issue is that when the
Insert is compiled with no values clause, it produces column
entries for all three columns; but youre only sending two columns in
your argument list. this behavior is the same in 0.3.
seems there is some other prob too:
- in 0.3 it is issued 2 statements:
select nextval('Manager_id_seq')
and then:
INSERT INTO Manager (duties, name, id) VALUES (%(duties)s, %(name)s,
%(id)s)
with the ids got from the db and the other parameters.
- in 0.4 it is issued only the last insert with all ids None which
leads to the error
i cant reply to this issue for another week since im out of town. as
a workaround, placing autoincrement=False on your primary key
column for now may resolve the issue.
--~--~-~--~~~---~--~~
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: sql executemany and postgresql - probably bug
dont compile() the insert statement yourself here; since you are only
executing it once, theres nothing to be gained by manually compiling
first. Its also the source of the error. the issue is that when the
Insert is compiled with no values clause, it produces column
entries for all three columns; but youre only sending two columns in
your argument list. this behavior is the same in 0.3.
to compile the insert for just two columns (which again, you probably
dont need to do here), put them in the values clause:
c = Insert(values={'x':bindparam('x'), 'y':bindparam('y')}).compile()
engine.execute(c, {'x':5, 'y':7})
On Aug 23, 2007, at 10:16 AM, che wrote:
Hi,
i tried suggested in other thread way of inserting many records into
database table and it raised exception against postgres (psycopg2)
using the latest trunk (r3412) of SA. Then i checked that in version
0.3.10 same(analogical) code works. Please tell me if there is
something wrong with my usage of the 0.4 version of SA if this is not
a bug.
regards,
stefan
the sample code is below:
###
from sqlalchemy import *
from sqlalchemy.orm import *
#db_sqlite = create_engine( 'sqlite:///testdb.db', echo =True )
if 10:
import os
try:
r = os.system( 'dropdb testdb')
r = os.system( 'createdb testdb')
except OSError: pass
db_postgres = create_engine( 'postgres:///testdb', echo =True )
SA_VERSION = '0.4'
def checkWith( db):
if SA_VERSION == '0.3':
meta = BoundMetaData( db)
meta.engine.echo = 1
table_Manager = Table( 'Manager', meta,
Column( 'duties', type= String, ),
Column( 'name', type= String, ),
Column( 'id', Integer, primary_key= True, ),
)
else:
meta = MetaData( db)
meta.bind = db
meta.bind.echo = 1
table_Manager = Table( 'Manager', meta,
Column( 'duties', type_= String, ),
Column( 'name', type_= String, ),
Column( 'id', Integer, primary_key= True, ),
)
meta.create_all()
con = db.connect()
isql = table_Manager.insert().compile()
r2 = con.execute( isql, [
dict( name= 'torencho', duties= 'bany'),
dict( name= 'mnogoVojdMalkoIndianec', duties= 'lany'),
])
r2 = r2.last_inserted_ids()
print 'R2: %(r2)s\n' % locals()
#checkWith( db_sqlite)
checkWith( db_postgres)
--~--~-~--~~~---~--~~
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: sql executemany and postgresql - probably bug
Hi,
On 23 Авг, 17:47, Michael Bayer [EMAIL PROTECTED] wrote:
dont compile() the insert statement yourself here; since you are only
executing it once, theres nothing to be gained by manually compiling
first.
this was the minimal code demonstrating the issue.
i planned to do this many times for bulk insert into the table in
database-independent way.
Its also the source of the error. the issue is that when the
Insert is compiled with no values clause, it produces column
entries for all three columns; but youre only sending two columns in
your argument list. this behavior is the same in 0.3.
seems there is some other prob too:
- in 0.3 it is issued 2 statements:
select nextval('Manager_id_seq')
and then:
INSERT INTO Manager (duties, name, id) VALUES (%(duties)s, %(name)s,
%(id)s)
with the ids got from the db and the other parameters.
- in 0.4 it is issued only the last insert with all ids None which
leads to the error
to compile the insert for just two columns (which again, you probably
dont need to do here), put them in the values clause:
c = Insert(values={'x':bindparam('x'), 'y':bindparam('y')}).compile()
engine.execute(c, {'x':5, 'y':7})
even in this case the error is the same in 0.4:
2007-08-23 17:59:34,573 INFO sqlalchemy.engine.base.Engine.0x..2c
INSERT INTO Manager (duties, name, id) VALUES (%(duties)s, %(name)s,
%(id)s)
2007-08-23 17:59:34,573 INFO sqlalchemy.engine.base.Engine.0x..2c
[{'name': 'torencho', 'duties': 'bany', 'id': None}, {'name':
'mnogoVojdMalkoIndianec', 'duties': 'lany', 'id': None}]
2007-08-23 17:59:34,578 INFO sqlalchemy.engine.base.Engine.0x..2c
ROLLBACK
Traceback (most recent call last):
File insertMultiple.py, line 46, in module
checkWith( db_postgres)
File insertMultiple.py, line 40, in checkWith
dict( name= 'mnogoVojdMalkoIndianec', duties= 'lany'),
File /home/stefanb/src/hor/sqlalchemy/engine/base.py, line 784, in
execute
return Connection.executors[c](self, object, multiparams, params)
File /home/stefanb/src/hor/sqlalchemy/engine/base.py, line 815, in
_execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
parameters=param), multiparams, params)
File /home/stefanb/src/hor/sqlalchemy/engine/base.py, line 826, in
_execute_compiled
self.__execute_raw(context)
File /home/stefanb/src/hor/sqlalchemy/engine/base.py, line 838, in
__execute_raw
self.__executemany(context)
File /home/stefanb/src/hor/sqlalchemy/engine/base.py, line 872, in
__executemany
raise exceptions.SQLError(context.statement, context.parameters,
e)
sqlalchemy.exceptions.IntegrityError: (IntegrityError) null value in
column id violates not-null constraint
'INSERT INTO Manager (duties, name, id) VALUES (%(duties)s, %
(name)s, %(id)s)' [{'name': 'torencho', 'duties': 'bany', 'id': None},
{'name': 'mnogoVojdMalkoIndianec', 'duties': 'lany', 'id': None}]
regards,
stefan
--~--~-~--~~~---~--~~
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: sql executemany and postgresql - probably bug
seems there is some other prob too:
- in 0.3 it is issued 2 statements:
select nextval('Manager_id_seq')
and then:
INSERT INTO Manager (duties, name, id) VALUES (%(duties)s, %(name)s,
%(id)s)
with the ids got from the db and the other parameters.
- in 0.4 it is issued only the last insert with all ids None which
leads to the error
If you have to insert a lot of this... (and id is a serial) let postgres
do his work.
sql=INSERT INTO Manager (duties, name) VALUES (%(duties)s,
%(name)s)
list_qry_params = [('dut1','name1'),('dut2','name2'),('dut_n','name_n')]
engine.connect().execute(sql, list_qry_params )
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software(r) [EMAIL PROTECTED]
Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054
++
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
